Re: [SQL] COALESCE() bug?

Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us> writes:
>> Jens Glaser <jens(at)helena(dot)jens(dot)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 know whether nodes of type %d are equal",