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

INNER JOIN gives the results of a CROSS JOIN #6140

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

INNER JOIN gives the results of a CROSS JOIN #6140

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

Comments

@monetdb-team
Copy link

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

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:

Added test for bug #6140

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:

fix for bug #6140, ie the push project up properly detects more ambigious
expression names

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:

Added test for bug #6140

Comment 24848

Date: 2016-12-22 14:07:12 +0100
From: @yzchang

(In reply to MonetDB Mercurial Repository from comment 4)

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:

Added test for bug #6140

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.

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