Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

joins fail in the presence of nulls #3747

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments
Closed

joins fail in the presence of nulls #3747

monetdb-team opened this issue Nov 30, 2020 · 0 comments

Comments

@monetdb-team
Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

Date: 2015-06-26 09:18:20 +0200
From: John Thomas <>
To: SQL devs <>
Version: 11.19.15 (Oct2014-SP4)
CC: @njnes

Last updated: 2015-08-28 13:43:02 +0200

Comment 20943

Date: 2015-06-26 09:18:20 +0200
From: John Thomas <>

User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_10_3) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/43.0.2357.124 Safari/537.36
Build Identifier:

When joining on a column that contains nulls, the resulting output is incorrect.

Reproducible: Always

Steps to Reproduce:

  1. Very basic test case setup:

create table foo (a string, b int);
create table bar (a string, b int);
insert into foo values ('hi',0),('there',null),('monet',1);
insert into bar values ('sup',0),('dude',1);

  1. Perform join on column with null present.
  2. Observe failure.

Actual Results:

select foo.a,foo.b,bar.b,bar.a from foo join bar on foo.b=bar.b;
+-------+------+------+------+
| a | b | b | a |
+=======+======+======+======+
| hi | 0 | 0 | sup |
| there | null | 1 | dude |
+-------+------+------+------+

Expected Results:

+-------+------+------+------+
| a | b | b | a |
+=======+======+======+======+
| hi | 0 | 0 | sup |
| monet | 1 | 1 | dude |
+-------+------+------+------+

From what I've observed of larger test cases, the result seems to have the correct number of rows, with the correct values on the right and incorrect values on the left.

This bug is present in all releases from Jan2014 (11.17.9) through Oct2014-SP4 (11.19.15). The Feb2013 release behaves correctly.

Interestingly, if you add "where foo.b is not null" to the test query above, the optimizer performs that filter prior to the join and produces the expected result. This suggests that null values aren't being handled correctly in the join function itself.

Comment 20944

Date: 2015-06-26 20:23:32 +0200
From: @njnes

The mergejoin code didn't handle the nil properly (fixed in Jul2015).

Comment 21228

Date: 2015-08-28 13:43:02 +0200
From: @sjoerdmullender

Jul2015 has been released.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant