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 13:00	Modified:	4 May 2004 18:06
Reporter:	Beat Vontobel (Silver Quality Contributor)
Status:	Closed
Category:	Server	Severity:	S1 (Critical)
Version:	4.1.1-alpha-standard-log	OS:	Linux (Linux i386)
Assigned to:	Oleksandr Byelkin	Target Version:

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.