Skip site navigation (1) Skip section navigation (2)

Re: BUG #5084: Query gives different number of rows depending on ORDER BY

From: Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>
To: tgl(at)sss(dot)pgh(dot)pa(dot)us (Tom Lane), heikki(dot)linnakangas(at)enterprisedb(dot)com (Heikki Linnakangas), Bernt Marius Johnsen <bernt(dot)johnsen(at)sun(dot)com>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #5084: Query gives different number of rows depending on ORDER BY
Date: 2009-09-28 22:14:47
Message-ID: 8763b2lns8.fsf@news-spur.riddles.org.uk (view raw or flat)
Thread:
Lists: pgsql-bugs
>>>>> "Tom" == Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

 Tom> After digging into it, I find that:

 Tom> 1. Without ORDER BY, process_equivalence generates an
 Tom> equivalence class that lists k twice.  This is pretty bogus but
 Tom> it happens to produce the desired results in the example at
 Tom> hand.  (In some other cases you'll get redundant clauses out,
 Tom> because the eclass machinery isn't expecting this.)

 Tom> 2. With ORDER BY k, the code first creates a single-element
 Tom> equivalence class containing k, because it needs that to
 Tom> represent the desired pathkey.  Then, process_equivalence finds
 Tom> that both sides of the k = k clause are already known to be in
 Tom> the same eclass, so it concludes that this is redundant
 Tom> information.

I'd found the right place in the code, but I hadn't twigged that the
ORDER BY one was being called _first_, so I hadn't spotted the bug yet.

 Tom> I'm inclined to think that the best solution is to have
 Tom> process_equivalence just reject any clauses that have equal()
 Tom> left and right sides, ie, throw them back to be processed as
 Tom> ordinary non-equivalence clauses.  The only case I can think of
 Tom> where this might be less than ideal is if you have "k = k AND k
 Tom> = x"; if both operators are strict then the k = k test is indeed
 Tom> redundant and could be discarded, but it won't be.  But it
 Tom> doesn't seem like that's going to come up enough to be worth
 Tom> stressing about.  If we wanted to be smart about it we'd have to
 Tom> have two kinds of single-element equivalence classes (one that
 Tom> implies a k = k check is needed, and one that does not).  It
 Tom> doesn't seem worth the complication.

Hmm. Is it ever possible for mergejoinable operators to be non-strict?
Does that matter?

-- 
Andrew (irc:RhodiumToad)

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2009-09-28 23:10:27
Subject: Re: BUG #5084: Query gives different number of rows depending on ORDER BY
Previous:From: Tim BunceDate: 2009-09-28 20:23:01
Subject: Re: BUG #5066: plperl issues with perl_destruct() and ENDblocks

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group