MySQL Bugs: #57434: Mysql returns wrong result when column is nullable and has an index

Bug #57434	Mysql returns wrong result when column is nullable and has an index
Submitted:	13 Oct 2010 15:38	Modified:	13 Oct 2010 16:44
Reporter:	Nathan Kontny	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.1.51, 5.5.7	OS:	MacOS
Assigned to:		CPU Architecture:	Any
Tags:	5.1, INDEX, null, nullable

Description:
We were all of a sudden seeing trouble when we upgraded from 5.0 to a version of MySQL 5.1. Though this problem appears in a version of MySQL after version 5.1.37. 

What happens is that given certain properties of the table (indexes, nullable column, etc) a query fails to return the data expected. It's a pretty specific set of properties to repeat the bug. And I can make the bug disappear changing some of those conditions as I'll show in the steps to repeat.

How to repeat:
Here's the table: 

CREATE TABLE `test_table` (
  `some_id` int(11) DEFAULT '0',
  `another_id` int(11) DEFAULT NULL,
  KEY `some_id` (`some_id`),
  KEY `another_id` (`another_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Here's data for that table: 

INSERT INTO `test_table` VALUES (460536956,20),(460536956,19),(460536956,16),(1023153465,15),(460536956,15),(460536956,27),(460536956,24),(460536956,22),(460536956,346856985),(930089099,21),(460536956,21),(460536956,1035990391),(460536956,25),(460536956,23),(1023153465,827722210),(460536956,827722210),(460536956,228466266),(460536956,8),(930089099,31),(460536956,29),(460536956,17),(460536956,12),(460536956,30),(460536956,28),(930089099,14),(460536956,14);

Here's a query that isn't working: 

select * from test_table where some_id = 930089099 AND another_id = 14

Now if you do something like delete one of the indexes on this table, now the query returns one row which you'd expect

some_id | membership_id
930089099	14

If you add the index back, the result disappears. 

With both indexes in place you can also change the "another_id" column to not allow null. And the query works again. 

You can also delete certain rows of data and the query will work again. If you delete the row 

some_id | membership_id
460536956	14

The query works again.

looks like bug #50402 to me ?

Not so sure. It still happens for me with current 5.5.7 (mysql-5.5 from bzr) on Mac OS X:

mysql> CREATE TABLE `test_table` (
    ->   `some_id` int(11) DEFAULT '0',
    ->   `another_id` int(11) DEFAULT NULL,
    ->   KEY `some_id` (`some_id`),
    ->   KEY `another_id` (`another_id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO `test_table` VALUES
    -> (460536956,20),(460536956,19),(460536956,16),(1023153465,15),(460536956,15),(460536956,27),(460536956,24),(460536956,22),(460536956,346856985),(930089099,21),(460536956,21),(460536956,1035990391),(460536956,25),(460536956,23),(1023153465,827722210),(460536956,827722210),(460536956,228466266),(460536956,8),(930089099,31),(460536956,29),(460536956,17),(460536956,12),(460536956,30),(460536956,28),(930089099,14),(460536956,14);
Query OK, 26 rows affected (0.00 sec)
Records: 26  Duplicates: 0  Warnings: 0

mysql> 
mysql> select * from test_table where some_id = 930089099 AND another_id = 14
    -> ;
Empty set (0.00 sec)

mysql> alter table test_table drop key `some_id`;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from test_table where some_id = 930089099 AND another_id = 14;
+-----------+------------+
| some_id   | another_id |
+-----------+------------+
| 930089099 |         14 |
+-----------+------------+
1 row in set (0.00 sec)

mysql> select version();
+----------------+
| version()      |
+----------------+
| 5.5.7-rc-debug |
+----------------+
1 row in set (0.00 sec)

Duplicate of bug #50402. 

Not in mysql-5.5 tree yet, as of:

macbook-pro:mysql-5.5-work openxs$ bzr revision-info
3088 alexander.nozdrin@oracle.com-20101002180831-590ka2tuit9qoxbb