<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<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. <br>
</p>
<p>I have two geometries in table tmp_test_geoms , one point and a
line: <br>
</p>
<tt> ST_Point "0101000000806FE29BC53B2241800FDDE3E9C53341"</tt><tt><br>
</tt><tt> ST_LineString
"010200000005000000E072B95C215A22412033C40C65963341401C0E074D5A22412097CB056E963341809821E6235E22412033C48C6D963341A027E909BA5F22410087C3E16E963341C0B2AEACD95F224100FCFDFE71963341"</tt><br>
<p>Both Query 1 and Query 2 fails w/ message "Argument to ST_X()
must have type POINT," Query 3 works however<br>
</p>
<p><tt> 1. SELECT ST_GeometryType(geom) gtype, geom FROM
tmp_test_geoms WHERE ST_GeometryType(geom) = 'ST_Point' AND
ST_X(geom) > 1;</tt><tt><br>
</tt><tt><br>
</tt><tt> 2. WITH foo AS (SELECT ST_GeometryType(geom) gtype,
geom FROM tmp_test_geoms WHERE ST_GeometryType(geom) =
'ST_Point')</tt><tt> SELECT * FROM foo WHERE ST_X(geom) > 1;</tt><tt><br>
</tt><tt> 3. WITH foo AS (SELECT ST_GeometryType(geom) gtype,
geom FROM tmp_test_geoms WHERE ST_GeometryType(geom) =
'ST_Point' </tt><font size="+2"><tt>LIMIT 10</tt></font><tt>)</tt><tt>
SELECT * FROM foo WHERE ST_X(geom) > 1;</tt><br>
</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. <br>
</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!<br>
</p>
<p>Bo<br>
</p>
</body>
</html>