Bug #29661 EXPLAIN for an ENUM column incorrectly shows 'Impossible WHERE noticed'
Submitted: 9 Jul 2007 21:39 Modified: 27 Jul 2007 16:15
Reporter: Baron Schwartz (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.38, 4.1, 5.1, 5.0 BK OS:Linux
Assigned to: Igor Babaev

[9 Jul 2007 21:39] Baron Schwartz
Description:
EXPLAIN says an impossible WHERE is noticed for the 0th element of the ENUM list, but the SELECT actually retrieves rows.

How to repeat:
mysql> select version();
+------------+
| version()  |
+------------+
| 5.0.38-log | 
+------------+
1 row in set (0.01 sec)

mysql> create table test(a enum('a','b','c','d') not null primary key);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values(0),(1),(2),(3),(4);
Query OK, 5 rows affected, 1 warning (0.03 sec)
Records: 5  Duplicates: 0  Warnings: 1

mysql> select * from test;
+---+
| a |
+---+
|   | 
| a | 
| b | 
| c | 
| d | 
+---+
5 rows in set (0.00 sec)

mysql> explain select * from test where a = 0\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Impossible WHERE noticed after reading const tables
1 row in set (0.00 sec)

mysql> select * from test where a = 0;
+---+
| a |
+---+
|   | 
+---+
1 row in set (0.00 sec)

mysql> explain select * from test where a = ''\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Impossible WHERE noticed after reading const tables
1 row in set (0.00 sec)

mysql> select * from test where a = ''; 
+---+
| a |
+---+
|   | 
+---+
1 row in set (0.00 sec)
[10 Jul 2007 10:33] Sveta Smirnova
Thank you for the report.

Verified as described.
[20 Jul 2007 9:09] Lu Jingdong
mysql> explain select * from test where a = ''\G
It can get the correct result using later version(>=5.1).
--- field.cc.orig        2007-07-20 16:00:03.674663168 +0800
+++ field.cc    2007-07-20 14:45:15.000000000 +0800
@@ -7880,6 +7880,8 @@
        tmp=0;
        set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, WARN_DATA_TRUNCATED, 1);
       }
+      if (!table->in_use->count_cuted_fields)
+        err= 0;
     }
     else
       set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, WARN_DATA_TRUNCATED, 1);

