INNER JOIN with NULL values returning incorrect result

2 Jul 2015

      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