MySQL Bugs: #33061: ORDER BY DESC becomes ASC in NDB partition pruning to one partition

Bug #33061	ORDER BY DESC becomes ASC in NDB partition pruning to one partition
Submitted:	7 Dec 2007 13:22	Modified:	20 Feb 2008 20:58
Reporter:	Kristian Nielsen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S2 (Serious)
Version:	5.1, 6.0, telco-6.2, telco-6.3	OS:	Any
Assigned to:	Kristian Nielsen	CPU Architecture:	Any

Description:
With NDB storage engine and PARTITION BY KEY on part of a composite primary key, index-based SELECT MAX() returns the wrong result.

This is on latest bitkeeper version of mysql-5.1-telco-6.2.

How to repeat:
mysql> create table t (a int, b int, primary key (a,b)) engine=ndb partition by key(a) partitions 4;
mysql> insert into t values (0,0), (0,1);
mysql> select max(b) from t where a = 0;
+--------+
| max(b) |
+--------+
|      0 | 
+--------+

/* Returns 0, even though the maximum is reall 1. */

works fine with MyISAM and Innodb, fails with ndbcluster (tested on ndb-6.3.6)

mysqltest test case

Attachment: bug33061.tgz (application/x-gtar, text), 854 bytes.

More information, simpler testcase without the PARTITIONS 4:

mysql> create table t (a int, b int, primary key (a,b)) engine=ndb partition by key(a);
mysql> insert into t values (0,0), (0,1);
mysql> select max(b) from t where a = 0;
+--------+
| max(b) |
+--------+
|      0 | 
+--------+
mysql> drop table t;

mysql> create table t (a int, b int, primary key (a,b)) engine=ndb partition by key(a,b);
mysql> insert into t values (0,0), (0,1);
mysql> select max(b) from t where a = 0;
+--------+
| max(b) |
+--------+
|      1 | 
+--------+

So the failure is not related to PARTITIONS 4. It _is_ related to partitioning on only part of the primary key (ie. setting non-default distribution key).

Bug is not present in 5.1.

Real problem here is a bug in an optimization in NDB.

When partition pruning finds that only one partition is needed in an ordered
index scan, there is no need for a merge sort among partitions. But the code
then also wrongly disregards the flag telling if the scan is descending, so
turns DESC into ASC.

Found this bug in 5.1 as well, with a different testcase (requires mysqld --new):

mysql> create table t (a int, b int, primary key (a,b)) engine=ndb partition by range(a) partitions 1 (partition x1 values less than (5));
mysql> insert into t values (1,1),(1,2),(1,3),(1,4),(1,5);
mysql> select b from t where a = 1 order by b desc;
+---+
| b |
+---+
| 1 | 
| 2 | 
| 3 | 
| 4 | 
| 5 | 
+---+
5 rows in set (0.00 sec)

The reason the original test does not fail in 5.1 is that no partition pruning
takes place for this test in 5.1 for some reason.

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/40132

ChangeSet@1.2645, 2007-12-18 13:40:35+01:00, knielsen@ymer.(none) +5 -0
  Bug #33061: ORDER BY DESC becomes ASC in NDB partition pruning to one partition
  
  When partition pruning resulted in an ordered index scan spanning only
  one partition, any descending flag for the scan was wrongly discarded,
  turning ORDER BY DESC into ORDER BY ASC, and similar problems.
  
  Fixed by correctly passing descending flag in SCAN_TABREQ signal sent
  to data nodes.

Pushed to mysql-5.1-new-ndb, mysql-5.1-telco-6.2, mysql-5.1-telco-6.3, mysql-6.0-ndb.

Bug affects all existing version with partitioning, 5.1+.

Documented in 5.1.23-ndb-6.3.7 changelog as follows:

        When partition pruning on an NDB table
        resulted in an ordered index scan spanning only one partition,
        any descending flag for the scan was wrongly discarded, causing
        ORDER BY DESC to be treated as ORDER
        BY ASC, MAX() to be handled
        incorrectly, and similar problems.  

Left status as PQ pending merges to other trees.

Also documented fix in 5.1.23-ndb-6.2.10. Left bug status unchanged.

Bug does also affect mysql-5.1 and mysql-6.0, and will also be fixed in these versions.

The fix should appear in those versions after the next merge of -ndb trees to main,

Pushed into 5.1.24-rc

Pushed into 6.0.5-alpha

Also documented for 5.1.24 and 6.0.5.