#At file:///net/fimafeng09/export/home/tmp/oleja/mysql/mysql-5.5-cluster-spj/ based on revid:ole.john.aske@stripped
3372 Ole John Aske 2011-06-23
Accept changed result file for ndb_pushdown_join when merged to mysql-5.5-cluster as result of:
- Changed order of 'cannot push' warning defined in sql/share/errmsg... 1644 -> 1709.
- A few non-SPJ specific changes in the optimizer.
- Slightly different 'rows' / 'filtered' reported in explain for a few queries due
to insufficient knowloedge about 'row count' - Will be fixed by the upcomming
'maintained statistics' patch to be merged from telco-7.0
modified:
mysql-test/suite/ndb/r/ndb_join_pushdown.result
=== modified file 'mysql-test/suite/ndb/r/ndb_join_pushdown.result'
--- a/mysql-test/suite/ndb/r/ndb_join_pushdown.result 2011-06-23 13:26:22 +0000
+++ b/mysql-test/suite/ndb/r/ndb_join_pushdown.result 2011-06-23 13:44:45 +0000
@@ -57,9 +57,9 @@ id select_type table type possible_keys
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 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 1709 Can't push table 'x2' as child, 'type' must be a 'ref' access
+Note 1709 Cannot push table 'x3' as child of table 'x1'. Doing so would prevent using join buffer for table 'x2'.
+Note 1709 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
@@ -78,9 +78,9 @@ id select_type table type possible_keys
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
Warnings:
-Note 1644 Can't push table 'x3' as child, 'type' must be a 'ref' access
-Note 1644 Cannot push table 'x4' as child of table 'x1'. Doing so would prevent using join buffer for table 'x3'.
-Note 1644 Cannot push table 'x4' as child of 'x3', since it referes to column 'x1.d' which will be stored in a join buffer.
+Note 1709 Can't push table 'x3' as child, 'type' must be a 'ref' access
+Note 1709 Cannot push table 'x4' as child of table 'x1'. Doing so would prevent using join buffer for table 'x3'.
+Note 1709 Cannot push table 'x4' as child of 'x3', since it referes to column 'x1.d' which will be stored in a join buffer.
Note 1003 select `test`.`x1`.`a` AS `a`,`test`.`x1`.`b` AS `b`,`test`.`x1`.`c` AS `c`,`test`.`x1`.`d` AS `d`,`test`.`x2`.`a` AS `a`,`test`.`x2`.`b` AS `b`,`test`.`x2`.`c` AS `c`,`test`.`x2`.`d` AS `d`,`test`.`x3`.`a` AS `a`,`test`.`x3`.`b` AS `b`,`test`.`x3`.`c` AS `c`,`test`.`x3`.`d` AS `d`,`test`.`x4`.`a` AS `a`,`test`.`x4`.`b` AS `b`,`test`.`x4`.`c` AS `c`,`test`.`x4`.`d` AS `d` from `test`.`t1` `x1` join `test`.`t1` `x2` join `test`.`t1` `x3` join `test`.`t1` `x4` where ((`test`.`x2`.`a` = `test`.`x1`.`c`) and (`test`.`x1`.`a` = 1) and (`test`.`x2`.`b` = `test`.`x1`.`d`) and (`test`.`x4`.`b` = `test`.`x1`.`d`) and (`test`.`x4`.`a` = `test`.`x3`.`c`))
select *
from t1 as x1
@@ -255,8 +255,8 @@ id select_type table type possible_keys
1 SIMPLE t1 ALL NULL NULL NULL NULL 16 100.00
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.c,test.t1.d 1 100.00
Warnings:
-Note 1644 Table 't1' is not pushable: 'PROCEDURE'-clause post processing cannot be pushed.
-Note 1644 Table 't2' is not pushable: 'PROCEDURE'-clause post processing cannot be pushed.
+Note 1709 Table 't1' is not pushable: 'PROCEDURE'-clause post processing cannot be pushed.
+Note 1709 Table 't2' is not pushable: 'PROCEDURE'-clause post processing cannot be pushed.
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`.`d`) and (`test`.`t2`.`a` = `test`.`t1`.`c`))
explain extended
select *
@@ -331,7 +331,7 @@ id select_type table type possible_keys
1 SIMPLE t1 const PRIMARY PRIMARY 8 const,const 1 100.00
1 SIMPLE t2 const PRIMARY PRIMARY 8 const,const 0 0.00 unique row not found
Warnings:
-Note 1003 select '2' AS `a`,'3' AS `b`,'4' AS `c`,'5' AS `d`,NULL AS `a`,NULL AS `b`,NULL AS `c`,NULL AS `d` from `test`.`t1` left join `test`.`t1` `t2` on((multiple equal('4') and multiple equal('5'))) where 1
+Note 1003 select '2' AS `a`,'3' AS `b`,'4' AS `c`,'5' AS `d`,NULL AS `a`,NULL AS `b`,NULL AS `c`,NULL AS `d` from `test`.`t1` left join `test`.`t1` `t2` on((multiple equal('4', NULL) and multiple equal('5', NULL))) where 1
select *
from t1
left join t1 as t2 on t2.a = t1.c and t2.b = t1.d
@@ -397,7 +397,7 @@ id select_type table type possible_keys
1 SIMPLE t1 const PRIMARY PRIMARY 8 const,const 1 100.00
1 SIMPLE t2 const PRIMARY PRIMARY 8 const,const 1 100.00
Warnings:
-Note 1003 select '1' AS `a`,'1' AS `b`,'1' AS `c`,'1' AS `d`,'1' AS `a`,'1' AS `b`,'1' AS `c`,'1' AS `d` from `test`.`t1` left join `test`.`t1` `t2` on((multiple equal('1') and multiple equal('1'))) where 1
+Note 1003 select '1' AS `a`,'1' AS `b`,'1' AS `c`,'1' AS `d`,'1' AS `a`,'1' AS `b`,'1' AS `c`,'1' AS `d` from `test`.`t1` left join `test`.`t1` `t2` on((multiple equal('1', '1') and multiple equal('1', '1'))) where 1
select *
from t1
left join t1 as t2 on t2.a = t1.c and t2.b = t1.d
@@ -482,9 +482,9 @@ id select_type table type possible_keys
1 SIMPLE y ALL NULL NULL NULL NULL 16 100.00 Using join buffer
1 SIMPLE z eq_ref PRIMARY PRIMARY 8 test.x.a,test.y.b 1 100.00
Warnings:
-Note 1644 Can't push table 'y' as child, 'type' must be a 'ref' access
-Note 1644 Cannot push table 'z' as child of table 'x'. Doing so would prevent using join buffer for table 'y'.
-Note 1644 Cannot push table 'z' as child of 'y', since it referes to column 'x.a' which will be stored in a join buffer.
+Note 1709 Can't push table 'y' as child, 'type' must be a 'ref' access
+Note 1709 Cannot push table 'z' as child of table 'x'. Doing so would prevent using join buffer for table 'y'.
+Note 1709 Cannot push table 'z' as child of 'y', since it referes to column 'x.a' which will be stored in a join buffer.
Note 1003 select straight_join `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`,`test`.`z`.`a` AS `a`,`test`.`z`.`b` AS `b`,`test`.`z`.`c` AS `c`,`test`.`z`.`d` AS `d` from `test`.`t1` `x` join `test`.`t1` `y` join `test`.`t1` `z` where ((`test`.`z`.`b` = `test`.`y`.`b`) and (`test`.`z`.`a` = `test`.`x`.`a`))
select straight_join *
from (t1 as x cross join t1 as y)
@@ -756,7 +756,7 @@ id select_type table type possible_keys
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 't1' in pushed join@1; Using where
Warnings:
-Note 1644 Can't push table 't2' as child, column 'a' does neither 'ref' a column nor a constant
+Note 1709 Can't push table 't2' as child, column 'a' does neither 'ref' a column nor a constant
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`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t3`.`d` AS `d` from `test`.`t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t2`.`b` = `test`.`t1`.`c`) and (`test`.`t3`.`b` = `test`.`t1`.`c`) and (`test`.`t3`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`a` = (`test`.`t1`.`b` + 0)))
select *
from t1
@@ -787,7 +787,7 @@ id select_type table type possible_keys
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 't1' in pushed join@1; Using where
Warnings:
-Note 1644 Can't push table 't2' as child, column 'a' does neither 'ref' a column nor a constant
+Note 1709 Can't push table 't2' as child, column 'a' does neither 'ref' a column nor a constant
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`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t3`.`d` AS `d` from `test`.`t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t2`.`b` = `test`.`t1`.`c`) and (`test`.`t3`.`b` = `test`.`t1`.`c`) and (`test`.`t3`.`a` = `test`.`t1`.`b`) and (`test`.`t1`.`d` = 1) and (`test`.`t1`.`a` = 1) and (`test`.`t2`.`a` = (`test`.`t1`.`b` + 0)))
select *
from t1
@@ -806,8 +806,8 @@ id select_type table type possible_keys
1 SIMPLE t2 ref PRIMARY PRIMARY 4 func 1 100.00 Parent of 2 pushed join@1; Using where
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t2.b 1 100.00 Child of 't2' in pushed join@1
Warnings:
-Note 1644 Can't push table 't2' as child, column 'a' does neither 'ref' a column nor a constant
-Note 1644 Can't push table 't3' as child of 't1', column 't2.b' is outside scope of pushable join
+Note 1709 Can't push table 't2' as child, column 'a' does neither 'ref' a column nor a constant
+Note 1709 Can't push table 't3' as child of 't1', column 't2.b' is outside scope of pushable join
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`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t3`.`d` AS `d` from `test`.`t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`a` = (`test`.`t1`.`b` + 0)))
select *
from t1
@@ -899,7 +899,7 @@ id select_type table type possible_keys
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 const,const 1 100.00 Parent of 2 pushed join@1
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 const,test.t2.b 1 100.00 Child of 't2' in pushed join@1; Using where
Warnings:
-Note 1644 Table 't1' not in ndb engine, not pushable
+Note 1709 Table 't1' not in ndb engine, not pushable
Note 1003 select '2' AS `a`,'2' 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_myisam` `t1` join `test`.`t1` `t2` join `test`.`t1` `t3` where ((`test`.`t2`.`b` = '1') and (`test`.`t3`.`b` = '1') and (`test`.`t2`.`a` = '1') and (`test`.`t3`.`a` = '1'))
select *
from t1_myisam as t1
@@ -972,8 +972,8 @@ id select_type table type possible_keys
1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.c 1 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 't2' in pushed join@1
Warnings:
-Note 1644 Push of table 't2' as scan-child with lookup-root 't1' not implemented
-Note 1644 Can't push table 't3' as child of 't1', column 't2.c' is outside scope of pushable join
+Note 1709 Push of table 't2' as scan-child with lookup-root 't1' not implemented
+Note 1709 Can't push table 't3' as child of 't1', column 't2.c' is outside scope of pushable join
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`,`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` = `test`.`t1`.`c`) and (`test`.`t3`.`b` = `test`.`t2`.`d`) and (`test`.`t3`.`a` = `test`.`t2`.`c`) and (`test`.`t1`.`b` = 1) and (`test`.`t1`.`a` = 1))
select *
from t1
@@ -995,8 +995,8 @@ id select_type table type possible_keys
1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.c 1 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 't2' in pushed join@1
Warnings:
-Note 1644 Push of table 't2' as scan-child with lookup-root 't1' not implemented
-Note 1644 Can't push table 't3' as child of 't1', column 't2.c' is outside scope of pushable join
+Note 1709 Push of table 't2' as scan-child with lookup-root 't1' not implemented
+Note 1709 Can't push table 't3' as child of 't1', column 't2.c' is outside scope of pushable join
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`,`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` = `test`.`t1`.`c`)) left join `test`.`t1` `t3` on(((`test`.`t3`.`b` = `test`.`t2`.`d`) and (`test`.`t3`.`a` = `test`.`t2`.`c`))) where ((`test`.`t1`.`b` = 1) and (`test`.`t1`.`a` = 1))
select *
from t1
@@ -1448,7 +1448,7 @@ id select_type table type possible_keys
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t1.c,test.t1.d 1 100.00 Child of 't1' in pushed join@1
1 SIMPLE t4 eq_ref PRIMARY PRIMARY 8 test.t3.c,test.t2.c 1 100.00
Warnings:
-Note 1644 Can't push table 't4' as child of 't1', dependencies on outer joined grandparents not implemented
+Note 1709 Can't push table 't4' as child of 't1', dependencies on outer joined grandparents not implemented
Note 1003 select straight_join `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`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t3`.`d` AS `d`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t4`.`c` AS `c`,`test`.`t4`.`d` AS `d` from `test`.`t1` left join `test`.`t1` `t2` on(((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`a` = `test`.`t1`.`a`))) join `test`.`t1` `t3` left join `test`.`t1` `t4` on(((`test`.`t4`.`b` = `test`.`t2`.`c`) and (`test`.`t4`.`a` = `test`.`t3`.`c`))) where ((`test`.`t3`.`b` = `test`.`t1`.`d`) and (`test`.`t3`.`a` = `test`.`t1`.`c`))
explain extended
select straight_join *
@@ -1475,7 +1475,7 @@ id select_type table type possible_keys
1 SIMPLE t3 ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 Child of 't1' in pushed join@1
1 SIMPLE t4 eq_ref PRIMARY PRIMARY 8 test.t3.c,test.t2.c 1 100.00
Warnings:
-Note 1644 Can't push table 't4' as child of 't1', dependencies on outer joined grandparents not implemented
+Note 1709 Can't push table 't4' as child of 't1', dependencies on outer joined grandparents not implemented
Note 1003 select straight_join `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`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t3`.`d` AS `d`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t4`.`c` AS `c`,`test`.`t4`.`d` AS `d` from `test`.`t1` left join `test`.`t1` `t2` on(((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`a` = `test`.`t1`.`a`))) join `test`.`t1` `t3` left join `test`.`t1` `t4` on(((`test`.`t4`.`b` = `test`.`t2`.`c`) and (`test`.`t4`.`a` = `test`.`t3`.`c`))) where (`test`.`t3`.`a` = `test`.`t1`.`a`)
explain extended
select straight_join *
@@ -1489,8 +1489,8 @@ id select_type table type possible_keys
1 SIMPLE t3 ref PRIMARY PRIMARY 4 test.t2.a 1 100.00 Parent of 2 pushed join@2
1 SIMPLE t4 eq_ref PRIMARY PRIMARY 8 test.t3.c,test.t2.c 1 100.00 Child of 't3' in pushed join@2
Warnings:
-Note 1644 Can't push table 't3' as child of 't1', outer join of scan-child not implemented
-Note 1644 Can't push table 't4' as child of 't1', column 't3.c' is outside scope of pushable join
+Note 1709 Can't push table 't3' as child of 't1', outer join of scan-child not implemented
+Note 1709 Can't push table 't4' as child of 't1', column 't3.c' is outside scope of pushable join
Note 1003 select straight_join `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`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t3`.`d` AS `d`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t4`.`c` AS `c`,`test`.`t4`.`d` AS `d` from `test`.`t1` join `test`.`t1` `t2` left join `test`.`t1` `t3` on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`))) left join `test`.`t1` `t4` on(((`test`.`t4`.`b` = `test`.`t2`.`c`) and (`test`.`t4`.`a` = `test`.`t3`.`c`))) where ((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`a` = `test`.`t1`.`a`))
explain extended
select straight_join *
@@ -1504,7 +1504,7 @@ id select_type table type possible_keys
1 SIMPLE t3 ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 Child of 't1' in pushed join@1
1 SIMPLE t4 ref PRIMARY PRIMARY 4 test.t3.c 1 100.00
Warnings:
-Note 1644 Can't push table 't4' as child of 't1', outer join of scan-child not implemented
+Note 1709 Can't push table 't4' as child of 't1', outer join of scan-child not implemented
Note 1003 select straight_join `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`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t3`.`d` AS `d`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t4`.`c` AS `c`,`test`.`t4`.`d` AS `d` from `test`.`t1` left join `test`.`t1` `t2` on(((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`a` = `test`.`t1`.`a`))) join `test`.`t1` `t3` left join `test`.`t1` `t4` on((`test`.`t4`.`a` = `test`.`t3`.`c`)) where (`test`.`t3`.`a` = `test`.`t1`.`a`)
explain extended
select straight_join *
@@ -1518,9 +1518,9 @@ id select_type table type possible_keys
1 SIMPLE t3 ref PRIMARY PRIMARY 4 test.t2.a 1 100.00
1 SIMPLE t4 ref PRIMARY PRIMARY 4 test.t3.c 1 100.00
Warnings:
-Note 1644 Can't push table 't3' as child of 't1', outer join of scan-child not implemented
-Note 1644 Can't push table 't4' as child of 't1', column 't3.c' is outside scope of pushable join
-Note 1644 Can't push table 't4' as child of 't3', outer join of scan-child not implemented
+Note 1709 Can't push table 't3' as child of 't1', outer join of scan-child not implemented
+Note 1709 Can't push table 't4' as child of 't1', column 't3.c' is outside scope of pushable join
+Note 1709 Can't push table 't4' as child of 't3', outer join of scan-child not implemented
Note 1003 select straight_join `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`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t3`.`d` AS `d`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t4`.`c` AS `c`,`test`.`t4`.`d` AS `d` from `test`.`t1` join `test`.`t1` `t2` left join `test`.`t1` `t3` on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`))) left join `test`.`t1` `t4` on((`test`.`t4`.`a` = `test`.`t3`.`c`)) where ((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`a` = `test`.`t1`.`a`))
set ndb_join_pushdown=true;
explain extended
@@ -1680,7 +1680,7 @@ id select_type table type possible_keys
1 SIMPLE x ALL PRIMARY NULL NULL NULL 16 100.00 Parent of 2 pushed join@1; Using where
1 SIMPLE y eq_ref PRIMARY PRIMARY 8 test.x.d,test.x.b 1 100.00 Child of 'x' in 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`,`test`.`x`.`b`) in ((0,0),(5,0),(4,3)))))
+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`,`test`.`x`.`b`) in (<cache>((0,0)),<cache>((5,0)),<cache>((4,3))))))
select * from t1 x, t1 y where
(x.a <= 2 or (x.a,x.b) in ((0,0),(5,0),(4,3))) and
y.a=x.d and y.b=x.b;
@@ -1994,8 +1994,8 @@ id select_type table type possible_keys
1 SIMPLE t2 const PRIMARY PRIMARY 8 const,const 1 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.
-Note 1644 Table 't1' is not pushable: GROUP BY cannot be done using index on grouped columns.
+Note 1709 Table 't2' is not pushable: GROUP BY cannot be done using index on grouped columns.
+Note 1709 Table 't1' is not pushable: GROUP BY cannot be done using index on grouped columns.
Note 1003 select `test`.`t2`.`c` AS `c`,count(distinct `test`.`t2`.`a`) AS `count(distinct t2.a)` from `test`.`t1` join `test`.`t1` `t2` where ((`test`.`t1`.`b` = `test`.`t2`.`d`) and (`test`.`t1`.`a` = `test`.`t2`.`c`) and (`test`.`t2`.`b` = 4) and (`test`.`t2`.`a` = 4)) group by `test`.`t2`.`c`
select t2.c, count(distinct t2.a)
from t1
@@ -2014,8 +2014,8 @@ id select_type table type possible_keys
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.
-Note 1644 Table 't1' is not pushable: GROUP BY cannot be done using index on grouped columns.
+Note 1709 Table 't2' is not pushable: GROUP BY cannot be done using index on grouped columns.
+Note 1709 Table 't1' is not pushable: GROUP BY cannot be done using index on grouped columns.
Note 1003 select `test`.`t2`.`c` AS `c`,count(distinct `test`.`t2`.`a`) AS `count(distinct t2.a)` from `test`.`t1` join `test`.`t1` `t2` where ((`test`.`t1`.`b` = `test`.`t2`.`d`) and (`test`.`t1`.`a` = `test`.`t2`.`c`) and (`test`.`t2`.`a` = 4)) group by `test`.`t2`.`c`
select t2.c, count(distinct t2.a)
from t1
@@ -2035,8 +2035,8 @@ id select_type table type possible_keys
1 SIMPLE t2 const PRIMARY PRIMARY 8 const,const 1 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.
-Note 1644 Table 't1' is not pushable: GROUP BY cannot be done using index on grouped columns.
+Note 1709 Table 't2' is not pushable: GROUP BY cannot be done using index on grouped columns.
+Note 1709 Table 't1' is not pushable: GROUP BY cannot be done using index on grouped columns.
Note 1003 select `test`.`t2`.`c` AS `c`,count(distinct `test`.`t2`.`a`) AS `count(distinct t2.a)` from `test`.`t1` join `test`.`t1` `t2` where ((`test`.`t1`.`b` = `test`.`t2`.`d`) and (`test`.`t1`.`a` = `test`.`t2`.`c`) and (`test`.`t2`.`b` = 4) and (`test`.`t2`.`a` = 4)) group by `test`.`t2`.`c` order by `test`.`t2`.`c`
select t2.c, count(distinct t2.a)
from t1
@@ -2147,7 +2147,7 @@ where t1.a in (1,3,5)
and t2.a = t1.b
order by t1.a desc;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Parent of 2 pushed join@1; Using where with pushed condition: (`test`.`t1`.`a` in (1,3,5))
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 66.67 Parent of 2 pushed join@1; Using where with pushed condition: (`test`.`t1`.`a` in (1,3,5))
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 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`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t1` `t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t1`.`a` in (1,3,5))) order by `test`.`t1`.`a` desc
@@ -2223,7 +2223,7 @@ id select_type table type possible_keys
1 SIMPLE x ALL NULL NULL NULL NULL 3 66.67 Using where with pushed condition: (`test`.`x`.`b` = 0x1f)
1 SIMPLE y const PRIMARY PRIMARY 4 const 1 100.00
Warnings:
-Note 1644 Can't push table 'y' as child of 'x', their dependency is 'const'
+Note 1709 Can't push table 'y' as child of 'x', their dependency is 'const'
Note 1003 select straight_join `test`.`x`.`a` AS `a`,`test`.`x`.`b` AS `b`,`test`.`y`.`a` AS `a`,`test`.`y`.`b` AS `b` from `test`.`t1` `x` join `test`.`t1` `y` where ((`test`.`y`.`a` = 0x1f) and (`test`.`x`.`b` = 0x1f))
select straight_join * from t1 x, t1 y where y.a=0x1f and x.b = 0x1f;
a b a b
@@ -2234,7 +2234,7 @@ id select_type table type possible_keys
1 SIMPLE x ALL NULL NULL NULL NULL 3 100.00 Using where with pushed condition: (`test`.`x`.`b` = 0x1f)
1 SIMPLE y const PRIMARY PRIMARY 4 const 1 100.00 Using where
Warnings:
-Note 1644 Can't push table 'y' as child of 'x', their dependency is 'const'
+Note 1709 Can't push table 'y' as child of 'x', their dependency is 'const'
Note 1003 select straight_join `test`.`x`.`a` AS `a`,`test`.`x`.`b` AS `b`,`test`.`y`.`a` AS `a`,`test`.`y`.`b` AS `b` from `test`.`t1` `x` join `test`.`t1` `y` where ((`test`.`y`.`a` = `test`.`x`.`b`) and (`test`.`x`.`b` = 0x1f))
select straight_join * from t1 x, t1 y where y.a=x.b and x.b = 0x1f;
a b a b
@@ -2615,7 +2615,7 @@ id select_type table type possible_keys
1 SIMPLE x ALL NULL NULL NULL NULL 7 100.00 Parent of 2 pushed join@1
1 SIMPLE y ref b3 b3 9 const,test.x.d3 1 100.00 Child of 'x' in pushed join@1; Using where
Warnings:
-Note 1003 select straight_join `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`.`d3`) and (`test`.`y`.`b3` = (0x20 + 0x2f)))
+Note 1003 select straight_join `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`.`d3`) and (`test`.`y`.`b3` = <cache>((0x20 + 0x2f))))
select straight_join *
from t3 as x join t3 as y on x.d3 = y.d3
where y.b3 = 0x20+0x2f;
@@ -2871,7 +2871,7 @@ id select_type table type possible_keys
1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t3.b 1 100.00 Child of 't3' in pushed join@1
1 SIMPLE t1 ALL PRIMARY,b NULL NULL NULL 6 100.00 Range checked for each record (index map: 0x3)
Warnings:
-Note 1644 Table 't1' is not pushable: Access type was not chosen at 'prepare' time
+Note 1709 Table 't1' is not pushable: Access type was not chosen at 'prepare' time
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t4`.`c` AS `c` from `test`.`t1` join `test`.`t1` `t2` join `test`.`t1` `t3` join `test`.`t1` `t4` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and (`test`.`t4`.`a` = `test`.`t3`.`b`) and ((`test`.`t1`.`b` = `test`.`t2`.`b`) or (`test`.`t1`.`a` = `test`.`t2`.`b`)))
select *
from t1
@@ -2917,7 +2917,7 @@ id select_type table type possible_keys
2 DEPENDENT SUBQUERY y eq_ref PRIMARY,b PRIMARY 4 test.t1.b 1 100.00 Using where
Warnings:
Note 1276 Field or reference 'test.t1.b' of SELECT #2 was resolved in SELECT #1
-Note 1644 Can't push table 'y' as child of 'x', column 't1.b' is outside scope of pushable join
+Note 1709 Can't push table 'y' as child of 'x', column 't1.b' is outside scope of pushable join
Note 1003 select `test`.`t1`.`a` AS `a`,(select straight_join `test`.`x`.`a` from `test`.`t1` `x` join `test`.`t1` `y` where ((`test`.`y`.`b` = `test`.`x`.`a`) and (`test`.`y`.`a` = `test`.`t1`.`b`))) AS `(select straight_join x.a from t1 as x join t1 as y on x.a=y.b where y.a = t1.b)` from `test`.`t1`
select t1.a, (select straight_join x.a from t1 as x join t1 as y on x.a=y.b where y.a = t1.b) from t1;
a (select straight_join x.a from t1 as x join t1 as y on x.a=y.b where y.a = t1.b)
@@ -2941,8 +2941,8 @@ id select_type table type possible_keys
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.b 1 100.00
Warnings:
-Note 1644 Table 't2' not in ndb engine, not pushable
-Note 1644 Can't push table 't3' as child of 't1', column 't2.b' is outside scope of pushable join
+Note 1709 Table 't2' not in ndb engine, not pushable
+Note 1709 Can't push table 't3' as child of 't1', column 't2.b' is outside scope of pushable join
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` join `test`.`t2` join `test`.`t1` `t3` where ((`test`.`t3`.`a` = `test`.`t2`.`b`) and (`test`.`t2`.`a` = `test`.`t1`.`b`))
select * from t1, t2, t1 as t3
where t2.a = t1.b
@@ -3038,7 +3038,7 @@ id select_type table type possible_keys
1 SIMPLE t1 ALL NULL NULL NULL NULL 4 100.00
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00
Warnings:
-Note 1644 Table 't1' is not pushable: select list can't contain BLOB columns
+Note 1709 Table 't1' is not pushable: select list can't contain BLOB columns
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`a` = `test`.`t1`.`b`)
select *
from t1, t2
@@ -3057,7 +3057,7 @@ id select_type table type possible_keys
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 100.00
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 const 1 100.00
Warnings:
-Note 1644 Table 't1' was optimized away, or const'ified by optimizer
+Note 1709 Table 't1' was optimized away, or const'ified by optimizer
Note 1003 select '2' AS `a`,'1' AS `b`,'kalle' AS `c`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = '1'))
select *
from t1, t2
@@ -3073,7 +3073,7 @@ id select_type table type possible_keys
1 SIMPLE t2 ALL NULL NULL NULL NULL 4 100.00
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.b 1 100.00
Warnings:
-Note 1644 Table 't1' is not pushable: select list can't contain BLOB columns
+Note 1709 Table 't1' is not pushable: select list can't contain BLOB columns
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`b`)
select *
from t1, t2
@@ -3092,7 +3092,7 @@ id select_type table type possible_keys
1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 100.00
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.b 1 100.00
Warnings:
-Note 1644 Table 't1' is not pushable: select list can't contain BLOB columns
+Note 1709 Table 't1' is not pushable: select list can't contain BLOB columns
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = 3) and (`test`.`t1`.`a` = `test`.`t2`.`b`))
select *
from t1, t2
@@ -3126,9 +3126,9 @@ 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 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 'x' in pushed join@1
+1 SIMPLE y eq_ref PRIMARY PRIMARY 5 test.x.d3,const 1 100.00 Child of 'x' in 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` = (60 + 3)) and (`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f))
+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` = <cache>((60 + 3))) and (`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f))
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
a3 b3 c3 d3 a3 b3 c3 d3
47 63 2 47 47 63 2 47
@@ -3142,9 +3142,9 @@ 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 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 'x' in pushed join@1
+1 SIMPLE y eq_ref PRIMARY PRIMARY 6 test.x.d3,const 1 100.00 Child of 'x' in 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` = (60 + 3)) and (`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f))
+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` = <cache>((60 + 3))) and (`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f))
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
a3 b3 c3 d3 a3 b3 c3 d3
47 63 2 47 47 63 2 47
@@ -3158,9 +3158,9 @@ 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 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 'x' in pushed join@1
+1 SIMPLE y eq_ref PRIMARY PRIMARY 6 test.x.d3,const 1 100.00 Child of 'x' in 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` = (60 + 3)) and (`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f))
+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` = <cache>((60 + 3))) and (`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f))
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
a3 b3 c3 d3 a3 b3 c3 d3
47 63 2 47 47 63 2 47
@@ -3174,9 +3174,9 @@ 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 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 'x' in pushed join@1
+1 SIMPLE y eq_ref PRIMARY PRIMARY 7 test.x.d3,const 1 100.00 Child of 'x' in 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` = (60 + 3)) and (`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f))
+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` = <cache>((60 + 3))) and (`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f))
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
a3 b3 c3 d3 a3 b3 c3 d3
47 63 2 47 47 63 2 47
@@ -3190,9 +3190,9 @@ 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 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 'x' in pushed join@1
+1 SIMPLE y eq_ref PRIMARY PRIMARY 7 test.x.d3,const 1 100.00 Child of 'x' in 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` = (60 + 3)) and (`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f))
+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` = <cache>((60 + 3))) and (`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f))
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
a3 b3 c3 d3 a3 b3 c3 d3
47 63 2 47 47 63 2 47
@@ -3206,9 +3206,9 @@ 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 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 'x' in pushed join@1
+1 SIMPLE y eq_ref PRIMARY PRIMARY 8 test.x.d3,const 1 100.00 Child of 'x' in 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` = (60 + 3)) and (`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f))
+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` = <cache>((60 + 3))) and (`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f))
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
a3 b3 c3 d3 a3 b3 c3 d3
47 63 2 47 47 63 2 47
@@ -3222,9 +3222,9 @@ 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 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 'x' in pushed join@1
+1 SIMPLE y eq_ref PRIMARY PRIMARY 8 test.x.d3,const 1 100.00 Child of 'x' in 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` = (60 + 3)) and (`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f))
+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` = <cache>((60 + 3))) and (`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f))
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
a3 b3 c3 d3 a3 b3 c3 d3
47 63 2 47 47 63 2 47
@@ -3511,7 +3511,7 @@ id select_type table type possible_keys
1 SIMPLE x ALL NULL NULL NULL NULL 3 66.67
1 SIMPLE y eq_ref PRIMARY PRIMARY 5 test.x.b3,const 1 100.00 Using where
Warnings:
-Note 1644 Can't push table 'y' as child, column 'a3' does not have same datatype as ref'ed column 'x.b3'
+Note 1709 Can't push table 'y' as child, column 'a3' does not have same datatype as ref'ed column 'x.b3'
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`.`b3`) and (`test`.`y`.`b3` = '63'))
select * from t3 x, t3 y where y.a3=x.b3 and y.b3="63";
a3 b3 c3 d3 a3 b3 c3 d3
@@ -3535,7 +3535,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 3 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 2 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 'x' in 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'))
@@ -3557,8 +3557,8 @@ id select_type table type possible_keys
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.b 1 100.00 Child of 't2' in pushed join@2
1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00 Child of 't1' in pushed join@1
Warnings:
-Note 1644 Can't push table 't2' as child, column 'k' does neither 'ref' a column nor a constant
-Note 1644 Can't push table 't3' as child of 't1', column 't2.b' is outside scope of pushable join
+Note 1709 Can't push table 't2' as child, column 'k' does neither 'ref' a column nor a constant
+Note 1709 Can't push table 't3' as child of 't1', column 't2.b' is outside scope of pushable join
Note 1003 select `test`.`t1`.`k` AS `k`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`k` AS `k`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`k` AS `k`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`k` AS `k`,`test`.`t4`.`b` AS `b` from `test`.`t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` straight_join `test`.`t1` `t4` where ((`test`.`t3`.`k` = `test`.`t2`.`b`) and (`test`.`t4`.`k` = `test`.`t1`.`b`) and (`test`.`t2`.`k` = (`test`.`t1`.`b` + 0)))
select *
from t1
@@ -3583,8 +3583,8 @@ id select_type table type possible_keys
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.b 1 100.00 Child of 't2' in pushed join@2
1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00 Child of 't1' in pushed join@1
Warnings:
-Note 1644 Can't push table 't2' as child of 't1', their dependency is 'const'
-Note 1644 Can't push table 't3' as child of 't1', column 't2.b' is outside scope of pushable join
+Note 1709 Can't push table 't2' as child of 't1', their dependency is 'const'
+Note 1709 Can't push table 't3' as child of 't1', column 't2.b' is outside scope of pushable join
Note 1003 select `test`.`t1`.`k` AS `k`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`k` AS `k`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`k` AS `k`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`k` AS `k`,`test`.`t4`.`b` AS `b` from `test`.`t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` straight_join `test`.`t1` `t4` where ((`test`.`t3`.`k` = `test`.`t2`.`b`) and (`test`.`t4`.`k` = `test`.`t1`.`b`) and (`test`.`t2`.`k` = 1) and (1 = (`test`.`t1`.`b` + 0)))
select *
from t1
@@ -4070,7 +4070,7 @@ id select_type table type possible_keys
1 SIMPLE y const PRIMARY PRIMARY 4 const 1 100.00
1 SIMPLE x const PRIMARY,a PRIMARY 4 const 1 100.00
Warnings:
-Note 1644 Can't push table 'x' as child of 'y', their dependency is 'const'
+Note 1709 Can't push table 'x' as child of 'y', their dependency is 'const'
Note 1003 select `test`.`x`.`pk` AS `pk`,`test`.`x`.`a` AS `a`,`test`.`y`.`pk` AS `pk`,`test`.`y`.`a` AS `a` from `test`.`t1` `y` left join `test`.`t1` `x` on(((`test`.`y`.`a` = 2) and (`test`.`x`.`pk` = 2) and (`test`.`x`.`a` = 2))) where (`test`.`y`.`pk` = 2)
select * from t1 as x right join t1 as y
on x.pk = y.pk
@@ -4170,7 +4170,7 @@ id select_type table type possible_keys
1 SIMPLE x1 ALL NULL NULL NULL NULL 13 92.31
1 SIMPLE x2 ref ix1 ix1 5 test.x1.a 2 100.00
Warnings:
-Note 1644 Can't push table 'x2' as child of 'x1', outer join of scan-child not implemented
+Note 1709 Can't push table 'x2' as child of 'x1', outer join of scan-child not implemented
Note 1003 select count(0) AS `count(*)` from `test`.`t1` `x1` left join `test`.`t1` `x2` on((`test`.`x1`.`a` = `test`.`x2`.`b`)) where 1
select count(*) from t1 as x1 left join t1 as x2 on x1.a=x2.b;
count(*)
@@ -4188,7 +4188,7 @@ id select_type table type possible_keys
1 SIMPLE x2 eq_ref PRIMARY PRIMARY 4 test.x1.u 1 100.00 Child of 'x1' in pushed join@1
1 SIMPLE x3 ref ix1 ix1 5 test.x2.a 2 100.00
Warnings:
-Note 1644 Can't push table 'x3' as child of 'x1', outer join of scan-child not implemented
+Note 1709 Can't push table 'x3' as child of 'x1', outer join of scan-child not implemented
Note 1003 select count(0) AS `count(*)` from `test`.`t1` `x1` left join `test`.`t1` `x2` on((`test`.`x1`.`u` = `test`.`x2`.`pk`)) left join `test`.`t1` `x3` on((`test`.`x2`.`a` = `test`.`x3`.`b`)) where 1
select count(*) from t1 as x1
left join t1 as x2 on x1.u=x2.pk
@@ -4214,10 +4214,10 @@ id select_type table type possible_keys
1 SIMPLE x4 eq_ref PRIMARY PRIMARY 4 test.x3.u 1 100.00 Child of 'x3' in pushed join@2
1 SIMPLE x5 ref ix1 ix1 5 test.x4.a 2 100.00
Warnings:
-Note 1644 Can't push table 'x3' as child of 'x1', outer join of scan-child not implemented
-Note 1644 Can't push table 'x4' as child of 'x1', column 'x3.u' is outside scope of pushable join
-Note 1644 Can't push table 'x5' as child of 'x1', column 'x4.a' is outside scope of pushable join
-Note 1644 Can't push table 'x5' as child of 'x3', outer join of scan-child not implemented
+Note 1709 Can't push table 'x3' as child of 'x1', outer join of scan-child not implemented
+Note 1709 Can't push table 'x4' as child of 'x1', column 'x3.u' is outside scope of pushable join
+Note 1709 Can't push table 'x5' as child of 'x1', column 'x4.a' is outside scope of pushable join
+Note 1709 Can't push table 'x5' as child of 'x3', outer join of scan-child not implemented
Note 1003 select count(0) AS `count(*)` from `test`.`t1` `x1` left join `test`.`t1` `x2` on((`test`.`x1`.`u` = `test`.`x2`.`pk`)) left join `test`.`t1` `x3` on((`test`.`x2`.`a` = `test`.`x3`.`b`)) left join `test`.`t1` `x4` on((`test`.`x3`.`u` = `test`.`x4`.`pk`)) left join `test`.`t1` `x5` on((`test`.`x4`.`a` = `test`.`x5`.`b`)) where 1
select count(*) from t1 as x1
left join t1 as x2 on x1.u=x2.pk
@@ -4242,7 +4242,7 @@ id select_type table type possible_keys
1 SIMPLE x1 index_merge PRIMARY,ix2 ix2,PRIMARY 4,4 NULL 2 100.00 Using sort_union(ix2,PRIMARY); Using where with pushed condition: ((`test`.`x1`.`pk` = 1) or (`test`.`x1`.`u` = 1))
1 SIMPLE x2 ref ix1 ix1 5 test.x1.a 2 100.00 Using where
Warnings:
-Note 1644 Push of table 'x2' as scan-child with lookup-root 'x1' not implemented
+Note 1709 Push of table 'x2' as scan-child with lookup-root 'x1' not implemented
Note 1003 select count(0) AS `count(*)` from `test`.`t1` `x1` join `test`.`t1` `x2` where ((`test`.`x2`.`b` = `test`.`x1`.`a`) and ((`test`.`x1`.`pk` = 1) or (`test`.`x1`.`u` = 1)))
select count(*) from t1 as x1
join t1 as x2 on x1.a=x2.b
@@ -4260,8 +4260,8 @@ id select_type table type possible_keys
1 SIMPLE table2 ref PRIMARY,ix1 ix1 5 test.table1.pk 2 100.00 Parent of 2 pushed join@1
1 SIMPLE table3 ref ix1 ix1 5 test.table2.pk 2 100.00 Child of 'table2' in pushed join@1
Warnings:
-Note 1644 Can't push table 'table2' as child of 'table1', outer join of scan-child not implemented
-Note 1644 Can't push table 'table3' as child of 'table1', column 'table2.pk' is outside scope of pushable join
+Note 1709 Can't push table 'table2' as child of 'table1', outer join of scan-child not implemented
+Note 1709 Can't push table 'table3' as child of 'table1', column 'table2.pk' is outside scope of pushable join
Note 1003 select straight_join `test`.`table1`.`pk` AS `pk`,`test`.`table1`.`u` AS `u`,`test`.`table1`.`a` AS `a`,`test`.`table1`.`b` AS `b`,`test`.`table2`.`pk` AS `pk`,`test`.`table2`.`u` AS `u`,`test`.`table2`.`a` AS `a`,`test`.`table2`.`b` AS `b`,`test`.`table3`.`pk` AS `pk`,`test`.`table3`.`u` AS `u`,`test`.`table3`.`a` AS `a`,`test`.`table3`.`b` AS `b` from `test`.`t1` `table1` left join (`test`.`t1` `table2` join `test`.`t1` `table3`) on(((`test`.`table1`.`pk` = `test`.`table2`.`b`) and (`test`.`table2`.`pk` = `test`.`table3`.`b`))) where 1
select straight_join * from t1 as table1
left join
@@ -4306,15 +4306,15 @@ id select_type table type possible_keys
1 SIMPLE x1 ALL NULL NULL NULL NULL 13 100.00
1 SIMPLE x2 ref ix1 ix1 5 test.x1.a 2 100.00
Warnings:
-Note 1644 Can't push table 'x2' as child of 'x1', outer join of scan-child not implemented
+Note 1709 Can't push table 'x2' as child of 'x1', outer join of scan-child not implemented
Note 1003 select straight_join `test`.`x1`.`pk` AS `pk`,`test`.`x1`.`u` AS `u`,`test`.`x1`.`a` AS `a`,`test`.`x1`.`b` AS `b`,`test`.`x2`.`pk` AS `pk`,`test`.`x2`.`u` AS `u`,`test`.`x2`.`a` AS `a`,`test`.`x2`.`b` AS `b` from `test`.`t1` `x1` left join `test`.`t1` `x2` on((`test`.`x2`.`b` = `test`.`x1`.`a`)) where 1
explain extended select straight_join * from t1 as x1
right join t1 as x2 on x2.b = x1.a;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE x2 ALL NULL NULL NULL NULL 13 100.00
+1 SIMPLE x2 ALL NULL NULL NULL NULL 13 92.31
1 SIMPLE x1 ALL NULL NULL NULL NULL 13 100.00
Warnings:
-Note 1644 Can't push table 'x1' as child, 'type' must be a 'ref' access
+Note 1709 Can't push table 'x1' as child, 'type' must be a 'ref' access
Note 1003 select straight_join `test`.`x1`.`pk` AS `pk`,`test`.`x1`.`u` AS `u`,`test`.`x1`.`a` AS `a`,`test`.`x1`.`b` AS `b`,`test`.`x2`.`pk` AS `pk`,`test`.`x2`.`u` AS `u`,`test`.`x2`.`a` AS `a`,`test`.`x2`.`b` AS `b` from `test`.`t1` `x2` left join `test`.`t1` `x1` on((`test`.`x2`.`b` = `test`.`x1`.`a`)) where 1
explain extended select straight_join * from
t1 as x1 inner join
@@ -4335,7 +4335,7 @@ id select_type table type possible_keys
1 SIMPLE x2 eq_ref PRIMARY PRIMARY 4 test.x1.a 1 100.00 Child of 'x1' in pushed join@1
1 SIMPLE x3 ref ix1 ix1 5 test.x2.a 2 100.00
Warnings:
-Note 1644 Can't push table 'x3' as child of 'x1', outer join of scan-child not implemented
+Note 1709 Can't push table 'x3' as child of 'x1', outer join of scan-child not implemented
Note 1003 select straight_join `test`.`x1`.`pk` AS `pk`,`test`.`x1`.`u` AS `u`,`test`.`x1`.`a` AS `a`,`test`.`x1`.`b` AS `b`,`test`.`x2`.`pk` AS `pk`,`test`.`x2`.`u` AS `u`,`test`.`x2`.`a` AS `a`,`test`.`x2`.`b` AS `b`,`test`.`x3`.`pk` AS `pk`,`test`.`x3`.`u` AS `u`,`test`.`x3`.`a` AS `a`,`test`.`x3`.`b` AS `b` from `test`.`t1` `x1` left join (`test`.`t1` `x2` join `test`.`t1` `x3`) on(((`test`.`x2`.`pk` = `test`.`x1`.`a`) and (`test`.`x3`.`b` = `test`.`x2`.`a`))) where 1
update spj_save_counts set val = (select sum(val) from ndbinfo.counters where block_name='DBSPJ' and counter_name='SCAN_ROWS_RETURNED') where counter_name='SCAN_ROWS_RETURNED';
explain extended select straight_join count(*) from t1 as x1
@@ -4396,7 +4396,7 @@ id select_type table type possible_keys
1 SIMPLE x2 ref ix1 ix1 5 test.x1.a 2 100.00
1 SIMPLE x3 ref ix1 ix1 5 test.x1.b 2 100.00 Child of 'x1' in pushed join@1; Using where
Warnings:
-Note 1644 Can't push table 'x2' as child of 'x1', outer join of scan-child not implemented
+Note 1709 Can't push table 'x2' as child of 'x1', outer join of scan-child not implemented
Note 1003 select straight_join count(0) AS `count(*)` from `test`.`t1` `x1` left join `test`.`t1` `x2` on((`test`.`x2`.`b` = `test`.`x1`.`a`)) join `test`.`t1` `x3` where (`test`.`x3`.`b` = `test`.`x1`.`b`)
set ndb_join_pushdown=off;
select straight_join count(*) from t1 as x1
@@ -4418,7 +4418,7 @@ id select_type table type possible_keys
1 SIMPLE x2 ref ix1 ix1 5 test.x1.a 2 100.00 Child of 'x1' in pushed join@1; Using where
1 SIMPLE x3 ref ix1 ix1 5 test.x1.b 2 100.00
Warnings:
-Note 1644 Can't push table 'x3' as child of 'x1', outer join of scan-child not implemented
+Note 1709 Can't push table 'x3' as child of 'x1', outer join of scan-child not implemented
Note 1003 select straight_join count(0) AS `count(*)` from `test`.`t1` `x1` join `test`.`t1` `x2` left join `test`.`t1` `x3` on((`test`.`x3`.`b` = `test`.`x1`.`b`)) where (`test`.`x2`.`b` = `test`.`x1`.`a`)
set ndb_join_pushdown=off;
select straight_join count(*) from t1 as x1
@@ -4465,7 +4465,7 @@ id select_type table type possible_keys
1 SIMPLE x3 ref ix1 ix1 5 test.x1.a 2 100.00
1 SIMPLE x2 eq_ref PRIMARY PRIMARY 4 test.x1.a 1 100.00 Child of 'x1' in pushed join@1
Warnings:
-Note 1644 Can't push table 'x3' as child of 'x1', outer join of scan-child not implemented
+Note 1709 Can't push table 'x3' as child of 'x1', outer join of scan-child not implemented
Note 1003 select straight_join count(0) AS `count(*)` from `test`.`t1` `x1` left join `test`.`t1` `x3` on(((`test`.`x3`.`b` = `test`.`x1`.`a`) and (`test`.`x2`.`pk` = `test`.`x1`.`a`))) join `test`.`t1` `x2` where (`test`.`x2`.`pk` = `test`.`x1`.`a`)
select straight_join count(*) from t1 as x1
left join t1 as x3 on x3.b = x1.a
@@ -4510,8 +4510,8 @@ id select_type table type possible_keys
1 SIMPLE table2 const PRIMARY,ix2 PRIMARY 4 const 1 100.00 Parent of 2 pushed join@1
1 SIMPLE table3 ref ix2 ix2 9 test.table2.a,test.table1.u 1 100.00 Child of 'table2' in pushed join@1; Using where
Warnings:
-Note 1644 Can't push table 'table2' as child of 'table1', their dependency is 'const'
-Note 1644 Can't push table 'table3' as child of 'table1', column 'table2.a' is outside scope of pushable join
+Note 1709 Can't push table 'table2' as child of 'table1', their dependency is 'const'
+Note 1709 Can't push table 'table3' as child of 'table1', column 'table2.a' is outside scope of pushable join
Note 1003 select straight_join `test`.`table1`.`pk` AS `pk`,`test`.`table1`.`u` AS `u`,`test`.`table1`.`a` AS `a`,`test`.`table1`.`b` AS `b`,`test`.`table2`.`pk` AS `pk`,`test`.`table2`.`u` AS `u`,`test`.`table2`.`a` AS `a`,`test`.`table2`.`b` AS `b`,`test`.`table3`.`pk` AS `pk`,`test`.`table3`.`u` AS `u`,`test`.`table3`.`a` AS `a`,`test`.`table3`.`b` AS `b` from `test`.`t1` `table1` join `test`.`t1` `table2` join `test`.`t1` `table3` where ((`test`.`table3`.`a` = `test`.`table2`.`a`) and (`test`.`table3`.`u` = `test`.`table1`.`u`) and (`test`.`table2`.`pk` = 3))
select straight_join * from
t1 as table1 join
@@ -4916,10 +4916,10 @@ id select_type table type possible_keys
1 SIMPLE table3 eq_ref PRIMARY PRIMARY 4 test.table2.pk 1 100.00 Child of 'table2' in pushed join@1; Distinct
1 SIMPLE table4 eq_ref PRIMARY PRIMARY 4 test.table1.a 1 100.00 Using where; Distinct
Warnings:
-Note 1644 Can't push table 'table2' as child, 'type' must be a 'ref' access
-Note 1644 Cannot push table 'table3' as child of table 'table1'. Doing so would prevent using join buffer for table 'table2'.
-Note 1644 Cannot push table 'table4' as child of table 'table1'. Doing so would prevent using join buffer for table 'table2'.
-Note 1644 Cannot push table 'table4' as child of 'table2', since it referes to column 'table1.a' which will be stored in a join buffer.
+Note 1709 Can't push table 'table2' as child, 'type' must be a 'ref' access
+Note 1709 Cannot push table 'table3' as child of table 'table1'. Doing so would prevent using join buffer for table 'table2'.
+Note 1709 Cannot push table 'table4' as child of table 'table1'. Doing so would prevent using join buffer for table 'table2'.
+Note 1709 Cannot push table 'table4' as child of 'table2', since it referes to column 'table1.a' which will be stored in a join buffer.
Note 1003 select straight_join distinct `test`.`table1`.`pk` AS `pk` from `test`.`t` `table1` join `test`.`t` `table2` join `test`.`t` `table3` join `test`.`t` `table4` where ((`test`.`table3`.`pk` = `test`.`table2`.`pk`) and (`test`.`table4`.`a` = `test`.`table2`.`pk`) and (`test`.`table4`.`pk` = `test`.`table1`.`a`) and (`test`.`table2`.`pk` <> 6))
select distinct straight_join table1.pk FROM
t as table1 join
@@ -4956,7 +4956,7 @@ id select_type table type possible_keys
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
+Note 1709 Push of table 't2' as scan-child with ordered indexscan-root 't1' not implemented
Note 1003 select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`pk2` AS `pk2`,`test`.`t2`.`pk1` AS `pk1`,`test`.`t2`.`pk2` AS `pk2` from `test`.`t` `t1` join `test`.`t` `t2` where ((`test`.`t2`.`pk1` = `test`.`t1`.`pk2`) and (`test`.`t1`.`pk1` <> 6)) order by `test`.`t1`.`pk1` desc
select * from t as t1 join t as t2
on t1.pk2 = t2.pk1
@@ -4976,7 +4976,7 @@ id select_type table type possible_keys
1 SIMPLE a ALL ix1 NULL NULL NULL 4 50.00
1 SIMPLE b ref_or_null ix1 ix1 5 test.a.uq 2 100.00 Using where
Warnings:
-Note 1644 Table 'b' is not pushable: This table access method can not be pushed.
+Note 1709 Table 'b' is not pushable: This table access method can not be pushed.
Note 1003 select straight_join `test`.`a`.`k` AS `k`,`test`.`a`.`uq` AS `uq`,`test`.`b`.`k` AS `k`,`test`.`b`.`uq` AS `uq` from `test`.`t` `a` join `test`.`t` `b` where ((`test`.`b`.`uq` = `test`.`a`.`uq`) or isnull(`test`.`b`.`uq`))
select straight_join * from t as a join t as b
on a.uq=b.uq or b.uq is null;
@@ -4998,8 +4998,8 @@ id select_type table type possible_keys
1 SIMPLE b system NULL NULL NULL NULL 1 100.00
1 SIMPLE a ALL NULL NULL NULL NULL 4 50.00
Warnings:
-Note 1644 Table 'b' was optimized away, or const'ified by optimizer
-Note 1003 select `test`.`a`.`k` AS `k`,`test`.`a`.`uq` AS `uq`,NULL AS `k`,NULL AS `uq` from `test`.`t` `a` left join `test`.`t` `b` on((isnull(`test`.`a`.`k`) and (`test`.`a`.`uq` = NULL)))
+Note 1709 Table 'b' was optimized away, or const'ified by optimizer
+Note 1003 select `test`.`a`.`k` AS `k`,`test`.`a`.`uq` AS `uq`,NULL AS `k`,NULL AS `uq` from `test`.`t` `a`
select * from t as a left join t as b
on a.k is null and a.uq=b.uq;
k uq k uq
@@ -5024,8 +5024,8 @@ id select_type table type possible_keys
1 SIMPLE x1 ref uk1 uk1 13 test.x2.a 1 100.00 Parent of 2 pushed join@1
1 SIMPLE x3 ref uk1 uk1 26 test.x2.b,test.x1.c 1 100.00 Child of 'x1' in pushed join@1
Warnings:
-Note 1644 Can't push table 'x1' as child of 'x2', outer join of scan-child not implemented
-Note 1644 Can't push table 'x3' as child of 'x2', column 'x1.c' is outside scope of pushable join
+Note 1709 Can't push table 'x1' as child of 'x2', outer join of scan-child not implemented
+Note 1709 Can't push table 'x3' as child of 'x2', column 'x1.c' is outside scope of pushable join
Note 1003 select `test`.`x1`.`a` AS `a`,`test`.`x1`.`b` AS `b`,`test`.`x1`.`c` AS `c`,`test`.`x2`.`a` AS `a`,`test`.`x2`.`b` AS `b`,`test`.`x2`.`c` AS `c`,`test`.`x3`.`a` AS `a`,`test`.`x3`.`b` AS `b`,`test`.`x3`.`c` AS `c` from `test`.`tc` `x2` left join `test`.`tc` `x1` on((`test`.`x1`.`b` = `test`.`x2`.`a`)) left join `test`.`tc` `x3` on(((`test`.`x2`.`b` = `test`.`x3`.`b`) and (`test`.`x1`.`c` = `test`.`x3`.`c`))) where 1
select * from tc as x1
right outer join tc as x2 on x1.b=x2.a
@@ -5036,11 +5036,11 @@ aa bb x bb cc x bb cc x
bb cc x cc dd x cc dd x
explain extended select * from tc as x1, tc as x2 where x1.b=x2.a for update;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE x1 ALL uk1 NULL NULL NULL 3 100.00
+1 SIMPLE x1 ALL uk1 NULL NULL NULL 3 66.67
1 SIMPLE x2 eq_ref PRIMARY PRIMARY 12 test.x1.b 1 100.00
Warnings:
-Note 1644 Table 'x1' is not pushable: lock modes other than 'read committed' not implemented
-Note 1644 Table 'x2' is not pushable: lock modes other than 'read committed' not implemented
+Note 1709 Table 'x1' is not pushable: lock modes other than 'read committed' not implemented
+Note 1709 Table 'x2' is not pushable: lock modes other than 'read committed' not implemented
Note 1003 select `test`.`x1`.`a` AS `a`,`test`.`x1`.`b` AS `b`,`test`.`x1`.`c` AS `c`,`test`.`x2`.`a` AS `a`,`test`.`x2`.`b` AS `b`,`test`.`x2`.`c` AS `c` from `test`.`tc` `x1` join `test`.`tc` `x2` where (`test`.`x2`.`a` = `test`.`x1`.`b`)
explain extended select * from tc as x1, tc as x2 where x1.b=x2.a;
id select_type table type possible_keys key key_len ref rows filtered Extra
@@ -5097,10 +5097,10 @@ id select_type table type possible_keys
1 SIMPLE x4 ALL NULL NULL NULL NULL 8 100.00 Parent of 2 pushed join@1
1 SIMPLE x1 ref PRIMARY PRIMARY 4 test.x4.d 1 100.00 Child of 'x4' in pushed join@1
Warnings:
-Note 1644 Can't push table 'x3' as child of 'x2', outer join of scan-child not implemented
-Note 1644 Can't push table 'x4' as child, 'type' must be a 'ref' access
-Note 1644 Can't push table 'x1' as child of 'x2', column 'x4.d' is outside scope of pushable join
-Note 1644 Can't push table 'x1' as child of 'x3', column 'x4.d' is outside scope of pushable join
+Note 1709 Can't push table 'x3' as child of 'x2', outer join of scan-child not implemented
+Note 1709 Can't push table 'x4' as child, 'type' must be a 'ref' access
+Note 1709 Can't push table 'x1' as child of 'x2', column 'x4.d' is outside scope of pushable join
+Note 1709 Can't push table 'x1' as child of 'x3', column 'x4.d' is outside scope of pushable join
Note 1003 select count(0) AS `count(*)` from `test`.`t1` `x1` join `test`.`t1` `x2` left join (`test`.`t1` `x3` join `test`.`t1` `x4`) on((`test`.`x3`.`a` = `test`.`x2`.`d`)) where (`test`.`x1`.`a` = `test`.`x4`.`d`)
select count(*) from t1 as x1
join (t1 as x2
@@ -5115,12 +5115,12 @@ left join (t1 as x2
cross join t1 as x3)
on x1.d=x2.a;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE x1 ALL NULL NULL NULL NULL 8 100.00
+1 SIMPLE x1 ALL NULL NULL NULL NULL 8 25.00
1 SIMPLE x2 ref PRIMARY PRIMARY 4 test.x1.d 1 100.00
1 SIMPLE x3 ALL NULL NULL NULL NULL 8 100.00
Warnings:
-Note 1644 Can't push table 'x2' as child of 'x1', outer join of scan-child not implemented
-Note 1644 Can't push table 'x3' as child, 'type' must be a 'ref' access
+Note 1709 Can't push table 'x2' as child of 'x1', outer join of scan-child not implemented
+Note 1709 Can't push table 'x3' as child, 'type' must be a 'ref' access
Note 1003 select count(0) AS `count(*)` from `test`.`t1` `x1` left join (`test`.`t1` `x2` join `test`.`t1` `x3`) on((`test`.`x1`.`d` = `test`.`x2`.`a`)) where 1
select count(*) from t1 as x1
left join (t1 as x2
@@ -5143,14 +5143,14 @@ id select_type table type possible_keys
1 SIMPLE x3 ref PRIMARY PRIMARY 4 test.x2.d 1 100.00 Parent of 2 pushed join@1
1 SIMPLE x4 ref PRIMARY PRIMARY 4 test.x3.d 1 100.00 Child of 'x3' in pushed join@1
Warnings:
-Note 1644 Can't push table 'x1' as child of 'x0', outer join of scan-child not implemented
-Note 1644 Can't push table 'x2' as child, 'type' must be a 'ref' access
-Note 1644 Can't push table 'x3' as child of 'x0', column 'x2.d' is outside scope of pushable join
-Note 1644 Can't push table 'x4' as child of 'x0', column 'x3.d' is outside scope of pushable join
-Note 1644 Can't push table 'x3' as child of 'x1', column 'x2.d' is outside scope of pushable join
-Note 1644 Can't push table 'x4' as child of 'x1', column 'x3.d' is outside scope of pushable join
-Note 1644 Can't push table 'x3' as child of 'x2', outer join of scan-child not implemented
-Note 1644 Can't push table 'x4' as child of 'x2', column 'x3.d' is outside scope of pushable join
+Note 1709 Can't push table 'x1' as child of 'x0', outer join of scan-child not implemented
+Note 1709 Can't push table 'x2' as child, 'type' must be a 'ref' access
+Note 1709 Can't push table 'x3' as child of 'x0', column 'x2.d' is outside scope of pushable join
+Note 1709 Can't push table 'x4' as child of 'x0', column 'x3.d' is outside scope of pushable join
+Note 1709 Can't push table 'x3' as child of 'x1', column 'x2.d' is outside scope of pushable join
+Note 1709 Can't push table 'x4' as child of 'x1', column 'x3.d' is outside scope of pushable join
+Note 1709 Can't push table 'x3' as child of 'x2', outer join of scan-child not implemented
+Note 1709 Can't push table 'x4' as child of 'x2', column 'x3.d' is outside scope of pushable join
Note 1003 select count(0) AS `count(*)` from `test`.`t1` `x0` left join (`test`.`t1` `x1` join `test`.`t1` `x2` left join (`test`.`t1` `x3` join `test`.`t1` `x4`) on(((`test`.`x2`.`d` = `test`.`x3`.`a`) and (`test`.`x3`.`d` = `test`.`x4`.`a`)))) on(((`test`.`x0`.`d` = `test`.`x1`.`a`) and (isnull(`test`.`x2`.`c`) or (`test`.`x1`.`a` = `test`.`x4`.`d`)))) where 1
select count(*) from t1 as x0
left join (t1 as x1
Attachment: [text/bzr-bundle] bzr/ole.john.aske@oracle.com-20110623134445-g43vhqsaz7wldpoo.bundle
| Thread |
|---|
| • bzr commit into mysql-5.5-cluster-spj branch (ole.john.aske:3372) | Ole John Aske | 23 Jun |
