Bug #57019 InnoDB does not return data with merge index on nullable columns
Submitted: 25 Sep 2010 18:07 Modified: 27 Sep 2010 7:09
Reporter: Giuseppe Maxia Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.1.50, 5.1.52-bzr, 5.5.6 OS:MacOS
Assigned to: CPU Architecture:Any
Tags: INDEX, innodb, nullable

[25 Sep 2010 18:07] Giuseppe Maxia
Description:
When an index intersect is used on nullable columns, some queries return empty data sets instead of the expected records.

drop table if exists t1
Query OK, 0 rows affected (0.05 sec)

create table t1 ( 
    cola int(11), 
    colb int(11)
) engine=innodb default charset=utf8
Query OK, 0 rows affected (0.15 sec)

insert into t1 values (1,2), (1,3), (3,3), (4,9), (12,200)
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

select * from t1
+------+------+
| cola | colb |
+------+------+
|    1 |    2 |
|    1 |    3 |
|    3 |    3 |
|    4 |    9 |
|   12 |  200 |
+------+------+
5 rows in set (0.00 sec)

select * from t1 where cola = 3 and colb = 3
+------+------+
| cola | colb |
+------+------+
|    3 |    3 |
+------+------+
1 row in set (0.00 sec)

select * from t1 where cola = 1 and colb = 3
+------+------+
| cola | colb |
+------+------+
|    1 |    3 |
+------+------+
1 row in set (0.00 sec)

##
## So far, all the queries return the expected results.
##
##
## Now, we add two indexes:
##

alter table t1 add key key_a (cola), add key key_b (colb)
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

show create table t1
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `cola` int(11) DEFAULT NULL,
  `colb` int(11) DEFAULT NULL,
  KEY `key_a` (`cola`),
  KEY `key_b` (`colb`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

##
## This query, which previously returned the wanted
## record, now returns an empty set
##
select * from t1 where cola = 1 and colb = 3
Empty set (0.00 sec) 
####
####  ^^^^^^ The data is missing!
####

##
## The problem appears only when an index_merge is used.
##
EXPLAIN select * from t1 where cola = 1 and colb = 3
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: index_merge
possible_keys: key_a,key_b
          key: key_a,key_b
      key_len: 5,5
          ref: NULL
         rows: 1
        Extra: Using intersect(key_a,key_b); Using where; Using index
1 row in set (0.00 sec)

##
## When excluding the index, the query returns the wanted results
##

select * 
from t1 ignore index (key_a) 
where cola = 1 and colb = 3
+------+------+
| cola | colb |
+------+------+
|    1 |    3 |
+------+------+
1 row in set (0.00 sec)

##
## If the columns become NOT NULL, the problem disappears
##
alter table t1 
    modify column cola int(11) NOT NULL,
    modify column colb int(11) NOT NULL

Query OK, 5 rows affected (0.19 sec)
Records: 5  Duplicates: 0  Warnings: 0

select * from t1 where cola = 1 and colb = 3

+------+------+
| cola | colb |
+------+------+
|    1 |    3 |
+------+------+
1 row in set (0.00 sec)

Notice that MyISAM does not suffer from the same problem.

How to repeat:
use test;
drop table if exists t1;

create table t1 ( 
    cola int(11), 
    colb int(11)
) engine=innodb default charset=utf8;
insert into t1 values (1,2), (1,3), (3,3), (4,9), (12,200);
select * from t1;
select * from t1 where cola = 3 and colb = 3;
select * from t1 where cola = 1 and colb = 3;
## so far, all results are as expected

alter table t1 add key key_a (cola), add key key_b (colb);
show create table t1\G
## SAME QUERY after keys - added returns empty set.
select * from t1 where cola = 1 and colb = 3;

EXPLAIN select * from t1 where cola = 1 and colb = 3\G

## excluding the index,  the query returns the data.
select * 
from t1 ignore index (key_a) 
where cola = 1 and colb = 3;

## if the columns are NOT NULL, the query succeeds
alter table t1 
    modify column cola int(11) NOT NULL,
    modify column colb int(11) NOT NULL;

select * from t1 where cola = 1 and colb = 3;
[26 Sep 2010 7:33] Valeriy Kravchuk
Verified also with current mysql-5.1 from bzr:

macbook-pro:5.1 openxs$ 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.52-debug Source distribution

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> drop table if exists t1;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 ( 
    ->     cola int(11), 
    ->     colb int(11)
    -> ) engine=innodb default charset=utf8
    -> ;
Query OK, 0 rows affected (0.11 sec)

mysql> insert into t1 values (1,2), (1,3), (3,3), (4,9), (12,200);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from t1 where cola = 1 and colb = 3;
+------+------+
| cola | colb |
+------+------+
|    1 |    3 |
+------+------+
1 row in set (0.00 sec)

mysql> alter table t1 add key key_a (cola), add key key_b (colb);
Query OK, 5 rows affected (0.04 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from t1 where cola = 1 and colb = 3;
Empty set (0.04 sec)

mysql> explain select * from t1 where cola = 1 and colb = 3
    -> \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: index_merge
possible_keys: key_a,key_b
          key: key_a,key_b
      key_len: 5,5
          ref: NULL
         rows: 1
        Extra: Using intersect(key_a,key_b); Using where; Using index
1 row in set (0.00 sec)

mysql> select * 
    -> from t1 ignore index (key_a) 
    -> where cola = 1 and colb = 3
    -> ;
+------+------+
| cola | colb |
+------+------+
|    1 |    3 |
+------+------+
1 row in set (0.00 sec)

mysql> alter table t1 
    ->     modify column cola int(11) NOT NULL,
    ->     modify column colb int(11) NOT NULL;
Query OK, 5 rows affected (0.17 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from t1 where cola = 1 and colb = 3;
+------+------+
| cola | colb |
+------+------+
|    1 |    3 |
+------+------+
1 row in set (0.00 sec)
[27 Sep 2010 6:55] MySQL Verification Team
Hi Giuseppe!

Is this a duplicate of bug #50402 ?

drop table if exists t1;
create table t1(a int,b int,key(a),key(b))engine=innodb;
insert into t1 values (1,2),(1,2),(1,2),(1,2);
select * from t1 force index(a,b) where a=1 and b=2;
select * from t1 ignore index(a,b) where a=1 and b=2;
[27 Sep 2010 7:09] Giuseppe Maxia
Duplicate of bug #50402