INNER JOIN gives the results of a CROSS JOIN #6140
Labels
Milestone
Comments
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Date: 2016-12-08 15:22:58 +0100
From: @rkoopmanschap
To: SQL devs <>
Version: 11.23.13 (Jun2016-SP2)
CC: @njnes, @yzchang
Last updated: 2017-01-26 14:56:04 +0100
Comment 24766
Date: 2016-12-08 15:22:58 +0100
From: @rkoopmanschap
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/54.0.2840.98 Safari/537.36
Build Identifier:
The results of the query below are incorrect. It joins 2 tables that are generated in subqueries. The first table is:
+---------+------------+
| key_var | value_var1 |
+=========+============+
| 1 | 11 |
| 2 | 27 |
+---------+------------+
The second table is:
+---------+------------+
| key_var | value_var2 |
+=========+============+
| 1 | 630 |
| 2 | 420 |
+---------+------------+
Joining these tables on key_var should result in 2 rows, but the result returns 4. It seems as if they are joined with a cross join, rather than with an inner join.
In the december 2016 release branch the query result still has 4 rows, but the values in rows themselves are now correct. So it is still not working correctly, because only 2 rows should be returned.
Reproducible: Always
Steps to Reproduce:
create schema test;
SET SCHEMA test;
create table "test"."foo"
(
"key_var" int,
"value_var2" int
);
create table "test"."bar"
(
"key_var" int,
"value_var1" int
);
insert into "test"."foo" values (1, 630);
insert into "test"."foo" values (2, 420);
insert into "test"."bar" values (1, 11);
insert into "test"."bar" values (2, 27);
SELECT *
FROM
(
SELECT t1. "key_var" AS "key_var",
SUM(t2. "value_var1") AS "value_var1"
FROM "test"."bar" t2
RIGHT OUTER JOIN
(
SELECT t2. "key_var" AS "key_var"
FROM "test"."foo" t2
GROUP BY t2. "key_var"
) t1
ON t1. "key_var" = t2. "key_var"
GROUP BY t1. "key_var"
) t2
INNER JOIN
(
SELECT t3. "key_var" AS "key_var",
SUM(t3. "value_var2") AS "value_var2"
FROM "test"."foo" t3
GROUP BY t3. "key_var"
) t1
ON t2."key_var" = t1."key_var";
Actual Results:
+---------+------------+---------+------------+
| key_var | value_var1 | key_var | value_var2 |
+=========+============+=========+============+
| 1 | 11 | 1 | 630 |
| 1 | 11 | 2 | 420 |
| 2 | 27 | 1 | 630 |
| 2 | 27 | 2 | 420 |
+---------+------------+---------+------------+
Expected Results:
+---------+------------+---------+------------+
| key_var | value_var2 | key_var | value_var1 |
+=========+============+=========+============+
| 1 | 630 | 1 | 11 |
| 2 | 420 | 2 | 27 |
+---------+------------+---------+------------+
Comment 24780
Date: 2016-12-16 18:22:07 +0100
From: MonetDB Mercurial Repository <>
Changeset a5450ec40d16 made by Richard Koopmanschap richard.koopmanschap@monetdbsolutions.com in the MonetDB repo, refers to this bug.
For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=a5450ec40d16
Changeset description:
Comment 24843
Date: 2016-12-21 18:24:29 +0100
From: MonetDB Mercurial Repository <>
Changeset f5e97fd4b691 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=f5e97fd4b691
Changeset description:
Comment 24844
Date: 2016-12-21 18:32:20 +0100
From: @njnes
fixed checked into the dec2016 branch.
Comment 24847
Date: 2016-12-22 14:06:07 +0100
From: MonetDB Mercurial Repository <>
Changeset e420421d4be7 made by Richard Koopmanschap richard.koopmanschap@monetdbsolutions.com in the MonetDB repo, refers to this bug.
For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=e420421d4be7
Changeset description:
Comment 24848
Date: 2016-12-22 14:07:12 +0100
From: @yzchang
(In reply to MonetDB Mercurial Repository from comment 4)
Back ported the tests from default to Dec2016
Comment 24912
Date: 2017-01-26 14:56:04 +0100
From: @kutsurak
Fixed in version Dec2016-SP1.
The text was updated successfully, but these errors were encountered: