Bug #59519 ::set_rec_per_key() assumes ORDER_INDEX to be unique
Submitted: 14 Jan 2011 22:06 Modified: 26 Jan 2011 11:20
Reporter: Ole John Aske Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: Ole John Aske CPU Architecture:Any

[14 Jan 2011 22:06] Ole John Aske
Description:
ha_ndbcluster::set_rec_per_key should set the KEYINFO::rec_pr_key[] such that a value '=1' specifies that a single row is returned when that key is used.

ha_ndbcluster incorrectly sets 'rec_pr_key[]= 1' even for a non-unique 'ORDERED_INDEXE'. This is incorrect !

How to repeat:
CREATE TABLE t10(
  K INT NOT NULL AUTO_INCREMENT,
  I INT, J INT,
  PRIMARY KEY(K),
  KEY(I,J),
  UNIQUE KEY(J,K)
) ENGINE=ndbcluster;

INSERT INTO t10(I,J) VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(0,0);

CREATE TABLE t100 LIKE t10;
INSERT INTO t100(I,J)
  SELECT X.J, X.J+(10*Y.J) FROM t10 AS X,t10 AS Y;

CREATE TABLE t10000 LIKE t10;
INSERT INTO t10000(I,J)
  SELECT X.J, X.J+(100*Y.J) FROM t100 AS X,t100 AS Y;

ANALYZE TABLE t10,t100,t10000;

# 'REF' join on 'Y' should estimate match >1 rows
# Will return =1 wo/ patch and =11 w/ patch 
EXPLAIN
SELECT * FROM t10000 AS X JOIN t10000 AS Y 
  ON Y.I=X.I AND Y.J = X.I;

Suggested fix:
=== modified file 'sql/ha_ndbcluster.cc'
--- sql/ha_ndbcluster.cc	2010-12-22 17:05:34 +0000
+++ sql/ha_ndbcluster.cc	2011-01-12 09:52:06 +0000
@@ -962,7 +962,11 @@ void ha_ndbcluster::set_rec_per_key()
   DBUG_ENTER("ha_ndbcluster::set_rec_per_key");
   for (uint i=0 ; i < table_share->keys ; i++)
   {
-    table->key_info[i].rec_per_key[table->key_info[i].key_parts-1]= 1;
+    if (get_index_type(i) != ORDERED_INDEX)
+    {
+      KEY* key_info= table->key_info + i;
+      key_info->rec_per_key[key_info->key_parts-1]= 1;
+    }
   }
   DBUG_VOID_RETURN;
 }
[18 Jan 2011 11:53] Ole John Aske
Fixed has been pushed into mysql-5.1-telco-7.0 ++

... Even if not shown by 'Progress log' as commit message contained Bug##59519
(Instead of single '#')
[18 Jan 2011 12:47] 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/129087

4135 Ole John Aske	2011-01-18
      Resultfile needed update after commit of fix for bug#59519.
     @ mysql-test/suite/rpl_ndb/r/rpl_ndb_ddl.result
        As 'rec_per_key[]= 1' is no longer set for non-unique index we don't have available 'Cardinality' statistics for ordered indexes. Thus the change of 'Cardinality' from '0' to NULL (unknown) for the above results.
[18 Jan 2011 12:47] Bugs System
Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.22 (revid:ole.john.aske@oracle.com-20110118124701-t0bxo2vjh3vh9ia8) (version source revid:ole.john.aske@oracle.com-20110118124701-t0bxo2vjh3vh9ia8) (merge vers: 5.1.51-ndb-7.0.22) (pib:24)
[26 Jan 2011 11:20] Jon Stephens
Documented bugfix in the NDB-7.0.22 and 7.1.11 changelogs as follows:

      NDB sometimes treated a simple (not unique) ordered index as 
      unique.

Closed.