bzr commit into mysql-5.1-telco-7.0 branch (ole.john.aske:4125) Bug#59517

[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

 4125 Ole John Aske	2011-01-17
      Fix for bug#59517: 'Incorrect detection of single row access in ha_ndbcluster::records_in_range()'
      
      The logic for detecting a range being an excact match on a single row has been corrected.
      
      Added a new MTR test 'ndb_statistics' intended for testing statistics returned 
      from ha_ndbcluster
     @ mysql-test/suite/ndb/r/ndb_index_unique.result
        The condition 'a < 9' was previously incorrectly detected as an excact match on pk(a) return 1 row.
     @ mysql-test/suite/ndb/r/ndb_read_multi_range.result
        The condition 't2.pk != 6' was previously detected as two exact ranges
        which produced the estimate '2 rows'
     @ mysql-test/suite/ndb/t/ndb_statistics.test
        Added a new MTR test which is intended for testing of 
        statistics returned from ha_ndbcluster.

    added:
      mysql-test/suite/ndb/r/ndb_statistics.result
      mysql-test/suite/ndb/t/ndb_statistics.test
    modified:
      mysql-test/suite/ndb/r/ndb_index_unique.result
      mysql-test/suite/ndb/r/ndb_read_multi_range.result
      sql/ha_ndbcluster.cc
=== modified file 'mysql-test/suite/ndb/r/ndb_index_unique.result'
--- a/mysql-test/suite/ndb/r/ndb_index_unique.result	2011-01-17 12:31:29 +0000
+++ b/mysql-test/suite/ndb/r/ndb_index_unique.result	2011-01-17 14:47:10 +0000
@@ -185,7 +185,7 @@ set @old_ecpd = @@session.engine_conditi
 set engine_condition_pushdown = true;
 explain select * from t2 where (b = 3 OR b = 5) AND c IS NULL AND a < 9 order by a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	range	PRIMARY,b	PRIMARY	4	NULL	1	Using where with pushed condition
+1	SIMPLE	t2	range	PRIMARY,b	b	9	NULL	2	Using where with pushed condition; Using filesort
 select * from t2 where (b = 3 OR b = 5) AND c IS NULL AND a < 9 order by a;
 a	b	c
 3	3	NULL

=== modified file 'mysql-test/suite/ndb/r/ndb_read_multi_range.result'
--- a/mysql-test/suite/ndb/r/ndb_read_multi_range.result	2010-11-24 13:16:09 +0000
+++ b/mysql-test/suite/ndb/r/ndb_read_multi_range.result	2011-01-17 14:47:10 +0000
@@ -605,7 +605,7 @@ SELECT DISTINCT STRAIGHT_JOIN t1.pk FROM
 t1 LEFT JOIN t2 ON t2.a = t1.a AND t2.pk != 6;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3000	Using temporary
-1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	Using where; Distinct
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	20	Using where; Distinct
 SELECT DISTINCT STRAIGHT_JOIN t1.pk FROM 
 t1 LEFT JOIN t2 ON t2.a = t1.a AND t2.pk != 6;
 drop table t1, t2;

=== added file 'mysql-test/suite/ndb/r/ndb_statistics.result'
--- a/mysql-test/suite/ndb/r/ndb_statistics.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/ndb/r/ndb_statistics.result	2011-01-17 14:47:10 +0000
@@ -0,0 +1,55 @@
+drop table if exists t1, t2, t3, t4;
+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
+WHERE X.J<50;
+INSERT INTO t10000(I,J)
+SELECT X.J, X.J+(100*Y.J) FROM t100 AS X,t100 AS Y
+WHERE X.J>=50;
+ANALYZE TABLE t10,t100,t10000;
+Table	Op	Msg_type	Msg_text
+test.t10	analyze	status	OK
+test.t100	analyze	status	OK
+test.t10000	analyze	status	OK
+SELECT COUNT(*) FROM t10;
+COUNT(*)
+10
+SELECT COUNT(*) FROM t100;
+COUNT(*)
+100
+SELECT COUNT(*) FROM t10000;
+COUNT(*)
+10000
+EXPLAIN
+SELECT * FROM t10000 WHERE k = 42;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t10000	const	PRIMARY	PRIMARY	4	const	1	
+EXPLAIN
+SELECT * FROM t10000 WHERE k >= 42 and k < 10000;
+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 WHERE k BETWEEN 42 AND 10000;
+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 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 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
+DROP TABLE t10,t100,t10000;
+End of 5.1 tests

=== added file 'mysql-test/suite/ndb/t/ndb_statistics.test'
--- a/mysql-test/suite/ndb/t/ndb_statistics.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/ndb/t/ndb_statistics.test	2011-01-17 14:47:10 +0000
@@ -0,0 +1,59 @@
+-- source include/have_ndb.inc
+
+--disable_warnings
+drop table if exists t1, t2, t3, t4;
+--enable_warnings
+
+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 in two chunks to not
+#  exhaust MaxNoOfConcurrentOperations
+INSERT INTO t10000(I,J)
+  SELECT X.J, X.J+(100*Y.J) FROM t100 AS X,t100 AS Y
+  WHERE X.J<50;
+INSERT INTO t10000(I,J)
+  SELECT X.J, X.J+(100*Y.J) FROM t100 AS X,t100 AS Y
+  WHERE X.J>=50;
+
+ANALYZE TABLE t10,t100,t10000;
+
+SELECT COUNT(*) FROM t10;
+SELECT COUNT(*) FROM t100;
+SELECT COUNT(*) FROM t10000;
+
+#
+# Bug #59517: Incorrect detection of single row access in
+#             ha_ndbcluster::records_in_range()
+
+# Expect a single row (or const) when PK is excact specified
+EXPLAIN
+SELECT * FROM t10000 WHERE k = 42;
+
+# All queries below should *not* return a single row
+EXPLAIN
+SELECT * FROM t10000 WHERE k >= 42 and k < 10000;
+EXPLAIN
+SELECT * FROM t10000 WHERE k BETWEEN 42 AND 10000;
+EXPLAIN
+SELECT * FROM t10000 WHERE k < 42;
+EXPLAIN
+SELECT * FROM t10000 WHERE k > 42;
+
+
+DROP TABLE t10,t100,t10000;
+
+--echo End of 5.1 tests

=== modified file 'sql/ha_ndbcluster.cc'
--- a/sql/ha_ndbcluster.cc	2011-01-17 13:29:52 +0000
+++ b/sql/ha_ndbcluster.cc	2011-01-17 14:47:10 +0000
@@ -10578,8 +10578,10 @@ ha_ndbcluster::records_in_range(uint inx
   // Read from hash index with full key
   // This is a "const" table which returns only one record!      
   if ((idx_type != ORDERED_INDEX) &&
-      ((min_key && min_key->length == key_length) || 
-       (max_key && max_key->length == key_length)))
+      ((min_key && min_key->length == key_length) &&
+       (max_key && max_key->length == key_length) &&
+       (min_key->key==max_key->key ||
+        memcmp(min_key->key, max_key->key, key_length)==0)))
     DBUG_RETURN(1);
   
   if ((idx_type == PRIMARY_KEY_ORDERED_INDEX ||


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