MySQL Bugs: #46892: Fedarated table - empty result set with range scan on combined index

Bug #46892	Fedarated table - empty result set with range scan on combined index
Submitted:	24 Aug 2009 12:46	Modified:	24 Aug 2009 13:44
Reporter:	Rene' Cannao'	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Federated storage engine	Severity:	S3 (Non-critical)
Version:	5.0, 5.1, 5.4	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	federated

Description:
If a table has a combined index on two fields (date, int) , WHERE clauses that need a range scan on the first field and exact match on the second field will return an empty resultset.

Forcing the use of an index (int, date) does not return an empty resultset.

How to repeat:
mysql> CREATE TABLE table_innodb (str1 VARCHAR(30) NOT NULL, dt1 date NOT NULL, type INT NOT NULL, INDEX(type, dt1), INDEX(dt1, type, str1)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.06 sec)

mysql> 
mysql> INSERT INTO table_innodb VALUES ('',CURDATE()-INTERVAL 1 DAY,20);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO table_innodb VALUES ('',CURDATE()-INTERVAL 1 DAY,30);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO table_innodb VALUES ('',CURDATE()-INTERVAL 1 DAY,40);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO table_innodb VALUES ('',CURDATE()-INTERVAL 1 DAY,50);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO table_innodb VALUES ('',CURDATE()-INTERVAL 1 DAY,60);
Query OK, 1 row affected (0.01 sec)

mysql> CREATE TABLE table_federated (str1 VARCHAR(30) NOT NULL, dt1 date NOT NULL, type INT NOT NULL, INDEX(type, dt1), INDEX(dt1, type, str1)) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://root@localhost:3306/test/table_innodb';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM table_innodb tbl WHERE type IN (30,40,50) AND dt1<NOW();
+------+------------+------+
| str1 | dt1        | type |
+------+------------+------+
|      | 2009-08-23 |   30 | 
|      | 2009-08-23 |   40 | 
|      | 2009-08-23 |   50 | 
+------+------------+------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM table_federated tbl WHERE type IN (30,40,50) AND dt1<NOW();
Empty set (0.01 sec)

mysql> EXPLAIN SELECT * FROM table_innodb tbl WHERE type IN (30,40,50) AND dt1<NOW();
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | tbl   | index | type,dt1      | dt1  | 39      | NULL |    5 | Using where; Using index | 
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set (0.01 sec)

mysql> EXPLAIN SELECT * FROM table_federated tbl WHERE type IN (30,40,50) AND dt1<NOW();
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tbl   | range | type,dt1      | dt1  | 7       | NULL |    2 | Using where | 
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM table_innodb tbl WHERE type IN (30,40,50) AND dt1='2009-08-23';
+------+------------+------+
| str1 | dt1        | type |
+------+------------+------+
|      | 2009-08-23 |   30 | 
|      | 2009-08-23 |   40 | 
|      | 2009-08-23 |   50 | 
+------+------------+------+
3 rows in set (0.00 sec)

mysql> explain SELECT * FROM table_innodb tbl WHERE type IN (30,40,50) AND dt1='2009-08-23';
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | tbl   | range | type,dt1      | dt1  | 7       | NULL |    3 | Using where; Using index | 
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM table_innodb tbl WHERE type IN (30,40,50) AND dt1=CURDATE()-INTERVAL 1 DAY;
+------+------------+------+
| str1 | dt1        | type |
+------+------------+------+
|      | 2009-08-23 |   30 | 
|      | 2009-08-23 |   40 | 
|      | 2009-08-23 |   50 | 
+------+------------+------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM table_federated tbl WHERE type IN (30,40,50) AND dt1=CURDATE()-INTERVAL 1 DAY;
+------+------------+------+
| str1 | dt1        | type |
+------+------------+------+
|      | 2009-08-23 |   30 | 
|      | 2009-08-23 |   40 | 
|      | 2009-08-23 |   50 | 
+------+------------+------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM table_federated tbl FORCE INDEX(dt1) WHERE type IN (30) AND dt1<NOW();
Empty set (0.01 sec)

mysql> SELECT * FROM table_federated tbl WHERE type IN (30) AND dt1<NOW();
+------+------------+------+
| str1 | dt1        | type |
+------+------------+------+
|      | 2009-08-23 |   30 | 
+------+------------+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM table_federated tbl WHERE type IN (30,40,50) AND dt1=CURDATE()-INTERVAL 1 DAY;
+------+------------+------+
| str1 | dt1        | type |
+------+------------+------+
|      | 2009-08-23 |   30 | 
|      | 2009-08-23 |   40 | 
|      | 2009-08-23 |   50 | 
+------+------------+------+
3 rows in set (0.01 sec)

mysql> SELECT * FROM table_federated tbl WHERE type IN (30) AND dt1<NOW();
+------+------------+------+
| str1 | dt1        | type |
+------+------------+------+
|      | 2009-08-23 |   30 | 
+------+------------+------+
1 row in set (0.01 sec)

Verified as described