From: | "Michael Beckstette" <mbeckste(at)TechFak(dot)Uni-Bielefeld(dot)DE> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Problems with select chaining using INTERSECT |
Date: | 2002-06-13 19:56:38 |
Message-ID: | 1020613215638.ZM14513@sirari.TechFak.Uni-Bielefeld.DE |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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(at)techfak(dot)uni-bielefeld(dot)de
Universitaet Bielefeld Fon: +49-521-106-2914
Postfach 100131 Fax: +49-521-106-6411
D-33501 BIELEFELD
Germany
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2002-06-13 20:41:13 | Re: Problems with select chaining using INTERSECT |
Previous Message | Tom Lane | 2002-06-13 19:56:00 | Re: cannot connect to database and strange error |