bzr commit into mysql-5.1-telco-7.0-spj-scan-vs-scan branch (jan.wedvik:3505)

[Jan Wedvik]

#At file:///net/atum17/export/home/tmp/jw159207/mysql/repo/push-scan-scan/ based on revid:jan.wedvik@stripped

 3505 Jan Wedvik	2011-06-16
      This commit is a regression test case mysql-5.1-telco-7.0 revno: 4457, which was:
      
      "There was an error in the code setting the T_SCAN_REPEATABLE flag. The
        flag was set on the parent of the intended operation rather than on the 
        operation itself. This caused scan ranges for the repeatable scan to be 
        released too early. This gave wrong results when running a bushy scan where
        the scan to be repeated used pruning."

    modified:
      mysql-test/suite/ndb/r/ndb_join_pushdown.result
      mysql-test/suite/ndb/t/ndb_join_pushdown.test
=== modified file 'mysql-test/suite/ndb/r/ndb_join_pushdown.result'
--- a/mysql-test/suite/ndb/r/ndb_join_pushdown.result	2011-06-16 09:00:55 +0000
+++ b/mysql-test/suite/ndb/r/ndb_join_pushdown.result	2011-06-16 09:17:41 +0000
@@ -2070,7 +2070,27 @@ join t1 as t2 on t2.a = t1.c 
 join t1 as t3 on t3.a = t1.c;
 count(*)
 208
+CREATE TABLE tx (
+a int NOT NULL,
+PRIMARY KEY (`a`)
+);
+delete from t1;
+insert into tx values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
+insert into t1 select 1, x1.a * 10+x2.a, 1, 0 from tx as x1 cross join tx as x2;
+explain select count(*) from t1 as x1
+join t1 as x2 on x2.a = x1.c and x1.b < 2 
+join t1 as x3 on x3.a = x1.c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	x1	ALL	NULL	NULL	NULL	NULL	100	Parent of 3 pushed join@1; Using where with pushed condition
+1	SIMPLE	x2	ref	PRIMARY	PRIMARY	4	test.x1.c	1	Child of 'x1' in pushed join@1
+1	SIMPLE	x3	ref	PRIMARY	PRIMARY	4	test.x1.c	1	Child of 'x1' in pushed join@1
+select count(*) from t1 as x1
+join t1 as x2 on x2.a = x1.c and x1.b < 2 
+join t1 as x3 on x3.a = x1.c;
+count(*)
+20000
 drop table t1;
+drop table tx;
 create table t1 (a int, b int, primary key(a) using hash) engine = ndb;
 insert into t1 values (1, 2);
 insert into t1 values (2, 3);
@@ -5169,13 +5189,13 @@ and spj_counts_at_end.counter_name <> 'L
        and spj_counts_at_end.counter_name <> 'SCAN_BATCHES_RETURNED';
 counter_name	spj_counts_at_end.val - spj_counts_at_startup.val
 CONST_PRUNED_RANGE_SCANS_RECEIVED	6
-LOCAL_TABLE_SCANS_SENT	234
-PRUNED_RANGE_SCANS_RECEIVED	21
+LOCAL_TABLE_SCANS_SENT	236
+PRUNED_RANGE_SCANS_RECEIVED	25
 RANGE_SCANS_RECEIVED	720
 READS_NOT_FOUND	6616
 READS_RECEIVED	52
-SCAN_ROWS_RETURNED	94389
-TABLE_SCANS_RECEIVED	234
+SCAN_ROWS_RETURNED	95391
+TABLE_SCANS_RECEIVED	236
 select sum(spj_counts_at_end.val - spj_counts_at_startup.val) as 'LOCAL+REMOTE READS_SENT'
        from spj_counts_at_end, spj_counts_at_startup 
 where spj_counts_at_end.counter_name = spj_counts_at_startup.counter_name
@@ -5186,15 +5206,15 @@ LOCAL+REMOTE READS_SENT
 drop table spj_counts_at_startup;
 drop table spj_counts_at_end;
 scan_count
-2562
+2565
 pruned_scan_count
 8
 sorted_scan_count
 10
 pushed_queries_defined
-381
+383
 pushed_queries_dropped
 11
 pushed_queries_executed
-533
+534
 set ndb_join_pushdown = @save_ndb_join_pushdown;

=== modified file 'mysql-test/suite/ndb/t/ndb_join_pushdown.test'
--- a/mysql-test/suite/ndb/t/ndb_join_pushdown.test	2011-06-07 12:26:02 +0000
+++ b/mysql-test/suite/ndb/t/ndb_join_pushdown.test	2011-06-16 09:17:41 +0000
@@ -1007,8 +1007,28 @@ select count(*) from t1 
   join t1 as t2 on t2.a = t1.c 
   join t1 as t3 on t3.a = t1.c;
 
-drop table t1;
+# Test bushy join with pruned scan and larger result set.
+
+CREATE TABLE tx (
+  a int NOT NULL,
+  PRIMARY KEY (`a`)
+);
+
+delete from t1;
 
+insert into tx values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
+
+insert into t1 select 1, x1.a * 10+x2.a, 1, 0 from tx as x1 cross join tx as x2;
+
+explain select count(*) from t1 as x1
+  join t1 as x2 on x2.a = x1.c and x1.b < 2 
+  join t1 as x3 on x3.a = x1.c;
+select count(*) from t1 as x1
+  join t1 as x2 on x2.a = x1.c and x1.b < 2 
+  join t1 as x3 on x3.a = x1.c;
+
+drop table t1;
+drop table tx;
 
 # pushed mrr does not yet handle multiple PK operations in same transaction
 # Need 6.0 result handling stuff to simplify result handling


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