Обсуждение: BUG #1591: BETWEEN NULL AND NULL causes crash

The following bug has been logged online:

Bug reference:      1591
Logged by:          Michael Williamson
Email address:      michael.williamson@caseware.com
PostgreSQL version: 8.0.1
Operating system:   Windows 2000
Description:        BETWEEN NULL AND NULL causes crash
Details:

Hello,

I have the following query:

SELECT * FROM Entities
WHERE NULL IS NULL OR (EntityNo BETWEEN NULL AND COALESCE(NULL,NULL))

This causes one of two results: postmaster crashes or goes into an infinite
loop.  The reason I have such a strange query is because values are filled
in from a dialog.  It looks more like:

SELECT * FROM Entities
WHERE %p1 IS NULL OR (EntityNo BETWEEN %p1 AND COALESCE(%p2,%p1))

This query executes ok on other dbs (mssql, mysql) and returns the correct
result (all records).  I can fix it for postgre by doing:

SELECT * FROM Entities
WHERE NULL IS NULL OR (EntityNo BETWEEN NULL AND COALESCE(NULL,NULL,''))

But none the less nothing should make it loop infinitely or crash.

Thanks

"Michael Williamson" <michael.williamson@caseware.com> writes:
> SELECT * FROM Entities
> WHERE NULL IS NULL OR (EntityNo BETWEEN NULL AND COALESCE(NULL,NULL))
> This causes one of two results: postmaster crashes or goes into an infinite
> loop.

Good catch.  COALESCE(NULL,NULL) is actually broken as far back as 7.4,
though I'm not sure if the error is visible in that version.  If you
need the patch it's

Index: clauses.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/optimizer/util/clauses.c,v
retrieving revision 1.186.4.2
diff -c -r1.186.4.2 clauses.c
*** clauses.c    2 Feb 2005 21:49:43 -0000    1.186.4.2
--- clauses.c    10 Apr 2005 20:54:52 -0000
***************
*** 1771,1776 ****
--- 1771,1780 ----
              newargs = lappend(newargs, e);
          }

+         /* If all the arguments were constant null, the result is just null */
+         if (newargs == NIL)
+             return (Node *) makeNullConst(coalesceexpr->coalescetype);
+
          newcoalesce = makeNode(CoalesceExpr);
          newcoalesce->coalescetype = coalesceexpr->coalescetype;
          newcoalesce->args = newargs;


            regards, tom lane