MySQL Bugs: #3505: Wrong results on use of ORDER BY with subqueries

Bug #3505	Wrong results on use of ORDER BY with subqueries
Submitted:	19 Apr 2004 11:00	Modified:	4 May 2004 16:06
Reporter:	Beat Vontobel (Silver Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.1.1-alpha-standard-log	OS:	Linux (Linux i386)
Assigned to:	Oleksandr Byelkin	CPU Architecture:	Any

Description:
I'm sorry to post yet another bug concerning subqueries in 4.1.1-alpha. My last bug #2901 to crash the 
server was unfortunately a duplicate and was fixed in the source two weeks after my post (thanks 
for your great work!). I tried again hard to find possible duplicates for this one. But the only candidate I 
found was #2608 with no test case attached and therefore waiting for feedback since 74 days. Maybe 
this helps you to close that one, too.

The problem seems to be that the use of an ORDER BY clause changes the result of a subquery in an 
unexpected (as I understand it) way. See attached test case. I checked it against the latest binary of 
4.1.1-alpha-standard. (I'm sorry I wasn't able to check against the latest source, it's hopefully already 
fixed there.)

Please note that the attached test case doesn't make much sense as a real world example and the same 
result could easily and better be achieved without subqueries. But my original query was _much_ more 
complicated - using 5 JOINs in the main query and some additional subqueries. It's just for your 
convenience I stripped this down to the minimum needed to show the unexpected behaviour.

How to repeat:
/*                                                                                                                
 * Test case table preparation                                                                                    
 */
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
CREATE TABLE t1 (id INT);
CREATE TABLE t2 (id INT);
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (2);
INSERT INTO t2 VALUES (1);

/*                                                                                                                
 * Test case to produce the expected result                                                                       
 */
SELECT t1.id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c
FROM t1
LEFT JOIN t2
USING (id);

/*                                                                                                                
 * Test case to produce the unexpected result                                                                     
 */
SELECT t1.id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c
FROM t1
LEFT JOIN t2
USING (id)
ORDER BY t1.id;

Test case (pure SQL)

Attachment: CountAndOrderSubquery.sql (application/octet-stream, text), 697 bytes.

ChangeSet 
  1.1802 04/05/02 13:03:49 bell@sanja.is.com.ua +3 -0 
  fixed zero result case for group functions in subquery (Bug #3505) 
  fixed LIMIT 0 for zero rows optimisation

Thank you for good bugreport! 
patch for this bug is pushed in our internal source repository and will be 
available in next release of server.