Bug #28729 Blank not found in indexed enum column
Submitted: 28 May 2007 19:05 Modified: 18 Jun 2007 19:23
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.1.20-beta-debug/5.0 OS:Linux (SUSE 10 64-bit)
Assigned to: Evgeny Potemkin CPU Architecture:Any

[28 May 2007 19:05] Peter Gulutzan
Description:
I create a table with an ENUM column.
I insert ''. I get warnings, but something goes in.
I select with "WHERE enum_column=''".
I get what I inserted.
I create an index on the column.
I select with "WHERE enum_column=''".
I don't get what I inserted.

Indexed and unindexed searches should return the same results.

How to repeat:
mysql> create table td (s1 enum('a','b'));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into td values (''),(''),('');
Query OK, 3 rows affected, 3 warnings (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 3

mysql> show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1265 | Data truncated for column 's1' at row 1 |
| Warning | 1265 | Data truncated for column 's1' at row 2 |
| Warning | 1265 | Data truncated for column 's1' at row 3 |
+---------+------+-----------------------------------------+
3 rows in set (0.00 sec)

mysql> select * from td where s1 = '';
+------+
| s1   |
+------+
|      |
|      |
|      |
+------+
3 rows in set (0.00 sec)

mysql> create index id on td (s1);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from td where s1 = '';
Empty set (0.00 sec)
[28 May 2007 19:25] Miguel Solorzano
Thank you for the bug report. Verified as described. That bug not
happens on 4.1 server.

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

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table td (s1 enum('a','b'));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into td values (''),(''),('');
Query OK, 3 rows affected, 3 warnings (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 3

mysql> show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1265 | Data truncated for column 's1' at row 1 | 
| Warning | 1265 | Data truncated for column 's1' at row 2 | 
| Warning | 1265 | Data truncated for column 's1' at row 3 | 
+---------+------+-----------------------------------------+
3 rows in set (0.00 sec)

mysql> select * from td where s1 = '';
+------+
| s1   |
+------+
|      | 
|      | 
|      | 
+------+
3 rows in set (0.02 sec)

mysql> create index id on td (s1);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from td where s1 = '';
Empty set (0.00 sec)

-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.23-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table td (s1 enum('a','b'));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into td values (''),(''),('');
Query OK, 3 rows affected, 3 warnings (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 3

mysql> show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1265 | Data truncated for column 's1' at row 1 |
| Warning | 1265 | Data truncated for column 's1' at row 2 |
| Warning | 1265 | Data truncated for column 's1' at row 3 |
+---------+------+-----------------------------------------+
3 rows in set (0.00 sec)

mysql> select * from td where s1 = '';
+------+
| s1   |
+------+
|      |
|      |
|      |
+------+
3 rows in set (0.00 sec)

mysql> create index id on td (s1);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from td where s1 = '';
+------+
| s1   |
+------+
|      |
|      |
|      |
+------+
3 rows in set (0.00 sec)

mysql>
[30 May 2007 18:03] 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/27711

ChangeSet@1.2511, 2007-05-30 22:00:11+04:00, evgen@moonbone.local +3 -0
  Bug#28729: Field_enum wrongly reported an error while storing an empty string.
  
  ENUM fields internally store their values as integers and may use integer
  values as indexes to their values. Invalid values are mapped to zero value.
  When storing an empty string the ENUM field fails to find an appropriate value
  and tried to convert the provided string to integer. The conversion is also
  failed and error is returned even if the thd->count_cuted_fields is set to
  CHECK_FIELD_IGNORE. This makes the range optimizer to wrongly decide that an
  impossible range is present.
  
  Now the Field_enum::store() returns error while storing an empty string only
  if the thd->count_cuted_fields isn't set to CHECK_FIELD_IGNORE.
[30 May 2007 19:31] 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/27725

ChangeSet@1.2511, 2007-05-30 23:28:14+04:00, evgen@moonbone.local +3 -0
  Bug#28729: Field_enum wrongly reported an error while storing an empty string.
  
  ENUM fields internally store their values as integers and may use integer
  values as indexes to their values. Invalid values are mapped to zero value.
  When storing an empty string the ENUM field fails to find an appropriate value
  and tries to convert the provided string to integer. The conversion also
  fails and error is returned even if the thd->count_cuted_fields is set to
  CHECK_FIELD_IGNORE. This makes the range optimizer wrongly decide that an
  impossible range is present.
  
  Now the Field_enum::store() returns error while storing an empty string only
  if the thd->count_cuted_fields isn't set to CHECK_FIELD_IGNORE.
[4 Jun 2007 21:21] Bugs System
Pushed into 5.1.20-beta
[4 Jun 2007 21:23] Bugs System
Pushed into 5.0.44
[18 Jun 2007 19:23] Paul Dubois
Noted in 5.0.44, 5.1.20 changelogs.