[postgis-devel] why this difference when sub-query

[nicklas.aven at jordogskog.no]

Thanks 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.  I don't understand the sub-query properly I guess.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. Thanks/Nicklas 
2009-07-13 Paragon Corporation wrote:

>Nicklas,> >That is odd.  And of course this works> >SELECT st_astext(geom1) from
>(SELECT CAST(NULL As geometry) as geom1) a;> >All I can say is counting on autocasts is evil.> >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.> >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.> >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>since NULL can be cast to anything, and all the values in the column need to be cast to the same thing> >SELECT st_astext(geom1) from
>(select NULL as geom1 
>UNION ALL 
>SELECT ST_GeomFromText('LINESTRING(1 2, 3 4)') As geom1
>) a;>
 >
Hope that helps,>
Regina
>>From: postgis-devel-bounces at postgis.refractions.net [mailto:postgis-devel-bounces at postgis.refractions.net] On Behalf Of nicklas.aven at jordogskog.no
>Sent: Monday, July 13, 2009 4:08 AM
>To: postgis-devel at postgis.refractions.net
>Subject: [postgis-devel] why this difference when sub-query
>
>>
 >
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.>
 >
if I run:
>select st_astext(geom1) from
>(select NULL as geom1) a;>
 >
I get:>

>FEIL:  failed to find conversion function from unknown to geometry>

>********** Error **********>
FEIL: failed to find conversion function from unknown to geometry
>SQL state: XX000>
 >
but if I run:>
select st_astext(NULL)>
or>
select st_astext(NULL) from
>(select NULL as geom1) a;>
 >
then I just get an empty answer without error-message.>
 >
and if I run :>
 >
select geom1 from
>(select NULL as geom1) a;>
 >
I also get just an empty answer.>
 >
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.>
 >
 >
 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20090713/48c849d9/attachment.html>