MySQL Bugs: #44855: Incorrect results with multiple indexes on innodb table

Bug #44855	Incorrect results with multiple indexes on innodb table
Submitted:	13 May 2009 21:11	Modified:	25 Jun 2009 7:10
Reporter:	Jess Balint	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	6.0.10/6.0.12	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
I've simplified it as much as possible.

CREATE  TABLE test (
		field_1 int(18) NOT NULL auto_increment,
		field_3 varchar(12) default NULL,
		field_6 int default NULL,
		field_9 datetime default NULL,
		field_10 int(18) NOT NULL default '0',
		PRIMARY KEY  (field_1),
		KEY (field_3),
		KEY (field_6,field_10,field_9))
	DEFAULT CHARSET=latin1 ENGINE = InnoDB;

INSERT INTO test VALUES (15,'FRL',0,NULL,0),(16,'FRL',0,NULL,0);

select field_10, field_3, field_1 from test where field_3 = 'FRL';

Result is:

+----------+---------+---------+
| field_10 | field_3 | field_1 |
+----------+---------+---------+
|        0 | FRL     |      15 |
|        0 | NULL    |      16 |
+----------+---------+---------+

field_3 should be 'FRL' in both rows. In some cases, running it again returns the correct result, in other cases, it returns NULL for field_3 in both rows.

How to repeat:
will attach test scripts, i've found it easiest to reproduce when running the whole script through the client with \. a1.sql

simplest test

Attachment: a.sql (application/octet-stream, text), 458 bytes.

other test

Attachment: a1.sql (application/octet-stream, text), 757 bytes.

other test

Attachment: a3.sql (application/octet-stream, text), 565 bytes.

other test

Attachment: a4.sql (application/octet-stream, text), 458 bytes.

c:\dbs>c:\dbs\6.0\bin\mysql -uroot --port=3600 --prompt="mysql 6.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 6.0.12-alpha-Win X64-log Source distribution

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

mysql 6.0 > create database r5;
Query OK, 1 row affected (0.06 sec)

mysql 6.0 > use r5
Database changed
mysql 6.0 > CREATE  TABLE test (
    ->          field_1 int(18) NOT NULL auto_increment,
    ->          field_3 varchar(12) default NULL,
    ->          field_6 int default NULL,
    ->          field_9 datetime default NULL,
    ->          field_10 int(18) NOT NULL default '0',
    ->          PRIMARY KEY  (field_1),
    ->          KEY (field_3),
    ->          KEY (field_6,field_10,field_9))
    ->  DEFAULT CHARSET=latin1 ENGINE = InnoDB;
Query OK, 0 rows affected (0.30 sec)

mysql 6.0 >
mysql 6.0 > INSERT INTO test VALUES (15,'FRL',0,NULL,0),(16,'FRL',0,NULL,0);
Query OK, 2 rows affected (0.12 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql 6.0 >
mysql 6.0 > select field_10, field_3, field_1 from test where field_3 = 'FRL';
+----------+---------+---------+
| field_10 | field_3 | field_1 |
+----------+---------+---------+
|        0 | FRL     |      15 |
|        0 | NULL    |      16 |
+----------+---------+---------+
2 rows in set (0.00 sec)

mysql 6.0 > alter table test engine MyISAM;
Query OK, 2 rows affected (0.31 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql 6.0 > select field_10, field_3, field_1 from test where field_3 = 'FRL';
+----------+---------+---------+
| field_10 | field_3 | field_1 |
+----------+---------+---------+
|        0 | FRL     |      15 |
|        0 | FRL     |      16 |
+----------+---------+---------+
2 rows in set (0.00 sec)

mysql 6.0 >

Thank you for the bug report. 5.1 isn't affected by this bug:

c:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.35-Win X64 revno: 2853-log Source distribution

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

mysql 5.1 >create database r5;
Query OK, 1 row affected (0.01 sec)

mysql 5.1 >use r5
Database changed
mysql 5.1 >CREATE  TABLE test (
    ->          field_1 int(18) NOT NULL auto_increment,
    ->          field_3 varchar(12) default NULL,
    ->          field_6 int default NULL,
    ->          field_9 datetime default NULL,
    ->          field_10 int(18) NOT NULL default '0',
    ->          PRIMARY KEY  (field_1),
    ->          KEY (field_3),
    ->          KEY (field_6,field_10,field_9))
    ->  DEFAULT CHARSET=latin1 ENGINE = InnoDB;
Query OK, 0 rows affected (0.17 sec)

mysql 5.1 >
mysql 5.1 >INSERT INTO test VALUES (15,'FRL',0,NULL,0),(16,'FRL',0,NULL,0);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql 5.1 >
mysql 5.1 >select field_10, field_3, field_1 from test where field_3 = 'FRL';
+----------+---------+---------+
| field_10 | field_3 | field_1 |
+----------+---------+---------+
|        0 | FRL     |      15 |
|        0 | FRL     |      16 |
+----------+---------+---------+
2 rows in set (0.00 sec)

This appears to be another index condition pushdown bug:

C:\MySQL\MySQL Server 6.0\bin>mysql --user=root --password=mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.10-alpha-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use r5
Database changed
mysql> set engine_condition_pushdown=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select field_10, field_3, field_1 from test where field_3 = 'FRL';
+----------+---------+---------+
| field_10 | field_3 | field_1 |
+----------+---------+---------+
|        0 | FRL     |      15 |
|        0 | FRL     |      16 |
+----------+---------+---------+
2 rows in set (0.00 sec)

mysql> exit
Bye

C:\MySQL\MySQL Server 6.0\bin>mysql --user=root --password=mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.10-alpha-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use r5
Database changed
mysql> select @@engine_condition_pushdown;
+-----------------------------+
| @@engine_condition_pushdown |
+-----------------------------+
|                           1 |
+-----------------------------+
1 row in set (0.00 sec)

mysql> select field_10, field_3, field_1 from test where field_3 = 'FRL';
+----------+---------+---------+
| field_10 | field_3 | field_1 |
+----------+---------+---------+
|        0 | NULL    |      15 |
|        0 | FRL     |      16 |
+----------+---------+---------+
2 rows in set (0.00 sec)

mysql>

There is already an "Umbrella" bug for MRR http://bugs.mysql.com/45029
closing this as a duplicate of that.