Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id XAA21560; Wed, 28 Jul 1999 23:50:48 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id XAA29430; Wed, 28 Jul 1999 23:47:52 -0400 (EDT) To: Bruce Momjian cc: jens@jens.de, pgsql-sql@postgreSQL.org, pgsql-patches@postgreSQL.org Subject: Re: [SQL] COALESCE() bug? In-reply-to: Your message of Wed, 28 Jul 1999 20:28:18 -0400 (EDT) <199907290028.UAA24698@candle.pha.pa.us> Date: Wed, 28 Jul 1999 23:47:52 -0400 Message-ID: <29428.933220072@sss.pgh.pa.us> From: Tom Lane Bruce Momjian writes: >> Jens Glaser 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",