| 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: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0.38, 4.1, 5.1, 5.0 BK | OS: | Linux |
| Assigned to: | Igor Babaev | CPU Architecture: | Any |
[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.
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)