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

Bug #454: result of intersect depends on order?

From: pgsql-bugs(at)postgresql(dot)org
To: pgsql-bugs(at)postgresql(dot)org
Subject: Bug #454: result of intersect depends on order?
Date: 2001-09-17 15:39:41
Message-ID: 200109171539.f8HFdfF04215@hub.org (view raw or flat)
Thread:
Lists: pgsql-bugs
karl s.eiringer (karl(at)t0(dot)or(dot)at) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
result of intersect depends on order?

Long Description
first at all - postgres:fabulous

while concatenating queries in php there happend this strange error - it seems that intersecting queries do not produce the same result when they are rearranged in order - has it to do with the space of result, 'cause if it try the query with the less results on top it works, otherwise there's a false result

below the statements & some counts and results:
the first query brings the result as expected, the second doesn't

thx in advance
karl.

Sample Code
easytravel2=# ( SELECT DISTINCT o.pofferid FROM offer o, destination d    WHERE
easytravel2(# o.fdestination = d.pdestinationid AND d.key >= 1616030000 AND d.key <
easytravel2(# 1616040000 )
easytravel2-# 
easytravel2-# INTERSECT
easytravel2-# 
easytravel2-# ( SELECT DISTINCT o.pofferid FROM offer o, offerisincategory oic    WHERE
easytravel2(# o.pofferid = oic.pfoffer AND oic.pfoffercategory = 1011 )
easytravel2-# 
easytravel2-# INTERSECT
easytravel2-# 
easytravel2-# ( SELECT DISTINCT o.pofferid FROM offer o    WHERE o.state = 1 )
easytravel2-# ;
 pofferid 
----------
     2943
(1 row)

easytravel2=# ( SELECT DISTINCT o.pofferid FROM offer o    WHERE o.state = 1 )
easytravel2-# 
easytravel2-# INTERSECT
easytravel2-# 
easytravel2-# ( SELECT DISTINCT o.pofferid FROM offer o, offerisincategory oic    WHERE
easytravel2(# o.pofferid = oic.pfoffer AND oic.pfoffercategory = 1011 )
easytravel2-# 
easytravel2-# INTERSECT
easytravel2-# 
easytravel2-# ( SELECT DISTINCT o.pofferid FROM offer o, destination d    WHERE
easytravel2(# o.fdestination = d.pdestinationid AND d.key >= 1616030000 AND d.key <
easytravel2(# 1616040000 )
easytravel2-# ;
 pofferid 
----------
(0 rows)

easytravel2=# ( SELECT DISTINCT count(o.pofferid) FROM offer o    WHERE o.state = 1 );
 count 
-------
   364
(1 row)

easytravel2=# ( SELECT DISTINCT count(o.pofferid) FROM offer o, offerisincategory oic    WHERE
easytravel2(# o.pofferid = oic.pfoffer AND oic.pfoffercategory = 1011 )
easytravel2-# ;
 count 
-------
  1492
(1 row)


easytravel2=# ( SELECT DISTINCT count(o.pofferid) FROM offer o, destination d    WHERE
easytravel2(# o.fdestination = d.pdestinationid AND d.key >= 1616030000 AND d.key <
easytravel2(# 1616040000 )
easytravel2-# ;
 count 
-------
     4
(1 row)



No file was uploaded with this report


Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2001-09-17 17:46:05
Subject: Re: Bug #454: result of intersect depends on order?
Previous:From: Thomas LockhartDate: 2001-09-17 15:18:52
Subject: Re: Performance and 72.devel

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