Or I forgot to mention, I think the old hack of OFFSET like you have in 3 still works and for backward compatibility would be preferable over using the MATERIALIZED keyword, but less clear in intent
So something WITH foo AS (SELECT ST_GeometryType(geom) gtype, geom FROM tmp_test_geoms WHERE ST_GeometryType(geom) = 'ST_Point' OFFSET 0) SELECT * FROM foo WHERE ST_X(geom) > 1; From: Regina Obe [mailto:[email protected]] Sent: Saturday, September 5, 2020 2:12 PM To: 'PostGIS Users Discussion' <[email protected]> Subject: RE: [postgis-users] Bizarre behavior with postgis function as part of where clause! Have you tried adding a MATERIALIZED to your WITH? In PostgreSQL 12 and above, CTEs are no longer always materialized. e.g. WITH foo AS MATERIALIZED (SELECT ST_GeometryType(geom) gtype, geom FROM tmp_test_geoms WHERE ST_GeometryType(geom) = 'ST_Point') SELECT * FROM foo WHERE ST_X(geom) > 1; This article describes it a bit https://paquier.xyz/postgresql-2/postgres-12-with-materialize/ From: postgis-users [mailto:[email protected]] On Behalf Of Bo Guo Sent: Friday, September 4, 2020 11:28 PM To: [email protected] <mailto:[email protected]> Subject: [postgis-users] Bizarre behavior with postgis function as part of where clause! 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
_______________________________________________ postgis-users mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users
