From: | kevin(dot)perais(at)trivia-marketing(dot)com |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #12556: Clause IN and NOT IN buggy |
Date: | 2015-01-15 11:27:20 |
Message-ID: | 20150115112720.2502.55131@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 12556
Logged by: Kevin PERAIS
Email address: kevin(dot)perais(at)trivia-marketing(dot)com
PostgreSQL version: 9.3.5
Operating system: Ubuntu 12.04
Description:
Hi everybody.
I've been noticing several times that clauses IN and NOT IN are often buggy.
I'll show you 2 concrete cases to illustrate that.
---------------------------------------------
- 1 - IN and NOT IN not coherent with JOIN -
---------------------------------------------
I have 2 tables here:
Account(id BIGINT PRIMARY KEY NOT NULL, ...)
Contact(contact_id VARCHAR(11) PRIMARY KEY NOT NULL, account_id BIGINT,
...)
I the contacts are linked to an account. However, I know I do not have all
the account, so I want to know how many contacts are linked to an account
and how many are not.
Here is a set of queries:
SELECT count(*)
FROM Account
WHERE id IS NULL;
--> 0
SELECT count(*)
FROM Contact
WHERE account_id IS NULL;
--> 0
SELECT count(*)
FROM Contact;
--> 257726
SELECT count(*)
FROM Contact
WHERE account_id IN (
SELECT id
FROM Account
);
--> 257726 (result very very suspect)
SELECT count(*)
FROM Contact
WHERE account_id NOT IN (
SELECT id
FROM Account
);
--> 0 (coherent with previous query, but result very very suspect)
SELECT count(*)
FROM Contact
JOIN Account ON Account.id = Contact.account_id;
--> 135664
SELECT count(Account.id)
FROM Contact
LEFT JOIN Account ON Account.id = Contact.account_id;
--> 135664
So I decided to take a random account_id in Contact table and run the
following queries:
SELECT count(*)
FROM Contact
WHERE account_id = 3074054072;
--> 6
SELECT count(*)
FROM Account
WHERE id = 3074054072;
--> 0
So that proves that IN and NOT IN queries give me wrong results.
-------------------------------------------------
- 2 - NOT IN clause not coherent with IN clause -
-------------------------------------------------
I have 2 tables:
Ref(num INTEGER UNIQUE, ...)
Sample(num VARCHAR(9), ...)
Here is a set of queries:
SELECT count(*)
FROM Sample;
--> 692
SELECT count(DISTINCT num)
FROM Sample;
--> 673
SELECT count(*)
FROM Sample
WHERE num IS NULL;
--> 19
--> 673 + 19 = 692
--> So we now know there are 673 distinct num and 19 NULL in the Sample
table.
SELECT count(*)
FROM Ref;
--> 8232
SELECT count(*)
FROM Ref
WHERE num IS NULL;
--> 151
SELECT count(*)
FROM Sample
WHERE num::INTEGER IN (
SELECT num
FROM Ref
);
--> 2
SELECT count(*)
FROM Sample
WHERE num::INTEGER NOT IN (
SELECT num
FROM Ref
);
--> 0 (Well that is very strange and not coherent at all with the
previous query).
I might be able to provide a data set to test case 2 (but not case 1 as data
are confidential).
Hope I gave enough info on the bug.
Kevin
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2015-01-15 17:05:26 | Re: BUG #12556: Clause IN and NOT IN buggy |
Previous Message | Tom Lane | 2015-01-15 00:44:34 | Re: BUG #12553: Altering search_path between function calls |