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

[Paragon Corporation]

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/0d4ff4cb/attachment.html>