Bug #34515 Secondary index search results in Empty set

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);

Please attach data.

Verified using current 5.1.
Data node restart is required (as reported).
Bug did not occur in 5.0.
Bug requires DECIMAL type (INT UNSIGNED works).
Engine condition pushdown on/off had no effect.

Hi, Pekka
Thanks for your quick analysis.

I've confirmed that INT type can work.
This can be alternative plan for our testing.

Thank you.
Yotaro Nakayama

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/44374

ChangeSet@1.2549, 2008-03-25 11:37:45+02:00, pekka@sama.ndb.mysql.com +1 -0
  ndb - bug#34515 fix

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/44506

ChangeSet@1.2549, 2008-03-27 13:25:56+02:00, pekka@sama.ndb.mysql.com +3 -0
  ndb - bug#34515 - use bytesize (not wordsize) in TUX bounds and comparisons

Documented bugfix in the 5.1.23-ndb-6.3.11 changelog as follows:

        When a secondary index on a DECIMAL column was used to retrieve data, no
        results were returned even if the target table had a matched value in
        the column that was defined with the secondary index.

Left in Patch Pending status pending further merges.

Also noted fix in the 5.1.23-ndb-6.2.15 changelog.

No need to document for 6.4 since there's no release yet.

Already documented for 6.2 and 6.3 series.

Closed.

Pushed into 6.0.6-alpha  (revid:sp1r-pekka@sama.ndb.mysql.com-20080327112556-31653) (version source revid:sp1r-tomas@poseidon.ndb.mysql.com-20080516085603-30848) (pib:5)