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: 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 19:16:31
Message-ID: 871vlqnals.fsf@news-spur.riddles.org.uk (view raw or flat)
Thread:
Lists: pgsql-bugs
 > Bernt Marius Johnsen wrote:
 >> Dump of the database:

To save anyone else the bother, there's a VASTLY simpler testcase for
this one, requiring no tables at all:

test1=# explain select * from (values (1),(null)) v(k) where k = k order by k;
                            QUERY PLAN                             
-------------------------------------------------------------------
 Sort  (cost=0.04..0.04 rows=2 width=4)
   Sort Key: "*VALUES*".column1
   ->  Values Scan on "*VALUES*"  (cost=0.00..0.03 rows=2 width=4)
(3 rows)

test1=# explain select * from (values (1),(null)) v(k) where k = k;
                         QUERY PLAN                          
-------------------------------------------------------------
 Values Scan on "*VALUES*"  (cost=0.00..0.03 rows=1 width=4)
   Filter: (column1 = column1)
(2 rows)

Notice that the (k = k) qual is being dropped somewhere, which changes
the output since that's a disguised not-null condition.

-- 
Andrew (irc:RhodiumToad)

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2009-09-28 19:27:20
Subject: Re: BUG #5084: Query gives different number of rows depending on ORDER BY
Previous:From: Bernt M. JohnsenDate: 2009-09-28 16:16:47
Subject: Re: BUG #5084: Query gives different number of rows depending on ORDER BY

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