Thread: PostgreSQL crashes using distance operator whith records where 'point' data type set to null

Hello,

I'd like to file the following bug report for two 'bugs' that may (or may
not) have the same origin.

The context is the following :

* PostgreSQL v7.0.2 from RPMs :
' PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66   '
postgresql-jdbc-7.0.2-2
postgresql-odbc-7.0.2-2
postgresql-server-7.0.2-2
postgresql-tcl-7.0.2-2
postgresql-devel-7.0.2-2
postgresql-7.0.2-2

* Computer :
Pentium III 550MHz, 256 Mo
Red Hat 6.2 base
Kernel 2.2.16
libc-2.1.3



Bug description :

The problem is the following :
I use geometric computation in my database, and in this context, I'm trying
to compute distances using PostgreSQL operator '<->'.
It happens that the coordinates I'm using are sometimes NULL, and in this
case, it leads to a backend termination (which is not very clean...)

-The first 'bug' is when you try to compute the distance between two points,
and one of them is NULL. The backend terminates. The workaround is to test
for nullity, and to do it before computing the distance.

-The second 'bug' is very strange, but, as I did not find a workaround, is
very annoying for me.
It happens the following way :
I have two 'point' fields (bar and bar2) in my table foo. I'm trying to
compute the best distance between a given point (parameter) and one of the
two fields. For doing this, I'm using the following request : (this is not
really postgresql-compatible SQL, but it is easier to read)

SELECT * FROM foo
    WHERE (distance(param1, bar)  < range)
       OR (distance(param1, bar2) < range)

When I use this request, and when I test for nullity of bar and bar2 before,
everything seems perfect.

But when I add one condition that WOULD return a result containing a NULL
point record, the backend terminates (EVEN when I test for nullity before the
distance computation) :

SELECT * FROM foo
    WHERE text='hello1'
          ( (distance(param1, bar)  < range)
       OR   (distance(param1, bar2) < range) )

[if I use a condition that WOULDN'T return a result, it works :

SELECT * FROM foo
    WHERE text='hello0'
          ( (distance(param1, bar)  < range)
       OR   (distance(param1, bar2) < range) )

as I know that I haven't any records containing 'hello0' in the 'text' field]



Steps to reproduce :

1) Create a 'test' database :
=============================
createdb test

2) Fill the 'test' database with a table 'foo' (using the 'wrong_db.sql' file)
==============================================================================
bash$ psql test
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

test=# \i wrong_db.sql
You are now connected as new user postgres.
DROP
CREATE
INSERT 78923 1

3) (Bug # 1) Now, try to compute a distance on a record where a 'point' data
type is NULL:
===========================================================================
test=# \i trythis1.sql
  bar  |  text  | bar2
-------+--------+-------
 (0,0) | hello1 | (0,0)
(1 row)

psql:trythis1.sql:2: pqReadData() -- backend closed the channel unexpectedly.
        This probably means the backend terminated abnormally
        before or while processing the request.
psql:trythis1.sql:2: connection to server was lost
bash$

[Note : the first statement is a workaround (explicitely testing for the
non-nullity before doing the distance computation)]

4) (Bug # 2) Now, try to compute two distances on a record where some 'point'
data types are NULL and with another condition : (reconnect before)
=============================================================================
test=# \i trythis2.sql
 bar | text | bar2
-----+------+------
(0 rows)

  bar  |  text  | bar2
-------+--------+-------
 (0,0) | hello1 | (0,0)
(1 row)

psql:trythis2.sql:3: pqReadData() -- backend closed the channel unexpectedly.
        This probably means the backend terminated abnormally
        before or while processing the request.
psql:trythis2.sql:3: connection to server was lost

[Note : I've not found a workaround ]


5) With no NULL records, it seems to work
=========================================

Re-experiment with database from file 'good_db.sql'



Regards,

--
----------------------------
-- Ludovic LANGE mailto:llange@ftd.fr
-- Groupe FTD    -     http://www.ftd.fr

Yeah, there are a lot of geometric operators that neglect to check for
null inputs :-(.  I believe I have fixed this class of problems in
current sources.

            regards, tom lane

Hi tom,

Thanks for the answer.
Can you tell me about the files concerned by this fix of yours ? I intend to
make a patch for the 7.0.2 sources, because, 1) I won't use a CVS version on
my production server [ and 2) I'm using the rpm-built postgresql and I didn't
manage to easily turn the CVS version into a RPM one ;-)  ]

Thanks in advance

On lun, 25 sep 2000, Tom Lane wrote:
> Yeah, there are a lot of geometric operators that neglect to check for
> null inputs :-(.  I believe I have fixed this class of problems in
> current sources.
>
>             regards, tom lane

--
----------------------------
-- Ludovic LANGE mailto:llange@ftd.fr
-- Groupe FTD    -     http://www.ftd.fr

Ludovic LANGE <llange@ftd.fr> writes:
> Can you tell me about the files concerned by this fix of yours ? I intend to
> make a patch for the 7.0.2 sources, because, 1) I won't use a CVS version on
> my production server [ and 2) I'm using the rpm-built postgresql and I didn't
> manage to easily turn the CVS version into a RPM one ;-)  ]

If you don't want a CVS version then you don't want my fix either ---
it's part of a wholesale restructuring of the function manager.  You'll
just have to start hacking on the geometry functions for yourself...

            regards, tom lane