[postgis-users] Bizarre behavior with postgis function as part of where clause!

[Bo Guo]

Hi List,

I just ran into a pretty bizarre query behavior involving simple queries 
involving PostGIS functions as part of where clause condition.

I have two geometries in table tmp_test_geoms , one point and a line:

     ST_Point    "0101000000806FE29BC53B2241800FDDE3E9C53341"
     ST_LineString 
"010200000005000000E072B95C215A22412033C40C65963341401C0E074D5A22412097CB056E963341809821E6235E22412033C48C6D963341A027E909BA5F22410087C3E16E963341C0B2AEACD95F224100FCFDFE71963341"

Both Query 1 and Query 2 fails w/ message "Argument to ST_X() must have 
type POINT," Query 3 works however

     1. SELECT ST_GeometryType(geom) gtype, geom FROM tmp_test_geoms 
WHERE ST_GeometryType(geom) = 'ST_Point' AND ST_X(geom) > 1;

     2. WITH foo AS (SELECT ST_GeometryType(geom) gtype, geom FROM 
tmp_test_geoms WHERE ST_GeometryType(geom) = 'ST_Point')SELECT * FROM 
foo WHERE ST_X(geom) > 1;
     3. WITH foo AS (SELECT ST_GeometryType(geom) gtype, geom FROM 
tmp_test_geoms WHERE ST_GeometryType(geom) = 'ST_Point' LIMIT 10)SELECT 
* FROM foo WHERE ST_X(geom) > 1;

It seems that the query engine evaluates ST_X first before applying the 
GeometryType filter, even when the filter is in a CTE.

It further seems that by adding LIMIT with an arbitrary value at the end 
of the CTE, the filter is applied first!

Wonder if there is a way to give some type of query hint to force the 
evaluation sequences.

Thanks for any suggestions!

Bo

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200904/b3426aa2/attachment.html>