Using nulls with earthdistance operator crashes backend

Mark Stosberg <mark@summersault.com> writes:

* Install earthdistance operator from the contrib directory.
* try this:
cascade=> select null <@> '1,1'::point;

## The result I get:
pqReadData() -- backend closed the channel unexpectedly.

Probably the earthdistance functions are not NULL-safe and need to be
marked "isStrict" in CREATE FUNCTION. Would you be willing to do the
legwork on working up a patch for that?

regards, tom lane

I'll give it a shot and post the patch or let you know I'm stuck. :) Thanks!

-mark

Tom Lane wrote:

Mark Stosberg <mark@summersault.com> writes:

* Install earthdistance operator from the contrib directory.
* try this:
cascade=> select null <@> '1,1'::point;

## The result I get:
pqReadData() -- backend closed the channel unexpectedly.

Probably the earthdistance functions are not NULL-safe and need to be
marked "isStrict" in CREATE FUNCTION. Would you be willing to do the
legwork on working up a patch for that?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

--

http://mark.stosberg.com/

Tom Lane wrote:

Mark Stosberg <mark@summersault.com> writes:

* Install earthdistance operator from the contrib directory.
* try this:
cascade=> select null <@> '1,1'::point;

## The result I get:
pqReadData() -- backend closed the channel unexpectedly.

Probably the earthdistance functions are not NULL-safe and need to be
marked "isStrict" in CREATE FUNCTION. Would you be willing to do the
legwork on working up a patch for that?

Tom,

Here's a patch using "isstrict":

############
--- earthdistance.sql.in.org    Thu Aug 16 17:08:19 2001
+++ earthdistance.sql.in        Thu Aug 16 17:09:01 2001
@@ -3,7 +3,8 @@

 DROP FUNCTION geo_distance (point, point);
 CREATE FUNCTION geo_distance (point, point) RETURNS float8
-  AS 'MODULE_PATHNAME' LANGUAGE 'c';
+  AS 'MODULE_PATHNAME' LANGUAGE 'c'
+  WITH (isstrict);

SELECT geo_distance ('(1,2)'::point, '(3,4)'::point);
#############

Now when I run the "crasher" SQL above, I get one empty row back:

sumsault_test=# select null <@> '1,1'::point;
?column?
----------

(1 row)
#############

I look forward to seeing you at the Open Source Database Summit!

-mark

. . . . . . . . . . . . . . . . . . . . . . . . . .
Mark Stosberg Principal Developer
mark@summersault.com Summersault, LLC
v: 765-939-9301 ext 223 website development
. . . . . http://www.summersault.com/ . . . . . . .

Mark Stosberg <mark@summersault.com> writes:

Here's a patch using "isstrict":

Oh, there's just the one function? Sorry for making you go to the work
of submitting a patch ;-) ... I thought there'd be more to it. Will
apply.

regards, tom lane

This will be fixed in 7.2:

test=> select null <@> '1,1'::point;
?column?
----------

(1 row)

Hello!

Here's now to reproduce my bug:
* Start with Postgres 7.1.2
(specifically: PostgreSQL 7.1 on i386--freebsd4.3, compiled by GCC 2.95.3
)
* Install earthdistance operator from the contrib directory.
* try this:
cascade=> select null <@> '1,1'::point;

## The result I get:
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

###############

I expected simply for "null" to be returned as the result. I can work
around
this by including an extra step to make sure that my data is not null
before
it's passed off a SQL statement like this.

Thanks!

-mark

http://mark.stosberg.com/

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026