Обсуждение: intersect bug

Hello,

The following exemplifies a problem with Postgresql 7.1.3 that I have
recently encountered. I have an impression that a similar problem was
addressed in version 7.1.3 but, as it seems, not completely.

+++++++++++++++++++++++++++++++++++++++++++++++++++++
(select k.bib from kw k where k.kword='concept')
intersect
(select k.bib from kw k where k.kword='of')
intersect
(select k.bib from kw k where k.kword='god');
 bib
---
(0 rows)

// THIS GIVES AN INCORRECT RESULT - see the following //


(select k.bib from kw k where k.kword='of')
intersect
(select k.bib from kw k where k.kword='concept')
intersect
(select k.bib from kw k where k.kword='god');
   bib
---------
 1233804
(1 row)

// THIS GIVES A CORRECT RESULT //
// SEE BELOW FOR FURTHER CLARIFICATION //
// A TEMPORARY, USER-SIDE SOLUTION USING 'distinct' IS ALSO SHOWN //

(select k.bib from kw k where k.kword='concept')
intersect
(select k.bib from kw k where k.kword='of' and bib=1233804)
intersect
(select k.bib from kw k where k.kword='god');
   bib
---------
 1233804
(1 row)

(select k.bib from kw k where k.kword='concept');
   bib
---------
 1233804
 1233811
 1234135
(3 rows)

(select k.bib from kw k where k.kword='of');
...
 1215972
 1215972
 1234632
 1107678
 1216005
 1178890
 1234370
  796097
 1234786
 1121627
(3618 rows)

(select k.bib from kw k where k.kword='god');
   bib
---------
 1233804
 1233827
 1233881
 1233920
 1233985
 1234001
 1234014
 1234763
(8 rows)

(select k.bib,k.spool,k.kword from kw k where k.kword='of' and bib=1233804);
   bib   | spool | kword
---------+-------+-------
 1233804 | p     | of
 1233804 | t     | of
(2 rows

(select k.bib from kw k where k.kword='concept')
intersect
(select distinct k.bib from kw k where k.kword='of')
intersect
(select k.bib from kw k where k.kword='god');
   bib
---------
 1233804
+++++++++++++++++++++++++++++++++++++++++++++++++++++

I hope the above is generic enough.


Sincerely,

_________________________________________________________________________
Wiktor Rzeczkowski, PhD            Phone:  (905) 525-9140, Ext. 23877
Systems Development                E-mail: rzeczkow@mcmaster.ca
Mills Memorial Library             Fax:    (905) 546-0625
McMaster University, 1280 Main St. W., Hamilton, Ont. L8S 4L6, Canada

Wiktor Rzeczkowski <rzeczkow@mcmail.cis.mcmaster.ca> writes:
> (select k.bib from kw k where k.kword='concept')
> intersect
> (select k.bib from kw k where k.kword='of')
> intersect
> (select k.bib from kw k where k.kword='god');
>  bib
> ---
> (0 rows)

> // THIS GIVES AN INCORRECT RESULT - see the following //

Yup.  Nested intersect/except structures are buggy in 7.1.*.  I believe
it's fixed for 7.2 however.  If you are in a hurry for a fix, you could
try back-patching the most recent change depicted at
http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/optimizer/prep/prepunion.c

            regards, tom lane