| Bug #63437 | CHAR & BETWEEN with index returns wrong result after MySQL 5.1. | ||
|---|---|---|---|
| Submitted: | 26 Nov 2011 14:10 | Modified: | 27 Jan 2012 19:30 |
| Reporter: | Meiji KIMURA | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S2 (Serious) |
| Version: | 5.1, 5.5 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | regression | ||
[26 Nov 2011 15:47]
Valeriy Kravchuk
Thank you for the problem report. Verified with 5.1.60:
macbook-pro:5.1 openxs$ bin/mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.60 Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE TABLE T1(
-> F1 CHAR(5) NOT NULL,
-> F2 CHAR(5) NOT NULL,
-> F3 CHAR(5) NOT NULL,
-> PRIMARY KEY(F1),
-> INDEX IDX_F2(F2)
-> ) engine = InnoDB;
Query OK, 0 rows affected (0.09 sec)
mysql> INSERT INTO T1 VALUES('A','A','A');
Query OK, 1 row affected (0.02 sec)
mysql> INSERT INTO T1 VALUES('AA','AA','AA');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO T1 VALUES('AAA','AAA','AAA');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO T1 VALUES('AAAA','AAAA','AAAA');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO T1 VALUES('AAAAA','AAAAA','AAAAA');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM T1 WHERE F1 BETWEEN 'A ' AND 'AAAAA'; -- (a)
+-------+-------+-------+
| F1 | F2 | F3 |
+-------+-------+-------+
| AA | AA | AA |
| AAA | AAA | AAA |
| AAAA | AAAA | AAAA |
| AAAAA | AAAAA | AAAAA |
+-------+-------+-------+
4 rows in set (0.39 sec)
mysql> SELECT * FROM T1 WHERE F2 BETWEEN 'A ' AND 'AAAAA'; -- (b)
+-------+-------+-------+
| F1 | F2 | F3 |
+-------+-------+-------+
| AA | AA | AA |
| AAA | AAA | AAA |
| AAAA | AAAA | AAAA |
| AAAAA | AAAAA | AAAAA |
+-------+-------+-------+
4 rows in set (0.01 sec)
mysql> SELECT * FROM T1 WHERE F3 BETWEEN 'A ' AND 'AAAAA'; -- (c)
+-------+-------+-------+
| F1 | F2 | F3 |
+-------+-------+-------+
| A | A | A |
| AA | AA | AA |
| AAA | AAA | AAA |
| AAAA | AAAA | AAAA |
| AAAAA | AAAAA | AAAAA |
+-------+-------+-------+
5 rows in set (0.00 sec)
Surely when index is not used we have correct results:
mysql> SELECT * FROM T1 IGNORE INDEX(PRIMARY) WHERE F1 BETWEEN 'A ' AND 'AAAAA'; -- (a)
+-------+-------+-------+
| F1 | F2 | F3 |
+-------+-------+-------+
| A | A | A |
| AA | AA | AA |
| AAA | AAA | AAA |
| AAAA | AAAA | AAAA |
| AAAAA | AAAAA | AAAAA |
+-------+-------+-------+
5 rows in set (0.00 sec)
[27 Jan 2012 19:30]
Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.
If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at
http://dev.mysql.com/doc/en/installing-source.html
[27 Jan 2012 19:31]
Jon Stephens
Fixed in 5.1+. Documented in the 5.1.62, 5.5.21, and 5.6.5 changelogs as follows:
A query that used an index on a CHAR column referenced in a
BETWEEN clause could return invalid results.
Closed.
[24 Dec 2012 9:32]
Erlend Dahl
Bug#62940 was marked as a duplicate.
Description: Under the specific contition, CHAR & BETWEEN with index returns wrong result in MySQL 5.1 or later. The same SQL returns right result without any index. How to repeat: (1) Create test table T1 CREATE TABLE T1( F1 CHAR(5) NOT NULL, F2 CHAR(5) NOT NULL, F3 CHAR(5) NOT NULL, PRIMARY KEY(F1), INDEX IDX_F2(F2) ) engine = InnoDB; (2) Insert test data into T1 INSERT INTO T1 VALUES('A','A','A'); INSERT INTO T1 VALUES('AA','AA','AA'); INSERT INTO T1 VALUES('AAA','AAA','AAA'); INSERT INTO T1 VALUES('AAAA','AAAA','AAAA'); INSERT INTO T1 VALUES('AAAAA','AAAAA','AAAAA'); (3) SELECT data using BETWEEN. (a),(b) may use index, (c) don't use index. (you can see with explain) SELECT * FROM T1 WHERE F1 BETWEEN 'A ' AND 'AAAAA'; -- (a) SELECT * FROM T1 WHERE F2 BETWEEN 'A ' AND 'AAAAA'; -- (b) SELECT * FROM T1 WHERE F3 BETWEEN 'A ' AND 'AAAAA'; -- (c) MySQL 5.0.x return right result. mysql [localhost] {msandbox} (test) > SELECT * FROM T1 WHERE F1 BETWEEN 'A ' AND 'AAAAA'; +-------+-------+-------+ | F1 | F2 | F3 | +-------+-------+-------+ | A | A | A | | AA | AA | AA | | AAA | AAA | AAA | | AAAA | AAAA | AAAA | | AAAAA | AAAAA | AAAAA | +-------+-------+-------+ 5 rows in set (0.02 sec) mysql [localhost] {msandbox} (test) > SELECT * FROM T1 WHERE F2 BETWEEN 'A ' AND 'AAAAA'; +-------+-------+-------+ | F1 | F2 | F3 | +-------+-------+-------+ | A | A | A | | AA | AA | AA | | AAA | AAA | AAA | | AAAA | AAAA | AAAA | | AAAAA | AAAAA | AAAAA | +-------+-------+-------+ 5 rows in set (0.00 sec) mysql [localhost] {msandbox} (test) > SELECT * FROM T1 WHERE F3 BETWEEN 'A ' AND 'AAAAA'; +-------+-------+-------+ | F1 | F2 | F3 | +-------+-------+-------+ | A | A | A | | AA | AA | AA | | AAA | AAA | AAA | | AAAA | AAAA | AAAA | | AAAAA | AAAAA | AAAAA | +-------+-------+-------+ 5 rows in set (0.00 sec) But MySQL 5.1 or later, (a),(b) returns wrong result, (c) returns right result. mysql [localhost] {msandbox} (test) > SELECT * FROM T1 WHERE F1 BETWEEN 'A ' AND 'AAAAA'; +-------+-------+-------+ | F1 | F2 | F3 | +-------+-------+-------+ | AA | AA | AA | | AAA | AAA | AAA | | AAAA | AAAA | AAAA | | AAAAA | AAAAA | AAAAA | +-------+-------+-------+ 4 rows in set (0.01 sec) mysql [localhost] {msandbox} (test) > SELECT * FROM T1 WHERE F2 BETWEEN 'A ' AND 'AAAAA'; +-------+-------+-------+ | F1 | F2 | F3 | +-------+-------+-------+ | AA | AA | AA | | AAA | AAA | AAA | | AAAA | AAAA | AAAA | | AAAAA | AAAAA | AAAAA | +-------+-------+-------+ 4 rows in set (0.00 sec) mysql [localhost] {msandbox} (test) > SELECT * FROM T1 WHERE F3 BETWEEN 'A ' AND 'AAAAA'; +-------+-------+-------+ | F1 | F2 | F3 | +-------+-------+-------+ | A | A | A | | AA | AA | AA | | AAA | AAA | AAA | | AAAA | AAAA | AAAA | | AAAAA | AAAAA | AAAAA | +-------+-------+-------+ 5 rows in set (0.00 sec) Suggested fix: [Suggested fix] That query should returns right result with/without indexes. [Workaround] Set this option. set sql_mode='PAD_CHAR_TO_FULL_LENGTH'; mysql [localhost] {msandbox} (test) > set sql_mode='PAD_CHAR_TO_FULL_LENGTH'; Query OK, 0 rows affected (0.00 sec) mysql [localhost] {msandbox} (test) > SELECT * FROM T1 WHERE F1 BETWEEN 'A ' AND 'AAAAA'; -- (a) +-------+-------+-------+ | F1 | F2 | F3 | +-------+-------+-------+ | A | A | A | | AA | AA | AA | | AAA | AAA | AAA | | AAAA | AAAA | AAAA | | AAAAA | AAAAA | AAAAA | +-------+-------+-------+ 5 rows in set (0.00 sec) mysql [localhost] {msandbox} (test) > SELECT * FROM T1 WHERE F2 BETWEEN 'A ' AND 'AAAAA'; -- (b) +-------+-------+-------+ | F1 | F2 | F3 | +-------+-------+-------+ | A | A | A | | AA | AA | AA | | AAA | AAA | AAA | | AAAA | AAAA | AAAA | | AAAAA | AAAAA | AAAAA | +-------+-------+-------+ 5 rows in set (0.00 sec) mysql [localhost] {msandbox} (test) > SELECT * FROM T1 WHERE F3 BETWEEN 'A ' AND 'AAAAA'; -- (c) +-------+-------+-------+ | F1 | F2 | F3 | +-------+-------+-------+ | A | A | A | | AA | AA | AA | | AAA | AAA | AAA | | AAAA | AAAA | AAAA | | AAAAA | AAAAA | AAAAA | +-------+-------+-------+ 5 rows in set (0.00 sec)