<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Hi, "offset 0" in CTE worked. I later used CASE-CLAUSE to avoid
using CTE and worked as well. Cheers!<br>
</p>
<pre>SELECT ST_GeometryType(geom) gtype, geom FROM <tt><span>tmp_test_geoms</span></tt> WHERE <font size="+1">CASE WHEN ST_GeometryType(geom) = 'ST_Point' THEN ST_X(geom) < 1 ELSE FALSE END</font>;
</pre>
<div class="moz-cite-prefix">On 9/5/20 11:14 AM, Regina Obe wrote:<br>
</div>
<blockquote type="cite"
cite="mid:000501d683b0$6023f3e0$206bdba0$@pcorp.us">
<meta name="Generator" content="Microsoft Word 15 (filtered
medium)">
<div class="WordSection1">
<p class="MsoNormal"><span>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</span></p>
<p class="MsoNormal"><span> </span></p>
<p class="MsoNormal"><span>So something</span></p>
<p class="MsoNormal"><span> </span></p>
<p><tt><span>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;</span></tt></p>
<p class="MsoNormal"><span> </span></p>
<p class="MsoNormal"><span> </span></p>
<div>
<div>
<div>
<p class="MsoNormal"><b><span>From:</span></b><span>
Regina Obe [<a class="moz-txt-link-freetext" href="mailto:lr@pcorp.us">mailto:lr@pcorp.us</a>] <br>
<b>Sent:</b> Saturday, September 5, 2020 2:12 PM<br>
<b>To:</b> 'PostGIS Users Discussion'
<a class="moz-txt-link-rfc2396E" href="mailto:postgis-users@lists.osgeo.org"><postgis-users@lists.osgeo.org></a><br>
<b>Subject:</b> RE: [postgis-users] Bizarre behavior
with postgis function as part of where clause!</span></p>
</div>
</div>
<p class="MsoNormal"> </p>
<p class="MsoNormal"><span>Have you tried adding a
MATERIALIZED to your WITH? In PostgreSQL 12 and above,
CTEs are no longer always materialized.</span></p>
<p class="MsoNormal"><span> </span></p>
<p class="MsoNormal"><span>e.g.</span></p>
<p class="MsoNormal"><span> </span></p>
<p class="MsoNormal"><tt><span>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;</span></tt></p>
<p class="MsoNormal"><tt><span> </span></tt></p>
<p class="MsoNormal"><tt><span>This article describes it a bit</span></tt></p>
<p class="MsoNormal"><tt><span> </span></tt></p>
<p class="MsoNormal"><span><a
href="https://paquier.xyz/postgresql-2/postgres-12-with-materialize/"
moz-do-not-send="true">https://paquier.xyz/postgresql-2/postgres-12-with-materialize/</a></span></p>
<p class="MsoNormal"><span> </span></p>
<div>
<div>
<div>
<p class="MsoNormal"><b><span>From:</span></b><span>
postgis-users [<a
href="mailto:postgis-users-bounces@lists.osgeo.org"
moz-do-not-send="true">mailto:postgis-users-bounces@lists.osgeo.org</a>]
<b>On Behalf Of </b>Bo Guo<br>
<b>Sent:</b> Friday, September 4, 2020 11:28 PM<br>
<b>To:</b> <a
href="mailto:postgis-users@lists.osgeo.org"
moz-do-not-send="true">postgis-users@lists.osgeo.org</a><br>
<b>Subject:</b> [postgis-users] Bizarre behavior
with postgis function as part of where clause!</span></p>
</div>
</div>
<p class="MsoNormal"> </p>
<p>Hi List,</p>
<p>I just ran into a pretty bizarre query behavior involving
simple queries involving PostGIS functions as part of
where clause condition. </p>
<p>I have two geometries in table tmp_test_geoms , one point
and a line: </p>
<p class="MsoNormal"><tt><span> ST_Point
"0101000000806FE29BC53B2241800FDDE3E9C53341"</span></tt><span><br>
<tt> ST_LineString
"010200000005000000E072B95C215A22412033C40C65963341401C0E074D5A22412097CB056E963341809821E6235E22412033C48C6D963341A027E909BA5F22410087C3E16E963341C0B2AEACD95F224100FCFDFE71963341"</tt></span></p>
<p>Both Query 1 and Query 2 fails w/ message "Argument to
ST_X() must have type POINT," Query 3 works however</p>
<p><tt><span> 1. SELECT ST_GeometryType(geom) gtype, geom
FROM tmp_test_geoms WHERE ST_GeometryType(geom) =
'ST_Point' AND ST_X(geom) > 1;</span></tt><span><br>
<br>
<tt> 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;</tt><br>
<tt> 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;</tt></span></p>
<p>It seems that the query engine evaluates ST_X first
before applying the GeometryType filter, even when the
filter is in a CTE. </p>
<p>It further seems that by adding LIMIT with an arbitrary
value at the end of the CTE, the filter is applied first!</p>
<p>Wonder if there is a way to give some type of query hint
to force the evaluation sequences.</p>
<p>Thanks for any suggestions!</p>
<p>Bo</p>
</div>
</div>
</div>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<pre class="moz-quote-pre" wrap="">_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>
<a class="moz-txt-link-freetext" href="https://lists.osgeo.org/mailman/listinfo/postgis-users">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></pre>
</blockquote>
<pre class="moz-signature" cols="72">--
Regards,
Bo Guo, PhD, PE
President
Gistic Research, Inc.
2033 E Warner Rd Ste 105
Tempe, AZ 85284
<a class="moz-txt-link-abbreviated" href="http://www.gisticinc.com">www.gisticinc.com</a>
<a class="moz-txt-link-abbreviated" href="http://www.youtube.com/linearbench">www.youtube.com/linearbench</a>
Office: 480-656-9962
Cell: 602-570-4697</pre>
</body>
</html>