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:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.45, 4.1, 5.1 BK OS:Any
Assigned to: Georgi Kodinov CPU Architecture:Any
Triage: D2 (Serious)

[4 Sep 2007 10:33] Jan Lindström
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;
[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.