MySQL Bugs: #29360: SELECT aggregate function GROUP BY enum returns incorrect result

Bug #29360	SELECT aggregate function GROUP BY enum returns incorrect result
Submitted:	26 Jun 2007 13:49	Modified:	27 Jul 2007 4:52
Reporter:	Gleb Shchepa	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1, 5.0, 5.1	OS:	Any
Assigned to:	Gleb Shchepa	CPU Architecture:	Any

Description:
This bug was found during investigation of the bug #29251.

SELECT aggregate function of some column GROUP BY another enum column doesn't distinguish valid empty string values from the special error value.

SELECT without any aggregate function GROUP BY enum column is not affected.

5.x note: only SELECTs from nullable enum columns are affected.

For example, column `c1' has type ENUM ('a', '', 'b'), and table `t1' has both special error values and empty '' values in this column:

  SELECT * FROM t1 GROUP BY c1; # will return N rows,

but

  SELECT c1, SUM(c2) FROM t1 GROUP BY c1; # will return only (N-1) rows.

How to repeat:
mysql> SELECT VERSION();
+-------------------+
| VERSION()         |
+-------------------+
| 5.1.21-beta-debug | 
+-------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE t1 (c1 ENUM('a', '', 'b'), c2 INT DEFAULT 1);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 (c1) VALUES (0), ('a'), (''), ('b');
Query OK, 4 rows affected, 1 warning (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 1

mysql> SELECT c1 + 0, c1 FROM t1 GROUP BY c1;
+--------+------+
| c1 + 0 | c1   |
+--------+------+
|      0 |      | 
|      1 | a    | 
|      2 |      | 
|      3 | b    | 
+--------+------+
4 rows in set (0.00 sec)

mysql> SELECT c1 + 0, c1, SUM(c2) FROM t1 GROUP BY c1;
+--------+------+---------+
| c1 + 0 | c1   | SUM(c2) |
+--------+------+---------+
|      1 | a    |       1 | 
|      0 |      |       2 | 
|      3 | b    |       1 | 
+--------+------+---------+
3 rows in set (0.00 sec)

test case

Attachment: 29360.test (application/octet-stream, text), 280 bytes.

test case result file

Attachment: 29360.result (application/octet-stream, text), 360 bytes.

It seems like that GROUP BY clause does improper type casting to the string in a presence of aggregate functions of another columns - there is no error, if we use GROUP BY c1 + 0:

mysql> SELECT c1 + 0, c1, SUM(c2) FROM t1 GROUP BY c1 + 0;
+--------+------+---------+
| c1 + 0 | c1   | SUM(c2) |
+--------+------+---------+
|      0 |      |       1 | 
|      1 | a    |       1 | 
|      2 |      |       1 | 
|      3 | b    |       1 | 
+--------+------+---------+
4 rows in set (0.00 sec)

But with aggregate function we have an error:

mysql> SELECT c1 + 0, c1, SUM(c2) FROM t1 GROUP BY c1;
+--------+------+---------+
| c1 + 0 | c1   | SUM(c2) |
+--------+------+---------+
|      1 | a    |       1 | 
|      0 |      |       2 | 
|      3 | b    |       1 | 
+--------+------+---------+
3 rows in set (0.00 sec)

Thank you for the bug report.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/30715

ChangeSet@1.2527, 2007-07-11 20:50:13+05:00, gshchepa@gleb.loc +4 -0
  Fixed bug #29360.
  The special `zero' enum value was coerced to the normal
  empty string enum value during a field-to-field copy.
  This bug affected CREATE ... SELECT statements and
  SELECT aggregate GROUP BY enum field statements.
  
  Also this bug made unnecessary warnings during
  the execution of CREATE ... SELECT statements:
  Warning       1265    Data truncated for column...

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/30734

ChangeSet@1.2527, 2007-07-12 00:03:08+05:00, gshchepa@gleb.loc +4 -0
  Fixed bug #29360.
  The special `zero' enum value was coerced to the normal
  empty string enum value during a field-to-field copy.
  This bug affected CREATE ... SELECT statements and
  SELECT aggregate GROUP BY enum field statements.
  
  Also this bug made unnecessary warnings during
  the execution of CREATE ... SELECT statements:
  Warning       1265    Data truncated for column...

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/30736

ChangeSet@1.2528, 2007-07-12 00:55:40+05:00, gshchepa@gleb.loc +1 -0
  field_conv.cc:
    Additional fix for bug #29360.

Pushed into 5.1.21-beta

Pushed into 5.0.48

Noted in 5.0.48, 5.1.21 changelogs.

The special zero ENUM value was coerced to the normal empty string
ENUM value during a column-to-column copy. This affected CREATE ...
SELECT statements and SELECT statements with aggregate functions on
ENUM columns in the GROUP BY clause.