| Bug #30788 | Inconsistent retrieval of char/varchar | ||
|---|---|---|---|
| Submitted: | 4 Sep 2007 10:33 | Modified: | 23 Jan 2008 18:25 |
| Reporter: | Jan Lindström | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 5.0.45, 4.1, 5.1 BK | OS: | Any |
| Assigned to: | Georgi Kodinov | CPU Architecture: | Any |
[4 Sep 2007 10:54]
Sveta Smirnova
Thank you for the report. Verified as described. All versions are affected except 5.2
[12 Oct 2007 14:24]
Konstantin Osipov
Can't repeat. mysql> CREATE INDEX I1 ON T1 (S1); Query OK, 7 rows affected (0.00 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> CREATE INDEX I2 ON T1 (S2); Query OK, 7 rows affected (0.00 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> SELECT S1,S2 FROM T1; +-------+---------+ | S1 | S2 | +-------+---------+ | a | a | | aa | aa | | aaa | aaa | | aaaa | aaaa | | aaaaa | aaaaa | | aaaaa | aaaaaa | | aaaaa | aaaaaaa | +-------+---------+ 7 rows in set (0.00 sec) mysql> SELECT S1,S2 FROM T1 WHERE S1 IN (SELECT S2 FROM T1); +-------+---------+ | S1 | S2 | +-------+---------+ | a | a | | aa | aa | | aaa | aaa | | aaaa | aaaa | | aaaaa | aaaaa | | aaaaa | aaaaaa | | aaaaa | aaaaaaa | +-------+---------+ 7 rows in set (0.00 sec) mysql> SELECT S1,S2 FROM T1 WHERE S2 IN (SELECT S1 FROM T1); +-------+---------+ | S1 | S2 | +-------+---------+ | a | a | | aa | aa | | aaa | aaa | | aaaa | aaaa | | aaaaa | aaaaa | | aaaaa | aaaaaa | | aaaaa | aaaaaaa | +-------+---------+ 7 rows in set (0.00 sec) mysql> DROP TABLE IF EXISTS T1; Query OK, 0 rows affected (0.01 sec) mysql> select version();p +---------------------------+ | version() | +---------------------------+ | 5.0.50-valgrind-max-debug | +---------------------------+ 1 row in set (0.00 sec) Likely a duplicate of Bug#31001
[12 Oct 2007 14:26]
Konstantin Osipov
Sorry, was wrong. The bug is actually repeatable.
[12 Oct 2007 14:26]
Konstantin Osipov
Bad data, P2
[26 Oct 2007 9:41]
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/36419 ChangeSet@1.2549, 2007-10-26 12:40:58+03:00, gkodinov@magare.gmz +5 -0 Bug #30788: Inconsistent retrieval of char/varchar When using indexed search the server constructs a key image from the right arguments of the sargable conditions. String truncations during the creation of that image were not checked for and reported. Also error checking when using index for a subquery execution was not considering such errors as fatal and was continuing to search with the truncated string. Fixed the error processing to include and handle the truncation errors.
[13 Nov 2007 17:24]
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/37686 ChangeSet@1.2549, 2007-11-13 19:23:27+02:00, gkodinov@magare.gmz +6 -0 Bug #30788: Inconsistent retrieval of char/varchar Index lookup does not always guarantee that we can simply remove the relevant conditions from the WHERE clause. Reasons can be e.g. conversion errors, partial indexes etc. The optimizer was removing these parts of the WHERE condition without any further checking. This leads to "false positives" when using indexes. Fixed by checking the index reference conditions (using WHERE) when using indexes with sub-queries.
[16 Nov 2007 12:28]
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/37940 ChangeSet@1.2549, 2007-11-16 14:28:07+02:00, gkodinov@magare.gmz +6 -0 Bug #30788: Inconsistent retrieval of char/varchar Index lookup does not always guarantee that we can simply remove the relevant conditions from the WHERE clause. Reasons can be e.g. conversion errors, partial indexes etc. The optimizer was removing these parts of the WHERE condition without any further checking. This leads to "false positives" when using indexes. Fixed by checking the index reference conditions (using WHERE) when using indexes with sub-queries.
[19 Nov 2007 17:53]
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/38088 ChangeSet@1.2549, 2007-11-19 19:53:03+02:00, gkodinov@magare.gmz +6 -0 Bug #30788: Inconsistent retrieval of char/varchar Index lookup does not always guarantee that we can simply remove the relevant conditions from the WHERE clause. Reasons can be e.g. conversion errors, partial indexes etc. The optimizer was removing these parts of the WHERE condition without any further checking. This leads to "false positives" when using indexes. Fixed by checking the index reference conditions (using WHERE) when using indexes with sub-queries.
[21 Nov 2007 9:40]
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/38195 ChangeSet@1.2549, 2007-11-21 11:40:05+02:00, gkodinov@magare.gmz +5 -0 Bug #30788: Inconsistent retrieval of char/varchar Index lookup does not always guarantee that we can simply remove the relevant conditions from the WHERE clause. Reasons can be e.g. conversion errors, partial indexes etc. The optimizer was removing these parts of the WHERE condition without any further checking. This leads to "false positives" when using indexes. Fixed by checking the index reference conditions (using WHERE) when using indexes with sub-queries.
[14 Dec 2007 8:15]
Bugs System
Pushed into 5.0.54
[14 Dec 2007 8:19]
Bugs System
Pushed into 5.1.23-rc
[14 Dec 2007 8:22]
Bugs System
Pushed into 6.0.5-alpha
[23 Jan 2008 18:25]
Paul DuBois
Noted in 5.0.54, 5.1.23, 6.0.5 changelogs. The optimizer incorrectly optimized conditions out of the WHERE clause in some queries involving subqueries and indexed columns.
Description: I create MyISAM table with char and varchar field and insert some data. When selecting I get different results if table contain indexes. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 5.0.45-debug Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> DROP TABLE IF EXISTS T1; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> CREATE TABLE T1 (S1 char(5), S2 varchar(10)) ENGINE=MYISAM; Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO T1 VALUES ('a', 'a'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO T1 VALUES ('aa', 'aa'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO T1 VALUES ('aaa', 'aaa'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO T1 VALUES ('aaaa', 'aaaa'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO T1 VALUES ('aaaaa', 'aaaaa'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO T1 VALUES ('aaaaa', 'aaaaaa'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO T1 VALUES ('aaaaa', 'aaaaaaa'); Query OK, 1 row affected (0.00 sec) mysql> SELECT S1,S2 FROM T1; +-------+---------+ | S1 | S2 | +-------+---------+ | a | a | | aa | aa | | aaa | aaa | | aaaa | aaaa | | aaaaa | aaaaa | | aaaaa | aaaaaa | | aaaaa | aaaaaaa | +-------+---------+ 7 rows in set (0.00 sec) mysql> SELECT S1,S2 FROM T1 WHERE S1 IN (SELECT S2 FROM T1); +-------+---------+ | S1 | S2 | +-------+---------+ | a | a | | aa | aa | | aaa | aaa | | aaaa | aaaa | | aaaaa | aaaaa | | aaaaa | aaaaaa | | aaaaa | aaaaaaa | +-------+---------+ 7 rows in set (0.00 sec) mysql> SELECT S1,S2 FROM T1 WHERE S2 IN (SELECT S1 FROM T1); +-------+-------+ | S1 | S2 | +-------+-------+ | a | a | | aa | aa | | aaa | aaa | | aaaa | aaaa | | aaaaa | aaaaa | +-------+-------+ 5 rows in set (0.00 sec) mysql> CREATE INDEX I1 ON T1 (S1); Query OK, 7 rows affected (0.00 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> CREATE INDEX I2 ON T1 (S2); Query OK, 7 rows affected (0.00 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> SELECT S1,S2 FROM T1; +-------+---------+ | S1 | S2 | +-------+---------+ | a | a | | aa | aa | | aaa | aaa | | aaaa | aaaa | | aaaaa | aaaaa | | aaaaa | aaaaaa | | aaaaa | aaaaaaa | +-------+---------+ 7 rows in set (0.00 sec) mysql> SELECT S1,S2 FROM T1 WHERE S1 IN (SELECT S2 FROM T1); +-------+---------+ | S1 | S2 | +-------+---------+ | a | a | | aa | aa | | aaa | aaa | | aaaa | aaaa | | aaaaa | aaaaa | | aaaaa | aaaaaa | | aaaaa | aaaaaaa | +-------+---------+ 7 rows in set (0.00 sec) mysql> SELECT S1,S2 FROM T1 WHERE S2 IN (SELECT S1 FROM T1); +-------+---------+ | S1 | S2 | +-------+---------+ | a | a | | aa | aa | | aaa | aaa | | aaaa | aaaa | | aaaaa | aaaaa | | aaaaa | aaaaaa | | aaaaa | aaaaaaa | +-------+---------+ 7 rows in set (0.00 sec) mysql> DROP TABLE IF EXISTS T1; Query OK, 0 rows affected (0.00 sec) How to repeat: DROP TABLE IF EXISTS T1; CREATE TABLE T1 (S1 char(5), S2 varchar(10)) ENGINE=MYISAM; INSERT INTO T1 VALUES ('a', 'a'); INSERT INTO T1 VALUES ('aa', 'aa'); INSERT INTO T1 VALUES ('aaa', 'aaa'); INSERT INTO T1 VALUES ('aaaa', 'aaaa'); INSERT INTO T1 VALUES ('aaaaa', 'aaaaa'); INSERT INTO T1 VALUES ('aaaaa', 'aaaaaa'); INSERT INTO T1 VALUES ('aaaaa', 'aaaaaaa'); SELECT S1,S2 FROM T1; SELECT S1,S2 FROM T1 WHERE S1 IN (SELECT S2 FROM T1); SELECT S1,S2 FROM T1 WHERE S2 IN (SELECT S1 FROM T1); CREATE INDEX I1 ON T1 (S1); CREATE INDEX I2 ON T1 (S2); SELECT S1,S2 FROM T1; SELECT S1,S2 FROM T1 WHERE S1 IN (SELECT S2 FROM T1); SELECT S1,S2 FROM T1 WHERE S2 IN (SELECT S1 FROM T1); DROP TABLE IF EXISTS T1;