in mysql-test/suite/ndb/t/ndb_join_pushdown.inc [34:2697]
create table t1 (
a int not null,
b int not null,
c int not null,
d int not null,
primary key (`a`,`b`)
) engine=ndbcluster
partition by key() partitions 8;
connection spj;
insert into t1 values
(1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4),
(1,2,5,1), (1,3,1,2), (1,4,2,3),
(2,1,3,4), (2,3,4,5), (2,4,5,1),
(3,1,1,2), (3,2,2,3), (3,4,3,4),
(4,1,4,5), (4,2,5,1), (4,3,1,2);
explain
select *
from t1
join t1 as t2 on t2.a = t1.b and t2.b = t1.c;
--sorted_result
select *
from t1
join t1 as t2 on t2.a = t1.b and t2.b = t1.c;
# Check that new JSON explain format is also handled:
explain format=JSON
select *
from t1
join t1 as t2 on t2.a = t1.b and t2.b = t1.c;
# Check that we do not push an operation if this prevents us from using
# 'join buffer'.
explain
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;
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;
# Check that we do not push an operation if this prevents us from using
# 'join buffer'.
explain select *
from t1 as x1
join t1 as x2 on x1.a=1 and x1.c=x2.a and x1.d=x2.b
join t1 as x3
join t1 as x4 where x4.a=x3.c and x4.b=x1.d;
--sorted_result
select *
from t1 as x1
join t1 as x2 on x1.a=1 and x1.c=x2.a and x1.d=x2.b
join t1 as x3
join t1 as x4 where x4.a=x3.c and x4.b=x1.d;
explain
select *
from t1
left join t1 as t2 on t2.a = t1.b and t2.b = t1.c;
--sorted_result
select *
from t1
left join t1 as t2 on t2.a = t1.b and t2.b = t1.c;
explain
select *
from t1
join t1 as t2 on t2.a = t1.b and t2.b = t1.c
join t1 as t3 on t3.a = t2.a and t3.b = t2.b
where t1.a = 1 and t1.b = 1;
--sorted_result
select *
from t1
join t1 as t2 on t2.a = t1.b and t2.b = t1.c
join t1 as t3 on t3.a = t2.a and t3.b = t2.b
where t1.a = 1 and t1.b = 1;
explain
select *
from t1
join t1 as t2 on t2.a = t1.b and t2.b = t1.c
join t1 as t3 on t3.a = t2.a and t3.b = t2.b;
--sorted_result
select *
from t1
join t1 as t2 on t2.a = t1.b and t2.b = t1.c
join t1 as t3 on t3.a = t2.a and t3.b = t2.b;
explain
select *
from t1
join t1 as t2 on t2.a = t1.b and t2.b = t1.c
left join t1 as t3 on t3.a = t2.a and t3.b = t2.b;
--sorted_result
select *
from t1
join t1 as t2 on t2.a = t1.b and t2.b = t1.c
left join t1 as t3 on t3.a = t2.a and t3.b = t2.b;
explain
select *
from t1
left join t1 as t2 on t2.a = t1.b and t2.b = t1.c
left join t1 as t3 on t3.a = t2.a and t3.b = t2.b;
--sorted_result
select *
from t1
left join t1 as t2 on t2.a = t1.b and t2.b = t1.c
left join t1 as t3 on t3.a = t2.a and t3.b = t2.b;
set ndb_join_pushdown=true;
--replace_result p0,p1,p2,p3,p4,p5,p6,p7 pXYZ p0 pX p5 pX
explain
select *
from t1
join t1 as t2 on t2.a = t1.c and t2.b = t1.d
where t1.a = 2 and t1.b = 3;
select *
from t1
join t1 as t2 on t2.a = t1.c and t2.b = t1.d
where t1.a = 2 and t1.b = 3;
################################################################
# Bug#13901890 SQL NODE CRASHES DURING INSERT SELECT
#
# Incorrect lifetime handling for NdbQuery objects where
# the root operation was a 'const' lookup operation.
#
# 'virtual handler::index_read_idx_map()' was incorectly
# implemented by ha_ndbcluster, such that it failed to
# 'close' the table when the single 'const' row has been read.
# Neither was it registered as 'open' by using the ::ha_index_init().
# (Called ha_ndbcluster::index_init() directly)
# This resulted in that NdbQuery::close() was never called
# on these 'const' SPJ queries. Instead NdbTransaction end
# forcefully whiped them away by calling NdbQuery::release().
# However, as there still was dangling pointer refs to
# them from ha_ndbcluster::m_active_query, we may later refer
# them and crash!
#
# - Same query as above.
# - Added explict table locks as cleanup of these is one
# (of several?) way to cause released NdbQuery objects to
# be refered.
################################################################
# LOCK'ed tables invokes the same code path as executing
# an 'INSERT... SELECT' inside a procedure invoked from a trigger.
# ... and is a much simpler testcase.....
LOCK TABLES t1 read, t1 as t2 read;
--replace_result p0,p1,p2,p3,p4,p5,p6,p7 pXYZ p0 pX p5 pX
explain
select *
from t1
join t1 as t2 on t2.a = t1.c and t2.b = t1.d
where t1.a = 2 and t1.b = 3;
#Returns empty result set
select *
from t1
join t1 as t2 on t2.a = t1.c and t2.b = t1.d
where t1.a = 2 and t1.b = 3;
#Returns a single row
select *
from t1
join t1 as t2 on t2.a = t1.c and t2.b = t1.d
where t1.a = 3 and t1.b = 3;
UNLOCK TABLES;
--replace_result p0,p1,p2,p3,p4,p5,p6,p7 pXYZ p0 pX p5 pX
explain
select *
from t1
left join t1 as t2 on t2.a = t1.c and t2.b = t1.d
where t1.a = 2 and t1.b = 3;
select *
from t1
left join t1 as t2 on t2.a = t1.c and t2.b = t1.d
where t1.a = 2 and t1.b = 3;
--replace_result p0,p1,p2,p3,p4,p5,p6,p7 pXYZ p0 pX p5 pX
explain
select *
from t1
left join t1 as t2 on t2.a = t1.c and t2.b = t1.d
where t1.a = 2 and t1.b = 3
order by t1.c;
select *
from t1
left join t1 as t2 on t2.a = t1.c and t2.b = t1.d
where t1.a = 2 and t1.b = 3
order by t1.c;
set ndb_join_pushdown=false;
--replace_result p0,p1,p2,p3,p4,p5,p6,p7 pXYZ p0 pX p5 pX
explain
select *
from t1
join t1 as t2 on t2.a = t1.c and t2.b = t1.d
where t1.a = 2 and t1.b = 3;
select *
from t1
join t1 as t2 on t2.a = t1.c and t2.b = t1.d
where t1.a = 2 and t1.b = 3;
--replace_result p0,p1,p2,p3,p4,p5,p6,p7 pXYZ p0 pX p5 pX
explain
select *
from t1
left join t1 as t2 on t2.a = t1.c and t2.b = t1.d
where t1.a = 2 and t1.b = 3;
select *
from t1
left join t1 as t2 on t2.a = t1.c and t2.b = t1.d
where t1.a = 2 and t1.b = 3;
set ndb_join_pushdown=true;
explain
select *
from t1
join t1 as t2 on t2.a = t1.c and t2.b = t1.d
where t1.a = 1 and t1.b = 1;
select *
from t1
join t1 as t2 on t2.a = t1.c and t2.b = t1.d
where t1.a = 1 and t1.b = 1;
explain
select *
from t1
left join t1 as t2 on t2.a = t1.c and t2.b = t1.d
where t1.a = 1 and t1.b = 1;
select *
from t1
left join t1 as t2 on t2.a = t1.c and t2.b = t1.d
where t1.a = 1 and t1.b = 1;
set ndb_join_pushdown=false;
explain
select *
from t1
join t1 as t2 on t2.a = t1.c and t2.b = t1.d
where t1.a = 1 and t1.b = 1;
select *
from t1
join t1 as t2 on t2.a = t1.c and t2.b = t1.d
where t1.a = 1 and t1.b = 1;
explain
select *
from t1
left join t1 as t2 on t2.a = t1.c and t2.b = t1.d
where t1.a = 1 and t1.b = 1;
select *
from t1
left join t1 as t2 on t2.a = t1.c and t2.b = t1.d
where t1.a = 1 and t1.b = 1;
## Join as 't1 ALL' -> 't2 RANGE' -> 't3 EQ_REF'
## Possibly joinable starting with 't2 - RANGE' as root.
## However t3's join condition 't3.a = t1.c' refers t1 which is
## outside the scope of current queryplan. The equality set
## should be consulted in order to replace 't1.c' with 't2.a'
## inside the scope
set ndb_join_pushdown=true;
explain
select *
from t1
join t1 as t2 on t2.a = t1.c
join t1 as t3 on t3.a = t1.c and t3.b = t2.b;
--sorted_result
select *
from t1
join t1 as t2 on t2.a = t1.c
join t1 as t3 on t3.a = t1.c and t3.b = t2.b;
## Join as 'x ALL' -> 'y ALL' -> 'z EQ_REF'
## As Scan vs. scan is not pushable, only y,z is pushed
## However join cond on z refer x which is a
## (pseudo constant) paramValue wrt. the pushed join.
## As we have a dependency on previous rows these
## should not be join cached (ref. HA_PUSH_BLOCK_JOINCACHE)
explain
select straight_join *
from (t1 as x cross join t1 as y)
join t1 as z on z.a=x.a and z.b=y.b;
--sorted_result
select straight_join *
from (t1 as x cross join t1 as y)
join t1 as z on z.a=x.a and z.b=y.b;
## Some variants of the above where t3 has a join conditions in t1
## where t1 is outside scope of pushed join (as above). However, in
## these tests t3 is also linked with t2 through another join condition.
## This makes t3 join pushable by specifying the value of t1.c as a
## paramValue()
explain
select *
from t1
straight_join t1 as t2 on t2.a = t1.b+0 and t2.b = t1.c
straight_join t1 as t3 on t3.a = t1.b and t3.b = t2.b;
--sorted_result
select *
from t1
straight_join t1 as t2 on t2.a = t1.b+0 and t2.b = t1.c
straight_join t1 as t3 on t3.a = t1.b and t3.b = t2.b;
--replace_column 10 # 11 #
explain
select *
from t1
straight_join t1 as t2 on t2.a = t1.b+0 and t2.b = t1.c
straight_join t1 as t3 on t3.a = t1.b and t3.b = t2.b
where t1.a=1 and t1.d=1;
--sorted_result
select *
from t1
straight_join t1 as t2 on t2.a = t1.b+0 and t2.b = t1.c
straight_join t1 as t3 on t3.a = t1.b and t3.b = t2.b
where t1.a=1 and t1.d=1;
explain
select *
from t1
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;
--sorted_result
select *
from t1
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;
## Create a non-ndb table used as a tool to force part of
## a query to be non-pushable.
connection ddl;
create table t1_myisam (
a int not null,
b int not null,
c int not null,
d int not null,
primary key (`a`,`b`)
) engine=myisam;
connection spj;
insert into t1_myisam values
(1,1,1,1), (2,2,1,1), (3,3,1,1), (4,4,1,1);
## Optimizer will use the equality set to replace 't2.a'
## in the term 't3.a = t2.a' with 't1.c' (as 't2.a = t1.c').
## Furthermore the MyIsam table t1 is const table optimized making
## 't1.c' a const_item. This constant value has not yet been materialized
## into the key_buffer when the definition for the linked query is
## created. However, it is always available through the
## Field defining the KEY_PART of this JT_EQ_REF.
##
set ndb_join_pushdown=true;
explain
select *
from t1_myisam as t1
join t1 as t2 on t2.a = t1.c and t2.b = t1.d
join t1 as t3 on t3.a = t2.a and t3.b = t2.b
where t1.a=2 and t1.b=2;
--sorted_result
select *
from t1_myisam as t1
join t1 as t2 on t2.a = t1.c and t2.b = t1.d
join t1 as t3 on t3.a = t2.a and t3.b = t2.b
where t1.a=2 and t1.b=2;
connection ddl;
drop table t1_myisam;
#
# Test scans with filter. These should be pushed as linked operations
# where the root operation is a scan *with* a (pushed) filter and a
# primary key lookup child operation.
#
connection spj;
set ndb_join_pushdown=true;
# Table scan
--replace_column 10 # 11 #
explain select *
from t1
join t1 as t2 on t2.a = t1.c and t2.b = t1.d
where t1.d = 3;
--sorted_result
select *
from t1
join t1 as t2 on t2.a = t1.c and t2.b = t1.d
where t1.d = 3;
# Ordered index scan
--replace_column 10 # 11 #
explain select *
from t1
join t1 as t2 on t2.a = t1.c and t2.b = t1.d
where t1.a > 2 and t1.d = 3;
--sorted_result
select *
from t1
join t1 as t2 on t2.a = t1.c and t2.b = t1.d
where t1.a > 2 and t1.d = 3;
# Sorted scan of ordered index.
--replace_column 10 # 11 #
explain select *
from t1 join t1 as t2 on t2.a = t1.c and t2.b = t1.d
where t1.d = 3
order by t1.a;
--sorted_result
select *
from t1 join t1 as t2 on t2.a = t1.c and t2.b = t1.d
where t1.d = 3
order by t1.a;
#
# Test index scan w/ equal-bounds (low == high)
# NOTE: There used to be temp restriction of not allowing ordered
# index scans to be pushed. (Has later been lifted)
# SQL stmt. are therefore written with pushable JT_REFs from table 2 ->
#
set ndb_join_pushdown=true;
explain
select *
from t1
join t1 as t2 on t2.a = t1.c
join t1 as t3 on t3.a = t2.c and t3.b = t2.d
where t1.a = 1 and t1.b = 1;
--sorted_result
select *
from t1
join t1 as t2 on t2.a = t1.c
join t1 as t3 on t3.a = t2.c and t3.b = t2.d
where t1.a = 1 and t1.b = 1;
explain
select *
from t1
left join t1 as t2 on t2.a = t1.c
left join t1 as t3 on t3.a = t2.c and t3.b = t2.d
where t1.a = 1 and t1.b = 1;
--sorted_result
select *
from t1
left join t1 as t2 on t2.a = t1.c
left join t1 as t3 on t3.a = t2.c and t3.b = t2.d
where t1.a = 1 and t1.b = 1;
set ndb_join_pushdown=false;
explain
select *
from t1
join t1 as t2 on t2.a = t1.c
join t1 as t3 on t3.a = t2.c and t3.b = t2.d
where t1.a = 1 and t1.b = 1;
--sorted_result
select *
from t1
join t1 as t2 on t2.a = t1.c
join t1 as t3 on t3.a = t2.c and t3.b = t2.d
where t1.a = 1 and t1.b = 1;
explain
select *
from t1
left join t1 as t2 on t2.a = t1.c
left join t1 as t3 on t3.a = t2.c and t3.b = t2.d
where t1.a = 1 and t1.b = 1;
--sorted_result
select *
from t1
left join t1 as t2 on t2.a = t1.c
left join t1 as t3 on t3.a = t2.c and t3.b = t2.d
where t1.a = 1 and t1.b = 1;
# JT_REF as root operations is now supported as pushed joins
set ndb_join_pushdown=true;
explain
select *
from t1 as t2
join t1 as t3 on t3.a = t2.c and t3.b = t2.d
where t2.a = 1;
--sorted_result
select *
from t1 as t2
join t1 as t3 on t3.a = t2.c and t3.b = t2.d
where t2.a = 1;
# Test multiparent pushed joins where it is not possible
# to find a single common parent by using the equality set
#
# NOTE: We should take care to join the multiparent linked
# table on field refs. not also being refered from other join expr.
# as this will make them candidates for equality set replacement.
#
set ndb_join_pushdown=true;
# t3 refer both t1,t2 as parrent.
# t1 should be identifed as a grandparent available
# through its child t2.
explain
select straight_join *
from t1
join t1 as t2 on t2.a = t1.a and t2.b = t1.b
join t1 as t3 on t3.a = t2.c and t3.b = t1.c;
--sorted_result
select straight_join *
from t1
join t1 as t2 on t2.a = t1.a and t2.b = t1.b
join t1 as t3 on t3.a = t2.c and t3.b = t1.c;
# t4 is pushable iff we force an artificial parental dependency between t2 & t3.
explain
select straight_join *
from t1
join t1 as t2 on t2.a = t1.a and t2.b = t1.b
join t1 as t3 on t3.a = t1.c and t3.b = t1.d
join t1 as t4 on t4.a = t3.c and t4.b = t2.c;
--sorted_result
select straight_join *
from t1
join t1 as t2 on t2.a = t1.a and t2.b = t1.b
join t1 as t3 on t3.a = t1.c and t3.b = t1.d
join t1 as t4 on t4.a = t3.c and t4.b = t2.c;
# t3 is a child of t2 and grandchild of t1
# t4 is a child of t3 and grandchild of t2
explain
select straight_join *
from t1
join t1 as t2 on t2.a = t1.a and t2.b = t1.b
join t1 as t3 on t3.a = t1.c and t3.b = t2.d
join t1 as t4 on t4.a = t3.c and t4.b = t2.c;
--sorted_result
select straight_join *
from t1
join t1 as t2 on t2.a = t1.a and t2.b = t1.b
join t1 as t3 on t3.a = t1.c and t3.b = t2.d
join t1 as t4 on t4.a = t3.c and t4.b = t2.c;
# t3 is a child of t2 and grandchild of t1
# t4 is a child of t3 and grandgrandchild of t1
explain
select straight_join *
from t1
join t1 as t2 on t2.a = t1.a and t2.b = t1.b
join t1 as t3 on t3.a = t1.c and t3.b = t2.d
join t1 as t4 on t4.a = t3.c and t4.b = t1.d;
--sorted_result
select straight_join *
from t1
join t1 as t2 on t2.a = t1.a and t2.b = t1.b
join t1 as t3 on t3.a = t1.c and t3.b = t2.d
join t1 as t4 on t4.a = t3.c and t4.b = t1.d;
# Some testcases where t4 is not directly pushable, but
# may be made pushable by equality set replacement.
#
# BEWARE: mysqld optimizer may do its own replacement
# before ha_ndbcluster_push analyze the AQP. We therefore
# provide multiple similar testcases and hope that
# some of them will trigger the replacement code in
# ha_ndbcluster_push :-o
explain
select straight_join *
from t1
join t1 as t2 on t2.a = t1.a and t2.b = t1.b
join t1 as t3 on t3.a = t1.c and t3.b = t1.d
join t1 as t4 on t4.a = t3.a and t4.b = t2.c;
--sorted_result
select straight_join *
from t1
join t1 as t2 on t2.a = t1.a and t2.b = t1.b
join t1 as t3 on t3.a = t1.c and t3.b = t1.d
join t1 as t4 on t4.a = t3.a and t4.b = t2.c;
explain
select straight_join *
from t1
join t1 as t2 on t2.a = t1.a and t2.b = t1.b
join t1 as t3 on t3.a = t1.c and t3.b = t1.d
join t1 as t4 on t4.a = t3.b and t4.b = t2.c;
--sorted_result
select straight_join *
from t1
join t1 as t2 on t2.a = t1.a and t2.b = t1.b
join t1 as t3 on t3.a = t1.c and t3.b = t1.d
join t1 as t4 on t4.a = t3.b and t4.b = t2.c;
explain
select straight_join *
from t1
join t1 as t2 on t2.a = t1.a and t2.b = t1.b
join t1 as t3 on t3.a = t1.c and t3.b = t1.d
join t1 as t4 on t4.a = t3.c and t4.b = t2.a;
--sorted_result
select straight_join *
from t1
join t1 as t2 on t2.a = t1.a and t2.b = t1.b
join t1 as t3 on t3.a = t1.c and t3.b = t1.d
join t1 as t4 on t4.a = t3.c and t4.b = t2.a;
explain
select straight_join *
from t1
join t1 as t2 on t2.a = t1.a and t2.b = t1.b
join t1 as t3 on t3.a = t1.c and t3.b = t1.d
join t1 as t4 on t4.a = t3.c and t4.b = t2.b;
--sorted_result
select straight_join *
from t1
join t1 as t2 on t2.a = t1.a and t2.b = t1.b
join t1 as t3 on t3.a = t1.c and t3.b = t1.d
join t1 as t4 on t4.a = t3.c and t4.b = t2.b;
explain
select straight_join *
from t1
join t1 as t2 on t2.a = t1.a and t2.b = t1.b
join t1 as t3 on t3.a = t1.c and t3.b = t1.d
join t1 as t4 on t4.a = t1.c and t4.b = t2.c;
--sorted_result
select straight_join *
from t1
join t1 as t2 on t2.a = t1.a and t2.b = t1.b
join t1 as t3 on t3.a = t1.c and t3.b = t1.d
join t1 as t4 on t4.a = t1.c and t4.b = t2.c;
explain
select straight_join *
from t1
join t1 as t2 on t2.a = t1.a and t2.b = t1.b
join t1 as t3 on t3.a = t1.c and t3.b = t1.d
join t1 as t4 on t4.a = t3.c and t4.b = t1.b;
--sorted_result
select straight_join *
from t1
join t1 as t2 on t2.a = t1.a and t2.b = t1.b
join t1 as t3 on t3.a = t1.c and t3.b = t1.d
join t1 as t4 on t4.a = t3.c and t4.b = t1.b;
# Added more multidependency tests;
#
explain
select straight_join *
from t1
join t1 as t2 on t2.a = t1.a and t2.b = t1.b
join t1 as t2x on t2x.a = t2.c and t2x.b = t2.d
join t1 as t3x on t3x.a = t1.c and t3x.b = t1.d
join t1 as t4 on t4.a = t3x.c and t4.b = t2x.c;
explain
select straight_join *
from t1
join t1 as t2 on t2.a = t1.a and t2.b = t1.b
join t1 as t2x on t2x.a = t2.c and t2x.b = t2.d
join t1 as t3 on t3.a = t1.c and t3.b = t1.d
join t1 as t3x on t3x.a = t3.c and t3x.b = t3.d
join t1 as t4 on t4.a = t3x.c and t4.b = t2x.c;
explain
select straight_join *
from t1
join t1 as t2 on t2.a = t1.a and t2.b = t1.b
join t1 as t3 on t3.a = t1.c and t3.b = t1.d
join t1 as t2x on t2x.a = t2.c and t2x.b = t2.d
join t1 as t3x on t3x.a = t3.c and t3x.b = t3.d
join t1 as t4 on t4.a = t3x.c and t4.b = t2x.c;
# 't3' is not referred as ancestor and should not be
# included in the forced dependencies
# (Depends directly on 't1' and can be bushy wrt. to
# the other tables)
explain
select straight_join *
from t1
join t1 as t2 on t2.a = t1.a and t2.b = t1.b
join t1 as t2x on t2x.a = t2.c and t2x.b = t2.d
join t1 as t3 on t3.a = t1.c and t3.b = t1.d
join t1 as t3x on t3x.a = t1.c and t3x.b = t1.d
join t1 as t4 on t4.a = t3x.c and t4.b = t2x.c;
# 't3' is still independent - see comment above.
explain
select straight_join *
from t1
join t1 as t2 on t2.a = t1.a and t2.b = t1.b
join t1 as t2x on t2x.a = t2.c and t2x.b = t2.d
join t1 as t3 on t3.a = t1.c and t3.b = t1.b
join t1 as t3x on t3x.a = t1.c and t3x.b = t1.d
join t1 as t4 on t4.a = t3x.c and t4.b = t2x.c;
## It should not be possible to force grandparent dependencies
## via a previously outer joined table:
explain
select straight_join *
from t1
left join t1 as t2 on t2.a = t1.a and t2.b = t1.b
join t1 as t3 on t3.a = t1.c and t3.b = t1.d
left join t1 as t4 on t4.a = t3.c and t4.b = t2.c;
explain
select straight_join *
from t1
join t1 as t2 on t2.a = t1.a and t2.b = t1.b
left join t1 as t3 on t3.a = t1.c and t3.b = t1.d
left join t1 as t4 on t4.a = t3.c and t4.b = t2.c;
explain
select straight_join *
from t1
left join t1 as t2 on t2.a = t1.a and t2.b = t1.b
join t1 as t3 on t3.a = t1.a
left join t1 as t4 on t4.a = t3.c and t4.b = t2.c;
explain
select straight_join *
from t1
join t1 as t2 on t2.a = t1.a and t2.b = t1.b
left join t1 as t3 on t3.a = t1.a
left join t1 as t4 on t4.a = t3.c and t4.b = t2.c;
explain
select straight_join *
from t1
left join t1 as t2 on t2.a = t1.a and t2.b = t1.b
join t1 as t3 on t3.a = t1.a
left join t1 as t4 on t4.a = t3.c;
explain
select straight_join *
from t1
join t1 as t2 on t2.a = t1.a and t2.b = t1.b
left join t1 as t3 on t3.a = t1.a
left join t1 as t4 on t4.a = t3.c;
# Bug#16199028:
# Tescase where a too strict 'root' was checked:
# In these cases t4 was not pushed as it was incorrectly
# concluded that its grandparent ref of 't2' through 't3'
# introduced dependencies on the outer joined t1.
#
explain
select straight_join *
from
( t1 as t0 left join t1 as t1 on t1.a = t0.a and t1.b = t0.b
)
left join
( t1 as t2 join t1 as t3 on t3.a = t2.c and t3.b = t2.d
join t1 as t4 on t4.a = t3.c and t4.b = t2.c
)
on t2.a = t1.a and t2.b = t1.b;
explain
select straight_join *
from
t1 as x1
left join
( t1 as x2 join t1 as x3 on x3.a=x2.c
join t1 as x4 on x4.a=x2.d
join t1 as x5 on x5.a=x3.d and x5.b=x4.d
)
on x2.a=x1.c and x2.b=x1.c and
x3.b=x1.d and
x4.b=x1.d;
# Bug#16198866:
# Additional tests where equality set [t1.d, t3.b, t4.d]
# propagation incorrectly allowed grandparent references
# to be allowed 'through' outer joins (t2):
#
# Test: don't allow t4 to propagate parent deps past
# outer joined 't2' into t3.
# (As that would implicit cause t3 to depend on t2.)
#
explain
select straight_join * from
(t1 left join t1 as t2 on t2.a = t1.c and t2.b = t1.d)
inner join t1 as t3 on t3.a = t1.b and t3.b = t1.c
left join t1 as t4 on t4.a = t2.c and t4.b = t1.c;
--sorted_result
select straight_join * from
(t1 left join t1 as t2 on t2.a = t1.c and t2.b = t1.d)
inner join t1 as t3 on t3.a = t1.b and t3.b = t1.c
left join t1 as t4 on t4.a = t2.c and t4.b = t1.c;
# However, if t3 already depends on t2 (t3.a = t2.b)
# it *is* allowed as it will not introduce any new
# t3 dependencies.
#
# Note: Both these two queries are fully pushable, but
# the later will likely result in a better query plan.
#
explain
select straight_join * from
(t1 left join t1 as t2 on t2.a = t1.c and t2.b = t1.d)
inner join t1 as t3 on t3.a = t2.b and t3.b = t1.c
left join t1 as t4 on t4.a = t2.c and t4.b = t1.c;
--sorted_result
select straight_join * from
(t1 left join t1 as t2 on t2.a = t1.c and t2.b = t1.d)
inner join t1 as t3 on t3.a = t2.b and t3.b = t1.c
left join t1 as t4 on t4.a = t2.c and t4.b = t1.c;
# Test a combination of pushed table scan (x, y)
# & pushed EQ-bound (indexScan) (z, t1)
# This used to give incorrect results with random result for last table (t1)
set ndb_join_pushdown=true;
explain
select * from t1 x, t1 y, t1 z, t1 where
y.a=x.d and y.b=x.b and
z.a=y.d and
t1.a = z.d and t1.b=z.b;
--sorted_result
select * from t1 x, t1 y, t1 z, t1 where
y.a=x.d and y.b=x.b and
z.a=y.d and
t1.a = z.d and t1.b=z.b;
# Pushed scanIndex() with (multi-)range:
explain
select * from t1 x, t1 y where
x.a <= 2 and
y.a=x.d and y.b=x.b;
--sorted_result
select * from t1 x, t1 y where
x.a <= 2 and
y.a=x.d and y.b=x.b;
explain
select * from t1 x, t1 y where
(x.a <= 2 or x.a > 3) and
y.a=x.d and y.b=x.b;
--sorted_result
select * from t1 x, t1 y where
(x.a <= 2 or x.a > 3) and
y.a=x.d and y.b=x.b;
# 'open' range:
--replace_column 10 # 11 #
explain
select * from t1 x, t1 y where
(x.a >= 2 or x.a < 3) and
y.a=x.d and y.b=x.b;
--sorted_result
select * from t1 x, t1 y where
(x.a >= 2 or x.a < 3) and
y.a=x.d and y.b=x.b;
# Combination of range and 'in' list
explain
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;
--sorted_result
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;
# Combination of range and 'in' list with exact match
# NOTE: Due to simplification in pushed mrr, exact matches are also
# executed as range scans
explain
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;
--sorted_result
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;
# Test ORDER BY expressions
# If it is a 'simple' order, i.e all order by's are plain column refs
# to the first non-const table, the optimizer will (by heuristic)
# make the first table 'ordered' beforing joining in the siblings.
# This may involve presorting of the first table into intermediate storage.
#
# This will make this (parent-) table non-pushable as
# read of rows to be filesorted will also prefetch rows from pushed child
# operands. These are not cached by the filesort buffer mechanisnm and are
# effectively lost.
#
# Non simple ordering will always writte entire resultset to temp.
# table and filesort that -> No extra push restrictions on these.
#
# Integrating pushed joins we either has to:
# 1) find a suitable ordered index which we can create an ordered indexscan on
# (-> joinType() -> JT_NEXT, or type: 'index' w/ explain)
# or:
# 2) Reject pushing of the this parent table.
#
# Comment1: As 'order by' correctness is part of what we want to test,
# '--sorted_result' is *not* specified. Instead we aim at
# specifying a deterministic sort ordering in order by list.
#
# Comment2: 't1.a, t1.b' is appended where required to the order by spec
# to get a deterministic sorting order wo/ '--sorted_result'
#
## Non-pushed join w/ 'simple order' on non_PK - Presorts parent table.
explain
select *
from t1
join t1 as t2 on t2.a = t1.b and t2.b = t1.c
join t1 as t3 on t3.a = t2.a and t3.b = t2.b
order by t1.c,t1.d,
t1.a, t1.b;
#--sorted_result
select *
from t1
join t1 as t2 on t2.a = t1.b and t2.b = t1.c
join t1 as t3 on t3.a = t2.a and t3.b = t2.b
order by t1.c,t1.d,
t1.a, t1.b;
## pushed join w/ non-'simple order' on non_PK - Need temp table + filesort
explain
select *
from t1
join t1 as t2 on t2.a = t1.b and t2.b = t1.c
join t1 as t3 on t3.a = t2.a and t3.b = t2.b
order by t1.c,t2.d,
t1.a, t1.b;
#--sorted_result
select *
from t1
join t1 as t2 on t2.a = t1.b and t2.b = t1.c
join t1 as t3 on t3.a = t2.a and t3.b = t2.b
order by t1.c,t2.d,
t1.a, t1.b;
## pushed join w/ 'simple order' on PK - Should use ordered index scan
explain
select *
from t1
join t1 as t2 on t2.a = t1.b and t2.b = t1.c
join t1 as t3 on t3.a = t2.a and t3.b = t2.b
order by t1.a,t1.b;
#--sorted_result
select *
from t1
join t1 as t2 on t2.a = t1.b and t2.b = t1.c
join t1 as t3 on t3.a = t2.a and t3.b = t2.b
order by t1.a,t1.b;
## pushed join w/ non-'simple order' on PK - will need temp table + filesort
explain
select *
from t1
join t1 as t2 on t2.a = t1.b and t2.b = t1.c
join t1 as t3 on t3.a = t2.a and t3.b = t2.b
order by t1.a,t2.b,
t1.a, t1.b;
#--sorted_result
select *
from t1
join t1 as t2 on t2.a = t1.b and t2.b = t1.c
join t1 as t3 on t3.a = t2.a and t3.b = t2.b
order by t1.a,t2.b,
t1.a, t1.b;
## Descending ordering on PK - use reversed ordered index scan
explain
select *
from t1
join t1 as t2 on t2.a = t1.b and t2.b = t1.c
join t1 as t3 on t3.a = t2.a and t3.b = t2.b
order by t1.a desc,t1.b desc;
#--sorted_result
select *
from t1
join t1 as t2 on t2.a = t1.b and t2.b = t1.c
join t1 as t3 on t3.a = t2.a and t3.b = t2.b
order by t1.a desc,t1.b desc;
## Simple-PK column in incorrect order, -> Presort parent table, (no-push)
explain
select *
from t1
join t1 as t2 on t2.a = t1.b and t2.b = t1.c
join t1 as t3 on t3.a = t2.a and t3.b = t2.b
order by t1.b,t1.a;
#--sorted_result
select *
from t1
join t1 as t2 on t2.a = t1.b and t2.b = t1.c
join t1 as t3 on t3.a = t2.a and t3.b = t2.b
order by t1.b,t1.a;
## Explore other permutations of PK columns in ORDER BY clause
## Don't care about the results here....
# Subset of first part of PK -> ordered index scan
explain
select *
from t1
join t1 as t2 on t2.a = t1.b and t2.b = t1.c
join t1 as t3 on t3.a = t2.a and t3.b = t2.b
order by t1.a;
# PK columns not including first part -> Presort parent table, (no-push)
explain
select *
from t1
join t1 as t2 on t2.a = t1.b and t2.b = t1.c
join t1 as t3 on t3.a = t2.a and t3.b = t2.b
order by t1.b;
## Similar tests for GROUP BY expression
## PK grouping (or subpart) may be optimized
## by ordered index access.
##
explain
select t1.a, t1.b, count(*)
from t1
join t1 as t2 on t2.a = t1.b and t2.b = t1.c
join t1 as t3 on t3.a = t2.a and t3.b = t2.b
group by t1.a, t1.b;
--sorted_result
select t1.a, t1.b, count(*)
from t1
join t1 as t2 on t2.a = t1.b and t2.b = t1.c
join t1 as t3 on t3.a = t2.a and t3.b = t2.b
group by t1.a, t1.b;
explain
select t1.a, count(*)
from t1
join t1 as t2 on t2.a = t1.b and t2.b = t1.c
join t1 as t3 on t3.a = t2.a and t3.b = t2.b
group by t1.a;
--sorted_result
select t1.a, count(*)
from t1
join t1 as t2 on t2.a = t1.b and t2.b = t1.c
join t1 as t3 on t3.a = t2.a and t3.b = t2.b
group by t1.a;
explain
select t1.b, count(*)
from t1
join t1 as t2 on t2.a = t1.b and t2.b = t1.c
join t1 as t3 on t3.a = t2.a and t3.b = t2.b
group by t1.b;
--sorted_result
select t1.b, count(*)
from t1
join t1 as t2 on t2.a = t1.b and t2.b = t1.c
join t1 as t3 on t3.a = t2.a and t3.b = t2.b
group by t1.b;
--replace_result p0,p1,p2,p3,p4,p5,p6,p7 pXYZ p1 pX p2 pX
explain
select t2.c, count(distinct t2.a)
from t1
join t1 as t2 on t1.a = t2.c and t1.b = t2.d
where t2.a = 4 and t2.b=4
group by t2.c;
--sorted_result
select t2.c, count(distinct t2.a)
from t1
join t1 as t2 on t1.a = t2.c and t1.b = t2.d
where t2.a = 4 and t2.b=4
group by t2.c;
explain
select t2.c, count(distinct t2.a)
from t1
join t1 as t2 on t1.a = t2.c and t1.b = t2.d
where t2.a = 4
group by t2.c;
--sorted_result
select t2.c, count(distinct t2.a)
from t1
join t1 as t2 on t1.a = t2.c and t1.b = t2.d
where t2.a = 4
group by t2.c;
--replace_result p0,p1,p2,p3,p4,p5,p6,p7 pXYZ p1 pX p2 pX
explain
select t2.c, count(distinct t2.a)
from t1
join t1 as t2 on t1.a = t2.c and t1.b = t2.d
where t2.a = 4 and t2.b=4
group by t2.c order by t2.c;
--sorted_result
select t2.c, count(distinct t2.a)
from t1
join t1 as t2 on t1.a = t2.c and t1.b = t2.d
where t2.a = 4 and t2.b=4
group by t2.c order by t2.c;
connection ddl;
create table tx like t1;
connection spj;
insert into tx
select x1.a+x2.a*16, x1.b+x2.b*16, x1.c+x2.c*16, x1.d+x2.d*16
from t1 as x1 cross join t1 as x2;
# Test of outer join with scan child.
# This query should not be pushed. Doing so would produce lots of extra
# [<x1 row>.NULL] rows, since the x1.d=x2.d predicate cannot be pushed.
explain select count(*) from tx as x1
left join tx as x2 on x1.c=x2.a and x1.d=x2.d;
select count(*) from tx as x1
left join tx as x2 on x1.c=x2.a and x1.d=x2.d;
connection ddl;
drop table tx;
# Test bushy join with pruned scan.
connection ddl;
alter table t1 partition by key(a);
connection spj;
explain select count(*) from t1
join t1 as t2 on t2.a = t1.c
join t1 as t3 on t3.a = t1.d;
select count(*) from t1
join t1 as t2 on t2.a = t1.c
join t1 as t3 on t3.a = t1.d;
# Test bushy join with pruned scan and larger result set.
connection ddl;
CREATE TABLE tx (
a int NOT NULL,
PRIMARY KEY (`a`)
) comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
delete from t1;
insert into tx values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
insert into t1 select 1, x1.a * 10+x2.a, 1, 1 from tx as x1 cross join tx as x2;
--error 0,1193
set global debug='+d,max_64rows_in_spj_batches';
--replace_column 10 # 11 #
explain select count(*) from t1 as x1
join t1 as x2 on x2.a = x1.c and x1.b < 2
join t1 as x3 on x3.a = x1.d;
select count(*) from t1 as x1
join t1 as x2 on x2.a = x1.c and x1.b < 2
join t1 as x3 on x3.a = x1.d;
--error 0,1193
set global debug=@save_debug;
connection ddl;
drop table t1;
drop table tx;
# Test user defined partition not being pushed
#
# Note: User defined partitions are handled
# by the SQL layer, and as such are unknown
# to the NDB datanodes.
#
connection spj;
create table t1 (
a int not null,
b int not null,
c int not null,
d int not null,
primary key (`a`,`b`)
) engine=ndbcluster
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM"
partition by key(a);
connection spj;
insert into t1 values
(1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4),
(1,2,5,1), (1,3,1,2), (1,4,2,3),
(2,1,3,4), (2,3,4,5), (2,4,5,1),
(3,1,1,2), (3,2,2,3), (3,4,3,4),
(4,1,4,5), (4,2,5,1), (4,3,1,2);
# Only this query('partition by key') should be pushed
explain
select *
from t1
join t1 as t2 on t2.a = t1.b and t2.b = t1.c;
connection ddl;
set new=on;
alter table t1 partition by hash(a);
explain
select *
from t1
join t1 as t2 on t2.a = t1.b and t2.b = t1.c;
connection ddl;
alter table t1 partition by list(a) (
partition p1 values in (1),
partition p2 values in (2),
partition p3 values in (3),
partition p4 values in (4)
);
explain
select *
from t1
join t1 as t2 on t2.a = t1.b and t2.b = t1.c;
connection ddl;
alter table t1 partition by range(a) partitions 4 (
partition p1 values less than (0),
partition p2 values less than (2),
partition p3 values less than (4),
partition p4 values less than (99999)
);
explain
select *
from t1
join t1 as t2 on t2.a = t1.b and t2.b = t1.c;
connection ddl;
drop table t1;
set new=default;
# pushed mrr does not yet handle multiple PK operations in same transaction
# Need 6.0 result handling stuff to simplify result handling
# *** join push is currently dissabled for these ****
#
connection ddl;
create table t1 (a int, b int, primary key(a) using hash) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t1 values (1, 2);
insert into t1 values (2, 3);
insert into t1 values (3, 1);
set ndb_join_pushdown=true;
##
# In ps-protocol, server will adds calls to execute(Commit)
# (these are optimized away by ndbapi, since there is nothing to commit)
# and these generates a diff in execute-count
# To not have to investigate problem futher, I simply set autocommit=off
# (and back further down where we don't track execute-count any longer)
# It would probably be good to changes these tests to instead use frazers new
# ndbapi counters, and instead measure #round-trips
set autocommit=off;
explain
select *
from t1, t1 as t2
where t1.a in (1,3,5)
and t2.a = t1.b;
--source suite/ndb/include/ndb_init_execute_count.inc
--sorted_result
select *
from t1, t1 as t2
where t1.a in (1,3,5)
and t2.a = t1.b;
--source suite/ndb/include/ndb_execute_count.inc
--echo This should yield 3 executes (for now...buh)
set autocommit=on;
connection ddl;
drop table t1;
# Same case when there is an ordered index on PK
connection ddl;
create table t1 (a int, b int, primary key(a)) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t1 values (1, 2);
insert into t1 values (2, 3);
insert into t1 values (3, 1);
set ndb_join_pushdown=true;
##
# In ps-protocol, server will adds calls to execute(Commit)
# (these are optimized away by ndbapi, since there is nothing to commit)
# and these generates a diff in execute-count
# To not have to investigate problem futher, I simply set autocommit=off
# (and back further down where we don't track execute-count any longer)
# It would probably be good to changes these tests to instead use frazers new
# ndbapi counters, and instead measure #round-trips
set autocommit=off;
explain
select *
from t1, t1 as t2
where t1.a in (1,3,5)
and t2.a = t1.b;
--source suite/ndb/include/ndb_init_execute_count.inc
--sorted_result
select *
from t1, t1 as t2
where t1.a in (1,3,5)
and t2.a = t1.b;
--source suite/ndb/include/ndb_execute_count.inc
--echo This should yield 1 execute (but inefficient since it's based on scan)
set autocommit=on;
## Adding and 'order by ... desc' trigger the usage
## of QUICK_SELECT_DESC which somehow prepares a
## pushed join as indexscan but ends up executing it as
## primary key access. This (auto-) disables the pushed
## join execution (EXPLAIN still says 'pushdown') which
## should test handling of this in ha_ndbcluster::index_read_pushed()
explain
select *
from t1, t1 as t2
where t1.a in (1,3,5)
and t2.a = t1.b
order by t1.a desc;
select *
from t1, t1 as t2
where t1.a in (1,3,5)
and t2.a = t1.b
order by t1.a desc;
connection ddl;
drop table t1;
set ndb_join_pushdown=true;
connection ddl;
create table t1 (a int, b int, primary key(a)) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
create table t2 (c int, d int, primary key(c)) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
create table t3 (a3 int, b3 int, c3 int not null, d3 int not null,
primary key(a3, b3)) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
create table t3_hash (a3 int, b3 int, c3 int not null, d3 int not null,
primary key(a3, b3) using hash) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t1 values (0x1f, 0x2f);
insert into t1 values (0x2f, 0x3f);
insert into t1 values (0x3f, 0x1f);
insert into t2 values (0x1f, 0x2f);
insert into t2 values (0x2f, 0x3f);
insert into t2 values (0x3f, 0x1f);
insert into t3 values (0x1f, 0x2f, 1, 0x1f);
insert into t3 values (0x2f, 0x3f, 2, 0x2f);
insert into t3 values (0x3f, 0x1f, 3, 0x3f);
insert into t3_hash values (0x1f, 0x2f, 1, 0x1f);
insert into t3_hash values (0x2f, 0x3f, 2, 0x2f);
insert into t3_hash values (0x3f, 0x1f, 3, 0x3f);
explain
select * from t3 x, t3 y, t1 where y.a3=x.d3 and y.b3=x.b3 and t1.a = y.d3;
--sorted_result
select * from t3 x, t3 y, t1 where y.a3=x.d3 and y.b3=x.b3 and t1.a = y.d3;
explain
select *
from t3 x, t3 y, t3 z, t3 z2, t1
where y.a3=x.d3 and y.b3=x.b3 and
z.a3=y.d3 and z.b3=y.b3 and
z2.a3=z.d3 and z2.b3=z.b3 and
t1.a = z2.d3;
--sorted_result
select *
from t3 x, t3 y, t3 z, t3 z2, t1
where y.a3=x.d3 and y.b3=x.b3 and
z.a3=y.d3 and z.b3=y.b3 and
z2.a3=z.d3 and z2.b3=z.b3 and
t1.a = z2.d3;
# Table expressions wo/ parent-child linkage should *not* be executes as a pushed join:
--replace_column 10 # 11 #
explain
select straight_join * from t1 x, t1 y where y.a=0x1f and x.b = 0x1f;
select straight_join * from t1 x, t1 y where y.a=0x1f and x.b = 0x1f;
# NOTE: Due to constValue replacement in equality sets, query below are
# effectively the same as the one above. -> Don't push either
--replace_column 10 # 11 #
explain
select straight_join * from t1 x, t1 y where y.a=x.b and x.b = 0x1f;
select straight_join * from t1 x, t1 y where y.a=x.b and x.b = 0x1f;
# Tests usage of unique index
connection ddl;
create unique index t3_d3 on t3(d3);
create unique index t3_d3 on t3_hash(d3);
commit;
connection spj;
# Use an unique key to lookup root in pushed join:
explain
select * from t3 x, t3 y where x.d3=31 and y.a3=x.d3 and y.b3=x.b3;
select * from t3 x, t3 y where x.d3=31 and y.a3=x.d3 and y.b3=x.b3;
# No data-found on unique key lookup root
explain
select * from t3 x, t3 y where x.d3=0 and y.a3=x.d3 and y.b3=x.b3;
select * from t3 x, t3 y where x.d3=0 and y.a3=x.d3 and y.b3=x.b3;
# Use an unique key to lookup joined child tables
explain
select * from t1 x, t3 y where y.d3=x.b;
--sorted_result
select * from t1 x, t3 y where y.d3=x.b;
# Unique index used both for root lookup and child linkage.
explain
select * from t3 x, t3 y where x.d3=31 and y.d3=x.b3;
select * from t3 x, t3 y where x.d3=31 and y.d3=x.b3;
# No data-found on unique key lookup child
explain
select * from t3 x, t3 y where x.d3=31 and y.d3=x.c3;
select * from t3 x, t3 y where x.d3=31 and y.d3=x.c3;
# 'index_merge' between PRIMARY and index t3.d3
# NOTE: currently unhandled
explain
select * from t3 x, t3 y
where ((x.a3=0x2f and x.b3=0x3f) or x.d3=0x1f)
and (y.a3=x.d3 and y.b3=x.b3);
# No 'sorted_result' required as index merge itself sort on PK
select * from t3 x, t3 y
where ((x.a3=0x2f and x.b3=0x3f) or x.d3=0x1f)
and (y.a3=x.d3 and y.b3=x.b3);
explain
select * from t3_hash x, t3_hash y
where ((x.a3=0x2f and x.b3=0x3f) or x.d3=0x1f)
and (y.a3=x.d3 and y.b3=x.b3);
# No 'sorted_result' required as index merge itself sort on PK
select * from t3_hash x, t3_hash y
where ((x.a3=0x2f and x.b3=0x3f) or x.d3=0x1f)
and (y.a3=x.d3 and y.b3=x.b3);
# Any ordered index may also be used to scan a 'range'
explain
select * from t3 x, t3 y where x.d3>=31 and y.d3=x.b3;
--sorted_result
select * from t3 x, t3 y where x.d3>=31 and y.d3=x.b3;
# handle "null" key
insert into t1 values (0x4f, null);
--sorted_result
select * from t1 left join t1 as t2 on t2.a = t1.b;
### Test max number of pushable operations.
insert into t3 values (8,8,8,8);
# Unique lookups only.
explain select count(*) from t3 as x0
join t3 as x1 on x0.b3=x1.d3 and x0.d3=8
join t3 as x2 on x1.b3=x2.d3
join t3 as x3 on x2.b3=x3.d3
join t3 as x4 on x3.b3=x4.d3
join t3 as x5 on x4.b3=x5.d3
join t3 as x6 on x5.b3=x6.d3
join t3 as x7 on x6.b3=x7.d3
join t3 as x8 on x7.b3=x8.d3
join t3 as x9 on x8.b3=x9.d3
join t3 as x10 on x9.b3=x10.d3
join t3 as x11 on x10.b3=x11.d3
join t3 as x12 on x11.b3=x12.d3
join t3 as x13 on x12.b3=x13.d3
join t3 as x14 on x13.b3=x14.d3
join t3 as x15 on x14.b3=x15.d3
join t3 as x16 on x15.b3=x16.d3
join t3 as x17 on x16.b3=x17.d3;
select count(*) from t3 as x0
join t3 as x1 on x0.b3=x1.d3 and x0.d3=8
join t3 as x2 on x1.b3=x2.d3
join t3 as x3 on x2.b3=x3.d3
join t3 as x4 on x3.b3=x4.d3
join t3 as x5 on x4.b3=x5.d3
join t3 as x6 on x5.b3=x6.d3
join t3 as x7 on x6.b3=x7.d3
join t3 as x8 on x7.b3=x8.d3
join t3 as x9 on x8.b3=x9.d3
join t3 as x10 on x9.b3=x10.d3
join t3 as x11 on x10.b3=x11.d3
join t3 as x12 on x11.b3=x12.d3
join t3 as x13 on x12.b3=x13.d3
join t3 as x14 on x13.b3=x14.d3
join t3 as x15 on x14.b3=x15.d3
join t3 as x16 on x15.b3=x16.d3
join t3 as x17 on x16.b3=x17.d3;
# Max scans
explain select count(*) from t3 as x0
join t3 as x1 on x0.c3=x1.a3
join t3 as x2 on x1.c3=x2.a3
join t3 as x3 on x2.c3=x3.a3
join t3 as x4 on x3.c3=x4.a3
join t3 as x5 on x4.c3=x5.a3
join t3 as x6 on x5.c3=x6.a3
join t3 as x7 on x6.c3=x7.a3
join t3 as x8 on x7.c3=x8.a3
join t3 as x9 on x8.c3=x9.a3
join t3 as x10 on x9.c3=x10.a3
join t3 as x11 on x10.c3=x11.a3
join t3 as x12 on x11.c3=x12.a3
join t3 as x13 on x12.c3=x13.a3
join t3 as x14 on x13.c3=x14.a3
join t3 as x15 on x14.c3=x15.a3
join t3 as x16 on x15.c3=x16.a3
join t3 as x17 on x16.c3=x17.a3
join t3 as x18 on x17.c3=x18.a3
join t3 as x19 on x18.c3=x19.a3
join t3 as x20 on x19.c3=x20.a3
join t3 as x21 on x20.c3=x21.a3
join t3 as x22 on x21.c3=x22.a3
join t3 as x23 on x22.c3=x23.a3
join t3 as x24 on x23.c3=x24.a3
join t3 as x25 on x24.c3=x25.a3
join t3 as x26 on x25.c3=x26.a3
join t3 as x27 on x26.c3=x27.a3
join t3 as x28 on x27.c3=x28.a3
join t3 as x29 on x28.c3=x29.a3
join t3 as x30 on x29.c3=x30.a3
join t3 as x31 on x30.c3=x31.a3
join t3 as x32 on x31.c3=x32.a3
join t3 as x33 on x32.c3=x33.a3;
select count(*) from t3 as x0
join t3 as x1 on x0.c3=x1.a3
join t3 as x2 on x1.c3=x2.a3
join t3 as x3 on x2.c3=x3.a3
join t3 as x4 on x3.c3=x4.a3
join t3 as x5 on x4.c3=x5.a3
join t3 as x6 on x5.c3=x6.a3
join t3 as x7 on x6.c3=x7.a3
join t3 as x8 on x7.c3=x8.a3
join t3 as x9 on x8.c3=x9.a3
join t3 as x10 on x9.c3=x10.a3
join t3 as x11 on x10.c3=x11.a3
join t3 as x12 on x11.c3=x12.a3
join t3 as x13 on x12.c3=x13.a3
join t3 as x14 on x13.c3=x14.a3
join t3 as x15 on x14.c3=x15.a3
join t3 as x16 on x15.c3=x16.a3
join t3 as x17 on x16.c3=x17.a3
join t3 as x18 on x17.c3=x18.a3
join t3 as x19 on x18.c3=x19.a3
join t3 as x20 on x19.c3=x20.a3
join t3 as x21 on x20.c3=x21.a3
join t3 as x22 on x21.c3=x22.a3
join t3 as x23 on x22.c3=x23.a3
join t3 as x24 on x23.c3=x24.a3
join t3 as x25 on x24.c3=x25.a3
join t3 as x26 on x25.c3=x26.a3
join t3 as x27 on x26.c3=x27.a3
join t3 as x28 on x27.c3=x28.a3
join t3 as x29 on x28.c3=x29.a3
join t3 as x30 on x29.c3=x30.a3
join t3 as x31 on x30.c3=x31.a3
join t3 as x32 on x31.c3=x32.a3
join t3 as x33 on x32.c3=x33.a3;
#Mixed join
explain select count(*) from t3 as x0
join t3 as x1 on x0.b3=x1.d3
join t3 as x2 on x1.b3=x2.d3
join t3 as x3 on x2.b3=x3.d3
join t3 as x4 on x3.b3=x4.d3
join t3 as x5 on x4.b3=x5.d3
join t3 as x6 on x5.b3=x6.d3
join t3 as x7 on x6.b3=x7.d3
join t3 as x8 on x7.b3=x8.d3
join t3 as x9 on x8.b3=x9.d3
join t3 as x10 on x9.b3=x10.d3
join t3 as x11 on x10.b3=x11.d3
join t3 as x12 on x11.b3=x12.d3
join t3 as x13 on x12.b3=x13.d3
join t3 as x14 on x13.b3=x14.d3
join t3 as x15 on x14.b3=x15.d3
join t3 as x16 on x15.b3=x16.d3
join t3 as x17 on x15.b3=x17.a3
join t3 as x18 on x16.b3=x18.d3;
select count(*) from t3 as x0
join t3 as x1 on x0.b3=x1.d3
join t3 as x2 on x1.b3=x2.d3
join t3 as x3 on x2.b3=x3.d3
join t3 as x4 on x3.b3=x4.d3
join t3 as x5 on x4.b3=x5.d3
join t3 as x6 on x5.b3=x6.d3
join t3 as x7 on x6.b3=x7.d3
join t3 as x8 on x7.b3=x8.d3
join t3 as x9 on x8.b3=x9.d3
join t3 as x10 on x9.b3=x10.d3
join t3 as x11 on x10.b3=x11.d3
join t3 as x12 on x11.b3=x12.d3
join t3 as x13 on x12.b3=x13.d3
join t3 as x14 on x13.b3=x14.d3
join t3 as x15 on x14.b3=x15.d3
join t3 as x16 on x15.b3=x16.d3
join t3 as x17 on x15.b3=x17.a3
join t3 as x18 on x16.b3=x18.d3;
connection ddl;
drop table t1,t2,t3, t3_hash;
###############################
## Test Primary key and unique key defined 'out of order'
## wrt. the order in which columns was defined in 'create table'
connection ddl;
create table t3 (a3 int, b3 int, c3 int, d3 int,
primary key(b3, a3)) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
create table t3_hash (a3 int, b3 int, c3 int, d3 int,
primary key(b3,a3) using hash) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
create table t3_unq (pk int, a3 int not null, b3 int not null, c3 int, d3 int,
primary key(pk) using hash, unique key(b3,a3) using hash) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t3 values (0x1f, 0x2f, 1, 0x1f);
insert into t3 values (0x2f, 0x3f, 2, 0x2f);
insert into t3 values (0x3f, 0x1f, 3, 0x3f);
insert into t3_hash values (0x1f, 0x2f, 1, 0x1f);
insert into t3_hash values (0x2f, 0x3f, 2, 0x2f);
insert into t3_hash values (0x3f, 0x1f, 3, 0x3f);
insert into t3_unq values (1001, 0x1f, 0x2f, 1, 0x1f);
insert into t3_unq values (1002, 0x2f, 0x3f, 2, 0x2f);
insert into t3_unq values (1003, 0x3f, 0x1f, 3, 0x3f);
## Table scans (ALL) as pushed root
explain
select * from t3 x, t3 y where y.a3=x.d3 and y.b3=x.b3;
--sorted_result
select * from t3 x, t3 y where y.a3=x.d3 and y.b3=x.b3;
explain
select * from t3_hash x, t3_hash y where y.a3=x.d3 and y.b3=x.b3;
--sorted_result
select * from t3_hash x, t3_hash y where y.a3=x.d3 and y.b3=x.b3;
explain
select * from t3_unq x, t3_unq y where y.a3=x.d3 and y.b3=x.b3;
--sorted_result
select * from t3_unq x, t3_unq y where y.a3=x.d3 and y.b3=x.b3;
## Lookup (eq_ref/const) as pushed root
explain
select * from t3 x, t3 y where y.a3=x.d3 and y.b3=x.b3
and x.a3=0x2f and x.b3=0x3f;
select * from t3 x, t3 y where y.a3=x.d3 and y.b3=x.b3
and x.a3=0x2f and x.b3=0x3f;
explain
select * from t3_hash x, t3_hash y where y.a3=x.d3 and y.b3=x.b3
and x.a3=0x2f and x.b3=0x3f;
select * from t3_hash x, t3_hash y where y.a3=x.d3 and y.b3=x.b3
and x.a3=0x2f and x.b3=0x3f;
explain
select * from t3_unq x, t3_unq y where y.a3=x.d3 and y.b3=x.b3
and x.a3=0x2f and x.b3=0x3f;
select * from t3_unq x, t3_unq y where y.a3=x.d3 and y.b3=x.b3
and x.a3=0x2f and x.b3=0x3f;
connection ddl;
drop table t3, t3_hash, t3_unq;
###########
connection ddl;
create table t3 (a3 int, b3 int, c3 int, d3 int,
primary key(a3), unique key(d3)) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t3 values (0x1f, 0x2f, 1, 0x1f);
insert into t3 values (0x2f, 0x3f, 2, 0x2f);
insert into t3 values (0x3f, 0x1f, 3, 0x3f);
insert into t3 values (0x4f, 0, null, null);
explain
select * from t3 as t1
left outer join t3 as t2 on t2.d3 = t1.d3
left outer join t3 as t3 on t3.a3 = t2.d3;
--sorted_result
select * from t3 as t1
left outer join t3 as t2 on t2.d3 = t1.d3
left outer join t3 as t3 on t3.a3 = t2.d3;
## Test usage of nullable unique key column in where clause on pushed parent node
explain
select * from t3 as t1
left outer join t3 as t2 on t2.d3 = t1.a3
left outer join t3 as t3 on t3.a3 = t2.d3
where t1.d3 = 47;
--sorted_result
select * from t3 as t1
left outer join t3 as t2 on t2.d3 = t1.a3
left outer join t3 as t3 on t3.a3 = t2.d3
where t1.d3 = 47;
explain
select * from t3 as t1
left outer join t3 as t2 on t2.d3 = t1.a3
left outer join t3 as t3 on t3.a3 = t2.d3
where t1.d3 >= 47;
--sorted_result
select * from t3 as t1
left outer join t3 as t2 on t2.d3 = t1.a3
left outer join t3 as t3 on t3.a3 = t2.d3
where t1.d3 >= 47;
explain
select * from t3 as t1
left outer join t3 as t2 on t2.d3 = t1.a3
left outer join t3 as t3 on t3.a3 = t2.d3
where t1.d3 is null;
--sorted_result
select * from t3 as t1
left outer join t3 as t2 on t2.d3 = t1.a3
left outer join t3 as t3 on t3.a3 = t2.d3
where t1.d3 is null;
explain
select * from t3 as t1
left outer join t3 as t2 on t2.d3 = t1.a3
left outer join t3 as t3 on t3.a3 = t2.d3
where t1.d3 is not null;
--sorted_result
select * from t3 as t1
left outer join t3 as t2 on t2.d3 = t1.a3
left outer join t3 as t3 on t3.a3 = t2.d3
where t1.d3 is not null;
connection ddl;
drop table t3;
####### Composite unique keys, 'const' is part of EQ_REF on child nodes ####
connection ddl;
create table t3 (a3 int not null, b3 int not null, c3 int, d3 int,
primary key(a3), unique key(b3,d3), unique key(c3,b3), unique key(c3,d3)) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t3 values (0x1f, 0x2f, 1, 0x1f);
insert into t3 values (0x2f, 0x3f, 2, 0x2f);
insert into t3 values (0x3f, 0x1f, 3, 0x3f);
insert into t3 values (0x40, 0, null, null);
insert into t3 values (0x41, 0, null, null);
insert into t3 values (0x42, 0, 4, null);
insert into t3 values (0x43, 0, null, 0x43);
## Baseline: Not pushed as only one of the columns in unique indexes are REF'ed
explain
select straight_join *
from t3 as x join t3 as y on x.b3 = y.b3;
--sorted_result
select straight_join *
from t3 as x join t3 as y on x.b3 = y.b3;
## Extend query above with 'where <const cond>'
explain
select straight_join *
from t3 as x join t3 as y on x.b3 = y.b3
where y.d3 = 0x2f;
--sorted_result
select straight_join *
from t3 as x join t3 as y on x.b3 = y.b3
where y.d3 = 0x2f;
--replace_column 10 # 11 #
explain
select straight_join *
from t3 as x join t3 as y on x.c3 = y.c3
where y.d3 = 0x2f;
--sorted_result
select straight_join *
from t3 as x join t3 as y on x.c3 = y.c3
where y.d3 = 0x2f;
explain
select straight_join *
from t3 as x join t3 as y on x.d3 = y.d3
where y.b3 = 0x2f;
--sorted_result
select straight_join *
from t3 as x join t3 as y on x.d3 = y.d3
where y.b3 = 0x2f;
explain
select straight_join *
from t3 as x join t3 as y on x.d3 = y.d3
where y.b3 = 0x20+0x2f;
--sorted_result
select straight_join *
from t3 as x join t3 as y on x.d3 = y.d3
where y.b3 = 0x20+0x2f;
## Not pushable as 'not null' is actually not a single const value
--replace_column 10 # 11 #
explain
select straight_join *
from t3 as x join t3 as y on x.b3 = y.b3
where y.d3 is not null;
--sorted_result
select straight_join *
from t3 as x join t3 as y on x.b3 = y.b3
where y.d3 is not null;
## Neither 'is null' pushable as uniqueness is not defined for null
## ... and null's are not present in the unique index
explain
select straight_join *
from t3 as x join t3 as y on x.b3 = y.b3
where y.d3 is null;
--sorted_result
select straight_join *
from t3 as x join t3 as y on x.b3 = y.b3
where y.d3 is null;
explain
select straight_join *
from t3 as x join t3 as y on x.c3 = y.c3
where y.b3 = 0;
--sorted_result
select straight_join *
from t3 as x join t3 as y on x.c3 = y.c3
where y.b3 = 0;
## As 'b3' is defined as 'not null', this query will optimized as 'Impossible WHERE' (No push)
explain
select straight_join *
from t3 as x join t3 as y on x.c3 = y.c3
where y.b3 is null;
--sorted_result
select straight_join *
from t3 as x join t3 as y on x.c3 = y.c3
where y.b3 is null;
## Will break up query in 2 pushed joins.
## Last join (join t3 as y2) refer x1.c3 which will
## be handled as a constant paramValue wrt. scope of the
## second pushed join.
explain
select straight_join * from
t3 as x1
join t3 as y1 on y1.b3 = x1.b3 and y1.d3 = x1.d3
join t3 as x2 on x2.b3 = y1.b3+0
join t3 as y2 on y2.b3 = x2.c3 and y2.d3 = x1.c3;
--sorted_result
select straight_join * from
t3 as x1
join t3 as y1 on y1.b3 = x1.b3 and y1.d3 = x1.d3
join t3 as x2 on x2.b3 = y1.b3+0
join t3 as y2 on y2.b3 = x2.c3 and y2.d3 = x1.c3;
###########################
### Prepared statments ####
###########################
prepare stmt1 from
'select straight_join *
from t3 as x join t3 as y on x.b3 = y.b3
where y.d3 = 0x2f';
#execute multiple times
execute stmt1;
execute stmt1;
# Execute after drop expected to fail
drop prepare stmt1;
--error 1243
execute stmt1;
# Multiple prepare of same stmt should silently discard prev prepared stmt
prepare stmt1 from
'select straight_join *
from t3 as x join t3 as y on x.b3 = y.b3
where y.d3 = 0x2f';
prepare stmt1 from
'select straight_join *
from t3 as x join t3 as y on x.b3 = y.b3
where y.d3 = 0x2f';
drop prepare stmt1;
#Prepare explain'ed statement and execute it
prepare stmt1 from
'explain select straight_join *
from t3 as x join t3 as y on x.b3 = y.b3
where y.d3 = 0x2f';
execute stmt1;
execute stmt1;
#survives commit;
commit;
execute stmt1;
# Drop index used by query -> Query plan should change to unpushed join
connection ddl;
drop index b3 on t3;
connection spj;
--replace_column 10 # 11 #
execute stmt1;
# Then recreate it -> original query plan
connection ddl;
create unique index b3 on t3(b3,d3);
connection spj;
execute stmt1;
drop prepare stmt1;
### Prepared stmt with dynamic parameters ('?') ###
prepare stmt1 from
'explain select straight_join *
from t3 as x join t3 as y on x.b3 = y.b3
where y.d3 = ?';
set @a=47;
execute stmt1 using @a;
set @a=0;
execute stmt1 using @a;
set @a=null;
execute stmt1 using @a;
prepare stmt1 from
'select straight_join *
from t3 as x join t3 as y on x.b3 = y.b3
where y.d3 = ?';
set @a=47;
execute stmt1 using @a;
set @a=0;
execute stmt1 using @a;
set @a=null;
execute stmt1 using @a;
prepare stmt1 from
'explain select straight_join *
from t3 as x join t3 as y on x.b3 = y.b3 and x.d3 = y.d3
where x.a3 = ?';
set @a=47;
execute stmt1 using @a;
set @a=0;
execute stmt1 using @a;
set @a=null;
execute stmt1 using @a;
prepare stmt1 from
'select straight_join *
from t3 as x join t3 as y on x.b3 = y.b3 and x.d3 = y.d3
where x.a3 = ?';
set @a=47;
execute stmt1 using @a;
set @a=0;
execute stmt1 using @a;
set @a=null;
execute stmt1 using @a;
connection ddl;
drop table t3;
connection spj;
# Execute after table dropped should fail
set @a=47;
--error 1146
execute stmt1 using @a;
####################
# test index scan disguised as JT_ALL
connection ddl;
create table t1 (a int primary key, b int, c int, index(b,c)) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t1 values (1,null, 2);
insert into t1 values (2,1, null);
insert into t1 values (3,2,2);
insert into t1 values (4,null, 2);
insert into t1 values (5,1, null);
insert into t1 values (6,2,2);
set ndb_join_pushdown=false;
--sorted_result
select *
from t1
join t1 as t2 on (t2.b = t1.b or t2.b = t1.a)
join t1 as t3 on t3.a = t2.a
join t1 as t4 on t4.a = t3.b /* index scan disguised as JT_ALL, pushdown=off */;
set ndb_join_pushdown=true;
explain
select *
from t1
join t1 as t2 on (t2.b = t1.b or t2.b = t1.a)
join t1 as t3 on t3.a = t2.a
join t1 as t4 on t4.a = t3.b /* index scan disguised as JT_ALL, pushdown=on */;
--sorted_result
select *
from t1
join t1 as t2 on (t2.b = t1.b or t2.b = t1.a)
join t1 as t3 on t3.a = t2.a
join t1 as t4 on t4.a = t3.b /* index scan disguised as JT_ALL, pushdown=on */;
## Test subquery execution where 'Full scan on null key' strategy requires
## table scan execution in addition to the key lookup which was prepared
## as part of the pushed join NdbQuery
explain
select *
from t1 where b in
(select x.a from t1 as x join t1 as y on (y.a = x.b))
xor c > 5;
--sorted_result
select *
from t1 where b in
(select x.a from t1 as x join t1 as y on (y.a = x.b))
xor c > 5;
##############
## Subqueries with EQ_REFs in subquery containing an outer referrences
## to 't1.b' should not be pushed as outer referrences are outside
## the scope of our JOIN_TAB's
##############
--replace_column 10 # 11 #
explain
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;
--sorted_result
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;
connection ddl;
drop table t1;
# mixed engines
connection ddl;
create table t1 (a int primary key, b int) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
create table t2 (a int primary key, b int) engine = myisam;
connection spj;
insert into t1 values(1,1), (2,2), (3,3), (4,4);
insert into t2 values(1,1), (2,2), (3,3), (4,4);
explain
select * from t1, t2, t1 as t3
where t2.a = t1.b
and t3.a = t2.b /* mixed engines */;
--sorted_result
select * from t1, t2, t1 as t3
where t2.a = t1.b
and t3.a = t2.b /* mixed engines */;
connection ddl;
drop table t1, t2;
# Tables with blob, but not in the selected columns:
connection ddl;
create table t1 (a int primary key, b int, c blob) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
create table t2 (a int primary key, b int) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t1 values (1,1, 'kalle');
insert into t1 values (2,1, 'kalle');
insert into t1 values (3,3, 'kalle');
insert into t1 values (4,1, 'kalle');
insert into t2 values (1,1);
insert into t2 values (2,1);
insert into t2 values (3,3);
insert into t2 values (4,1);
set ndb_join_pushdown=true;
explain
select t1.a, t1.b, t2.a, t2.b
from t1, t2
where t2.a = t1.b;
--sorted_result
select t1.a, t1.b, t2.a, t2.b
from t1, t2
where t2.a = t1.b;
explain
select t1.a, t1.b, t2.a, t2.b
from t1, t2
where t2.a = t1.b
and t1.a = 2;
--sorted_result
select t1.a, t1.b, t2.a, t2.b
from t1, t2
where t2.a = t1.b
and t1.a = 2;
explain
select t1.a, t1.b, t2.a, t2.b
from t1, t2
where t1.a = t2.b;
--sorted_result
select t1.a, t1.b, t2.a, t2.b
from t1, t2
where t2.a = t1.b;
explain
select t1.a, t1.b, t2.a, t2.b
from t1, t2
where t1.a = t2.b
and t2.a = 3;
--sorted_result
select t1.a, t1.b, t2.a, t2.b
from t1, t2
where t1.a = t2.b
and t2.a = 3;
#
# result sets contain blob
# i.e no push
explain
select *
from t1, t2
where t2.a = t1.b;
--sorted_result
select *
from t1, t2
where t2.a = t1.b;
explain
select *
from t1, t2
where t2.a = t1.b
and t1.a = 2;
--sorted_result
select *
from t1, t2
where t2.a = t1.b
and t1.a = 2;
explain
select *
from t1, t2
where t1.a = t2.b;
--sorted_result
select *
from t1, t2
where t2.a = t1.b;
explain
select *
from t1, t2
where t1.a = t2.b
and t2.a = 3;
--sorted_result
select *
from t1, t2
where t1.a = t2.b
and t2.a = 3;
connection ddl;
drop table t1, t2;
## Test usage of a constValue() as part of the EQ_REF key relating a child operation
## with its previous parents.
## All datatypes are tested in the section below
##
connection ddl;
create table t3 (a3 int, b3 tinyint, c3 int not null, d3 int not null,
primary key(a3,b3)) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t3 values (0x1f, 0x2f, 1, 0x1f);
insert into t3 values (0x2f, 0x3f, 2, 0x2f);
insert into t3 values (0x3f, 0x1f, 3, 0x3f);
explain
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3="63";
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3="63";
connection ddl;
drop table t3;
create table t3 (a3 int, b3 tinyint unsigned, c3 int not null, d3 int not null,
primary key(a3,b3)) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t3 values (0x1f, 0x2f, 1, 0x1f);
insert into t3 values (0x2f, 0x3f, 2, 0x2f);
insert into t3 values (0x3f, 0x1f, 3, 0x3f);
explain
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
connection ddl;
drop table t3;
create table t3 (a3 int, b3 smallint, c3 int not null, d3 int not null,
primary key(a3,b3)) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t3 values (0x1f, 0x2f, 1, 0x1f);
insert into t3 values (0x2f, 0x3f, 2, 0x2f);
insert into t3 values (0x3f, 0x1f, 3, 0x3f);
explain
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
connection ddl;
drop table t3;
create table t3 (a3 int, b3 smallint unsigned, c3 int not null, d3 int not null,
primary key(a3,b3)) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t3 values (0x1f, 0x2f, 1, 0x1f);
insert into t3 values (0x2f, 0x3f, 2, 0x2f);
insert into t3 values (0x3f, 0x1f, 3, 0x3f);
explain
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
connection ddl;
drop table t3;
create table t3 (a3 int, b3 mediumint, c3 int not null, d3 int not null,
primary key(a3,b3)) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t3 values (0x1f, 0x2f, 1, 0x1f);
insert into t3 values (0x2f, 0x3f, 2, 0x2f);
insert into t3 values (0x3f, 0x1f, 3, 0x3f);
explain
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
connection ddl;
drop table t3;
create table t3 (a3 int, b3 mediumint unsigned, c3 int not null, d3 int not null,
primary key(a3,b3)) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t3 values (0x1f, 0x2f, 1, 0x1f);
insert into t3 values (0x2f, 0x3f, 2, 0x2f);
insert into t3 values (0x3f, 0x1f, 3, 0x3f);
explain
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
connection ddl;
drop table t3;
create table t3 (a3 int, b3 int, c3 int not null, d3 int not null,
primary key(a3,b3)) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t3 values (0x1f, 0x2f, 1, 0x1f);
insert into t3 values (0x2f, 0x3f, 2, 0x2f);
insert into t3 values (0x3f, 0x1f, 3, 0x3f);
explain
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
connection ddl;
drop table t3;
create table t3 (a3 int, b3 int unsigned, c3 int not null, d3 int not null,
primary key(a3,b3)) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t3 values (0x1f, 0x2f, 1, 0x1f);
insert into t3 values (0x2f, 0x3f, 2, 0x2f);
insert into t3 values (0x3f, 0x1f, 3, 0x3f);
explain
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
connection ddl;
drop table t3;
create table t3 (a3 int, b3 bigint, c3 int not null, d3 int not null,
primary key(a3,b3)) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t3 values (0x1f, 0x2f, 1, 0x1f);
insert into t3 values (0x2f, 0x3f, 2, 0x2f);
insert into t3 values (0x3f, 0x1f, 3, 0x3f);
explain
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
connection ddl;
drop table t3;
create table t3 (a3 int, b3 bigint unsigned, c3 int not null, d3 int not null,
primary key(a3,b3)) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t3 values (0x1f, 0x2f, 1, 0x1f);
insert into t3 values (0x2f, 0x3f, 2, 0x2f);
insert into t3 values (0x3f, 0x1f, 3, 0x3f);
explain
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
connection ddl;
drop table t3;
create table t3 (a3 int, b3 boolean, c3 int not null, d3 int not null,
primary key(a3,b3)) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t3 values (0x1f, 0, 1, 0x1f);
insert into t3 values (0x2f, 1, 2, 0x2f);
insert into t3 values (0x3f, 0, 3, 0x3f);
explain
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=1;
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=1;
connection ddl;
drop table t3;
create table t3 (a3 int, b3 float, c3 int not null, d3 int not null,
primary key(a3,b3)) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t3 values (0x1f, 2.71, 1, 0x1f);
insert into t3 values (0x2f, 3.00, 2, 0x2f);
insert into t3 values (0x3f, 0.50, 3, 0x3f);
explain
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=3.0;
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=3.0;
connection ddl;
drop table t3;
create table t3 (a3 int, b3 float unsigned, c3 int not null, d3 int not null,
primary key(a3,b3)) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t3 values (0x1f, 2.71, 1, 0x1f);
insert into t3 values (0x2f, 3.00, 2, 0x2f);
insert into t3 values (0x3f, 0.50, 3, 0x3f);
explain
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=3.0;
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=3.0;
connection ddl;
drop table t3;
create table t3 (a3 int, b3 double, c3 int not null, d3 int not null,
primary key(a3,b3)) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t3 values (0x1f, 2.71, 1, 0x1f);
insert into t3 values (0x2f, 3.14, 2, 0x2f);
insert into t3 values (0x3f, 0.50, 3, 0x3f);
explain
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=3.14;
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=3.14;
connection ddl;
drop table t3;
create table t3 (a3 int, b3 double unsigned, c3 int not null, d3 int not null,
primary key(a3,b3)) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t3 values (0x1f, 2.71, 1, 0x1f);
insert into t3 values (0x2f, 3.14, 2, 0x2f);
insert into t3 values (0x3f, 0.50, 3, 0x3f);
explain
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=3.14;
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=3.14;
connection ddl;
drop table t3;
create table t3 (a3 int, b3 decimal, c3 int not null, d3 int not null,
primary key(a3,b3)) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t3 values (0x1f, 0x2f, 1, 0x1f);
insert into t3 values (0x2f, 0x3f, 2, 0x2f);
insert into t3 values (0x3f, 0x1f, 3, 0x3f);
explain
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=63;
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=63;
connection ddl;
drop table t3;
create table t3 (a3 int, b3 decimal(12,4), c3 int not null, d3 int not null,
primary key(a3,b3)) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t3 values (0x1f, 2.71, 1, 0x1f);
insert into t3 values (0x2f, 3.14, 2, 0x2f);
insert into t3 values (0x3f, 0.50, 3, 0x3f);
explain
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=3.14;
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=3.14;
connection ddl;
drop table t3;
create table t3 (a3 int, b3 date, c3 int not null, d3 int not null,
primary key(a3,b3)) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t3 values (0x1f, '1905-05-17', 1, 0x1f);
insert into t3 values (0x2f, '2000-02-28', 2, 0x2f);
insert into t3 values (0x3f, '2000-02-29', 3, 0x3f);
explain
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='2000-02-28';
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='2000-02-28';
## Temp removed due to lack of DATETIME2 support
#connection ddl;
# drop table t3;
# create table t3 (a3 int, b3 datetime, c3 int not null, d3 int not null,
# primary key(a3,b3)) engine = ndb;
#connection spj;
# insert into t3 values (0x1f, '1905-05-17 12:30:00', 1, 0x1f);
# insert into t3 values (0x2f, '2000-02-28 23:59:00', 2, 0x2f);
# insert into t3 values (0x3f, '2000-02-29 12:59:59', 2, 0x3f);
# explain
# select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='2000-02-28 23:59';
# select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='2000-02-28 23:59';
#
#connection ddl;
# drop table t3;
# create table t3 (a3 int, b3 time, c3 int not null, d3 int not null,
# primary key(a3,b3)) engine = ndb;
#connection spj;
# insert into t3 values (0x1f, '12:30:00', 1, 0x1f);
# insert into t3 values (0x2f, '23:59:00', 2, 0x2f);
# insert into t3 values (0x3f, '12:59:59', 2, 0x3f);
# explain
# select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='23:59';
# select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='23:59';
connection ddl;
drop table t3;
create table t3 (a3 int, b3 char(16), c3 int not null, d3 int not null,
primary key(a3,b3)) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t3 values (0x1f, 'Ole', 1, 0x1f);
insert into t3 values (0x2f, 'Dole', 2, 0x2f);
insert into t3 values (0x3f, 'Doffen', 2, 0x3f);
explain
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='Dole';
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='Dole';
connection ddl;
drop table t3;
create table t3 (a3 int, b3 varchar(16), c3 int not null, d3 int not null,
primary key(a3,b3)) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t3 values (0x1f, 'Ole', 1, 0x1f);
insert into t3 values (0x2f, 'Dole', 2, 0x2f);
insert into t3 values (0x3f, 'Doffen', 2, 0x3f);
explain
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='Dole';
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='Dole';
connection ddl;
drop table t3;
create table t3 (a3 int, b3 varchar(512), c3 int not null, d3 int not null,
primary key(a3,b3)) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t3 values (0x1f, 'Ole', 1, 0x1f);
insert into t3 values (0x2f, 'Dole', 2, 0x2f);
insert into t3 values (0x3f, 'Doffen', 2, 0x3f);
explain
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='Dole';
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='Dole';
connection ddl;
drop table t3;
create table t3 (a3 int, b3 binary(16), c3 int not null, d3 int not null,
primary key(a3,b3)) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t3 values (0x1f, 'Ole', 1, 0x1f);
insert into t3 values (0x2f, 'Dole', 2, 0x2f);
insert into t3 values (0x3f, 'Doffen', 2, 0x3f);
explain
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='Dole';
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='Dole';
connection ddl;
drop table t3;
create table t3 (a3 int, b3 varbinary(16), c3 int not null, d3 int not null,
primary key(a3,b3)) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t3 values (0x1f, 'Ole', 1, 0x1f);
insert into t3 values (0x2f, 'Dole', 2, 0x2f);
insert into t3 values (0x3f, 'Doffen', 2, 0x3f);
explain
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='Dole';
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='Dole';
connection ddl;
drop table t3;
## Joins where the datatype of the EQ_REF columns are not identical
## should not be pushed
##
connection ddl;
create table t3 (a3 int, b3 tinyint, c3 int not null, d3 int not null,
primary key(a3,b3)) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t3 values (0x1f, 0x2f, 1, 0x1f);
insert into t3 values (0x2f, 0x3f, 2, 0x2f);
insert into t3 values (0x3f, 0x1f, 3, 0x3f);
explain
select * from t3 x, t3 y where y.a3=x.b3 and y.b3="63";
select * from t3 x, t3 y where y.a3=x.b3 and y.b3="63";
connection ddl;
drop table t3;
##
## Testing of varchar datatype as part of lookup key and index bounds.
## Need special attention due to the 'ShrinkVarchar' format used by mysqld.
connection ddl;
create table t3 (a3 varchar(16), b3 int, c3 int not null, d3 int not null,
primary key(a3,b3)) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t3 values ('Ole', 0x1f, 1, 0x1f);
insert into t3 values ('Dole', 0x2f, 2, 0x2f);
insert into t3 values ('Doffen', 0x3f, 2, 0x3f);
# Varchar is lookup key
explain
select * from t3 x, t3 y where x.a3='Dole' and x.b3=0x2f and y.a3=x.a3 and y.b3=x.d3;
select * from t3 x, t3 y where x.a3='Dole' and x.b3=0x2f and y.a3=x.a3 and y.b3=x.d3;
# Varchar as hi/low bound
explain
select * from t3 x, t3 y where x.a3='Dole' and y.a3=x.a3 and y.b3=x.d3;
select * from t3 x, t3 y where x.a3='Dole' and y.a3=x.a3 and y.b3=x.d3;
connection ddl;
drop table t3;
connection ddl;
create table t1 (k int primary key, b int) engine = ndb
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
insert into t1 values (1,1), (2,1), (3,1), (4,1);
## Pushed join driven by a scan, with cached row lookups:
## (Note: We force the Scan to not be pushed as the parent op
## by making the join condition on t2 a non-FIELD_ITEM ('t1.b+0')
## As all column 'b' has the same value (1), the scan will refer
## the same t2 (parent) row in every access. This will trigger the
## row caching in join_read_key() where we eliminate redundant lookups
## where 'next row == current row'. In order to work for linked operations,
## the value and status for all linked tables should be kept unaltered.
explain
select *
from t1
straight_join t1 as t2 on t2.k = t1.b+0
straight_join t1 as t3 on t3.k = t2.b
straight_join t1 as t4 on t4.k = t1.b;
--sorted_result
select *
from t1
straight_join t1 as t2 on t2.k = t1.b+0
straight_join t1 as t3 on t3.k = t2.b
straight_join t1 as t4 on t4.k = t1.b;
## Similar example as above, except that access to 't2' is made
## a const table access
explain
select *
from t1
straight_join t1 as t2 on t2.k = t1.b+0
straight_join t1 as t3 on t3.k = t2.b
straight_join t1 as t4 on t4.k = t1.b
where t2.k = 1;
--sorted_result
select *
from t1
straight_join t1 as t2 on t2.k = t1.b+0
straight_join t1 as t3 on t3.k = t2.b
straight_join t1 as t4 on t4.k = t1.b
where t2.k = 1;
connection ddl;
drop table t1;
##
# Try with higher row-count to test batching/flow control
#
--error 0,1193
set global debug='+d,max_64rows_in_spj_batches';
connection ddl;
create table t1 (
a int not null auto_increment,
b char(255) not null,
c int not null,
d char(255) not null,
primary key (`a`,`b`)
) engine=ndbcluster
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
connection spj;
let $1=1000;
disable_query_log;
while ($1)
{
eval insert into t1(a,b,c,d) values
($1, 'a', $1, 'a'),($1, 'b', $1+1, 'b'),($1, 'c', $1-1, 'c');
dec $1;
}