pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Herouth Maoz <herouth@oumail.openu.ac.il>
Cc: Michael Richards <miker@scifair.acadiau.ca>
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] Re: [HACKERS] Counting bool flags in a complex query
Date: Mon, 19 Jul 1999 10:48:50 -0400
Message-ID: <27124.932395730@sss.pgh.pa.us> (raw)
In-Reply-To: <l03130303b3b8c4d43b74@[147.233.159.109]>

Herouth Maoz <herouth@oumail.openu.ac.il> writes:
> I think the problem results from using non-standard constructs such as
> order by expression, and indeed ordering by columns that don't appear in
> the select list.

No, that's not the problem.  I looked into this last night, but hadn't
got round to reporting my findings to the mail list.  Richards has
indeed tripped over a plain garden-variety bug, I think.  ORDER BY
clauses are set up to represent their targets by hard-coded pointers
to Resdom nodes in the target list, which is an incredibly fragile
representation, and I suspect it is failing to survive the slicing
dicing and mangling of the parsetree that happens during UNION
processing.  (The final output plan has Resdoms that are copies of the
originals, and the ORDER BY nodes are not pointing at the same copies as
are in the tlist, so any change to the tlist Resdoms blows the sort...)

We need to change this to be more like GROUP BY is currently done,
with an ID number that's actually stored in the Resdoms so that the
tlist can be copied without breaking it.  I'm afraid that means no
fix for 6.5.*, but I will try to fix it soon for 6.6.

> [ example of not using hidden target list entries snipped ]
> What is the difference? The difference is that now GROUP BY (which also
> does internal sorting) knows about that expression and considers it.

Richards' example would still fail if he did that.  He might have better
luck using UNION ALL instead of UNION --- I think that the specific
problem is that the DISTINCT implied by UNION requires a sort, which is
not getting merged with the user-given toplevel ORDER BY correctly as a
result of Resdom breakage.

We have had problems in the past with various bits of code failing to
ignore the hidden tlist entries ("resjunk" targets) that are added
to support ORDER BY or GROUP BY values that are not in the given tlist.
I suppose Herouth is suspicious of the feature because he's been burnt
by some of those bugs.  They are getting flushed out, though,
gradually...

			regards, tom lane



view thread (12+ messages)  latest in thread

Message-ID: <27124.932395730@sss.pgh.pa.us>
Permalink:  ../27124.932395730@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/27124.932395730@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, herouth@oumail.openu.ac.il, miker@scifair.acadiau.ca
  Subject: Re: [SQL] Re: [HACKERS] Counting bool flags in a complex query
  In-Reply-To: <27124.932395730@sss.pgh.pa.us>
  References: <l03130303b3b8c4d43b74@[147.233.159.109]> <27124.932395730@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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