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