Description:
When a secondary index is used to retrieve data, the empty set is returned even if the
target table has a matched data value in the column that is defined with the secondary
index. By dropping that secondary index, we can get correct result set.
=================
Empty set result
=================
mysql> select i_id, i_a_id from item where i_a_id=2500;
Empty set (0.00 sec)
mysql> explain select i_id, i_a_id from item where i_a_id=2500;
+----+-------------+-------+------+---------------+----------+---------+-------+------+---
--------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows |
Extra |
+----+-------------+-------+------+---------------+----------+---------+-------+------+---
--------------------------------+
| 1 | SIMPLE | item | ref | i_i_a_id | i_i_a_id | 6 | const | 10 |
Using where with pushed condition |
+----+-------------+-------+------+---------------+----------+---------+-------+------+---
--------------------------------+
1 row in set (0.00 sec)
==============================
Correct result by DROP INDEX:
==============================
mysql> drop index i_i_a_id on item;
Query OK, 0 rows affected (0.49 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select i_id, i_a_id from item where i_a_id=2500;
+------+--------+
| i_id | i_a_id |
+------+--------+
| 9925 | 2500 |
| 3152 | 2500 |
| 7645 | 2500 |
| 7440 | 2500 |
| 6247 | 2500 |
| 5068 | 2500 |
| 2500 | 2500 |
+------+--------+
7 rows in set (0.01 sec)
mysql> explain select i_id, i_a_id from item where i_a_id=2500;
+----+-------------+-------+------+---------------+------+---------+------+-------+-------
----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
|
+----+-------------+-------+------+---------------+------+---------+------+-------+-------
----------------------------+
| 1 | SIMPLE | item | ALL | NULL | NULL | NULL | NULL | 10000 | Using
where with pushed condition |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------
----------------------------+
1 row in set (0.00 sec)
======================================
Table definition for Empty set result:
======================================
mysql> show create table item;
+-------+---------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
------------------------------+
| Table | Create Table
+-------+---------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
------------------------------+
| item | CREATE TABLE `item` (
`i_id` decimal(10,0) NOT NULL DEFAULT '0',
`i_title` varchar(60) DEFAULT NULL,
`i_a_id` decimal(10,0) DEFAULT NULL,
`i_pub_date` date DEFAULT NULL,
`i_publisher` varchar(60) DEFAULT NULL,
`i_subject` varchar(60) DEFAULT NULL,
`i_desc` text,
`i_related1` decimal(10,0) DEFAULT NULL,
`i_related2` decimal(10,0) DEFAULT NULL,
`i_related3` decimal(10,0) DEFAULT NULL,
`i_related4` decimal(10,0) DEFAULT NULL,
`i_related5` decimal(10,0) DEFAULT NULL,
`i_thumbnail` decimal(8,0) DEFAULT NULL,
`i_image` decimal(8,0) DEFAULT NULL,
`i_srp` decimal(17,2) DEFAULT NULL,
`i_cost` decimal(17,2) DEFAULT NULL,
`i_avail` date DEFAULT NULL,
`i_stock` decimal(4,0) DEFAULT NULL,
`i_isbn` varchar(13) DEFAULT NULL,
`i_page` decimal(4,0) DEFAULT NULL,
`i_backing` varchar(15) DEFAULT NULL,
`i_dimensions` varchar(25) DEFAULT NULL,
PRIMARY KEY (`i_id`),
KEY `i_i_subject` (`i_subject`),
KEY `i_i_title` (`i_title`),
KEY `i_i_a_id` (`i_a_id`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
------------------------------+
1 row in set (0.00 sec)
How to repeat:
This result can be reproduced by the same table definition and data that we experienced.
If you need the data, please let us know.
1. Create NDB table and create index.
2. Insert data into the table.
3. Restart Data node.
4. Execute SELECT SQL using secondary index.
====
DDL:
====
CREATE TABLE `item` (
`i_id` decimal(10,0) NOT NULL default '0',
`i_title` varchar(60) default NULL,
`i_a_id` decimal(10,0) default NULL,
`i_pub_date` date default NULL,
`i_publisher` varchar(60) default NULL,
`i_subject` varchar(60) default NULL,
`i_desc` text,
`i_related1` decimal(10,0) default NULL,
`i_related2` decimal(10,0) default NULL,
`i_related3` decimal(10,0) default NULL,
`i_related4` decimal(10,0) default NULL,
`i_related5` decimal(10,0) default NULL,
`i_thumbnail` decimal(8,0) default NULL,
`i_image` decimal(8,0) default NULL,
`i_srp` decimal(17,2) default NULL,
`i_cost` decimal(17,2) default NULL,
`i_avail` date default NULL,
`i_stock` decimal(4,0) default NULL,
`i_isbn` varchar(13) default NULL,
`i_page` decimal(4,0) default NULL,
`i_backing` varchar(15) default NULL,
`i_dimensions` varchar(25) default NULL,
PRIMARY KEY (`i_id`)
) ENGINE=NDB;
create index i_i_subject on item (i_subject asc);
create index i_i_a_id on item (i_a_id asc);
create index i_i_title on item (i_title asc);
Description: When a secondary index is used to retrieve data, the empty set is returned even if the target table has a matched data value in the column that is defined with the secondary index. By dropping that secondary index, we can get correct result set. ================= Empty set result ================= mysql> select i_id, i_a_id from item where i_a_id=2500; Empty set (0.00 sec) mysql> explain select i_id, i_a_id from item where i_a_id=2500; +----+-------------+-------+------+---------------+----------+---------+-------+------+--- --------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+----------+---------+-------+------+--- --------------------------------+ | 1 | SIMPLE | item | ref | i_i_a_id | i_i_a_id | 6 | const | 10 | Using where with pushed condition | +----+-------------+-------+------+---------------+----------+---------+-------+------+--- --------------------------------+ 1 row in set (0.00 sec) ============================== Correct result by DROP INDEX: ============================== mysql> drop index i_i_a_id on item; Query OK, 0 rows affected (0.49 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select i_id, i_a_id from item where i_a_id=2500; +------+--------+ | i_id | i_a_id | +------+--------+ | 9925 | 2500 | | 3152 | 2500 | | 7645 | 2500 | | 7440 | 2500 | | 6247 | 2500 | | 5068 | 2500 | | 2500 | 2500 | +------+--------+ 7 rows in set (0.01 sec) mysql> explain select i_id, i_a_id from item where i_a_id=2500; +----+-------------+-------+------+---------------+------+---------+------+-------+------- ----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+-------+------- ----------------------------+ | 1 | SIMPLE | item | ALL | NULL | NULL | NULL | NULL | 10000 | Using where with pushed condition | +----+-------------+-------+------+---------------+------+---------+------+-------+------- ----------------------------+ 1 row in set (0.00 sec) ====================================== Table definition for Empty set result: ====================================== mysql> show create table item; +-------+--------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------ ------------------------------+ | Table | Create Table +-------+--------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------ ------------------------------+ | item | CREATE TABLE `item` ( `i_id` decimal(10,0) NOT NULL DEFAULT '0', `i_title` varchar(60) DEFAULT NULL, `i_a_id` decimal(10,0) DEFAULT NULL, `i_pub_date` date DEFAULT NULL, `i_publisher` varchar(60) DEFAULT NULL, `i_subject` varchar(60) DEFAULT NULL, `i_desc` text, `i_related1` decimal(10,0) DEFAULT NULL, `i_related2` decimal(10,0) DEFAULT NULL, `i_related3` decimal(10,0) DEFAULT NULL, `i_related4` decimal(10,0) DEFAULT NULL, `i_related5` decimal(10,0) DEFAULT NULL, `i_thumbnail` decimal(8,0) DEFAULT NULL, `i_image` decimal(8,0) DEFAULT NULL, `i_srp` decimal(17,2) DEFAULT NULL, `i_cost` decimal(17,2) DEFAULT NULL, `i_avail` date DEFAULT NULL, `i_stock` decimal(4,0) DEFAULT NULL, `i_isbn` varchar(13) DEFAULT NULL, `i_page` decimal(4,0) DEFAULT NULL, `i_backing` varchar(15) DEFAULT NULL, `i_dimensions` varchar(25) DEFAULT NULL, PRIMARY KEY (`i_id`), KEY `i_i_subject` (`i_subject`), KEY `i_i_title` (`i_title`), KEY `i_i_a_id` (`i_a_id`) ) ENGINE=ndbcluster DEFAULT CHARSET=latin1 | +-------+--------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------ ------------------------------+ 1 row in set (0.00 sec) How to repeat: This result can be reproduced by the same table definition and data that we experienced. If you need the data, please let us know. 1. Create NDB table and create index. 2. Insert data into the table. 3. Restart Data node. 4. Execute SELECT SQL using secondary index. ==== DDL: ==== CREATE TABLE `item` ( `i_id` decimal(10,0) NOT NULL default '0', `i_title` varchar(60) default NULL, `i_a_id` decimal(10,0) default NULL, `i_pub_date` date default NULL, `i_publisher` varchar(60) default NULL, `i_subject` varchar(60) default NULL, `i_desc` text, `i_related1` decimal(10,0) default NULL, `i_related2` decimal(10,0) default NULL, `i_related3` decimal(10,0) default NULL, `i_related4` decimal(10,0) default NULL, `i_related5` decimal(10,0) default NULL, `i_thumbnail` decimal(8,0) default NULL, `i_image` decimal(8,0) default NULL, `i_srp` decimal(17,2) default NULL, `i_cost` decimal(17,2) default NULL, `i_avail` date default NULL, `i_stock` decimal(4,0) default NULL, `i_isbn` varchar(13) default NULL, `i_page` decimal(4,0) default NULL, `i_backing` varchar(15) default NULL, `i_dimensions` varchar(25) default NULL, PRIMARY KEY (`i_id`) ) ENGINE=NDB; create index i_i_subject on item (i_subject asc); create index i_i_a_id on item (i_a_id asc); create index i_i_title on item (i_title asc);