"Michael Williamson" <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
