MySQL Bugs: #29017: Problem with subqueries and GROUP BY

Bug #29017	Problem with subqueries and GROUP BY
Submitted:	11 Jun 2007 11:43	Modified:	26 Jun 2007 8:04
Reporter:	Liesbeth Lyssens	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.41	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
A query which worked fine in version 5.0.24 doesn't seem to work in version 5.0.41
The query looks like this:

SELECT * from t1 INNER JOIN t2 ON t1.a = t2.x WHERE t2.y = (SELECT MAX(test.y) FROM t2 as test WHERE test.x = t1.a) GROUP BY t1.b;

This query doesn't give an error, but the resultset is empty when it shouldn't be. If I remove the subquery or the GROUP BY, the query works fine. If I replace t1.a in the subquery by an actual value, the query works fine too. I've also tested the same query and replaced the GROUP BY by ORDER BY and this also works.

How to repeat:
drop table if exists t1,t2;
create table t1 (a int, b int);
create table t2 (x int, y int);
insert into t1 values (1,11), (2,22);
insert into t2 values (1, 11), (1, 22), (2, 11);

#doesn't work --> no resultset
SELECT * from t1 INNER JOIN t2 ON t1.a = t2.x WHERE t2.y = (SELECT MAX(test.y) FROM t2 as test WHERE test.x = t1.a) GROUP BY t1.b;

#does work
SELECT * from t1 INNER JOIN t2 ON t1.a = t2.x WHERE t2.y = (SELECT MAX(test.y) FROM t2 as test WHERE test.x = 1) GROUP BY t1.b;

SELECT * from t1 INNER JOIN t2 ON t1.a = t2.x WHERE t2.y = (SELECT MAX(test.y) FROM t2 as test WHERE test.x = t1.a) ;

SELECT * from t1 INNER JOIN t2 ON t1.a = t2.x WHERE t2.y = (SELECT MAX(test.y) FROM t2 as test) GROUP BY t1.b;

SELECT * from t1 INNER JOIN t2 ON t1.a = t2.x WHERE t2.y = (SELECT MAX(test.y) FROM t2 as test WHERE test.x = t1.a) ORDER BY t1.b;

Thank you for the report.

I can not repeat described behaviour with current development sources, although bug is repeatabel with 5.0.41.

Please wait next release.

May i ask why this bug is marked non-critical? Imo this makes 5.0.41 a crippled release which breaks apps, which should be reason enough for a new release asap.

FYI This bug is also present in 5.0.42

This bug was non-critical for me, because I wasn't really using 5.0.41 yet, I was just testing it. But Hans Rakers is correct, if I did start to use 5.0.41, my program wouldn't work anymore. So this is a serious bug.

This seems fixed in 5.0.44. Testcase works flawlessly.