Bug #57032 'NOT NULL' evaluation is incorrect when using an 'unique index ... using hash'
Submitted: 27 Sep 2010 9:17 Modified: 18 Nov 2010 14:26
Reporter: Ole John Aske Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S2 (Serious)
Version:mysql-5.1-telco-7.1 OS:Any
Assigned to: Pekka Nousiainen CPU Architecture:Any
Tags: 5.1.47-ndb-7.1.8

[27 Sep 2010 9:17] Ole John Aske
Description:
NOT NULL predicate is incorrectly evaluated by MySQL Cluster if :

- There exists an unique index on the columns specified as 'NOT NULL'.
- This index is specified as 'using hash'

Either dropping the index, or creating it as non-'using hash' index will cause the correct result to be returned.

How to repeat:
create table t (pk int primary key, col_int_unique int) engine=ndb;
create unique index ix1 using hash on t(col_int_unique);
insert into t values (1,1);
SELECT * FROM t AS table1 WHERE col_int_unique IS NOT NULL;
--> Empty result!
[27 Sep 2010 9:18] Ole John Aske
This is similar to bug#44771 which is reported for engine=memory.

This might indicate that this is an optimizer bug instead of a Cluster specific bug.
[27 Sep 2010 10:41] Ole John Aske
There is a similar problem involving 'IS NULL' instead of 'NOT NULL':

create table t (pk int primary key, col_int int, col_int_unique int) engine=ndb;
create unique index ix1 using hash on t(col_int,col_int_unique);
insert into t values (1,NULL,1);

SELECT * FROM t WHERE col_int IS NULL AND col_int_unique=1;
-> incorrecty 'empty'

As the original case the index has to be declared as 'using hash'. However, in this 'IS NULL' case the index has to be declared to cover at least two columns, where the predicate specifies one of these as 'IS NULL' while the other column values are non-NULL constants.
[10 Nov 2010 13:12] Pekka Nousiainen
patch

Attachment: a01_patch.diff (text/x-diff), 15.78 KiB.

[14 Nov 2010 14:17] Bugs System
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/123823

3340 Pekka Nousiainen	2010-11-14
      bug#57032 a01_patch.diff
      check cases in "hash index scan"
[15 Nov 2010 9:36] Bugs System
Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.21 (revid:pekka@mysql.com-20101115092310-yr9m4d3s4gvg7zdf) (version source revid:pekka@mysql.com-20101115092310-yr9m4d3s4gvg7zdf) (merge vers: 5.1.51-ndb-7.0.21) (pib:21)
[15 Nov 2010 9:37] Bugs System
Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.40 (revid:pekka@mysql.com-20101114141610-vz25h5393cdp2x72) (version source revid:pekka@mysql.com-20101114141610-vz25h5393cdp2x72) (merge vers: 5.1.51-ndb-6.3.40) (pib:21)
[18 Nov 2010 14:26] Jon Stephens
Documented as follows in the NDB-6.3.40, 7.0.21, and 7.1.10 changelogs:

        Queries using column IS [NOT] NULL on a table with a unique
        index created with USING HASH on column always returned an empty
        result.
      
Closed.