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

[Bo Guo]

Hi, "offset 0" in CTE worked.  I later used CASE-CLAUSE to avoid using 
CTE and worked as well.  Cheers!

SELECT ST_GeometryType(geom) gtype, geom FROMtmp_test_geoms  WHERECASE WHEN ST_GeometryType(geom) = 'ST_Point' THEN ST_X(geom) < 1 ELSE 
FALSE END;


On 9/5/20 11:14 AM, Regina Obe wrote:
>
> 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:lr at pcorp.us]
> *Sent:* Saturday, September 5, 2020 2:12 PM
> *To:* 'PostGIS Users Discussion' <postgis-users at lists.osgeo.org>
> *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:postgis-users-bounces at lists.osgeo.org] 
> *On Behalf Of *Bo Guo
> *Sent:* Friday, September 4, 2020 11:28 PM
> *To:* postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org>
> *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
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users

-- 
Regards,

Bo Guo, PhD, PE
President
Gistic Research, Inc.
2033 E Warner Rd Ste 105
Tempe, AZ 85284
www.gisticinc.com
www.youtube.com/linearbench
Office: 480-656-9962
Cell: 602-570-4697

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