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

Incorrect results on joining with same table #3720

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

Incorrect results on joining with same table #3720

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-05-08 08:19:49 +0200
From: Vijay Krishna <>
To: SQL devs <>
Version: 11.19.11 (Oct2014-SP3)
CC: @njnes

Last updated: 2015-06-22 22:32:13 +0200

Comment 20853

Date: 2015-05-08 08:19:49 +0200
From: Vijay Krishna <>

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

I am using MonetDB Jan2014 SP1, running on MacOS. I have a table like this.

sql>select * from two;
+------+----------------------------------------------------------------------------------------------
| id | name | istrue | joindate | jointime | count |
+======+====================================================
| 1 | null | false | 2017-12-08 | 2014-06-30 14:05:31.000000 | 2 |
| 2 | vijay | false | 2017-12-08 | 2014-06-30 14:05:59.000000 | 3 |
| 3 | krish | true | 2017-12-08 | 2014-06-30 14:06:17.000000 | 5 |
| 4 | bat | true | null | null | null |
| 5 | gotham | false | null | null | null |
| 6 | wayne | false | null | null | 3 |
+------+-------------------------------------------------------------------------------------------------
6 tuples (3.504ms)

I tried to do a join on this table with the same table with the following query and got this result.

sql>select two.id, two.name, two.count, twoAlias.id as id2, twoAlias.name as name2 from two left join two twoAlias on two.count=twoAlias.id;
+------+--------------------------------------------------------------------------------------------------+

| id | name | count | id2 | name2

+=============================================================+

| 1 | null | 2 | 2 | vijay |

| 2 | vijay | 3 | 3 | krish |

| 3 | krish | 5 | 5 | gotham |

| 4 | bat | null | 3 | krish |

| 5 | gotham | null | null | null |

| 6 | wayne | 3 | null | null |

+------+----------------------------------------------------------------------------------------------------+

6 tuples (5.605ms)

As it could be seen clearly, this join is clearly resulting in a wrong output at the 4th row. The join criterion two.count=twoAlias.id is satisfied in the first 3 rows, but are clearly violated in the 4th row, where in the values in the join columns (count and id2 here) are clearly found to be different. Hence the join rows are not matching.

Reproducible: Always

Steps to Reproduce:

1.Create a table as above.
2.Do a select query by joining with the same table.
Eg - select two.id, two.name, two.count, twoAlias.id as id2, twoAlias.name as name2 from two left join two twoAlias on two.count=twoAlias.id;
3.Results will not be correct.

Actual Results:

sql>select two.id, two.name, two.count, twoAlias.id as id2, twoAlias.name as name2 from two left join two twoAlias on two.count=twoAlias.id;
+------+--------------------------------------------------------------------------------------------------+

| id | name | count | id2 | name2

+=============================================================+

| 1 | null | 2 | 2 | vijay |

| 2 | vijay | 3 | 3 | krish |

| 3 | krish | 5 | 5 | gotham |

| 4 | bat | null | 3 | krish |

| 5 | gotham | null | null | null |

| 6 | wayne | 3 | null | null |

+------+----------------------------------------------------------------------------------------------------+

Expected Results:

I expected that the output would be
+------+--------------------------------------------------------------------------------------------------+

| id | name | count | id2 | name2

+=============================================================+

| 1 | null | 2 | 2 | vijay |

| 2 | vijay | 3 | 3 | krish |

| 3 | krish | 5 | 5 | gotham |

| 4 | bat | null | null | null |

| 5 | gotham | null | null | null |

| 6 | wayne | 3 | 3 | krish |

+------+----------------------------------------------------------------------------------------------------+

6 tuples (5.605ms)

Comment 20866

Date: 2015-05-15 12:05:03 +0200
From: MonetDB Mercurial Repository <>

Changeset 443209cc4b86 made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=443209cc4b86

Changeset description:

added test for bug #3720

Comment 20871

Date: 2015-05-20 18:31:05 +0200
From: @njnes

Test added and runs correct

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