<html>
<head>
<title></title>
<meta name="GENERATOR" content="MSHTML 8.00.6001.18783"></meta>
</head>
<body>
<div align="left">Thanks</div>
<div align="left"> </div>
<div align="left">That sounds like an explanation I can buy. It is a little uncomfortable (and maybe evil as you said) with automated things that is difficult to see through. </div>
<div align="left"> </div>
<div align="left">I don't understand the sub-query properly I guess.</div>
<div align="left">As I understand it the data is supposed to be handled like being a real table, but physicly just a virtual. In that light it is easy to see why the values have to be casted to something before put into the virtual table. But then, if the consept is that the subquery should behave like a real table, I cannot understand why it is behaving like we have seen earlier, recalculating the values in the subquery each time the result is asked from the outer query.</div>
<div align="left"> </div>
<div align="left">Thanks</div>
<div align="left">/Nicklas</div>
<div align="left"> </div>
<div align="left"><br />
2009-07-13 Paragon Corporation wrote:<br />
<br />
></div>
<div align="left"><span style="FONT-FAMILY: Arial; COLOR: #0000ff; FONT-SIZE: 10pt">Nicklas,</span></div>>
<div align="left"><span style="FONT-FAMILY: Arial; COLOR: #0000ff; FONT-SIZE: 10pt"> </span></div>>
<div align="left"><span style="FONT-FAMILY: Arial; COLOR: #0000ff; FONT-SIZE: 10pt">That is odd. And of course this works</span></div>>
<div align="left"><span style="FONT-FAMILY: Arial; COLOR: #0000ff; FONT-SIZE: 10pt"> </span></div>>
<div align="left"><span style="FONT-FAMILY: Arial; COLOR: #0000ff; FONT-SIZE: 10pt">SELECT st_astext(geom1) from<br />
>(SELECT CAST(NULL As geometry) as geom1) a;</span></div>>
<div align="left"><span style="FONT-FAMILY: Arial; COLOR: #0000ff; FONT-SIZE: 10pt"> </span></div>>
<div align="left"><span style="FONT-FAMILY: Arial; COLOR: #0000ff; FONT-SIZE: 10pt">All I can say is counting on autocasts is evil.</span></div>>
<div align="left"><span style="FONT-FAMILY: Arial; COLOR: #0000ff; FONT-SIZE: 10pt"> </span></div>>
<div align="left"><span style="FONT-FAMILY: Arial; COLOR: #0000ff; FONT-SIZE: 10pt">My guess what is happening is when you create the virtual table -- it automatically autocasts it to an unknown because there is no data type where as when fed directly to a function NULL has not been CAST yet so it can be CAST to anything and since ST_AsText only accepts geometry, the NULL gets cast to a geometry as a result of being fed into ST_AsText.</span></div>>
<div align="left"><span style="FONT-FAMILY: Arial; COLOR: #0000ff; FONT-SIZE: 10pt"> </span></div>>
<div align="left"><span style="FONT-FAMILY: Arial; COLOR: #0000ff; FONT-SIZE: 10pt">So your fallacy is assuming that NULL and unknown are the same thing. They are NOT. NULL can be cast to anything and unknown can not.</span></div>>
<div align="left"><span style="FONT-FAMILY: Arial; COLOR: #0000ff; FONT-SIZE: 10pt"> </span></div>>
<div align="left"><span style="FONT-FAMILY: Arial; COLOR: #0000ff; FONT-SIZE: 10pt">Observe this - yap works fine - I rest my case - in this case, PostgreSQL finds the likely data type for the virtual column -- in this case it would be a geometry</span></div>>
<div align="left"><span style="FONT-FAMILY: Arial; COLOR: #0000ff; FONT-SIZE: 10pt">since NULL can be cast to anything, and all the values in the column need to be cast to the same thing</span></div>>
<div align="left"><span style="FONT-FAMILY: Arial; COLOR: #0000ff; FONT-SIZE: 10pt"> </span></div>>
<div align="left"><span style="FONT-FAMILY: Arial; COLOR: #0000ff; FONT-SIZE: 10pt">SELECT st_astext(geom1) from<br />
>(select NULL as geom1 <br />
>UNION ALL <br />
>SELECT ST_GeomFromText('LINESTRING(1 2, 3 4)') As geom1<br />
>) a;</span></div>>
<div> </div>>
<div><span style="FONT-FAMILY: Arial; COLOR: #0000ff; FONT-SIZE: 10pt">Hope that helps,</span></div>>
<div><span style="FONT-FAMILY: Arial; COLOR: #0000ff; FONT-SIZE: 10pt">Regina</span><br />
></div>>
<div align="left">
<hr />
<span style="FONT-FAMILY: Tahoma; FONT-SIZE: 10pt"><b>From:</b> postgis-devel-bounces@postgis.refractions.net [mailto:postgis-devel-bounces@postgis.refractions.net] <b>On Behalf Of </b>nicklas.aven@jordogskog.no<br />
><b>Sent:</b> Monday, July 13, 2009 4:08 AM<br />
><b>To:</b> postgis-devel@postgis.refractions.net<br />
><b>Subject:</b> [postgis-devel] why this difference when sub-query<br />
></span><br />
></div>>
<div> </div>>
<div>I'm working on the regression-tests for the distance-functions and don't really understand the behavior of postgis or the planner or what it is, doing it.</div>>
<div> </div>>
<div>if I run:<br />
>select st_astext(geom1) from<br />
>(select NULL as geom1) a;</div>>
<div> </div>>
<div>I get:</div>>
<div><br />
>FEIL: failed to find conversion function from unknown to geometry</div>>
<div><br />
>********** Error **********</div>>
<div>FEIL: failed to find conversion function from unknown to geometry<br />
>SQL state: XX000</div>>
<div> </div>>
<div>but if I run:</div>>
<div>select st_astext(NULL)</div>>
<div>or</div>>
<div>select st_astext(NULL) from<br />
>(select NULL as geom1) a;</div>>
<div> </div>>
<div>then I just get an empty answer without error-message.</div>>
<div> </div>>
<div>and if I run :</div>>
<div> </div>>
<div>select geom1 from<br />
>(select NULL as geom1) a;</div>>
<div> </div>>
<div>I also get just an empty answer.</div>>
<div> </div>>
<div>Why is this conversion-function trigged when I use a sub-query and not otherwise and only if I use the NULL-value in a function and not if I just show it.</div>>
<div> </div>>
<div> </div>>
<div> </div>
</body>
</html>