MySQL Bugs: #61395: Bogus result with SQL_BIG

Bug #61395	Bogus result with SQL_BIG_RESULT and multiple GROUP BY
Submitted:	3 Jun 2011 12:23	Modified:	15 Jun 2011 23:01
Reporter:	Philip Stoev	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.1.58	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
a SQL_BIG_RESULT query returns totally bogus result set that contains values that are nowhere to be seen in the original tab.e

How to repeat:
CREATE TABLE t1 ( f1 int NOT NULL , PRIMARY KEY (f1)) ;
INSERT IGNORE INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20);
SELECT SQL_BIG_RESULT f1 AS field1 , f1 AS field2
FROM t1
GROUP BY field1 , field2 ;

For me everything looks fine with both 5.1.57 and 5.5.13 (both 64 bit Windows servers configured by the .msi installer/config wizard). Optimizer switch(es) are defaults here.  

It should look like (what it does) I think: 

field1  field2
------  ------
    10      10
    11      11
    12      12
    13      13
    14      14
    15      15
    16      16
    17      17
    18      18
    19      19
    20      20

Peter
(not a MySQL person)

seen bug #53534 ?

Verified with current mysql-5.1 (5.1.58) on Mac OS X:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.58-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE t1 ( f1 int NOT NULL , PRIMARY KEY (f1)) ;
Query OK, 0 rows affected (0.10 sec)

mysql> INSERT IGNORE INTO t1 VALUES
    -> (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20);
Query OK, 11 rows affected (0.00 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> SELECT SQL_BIG_RESULT f1 AS field1 , f1 AS field2
    -> FROM t1
    -> GROUP BY field1 , field2 ;
+--------+--------+
| field1 | field2 |
+--------+--------+
|     20 |     20 |
|     20 |     20 |
|     20 |     20 |
|     20 |     20 |
|     20 |     20 |
|     20 |     20 |
|     20 |     20 |
|     20 |     20 |
|     20 |     20 |
|     20 |     20 |
|     20 |     20 |
+--------+--------+
11 rows in set (0.01 sec)

mysql> SELECT f1 AS field1 , f1 AS field2 FROM t1 GROUP BY field1 , field2;
+--------+--------+
| field1 | field2 |
+--------+--------+
|     10 |     10 |
|     11 |     11 |
|     12 |     12 |
|     13 |     13 |
|     14 |     14 |
|     15 |     15 |
|     16 |     16 |
|     17 |     17 |
|     18 |     18 |
|     19 |     19 |
|     20 |     20 |
+--------+--------+
11 rows in set (0.00 sec)

but no problem with current mysql-5.5 (5.5.14).

Issue is fixed in 5.5 and will not be back ported