Bug #454: result of intersect depends on order?

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