pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: [SQL] COALESCE() bug?
4+ messages / 3 participants
[nested] [flat]
* Re: [SQL] COALESCE() bug?
@ 1999-07-29 00:13 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jens Glaser <jens@helena.jens.de>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 1999-07-29 00:13 UTC (permalink / raw)
To: jens@jens.de; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] COALESCE() bug?
@ 1999-07-29 00:28 Bruce Momjian <maillist@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Bruce Momjian @ 1999-07-29 00:28 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: jens@jens.de; pgsql-sql
> 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, Pennsylvania 19026
^ permalink raw reply [nested|flat] 4+ messages in thread
* COALESCE() bug?
@ 1999-07-29 01:29 Jens Glaser <jens@helena.jens.de>
0 siblings, 1 reply; 4+ messages in thread
From: Jens Glaser @ 1999-07-29 01:29 UTC (permalink / raw)
To: pgsql-sql
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_comment as 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.objectid as objectid,
o.invoiceAddrKindID as invAddr, o.delNoteAddrKindID as delAddr,
o.currency as currency, pt.name as 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] COALESCE() bug?
@ 1999-07-29 03:47 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <maillist@candle.pha.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 1999-07-29 03:47 UTC (permalink / raw)
To: Bruce Momjian <maillist@candle.pha.pa.us>; +Cc: jens@jens.de; pgsql-sql; pgsql-patches@postgreSQL.org
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
inline patch 1: src/backend/optimizer/plan/createplan.c, src/backend/nodes/equalfuncs.c (+63/-0 lines) download inline-1.patch
*** 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",
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~1999-07-29 03:47 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-07-29 01:29 COALESCE() bug? Jens Glaser <jens@helena.jens.de>
1999-07-29 00:13 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-07-29 00:28 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-07-29 03:47 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox