intersect bug

From: Wiktor Rzeczkowski <rzeczkow(at)mcmail(dot)cis(dot)mcmaster(dot)ca>
To: <pgsql-bugs(at)postgresql(dot)org>
Subject: intersect bug
Date: 2001-10-23 01:43:32
Message-ID: Pine.SOL.4.33.0110222129210.24655-100000@mcmail.cis.mcmaster.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs


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(at)mcmaster(dot)ca
Mills Memorial Library Fax: (905) 546-0625
McMaster University, 1280 Main St. W., Hamilton, Ont. L8S 4L6, Canada

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message pgsql-bugs 2001-10-23 08:47:31 Bug #490: Can't compile PostgreSQL 7.1.3 with Solaris 2.6
Previous Message Tom Lane 2001-10-22 21:44:42 Re: Bug #489: pg_dump places GRANT queries before CREATE VIEW