MySQL Bugs: #64644: CREATE INDEX changes result

Bug #64644	CREATE INDEX changes result
Submitted:	14 Mar 2012 8:17	Modified:	14 Mar 2012 10:43
Reporter:	Marcos Klassen	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.5.15-log	OS:	Windows (Win32)
Assigned to:		CPU Architecture:	Any
Tags:	create index

Description:
After inserting an index, the result of a statement can be different
After development and testing of an database based application, database administrators improve indexes for query optimisation. Some results changes but nobody notice that, because content-related tests are finished.

How to repeat:
CREATE TABLE testRCAAI (ID int(10) unsigned NOT NULL AUTO_INCREMENT,
SearchVal smallint(3) NOT NULL, Result char(1) NOT NULL, PRIMARY  KEY (ID)) 
ENGINE  =  MyISAM;
INSERT INTO testRCAAI (SearchVal, Result) VALUES (100, 'A'), (150, 'B'), (200, 'C');
SELECT Result FROM testRCAAI WHERE SearchVal >= 149.5 LIMIT 1; 
+--------+
| Result |
+--------+
| B      |
+--------+
ALTER TABLE testRCAAI ADD INDEX (SearchVal);
SELECT Result FROM testRCAAI WHERE SearchVal >= 149.5 LIMIT 1; 
+--------+
| Result |
+--------+
| C      |
+--------+

Reproducible for me with MySQL 5.1.61 and 5.5.20 - but not with MySQL 5.0.90 (where "B" is returned in both cases)

Peter
(not a MySQL person)

Looks like this is fixed in 5.5.21. Please, check:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3312 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.5.21-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE testRCAAI (ID int(10) unsigned NOT NULL AUTO_INCREMENT,
    -> SearchVal smallint(3) NOT NULL, Result char(1) NOT NULL, PRIMARY  KEY (ID
))
    -> ENGINE  =  MyISAM;
Query OK, 0 rows affected (0.13 sec)

mysql> INSERT INTO testRCAAI (SearchVal, Result) VALUES (100, 'A'), (150, 'B'),
(200, 'C');
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT Result FROM testRCAAI WHERE SearchVal >= 149.5 LIMIT 1;
+--------+
| Result |
+--------+
| B      |
+--------+
1 row in set (0.00 sec)

mysql> ALTER TABLE testRCAAI ADD INDEX (SearchVal);
Query OK, 3 rows affected (0.19 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT Result FROM testRCAAI WHERE SearchVal >= 149.5 LIMIT 1;
+--------+
| Result |
+--------+
| B      |
+--------+
1 row in set (0.00 sec)

But it is not fixed in 5.6.61 (however maybe it is in current active 5.5 code trunk).

Peter,

This is what I see in more or less current mysql-5.1 tree:

[openxs@chief 5.1]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.63-debug 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 testRCAAI (ID int(10) unsigned NOT NULL AUTO_INCREMENT,
    -> SearchVal smallint(3) NOT NULL, Result char(1) NOT NULL, PRIMARY  KEY (ID))
    -> ENGINE  =  MyISAM;
SELECT Result FROM testRCAAI WHERE SearchVal >= 149.5 LIMIT 1; Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO testRCAAI (SearchVal, Result) VALUES (100, 'A'), (150, 'B'), (200, 'C');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT Result FROM testRCAAI WHERE SearchVal >= 149.5 LIMIT 1;
+--------+
| Result |
+--------+
| B      |
+--------+
1 row in set (0.01 sec)

mysql> ALTER TABLE testRCAAI ADD INDEX (SearchVal);
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT Result FROM testRCAAI WHERE SearchVal >= 149.5 LIMIT 1;
+--------+
| Result |
+--------+
| B      |
+--------+
1 row in set (0.00 sec)

So, the bug is fixed in 5.5.21 and current 5.1.x code.