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