Обсуждение: COALESCE() bug?

Hi,

the following SQL statement gives an error: ERROR:  fix_indxqual_references: Cannot handle node type 723

if I leave out the COALESCE() statements, everything is ok..
(Postgresql 6.5.1-2, Debian Package)

Regards,

SQL query:

SELECT o.f_number as ordNo,  o.shortDescription as ordDesc, o.f_date as ordDate,  o.autoEntry as ordAEnt, o.objectid as
objectid, o.invoiceAddrKindID as invAddr, o.delNoteAddrKindID as delAddr,  o.currency as currency, pt.name as cond,
o.f_commentas comment
 
FROM Orders o, PaymentTerms pt
WHERE (coalesce(o.paymentTermsOId,-1)=pt.objectid) and (o.customerOID = 300)

UNION SELECT o.f_number as ordNo, o.shortDescription as ordDesc, o.f_date as ordDate, o.autoEntry as ordAEnt,
o.objectidas objectid, o.invoiceAddrKindID   as invAddr, o.delNoteAddrKindID   as delAddr, o.currency as currency,
pt.nameas cond, o.f_comment as comment
 
FROM Orders o, PaymentTerms pt
WHERE (coalesce(o.paymentTermsOId,-1)=pt.objectid)  and (o.customerOID = 300)
ORDER by ordDate, ordNo

-- 
Jens Glaser     Am Holderstrauch 13, 36041 Fulda, 0661/9429507    jens@jens.de

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.

I'll take a look, but I can't guarantee that the fix is simple enough
to back-patch into 6.5 ... you might have to drop the index on
pt.objectid to get this to work :-(
        regards, tom lane

> 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.
> 
> I'll take a look, but I can't guarantee that the fix is simple enough
> to back-patch into 6.5 ... you might have to drop the index on
> pt.objectid to get this to work :-(

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

--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

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",