MySQL Bugs: #60231: Select on table with two colums cause empty result.

Bug #60231	Select on table with two colums cause empty result.
Submitted:	24 Feb 2011 12:58	Modified:	1 Mar 2011 10:16
Reporter:	Marcin Grzymski	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	Ver 14.14 Distrib 5.1.49	OS:	Linux (debian squeeze and ubuntu 10.10)
Assigned to:		CPU Architecture:	Any

Description:
Table with two integer columns and separate indexes on each column can cause problems during select with conditions on both columns.
Selecting with condition on one column works ok but multiple columns in same time generate empty result.

Creating index on two columns fixes results from select.
Version 5.0 works fine in that case.

How to repeat:
CREATE TABLE `a_b` (
  `a_id` int(11) DEFAULT NULL,
  `b_id` int(11) DEFAULT NULL,
  KEY `index_a_b_on_a_id` (`a_id`),
  KEY `index_a_b_on_b_id` (`b_id`)
) ENGINE=InnoDB;
INSERT INTO `a_b` VALUES (1,4), (11,4), (11,5);

select * from a_b where a_id = 11 and b_id = 4;
+------+------+
| a_id | b_id |
+------+------+
|   11 |    4 |
+------+------+

INSERT INTO `a_b` VALUES (12,4);

select * from a_b where a_id = 11 and b_id = 4;
Empty set (0.00 sec)

mysql> select * from a_b where a_id = 11;
+------+------+
| a_id | b_id |
+------+------+
|   11 |    4 |
|   11 |    5 |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from a_b where b_id = 4;
+------+------+
| a_id | b_id |
+------+------+
|    1 |    4 |
|   11 |    4 |
|   12 |    4 |
+------+------+
3 rows in set (0.02 sec)

create unique index index_a_b_on_both_id on a_b (a_id, b_id);

select * from a_b where a_id = 11 and b_id = 4;
+------+------+
| a_id | b_id |
+------+------+
|   11 |    4 |
+------+------+

More clear synopsis.

Please, check with recent version, 5.1.55. I do not see this problem even with 5.1.54:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 64
Server version: 5.1.54-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, 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 `a_b` (
    ->   `a_id` int(11) DEFAULT NULL,
    ->   `b_id` int(11) DEFAULT NULL,
    ->   KEY `index_a_b_on_a_id` (`a_id`),
    ->   KEY `index_a_b_on_b_id` (`b_id`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.34 sec)

mysql> INSERT INTO `a_b` VALUES (1,4), (11,4), (11,5);
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from a_b where a_id = 11 and b_id = 4;
+------+------+
| a_id | b_id |
+------+------+
|   11 |    4 |
+------+------+
1 row in set (0.05 sec)

mysql> INSERT INTO `a_b` VALUES (12,4);
Query OK, 1 row affected (0.03 sec)

mysql> select * from a_b where a_id = 11 and b_id = 4;
+------+------+
| a_id | b_id |
+------+------+
|   11 |    4 |
+------+------+
1 row in set (0.00 sec)

mysql> select * from a_b where a_id = 11;
+------+------+
| a_id | b_id |
+------+------+
|   11 |    4 |
|   11 |    5 |
+------+------+
2 rows in set (0.00 sec)

I do not have access to 5.1.54 or 55. I will try to run new version on VM and double check.
If it works with updated versions it is great. I will need to contact debian and ubuntu people to upgrade if possible.

Does anyone know whether there was bug in 5.1.49 version which can cause that strange behaviour. 

many thanks.
Marcin

Set to "Need Feedback" as we are waiting results of your tests.

Hi,

I have managed to install mysqld 5.1.55 in VBox on 32 bit and that problem is resolved. Works as it should.

regards Marcin

So, not repeatable with 5.1.55.