Re: [SQL] COALESCE() bug? - Mailing list pgsql-sql

Bruce Momjian <maillist@candle.pha.pa.us> writes:
>> Jens Glaser <jens@helena.jens.de> writes:
>>>> the following SQL statement gives an error:
>>>> ERROR:  fix_indxqual_references: Cannot handle node type 723
>> 
>> Interesting.  I wouldn't have thought 6.5 would try to use an index
>> with an expression involving COALESCE().  Evidently it does.

> Let me know if this is a TODO item.  Sounds interesting.

It's not a TODO item any more ;-).  Not really all that interesting
--- just another routine that didn't know about all the expression node
types it should have.  I am planning a sweep to catch all of these
guys and reduce them to calls of centralized tree-walker routines,
so that future addition of node types shouldn't be quite so painful.

In the meantime, I have fixed the problem via brute-force addition
of code in both the current and 6.5 CVS branches.  Since there may
or may not be a 6.5.2 release, I attach a diff against 6.5.1 code
for anyone who needs to patch this problem locally.
        regards, tom lane


*** src/backend/optimizer/plan/createplan.c.orig    Tue May 25 18:41:38 1999
--- src/backend/optimizer/plan/createplan.c    Wed Jul 28 23:08:35 1999
***************
*** 746,751 ****
--- 746,782 ----          return (Node *) newnode;     }
+     else if (IsA(clause, CaseExpr))
+     {
+         CaseExpr   *oldnode = (CaseExpr *) clause;
+         CaseExpr   *newnode = makeNode(CaseExpr);
+ 
+         newnode->casetype = oldnode->casetype;
+         newnode->arg = oldnode->arg;    /* XXX should always be null
+                                          * anyway ... */
+         newnode->args = (List *)
+             fix_indxqual_references((Node *) oldnode->args,
+                                     index_path);
+         newnode->defresult =
+             fix_indxqual_references(oldnode->defresult,
+                                     index_path);
+ 
+         return (Node *) newnode;
+     }
+     else if (IsA(clause, CaseWhen))
+     {
+         CaseWhen   *oldnode = (CaseWhen *) clause;
+         CaseWhen   *newnode = makeNode(CaseWhen);
+ 
+         newnode->expr =
+             fix_indxqual_references(oldnode->expr,
+                                     index_path);
+         newnode->result =
+             fix_indxqual_references(oldnode->result,
+                                     index_path);
+ 
+         return (Node *) newnode;
+     }     else     {         elog(ERROR, "fix_indxqual_references: Cannot handle node type %d",
*** src/backend/nodes/equalfuncs.c.orig    Sun Jun  6 13:46:40 1999
--- src/backend/nodes/equalfuncs.c    Wed Jul 28 23:07:56 1999
***************
*** 706,711 ****
--- 706,737 ----     return true; } 
+ static bool
+ _equalCaseExpr(CaseExpr *a, CaseExpr *b)
+ {
+     if (a->casetype != b->casetype)
+         return false;
+     if (!equal(a->arg, b->arg))
+         return false;
+     if (!equal(a->args, b->args))
+         return false;
+     if (!equal(a->defresult, b->defresult))
+         return false;
+ 
+     return true;
+ }
+ 
+ static bool
+ _equalCaseWhen(CaseWhen *a, CaseWhen *b)
+ {
+     if (!equal(a->expr, b->expr))
+         return false;
+     if (!equal(a->result, b->result))
+         return false;
+ 
+     return true;
+ }
+  /*  * Stuff from pg_list.h  */
***************
*** 873,878 ****
--- 899,910 ----             break;         case T_TargetEntry:             retval = _equalTargetEntry(a, b);
+             break;
+         case T_CaseExpr:
+             retval = _equalCaseExpr(a, b);
+             break;
+         case T_CaseWhen:
+             retval = _equalCaseWhen(a, b);             break;         default:             elog(NOTICE, "equal: don't
knowwhether nodes of type %d are equal",