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

Query giving wrong results, extra records are appearing #6150

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

Query giving wrong results, extra records are appearing #6150

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

Comments

@monetdb-team
Copy link

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

Date: 2016-12-16 20:13:49 +0100
From: Manuel <>
To: SQL devs <>
Version: 11.23.13 (Jun2016-SP2)
CC: @njnes

Last updated: 2017-01-26 14:56:38 +0100

Comment 24786

Date: 2016-12-16 20:13:49 +0100
From: Manuel <>

User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/55.0.2883.87 Safari/537.36
Build Identifier:

When I execute this query

select "t3e"."c22_store_phone_number" as "c3c_r_c"

from (
(select null as "c22_store_phone_number" from "test_join_right_table") union all
(select "store_phone_number" as "c22_store_phone_number" from "lineitem_denormalized_first1k")
) as "t3e"

where exists (select 1 as "one" from "test_join_right_table" as "t3d" where ("t3e"."c22_store_phone_number" = "t3d"."r_c" or ("t3e"."c22_store_phone_number" is null and "t3d"."r_c" is null)))

I get 4 extra records that should not be part of the result set.

Please find the DDL and all files necessary to reproduce the issue attached.

I expect only null values to be in the result set. Note that each condition in the final or produces the correct result.

Reproducible: Always

Steps to Reproduce:

  1. Execute the sql statements in 'create_join_tables.sql' to create one of the two tables, and insert the records.

  2. Execute the sql statement in 'create_lineitem_denormalized_first1k' to create the schema for the second table.

  3. Populate this second table with the CSV contained in 'lineitem_denormalized_first1k_headerless.csv'. You should be able to do so by running a command like: mclient -d visokio -s "COPY INTO lineitem_denormalized_first1k FROM '/home/visokio/lineitem_denormalized_headerless.csv' USING DELIMITERS ',','\n','"'"

  4. Run the query :

select "t3e"."c22_store_phone_number" as "c3c_r_c"

from (
(select null as "c22_store_phone_number" from "test_join_right_table") union all
(select "store_phone_number" as "c22_store_phone_number" from "lineitem_denormalized_first1k")
) as "t3e"

where exists (select 1 as "one" from "test_join_right_table" as "t3d" where ("t3e"."c22_store_phone_number" = "t3d"."r_c" or ("t3e"."c22_store_phone_number" is null and "t3d"."r_c" is null)))

The records

| +44-875-777-1712 |
| +44-875-777-1712 |
| +44-875-777-1712 |
| +44-875-777-1712 |

should not be part part of the output I think.

Actual Results:

+------------------+
| c3c_r_c |
+==================+
| null |
| null |
| null |
| null |
| +44-875-777-1712 |
| +44-875-777-1712 |
| +44-875-777-1712 |
| +44-875-777-1712 |
+------------------+

Expected Results:

+------------------+
| c3c_r_c |
+==================+
| null |
| null |
| null |
| null |
+------------------+

Comment 24787

Date: 2016-12-16 20:26:09 +0100
From: Manuel <>

Created attachment 521
DDL for one of the test table

Attached file: create_join_tables.sql (application/octet-stream, 358 bytes)
Description: DDL for one of the test table

Comment 24788

Date: 2016-12-16 20:26:34 +0100
From: Manuel <>

Created attachment 522
DDL for the other test table

Attached file: create_lineitem_denormalized_first1k.sql (application/octet-stream, 1238 bytes)
Description: DDL for the other test table

Comment 24789

Date: 2016-12-16 20:27:23 +0100
From: Manuel <>

Created attachment 523
CSV contained the data for the second test table

Attached file: lineitem_denormalized_first1k_headerless.csv (application/vnd.ms-excel, 319403 bytes)
Description: CSV contained the data for the second test table

Comment 24791

Date: 2016-12-16 20:27:44 +0100
From: Manuel <>

Created attachment 524
The query that produces the wrong result

Attached file: query.txt (text/plain, 449 bytes)
Description: The query that produces the wrong result

Comment 24870

Date: 2017-01-06 20:31:29 +0100
From: MonetDB Mercurial Repository <>

Changeset 8069d2353d05 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=8069d2353d05

Changeset description:

fix bug #6150, ie be more carefull with semijoin rewrites

Comment 24871

Date: 2017-01-06 20:32:06 +0100
From: @njnes

fixed, improved the semijoin push down rewriter

Comment 24872

Date: 2017-01-09 14:58:42 +0100
From: Manuel <>

(In reply to Niels Nes from comment 6)

fixed, improved the semijoin push down rewriter

Thanks a lot.

Comment 24917

Date: 2017-01-26 14:56:38 +0100
From: @kutsurak

Fixed in version Dec2016-SP1.

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