Description:
MySQL doesn't return expected results when using the ENUM type in the following manner (expect to see 2 rows, instead get an empty results set). The manual says that in aggregate context, casting happens automatically. Ignoring that, one would think it would be sorted lexicographic-ally with the ENUM string value. Instead it appears they are coerced to null?
mysql> create table foo (pk int primary key not null, a varchar(255) not null, b int not null, c enum ('a', 'b', 'c') not null, unique key (a,b) );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into foo values (1, 'a', 1, 'a'), (2, 'a', '2', 'b'), (3, 'b', 1, 'b'), (4, 'b', 2, 'a');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from foo;
+----+---+---+---+
| pk | a | b | c |
+----+---+---+---+
| 1 | a | 1 | a |
| 2 | a | 2 | b |
| 3 | b | 1 | b |
| 4 | b | 2 | a |
+----+---+---+---+
4 rows in set (0.00 sec)
mysql> select a, b, c from foo group by a having max(c);
Empty set (0.00 sec)
mysql> select a, b, c from foo group by a having max(0 + c);
+---+---+---+
| a | b | c |
+---+---+---+
| a | 1 | a |
| b | 1 | b |
+---+---+---+
2 rows in set (0.00 sec)
How to repeat:
mysql> create table foo (pk int primary key not null, a varchar(255) not null, b int not null, c enum ('a', 'b', 'c') not null, unique key (a,b) );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into foo values (1, 'a', 1, 'a'), (2, 'a', '2', 'b'), (3, 'b', 1, 'b'), (4, 'b', 2, 'a');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from foo;
+----+---+---+---+
| pk | a | b | c |
+----+---+---+---+
| 1 | a | 1 | a |
| 2 | a | 2 | b |
| 3 | b | 1 | b |
| 4 | b | 2 | a |
+----+---+---+---+
4 rows in set (0.00 sec)
mysql> select a, b, c from foo group by a having max(c);
Empty set (0.00 sec)
mysql> select a, b, c from foo group by a having max(0 + c);
+---+---+---+
| a | b | c |
+---+---+---+
| a | 1 | a |
| b | 1 | b |
+---+---+---+
2 rows in set (0.00 sec)
Description: MySQL doesn't return expected results when using the ENUM type in the following manner (expect to see 2 rows, instead get an empty results set). The manual says that in aggregate context, casting happens automatically. Ignoring that, one would think it would be sorted lexicographic-ally with the ENUM string value. Instead it appears they are coerced to null? mysql> create table foo (pk int primary key not null, a varchar(255) not null, b int not null, c enum ('a', 'b', 'c') not null, unique key (a,b) ); Query OK, 0 rows affected (0.01 sec) mysql> insert into foo values (1, 'a', 1, 'a'), (2, 'a', '2', 'b'), (3, 'b', 1, 'b'), (4, 'b', 2, 'a'); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from foo; +----+---+---+---+ | pk | a | b | c | +----+---+---+---+ | 1 | a | 1 | a | | 2 | a | 2 | b | | 3 | b | 1 | b | | 4 | b | 2 | a | +----+---+---+---+ 4 rows in set (0.00 sec) mysql> select a, b, c from foo group by a having max(c); Empty set (0.00 sec) mysql> select a, b, c from foo group by a having max(0 + c); +---+---+---+ | a | b | c | +---+---+---+ | a | 1 | a | | b | 1 | b | +---+---+---+ 2 rows in set (0.00 sec) How to repeat: mysql> create table foo (pk int primary key not null, a varchar(255) not null, b int not null, c enum ('a', 'b', 'c') not null, unique key (a,b) ); Query OK, 0 rows affected (0.01 sec) mysql> insert into foo values (1, 'a', 1, 'a'), (2, 'a', '2', 'b'), (3, 'b', 1, 'b'), (4, 'b', 2, 'a'); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from foo; +----+---+---+---+ | pk | a | b | c | +----+---+---+---+ | 1 | a | 1 | a | | 2 | a | 2 | b | | 3 | b | 1 | b | | 4 | b | 2 | a | +----+---+---+---+ 4 rows in set (0.00 sec) mysql> select a, b, c from foo group by a having max(c); Empty set (0.00 sec) mysql> select a, b, c from foo group by a having max(0 + c); +---+---+---+ | a | b | c | +---+---+---+ | a | 1 | a | | b | 1 | b | +---+---+---+ 2 rows in set (0.00 sec)