Bug #25821 Excessive partition pruning for multi-range index scan in NDB API
Submitted: 24 Jan 2007 11:44 Modified: 8 Feb 2007 8:57
Reporter: Kristian Nielsen Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S3 (Non-critical)
Version:5.0, 5.1.15 OS:Linux (Linux)
Assigned to: Martin Skold CPU Architecture:Any

[24 Jan 2007 11:44] Kristian Nielsen
Description:
NDB API can optimize ordered index scans that utilize an equal predicate on the
distribution key.

However, this is broken when doing multiple range scans in a single SCAN_TABREQ signal, as the partition pruning for one range is applied to all ranges.

How to repeat:
mysql> create table t2 (a int, b int, primary key (a,b)) engine=ndbcluster partition by key(a);
mysql> insert into t2 values (1,1), (10,10);
mysql> select * from t2 where a in (1,10);
+---+---+
| a | b |
+---+---+
| 1 | 1 | 
+---+---+

Here, NDB API is sending distribution key for a=1, causing the fragment containing a=10 to not be scanned at all, and the row therefore to be lost.

Suggested fix:
The proper fix would seem to be that SCAN_TABREQ would set distribution key individually for each of multiple ranges send in KEYINFO. This probably requires non-trivial protocol changes.

A simpler change would be to disable sending distribution key when using multi ranges. But then mysqld should be extended to handle partition pruning (or simpler, not use multi-range scans), or we would loose partition pruning completely for these kinds of index scans.
[24 Jan 2007 11:50] Kristian Nielsen
BTW, the above test case requires a two-node cluster, as started by mysql-test-run.pl.

Depending on the cluster setup, possibly other values than 1 and 10 will be needed to repeat, they need to map to different fragments in the cluster for the bug to show.
[6 Feb 2007 20:58] Martin Skold
Also reproducable in 5.0:

create table t2 (a int, b int, primary key (a), key ab (a,b)) engine=ndbcluster;
insert into t2 values (1,1), (10,10);
select * from t2 use index (ab) where a in(1,10);
a       b
1       1
[6 Feb 2007 22:07] 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/19452

ChangeSet@1.2297, 2007-02-06 23:06:58+01:00, mskold@mysql.com +7 -0
  Fix for bug#25821  Excessive partition pruning for multi-range index scan in NDB API: don't set distribution key if multi_range
[7 Feb 2007 8:20] 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/19462

ChangeSet@1.2298, 2007-02-07 09:19:33+01:00, mskold@mysql.com +1 -0
  bug#25821  Excessive partition pruning for multi-range index scan in NDB API: post-review fix, added default value
[7 Feb 2007 8:52] 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/19465

ChangeSet@1.2427, 2007-02-07 09:49:16+01:00, mskold@mysql.com +2 -0
  bug#25821  Excessive partition pruning for multi-range index scan in NDB API: added original test case
[7 Feb 2007 10:37] 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/19467

ChangeSet@1.2299, 2007-02-07 11:36:34+01:00, mskold@mysql.com +4 -0
  NdbScanOperation.hpp, NdbScanOperation.cpp, ha_ndbcluster.cc:
    bug#25821  Excessive partition pruning for multi-range index scan in NDB API: added multi_range error checking in end_of_bound
  ha_ndbcluster.h:
    Removed stray mthod declaration
[7 Feb 2007 16:55] Tomas Ulin
pushed to 5.1.16
[7 Feb 2007 23:54] Tomas Ulin
puhsed to 5.0.36
[8 Feb 2007 8:57] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.0.36 and 5.1.16 changelogs.