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

[26 Jun 2007 13:49] Gleb Shchepa
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)
[26 Jun 2007 13:55] Gleb Shchepa
test case

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

[26 Jun 2007 13:56] Gleb Shchepa
test case result file

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

[26 Jun 2007 14:17] Gleb Shchepa
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)
[26 Jun 2007 14:57] Miguel Solorzano
Thank you for the bug report.
[11 Jul 2007 15:42] Bugs System
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...
[11 Jul 2007 18:55] Bugs System
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...
[11 Jul 2007 19:47] Bugs System
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.
[20 Jul 2007 23:45] Bugs System
Pushed into 5.1.21-beta
[20 Jul 2007 23:49] Bugs System
Pushed into 5.0.48
[27 Jul 2007 4:52] Paul Dubois
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.