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