[postgis-users] Getting inconsistent results with function in select vs in a join

[Stephen Woodbridge]

Argh,

It looks like this problem is related to casts, or at least adding some 
explicit casts seems to change the behavior. But I'm not getting the 
pattern as to what or why.

So here is a test query that I have been playing with:

select (b).*, * from (
     select imt_rgeo_countyzip(x, y) as b, c.* from (
         --select -79.2723008572695::real as x, 41.2393432049441::real 
as y                           -- BAD result
         --select -79.2723008572695::double precision as x, 
41.2393432049441::double precision as y   -- GOOD result
         --select -79.2723008572695::numeric as x, 
41.2393432049441::numeric as y                     -- GOOD result
         --select * from testdata a where gid=552 
                               -- BAD result
         --select x::numeric, y::numeric from testdata a where gid=552 
                               -- GOOD result
         --select x::real, y::real from testdata a where gid=552 
                               -- BAD result
         --select x::double precision, y::double precision from testdata 
a where gid=552              -- BAD result
     ) as c offset 0
  ) as d;

if you uncomment one of the commented sub selects you get a GOOD or BAD 
result as indicated. The function is declared as:

CREATE OR REPLACE FUNCTION imt_rgeo_countyzip(double precision, double 
precision)
   RETURNS rgeo_out AS
$BODY$
...

I just changed the test query and put a cast on x,y getting passed into 
the function and this give more consistent good behavior except of the 
values are x,y are already double precision.

select (b).*, * from (
     select imt_rgeo_countyzip(x::numeric, y::numeric) as b, c.* from (
         --select -79.2723008572695 as x, 41.2393432049441 as y 
                               -- GOOD result
         --select -79.2723008572695::real as x, 41.2393432049441::real 
as y                           -- GOOD result
         --select -79.2723008572695::double precision as x, 
41.2393432049441::double precision as y   -- GOOD result
         --select -79.2723008572695::numeric as x, 
41.2393432049441::numeric as y                     -- GOOD result
         --select * from testdata a where gid=552 
                               -- GOOD result
         --select x, y from testdata a where gid=552 
                               -- GOOD result
         --select x::real, y::real from testdata a where gid=552 
                               -- GOOD result
         --select x::double precision, y::double precision from testdata 
a where gid=552              -- BAD result
         --select x::numeric, y::numeric from testdata a where gid=552 
                               -- GOOD result
     ) as c offset 0
  ) as d;

Sorry for the line wrapping it makes this a little harder to read. I 
also tried changing the function definition to these:

CREATE OR REPLACE FUNCTION imt_rgeo_countyzip(numeric, numeric)
CREATE OR REPLACE FUNCTION imt_rgeo_countyzip(real, real)

And these threw errors when the arguments did not explicitly match real 
or numeric.

So is this a postgis bug? I do not understand why passing double 
precisions to a double precision function would give the wrong results 
nor how casting it to another type only to have it implicitly cast back 
fixes something.

Now more confused than ever! in spite of having a work-around to the 
problem. Clearly I'm missing something.

-Steve

On 10/24/2011 2:27 PM, Stephen Woodbridge wrote:
> Hi all,
>
> I have a query that gives me one result when I just call the stored
> procedure directly and a different result when it gets call with a join.
> I can not for the life of me figure out what is going on.
>
> So here is the basic query:
> select * from imt_rgeo_countyzip(-79.2723,41.2393);
> "Clarion";"PA";"42031";"16225";0;0
>
> Here is the row that I will join the above with:
> select * from testdata where gid=552;
> -79.2723;41.2393;"Z";552;"Clarion";"PA";"42031";"16225";0;0
>
> Here is the joined results:
> select *, imt_rgeo_countyzip(a.x,a.y) from testdata a where gid=552;
> -79.2723;41.2393;"Z";552;"Clarion";"PA";"42031";"16225";0;0;"(Clarion,PA,42031,16225,5242.59489074449,0)"
>
>
> Notice the 5242.59489074449 as the 2nd to last result to the
> imt_rgeo_countyzip() call here but in the reference query above it is 0.
> I have also verified the I'm selecting the same polygon in both of these
> cases. Is this a bug in PostGIS? Specifically in:
> ST_Distance_Spheroid(pnt, the_geom,
> 'SPHEROID["WGS 84",6378137,298.257223563]')
>
> My stored procedure definition is here: http://codepad.org/jt9WVoi7
>
> "PostgreSQL 8.3.14 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.3.real
> (Debian 4.3.2-1.1) 4.3.2"
>
> "POSTGIS="1.5.1" GEOS="3.2.0-CAPI-1.6.0" PROJ="Rel. 4.6.1, 21 August
> 2008" LIBXML="2.6.32" USE_STATS"
>
> Confused again,
> -Steve
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users