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

Unexpected result when CREATE VIEW with WHERE NULL #7450

Closed
suyZhong opened this issue Jan 31, 2024 · 1 comment
Closed

Unexpected result when CREATE VIEW with WHERE NULL #7450

suyZhong opened this issue Jan 31, 2024 · 1 comment
Assignees
Labels
bug Something isn't working SQL
Milestone

Comments

@suyZhong
Copy link

Describe the bug
Consider the test cases below. It is unexpected that the query return true, since there should be no row in VIEW with NULL as the WHERE predicate. If we remove the GROUP BY clause for the VIEW, the query returns empty result as expected.

To Reproduce

DROP TABLE t0 CASCADE;
DROP VIEW v0 CASCADE;

CREATE TABLE t0(c0 INT);
INSERT INTO t0 ( c0) VALUES (false);
CREATE VIEW v0(c0) AS SELECT true FROM t0 WHERE NULL GROUP BY t0.c0, true;

SELECT * FROM v0;
-- Expected: Empty 
-- Actual: true


-- Or consider this one: 
SELECT * FROM v0 WHERE true; -- true (if the true is expected)
SELECT * FROM v0 WHERE true UNION ALL SELECT * FROM v0 WHERE (NOT true); -- empty (unexpected)

Expected behavior
As mentioned above

Screenshots
N/A

Software versions
Build from source:

> docker exec monetdb-test mserver5 --version --dbname=monetdb
MonetDB 5 server 11.50.0 (hg id: 1a6a23b) (64-bit, 128-bit integers)
This is an unreleased version
Copyright (c) 1993 - July 2008 CWI
Copyright (c) August 2008 - 2023 MonetDB B.V., all rights reserved
Visit https://www.monetdb.org/ for further information
Found 503.5GiB available memory, 128 available cpu cores
Libraries:
Compiled by: @fdb2b8a5a74e (x86_64-pc-linux-gnu)
Compilation: /usr/bin/cc 
Linking    : /usr/bin/ld

Issue labeling
Bug

Additional context
Add any other context about the problem here.

@njnes njnes self-assigned this Feb 8, 2024
@njnes njnes added the bug Something isn't working label Feb 8, 2024
@njnes njnes added this to the NEXTRELEASE milestone Feb 9, 2024
@njnes
Copy link
Contributor

njnes commented Feb 9, 2024

fixed in dec2023, optimizer correctly removed all output of the groupby (no columns are used), execution plan incorrectly generated a output (allways), now the output (single values) is projected with the group result again (given the correct empty result).

@njnes njnes closed this as completed Feb 9, 2024
monetdb-team pushed a commit that referenced this issue Feb 9, 2024
make sure group by results are used
don't rewrite into semijoin when for the join expression the uniqueness cannot be concluded.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working SQL
Projects
None yet
Development

No branches or pull requests

3 participants