mysql> explain select * from test where a = 0\G
I test it using version 6.0, it cann't get the corrent result, either. For enum type the index value of empty string error value is 0. I modified like this and it can work correctly.
--- sql/field.cc.orig    2007-07-20 13:59:34.000000000 +0800
+++ sql/field.cc        2007-07-20 13:59:53.000000000 +0800
@@ -7899,7 +7899,7 @@
 {
   ASSERT_COLUMN_MARKED_FOR_WRITE;
   int error= 0;
-  if ((ulonglong) nr > typelib->count || nr == 0)
+  if ((ulonglong) nr > typelib->count)
   {
     set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, WARN_DATA_TRUNCATED, 1);
     nr=0;

mysql> explain select * from test where a = 0\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 1
          ref: const
         rows: 1
        Extra: Using index
1 row in set (0.01 sec)
[26 Jul 2007 6:31] Igor Babaev
Pushed into 5.1.21-beta
[26 Jul 2007 6:31] Igor Babaev
Pushed into 5.0.48
[26 Jul 2007 6:54] Igor Babaev
Here's the patch that has been pushed:

# This is a BitKeeper generated diff -Nru style patch.
#
# ChangeSet
#   2007/07/22 18:26:16-07:00 igor@olga.mysql.com 
#   Fixed bug #29661.
#   If a primary key is defined over column c of enum type then 
#   the EXPLAIN command for a look-up query of the form
#     SELECT * FROM t WHERE c=0
#   said that the query was with an impossible where condition though the
#   query correctly returned non-empty result set when the table indeed 
#   contained rows with error empty strings for column c. 
#   
#   This kind of misbehavior was due to a bug in the function 
#   Field_enum::store(longlong,bool) that erroneously returned 1 if
#   the the value to be stored was equal to 0. 
#   Note that the method 
#   Field_enum::store(const char *from,uint length,CHARSET_INFO *cs)
#   correctly returned 0 if a value of the error empty string 
#   was stored. 
# 
# mysql-test/r/type_enum.result
#   2007/07/22 18:26:03-07:00 igor@olga.mysql.com +24 -0
#   Added a test case for bug #29661.
# 
# mysql-test/t/type_enum.test
#   2007/07/22 18:26:03-07:00 igor@olga.mysql.com +24 -0
#   Added a test case for bug #29661.
# 
# sql/field.cc
#   2007/07/22 18:26:03-07:00 igor@olga.mysql.com +5 -2
#   Fixed bug #29661.
#   If a primary key was defined over column c of enum type then 
#   the EXPLAIN command for a look-up query of the form
#     SELECT * FROM t WHERE c=0
#   said that the query was with an impossible where condition though the
#   query correctly returned non-empty result set when the table indeed 
#   contained rows with error empty strings for column c. 
#   
#   This kind of misbehavior was due to a bug in the function 
#   Field_enum::store(longlong,bool) that erroneously returned 1 if
#   the the value to be stored was equal to 0. 
#   Note that the method 
#   Field_enum::store(const char *from,uint length,CHARSET_INFO *cs)
#   correctly returned 0 if a value of the error empty string 
#   was stored. 
# 
diff -Nru a/mysql-test/r/type_enum.result b/mysql-test/r/type_enum.result
--- a/mysql-test/r/type_enum.result	2007-07-22 22:14:11 -07:00
+++ b/mysql-test/r/type_enum.result	2007-07-22 22:14:11 -07:00
@@ -1829,3 +1829,27 @@
 0
 2
 DROP TABLE t1,t2;
+CREATE TABLE t1(a enum('a','b','c','d'));
+INSERT INTO t1 VALUES (4),(1),(0),(3);
+Warnings:
+Warning	1265	Data truncated for column 'a' at row 3
+SELECT a FROM t1;
+a
+d
+a
+
+c
+EXPLAIN SELECT a FROM t1 WHERE a=0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
+SELECT a FROM t1 WHERE a=0;
+a
+
+ALTER TABLE t1 ADD PRIMARY KEY (a);
+EXPLAIN SELECT a FROM t1 WHERE a=0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	1	const	1	Using index
+SELECT a FROM t1 WHERE a=0;
+a
+
+DROP TABLE t1;
diff -Nru a/mysql-test/t/type_enum.test b/mysql-test/t/type_enum.test
--- a/mysql-test/t/type_enum.test	2007-07-22 22:14:11 -07:00
+++ b/mysql-test/t/type_enum.test	2007-07-22 22:14:11 -07:00
@@ -200,3 +200,27 @@
 SELECT c1 + 0 FROM t2;
 
 DROP TABLE t1,t2;
+
+#
+# Bug#29661: Lookup by 0 for a primary index over a enum type  
+#
+
+CREATE TABLE t1(a enum('a','b','c','d'));
+INSERT INTO t1 VALUES (4),(1),(0),(3);
+
+SELECT a FROM t1;
+
+EXPLAIN SELECT a FROM t1 WHERE a=0;
+SELECT a FROM t1 WHERE a=0;
+
+ALTER TABLE t1 ADD PRIMARY KEY (a);
+
+EXPLAIN SELECT a FROM t1 WHERE a=0;
+SELECT a FROM t1 WHERE a=0;
+
+DROP TABLE t1;
+
diff -Nru a/sql/field.cc b/sql/field.cc
--- a/sql/field.cc	2007-07-22 22:14:11 -07:00
+++ b/sql/field.cc	2007-07-22 22:14:11 -07:00
@@ -7640,8 +7640,11 @@
   if ((ulonglong) nr > typelib->count || nr == 0)
   {
     set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, WARN_DATA_TRUNCATED, 1);
-    nr=0;
-    error=1;
+    if (nr != 0 || table->in_use->count_cuted_fields)
+    {
+      nr= 0;
+      error= 1;
+    }
   }
   store_type((ulonglong) (uint) nr);
   return error;
[27 Jul 2007 16:15] Paul Dubois
Noted in 5.0.48, 5.1.21 changelogs.

EXPLAIN produced Impossible where for statements of the form SELECT
... FROM t WHERE c=0, where c was an ENUM column defined as a primary
key.