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 evaluation of conditions in WHERE part of SQL statement #3004

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

Incorrect evaluation of conditions in WHERE part of SQL statement #3004

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

Comments

@monetdb-team
Copy link

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

Date: 2012-02-13 15:10:17 +0100
From: Simon Brodt <<simon.brodt>>
To: SQL devs <>
Version: 11.7.5 (Dec2011) [obsolete]
CC: @njnes

Last updated: 2012-02-29 10:34:25 +0100

Comment 16879

Date: 2012-02-13 15:10:17 +0100
From: Simon Brodt <<simon.brodt>>

User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:10.0.1) Gecko/20100101 Firefox/10.0.1
Build Identifier: MonetDB 5 server v11.7.5 "Dec2011" Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked

OS: Windows 7 64bit Professional SP1

Reproducible: Always

Steps to Reproduce:

CREATE TABLE e (
"a" CLOB,
"r" BIGINT
);
CREATE TABLE s (
"a" CLOB,
"v" BIGINT
);

INSERT INTO e VALUES ('Simon', '1');
INSERT INTO s VALUES ('Simon', '0');

SELECT *
FROM
(
SELECT
"a" AS "ea",
"r" AS "er"
FROM
"e"
) AS "e"
,
(
SELECT
"a" AS "sa",
"v" AS "sv"
FROM
"s"
) AS "s"
WHERE "sv" <= "er"
AND "ea" = "sa"
;

SELECT *
FROM
(
SELECT
"a" AS "ea",
"r" AS "er"
FROM
"e"
) AS "e"
,
(
SELECT
"a" AS "sa",
"v" AS "sv"
FROM
"s"
) AS "s"
WHERE "ea" = "sa"
AND "sv" <= "er"
;

SELECT *
FROM
(
SELECT
"a" AS "ea",
"r" AS "er"
FROM
"e"
) AS "e",
(
SELECT
"a" AS "sa",
"v" AS "sv"
FROM
"s"
) AS "s"
WHERE ("sv" <= "er") = true
AND ("ea" = "sa") = true
;

DROP TABLE "e";
DROP TABLE "s";

Actual Results:

+----+----+----+----+
| ea | er | sa | sv |
+====+====+====+====+
+----+----+----+----+
0 tuples

+-------+------+-------+------+
| ea | er | sa | sv |
+=======+======+=======+======+
| Simon | 1 | Simon | 0 |
+-------+------+-------+------+
1 tuple

+-------+------+-------+------+
| ea | er | sa | sv |
+=======+======+=======+======+
| Simon | 1 | Simon | 0 |
+-------+------+-------+------+
1 tuple

Expected Results:

+-------+------+-------+------+
| ea | er | sa | sv |
+=======+======+=======+======+
| Simon | 1 | Simon | 0 |
+-------+------+-------+------+
1 tuple

+-------+------+-------+------+
| ea | er | sa | sv |
+=======+======+=======+======+
| Simon | 1 | Simon | 0 |
+-------+------+-------+------+
1 tuple

+-------+------+-------+------+
| ea | er | sa | sv |
+=======+======+=======+======+
| Simon | 1 | Simon | 0 |
+-------+------+-------+------+
1 tuple

Comment 16880

Date: 2012-02-13 15:11:37 +0100
From: Simon Brodt <<simon.brodt>>

When reporting the bug the selections of platform: PC and operating system: Windows 7 were not accepted

Comment 16959

Date: 2012-02-20 22:57:38 +0100
From: @njnes

Changeset 3ab6c6414080 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=3ab6c6414080

Changeset description:

fixed bug #3004.
Combinations of complex and normal equijoins were handled incorrectly.
Added a test for Bug #3004 including correct output

Comment 16960

Date: 2012-02-20 22:58:32 +0100
From: @njnes

fixed by handling all equijoins before thetajoins.

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