MySQL Bugs: #5814: Sub-Select with Group by would return a wrong result

Bug #5814	Sub-Select with Group by would return a wrong result
Submitted:	29 Sep 2004 21:52	Modified:	4 Oct 2004 13:26
Reporter:	Dorn Bhechsonggram	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.1.5-gamma-standard	OS:	Linux (Fedora Core 1)
Assigned to:	Oleksandr Byelkin	CPU Architecture:	Any

Description:
When issues a query contains sub-select statement with group by, mysql returns a wrong result when the group by has null values.  

Please take a look in how to repeat.  The expect result would be as follows:

+----+--------+-----+----------+
| r1 | r2     | r3  | COUNT(*) |
+----+--------+-----+----------+
| 0  | 67890  | NULL|        1 |
| 1  | 888888 | XYZ |        2 |
| 2  | 12345  | ABC |        3 |
| 25 | 777777 | NULL|        1 |
+----+--------+-----+----------+ 

How to repeat:
CREATE TABLE table1 (
  c11 char(3) NOT NULL,
  c12 varchar(16) NOT NULL
);

INSERT INTO table1 VALUES ('2', '12345');
INSERT INTO table1 VALUES ('0', '67890');
INSERT INTO table1 VALUES ('1', '888888');
INSERT INTO table1 VALUES ('25', '777777');
INSERT INTO table1 VALUES ('2', '12345');
INSERT INTO table1 VALUES ('2', '12345');
INSERT INTO table1 VALUES ('1', '888888');

CREATE TABLE table2 (
  c21 varchar(20) NOT NULL,
  c22 varchar(50) NOT NULL
);

INSERT INTO table2 VALUES ('12345', 'ABC');
INSERT INTO table2 VALUES ('16789', 'BCD');
INSERT INTO table2 VALUES ('888888', 'XYZ');

SELECT c11 AS r1,
       c12 AS r2,
       (SELECT c22 FROM table2 
        WHERE c12 = c21
       ) AS r3,
       COUNT(*)
FROM table1
GROUP BY 1,2,3 
ORDER BY 1,2,3;

+----+--------+-----+----------+
| r1 | r2     | r3  | COUNT(*) |
+----+--------+-----+----------+
| 0  | 67890  | ABC |        1 |
| 1  | 888888 | XYZ |        2 |
| 2  | 12345  | ABC |        3 |
| 25 | 777777 | XYZ |        1 |
+----+--------+-----+----------+

I can't repeat it on the latest 4.1.6-debug-log, but I was able reproduce it on latest 5.0 on Windows and Linux boxes.
Table types: MyISAM, InnoDB.

In addition, if I issues the query statement:
select c11,c12 from table1 group by 1;
I would get the following error in informix:
294: The column (c12) must be in the GROUP BY list.

However, mysql does not detect the error in group by list; however, it automatically does the group by for the rest of the columns.

I am not sure whether this one is also a bug; or, mysql intends to perform that.

FYI

Fixed.

Regarding your last comment, this is not a bug.
According MySQL documentation:

"MySQL extends the use of GROUP BY to allow you to select fields that are not mentioned in the GROUP BY clause."

http://dev.mysql.com/doc/mysql/en/SELECT.html