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
