Bug #36460 Subqueries eliminating duplicates needed by group by

• Submitted: 2 May 2008 0:14
• Modified: 2 May 2008 15:03
• Reporter: Barry Leslie
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.1
• OS: Any
• CPU Architecture: Any
• Tags: GROUP BY, subquery

[2 May 2008 0:14] Barry Leslie

Description:
It looks like subqueries  are eliminating duplicates and as a result selects using group by are returning incorrect results.

I am using PBXT engine but I do not think this problem is related to the engine.

How to repeat:
create table t1 ( c1 int, c2 int, c3 int, c4 int);

INSERT t1 (c1, c4, c2, c3) VALUES (222, 19, 1994, 2000);
INSERT t1 (c1, c4, c2, c3) VALUES (222, 10, 1994, 3000);
INSERT t1 (c1, c4, c2, c3) VALUES (123, 19, 1994, 1000);

create table t2 ( c1 int, c2 int);

INSERT t2 (c2, c1) VALUES (1, 0);
INSERT t2 (c2, c1) VALUES (10, 19);
INSERT t2 (c2, c1) VALUES (10, 19);
INSERT t2 (c2, c1) VALUES (12, 19);
INSERT t2 (c2, c1) VALUES (13, 19);
INSERT t2 (c2, c1) VALUES (10, 43);

select	t1.c1, t2.c1, t1.c2, sum(t1.c3)
from	t1, t2
where	t1.c4 = t2.c2 
and		t1.c1 = 222
and		t1.c2 = 1994
group by t1.c1, t2.c1, t1.c2;

select	t1.c1, t2.c1, t1.c2, sum(t1.c3)
from	t1,
		(select c1, c2 from t2) t2
where	t1.c4 = t2.c2 
and		t1.c1 = 222
and		t1.c2 = 1994
group by t1.c1, t2.c1, t1.c2;

[2 May 2008 1:06] MySQL Verification Team

Thank you for the bug report. I could not repeat with server from source
tree, which 5.1 exact version are you using? and please print the result
you got. I tested with MyISAM engine. Thanks in advance.

[2 May 2008 15:03] Barry Leslie

Sorry, my mistake.

I am using the embedded server and some where in the creation of the select I had added the 'distinct' clause.