bzr commit into mysql-5.1-telco-7.0 branch (ole.john.aske:4132)

[Ole John Aske]

#At file:///net/fimafeng09/export/home/tmp/oleja/mysql/mysql-5.1-telco-7.0/ based on revid:ole.john.aske@stripped

 4132 Ole John Aske	2011-01-18
      Fix for bug##59519 ::set_rec_per_key() assumes ORDER_INDEX to be unique
      
      An ORDERED_INDEX - which is non-unique - should not set rec_per_key[]= 1 which
      is intended for signaling that there will be ~1 row matching each key value.
      
      Leaving it at '0' will force the optimizer to use its own heuristic to estimate
      how mane 'records pr. key' there will be.
      
      NOTE: For some unknown reason the optimizer will not use handler::::records_in_range()
      to estimate this, but will instead really upon its own estimates.
     @ mysql-test/suite/ndb/r/ndb_alter_table3.result
        As rec_pr_key[] will no longer indicate an ordered index to be be unique, 
        the ordered indexes indexes 'b' and 'c' in this test will now 
        have an unknown 'Cardinality'.

    modified:
      mysql-test/suite/ndb/r/ndb_alter_table3.result
      mysql-test/suite/ndb/r/ndb_statistics.result
      mysql-test/suite/ndb/t/ndb_statistics.test
      sql/ha_ndbcluster.cc
=== modified file 'mysql-test/suite/ndb/r/ndb_alter_table3.result'
--- a/mysql-test/suite/ndb/r/ndb_alter_table3.result	2008-08-12 15:35:47 +0000
+++ b/mysql-test/suite/ndb/r/ndb_alter_table3.result	2011-01-18 08:55:15 +0000
@@ -6,8 +6,8 @@ create index c on t1(c);
 show indexes from t1;
 Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
 t1	0	PRIMARY	1	a	A	3	NULL	NULL		BTREE	
-t1	1	b	1	b	A	3	NULL	NULL	YES	BTREE	
-t1	1	c	1	c	A	3	NULL	NULL	YES	BTREE	
+t1	1	b	1	b	A	NULL	NULL	NULL	YES	BTREE	
+t1	1	c	1	c	A	NULL	NULL	NULL	YES	BTREE	
 select * from t1 where c = 'two';
 a	b	c
 2	two	two
@@ -15,7 +15,7 @@ alter table t1 drop index c;
 show indexes from t1;
 Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
 t1	0	PRIMARY	1	a	A	3	NULL	NULL		BTREE	
-t1	1	b	1	b	A	3	NULL	NULL	YES	BTREE	
+t1	1	b	1	b	A	NULL	NULL	NULL	YES	BTREE	
 select * from t1 where c = 'two';
 a	b	c
 2	two	two

=== modified file 'mysql-test/suite/ndb/r/ndb_statistics.result'
--- a/mysql-test/suite/ndb/r/ndb_statistics.result	2011-01-18 07:49:14 +0000
+++ b/mysql-test/suite/ndb/r/ndb_statistics.result	2011-01-18 08:55:15 +0000
@@ -51,5 +51,11 @@ EXPLAIN
 SELECT * FROM t10000 WHERE k > 42;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t10000	range	PRIMARY	PRIMARY	4	NULL	10	Using where with pushed condition
+EXPLAIN
+SELECT * FROM t10000 AS X JOIN t10000 AS Y
+ON Y.I=X.I AND Y.J = X.I;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	X	ALL	I	NULL	NULL	NULL	10000	
+1	SIMPLE	Y	ref	J,I	I	10	test.X.I,test.X.I	11	Using where
 DROP TABLE t10,t100,t10000;
 End of 5.1 tests

=== modified file 'mysql-test/suite/ndb/t/ndb_statistics.test'
--- a/mysql-test/suite/ndb/t/ndb_statistics.test	2011-01-18 07:49:14 +0000
+++ b/mysql-test/suite/ndb/t/ndb_statistics.test	2011-01-18 08:55:15 +0000
@@ -53,6 +53,15 @@ SELECT * FROM t10000 WHERE k < 42;
 EXPLAIN
 SELECT * FROM t10000 WHERE k > 42;
 
+#
+# Bug #59519 ::set_rec_per_key() assumes ORDER_INDEX to be unique
+#
+
+# 'REF' join of 'Y' should match >1 rows
+EXPLAIN
+SELECT * FROM t10000 AS X JOIN t10000 AS Y
+  ON Y.I=X.I AND Y.J = X.I;
+
 
 DROP TABLE t10,t100,t10000;
 

=== modified file 'sql/ha_ndbcluster.cc'
--- a/sql/ha_ndbcluster.cc	2011-01-18 07:49:14 +0000
+++ b/sql/ha_ndbcluster.cc	2011-01-18 08:55:15 +0000
@@ -959,9 +959,35 @@ Ndb *ha_ndbcluster::get_ndb(THD *thd)
 void ha_ndbcluster::set_rec_per_key()
 {
   DBUG_ENTER("ha_ndbcluster::set_rec_per_key");
+  /*
+    Set up the 'rec_per_key[]' for keys which we have good knowledge
+    about the distribution. 'rec_per_key[]' is init'ed to '0' by 
+    open_binary_frm(), which is interpreted as 'unknown' by optimizer.
+    -> Not setting 'rec_per_key[]' will force the optimizer to use
+    its own heuristic to estimate 'records pr. 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;
+    switch (get_index_type(i))
+    {
+    case UNIQUE_ORDERED_INDEX:
+    case PRIMARY_KEY_ORDERED_INDEX:
+    case UNIQUE_INDEX:
+    case PRIMARY_KEY_INDEX:
+    {
+      // Index is unique when all 'key_parts' are specified,
+      // else distribution is unknown and not specified here.
+      KEY* key_info= table->key_info + i;
+      key_info->rec_per_key[key_info->key_parts-1]= 1;
+      break;
+    }
+    case ORDERED_INDEX:
+      // 'Records pr. key' are unknown for non-unique indexes.
+      // (May change when we get better index statistics.)
+      break;
+    default:
+      DBUG_ASSERT(false);
+    }
   }
   DBUG_VOID_RETURN;
 }


Attachment: [text/bzr-bundle] bzr/[e-mail]