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.