pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: [SQL] COALESCE() bug?
4+ messages / 3 participants
[nested] [flat]

* COALESCE() bug?
@ 1999-07-29 01:29 Jens Glaser <jens@helena.jens.de>
  1999-07-29 00:13 ` Re: [SQL] COALESCE() bug? Tom Lane <tgl@sss.pgh.pa.us>
  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 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   ` Re: [SQL] COALESCE() bug? Bruce Momjian <maillist@candle.pha.pa.us>
  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 01:29 COALESCE() bug? Jens Glaser <jens@helena.jens.de>
  1999-07-29 00:13 ` Re: [SQL] COALESCE() bug? Tom Lane <tgl@sss.pgh.pa.us>
@ 1999-07-29 00:28   ` Bruce Momjian <maillist@candle.pha.pa.us>
  1999-07-29 03:47     ` Re: [SQL] COALESCE() bug? 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

* Re: [SQL] COALESCE() bug?
  1999-07-29 01:29 COALESCE() bug? Jens Glaser <jens@helena.jens.de>
  1999-07-29 00:13 ` Re: [SQL] COALESCE() bug? Tom Lane <tgl@sss.pgh.pa.us>
  1999-07-29 00:28   ` Re: [SQL] COALESCE() bug? Bruce Momjian <maillist@candle.pha.pa.us>
@ 1999-07-29 03:47     ` Tom Lane <tgl@sss.pgh.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