From: Ole John Aske Date: February 28 2011 12:27pm Subject: bzr push into mysql-5.1-telco-7.0-spj-scan-vs-scan branch (ole.john.aske:3443 to 3444) List-Archive: http://lists.mysql.com/commits/132111 Message-Id: <20110228122706.988FA223@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3444 Ole John Aske 2011-02-28 [merge] merge from 'mysql-5.1-telc0-7.0' to SPJ branch @ mysql-test/suite/ndb/r/ndb_join_pushdown.result Lots of expected result changed as result of merge of 'Bug#11804277' (Heuristic statistics added to ::records_in_range) modified: mysql-test/suite/ndb/r/ndb_condition_pushdown.result mysql-test/suite/ndb/r/ndb_index.result mysql-test/suite/ndb/r/ndb_index_unique.result mysql-test/suite/ndb/r/ndb_join_pushdown.result mysql-test/suite/ndb/r/ndb_read_multi_range.result mysql-test/suite/ndb/r/ndb_statistics.result mysql-test/suite/ndb/t/ndb_statistics.test sql/ha_ndbcluster.cc storage/ndb/include/kernel/signaldata/TcIndx.hpp storage/ndb/include/ndbapi/NdbTransaction.hpp storage/ndb/src/common/debugger/signaldata/SignalDataPrint.cpp storage/ndb/src/common/debugger/signaldata/TcIndx.cpp storage/ndb/src/kernel/blocks/backup/Backup.cpp storage/ndb/src/kernel/blocks/lgman.cpp storage/ndb/src/ndbapi/NdbTransaction.cpp storage/ndb/src/ndbapi/Ndbif.cpp storage/ndb/src/ndbapi/TransporterFacade.cpp storage/ndb/src/ndbapi/TransporterFacade.hpp storage/ndb/src/ndbapi/trp_client.cpp storage/ndb/src/ndbapi/trp_client.hpp 3443 Ole John Aske 2011-02-28 A few query plans changed as result of merge from main telco containing 'greedy optimizer patches'. Added 'straight_join' to these testcases to make then produce the original query plan. (Which is critical for having the test to test what it was supposed to test) modified: mysql-test/suite/ndb/t/ndb_join_pushdown.test === modified file 'mysql-test/suite/ndb/r/ndb_condition_pushdown.result' --- a/mysql-test/suite/ndb/r/ndb_condition_pushdown.result 2011-01-17 14:33:23 +0000 +++ b/mysql-test/suite/ndb/r/ndb_condition_pushdown.result 2011-02-28 12:25:52 +0000 @@ -1910,7 +1910,7 @@ insert into NodeAlias VALUES(null, 8 , ' 12:22:26'); explain select * from NodeAlias where (aliasKey LIKE '491803%'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NodeAlias range NodeAlias_KeyIndex NodeAlias_KeyIndex 48 NULL 10 Using where with pushed condition +1 SIMPLE NodeAlias range NodeAlias_KeyIndex NodeAlias_KeyIndex 48 NULL 2 Using where with pushed condition select * from NodeAlias where (aliasKey LIKE '491803%') order by id; id nodeId displayName aliasKey objectVersion changed 7 8 491803% 491803% 0 2008-03-10 12:22:26 @@ -2258,7 +2258,7 @@ join tx as t2 on tx.a = t2.c and tx.b = where t2.a = 4 group by t2.c; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 ref PRIMARY PRIMARY 4 const 10 100.00 Using where; Using filesort +1 SIMPLE t2 ref PRIMARY PRIMARY 4 const 2 100.00 Using where; Using filesort 1 SIMPLE tx eq_ref PRIMARY PRIMARY 8 test.t2.c,test.t2.d 1 100.00 Warnings: Note 1644 Table 't2' is not pushable: GROUP BY cannot be done using index on grouped columns. @@ -2278,7 +2278,7 @@ join tx as t2 on tx.a = t2.c and tx.b = where t2.a = 4 group by t2.c; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 ref PRIMARY PRIMARY 4 const 10 100.00 Using where; Using filesort +1 SIMPLE t2 ref PRIMARY PRIMARY 4 const 2 100.00 Using where; Using filesort 1 SIMPLE tx eq_ref PRIMARY PRIMARY 8 test.t2.c,test.t2.d 1 100.00 Warnings: Note 1644 Table 't2' is not pushable: GROUP BY cannot be done using index on grouped columns. === modified file 'mysql-test/suite/ndb/r/ndb_index.result' --- a/mysql-test/suite/ndb/r/ndb_index.result 2011-01-06 21:19:05 +0000 +++ b/mysql-test/suite/ndb/r/ndb_index.result 2011-02-28 12:25:52 +0000 @@ -306,7 +306,7 @@ explain select i,vc from t1 where i>=1 or vc > '0'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index_merge PRIMARY,i1,i2 i1,i2 5,18 NULL 20 Using sort_union(i1,i2); Using where with pushed condition +1 SIMPLE t1 index_merge PRIMARY,i1,i2 i1,i2 5,18 NULL 6 Using sort_union(i1,i2); Using where with pushed condition select i,vc from t1 where i>=1 or vc > '0'; i vc @@ -350,7 +350,7 @@ explain select i,vc from t2 where i>=1 or vc > '0'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index_merge i1,i2 i1,i2 5,19 NULL 20 Using sort_union(i1,i2); Using where with pushed condition +1 SIMPLE t2 index_merge i1,i2 i1,i2 5,19 NULL 6 Using sort_union(i1,i2); Using where with pushed condition select i,vc from t2 where i>=1 or vc > '0'; i vc === modified file 'mysql-test/suite/ndb/r/ndb_index_unique.result' --- a/mysql-test/suite/ndb/r/ndb_index_unique.result 2011-01-21 13:56:43 +0000 +++ b/mysql-test/suite/ndb/r/ndb_index_unique.result 2011-02-28 12:25:52 +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 10 Using where with pushed condition +1 SIMPLE t2 range PRIMARY,b PRIMARY 4 NULL 2 Using where with pushed condition 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_join_pushdown.result' --- a/mysql-test/suite/ndb/r/ndb_join_pushdown.result 2011-02-23 13:09:10 +0000 +++ b/mysql-test/suite/ndb/r/ndb_join_pushdown.result 2011-02-28 12:25:52 +0000 @@ -46,18 +46,20 @@ a b c d a b c d 4 3 1 2 3 1 1 2 4 4 4 4 4 4 4 4 explain extended -select count(*) +select straight_join count(*) from t1 as x1 join t1 as x2 on x1.d > x2.a + 1000 join t1 as x3 on x1.c=x3.a and x1.d=x3.b; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE x1 ALL NULL NULL NULL NULL 16 100.00 Parent of 2 pushed join@1 -1 SIMPLE x3 eq_ref PRIMARY PRIMARY 8 test.x1.c,test.x1.d 1 100.00 Child of pushed join@1 +1 SIMPLE x1 ALL NULL NULL NULL NULL 16 100.00 1 SIMPLE x2 ALL NULL NULL NULL NULL 16 100.00 Using where; Using join buffer +1 SIMPLE x3 eq_ref PRIMARY PRIMARY 8 test.x1.c,test.x1.d 1 100.00 Warnings: Note 1644 Can't push table 'x2' as child, 'type' must be a 'ref' access -Note 1003 select count(0) AS `count(*)` from `test`.`t1` `x1` join `test`.`t1` `x2` join `test`.`t1` `x3` where ((`test`.`x3`.`b` = `test`.`x1`.`d`) and (`test`.`x3`.`a` = `test`.`x1`.`c`) and (`test`.`x1`.`d` > (`test`.`x2`.`a` + 1000))) -select count(*) +Note 1644 Cannot push table 'x3' as child of table 'x1'. Doing so would prevent using join buffer for table 'x2'. +Note 1644 Cannot push table 'x3' as child of 'x2', since it referes to column 'x1.c' which will be stored in a join buffer. +Note 1003 select straight_join count(0) AS `count(*)` from `test`.`t1` `x1` join `test`.`t1` `x2` join `test`.`t1` `x3` where ((`test`.`x3`.`b` = `test`.`x1`.`d`) and (`test`.`x3`.`a` = `test`.`x1`.`c`) and (`test`.`x1`.`d` > (`test`.`x2`.`a` + 1000))) +select straight_join count(*) from t1 as x1 join t1 as x2 on x1.d > x2.a + 1000 join t1 as x3 on x1.c=x3.a and x1.d=x3.b; @@ -69,7 +71,7 @@ join t1 as x2 on x1.a=1 and x1.c=x2.a an join t1 as x3 join t1 as x4 where x4.a=x3.c and x4.b=x1.d; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE x1 ref PRIMARY PRIMARY 4 const 10 100.00 Parent of 2 pushed join@1 +1 SIMPLE x1 ref PRIMARY PRIMARY 4 const 3 100.00 Parent of 2 pushed join@1 1 SIMPLE x2 eq_ref PRIMARY PRIMARY 8 test.x1.c,test.x1.d 1 100.00 Child of pushed join@1 1 SIMPLE x3 ALL NULL NULL NULL NULL 16 100.00 Using join buffer 1 SIMPLE x4 eq_ref PRIMARY PRIMARY 8 test.x3.c,test.x1.d 1 100.00 @@ -779,7 +781,7 @@ straight_join t1 as t2 on t2.a = t1.b+0 straight_join t1 as t3 on t3.a = t1.b and t3.b = t2.b where t1.a=1 and t1.d=1; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ref PRIMARY PRIMARY 4 const 10 100.00 Parent of 2 pushed join@1; Using where with pushed condition: (`test`.`t1`.`d` = 1) +1 SIMPLE t1 ref PRIMARY PRIMARY 4 const 3 100.00 Parent of 2 pushed join@1; Using where with pushed condition: (`test`.`t1`.`d` = 1) 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 func,test.t1.c 1 100.00 Using where 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t2.b 1 100.00 Child of pushed join@1; Using where Warnings: @@ -928,7 +930,7 @@ from t1 join t1 as t2 on t2.a = t1.c and t2.b = t1.d where t1.a > 2 and t1.d = 3; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 10 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`t1`.`d` = 3) and (`test`.`t1`.`a` > 2)) +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`t1`.`d` = 3) and (`test`.`t1`.`a` > 2)) 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.c,const 1 100.00 Child of 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`.`a` = `test`.`t1`.`c`) and (`test`.`t1`.`d` = 3) and (`test`.`t2`.`b` = 3) and (`test`.`t1`.`a` > 2)) @@ -1013,7 +1015,7 @@ join t1 as t3 on t3.a = t2.c and t3.b = where t1.a = 1 and t1.b = 1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 const PRIMARY PRIMARY 8 const,const 1 100.00 -1 SIMPLE t2 ref PRIMARY PRIMARY 4 const 10 100.00 +1 SIMPLE t2 ref PRIMARY PRIMARY 4 const 3 100.00 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t2.c,test.t2.d 1 100.00 Warnings: Note 1003 select '1' AS `a`,'1' AS `b`,'1' AS `c`,'1' AS `d`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t3`.`d` AS `d` from `test`.`t1` join `test`.`t1` `t2` join `test`.`t1` `t3` where ((`test`.`t2`.`a` = '1') and (`test`.`t3`.`b` = `test`.`t2`.`d`) and (`test`.`t3`.`a` = `test`.`t2`.`c`)) @@ -1034,7 +1036,7 @@ left join t1 as t3 on t3.a = t2.c and t3 where t1.a = 1 and t1.b = 1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 const PRIMARY PRIMARY 8 const,const 1 100.00 -1 SIMPLE t2 ref PRIMARY PRIMARY 4 const 10 100.00 +1 SIMPLE t2 ref PRIMARY PRIMARY 4 const 3 100.00 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t2.c,test.t2.d 1 100.00 Warnings: Note 1003 select '1' AS `a`,'1' AS `b`,'1' AS `c`,'1' AS `d`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t3`.`d` AS `d` from `test`.`t1` left join `test`.`t1` `t2` on((`test`.`t2`.`a` = '1')) left join `test`.`t1` `t3` on(((`test`.`t3`.`b` = `test`.`t2`.`d`) and (`test`.`t3`.`a` = `test`.`t2`.`c`))) where 1 @@ -1055,7 +1057,7 @@ from t1 as t2 join t1 as t3 on t3.a = t2.c and t3.b = t2.d where t2.a = 1; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 ref PRIMARY PRIMARY 4 const 10 100.00 Parent of 2 pushed join@1 +1 SIMPLE t2 ref PRIMARY PRIMARY 4 const 3 100.00 Parent of 2 pushed join@1 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t2.c,test.t2.d 1 100.00 Child of pushed join@1 Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t3`.`d` AS `d` from `test`.`t1` `t2` join `test`.`t1` `t3` where ((`test`.`t3`.`b` = `test`.`t2`.`d`) and (`test`.`t3`.`a` = `test`.`t2`.`c`) and (`test`.`t2`.`a` = 1)) @@ -1412,7 +1414,7 @@ select * from t1 x, t1 y where x.a <= 2 and y.a=x.d and y.b=x.b; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE x range PRIMARY PRIMARY 4 NULL 10 100.00 Parent of 2 pushed join@1; Using where with pushed condition: (`test`.`x`.`a` <= 2) +1 SIMPLE x range PRIMARY PRIMARY 4 NULL 3 100.00 Parent of 2 pushed join@1; Using where with pushed condition: (`test`.`x`.`a` <= 2) 1 SIMPLE y eq_ref PRIMARY PRIMARY 8 test.x.d,test.x.b 1 100.00 Child of pushed join@1 Warnings: Note 1003 select `test`.`x`.`a` AS `a`,`test`.`x`.`b` AS `b`,`test`.`x`.`c` AS `c`,`test`.`x`.`d` AS `d`,`test`.`y`.`a` AS `a`,`test`.`y`.`b` AS `b`,`test`.`y`.`c` AS `c`,`test`.`y`.`d` AS `d` from `test`.`t1` `x` join `test`.`t1` `y` where ((`test`.`y`.`b` = `test`.`x`.`b`) and (`test`.`y`.`a` = `test`.`x`.`d`) and (`test`.`x`.`a` <= 2)) @@ -1432,7 +1434,7 @@ select * from t1 x, t1 y where (x.a <= 2 or x.a > 3) and y.a=x.d and y.b=x.b; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE x range PRIMARY PRIMARY 4 NULL 20 80.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`x`.`a` <= 2) or (`test`.`x`.`a` > 3)) +1 SIMPLE x range PRIMARY PRIMARY 4 NULL 6 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`x`.`a` <= 2) or (`test`.`x`.`a` > 3)) 1 SIMPLE y eq_ref PRIMARY PRIMARY 8 test.x.d,test.x.b 1 100.00 Child of pushed join@1 Warnings: Note 1003 select `test`.`x`.`a` AS `a`,`test`.`x`.`b` AS `b`,`test`.`x`.`c` AS `c`,`test`.`x`.`d` AS `d`,`test`.`y`.`a` AS `a`,`test`.`y`.`b` AS `b`,`test`.`y`.`c` AS `c`,`test`.`y`.`d` AS `d` from `test`.`t1` `x` join `test`.`t1` `y` where ((`test`.`y`.`b` = `test`.`x`.`b`) and (`test`.`y`.`a` = `test`.`x`.`d`) and ((`test`.`x`.`a` <= 2) or (`test`.`x`.`a` > 3))) @@ -1455,7 +1457,7 @@ select * from t1 x, t1 y where (x.a >= 2 or x.a < 3) and y.a=x.d and y.b=x.b; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE x range PRIMARY PRIMARY 4 NULL 20 80.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`x`.`a` >= 2) or (`test`.`x`.`a` < 3)) +1 SIMPLE x range PRIMARY PRIMARY 4 NULL 6 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`x`.`a` >= 2) or (`test`.`x`.`a` < 3)) 1 SIMPLE y eq_ref PRIMARY PRIMARY 8 test.x.d,test.x.b 1 100.00 Child of pushed join@1 Warnings: Note 1003 select `test`.`x`.`a` AS `a`,`test`.`x`.`b` AS `b`,`test`.`x`.`c` AS `c`,`test`.`x`.`d` AS `d`,`test`.`y`.`a` AS `a`,`test`.`y`.`b` AS `b`,`test`.`y`.`c` AS `c`,`test`.`y`.`d` AS `d` from `test`.`t1` `x` join `test`.`t1` `y` where ((`test`.`y`.`b` = `test`.`x`.`b`) and (`test`.`y`.`a` = `test`.`x`.`d`) and ((`test`.`x`.`a` >= 2) or (`test`.`x`.`a` < 3))) @@ -1482,7 +1484,7 @@ select * from t1 x, t1 y where (x.a <= 2 or x.a in (0,5,4)) and y.a=x.d and y.b=x.b; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE x range PRIMARY PRIMARY 4 NULL 50 32.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`x`.`a` <= 2) or (`test`.`x`.`a` in (0,5,4))) +1 SIMPLE x range PRIMARY PRIMARY 4 NULL 15 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`x`.`a` <= 2) or (`test`.`x`.`a` in (0,5,4))) 1 SIMPLE y eq_ref PRIMARY PRIMARY 8 test.x.d,test.x.b 1 100.00 Child of pushed join@1 Warnings: Note 1003 select `test`.`x`.`a` AS `a`,`test`.`x`.`b` AS `b`,`test`.`x`.`c` AS `c`,`test`.`x`.`d` AS `d`,`test`.`y`.`a` AS `a`,`test`.`y`.`b` AS `b`,`test`.`y`.`c` AS `c`,`test`.`y`.`d` AS `d` from `test`.`t1` `x` join `test`.`t1` `y` where ((`test`.`y`.`b` = `test`.`x`.`b`) and (`test`.`y`.`a` = `test`.`x`.`d`) and ((`test`.`x`.`a` <= 2) or (`test`.`x`.`a` in (0,5,4)))) @@ -1839,7 +1841,7 @@ join t1 as t2 on t1.a = t2.c and t1.b = where t2.a = 4 group by t2.c; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 ref PRIMARY PRIMARY 4 const 10 100.00 Using where; Using filesort +1 SIMPLE t2 ref PRIMARY PRIMARY 4 const 3 100.00 Using where; Using filesort 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 8 test.t2.c,test.t2.d 1 100.00 Warnings: Note 1644 Table 't2' is not pushable: GROUP BY cannot be done using index on grouped columns. @@ -2118,7 +2120,7 @@ a3 b3 c3 d3 a3 b3 c3 d3 explain extended select * from t3 x, t3 y where x.d3>=31 and y.d3=x.b3; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE x range t3_d3 t3_d3 4 NULL 10 30.00 Parent of 2 pushed join@1; Using where with pushed condition: (`test`.`x`.`d3` >= 31) +1 SIMPLE x range t3_d3 t3_d3 4 NULL 3 100.00 Parent of 2 pushed join@1; Using where with pushed condition: (`test`.`x`.`d3` >= 31) 1 SIMPLE y eq_ref t3_d3 t3_d3 4 test.x.b3 1 100.00 Child of pushed join@1 Warnings: Note 1003 select `test`.`x`.`a3` AS `a3`,`test`.`x`.`b3` AS `b3`,`test`.`x`.`c3` AS `c3`,`test`.`x`.`d3` AS `d3`,`test`.`y`.`a3` AS `a3`,`test`.`y`.`b3` AS `b3`,`test`.`y`.`c3` AS `c3`,`test`.`y`.`d3` AS `d3` from `test`.`t3` `x` join `test`.`t3` `y` where ((`test`.`y`.`d3` = `test`.`x`.`b3`) and (`test`.`x`.`d3` >= 31)) @@ -2234,7 +2236,7 @@ left outer join t3 as t2 on t2.d3 = t1.a left outer join t3 as t3 on t3.a3 = t2.d3 where t1.d3 >= 47; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range d3 d3 5 NULL 10 40.00 Parent of 3 pushed join@1; Using where with pushed condition: (`test`.`t1`.`d3` >= 47) +1 SIMPLE t1 range d3 d3 5 NULL 3 100.00 Parent of 3 pushed join@1; Using where with pushed condition: (`test`.`t1`.`d3` >= 47) 1 SIMPLE t2 ref d3 d3 5 test.t1.a3 1 100.00 Child of pushed join@1 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.d3 1 100.00 Child of pushed join@1 Warnings: @@ -2269,7 +2271,7 @@ left outer join t3 as t2 on t2.d3 = t1.a left outer join t3 as t3 on t3.a3 = t2.d3 where t1.d3 is not null; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range d3 d3 5 NULL 10 40.00 Parent of 3 pushed join@1; Using where with pushed condition: (`test`.`t1`.`d3` is not null) +1 SIMPLE t1 range d3 d3 5 NULL 3 100.00 Parent of 3 pushed join@1; Using where with pushed condition: (`test`.`t1`.`d3` is not null) 1 SIMPLE t2 ref d3 d3 5 test.t1.a3 1 100.00 Child of pushed join@1 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.d3 1 100.00 Child of pushed join@1 Warnings: @@ -2854,7 +2856,7 @@ insert into t3 values (0x3f, 0x1f, 3, 0x explain extended select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3="63"; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE x ref PRIMARY PRIMARY 4 const 10 100.00 Parent of 2 pushed join@1 +1 SIMPLE x ref PRIMARY PRIMARY 4 const 2 100.00 Parent of 2 pushed join@1 1 SIMPLE y eq_ref PRIMARY PRIMARY 5 test.x.d3,const 1 100.00 Child of pushed join@1 Warnings: Note 1003 select `test`.`x`.`a3` AS `a3`,`test`.`x`.`b3` AS `b3`,`test`.`x`.`c3` AS `c3`,`test`.`x`.`d3` AS `d3`,`test`.`y`.`a3` AS `a3`,`test`.`y`.`b3` AS `b3`,`test`.`y`.`c3` AS `c3`,`test`.`y`.`d3` AS `d3` from `test`.`t3` `x` join `test`.`t3` `y` where ((`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f) and (`test`.`y`.`b3` = '63')) @@ -2870,7 +2872,7 @@ insert into t3 values (0x3f, 0x1f, 3, 0x explain extended select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE x ref PRIMARY PRIMARY 4 const 10 100.00 Parent of 2 pushed join@1 +1 SIMPLE x ref PRIMARY PRIMARY 4 const 2 100.00 Parent of 2 pushed join@1 1 SIMPLE y eq_ref PRIMARY PRIMARY 5 test.x.d3,const 1 100.00 Child of pushed join@1 Warnings: Note 1003 select `test`.`x`.`a3` AS `a3`,`test`.`x`.`b3` AS `b3`,`test`.`x`.`c3` AS `c3`,`test`.`x`.`d3` AS `d3`,`test`.`y`.`a3` AS `a3`,`test`.`y`.`b3` AS `b3`,`test`.`y`.`c3` AS `c3`,`test`.`y`.`d3` AS `d3` from `test`.`t3` `x` join `test`.`t3` `y` where ((`test`.`y`.`b3` = (60 + 3)) and (`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f)) @@ -2886,7 +2888,7 @@ insert into t3 values (0x3f, 0x1f, 3, 0x explain extended select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE x ref PRIMARY PRIMARY 4 const 10 100.00 Parent of 2 pushed join@1 +1 SIMPLE x ref PRIMARY PRIMARY 4 const 2 100.00 Parent of 2 pushed join@1 1 SIMPLE y eq_ref PRIMARY PRIMARY 6 test.x.d3,const 1 100.00 Child of pushed join@1 Warnings: Note 1003 select `test`.`x`.`a3` AS `a3`,`test`.`x`.`b3` AS `b3`,`test`.`x`.`c3` AS `c3`,`test`.`x`.`d3` AS `d3`,`test`.`y`.`a3` AS `a3`,`test`.`y`.`b3` AS `b3`,`test`.`y`.`c3` AS `c3`,`test`.`y`.`d3` AS `d3` from `test`.`t3` `x` join `test`.`t3` `y` where ((`test`.`y`.`b3` = (60 + 3)) and (`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f)) @@ -2902,7 +2904,7 @@ insert into t3 values (0x3f, 0x1f, 3, 0x explain extended select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE x ref PRIMARY PRIMARY 4 const 10 100.00 Parent of 2 pushed join@1 +1 SIMPLE x ref PRIMARY PRIMARY 4 const 2 100.00 Parent of 2 pushed join@1 1 SIMPLE y eq_ref PRIMARY PRIMARY 6 test.x.d3,const 1 100.00 Child of pushed join@1 Warnings: Note 1003 select `test`.`x`.`a3` AS `a3`,`test`.`x`.`b3` AS `b3`,`test`.`x`.`c3` AS `c3`,`test`.`x`.`d3` AS `d3`,`test`.`y`.`a3` AS `a3`,`test`.`y`.`b3` AS `b3`,`test`.`y`.`c3` AS `c3`,`test`.`y`.`d3` AS `d3` from `test`.`t3` `x` join `test`.`t3` `y` where ((`test`.`y`.`b3` = (60 + 3)) and (`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f)) @@ -2918,7 +2920,7 @@ insert into t3 values (0x3f, 0x1f, 3, 0x explain extended select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE x ref PRIMARY PRIMARY 4 const 10 100.00 Parent of 2 pushed join@1 +1 SIMPLE x ref PRIMARY PRIMARY 4 const 2 100.00 Parent of 2 pushed join@1 1 SIMPLE y eq_ref PRIMARY PRIMARY 7 test.x.d3,const 1 100.00 Child of pushed join@1 Warnings: Note 1003 select `test`.`x`.`a3` AS `a3`,`test`.`x`.`b3` AS `b3`,`test`.`x`.`c3` AS `c3`,`test`.`x`.`d3` AS `d3`,`test`.`y`.`a3` AS `a3`,`test`.`y`.`b3` AS `b3`,`test`.`y`.`c3` AS `c3`,`test`.`y`.`d3` AS `d3` from `test`.`t3` `x` join `test`.`t3` `y` where ((`test`.`y`.`b3` = (60 + 3)) and (`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f)) @@ -2934,7 +2936,7 @@ insert into t3 values (0x3f, 0x1f, 3, 0x explain extended select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE x ref PRIMARY PRIMARY 4 const 10 100.00 Parent of 2 pushed join@1 +1 SIMPLE x ref PRIMARY PRIMARY 4 const 2 100.00 Parent of 2 pushed join@1 1 SIMPLE y eq_ref PRIMARY PRIMARY 7 test.x.d3,const 1 100.00 Child of pushed join@1 Warnings: Note 1003 select `test`.`x`.`a3` AS `a3`,`test`.`x`.`b3` AS `b3`,`test`.`x`.`c3` AS `c3`,`test`.`x`.`d3` AS `d3`,`test`.`y`.`a3` AS `a3`,`test`.`y`.`b3` AS `b3`,`test`.`y`.`c3` AS `c3`,`test`.`y`.`d3` AS `d3` from `test`.`t3` `x` join `test`.`t3` `y` where ((`test`.`y`.`b3` = (60 + 3)) and (`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f)) @@ -2950,7 +2952,7 @@ insert into t3 values (0x3f, 0x1f, 3, 0x explain extended select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE x ref PRIMARY PRIMARY 4 const 10 100.00 Parent of 2 pushed join@1 +1 SIMPLE x ref PRIMARY PRIMARY 4 const 2 100.00 Parent of 2 pushed join@1 1 SIMPLE y eq_ref PRIMARY PRIMARY 8 test.x.d3,const 1 100.00 Child of pushed join@1 Warnings: Note 1003 select `test`.`x`.`a3` AS `a3`,`test`.`x`.`b3` AS `b3`,`test`.`x`.`c3` AS `c3`,`test`.`x`.`d3` AS `d3`,`test`.`y`.`a3` AS `a3`,`test`.`y`.`b3` AS `b3`,`test`.`y`.`c3` AS `c3`,`test`.`y`.`d3` AS `d3` from `test`.`t3` `x` join `test`.`t3` `y` where ((`test`.`y`.`b3` = (60 + 3)) and (`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f)) @@ -2966,7 +2968,7 @@ insert into t3 values (0x3f, 0x1f, 3, 0x explain extended select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE x ref PRIMARY PRIMARY 4 const 10 100.00 Parent of 2 pushed join@1 +1 SIMPLE x ref PRIMARY PRIMARY 4 const 2 100.00 Parent of 2 pushed join@1 1 SIMPLE y eq_ref PRIMARY PRIMARY 8 test.x.d3,const 1 100.00 Child of pushed join@1 Warnings: Note 1003 select `test`.`x`.`a3` AS `a3`,`test`.`x`.`b3` AS `b3`,`test`.`x`.`c3` AS `c3`,`test`.`x`.`d3` AS `d3`,`test`.`y`.`a3` AS `a3`,`test`.`y`.`b3` AS `b3`,`test`.`y`.`c3` AS `c3`,`test`.`y`.`d3` AS `d3` from `test`.`t3` `x` join `test`.`t3` `y` where ((`test`.`y`.`b3` = (60 + 3)) and (`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f)) @@ -2982,7 +2984,7 @@ insert into t3 values (0x3f, 0x1f, 3, 0x explain extended select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE x ref PRIMARY PRIMARY 4 const 10 100.00 Parent of 2 pushed join@1 +1 SIMPLE x ref PRIMARY PRIMARY 4 const 2 100.00 Parent of 2 pushed join@1 1 SIMPLE y eq_ref PRIMARY PRIMARY 12 test.x.d3,const 1 100.00 Child of pushed join@1 Warnings: Note 1003 select `test`.`x`.`a3` AS `a3`,`test`.`x`.`b3` AS `b3`,`test`.`x`.`c3` AS `c3`,`test`.`x`.`d3` AS `d3`,`test`.`y`.`a3` AS `a3`,`test`.`y`.`b3` AS `b3`,`test`.`y`.`c3` AS `c3`,`test`.`y`.`d3` AS `d3` from `test`.`t3` `x` join `test`.`t3` `y` where ((`test`.`y`.`b3` = 63) and (`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f)) @@ -2998,7 +3000,7 @@ insert into t3 values (0x3f, 0x1f, 3, 0x explain extended select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE x ref PRIMARY PRIMARY 4 const 10 100.00 Parent of 2 pushed join@1 +1 SIMPLE x ref PRIMARY PRIMARY 4 const 2 100.00 Parent of 2 pushed join@1 1 SIMPLE y eq_ref PRIMARY PRIMARY 12 test.x.d3,const 1 100.00 Child of pushed join@1 Warnings: Note 1003 select `test`.`x`.`a3` AS `a3`,`test`.`x`.`b3` AS `b3`,`test`.`x`.`c3` AS `c3`,`test`.`x`.`d3` AS `d3`,`test`.`y`.`a3` AS `a3`,`test`.`y`.`b3` AS `b3`,`test`.`y`.`c3` AS `c3`,`test`.`y`.`d3` AS `d3` from `test`.`t3` `x` join `test`.`t3` `y` where ((`test`.`y`.`b3` = 63) and (`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f)) @@ -3014,7 +3016,7 @@ insert into t3 values (0x3f, 0, 3, 0x3f) explain extended select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=1; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE x ref PRIMARY PRIMARY 4 const 10 100.00 Parent of 2 pushed join@1 +1 SIMPLE x ref PRIMARY PRIMARY 4 const 2 100.00 Parent of 2 pushed join@1 1 SIMPLE y eq_ref PRIMARY PRIMARY 5 test.x.d3,const 1 100.00 Child of pushed join@1 Warnings: Note 1003 select `test`.`x`.`a3` AS `a3`,`test`.`x`.`b3` AS `b3`,`test`.`x`.`c3` AS `c3`,`test`.`x`.`d3` AS `d3`,`test`.`y`.`a3` AS `a3`,`test`.`y`.`b3` AS `b3`,`test`.`y`.`c3` AS `c3`,`test`.`y`.`d3` AS `d3` from `test`.`t3` `x` join `test`.`t3` `y` where ((`test`.`y`.`b3` = 1) and (`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f)) @@ -3030,7 +3032,7 @@ insert into t3 values (0x3f, 0.50, 3, 0x explain extended select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=3.0; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE x ref PRIMARY PRIMARY 4 const 10 100.00 Parent of 2 pushed join@1 +1 SIMPLE x ref PRIMARY PRIMARY 4 const 2 100.00 Parent of 2 pushed join@1 1 SIMPLE y eq_ref PRIMARY PRIMARY 8 test.x.d3,const 1 100.00 Child of pushed join@1; Using where with pushed condition: (`test`.`y`.`b3` = 3.0) Warnings: Note 1003 select `test`.`x`.`a3` AS `a3`,`test`.`x`.`b3` AS `b3`,`test`.`x`.`c3` AS `c3`,`test`.`x`.`d3` AS `d3`,`test`.`y`.`a3` AS `a3`,`test`.`y`.`b3` AS `b3`,`test`.`y`.`c3` AS `c3`,`test`.`y`.`d3` AS `d3` from `test`.`t3` `x` join `test`.`t3` `y` where ((`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f) and (`test`.`y`.`b3` = 3.0)) @@ -3046,7 +3048,7 @@ insert into t3 values (0x3f, 0.50, 3, 0x explain extended select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=3.0; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE x ref PRIMARY PRIMARY 4 const 10 100.00 Parent of 2 pushed join@1 +1 SIMPLE x ref PRIMARY PRIMARY 4 const 2 100.00 Parent of 2 pushed join@1 1 SIMPLE y eq_ref PRIMARY PRIMARY 8 test.x.d3,const 1 100.00 Child of pushed join@1; Using where with pushed condition: (`test`.`y`.`b3` = 3.0) Warnings: Note 1003 select `test`.`x`.`a3` AS `a3`,`test`.`x`.`b3` AS `b3`,`test`.`x`.`c3` AS `c3`,`test`.`x`.`d3` AS `d3`,`test`.`y`.`a3` AS `a3`,`test`.`y`.`b3` AS `b3`,`test`.`y`.`c3` AS `c3`,`test`.`y`.`d3` AS `d3` from `test`.`t3` `x` join `test`.`t3` `y` where ((`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f) and (`test`.`y`.`b3` = 3.0)) @@ -3062,7 +3064,7 @@ insert into t3 values (0x3f, 0.50, 3, 0x explain extended select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=3.14; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE x ref PRIMARY PRIMARY 4 const 10 100.00 Parent of 2 pushed join@1 +1 SIMPLE x ref PRIMARY PRIMARY 4 const 2 100.00 Parent of 2 pushed join@1 1 SIMPLE y eq_ref PRIMARY PRIMARY 12 test.x.d3,const 1 100.00 Child of pushed join@1 Warnings: Note 1003 select `test`.`x`.`a3` AS `a3`,`test`.`x`.`b3` AS `b3`,`test`.`x`.`c3` AS `c3`,`test`.`x`.`d3` AS `d3`,`test`.`y`.`a3` AS `a3`,`test`.`y`.`b3` AS `b3`,`test`.`y`.`c3` AS `c3`,`test`.`y`.`d3` AS `d3` from `test`.`t3` `x` join `test`.`t3` `y` where ((`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f) and (`test`.`y`.`b3` = 3.14)) @@ -3078,7 +3080,7 @@ insert into t3 values (0x3f, 0.50, 3, 0x explain extended select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=3.14; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE x ref PRIMARY PRIMARY 4 const 10 100.00 Parent of 2 pushed join@1 +1 SIMPLE x ref PRIMARY PRIMARY 4 const 2 100.00 Parent of 2 pushed join@1 1 SIMPLE y eq_ref PRIMARY PRIMARY 12 test.x.d3,const 1 100.00 Child of pushed join@1 Warnings: Note 1003 select `test`.`x`.`a3` AS `a3`,`test`.`x`.`b3` AS `b3`,`test`.`x`.`c3` AS `c3`,`test`.`x`.`d3` AS `d3`,`test`.`y`.`a3` AS `a3`,`test`.`y`.`b3` AS `b3`,`test`.`y`.`c3` AS `c3`,`test`.`y`.`d3` AS `d3` from `test`.`t3` `x` join `test`.`t3` `y` where ((`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f) and (`test`.`y`.`b3` = 3.14)) @@ -3094,7 +3096,7 @@ insert into t3 values (0x3f, 0x1f, 3, 0x explain extended select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=63; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE x ref PRIMARY PRIMARY 4 const 10 100.00 Parent of 2 pushed join@1 +1 SIMPLE x ref PRIMARY PRIMARY 4 const 2 100.00 Parent of 2 pushed join@1 1 SIMPLE y eq_ref PRIMARY PRIMARY 9 test.x.d3,const 1 100.00 Child of pushed join@1 Warnings: Note 1003 select `test`.`x`.`a3` AS `a3`,`test`.`x`.`b3` AS `b3`,`test`.`x`.`c3` AS `c3`,`test`.`x`.`d3` AS `d3`,`test`.`y`.`a3` AS `a3`,`test`.`y`.`b3` AS `b3`,`test`.`y`.`c3` AS `c3`,`test`.`y`.`d3` AS `d3` from `test`.`t3` `x` join `test`.`t3` `y` where ((`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f) and (`test`.`y`.`b3` = 63)) @@ -3110,7 +3112,7 @@ insert into t3 values (0x3f, 0.50, 3, 0x explain extended select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=3.14; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE x ref PRIMARY PRIMARY 4 const 10 100.00 Parent of 2 pushed join@1 +1 SIMPLE x ref PRIMARY PRIMARY 4 const 2 100.00 Parent of 2 pushed join@1 1 SIMPLE y eq_ref PRIMARY PRIMARY 10 test.x.d3,const 1 100.00 Child of pushed join@1 Warnings: Note 1003 select `test`.`x`.`a3` AS `a3`,`test`.`x`.`b3` AS `b3`,`test`.`x`.`c3` AS `c3`,`test`.`x`.`d3` AS `d3`,`test`.`y`.`a3` AS `a3`,`test`.`y`.`b3` AS `b3`,`test`.`y`.`c3` AS `c3`,`test`.`y`.`d3` AS `d3` from `test`.`t3` `x` join `test`.`t3` `y` where ((`test`.`y`.`b3` = 3.14) and (`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f)) @@ -3126,7 +3128,7 @@ insert into t3 values (0x3f, '2000-02-29 explain extended select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='2000-02-28'; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE x ref PRIMARY PRIMARY 4 const 10 100.00 Parent of 2 pushed join@1 +1 SIMPLE x ref PRIMARY PRIMARY 4 const 2 100.00 Parent of 2 pushed join@1 1 SIMPLE y eq_ref PRIMARY PRIMARY 7 test.x.d3,const 1 100.00 Child of pushed join@1 Warnings: Note 1003 select `test`.`x`.`a3` AS `a3`,`test`.`x`.`b3` AS `b3`,`test`.`x`.`c3` AS `c3`,`test`.`x`.`d3` AS `d3`,`test`.`y`.`a3` AS `a3`,`test`.`y`.`b3` AS `b3`,`test`.`y`.`c3` AS `c3`,`test`.`y`.`d3` AS `d3` from `test`.`t3` `x` join `test`.`t3` `y` where ((`test`.`y`.`b3` = '2000-02-28') and (`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f)) @@ -3142,7 +3144,7 @@ insert into t3 values (0x3f, '2000-02-29 explain extended select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='2000-02-28 23:59'; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE x ref PRIMARY PRIMARY 4 const 10 100.00 Parent of 2 pushed join@1 +1 SIMPLE x ref PRIMARY PRIMARY 4 const 2 100.00 Parent of 2 pushed join@1 1 SIMPLE y eq_ref PRIMARY PRIMARY 12 test.x.d3,const 1 100.00 Child of pushed join@1 Warnings: Note 1003 select `test`.`x`.`a3` AS `a3`,`test`.`x`.`b3` AS `b3`,`test`.`x`.`c3` AS `c3`,`test`.`x`.`d3` AS `d3`,`test`.`y`.`a3` AS `a3`,`test`.`y`.`b3` AS `b3`,`test`.`y`.`c3` AS `c3`,`test`.`y`.`d3` AS `d3` from `test`.`t3` `x` join `test`.`t3` `y` where ((`test`.`y`.`b3` = '2000-02-28 23:59') and (`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f)) @@ -3158,7 +3160,7 @@ insert into t3 values (0x3f, '12:59:59', explain extended select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='23:59'; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE x ref PRIMARY PRIMARY 4 const 10 100.00 Parent of 2 pushed join@1 +1 SIMPLE x ref PRIMARY PRIMARY 4 const 2 100.00 Parent of 2 pushed join@1 1 SIMPLE y eq_ref PRIMARY PRIMARY 7 test.x.d3,const 1 100.00 Child of pushed join@1 Warnings: Note 1003 select `test`.`x`.`a3` AS `a3`,`test`.`x`.`b3` AS `b3`,`test`.`x`.`c3` AS `c3`,`test`.`x`.`d3` AS `d3`,`test`.`y`.`a3` AS `a3`,`test`.`y`.`b3` AS `b3`,`test`.`y`.`c3` AS `c3`,`test`.`y`.`d3` AS `d3` from `test`.`t3` `x` join `test`.`t3` `y` where ((`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f) and (`test`.`y`.`b3` = 235900)) @@ -3174,7 +3176,7 @@ insert into t3 values (0x3f, 'Doffen', 2 explain extended select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='Dole'; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE x ref PRIMARY PRIMARY 4 const 10 100.00 Parent of 2 pushed join@1 +1 SIMPLE x ref PRIMARY PRIMARY 4 const 2 100.00 Parent of 2 pushed join@1 1 SIMPLE y eq_ref PRIMARY PRIMARY 20 test.x.d3,const 1 100.00 Child of pushed join@1; Using where with pushed condition: (`test`.`y`.`b3` = 'Dole') Warnings: Note 1003 select `test`.`x`.`a3` AS `a3`,`test`.`x`.`b3` AS `b3`,`test`.`x`.`c3` AS `c3`,`test`.`x`.`d3` AS `d3`,`test`.`y`.`a3` AS `a3`,`test`.`y`.`b3` AS `b3`,`test`.`y`.`c3` AS `c3`,`test`.`y`.`d3` AS `d3` from `test`.`t3` `x` join `test`.`t3` `y` where ((`test`.`y`.`b3` = 'Dole') and (`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f)) @@ -3190,7 +3192,7 @@ insert into t3 values (0x3f, 'Doffen', 2 explain extended select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='Dole'; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE x ref PRIMARY PRIMARY 4 const 10 100.00 Parent of 2 pushed join@1 +1 SIMPLE x ref PRIMARY PRIMARY 4 const 2 100.00 Parent of 2 pushed join@1 1 SIMPLE y eq_ref PRIMARY PRIMARY 22 test.x.d3,const 1 100.00 Child of pushed join@1; Using where with pushed condition: (`test`.`y`.`b3` = 'Dole') Warnings: Note 1003 select `test`.`x`.`a3` AS `a3`,`test`.`x`.`b3` AS `b3`,`test`.`x`.`c3` AS `c3`,`test`.`x`.`d3` AS `d3`,`test`.`y`.`a3` AS `a3`,`test`.`y`.`b3` AS `b3`,`test`.`y`.`c3` AS `c3`,`test`.`y`.`d3` AS `d3` from `test`.`t3` `x` join `test`.`t3` `y` where ((`test`.`y`.`b3` = 'Dole') and (`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f)) @@ -3206,7 +3208,7 @@ insert into t3 values (0x3f, 'Doffen', 2 explain extended select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='Dole'; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE x ref PRIMARY PRIMARY 4 const 10 100.00 Parent of 2 pushed join@1 +1 SIMPLE x ref PRIMARY PRIMARY 4 const 2 100.00 Parent of 2 pushed join@1 1 SIMPLE y eq_ref PRIMARY PRIMARY 518 test.x.d3,const 1 100.00 Child of pushed join@1; Using where with pushed condition: (`test`.`y`.`b3` = 'Dole') Warnings: Note 1003 select `test`.`x`.`a3` AS `a3`,`test`.`x`.`b3` AS `b3`,`test`.`x`.`c3` AS `c3`,`test`.`x`.`d3` AS `d3`,`test`.`y`.`a3` AS `a3`,`test`.`y`.`b3` AS `b3`,`test`.`y`.`c3` AS `c3`,`test`.`y`.`d3` AS `d3` from `test`.`t3` `x` join `test`.`t3` `y` where ((`test`.`y`.`b3` = 'Dole') and (`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f)) @@ -3222,7 +3224,7 @@ insert into t3 values (0x3f, 'Doffen', 2 explain extended select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='Dole'; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE x ref PRIMARY PRIMARY 4 const 10 100.00 Parent of 2 pushed join@1 +1 SIMPLE x ref PRIMARY PRIMARY 4 const 2 100.00 Parent of 2 pushed join@1 1 SIMPLE y eq_ref PRIMARY PRIMARY 20 test.x.d3,const 1 100.00 Child of pushed join@1; Using where Warnings: Note 1003 select `test`.`x`.`a3` AS `a3`,`test`.`x`.`b3` AS `b3`,`test`.`x`.`c3` AS `c3`,`test`.`x`.`d3` AS `d3`,`test`.`y`.`a3` AS `a3`,`test`.`y`.`b3` AS `b3`,`test`.`y`.`c3` AS `c3`,`test`.`y`.`d3` AS `d3` from `test`.`t3` `x` join `test`.`t3` `y` where ((`test`.`y`.`b3` = 'Dole') and (`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f)) @@ -3237,7 +3239,7 @@ insert into t3 values (0x3f, 'Doffen', 2 explain extended select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='Dole'; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE x ref PRIMARY PRIMARY 4 const 10 100.00 Parent of 2 pushed join@1 +1 SIMPLE x ref PRIMARY PRIMARY 4 const 2 100.00 Parent of 2 pushed join@1 1 SIMPLE y eq_ref PRIMARY PRIMARY 22 test.x.d3,const 1 100.00 Child of pushed join@1; Using where Warnings: Note 1003 select `test`.`x`.`a3` AS `a3`,`test`.`x`.`b3` AS `b3`,`test`.`x`.`c3` AS `c3`,`test`.`x`.`d3` AS `d3`,`test`.`y`.`a3` AS `a3`,`test`.`y`.`b3` AS `b3`,`test`.`y`.`c3` AS `c3`,`test`.`y`.`d3` AS `d3` from `test`.`t3` `x` join `test`.`t3` `y` where ((`test`.`y`.`b3` = 'Dole') and (`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f)) @@ -3280,7 +3282,7 @@ Dole 47 2 47 Dole 47 2 47 explain extended select * from t3 x, t3 y where x.a3='Dole' and y.a3=x.a3 and y.b3=x.d3; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE x ref PRIMARY PRIMARY 18 const 10 100.00 Parent of 2 pushed join@1; Using where with pushed condition: (`test`.`x`.`a3` = 'Dole') +1 SIMPLE x ref PRIMARY PRIMARY 18 const 3 100.00 Parent of 2 pushed join@1; Using where with pushed condition: (`test`.`x`.`a3` = 'Dole') 1 SIMPLE y eq_ref PRIMARY PRIMARY 22 const,test.x.d3 1 100.00 Child of pushed join@1; Using where with pushed condition: (`test`.`y`.`a3` = 'Dole') Warnings: Note 1003 select `test`.`x`.`a3` AS `a3`,`test`.`x`.`b3` AS `b3`,`test`.`x`.`c3` AS `c3`,`test`.`x`.`d3` AS `d3`,`test`.`y`.`a3` AS `a3`,`test`.`y`.`b3` AS `b3`,`test`.`y`.`c3` AS `c3`,`test`.`y`.`d3` AS `d3` from `test`.`t3` `x` join `test`.`t3` `y` where ((`test`.`y`.`b3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 'Dole') and (`test`.`y`.`a3` = 'Dole')) @@ -3655,7 +3657,7 @@ insert into t1(a,b,c,d) values (12, 12, create index i1 on t1(c,a); explain extended select count(*) from t1 t1, t1 t2 where t1.c = 12 and t1.a = 11 and t2.a = t1.d and t2.b = t1.d; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ref PRIMARY,i1 i1 8 const,const 10 100.00 Parent of 2 pushed join@1 +1 SIMPLE t1 ref PRIMARY,i1 i1 8 const,const 3 100.00 Parent of 2 pushed join@1 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.d,test.t1.d 1 100.00 Child of pushed join@1 Warnings: Note 1003 select count(0) AS `count(*)` from `test`.`t1` join `test`.`t1` `t2` where ((`test`.`t2`.`a` = `test`.`t1`.`d`) and (`test`.`t2`.`b` = `test`.`t1`.`d`) and (`test`.`t1`.`a` = 11) and (`test`.`t1`.`c` = 12)) @@ -3668,7 +3670,7 @@ pruned_scan_count create index i2 on t1(a,b); explain extended select count(*) from t1 t1, t1 t2 where t1.a = 11 and t1.b<13 and t2.a = t1.c and t2.b = t1.c; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range PRIMARY,i2 i2 8 NULL 10 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`t1`.`a` = 11) and (`test`.`t1`.`b` < 13)) +1 SIMPLE t1 range PRIMARY,i2 i2 8 NULL 3 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`t1`.`a` = 11) and (`test`.`t1`.`b` < 13)) 1 SIMPLE t2 eq_ref PRIMARY,i2 PRIMARY 8 test.t1.c,test.t1.c 1 100.00 Child of pushed join@1 Warnings: Note 1003 select count(0) AS `count(*)` from `test`.`t1` join `test`.`t1` `t2` where ((`test`.`t2`.`a` = `test`.`t1`.`c`) and (`test`.`t2`.`b` = `test`.`t1`.`c`) and (`test`.`t1`.`a` = 11) and (`test`.`t1`.`b` < 13)) @@ -3679,7 +3681,7 @@ pruned_scan_count 1 explain extended select count(*) from t1 t1, t1 t2 where t1.a >= 12 and t1.a<=12 and t2.a = t1.c and t2.b = t1.c; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range PRIMARY,i2 i2 4 NULL 10 30.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`t1`.`a` >= 12) and (`test`.`t1`.`a` <= 12)) +1 SIMPLE t1 range PRIMARY,i2 i2 4 NULL 3 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`t1`.`a` >= 12) and (`test`.`t1`.`a` <= 12)) 1 SIMPLE t2 eq_ref PRIMARY,i2 PRIMARY 8 test.t1.c,test.t1.c 1 100.00 Child of pushed join@1 Warnings: Note 1003 select count(0) AS `count(*)` from `test`.`t1` join `test`.`t1` `t2` where ((`test`.`t2`.`a` = `test`.`t1`.`c`) and (`test`.`t2`.`b` = `test`.`t1`.`c`) and (`test`.`t1`.`a` >= 12) and (`test`.`t1`.`a` <= 12)) @@ -3690,7 +3692,7 @@ pruned_scan_count 1 explain extended select count(*) from t1 t1, t1 t2 where t1.a >= 11 and t1.a<=12 and t2.a = t1.c and t2.b = t1.c; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range PRIMARY,i2 i2 4 NULL 10 30.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`t1`.`a` >= 11) and (`test`.`t1`.`a` <= 12)) +1 SIMPLE t1 range PRIMARY,i2 i2 4 NULL 3 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`t1`.`a` >= 11) and (`test`.`t1`.`a` <= 12)) 1 SIMPLE t2 eq_ref PRIMARY,i2 PRIMARY 8 test.t1.c,test.t1.c 1 100.00 Child of pushed join@1 Warnings: Note 1003 select count(0) AS `count(*)` from `test`.`t1` join `test`.`t1` `t2` where ((`test`.`t2`.`a` = `test`.`t1`.`c`) and (`test`.`t2`.`b` = `test`.`t1`.`c`) and (`test`.`t1`.`a` >= 11) and (`test`.`t1`.`a` <= 12)) @@ -3701,7 +3703,7 @@ pruned_scan_count 0 explain extended select count(*) from t1 t1, t1 t2 where (t1.a = 10 or t1.a=12) and t1.b<13 and t2.a = t1.c and t2.b = t1.c; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range PRIMARY,i2 i2 8 NULL 20 15.00 Parent of 2 pushed join@1; Using where with pushed condition: (((`test`.`t1`.`a` = 10) or (`test`.`t1`.`a` = 12)) and (`test`.`t1`.`b` < 13)) +1 SIMPLE t1 range PRIMARY,i2 i2 8 NULL 6 50.00 Parent of 2 pushed join@1; Using where with pushed condition: (((`test`.`t1`.`a` = 10) or (`test`.`t1`.`a` = 12)) and (`test`.`t1`.`b` < 13)) 1 SIMPLE t2 eq_ref PRIMARY,i2 PRIMARY 8 test.t1.c,test.t1.c 1 100.00 Child of pushed join@1 Warnings: Note 1003 select count(0) AS `count(*)` from `test`.`t1` join `test`.`t1` `t2` where ((`test`.`t2`.`a` = `test`.`t1`.`c`) and (`test`.`t2`.`b` = `test`.`t1`.`c`) and ((`test`.`t1`.`a` = 10) or (`test`.`t1`.`a` = 12)) and (`test`.`t1`.`b` < 13)) @@ -3712,7 +3714,7 @@ pruned_scan_count 0 explain extended select count(*) from t1 t1, t1 t2 where t1.a = 10 and (t1.b<11 or t1.b>11) and t2.a = t1.c and t2.b = t1.c; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range PRIMARY,i2 i2 8 NULL 20 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`t1`.`a` = 10) and ((`test`.`t1`.`b` < 11) or (`test`.`t1`.`b` > 11))) +1 SIMPLE t1 range PRIMARY,i2 i2 8 NULL 6 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`t1`.`a` = 10) and ((`test`.`t1`.`b` < 11) or (`test`.`t1`.`b` > 11))) 1 SIMPLE t2 eq_ref PRIMARY,i2 PRIMARY 8 test.t1.c,test.t1.c 1 100.00 Child of pushed join@1 Warnings: Note 1003 select count(0) AS `count(*)` from `test`.`t1` join `test`.`t1` `t2` where ((`test`.`t2`.`a` = `test`.`t1`.`c`) and (`test`.`t2`.`b` = `test`.`t1`.`c`) and (`test`.`t1`.`a` = 10) and ((`test`.`t1`.`b` < 11) or (`test`.`t1`.`b` > 11))) @@ -3742,7 +3744,7 @@ insert into t2(a,b,c,d,e,f) values (7, 8 create index i2_1 on t2(d, a, b, e); explain extended select count(*) from t2 x, t2 y where x.d=1 and x.a=1 and x.b=2 and y.a=x.d and y.b=x.e and y.c=3; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE x ref PRIMARY,i2_1 i2_1 12 const,const,const 10 100.00 Parent of 2 pushed join@1 +1 SIMPLE x ref PRIMARY,i2_1 i2_1 12 const,const,const 3 100.00 Parent of 2 pushed join@1 1 SIMPLE y eq_ref PRIMARY PRIMARY 12 const,test.x.e,const 1 100.00 Child of pushed join@1 Warnings: Note 1003 select count(0) AS `count(*)` from `test`.`t2` `x` join `test`.`t2` `y` where ((`test`.`y`.`c` = 3) and (`test`.`y`.`b` = `test`.`x`.`e`) and (`test`.`x`.`b` = 2) and (`test`.`x`.`a` = 1) and (`test`.`x`.`d` = 1) and (`test`.`y`.`a` = 1)) @@ -3755,7 +3757,7 @@ pruned_scan_count create index i2_3 on t2(a, d, b, e); explain extended select count(*) from t2 x, t2 y where x.d=1 and x.a=1 and x.b=2 and y.a=x.d and y.b=x.e and y.c=3; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE x ref PRIMARY,i2_3 i2_3 12 const,const,const 10 100.00 Parent of 2 pushed join@1 +1 SIMPLE x ref PRIMARY,i2_3 i2_3 12 const,const,const 3 100.00 Parent of 2 pushed join@1 1 SIMPLE y eq_ref PRIMARY,i2_3 PRIMARY 12 const,test.x.e,const 1 100.00 Child of pushed join@1 Warnings: Note 1003 select count(0) AS `count(*)` from `test`.`t2` `x` join `test`.`t2` `y` where ((`test`.`y`.`c` = 3) and (`test`.`y`.`b` = `test`.`x`.`e`) and (`test`.`x`.`b` = 2) and (`test`.`x`.`a` = 1) and (`test`.`x`.`d` = 1) and (`test`.`y`.`a` = 1)) @@ -4195,16 +4197,16 @@ count(*) update t1 set b=b-10; update t1 set u=u+100; set ndb_join_pushdown=on; -explain extended select count(*) from +explain extended select straight_join count(*) from (t1 as x join t1 as y on y.b = x.a) left outer join t1 as z on z.u = x.a; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE x ALL NULL NULL NULL NULL 13 100.00 Parent of 3 pushed join@1 -1 SIMPLE z eq_ref ix2 ix2 4 test.x.a 1 100.00 Child of pushed join@1 1 SIMPLE y ref ix1 ix1 5 test.x.a 2 100.00 Child of pushed join@1; Using where +1 SIMPLE z eq_ref ix2 ix2 4 test.y.b 1 100.00 Child of pushed join@1 Warnings: -Note 1003 select count(0) AS `count(*)` from `test`.`t1` `x` join `test`.`t1` `y` left join `test`.`t1` `z` on(((`test`.`z`.`u` = `test`.`x`.`a`) and (`test`.`y`.`b` = `test`.`x`.`a`))) where (`test`.`y`.`b` = `test`.`x`.`a`) -select count(*) from +Note 1003 select straight_join count(0) AS `count(*)` from `test`.`t1` `x` join `test`.`t1` `y` left join `test`.`t1` `z` on(((`test`.`y`.`b` = `test`.`x`.`a`) and (`test`.`z`.`u` = `test`.`x`.`a`))) where (`test`.`y`.`b` = `test`.`x`.`a`) +select straight_join count(*) from (t1 as x join t1 as y on y.b = x.a) left outer join t1 as z on z.u = x.a; count(*) @@ -4562,7 +4564,7 @@ pk a b 2 20 20 explain extended select * from t1 as x1, t1 as x2 where x1.a=x2.b and x1.b = 3; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE x1 ref ix1 ix1 5 const 10 100.00 Parent of 2 pushed join@1; Using where with pushed condition: (`test`.`x1`.`b` = 3) +1 SIMPLE x1 ref ix1 ix1 5 const 3 100.00 Parent of 2 pushed join@1; Using where with pushed condition: (`test`.`x1`.`b` = 3) 1 SIMPLE x2 ref ix1 ix1 5 test.x1.a 2 100.00 Child of pushed join@1; Using where Warnings: Note 1003 select `test`.`x1`.`pk` AS `pk`,`test`.`x1`.`a` AS `a`,`test`.`x1`.`b` AS `b`,`test`.`x2`.`pk` AS `pk`,`test`.`x2`.`a` AS `a`,`test`.`x2`.`b` AS `b` from `test`.`t1` `x1` join `test`.`t1` `x2` where ((`test`.`x1`.`b` = 3) and (`test`.`x2`.`b` = `test`.`x1`.`a`)) @@ -4584,7 +4586,7 @@ on table1.a = table4.pk where table2.pk != 6; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE table1 ALL NULL NULL NULL NULL 6 33.33 Using temporary -1 SIMPLE table2 range PRIMARY PRIMARY 4 NULL 20 30.00 Parent of 2 pushed join@1; Using where with pushed condition: (`test`.`table2`.`pk` <> 6); Distinct; Using join buffer +1 SIMPLE table2 range PRIMARY PRIMARY 4 NULL 6 100.00 Parent of 2 pushed join@1; Using where with pushed condition: (`test`.`table2`.`pk` <> 6); Distinct; Using join buffer 1 SIMPLE table3 eq_ref PRIMARY PRIMARY 4 test.table2.pk 1 100.00 Child of pushed join@1; Distinct 1 SIMPLE table4 eq_ref PRIMARY PRIMARY 4 test.table1.a 1 100.00 Using where; Distinct Warnings: @@ -4609,7 +4611,7 @@ insert into t values(0,0); explain extended select * from t as t1 join t as t2 on t2.a=t1.a where t1.a < 8 or t1.a >= 8; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range PRIMARY PRIMARY 0 NULL 10 20.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`t1`.`a` < 8) or (`test`.`t1`.`a` >= 8)) +1 SIMPLE t1 range PRIMARY PRIMARY 0 NULL 2 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`t1`.`a` < 8) or (`test`.`t1`.`a` >= 8)) 1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 Child of pushed join@1 Warnings: Note 1003 select `test`.`t1`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`a` AS `a` from `test`.`t` `t1` join `test`.`t` `t2` where ((`test`.`t2`.`a` = `test`.`t1`.`a`) and ((`test`.`t1`.`a` < 8) or (`test`.`t1`.`a` >= 8))) @@ -4625,7 +4627,7 @@ on t1.pk2 = t2.pk1 where t1.pk1 != 6 order by t1.pk1 DESC; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 20 10.00 Using where with pushed condition: (`test`.`t1`.`pk1` <> 6) +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 50.00 Using where with pushed condition: (`test`.`t1`.`pk1` <> 6) 1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.pk2 1 100.00 Warnings: Note 1644 Push of table 't2' as scan-child with ordered indexscan-root 't1' not implemented @@ -4732,7 +4734,7 @@ insert into t1 values ('aaa', 1, 'aaa', explain extended select * from t1 as q1, t1 as q2 where q1.a = 'aaa' and q1.c=q2.a; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE q1 ref PRIMARY PRIMARY 18 const 10 100.00 Parent of 2 pushed join@1; Using where with pushed condition: (`test`.`q1`.`a` = 'aaa') +1 SIMPLE q1 ref PRIMARY PRIMARY 18 const 2 100.00 Parent of 2 pushed join@1; Using where with pushed condition: (`test`.`q1`.`a` = 'aaa') 1 SIMPLE q2 ref PRIMARY PRIMARY 18 test.q1.c 1 100.00 Child of pushed join@1 Warnings: Note 1003 select `test`.`q1`.`a` AS `a`,`test`.`q1`.`b` AS `b`,`test`.`q1`.`c` AS `c`,`test`.`q1`.`d` AS `d`,`test`.`q2`.`a` AS `a`,`test`.`q2`.`b` AS `b`,`test`.`q2`.`c` AS `c`,`test`.`q2`.`d` AS `d` from `test`.`t1` `q1` join `test`.`t1` `q2` where ((`test`.`q2`.`a` = `test`.`q1`.`c`) and (`test`.`q1`.`a` = 'aaa')) @@ -4850,20 +4852,20 @@ 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 230 +LOCAL_TABLE_SCANS_SENT 228 PRUNED_RANGE_SCANS_RECEIVED 17 RANGE_SCANS_RECEIVED 718 -READS_NOT_FOUND 409 +READS_NOT_FOUND 404 READS_RECEIVED 49 -SCAN_ROWS_RETURNED 78755 -TABLE_SCANS_RECEIVED 230 +SCAN_ROWS_RETURNED 78728 +TABLE_SCANS_RECEIVED 228 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 and (spj_counts_at_end.counter_name = 'LOCAL_READS_SENT' or spj_counts_at_end.counter_name = 'REMOTE_READS_SENT'); LOCAL+REMOTE READS_SENT -28879 +28863 drop table spj_counts_at_startup; drop table spj_counts_at_end; scan_count @@ -4873,9 +4875,9 @@ pruned_scan_count sorted_scan_count 9 pushed_queries_defined -356 +354 pushed_queries_dropped 11 pushed_queries_executed -527 +526 set ndb_join_pushdown = @save_ndb_join_pushdown; === modified file 'mysql-test/suite/ndb/r/ndb_read_multi_range.result' --- a/mysql-test/suite/ndb/r/ndb_read_multi_range.result 2011-01-21 13:56:43 +0000 +++ b/mysql-test/suite/ndb/r/ndb_read_multi_range.result 2011-02-28 12:25:52 +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 20 Using where; Distinct +1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 6 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; === modified file 'mysql-test/suite/ndb/r/ndb_statistics.result' --- a/mysql-test/suite/ndb/r/ndb_statistics.result 2011-01-21 13:56:43 +0000 +++ b/mysql-test/suite/ndb/r/ndb_statistics.result 2011-02-28 12:25:52 +0000 @@ -38,24 +38,124 @@ id select_type table type possible_keys 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 +1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 500 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 +1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 500 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 +1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 1000 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 +1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 1000 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 Parent of 2 pushed join@1 1 SIMPLE Y ref J,I I 10 test.X.I,test.X.I 11 Child of pushed join@1; Using where +EXPLAIN +SELECT * FROM t100 WHERE k < 42; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t100 range PRIMARY PRIMARY 4 NULL 10 Using where with pushed condition +EXPLAIN +SELECT * FROM t100 WHERE k > 42; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t100 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 1000 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 1000 Using where with pushed condition +EXPLAIN +SELECT * FROM t100 WHERE k BETWEEN 42 AND 10000; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t100 range PRIMARY PRIMARY 4 NULL 5 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 500 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE I = 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 ref I I 5 const 200 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE J = 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 ref J J 5 const 100 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE I = 0 AND J = 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 ref J,I I 10 const,const 4 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE I = 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 ref I I 5 const 200 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE I = 0 AND J > 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 range J,I I 10 NULL 100 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE I = 0 AND J < 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 range J,I I 10 NULL 50 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE I = 0 AND J BETWEEN 1 AND 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 range J,I I 10 NULL 50 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE I = 0 AND J = 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 ref J,I I 10 const,const 4 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE J = 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 ref J J 5 const 100 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE J = 0 AND K > 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 range PRIMARY,J J 9 NULL 50 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE J = 0 AND K < 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 range PRIMARY,J J 9 NULL 50 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE J = 0 AND K BETWEEN 1 AND 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 range PRIMARY,J J 9 NULL 25 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE J = 0 AND K = 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 const PRIMARY,J PRIMARY 4 const 1 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE I = 0 AND J <> 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 range J,I I 10 NULL 150 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE I <> 0 AND J = 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 ref J,I J 5 const 100 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE I <> 0 AND J <> 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 range J,I J 5 NULL 1500 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE J <> 1 AND I = 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 range J,I I 10 NULL 150 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE J = 1 AND I <> 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 ref J,I J 5 const 100 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE J <> 1 AND I <> 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 range J,I J 5 NULL 1500 Using where with pushed condition 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 11:49:03 +0000 +++ b/mysql-test/suite/ndb/t/ndb_statistics.test 2011-02-28 10:42:04 +0000 @@ -62,6 +62,82 @@ EXPLAIN SELECT * FROM t10000 AS X JOIN t10000 AS Y ON Y.I=X.I AND Y.J = X.I; +# +# Bug #11804277: INCORRECT INDEX MAY BE SELECTED DUE TO INSUFFICIENT +# STATISTICS FROM CLUSTER +# + +# Open bounded range should return 10% of #rows in table +EXPLAIN +SELECT * FROM t100 WHERE k < 42; +EXPLAIN +SELECT * FROM t100 WHERE k > 42; +EXPLAIN +SELECT * FROM t10000 WHERE k < 42; +EXPLAIN +SELECT * FROM t10000 WHERE k > 42; + +#Closed bounded range should return 5% of #rows in table +EXPLAIN +SELECT * FROM t100 WHERE k BETWEEN 42 AND 10000; +EXPLAIN +SELECT * FROM t10000 WHERE k BETWEEN 42 AND 10000; + +#EQ-range selectivity depends on +# - key length specified +# - #rows in table. +# - unique/non-unique index +# - min 2% selectivity +# +# Possibly combined with open/closed ranges as +# above which further improves selectivity +# +EXPLAIN +SELECT * FROM t10000 WHERE I = 0; +EXPLAIN +SELECT * FROM t10000 WHERE J = 0; + +EXPLAIN +SELECT * FROM t10000 WHERE I = 0 AND J = 0; + +EXPLAIN +SELECT * FROM t10000 WHERE I = 0; +EXPLAIN +SELECT * FROM t10000 WHERE I = 0 AND J > 1; +EXPLAIN +SELECT * FROM t10000 WHERE I = 0 AND J < 1; +EXPLAIN +SELECT * FROM t10000 WHERE I = 0 AND J BETWEEN 1 AND 10; +EXPLAIN +SELECT * FROM t10000 WHERE I = 0 AND J = 1; + +EXPLAIN +SELECT * FROM t10000 WHERE J = 0; +EXPLAIN +SELECT * FROM t10000 WHERE J = 0 AND K > 1; +EXPLAIN +SELECT * FROM t10000 WHERE J = 0 AND K < 1; +EXPLAIN +SELECT * FROM t10000 WHERE J = 0 AND K BETWEEN 1 AND 10; +EXPLAIN +SELECT * FROM t10000 WHERE J = 0 AND K = 1; + +## Verify selection of 'best' index +## (The one of index I/J being EQ) +EXPLAIN +SELECT * FROM t10000 WHERE I = 0 AND J <> 1; +EXPLAIN +SELECT * FROM t10000 WHERE I <> 0 AND J = 1; +EXPLAIN +SELECT * FROM t10000 WHERE I <> 0 AND J <> 1; + +EXPLAIN +SELECT * FROM t10000 WHERE J <> 1 AND I = 0; +EXPLAIN +SELECT * FROM t10000 WHERE J = 1 AND I <> 0; +EXPLAIN +SELECT * FROM t10000 WHERE J <> 1 AND I <> 0; + DROP TABLE t10,t100,t10000; === modified file 'sql/ha_ndbcluster.cc' --- a/sql/ha_ndbcluster.cc 2011-02-23 13:09:10 +0000 +++ b/sql/ha_ndbcluster.cc 2011-02-28 12:25:52 +0000 @@ -9065,7 +9065,7 @@ static int create_ndb_column(THD *thd, col.setCharset(cs); } col.setInlineSize(256); - col.setPartSize(8000); + col.setPartSize(4 * (NDB_MAX_TUPLE_SIZE_IN_WORDS - /* safty */ 13)); col.setStripeSize(ndb_blob_striping() ? 4 : 0); break; // Other types @@ -12441,7 +12441,100 @@ ha_ndbcluster::records_in_range(uint inx DBUG_RETURN(rows); } - DBUG_RETURN(10); /* Good guess when you don't know anything */ + /* Use simple heuristics to estimate fraction + of 'stats.record' returned from range. + */ + do + { + if (stats.records == ~(ha_rows)0 || stats.records == 0) + { + /* Refresh statistics, only read from datanodes if 'use_exact_count' */ + THD *thd= current_thd; + if (update_stats(thd, THDVAR(thd, use_exact_count))) + break; + } + + Uint64 rows; + Uint64 table_rows= stats.records; + size_t eq_bound_len= 0; + size_t min_key_length= (min_key) ? min_key->length : 0; + size_t max_key_length= (max_key) ? max_key->length : 0; + + // Might have an closed/open range bound: + // Low range open + if (!min_key_length) + { + rows= (!max_key_length) + ? table_rows // No range was specified + : table_rows/10; // -oo .. -> 10% selectivity + } + // High range open + else if (!max_key_length) + { + rows= table_rows/10; // ..oo -> 10% selectivity + } + else + { + size_t bounds_len= min(min_key_length,max_key_length); + uint eq_bound_len= 0; + uint eq_bound_offs= 0; + + KEY_PART_INFO* key_part= key_info->key_part; + KEY_PART_INFO* end= key_part+key_info->key_parts; + for (; key_part != end; key_part++) + { + uint part_length= key_part->store_length; + if (eq_bound_offs+part_length > bounds_len || + memcmp(&min_key->key[eq_bound_offs], + &max_key->key[eq_bound_offs], + part_length)) + { + break; + } + eq_bound_len+= key_part->length; + eq_bound_offs+= part_length; + } + + if (!eq_bound_len) + { + rows= table_rows/20; // .. -> 5% + } + else + { + // Has an equality range on a leading part of 'key_length': + // - Null indicator, and HA_KEY_BLOB_LENGTH bytes in + // 'extra_length' are removed from key_fraction calculations. + // - Assume reduced selectivity for non-unique indexes + // by decreasing 'eq_fraction' by 20% + // - Assume equal selectivity for all eq_parts in key. + + double eq_fraction = (double)(eq_bound_len) / + (key_length - key_info->extra_length); + if (idx_type == ORDERED_INDEX) // Non-unique index -> less selectivity + eq_fraction/= 1.20; + if (eq_fraction >= 1.0) // Exact match -> 1 row + DBUG_RETURN(1); + + rows = (Uint64)((double)table_rows / pow(table_rows, eq_fraction)); + if (rows > (table_rows/50)) // EQ-range: Max 2% of rows + rows= (table_rows/50); + + if (min_key_length > eq_bound_offs) + rows/= 2; + if (max_key_length > eq_bound_offs) + rows/= 2; + } + } + + // Make sure that EQ is preferred even if row-count is low + if (eq_bound_len && rows < 2) // At least 2 rows as not exact + rows= 2; + else if (rows < 3) + rows= 3; + DBUG_RETURN(min(rows,table_rows)); + } while (0); + + DBUG_RETURN(10); /* Poor guess when you don't know anything */ } ulonglong ha_ndbcluster::table_flags(void) const === modified file 'storage/ndb/include/kernel/signaldata/TcIndx.hpp' --- a/storage/ndb/include/kernel/signaldata/TcIndx.hpp 2011-02-01 23:27:25 +0000 +++ b/storage/ndb/include/kernel/signaldata/TcIndx.hpp 2011-02-24 08:25:08 +0000 @@ -21,108 +21,8 @@ #include "SignalData.hpp" #include "TcKeyReq.hpp" +#include "TcKeyConf.hpp" -class TcIndxConf { - - /** - * Reciver(s) - */ - friend class Ndb; - friend class NdbTransaction; - - /** - * Sender(s) - */ - friend class Dbtc; - - /** - * For printing - */ - friend bool printTCINDXCONF(FILE *, const Uint32 *, Uint32, Uint16); - -public: - /** - * Length of signal - */ - STATIC_CONST( SignalLength = 5 ); - -private: - /** - * DATA VARIABLES - */ - //------------------------------------------------------------- - // Unconditional part. First 5 words - //------------------------------------------------------------- - - Uint32 apiConnectPtr; - Uint32 gci_hi; // gci_lo is stored after operations - Uint32 confInfo; - Uint32 transId1; - Uint32 transId2; - - struct OperationConf { - Uint32 apiOperationPtr; - Uint32 attrInfoLen; - }; - //------------------------------------------------------------- - // Operations confirmations, - // No of actually sent = getNoOfOperations(confInfo) - //------------------------------------------------------------- - OperationConf operations[10]; - - /** - * Get:ers for confInfo - */ - static Uint32 getNoOfOperations(const Uint32 & confInfo); - static Uint32 getCommitFlag(const Uint32 & confInfo); - static bool getMarkerFlag(const Uint32 & confInfo); - - /** - * Set:ers for confInfo - */ - static void setCommitFlag(Uint32 & confInfo, Uint8 flag); - static void setNoOfOperations(Uint32 & confInfo, Uint32 noOfOps); - static void setMarkerFlag(Uint32 & confInfo, Uint32 flag); -}; - -inline -Uint32 -TcIndxConf::getNoOfOperations(const Uint32 & confInfo){ - return confInfo & 65535; -} - -inline -Uint32 -TcIndxConf::getCommitFlag(const Uint32 & confInfo){ - return ((confInfo >> 16) & 1); -} - -inline -bool -TcIndxConf::getMarkerFlag(const Uint32 & confInfo){ - const Uint32 bits = 3 << 16; // Marker only valid when doing commit - return (confInfo & bits) == bits; -} - -inline -void -TcIndxConf::setNoOfOperations(Uint32 & confInfo, Uint32 noOfOps){ - ASSERT_MAX(noOfOps, 65535, "TcIndxConf::setNoOfOperations"); - confInfo |= noOfOps; -} - -inline -void -TcIndxConf::setCommitFlag(Uint32 & confInfo, Uint8 flag){ - ASSERT_BOOL(flag, "TcIndxConf::setCommitFlag"); - confInfo |= (flag << 16); -} - -inline -void -TcIndxConf::setMarkerFlag(Uint32 & confInfo, Uint32 flag){ - ASSERT_BOOL(flag, "TcIndxConf::setMarkerFlag"); - confInfo |= (flag << 17); -} +typedef TcKeyConf TcIndxConf; #endif === modified file 'storage/ndb/include/ndbapi/NdbTransaction.hpp' --- a/storage/ndb/include/ndbapi/NdbTransaction.hpp 2011-02-10 08:22:41 +0000 +++ b/storage/ndb/include/ndbapi/NdbTransaction.hpp 2011-02-28 12:25:52 +0000 @@ -985,7 +985,6 @@ private: int receiveTCROLLBACKCONF(const NdbApiSignal* anApiSignal); int receiveTCROLLBACKREF(const NdbApiSignal* anApiSignal); int receiveTCROLLBACKREP(const NdbApiSignal* anApiSignal); - int receiveTCINDXCONF(const class TcIndxConf *, Uint32 aDataLength); int receiveTCINDXREF(const NdbApiSignal*); int receiveSCAN_TABREF(const NdbApiSignal*); int receiveSCAN_TABCONF(const NdbApiSignal*, const Uint32*, Uint32 len); === modified file 'storage/ndb/src/common/debugger/signaldata/SignalDataPrint.cpp' --- a/storage/ndb/src/common/debugger/signaldata/SignalDataPrint.cpp 2011-02-08 14:29:52 +0000 +++ b/storage/ndb/src/common/debugger/signaldata/SignalDataPrint.cpp 2011-02-28 12:25:52 +0000 @@ -81,7 +81,7 @@ SignalDataPrintFunctions[] = { { GSN_ALTER_INDX_REQ, printALTER_INDX_REQ }, { GSN_ALTER_INDX_CONF, printALTER_INDX_CONF }, { GSN_ALTER_INDX_REF, printALTER_INDX_REF }, - { GSN_TCINDXCONF, printTCINDXCONF }, + { GSN_TCINDXCONF, printTCKEYCONF }, { GSN_TCINDXREF, printTCINDXREF }, { GSN_INDXKEYINFO, printINDXKEYINFO }, { GSN_INDXATTRINFO, printINDXATTRINFO }, === modified file 'storage/ndb/src/common/debugger/signaldata/TcIndx.cpp' --- a/storage/ndb/src/common/debugger/signaldata/TcIndx.cpp 2011-02-01 23:27:25 +0000 +++ b/storage/ndb/src/common/debugger/signaldata/TcIndx.cpp 2011-02-24 08:25:08 +0000 @@ -20,48 +20,6 @@ #include #include - -bool -printTCINDXCONF(FILE * output, const Uint32 * theData, Uint32 len, Uint16 receiverBlockNo){ - - if (receiverBlockNo == API_PACKED) { - fprintf(output, "Signal data: "); - Uint32 i = 0; - while (i < len) - fprintf(output, "H\'%.8x ", theData[i++]); - fprintf(output,"\n"); - } - else { - const TcIndxConf * const sig = (TcIndxConf *) theData; - - fprintf(output, "Signal data: "); - Uint32 i = 0; - Uint32 confInfo = sig->confInfo; - Uint32 noOfOp = TcIndxConf::getNoOfOperations(confInfo); - while (i < len) - fprintf(output, "H\'%.8x ", theData[i++]); - fprintf(output,"\n"); - fprintf(output, "apiConnectPtr: H'%.8x, gci: %u/%u, transId:(H'%.8x, H'%.8x)\n", - sig->apiConnectPtr, - sig->gci_hi, *(Uint32*)&sig->operations[noOfOp], - sig->transId1, sig->transId2); - - fprintf(output, "noOfOperations: %u, commitFlag: %s, markerFlag: %s\n", - noOfOp, - (TcIndxConf::getCommitFlag(confInfo) == 0)?"false":"true", - (TcIndxConf::getMarkerFlag(confInfo) == 0)?"false":"true"); - fprintf(output, "Operations:\n"); - for(i = 0; i < noOfOp; i++) { - fprintf(output, - "apiOperationPtr: H'%.8x, attrInfoLen: %u\n", - sig->operations[i].apiOperationPtr, - sig->operations[i].attrInfoLen); - } - } - - return true; -} - bool printTCINDXREF(FILE * output, const Uint32 * theData, Uint32 len, Uint16 receiverBlockNo){ === modified file 'storage/ndb/src/kernel/blocks/backup/Backup.cpp' --- a/storage/ndb/src/kernel/blocks/backup/Backup.cpp 2011-02-04 11:45:24 +0000 +++ b/storage/ndb/src/kernel/blocks/backup/Backup.cpp 2011-02-28 12:25:52 +0000 @@ -3016,7 +3016,7 @@ Backup::execDEFINE_BACKUP_REQ(Signal* si const Uint32 maxInsert[] = { MAX_WORDS_META_FILE, 4096, // 16k - 16*3000, // Max 16 tuples + 16 * (MAX_TUPLE_SIZE_IN_WORDS + 128 /* safety */), // Max 16 tuples }; Uint32 minWrite[] = { 8192, === modified file 'storage/ndb/src/kernel/blocks/lgman.cpp' --- a/storage/ndb/src/kernel/blocks/lgman.cpp 2011-02-17 15:51:08 +0000 +++ b/storage/ndb/src/kernel/blocks/lgman.cpp 2011-02-24 13:39:24 +0000 @@ -1207,19 +1207,15 @@ Lgman::alloc_logbuffer_memory(Ptr requested) + if(pages) { - // less than half allocated + /* Could not allocate all of the requested memory. + * So release that already allocated. + */ free_logbuffer_memory(ptr); return false; } - if(pages != 0) - { - warningEvent("Allocated %d pages for log buffer space, logfile_group: %d" - " , requested %d pages", - (requested-pages), ptr.p->m_logfile_group_id, requested); - } #if defined VM_TRACE || defined ERROR_INSERT ndbout << "DD lgman: fg id:" << ptr.p->m_logfile_group_id << " undo buffer pages/bytes:" << (requested-pages) << "/" << (requested-pages)*File_formats::NDB_PAGE_SIZE << endl; #endif === modified file 'storage/ndb/src/ndbapi/NdbTransaction.cpp' --- a/storage/ndb/src/ndbapi/NdbTransaction.cpp 2011-02-10 08:22:41 +0000 +++ b/storage/ndb/src/ndbapi/NdbTransaction.cpp 2011-02-28 12:25:52 +0000 @@ -2286,77 +2286,6 @@ NdbTransaction::receiveTCKEY_FAILREF(con return -1; }//NdbTransaction::receiveTCKEY_FAILREF() -/****************************************************************************** -int receiveTCINDXCONF(NdbApiSignal* aSignal, Uint32 long_short_ind); - -Return Value: Return 0 : receiveTCINDXCONF was successful. - Return -1: In all other case. -Parameters: aSignal: The signal object pointer. -Remark: -******************************************************************************/ -int -NdbTransaction::receiveTCINDXCONF(const TcIndxConf * indxConf, - Uint32 aDataLength) -{ - if(checkState_TransId(&indxConf->transId1)){ - const Uint32 tTemp = indxConf->confInfo; - const Uint32 tNoOfOperations = TcIndxConf::getNoOfOperations(tTemp); - const Uint32 tCommitFlag = TcKeyConf::getCommitFlag(tTemp); - - const Uint32* tPtr = (Uint32 *)&indxConf->operations[0]; - Uint32 tNoComp = theNoOfOpCompleted; - for (Uint32 i = 0; i < tNoOfOperations ; i++) { - NdbReceiver* tOp = theNdb->void2rec(theNdb->int2void(*tPtr)); - tPtr++; - const Uint32 tAttrInfoLen = *tPtr; - tPtr++; - if (tOp && tOp->checkMagicNumber()) { - tNoComp += tOp->execTCOPCONF(tAttrInfoLen); - } else { - return -1; - }//if - }//for - const Uint32 tNoSent = theNoOfOpSent; - const Uint32 tGCI_hi = indxConf->gci_hi; - Uint32 tGCI_lo = * tPtr; - if (unlikely(aDataLength < TcIndxConf::SignalLength+1+2*tNoOfOperations)) - { - tGCI_lo = 0; - } - const Uint64 tGCI = Uint64(tGCI_lo) | (Uint64(tGCI_hi) << 32); - - theNoOfOpCompleted = tNoComp; - if (tCommitFlag == 1) - { - theCommitStatus = Committed; - theGlobalCheckpointId = tGCI; - if (tGCI) // Read(dirty) only transaction doesnt get GCI - { - *p_latest_trans_gci = tGCI; - } - } - else if (theLastExecOpInList->theCommitIndicator == 1) - { - /** - * We're waiting for a commit reply... - */ - return -1; - }//if - - if (tNoComp >= tNoSent) - { - return 0; // No more operations to wait for - }//if - // Not completed the reception yet. - } else { -#ifdef NDB_NO_DROPPED_SIGNAL - abort(); -#endif - } - - return -1; -}//NdbTransaction::receiveTCINDXCONF() - /******************************************************************************* int OpCompletedFailure(); === modified file 'storage/ndb/src/ndbapi/Ndbif.cpp' --- a/storage/ndb/src/ndbapi/Ndbif.cpp 2011-02-09 13:15:56 +0000 +++ b/storage/ndb/src/ndbapi/Ndbif.cpp 2011-02-28 12:25:52 +0000 @@ -328,6 +328,7 @@ Ndb::handleReceivedSignal(const NdbApiSi switch (tSignalNumber){ case GSN_TCKEYCONF: + case GSN_TCINDXCONF: { const TcKeyConf * const keyConf = (TcKeyConf *)tDataPtr; if (tFirstData != RNIL) @@ -873,30 +874,6 @@ Ndb::handleReceivedSignal(const NdbApiSi return; } } - case GSN_TCINDXCONF:{ - tFirstDataPtr = int2void(tFirstData); - if (tFirstDataPtr == 0) goto InvalidSignal; - - const TcIndxConf * const indxConf = (TcIndxConf *)tDataPtr; - const BlockReference aTCRef = aSignal->theSendersBlockRef; - tCon = void2con(tFirstDataPtr); - if ((tCon->checkMagicNumber() == 0) && - (tCon->theSendStatus == NdbTransaction::sendTC_OP)) { - tReturnCode = tCon->receiveTCINDXCONF(indxConf, tLen); - if (tReturnCode != -1) { - completedTransaction(tCon); - }//if - }//if - - if(TcIndxConf::getMarkerFlag(indxConf->confInfo)){ - NdbTransaction::sendTC_COMMIT_ACK(theImpl, - theCommitAckSignal, - indxConf->transId1, - indxConf->transId2, - aTCRef); - } - return; - } case GSN_TCINDXREF:{ tFirstDataPtr = int2void(tFirstData); if (tFirstDataPtr == 0) goto InvalidSignal; === modified file 'storage/ndb/src/ndbapi/TransporterFacade.cpp' --- a/storage/ndb/src/ndbapi/TransporterFacade.cpp 2011-02-10 08:22:41 +0000 +++ b/storage/ndb/src/ndbapi/TransporterFacade.cpp 2011-02-28 12:25:52 +0000 @@ -1539,6 +1539,7 @@ TransporterFacade::start_poll(trp_client void TransporterFacade::do_poll(trp_client* clnt, Uint32 wait_time) { + clnt->m_poll.m_waiting = true; assert(clnt->m_poll.m_locked == true); trp_client* owner = m_poll_owner; if (owner != NULL && owner != clnt) @@ -1552,9 +1553,11 @@ TransporterFacade::do_poll(trp_client* c queue if it hasn't happened already. It is usually already out of the queue but at time-out it could be that the object is still there. */ + assert(clnt->m_poll.m_poll_owner == false); add_to_poll_queue(clnt); - clnt->cond_wait(wait_time, theMutexPtr); // release/reacquire mutex - if (clnt != m_poll_owner) + NdbCondition_WaitTimeout(clnt->m_poll.m_condition, theMutexPtr, + wait_time); + if (clnt != m_poll_owner && clnt->m_poll.m_waiting) { remove_from_poll_queue(clnt); } @@ -1574,8 +1577,23 @@ TransporterFacade::do_poll(trp_client* c } void +TransporterFacade::wakeup(trp_client* clnt) +{ + if (clnt->m_poll.m_waiting) + { + clnt->m_poll.m_waiting = false; + if (m_poll_owner != clnt) + { + remove_from_poll_queue(clnt); + NdbCondition_Signal(clnt->m_poll.m_condition); + } + } +} + +void TransporterFacade::complete_poll(trp_client* clnt) { + clnt->m_poll.m_waiting = false; if (!clnt->m_poll.m_locked) { assert(clnt->m_poll.m_poll_owner == false); @@ -1603,7 +1621,9 @@ TransporterFacade::complete_poll(trp_cli if (new_owner) { assert(new_owner->m_poll.m_poll_owner == false); - new_owner->cond_signal(); + assert(new_owner->m_poll.m_locked == true); + assert(new_owner->m_poll.m_waiting == true); + NdbCondition_Signal(new_owner->m_poll.m_condition); new_owner->m_poll.m_poll_owner = true; } clnt->m_poll.m_locked = false; === modified file 'storage/ndb/src/ndbapi/TransporterFacade.hpp' --- a/storage/ndb/src/ndbapi/TransporterFacade.hpp 2011-02-10 08:22:41 +0000 +++ b/storage/ndb/src/ndbapi/TransporterFacade.hpp 2011-02-28 12:25:52 +0000 @@ -149,6 +149,7 @@ public: void start_poll(trp_client*); void do_poll(trp_client* clnt, Uint32 wait_time); void complete_poll(trp_client*); + void wakeup(trp_client*); void external_poll(Uint32 wait_time); === modified file 'storage/ndb/src/ndbapi/trp_client.cpp' --- a/storage/ndb/src/ndbapi/trp_client.cpp 2011-02-04 17:52:38 +0000 +++ b/storage/ndb/src/ndbapi/trp_client.cpp 2011-02-24 07:47:22 +0000 @@ -21,6 +21,7 @@ trp_client::trp_client() : m_blockNo(~Uint32(0)), m_facade(0) { + m_poll.m_waiting = false; m_poll.m_locked = false; m_poll.m_poll_owner = false; m_poll.m_next = 0; @@ -101,22 +102,6 @@ trp_client::complete_poll() } void -trp_client::cond_signal() -{ - assert(m_poll.m_locked); - assert(m_poll.m_poll_owner == false); - NdbCondition_Signal(m_poll.m_condition); -} - -void -trp_client::cond_wait(Uint32 timeout, NdbMutex* mutexPtr) -{ - assert(m_poll.m_locked); - assert(m_poll.m_poll_owner == false); - NdbCondition_WaitTimeout(m_poll.m_condition, mutexPtr, (int)timeout); -} - -void trp_client::do_forceSend(int val) { if (val == 0) === modified file 'storage/ndb/src/ndbapi/trp_client.hpp' --- a/storage/ndb/src/ndbapi/trp_client.hpp 2011-02-04 17:52:38 +0000 +++ b/storage/ndb/src/ndbapi/trp_client.hpp 2011-02-24 07:47:22 +0000 @@ -84,24 +84,13 @@ private: { bool m_locked; bool m_poll_owner; + bool m_waiting; trp_client *m_prev; trp_client *m_next; NdbCondition * m_condition; } m_poll; - void cond_wait(Uint32 timeout, NdbMutex*); - void cond_signal(); }; -inline -void -trp_client::wakeup() -{ - if (m_poll.m_locked == true && m_poll.m_poll_owner == false) - cond_signal(); - else if (m_poll.m_poll_owner) - assert(m_poll.m_locked); -} - class PollGuard { public: @@ -139,6 +128,13 @@ trp_client::unlock() } inline +void +trp_client::wakeup() +{ + m_facade->wakeup(this); +} + +inline int trp_client::raw_sendSignal(const NdbApiSignal * signal, Uint32 nodeId) { No bundle (reason: useless for push emails).