Thread: BUG #2739: INTERSECT ALL not working
The following bug has been logged online: Bug reference: 2739 Logged by: Mason Hale Email address: masonhale@gmail.com PostgreSQL version: 8.1.5 Operating system: GNU/Linux 2.6.9-42.0.3.ELsmp Description: INTERSECT ALL not working Details: 'INTERSECT ALL' does not return duplicate rows in a query. The query below should return 10 rows, but it returns 5 rows on my system: ( SELECT tablename FROM pg_tables LIMIT 5 ) INTERSECT ALL ( ( SELECT tablename FROM pg_tables LIMIT 5 ) UNION ALL ( SELECT tablename FROM pg_tables LIMIT 5 ) ) Note, the above is a simplied query meant to demonstrate the problem. This same behavior occurs (and was discovered) in real-world situations with user-defined tables. This is nearly a deal-stopper for our application. Please reply to let me know the status of this report. Thanks, Mason Hale
"Mason Hale" <masonhale@gmail.com> writes: > The query below should return 10 rows, Not by my reading of the spec. SQL92 7.10 saith: b) If a set operator is specified, then the result of applying the set operator is a table containing the following rows: i) Let R be a row that is a duplicate of some row in T1 or of some row in T2 or both. Let m be the number of duplicates of R in T1 and let n be the number of duplicates of R in T2, where m >= 0 and n >= 0. ... iii) If ALL is specified, then ... 3) If INTERSECT is specified, then the number of duplicates of R that T contains is the minimum of m and n. You have m = 1, n = 2 for each distinct row at the INTERSECT step, ergo you get one copy out. regards, tom lane
Tom - Many thanks for the quick reply. I feel honored to receive email from you after seeing your name so many times in my web searches on Postgres topics. That's not how I understood INTERSECT ALL to work. But it's the clear the spec is right and my understanding is wrong. This is not a bug. Unfortunately the INTERSECT ALL as spec'd and implemented doesn't quite give me what I need. So back to the drawing board for me... best regards, Mason On 11/6/06, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > "Mason Hale" <masonhale@gmail.com> writes: > > The query below should return 10 rows, > > Not by my reading of the spec. SQL92 7.10 saith: > > b) If a set operator is specified, then the result of applying > the set operator is a table containing the following rows: > > i) Let R be a row that is a duplicate of some row in T1 or > of > some row in T2 or both. Let m be the number of duplicates > of R in T1 and let n be the number of duplicates of R in > T2, where m >= 0 and n >= 0. > > ... > > iii) If ALL is specified, then > > ... > > > 3) If INTERSECT is specified, then the number of > duplicates > of R that T contains is the minimum of m and n. > > You have m = 1, n = 2 for each distinct row at the INTERSECT step, > ergo you get one copy out. > > regards, tom lane >