Обсуждение: Problems with select chaining using INTERSECT

Version: PostgreSQL 7.1.2 on sparc-sun-solaris2.5.1, compiled by GCC 2.95

Hi, when using SELECT queries chained by INTERSECT i get confused about the
behavour of the INTERSECT operation. I try to outline this with the following
examples.

Example (Field query_desc is of type TEXT):

select query_desc FROM blasthits_obj_3_q3_db3;
Returning: 4114 rows

select distinct query_desc FROM blasthits_obj_3_q3_db3 ;
Returning: 333 rows

Now intersecting this table with itself:
select query_desc FROM blasthits_obj_3_q3_db3
INTERSECT
select query_desc FROM blasthits_obj_3_q3_db3;
Returning: 333 rows   thats ok till now

Adding a second INTERSECT:
select query_desc FROM blasthits_obj_3_q3_db3
INTERSECT
select query_desc FROM blasthits_obj_3_q3_db3
INTERSECT
select query_desc FROM blasthits_obj_3_q3_db3;
Returning: 157 rows  hmm..

ok lets try it with brackets:

select query_desc FROM blasthits_obj_3_q3_db3
INTERSECT
(select query_desc FROM blasthits_obj_3_q3_db3
INTERSECT
select query_desc FROM blasthits_obj_3_q3_db3);
Returning: 333 rows    ok...and now with a third INTERSECT

select query_desc FROM blasthits_obj_3_q3_db3
INTERSECT
((select query_desc FROM blasthits_obj_3_q3_db3
INTERSECT
select query_desc FROM blasthits_obj_3_q3_db3)
INTERSECT
SELECT query_desc FROM blasthits_obj_3_q3_db3);
Returning: 157 rows.   WHY ?

I tried to reproduce this on a small table containing one column and 7 integer
entries (2 duplicates). But in this case it works as expected, even without
 brackets. So mybe there is a problem with the data type TEXT and INTERSECT ?

Any idea ?

-------------------------------------------------------------------------------
Dipl.-Inform. Michael Beckstette        Office: M3-129
AG-PI / Technische Fakultaet            EMail:mbeckste@techfak.uni-bielefeld.de
Universitaet Bielefeld                  Fon: +49-521-106-2914
Postfach 100131                         Fax: +49-521-106-6411
D-33501 BIELEFELD
Germany

"Michael Beckstette" <mbeckste@TechFak.Uni-Bielefeld.DE> writes:
> Version: PostgreSQL 7.1.2 on sparc-sun-solaris2.5.1, compiled by GCC 2.95
> Hi, when using SELECT queries chained by INTERSECT i get confused about the
> behavour of the INTERSECT operation.

Nested INTERSECT and EXCEPT queries are broken in 7.1.*.  Please upgrade
to 7.2.

            regards, tom lane

On Thu, 13 Jun 2002, Michael Beckstette wrote:

> Version: PostgreSQL 7.1.2 on sparc-sun-solaris2.5.1, compiled by GCC 2.95
>
> Hi, when using SELECT queries chained by INTERSECT i get confused about the
> behavour of the INTERSECT operation. I try to outline this with the following
> examples.

I don't remember exact details, but I believe there were bugs in the
intersect implementation of the past.  You might want to see if it works
on 7.2.x