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

Wrong results for queries with "OR" and "LEFT JOIN" #4049

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

Wrong results for queries with "OR" and "LEFT JOIN" #4049

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

Comments

@monetdb-team
Copy link

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

Date: 2016-08-02 07:10:35 +0200
From: Roman <<kislenok.roman>>
To: SQL devs <>
Version: 11.23.7 (Jun2016-SP1)
CC: @njnes

Last updated: 2016-10-13 10:04:32 +0200

Comment 22276

Date: 2016-08-02 07:10:35 +0200
From: Roman <<kislenok.roman>>

User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/51.0.2704.63 Safari/537.36
Build Identifier:

Just updated to new release and catch a bug within a query with "OR".

Reproducible: Always

Steps to Reproduce:

  1. create table test1 (a int, b int, d int);

  2. create table test_dic1 (a int, c int);

  3. insert into test_dic1 values (1, 1), (1, 2);

  4. insert into test1 values (1, 2, 1), (1, 3, 2), (2, 2, 1), (2, 3, 2)

  5. select dd.*
    from test_dic1 as dd
    inner join test1 as d1 on d1.a = dd.a and d1.d = 1
    left join test1 as d2 on d2.a = dd.a and d2.d = 2
    where dd.c = 1

Get 1 row as expected

  1. select dd.*
    from test_dic1 as dd
    inner join test1 as d1 on d1.a = dd.a and d1.d = 1
    left join test1 as d2 on d2.a = dd.a and d2.d = 2
    where dd.c = 1 or dd.c = 3

Got 2 rows! But there are no dd.c = 3

Actual Results:

2 rows

Expected Results:

1 row

Comment 22277

Date: 2016-08-02 07:22:36 +0200
From: Roman <<kislenok.roman>>

If I replace "left join" with "inner join" the result would be ok:
"select *
from test_dic1 as dd
inner join test1 as d1 on d1.a = dd.a and d1.d = 1
inner join test1 as d2 on d2.a = dd.a and d2.d = 2
where dd.c = 1 or dd.c = 3"
prodeces 1 row.

But:
"select *
from test_dic1 as dd
inner join test1 as d1 on d1.a = dd.a and d1.d = 1
left join test1 as d2 on d2.a = dd.a and d2.d = 2
where dd.c in (1, 3)"
also produces 1 expected row

Comment 22278

Date: 2016-08-02 07:57:21 +0200
From: Roman <<kislenok.roman>>

Actualy by some Mumbo-Jumbo you could get expected results:
"select *
from test_dic1 as dd
inner join test1 as d1 on d1.a = dd.a
left join test1 as d2 on d2.a = dd.a and d2.d = 2
where dd.c = 1 or dd.c = 3
and d1.d = 1
and (((dd.c = 1 or dd.c = 3) and d2.d is null) or ((dd.c = 1 or dd.c = 3) and d2.d = 2))"

Comment 22290

Date: 2016-08-11 12:50:39 +0200
From: MonetDB Mercurial Repository <>

Changeset 16c01c543cab made by Martin van Dinther martin.van.dinther@monetdbsolutions.com in the MonetDB repo, refers to this bug.

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

Changeset description:

Add test innerjoin-leftjoin-or.Bug-4049

Comment 22293

Date: 2016-08-11 21:29:22 +0200
From: MonetDB Mercurial Repository <>

Changeset 0d2c55c221c2 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=0d2c55c221c2

Changeset description:

fixed leftjoin problem / bug #4049

Comment 22294

Date: 2016-08-11 21:30:03 +0200
From: @njnes

fixed by marking the end of the outerjoin properly

Comment 24499

Date: 2016-10-13 10:04:32 +0200
From: @sjoerdmullender

Jun2016-SP2 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