3602 Ole John Aske 2012-03-16
Added test for confirming that queries referring tables with
user deffined partitions should not be 'join pushed'
modified:
mysql-test/suite/ndb/r/ndb_join_pushdown_default.result
mysql-test/suite/ndb/t/ndb_join_pushdown.inc
3601 Maitrayi Sabaratnam 2012-03-15
Fixing compilor warnings
modified:
storage/ndb/src/kernel/blocks/dbspj/DbspjMain.cpp
=== modified file 'mysql-test/suite/ndb/r/ndb_join_pushdown_default.result'
--- a/mysql-test/suite/ndb/r/ndb_join_pushdown_default.result 2011-11-24 10:17:09 +0000
+++ b/mysql-test/suite/ndb/r/ndb_join_pushdown_default.result 2012-03-16 08:37:04 +0000
@@ -2099,6 +2099,77 @@ count(*)
20000
drop table t1;
drop table tx;
+create table t1 (
+a int not null,
+b int not null,
+c int not null,
+d int not null,
+primary key (`a`,`b`)
+) engine=ndbcluster partition by key(a);
+insert into t1 values
+(1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4),
+(1,2,5,1), (1,3,1,2), (1,4,2,3),
+(2,1,3,4), (2,3,4,5), (2,4,5,1),
+(3,1,1,2), (3,2,2,3), (3,4,3,4),
+(4,1,4,5), (4,2,5,1), (4,3,1,2);
+explain extended
+select *
+from t1
+join t1 as t2 on t2.a = t1.b and t2.b = t1.c;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 16 100.00 Parent of 2 pushed join@1
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 Child of 't1' in pushed join@1
+Warnings:
+Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t1` join `test`.`t1` `t2` where ((`test`.`t2`.`b` = `test`.`t1`.`c`) and (`test`.`t2`.`a` = `test`.`t1`.`b`))
+set new=on;
+alter table t1 partition by hash(a);
+explain extended
+select *
+from t1
+join t1 as t2 on t2.a = t1.b and t2.b = t1.c;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 16 100.00
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00
+Warnings:
+Note 9999 Table 't1' is not pushable: has user defined partioning
+Note 9999 Table 't2' is not pushable: has user defined partioning
+Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t1` join `test`.`t1` `t2` where ((`test`.`t2`.`b` = `test`.`t1`.`c`) and (`test`.`t2`.`a` = `test`.`t1`.`b`))
+alter table t1 partition by list(a) (
+partition p1 values in (1),
+partition p2 values in (2),
+partition p3 values in (3),
+partition p4 values in (4)
+);
+explain extended
+select *
+from t1
+join t1 as t2 on t2.a = t1.b and t2.b = t1.c;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 16 100.00
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00
+Warnings:
+Note 9999 Table 't1' is not pushable: has user defined partioning
+Note 9999 Table 't2' is not pushable: has user defined partioning
+Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t1` join `test`.`t1` `t2` where ((`test`.`t2`.`b` = `test`.`t1`.`c`) and (`test`.`t2`.`a` = `test`.`t1`.`b`))
+alter table t1 partition by range(a) partitions 4 (
+partition p1 values less than (0),
+partition p2 values less than (2),
+partition p3 values less than (4),
+partition p4 values less than (99999)
+);
+explain extended
+select *
+from t1
+join t1 as t2 on t2.a = t1.b and t2.b = t1.c;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 16 100.00
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00
+Warnings:
+Note 9999 Table 't1' is not pushable: has user defined partioning
+Note 9999 Table 't2' is not pushable: has user defined partioning
+Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t1` join `test`.`t1` `t2` where ((`test`.`t2`.`b` = `test`.`t1`.`c`) and (`test`.`t2`.`a` = `test`.`t1`.`b`))
+drop table t1;
+set new=default;
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);
@@ -5556,7 +5627,7 @@ where new.variable_name = old.variable_n
order by new.variable_name;
variable_name new.variable_value - old.variable_value
NDB_PRUNED_SCAN_COUNT 8
-NDB_PUSHED_QUERIES_DEFINED 405
+NDB_PUSHED_QUERIES_DEFINED 406
NDB_PUSHED_QUERIES_DROPPED 8
NDB_PUSHED_QUERIES_EXECUTED 550
NDB_SORTED_SCAN_COUNT 10
=== modified file 'mysql-test/suite/ndb/t/ndb_join_pushdown.inc'
--- a/mysql-test/suite/ndb/t/ndb_join_pushdown.inc 2011-11-24 10:17:09 +0000
+++ b/mysql-test/suite/ndb/t/ndb_join_pushdown.inc 2012-03-16 08:37:04 +0000
@@ -1055,6 +1055,76 @@ connection ddl;
drop table t1;
drop table tx;
+# Test user defined partition not being pushed
+#
+# Note: User defined partitions are handled
+# by the SQL layer, and as such are unknown
+# to the NDB datanodes.
+#
+
+connection spj;
+create table t1 (
+ a int not null,
+ b int not null,
+ c int not null,
+ d int not null,
+ primary key (`a`,`b`)
+) engine=ndbcluster partition by key(a);
+
+connection spj;
+insert into t1 values
+(1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4),
+(1,2,5,1), (1,3,1,2), (1,4,2,3),
+(2,1,3,4), (2,3,4,5), (2,4,5,1),
+(3,1,1,2), (3,2,2,3), (3,4,3,4),
+(4,1,4,5), (4,2,5,1), (4,3,1,2);
+
+# Only this query('partition by key') should be pushed
+explain extended
+select *
+from t1
+join t1 as t2 on t2.a = t1.b and t2.b = t1.c;
+
+connection ddl;
+set new=on;
+alter table t1 partition by hash(a);
+
+explain extended
+select *
+from t1
+join t1 as t2 on t2.a = t1.b and t2.b = t1.c;
+
+connection ddl;
+alter table t1 partition by list(a) (
+ partition p1 values in (1),
+ partition p2 values in (2),
+ partition p3 values in (3),
+ partition p4 values in (4)
+);
+
+explain extended
+select *
+from t1
+join t1 as t2 on t2.a = t1.b and t2.b = t1.c;
+
+connection ddl;
+alter table t1 partition by range(a) partitions 4 (
+ partition p1 values less than (0),
+ partition p2 values less than (2),
+ partition p3 values less than (4),
+ partition p4 values less than (99999)
+);
+
+explain extended
+select *
+from t1
+join t1 as t2 on t2.a = t1.b and t2.b = t1.c;
+
+connection ddl;
+drop table t1;
+set new=default;
+
+
# pushed mrr does not yet handle multiple PK operations in same transaction
# Need 6.0 result handling stuff to simplify result handling
# *** join push is currently dissabled for these ****
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-5.1-telco-7.0-spj-scan-vs-scan branch(ole.john.aske:3601 to 3602) | Ole John Aske | 16 Mar |
