INNER JOIN with NULL values returning incorrect result
Hi all,
I'm trying to do a simple INNER JOIN with two tables whereby the 1st table can contain NULL values in the join column. The query is as such:
---------- SELECT "B"."id", "A"."other_id" FROM "A" INNER JOIN "B" ON "B"."id" = "A"."other_id" WHERE "A"."afnemer" = '789' AND "A"."orgid" = 123; ----------
However this returns an incorrect result, such as: ---------- +------+----------+ | id | other_id | +======+==========+ | 143 | 143 | | 166 | 166 | | 147 | 147 | | 264 | 264 | | 323 | null | | 323 | 271 | | 271 | 275 | | 275 | 269 | | 269 | 150 | ----------
Clearly the JOIN has somehow mapped incorrect rows together from tables A and B. If I add a NOT NULL condition to the query on table A, I get correct results. This is the following query:
---------- SELECT "A"."other_id", "B"."id" , "A"."orgid" FROM "A" INNER JOIN "B" ON "B"."id" = "A"."other_id" WHERE "A"."afnemer" = '789' AND "A"."orgid" = 123 AND "A".other_id IS NOT NULL; ----------
Is this a known bug in MonetDB? Should I always add a NOT NULL condition to my JOIN queries?
Best regards, Dennis Pallett
On Thu, Jul 02, 2015 at 01:51:55PM +0200, Dennis Pallett wrote:
Hi all,
I'm trying to do a simple INNER JOIN with two tables whereby the 1st table can contain NULL values in the join column. The query is as such:
SELECT "B"."id", "A"."other_id" FROM "A" INNER JOIN "B" ON "B"."id" = "A"."other_id" WHERE "A"."afnemer" = '789' AND "A"."orgid" = 123;
However this returns an incorrect result, such as:
+------+----------+ | id | other_id | +======+==========+ | 143 | 143 | | 166 | 166 | | 147 | 147 | | 264 | 264 | | 323 | null | | 323 | 271 | | 271 | 275 | | 275 | 269 | | 269 | 150 |
Clearly the JOIN has somehow mapped incorrect rows together from tables A and B. If I add a NOT NULL condition to the query on table A, I get correct results. This is the following query: Is this a known bug in MonetDB? Should I always add a NOT NULL condition to my JOIN queries?
We did fix a similar bug recently, ie which version are you using? Could you report this bug including example data/query on the bugtracker.
Niels
Best regards, Dennis Pallett
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Hi Niels,
We're using version v11.19.15 (Oct2014-SP4).
I will also create a very small test set + queries and report it on the bug tracker.
Best regards, Dennis
On Thu, Jul 2, 2015 at 3:11 PM, Niels Nes Niels.Nes@cwi.nl wrote:
On Thu, Jul 02, 2015 at 01:51:55PM +0200, Dennis Pallett wrote:
Hi all,
I'm trying to do a simple INNER JOIN with two tables whereby the 1st table can contain NULL values in the join column. The query is as such:
SELECT "B"."id", "A"."other_id" FROM "A" INNER JOIN "B" ON "B"."id" = "A"."other_id" WHERE "A"."afnemer" = '789' AND "A"."orgid" = 123;
However this returns an incorrect result, such as:
+------+----------+ | id | other_id | +======+==========+ | 143 | 143 | | 166 | 166 | | 147 | 147 | | 264 | 264 | | 323 | null | | 323 | 271 | | 271 | 275 | | 275 | 269 | | 269 | 150 |
Clearly the JOIN has somehow mapped incorrect rows together from tables A and B. If I add a NOT NULL condition to the query on table A, I get correct results. This is the following query: Is this a known bug in MonetDB? Should I always add a NOT NULL condition to my JOIN queries?
We did fix a similar bug recently, ie which version are you using? Could you report this bug including example data/query on the bugtracker.
Niels
Best regards, Dennis Pallett
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- Niels Nes, Manager ITF, Centrum Wiskunde & Informatica (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room L3.14, phone ++31 20 592-4098 sip:4098@sip.cwi.nl url: https://www.cwi.nl/people/niels e-mail: Niels.Nes@cwi.nl
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
On Thu, Jul 02, 2015 at 03:36:20PM +0200, Dennis Pallett wrote:
Hi Niels,
We're using version v11.19.15 (Oct2014-SP4).
The fixes went into the jul2015 branch, which we hopefully release soon. Could you try this, or are you dependend on pre build binaries.
Niels
I will also create a very small test set + queries and report it on the bug tracker.
Best regards, Dennis
On Thu, Jul 2, 2015 at 3:11 PM, Niels Nes Niels.Nes@cwi.nl wrote:
On Thu, Jul 02, 2015 at 01:51:55PM +0200, Dennis Pallett wrote: > Hi all, > > I'm trying to do a simple INNER JOIN with two tables whereby the 1st > table can contain NULL values in the join column. The query is as such: > > ---------- > SELECT > "B"."id", > "A"."other_id" > FROM "A" > INNER JOIN "B" ON "B"."id" = "A"."other_id" > WHERE "A"."afnemer" = '789' > AND "A"."orgid" = 123; > ---------- > > However this returns an incorrect result, such as: > ---------- > +------+----------+ > | id | other_id | > +======+==========+ > | 143 | 143 | > | 166 | 166 | > | 147 | 147 | > | 264 | 264 | > | 323 | null | > | 323 | 271 | > | 271 | 275 | > | 275 | 269 | > | 269 | 150 | > ---------- > > Clearly the JOIN has somehow mapped incorrect rows together from tables > A and B. If I add a NOT NULL condition to the query on table A, I get > correct results. This is the following query: > Is this a known bug in MonetDB? Should I always add a NOT NULL > condition to my JOIN queries? We did fix a similar bug recently, ie which version are you using? Could you report this bug including example data/query on the bugtracker. Niels > > Best regards, > Dennis Pallett > _______________________________________________ > users-list mailing list > users-list@monetdb.org > https://www.monetdb.org/mailman/listinfo/users-list -- Niels Nes, Manager ITF, Centrum Wiskunde & Informatica (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room L3.14, phone ++31 20 592-4098 sip:4098@sip.cwi.nl url: https://www.cwi.nl/people/niels e-mail: Niels.Nes@cwi.nl _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
participants (2)
-
Dennis Pallett -
Niels Nes