MySQL Bugs: #25821: Excessive partition pruning for multi-range index scan in NDB API

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

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.

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.

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

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

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

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

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

pushed to 5.1.16

puhsed to 5.0.36

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.