MySQL Bugs: #28729: Blank not found in indexed enum column

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

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)

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>

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.

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.

Pushed into 5.1.20-beta

Pushed into 5.0.44

Noted in 5.0.44, 5.1.20 changelogs.