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: | |
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
[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