-- source include/have_ndb.inc
########################################
# Define two connections as we want DDL to use its own connection
# in order to keep DDL statistics counting out of the way
# of the SPJ testing
########################################
connect (spj,localhost,root,,test);
connect (ddl,localhost,root,,test);
--disable_warnings
connection ddl;
drop table if exists t1,t2,t3,t4;
--enable_warnings
#######################################
# Enable ndb$info counters for SPJ block.
connection ddl;
--source ndbinfo_create.inc
connection spj;
# Remember all SPJ conters when test started.
# Will report and compare the diff. at end of entire test
create temporary table spj_counts_at_startup
select counter_name, sum(val) as val
from ndbinfo.counters
where block_name='DBSPJ'
group by counter_name;
# Save old counter values.
let $scan_count_at_startup = query_get_value(show status like 'Ndb_scan_count', Value, 1);
let $pruned_scan_count_at_startup = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
let $sorted_scan_count_at_startup = query_get_value(show status like 'Ndb_sorted_scan_count', Value, 1);
let $pushed_queries_defined_at_startup = query_get_value(show status like 'Ndb_pushed_queries_defined', Value, 1);
let $pushed_queries_dropped_at_startup = query_get_value(show status like 'Ndb_pushed_queries_dropped', Value, 1);
let $pushed_queries_executed_at_startup = query_get_value(show status like 'Ndb_pushed_queries_executed', Value, 1);
let $pushed_reads_at_startup = query_get_value(show status like 'Ndb_pushed_reads', Value, 1);
# Use this table and the two queries below to turn of meassuring
# SCAN_ROWS_RETURNED for certain bushy scan queries. The reason for this is
# that growth of this counter is platform dependent for these queries. There
# are two reasons for this:
# 1. Distribution hashing (partitioning) of tables is endian dependent. This
# may cause data to be more skewed on some platforms. This again requires more
# batches to scan the table and thus more repeats of repeatable scans (i.e.
# those that will be repeated for each batch of the other branch of a bushy
# scan). This increases the overall scan row count.
# 2. If a timer expires in LQH after receiving SCAN_FRAGREQ, LQH may decide to
# send SCAN_FRAGCONF immediately, even if more tuples could fit in the batch.
# As above this causes more repeats of repeatable scans.
create temporary table spj_save_counts like spj_counts_at_startup;
insert into spj_save_counts values ('SCAN_ROWS_RETURNED', 0);
# Record current counter value.
let $save_scan_rows_returned = 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';
# Update spj_counts_at_startup to compensate for counter increments since
# running save_scan_rows_returned.
let $compensate_scan_rows_returned = update spj_counts_at_startup set val = val + (select sum(val) from ndbinfo.counters where block_name='DBSPJ' and counter_name='SCAN_ROWS_RETURNED') - (select val from spj_save_counts where counter_name='SCAN_ROWS_RETURNED') where counter_name='SCAN_ROWS_RETURNED';
##############
# Test start
set @save_ndb_join_pushdown = @@session.ndb_join_pushdown;
set ndb_join_pushdown = true;
connection ddl;
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;
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 extended
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 we do not push an operation if this prevents us from using
# 'join buffer'.
explain extended
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 extended 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 extended
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 extended
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 extended
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 extended
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;
explain extended
select *
from t1
join t1 as t2 on t2.a = t1.c and t2.b = t1.d procedure analyse();
explain extended
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;
explain extended
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;
explain extended
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;
explain extended
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;
explain extended
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 extended
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 extended
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 extended
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 extended
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 extended
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 extended
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 extended
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;
explain extended
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 extended
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 extended
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
explain extended 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
explain extended 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.
explain extended 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 extended
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 extended
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 extended
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 extended
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 extended
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 extended
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 extended
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 extended
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 extended
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 extended
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 extended
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 extended
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 extended
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 extended
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 extended
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 extended
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 extended
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 extended
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 extended
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 extended
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 extended
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 extended
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 extended
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 extended
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 extended
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 extended
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;
# 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 extended
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 extended
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 extended
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:
explain extended
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 extended
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 extended
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 expressons
# Filesort on pushed joins are not possible as the
# 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.
# With 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) Use a temporary result file for the result, which is then filesort'ed
# (-> 'Using temporary; Using filesort')
#
# 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'
#
## pushed join w/ 'simple order' on non_PK - Need temp table + filesort
explain extended
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 extended
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 extended
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 extended
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 extended
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;
## PK column in incorrect order, -> filesort to tempfile
explain extended
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 extended
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 -> filesort to tempfile
explain extended
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 extended
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 extended
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 extended
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;
explain extended
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 extended
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;
explain extended
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;
eval $save_scan_rows_returned;
explain select count(*) from t1
join t1 as t2 on t2.a = t1.c
join t1 as t3 on t3.a = t1.c;
select count(*) from t1
join t1 as t2 on t2.a = t1.c
join t1 as t3 on t3.a = t1.c;
# Test bushy join with pruned scan and larger result set.
connection ddl;
CREATE TABLE tx (
a int NOT NULL,
PRIMARY KEY (`a`)
);
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, 0 from tx as x1 cross join tx as x2;
explain select count(*) from t1 as x1
join t1 as x2 on x2.a = x1.c and x1.b < 2
join t1 as x3 on x3.a = x1.c;
select count(*) from t1 as x1
join t1 as x2 on x2.a = x1.c and x1.b < 2
join t1 as x3 on x3.a = x1.c;
eval $compensate_scan_rows_returned;
connection ddl;
drop table t1;
drop table tx;
# pushed mrr does not yet handle multiple PK operations in same transaction
# Need 6.0 result handling stuff to simplify result handling
# *** join push is currently dissabled for these ****
#
connection ddl;
create table t1 (a int, b int, primary key(a) using hash) engine = ndb;
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;
explain extended
select *
from t1, t1 as t2
where t1.a in (1,3,5)
and t2.a = t1.b;
--sorted_result
select *
from t1, t1 as t2
where t1.a in (1,3,5)
and t2.a = t1.b;
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;
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;
explain extended
select *
from t1, t1 as t2
where t1.a in (1,3,5)
and t2.a = t1.b;
--sorted_result
select *
from t1, t1 as t2
where t1.a in (1,3,5)
and t2.a = t1.b;
## 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 extended
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;
create table t2 (c int, d int, primary key(c)) engine = ndb;
create table t3 (a3 int, b3 int, c3 int not null, d3 int not null,
primary key(a3, b3)) engine = ndb;
create table t3_hash (a3 int, b3 int, c3 int not null, d3 int not null,
primary key(a3, b3) using hash) engine = ndb;
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 extended
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 extended
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:
explain extended
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
explain extended
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 extended
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 extended
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 extended
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 extended
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 extended
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 extended
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 extended
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 extended
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;
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;
create table t3_hash (a3 int, b3 int, c3 int, d3 int,
primary key(b3,a3) using hash) engine = ndb;
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;
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 extended
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 extended
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 extended
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 extended
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 extended
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 extended
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;
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 extended
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 extended
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 extended
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 extended
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 extended
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;
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 extended
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 extended
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;
explain extended
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 extended
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 extended
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
explain extended
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 extended
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 extended
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 extended
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 extended
select straight_join * from
t3 as x1
join t3 as y1 on x1.b3 = y1.b3 and x1.d3 = y1.d3
join t3 as x2 on x2.b3 = y1.b3
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 x1.b3 = y1.b3 and x1.d3 = y1.d3
join t3 as x2 on x2.b3 = y1.b3
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;
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;
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;
set ndb_join_pushdown=true;
explain extended
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;
--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;
## 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 extended
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
##############
explain extended
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;
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 extended
select * from t1, t2, t1 as t3
where t2.a = t1.b
and t3.a = t2.b;
--sorted_result
select * from t1, t2, t1 as t3
where t2.a = t1.b
and t3.a = t2.b;
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;
create table t2 (a int primary key, b int) engine = ndb;
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 extended
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 extended
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 extended
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 extended
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 extended
select *
from t1, t2
where t2.a = t1.b;
--sorted_result
select *
from t1, t2
where t2.a = t1.b;
explain extended
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 extended
select *
from t1, t2
where t1.a = t2.b;
--sorted_result
select *
from t1, t2
where t2.a = t1.b;
explain extended
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;
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 extended
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;
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 extended
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;
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 extended
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;
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 extended
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;
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 extended
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;
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 extended
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;
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 extended
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;
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 extended
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;
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 extended
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;
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 extended
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;
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 extended
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;
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 extended
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;
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 extended
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;
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 extended
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;
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 extended
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;
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 extended
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;
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 extended
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;
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 extended
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';
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 extended
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 extended
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;
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 extended
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;
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 extended
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;
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 extended
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;
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 extended
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;
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 extended
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;
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 extended
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;
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 extended
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 extended
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;
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 extended
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 extended
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
#
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;
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;
}
enable_query_log;
explain extended
select count(*)
from t1
join t1 as t2 on t2.a = t1.c and t2.b = t1.d
join t1 as t3 on t3.a = t2.c and t3.b = t2.d;
select count(*)
from t1
join t1 as t2 on t2.a = t1.c and t2.b = t1.d
join t1 as t3 on t3.a = t2.c and t3.b = t2.d;
explain extended
select count(*)
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;
select count(*)
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;
explain extended
select count(*)
from t1
join t1 as t2 on t2.a = t1.c and t2.b = t1.d
join t1 as t3 on t3.a = t2.c;
select count(*)
from t1
join t1 as t2 on t2.a = t1.c and t2.b = t1.d
join t1 as t3 on t3.a = t2.c;
connection ddl;
alter table t1 partition by key(a);
connection spj;
explain extended
select count(*)
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;
select count(*)
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;
connection ddl;
drop table t1;
# Pushed join accessing disk data.
connection ddl;
create logfile group lg1
add undofile 'undofile.dat'
initial_size 1m
undo_buffer_size = 1m
engine=ndb;
create tablespace ts1
add datafile 'datafile.dat'
use logfile group lg1
initial_size 6m
engine ndb;
create table t1 (a int not null,
b int not null storage disk,
c int not null storage memory,
primary key(a))
tablespace ts1 storage disk engine = ndb;
connection spj;
insert into t1 values (10, 11, 11);
insert into t1 values (11, 12, 12);
insert into t1 values (12, 13, 13);
connection ddl;
create table t2 (a int not null,
b int not null, primary key(a)) engine = ndb;
connection spj;
insert into t2 values (10, 11);
insert into t2 values (11, 12);
insert into t2 values (12, 13);
# Disk data in projection of first op.
explain extended select * from t1, t2 where t1.c = t2.a;
--sorted_result
select * from t1, t2 where t1.c = t2.a;
explain extended select * from t1, t2 where t1.a=11 and t1.c = t2.a;
select * from t1, t2 where t1.a=11 and t1.c = t2.a;
# Disk data in projection of second op.
explain extended select * from t2, t1 where t2.b = t1.a;
--sorted_result
select * from t2, t1 where t2.b = t1.a;
explain extended select * from t2, t1 where t2.a=11 and t2.b = t1.a;
select * from t2, t1 where t2.a=11 and t2.b = t1.a;
# Disk data in predicate but not in projection
explain extended select t1.a, t1.c, t2.a, t2.b from t1, t2 where t1.b = t2.a;
--sorted_result
select t1.a, t1.c, t2.a, t2.b from t1, t2 where t1.b = t2.a;
explain extended select t1.a, t1.c, t2.a, t2.b from t1, t2 where t1.a=11 and t1.b = t2.a;
select t1.a, t1.c, t2.a, t2.b from t1, t2 where t1.a=11 and t1.b = t2.a;
connection ddl;
drop table t1;
drop table t2;
alter tablespace ts1
drop datafile 'datafile.dat'
engine ndb;
drop tablespace ts1
engine ndb;
drop logfile group lg1
engine ndb;
connection spj;
# Store old counter values.
connection ddl;
create temporary table old_count
select counter_name, sum(val) as val
from ndbinfo.counters
where block_name='DBSPJ'
group by counter_name;
connection ddl;
create table t1 (a int not null,
b int not null,
c int not null,
primary key(a))
engine = ndb;
connection spj;
# We use key values that have the same representation in little and big endian.
# Otherwise, the numbers for local and remote reads may depend on endian-ness,
# since hashing is endian dependent.
insert into t1 values (1, 2, 2);
insert into t1 values (2, 3, 3);
insert into t1 values (3, 4, 4);
# Run some queries that should increment the counters.
select * from t1 t1, t1 t2 where t1.a = 2 and t2.a = t1.b;
select count(*) from t1 t1, t1 t2 where t2.a = t1.b;
select count(*) from t1 t1, t1 t2 where t1.a >= 2 and t2.a = t1.b;
# Get new counter values.
connection ddl;
create temporary table new_count
select counter_name, sum(val) as val
from ndbinfo.counters
where block_name='DBSPJ'
group by counter_name;
# Compute the difference.
--sorted_result
select new_count.counter_name, new_count.val - old_count.val
from new_count, old_count
where new_count.counter_name = old_count.counter_name
and new_count.counter_name <> 'LOCAL_READS_SENT'
and new_count.counter_name <> 'REMOTE_READS_SENT';
select 'READS_SENT', sum(new_count.val - old_count.val)
from new_count, old_count
where new_count.counter_name = old_count.counter_name
and (new_count.counter_name = 'LOCAL_READS_SENT'
or new_count.counter_name = 'REMOTE_READS_SENT');
connection ddl;
drop table old_count;
drop table new_count;
drop table t1;
### Test that scan filters are used for pushed operations.
connection ddl;
create table t1 (
a int primary key,
b int,
c int) engine = ndb;
connection spj;
insert into t1 values (1, 2, 3);
insert into t1 values (2, 3, 4);
insert into t1 values (3, 4, 5);
# Find the total number of lookups issued by the SPJ blocks.
let $spj_lookups = query_get_value(select sum(val) as Value from ndbinfo.counters where block_name='DBSPJ' and (counter_name='LOCAL_READS_SENT' or counter_name='REMOTE_READS_SENT'), Value, 1);
# Root scan should give only one tuple if scan filter is pushed.
# Therefore only one lookup on 'y'.
explain extended select * from t1 x, t1 y where x.b=y.a and x.c=4;
select * from t1 x, t1 y where x.b=y.a and x.c=4;
--disable_query_log
--eval select sum(val) - $spj_lookups as lookups from ndbinfo.counters where block_name='DBSPJ' and (counter_name='LOCAL_READS_SENT' or counter_name='REMOTE_READS_SENT')
--enable_query_log
# Lookup on y should only give one result tuple if filter is pushed.
# This should give 3 lookups on 'y' and 1 on 'z', 4 in all.
explain extended select * from t1 x, t1 y, t1 z where x.b=y.a and y.c=4 and y.b=z.a;
select * from t1 x, t1 y, t1 z where x.b=y.a and y.c=4 and y.b=z.a;
--disable_query_log
--eval select sum(val) - $spj_lookups as lookups from ndbinfo.counters where block_name='DBSPJ' and (counter_name='LOCAL_READS_SENT' or counter_name='REMOTE_READS_SENT')
--enable_query_log
connection ddl;
drop table t1;
# Test and server status variables (i.e. mysqld counters)
connection ddl;
create table t1(
a int not null,
b int not null,
c int not null,
primary key(a,b))
engine = ndb partition by key (a);
connection spj;
insert into t1 values (10, 10, 11);
insert into t1 values (11, 11, 12);
insert into t1 values (12, 12, 13);
# First query against a new table causes an extra scan (of a dictionaty table??)
# so adding an extra scan here to make results from the following part easier
# to interpret.
select * from t1 t1, t1 t2
where t1.a = 10 and t1.b = 10 and
t2.a = t1.c and t2.b = t1.c;
# Save old counter values.
let $old_scan_count = query_get_value(show status like 'Ndb_scan_count', Value, 1);
let $old_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
let $old_sorted_scan_count = query_get_value(show status like 'Ndb_sorted_scan_count', Value, 1);
let $old_pushed_queries_defined = query_get_value(show status like 'Ndb_pushed_queries_defined', Value, 1);
let $old_pushed_queries_dropped = query_get_value(show status like 'Ndb_pushed_queries_dropped', Value, 1);
let $old_pushed_queries_executed = query_get_value(show status like 'Ndb_pushed_queries_executed', Value, 1);
let $old_pushed_reads = query_get_value(show status like 'Ndb_pushed_reads', Value, 1);
# Run some queries that should increment the counters.
# This query should push a single read.
select * from t1 t1, t1 t2
where t1.a = 11 and t1.b = 11 and
t2.a = t1.c and t2.b = t1.c;
# This query should push a sorted scan (and three reads).
select * from t1 t1, t1 t2
where t2.a = t1.c and t2.b = t1.c
order by t1.a;
# This query should push a pruned scan (but pruning must be fixed for
# pushed scans.)
select count(*) from t1 t1, t1 t2
where t1.a = 11 and
t2.a = t1.c and t2.b = t1.c;
# Get the new values;
let $new_scan_count = query_get_value(show status like 'Ndb_scan_count', Value, 1);
let $new_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
let $new_sorted_scan_count = query_get_value(show status like 'Ndb_sorted_scan_count', Value, 1);
let $new_pushed_queries_defined = query_get_value(show status like 'Ndb_pushed_queries_defined', Value, 1);
let $new_pushed_queries_dropped = query_get_value(show status like 'Ndb_pushed_queries_dropped', Value, 1);
let $new_pushed_queries_executed = query_get_value(show status like 'Ndb_pushed_queries_executed', Value, 1);
let $new_pushed_reads = query_get_value(show status like 'Ndb_pushed_reads', Value, 1);
# Calculate the change.
--disable_query_log
--eval select $new_scan_count - $old_scan_count as scan_count
--eval select $new_pruned_scan_count - $old_pruned_scan_count as pruned_scan_count
--eval select $new_sorted_scan_count - $old_sorted_scan_count as sorted_scan_count
--eval select $new_pushed_queries_defined - $old_pushed_queries_defined as pushed_queries_defined
--eval select $new_pushed_queries_dropped - $old_pushed_queries_dropped as pushed_queries_dropped
--eval select $new_pushed_queries_executed - $old_pushed_queries_executed as pushed_queries_executed
--eval select $new_pushed_reads - $old_pushed_reads as pushed_reads
--enable_query_log
connection ddl;
drop table t1;
# Test scan pruning
connection ddl;
create table t1(
d int not null,
c int not null,
a int not null,
b int not null,
primary key using hash (a,b))
engine = ndb partition by key (a);
connection spj;
insert into t1(a,b,c,d) values (10, 10, 11, 11);
insert into t1(a,b,c,d) values (11, 11, 12, 12);
insert into t1(a,b,c,d) values (12, 12, 13, 13);
let $old_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
# Should give pruned scan.
connection ddl;
create index i1 on t1(c,a);
connection spj;
explain extended select count(*) from t1 t1, t1 t2 where t1.c = 12 and t1.a = 11 and t2.a = t1.d and t2.b = t1.d;
select count(*) from t1 t1, t1 t2 where t1.c = 12 and t1.a = 11 and t2.a = t1.d and t2.b = t1.d;
connection ddl;
drop index i1 on t1;
connection spj;
--disable_query_log
let $new_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
--eval select $new_pruned_scan_count - $old_pruned_scan_count as pruned_scan_count
let $old_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
--enable_query_log
# Should give pruned scan. There is a one sided limit for t1.b, but this is
# after the partition key prefix.
connection ddl;
create index i2 on t1(a,b);
connection spj;
explain extended select count(*) from t1 t1, t1 t2 where t1.a = 11 and t1.b<13 and t2.a = t1.c and t2.b = t1.c;
select count(*) from t1 t1, t1 t2 where t1.a = 11 and t1.b<13 and t2.a = t1.c and t2.b = t1.c;
--disable_query_log
let $new_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
--eval select $new_pruned_scan_count - $old_pruned_scan_count as pruned_scan_count
let $old_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
--enable_query_log
# Should give pruned scan. Upper and lower bounds for t1.a are the sane.
explain extended select count(*) from t1 t1, t1 t2 where t1.a >= 12 and t1.a<=12 and t2.a = t1.c and t2.b = t1.c;
select count(*) from t1 t1, t1 t2 where t1.a >= 12 and t1.a<=12 and t2.a = t1.c and t2.b = t1.c;
--disable_query_log
let $new_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
--eval select $new_pruned_scan_count - $old_pruned_scan_count as pruned_scan_count
let $old_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
--enable_query_log
# Should not give pruned scan. Upper and lower bounds for t1.a are different.
explain extended select count(*) from t1 t1, t1 t2 where t1.a >= 11 and t1.a<=12 and t2.a = t1.c and t2.b = t1.c;
select count(*) from t1 t1, t1 t2 where t1.a >= 11 and t1.a<=12 and t2.a = t1.c and t2.b = t1.c;
--disable_query_log
let $new_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
--eval select $new_pruned_scan_count - $old_pruned_scan_count as pruned_scan_count
let $old_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
--enable_query_log
# Should not give pruned scan. There will be two sets of bounds that have
# different distribution keys (t1.a=10 and t1.a=12).
explain extended select count(*) from t1 t1, t1 t2 where (t1.a = 10 or t1.a=12) and t1.b<13 and t2.a = t1.c and t2.b = t1.c;
select count(*) from t1 t1, t1 t2 where (t1.a = 10 or t1.a=12) and t1.b<13 and t2.a = t1.c and t2.b = t1.c;
--disable_query_log
let $new_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
--eval select $new_pruned_scan_count - $old_pruned_scan_count as pruned_scan_count
let $old_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
--enable_query_log
# Should give pruned scan. There will be two sets of bounds, but they have the
# same distribution key.
explain extended select count(*) from t1 t1, t1 t2 where t1.a = 10 and (t1.b<11 or t1.b>11) and t2.a = t1.c and t2.b = t1.c;
select count(*) from t1 t1, t1 t2 where t1.a = 10 and (t1.b<11 or t1.b>11) and t2.a = t1.c and t2.b = t1.c;
--disable_query_log
let $new_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
--eval select $new_pruned_scan_count - $old_pruned_scan_count as pruned_scan_count
let $old_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
--enable_query_log
connection ddl;
drop table t1;
create table t2(
d int not null,
e int not null,
f int not null,
a int not null,
b int not null,
c int not null,
primary key using hash (a,b,c))
engine = ndb partition by key (b,a);
connection spj;
insert into t2(a,b,c,d,e,f) values (1, 2, 3, 1, 2, 3);
insert into t2(a,b,c,d,e,f) values (1, 2, 4, 1, 2, 3);
insert into t2(a,b,c,d,e,f) values (2, 3, 4, 1, 2, 3);
insert into t2(a,b,c,d,e,f) values (3, 4, 5, 1, 2, 3);
insert into t2(a,b,c,d,e,f) values (4, 5, 6, 1, 2, 3);
insert into t2(a,b,c,d,e,f) values (5, 6, 7, 1, 2, 3);
insert into t2(a,b,c,d,e,f) values (6, 7, 8, 1, 2, 3);
insert into t2(a,b,c,d,e,f) values (7, 8, 9, 1, 2, 3);
connection ddl;
create index i2_1 on t2(d, a, b, e);
connection spj;
# Should give pruned scan. The index prefix containing the distribution key
# has a single possible value.
explain extended select count(*) from t2 x, t2 y where x.d=1 and x.a=1 and x.b=2 and y.a=x.d and y.b=x.e and y.c=3;
select count(*) from t2 x, t2 y where x.d=1 and x.a=1 and x.b=2 and y.a=x.d and y.b=x.e and y.c=3;
connection spj;
--disable_query_log
let $new_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
--eval select $new_pruned_scan_count - $old_pruned_scan_count as pruned_scan_count
let $old_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
--enable_query_log
connection ddl;
drop index i2_1 on t2;
create index i2_3 on t2(a, d, b, e);
# Should give pruned scan. The index prefix containing the distribution key
# has a single possible value.
connection spj;
explain extended select count(*) from t2 x, t2 y where x.d=1 and x.a=1 and x.b=2 and y.a=x.d and y.b=x.e and y.c=3;
select count(*) from t2 x, t2 y where x.d=1 and x.a=1 and x.b=2 and y.a=x.d and y.b=x.e and y.c=3;
--disable_query_log
let $new_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
--eval select $new_pruned_scan_count - $old_pruned_scan_count as pruned_scan_count
--enable_query_log
connection ddl;
drop table t2;
connection ddl;
create table t1 (a binary(10) primary key, b binary(10) not null) engine = ndb;
connection spj;
insert into t1 values ('\0123456789', '1234567890');
insert into t1 values ('1234567890', '\0123456789');
explain extended
select count(*)
from t1 join t1 as t2 on t2.a = t1.b
where t1.a = '\0123456789';
select count(*)
from t1 join t1 as t2 on t2.a = t1.b
where t1.a = '\0123456789';
connection ddl;
drop table t1;
# Tests for some bugfixes which have been cherry picked from 5.1 main
# Not necessarily test of SPJ functionality, but may test optimizer
# behaviour which we depend on when doing RQG testing
# We can remove these testcases when fixes - and propper MTR testcases -
# Have been merged from 5.1 main branch.
connection ddl;
create table t1 (pk int primary key, a int unique key) engine = ndb;
connection spj;
insert into t1 values (1,10), (2,20), (3,30);
set ndb_join_pushdown = false;
# Bug#53334:
# Join should be optimized as 'Impossible On condition'
# ... *not* 'Impossible WHERE'
explain extended
select * from t1 as x right join t1 as y
on x.pk = y.pk
and x.pk = y.a
and x.a = y.pk
where y.pk = 2;
select * from t1 as x right join t1 as y
on x.pk = y.pk
and x.pk = y.a
and x.a = y.pk
where y.pk = 2;
set ndb_join_pushdown = true;
explain extended
select * from t1 as x right join t1 as y
on x.pk = y.pk
and x.pk = y.a
and x.a = y.pk
where y.pk = 2;
select * from t1 as x right join t1 as y
on x.pk = y.pk
and x.pk = y.a
and x.a = y.pk
where y.pk = 2;
connection ddl;
drop table t1;
#########################################
# Test section for scan-child operations
#########################################
# Test scan-lookup-scan query (see http://lists.mysql.com/commits/115164)
connection ddl;
create table t1 (pk int primary key, u int not null, a int, b int) engine=ndb;
create index ix1 on t1(b,a);
connection spj;
insert into t1 values (0,1,10,20);
insert into t1 values (1,2,20,30);
insert into t1 values (2,3,30,40);
explain extended select * from t1 as x join t1 as y join t1 as z on x.u=y.pk and y.a=z.b;
--sorted_result
select * from t1 as x join t1 as y join t1 as z on x.u=y.pk and y.a=z.b;
connection ddl;
drop table t1;
# Test sorted scan where inner join eliminates all rows (known regression).
connection ddl;
create table t1 (pk int primary key, u int not null) engine=ndb;
connection spj;
insert into t1 values (0,-1), (1,-1), (2,-1), (3,-1), (4,-1), (5,-1), (6,-1),
(7,-1), (8,-1), (9,-1), (10,-1), (11,-1), (12,-1), (13,-1), (14,-1), (15,-1),
(16,-1), (17,-1), (18,-1), (19,-1), (20,-1), (21,-1), (22,-1), (23,-1),
(24,-1), (25,-1), (26,-1), (27,-1), (28,-1), (29,-1), (30,-1), (31,-1),
(32,-1), (33,-1), (34,-1), (35,-1), (36,-1), (37,-1), (38,-1), (39,-1),
(40,-1), (41,-1), (42,-1), (43,-1), (44,-1), (45,-1), (46,-1), (47,-1),
(48,-1), (49,-1), (50,-1), (51,-1), (52,-1), (53,-1), (54,-1), (55,-1),
(56,-1), (57,-1), (58,-1), (59,-1), (60,-1), (61,-1), (62,-1), (63,-1),
(64,-1), (65,-1), (66,-1), (67,-1), (68,-1), (69,-1), (70,-1), (71,-1),
(72,-1), (73,-1), (74,-1), (75,-1), (76,-1), (77,-1), (78,-1), (79,-1),
(80,-1), (81,-1), (82,-1), (83,-1), (84,-1), (85,-1), (86,-1), (87,-1),
(88,-1), (89,-1), (90,-1), (91,-1), (92,-1), (93,-1), (94,-1), (95,-1),
(96,-1), (97,-1), (98,-1), (99,-1), (100,-1), (101,-1), (102,-1), (103,-1),
(104,-1), (105,-1), (106,-1), (107,-1), (108,-1), (109,-1), (110,-1),
(111,-1), (112,-1), (113,-1), (114,-1), (115,-1), (116,-1), (117,-1),
(118,-1), (119,-1), (120,-1), (121,-1), (122,-1), (123,-1), (124,-1),
(125,-1), (126,-1), (127,-1), (128,-1), (129,-1), (130,-1), (131,-1),
(132,-1), (133,-1), (134,-1), (135,-1), (136,-1), (137,-1), (138,-1), (139,-1);
explain extended select * from t1 as x join t1 as y on x.u=y.pk order by(x.pk);
select * from t1 as x join t1 as y on x.u=y.pk order by(x.pk);
connection ddl;
drop table t1;
# Test query using "scan -> unique index lookup -> index scan".
connection ddl;
create table t1 (pk int primary key, u int not null, a int, b int) engine=ndb;
create index ix1 on t1(b,a);
create unique index ix2 on t1(u);
connection spj;
insert into t1 values (0,0,10,10);
insert into t1 values (1,1,10,10);
insert into t1 values (2,2,10,10);
insert into t1 values (3,3,10,10);
insert into t1 values (4,4,10,10);
insert into t1 values (5,5,10,10);
insert into t1 values (6,6,10,10);
insert into t1 values (7,7,10,10);
insert into t1 values (8,8,10,10);
insert into t1 values (9,9,10,10);
insert into t1 values (10,10,10,10);
insert into t1 values (11,11,10,10);
explain extended select count(*) from t1 as x1 join t1 as x2 join t1 as x3
on x1.a=x2.u and x2.a = x3.b;
select count(*) from t1 as x1 join t1 as x2 join t1 as x3
on x1.a=x2.u and x2.a = x3.b;
explain extended select count(*) from t1 as x1, t1 as x2, t1 as x3
where x1.u=x2.pk and x1.a=x3.b;
select count(*) from t1 as x1, t1 as x2, t1 as x3
where x1.u=x2.pk and x1.a=x3.b;
# Regression test for commit http://lists.mysql.com/commits/116372
# (missing rows in left join query with multiple result batches).
insert into t1 values (12,12,20,10);
explain extended select count(*) from t1 as x1 left join t1 as x2 on x1.a=x2.b;
select count(*) from t1 as x1 left join t1 as x2 on x1.a=x2.b;
set ndb_join_pushdown=off;
select count(*) from t1 as x1 left join t1 as x2 on x1.a=x2.b;
set ndb_join_pushdown=on;
# Test left join with mix of scan and lookup.
explain extended select count(*) from t1 as x1
left join t1 as x2 on x1.u=x2.pk
left join t1 as x3 on x2.a=x3.b;
select count(*) from t1 as x1
left join t1 as x2 on x1.u=x2.pk
left join t1 as x3 on x2.a=x3.b;
set ndb_join_pushdown=off;
select count(*) from t1 as x1
left join t1 as x2 on x1.u=x2.pk
left join t1 as x3 on x2.a=x3.b;
set ndb_join_pushdown=on;
explain extended select count(*) from t1 as x1
left join t1 as x2 on x1.u=x2.pk
left join t1 as x3 on x2.a=x3.b
left join t1 as x4 on x3.u=x4.pk
left join t1 as x5 on x4.a=x5.b;
select count(*) from t1 as x1
left join t1 as x2 on x1.u=x2.pk
left join t1 as x3 on x2.a=x3.b
left join t1 as x4 on x3.u=x4.pk
left join t1 as x5 on x4.a=x5.b;
set ndb_join_pushdown=off;
select count(*) from t1 as x1
left join t1 as x2 on x1.u=x2.pk
left join t1 as x3 on x2.a=x3.b
left join t1 as x4 on x3.u=x4.pk
left join t1 as x5 on x4.a=x5.b;
set ndb_join_pushdown=on;
############################
# Testcase for 'Got error 20002 'Unknown error code' from NDBCLUSTER'
# Caused by failure to identify AT_MULTI_PRIMARY_KEY as a lookup operation.
# This in turn caused a pushed 'lookup-scan' query to be produced - which we don't support
# Should not be pushed (lookup-scan query)
explain extended select count(*) from t1 as x1
join t1 as x2 on x1.a=x2.b
where x1.pk = 1 or x1.u=1;
select count(*) from t1 as x1
join t1 as x2 on x1.a=x2.b
where x1.pk = 1 or x1.u=1;
############################
# Testcase which forced us to ditch using the 'global cursor'
# on the NdbQuery result set from mysqld.
#
# As the global cursor will fool mysqld into handling the resultset
# as a result from a scan - n*lookup query, incorrect cardinality on the
# parent operation was perceived. Which caused extra null-joined outer rows
# to be emitted in this testcase.
#
# Refactored handler interface and SPJ API use subcursor on each operation
# which correctly preserves the dependency between the parent subscans
# and its child(s).
set ndb_join_pushdown=on;
explain extended
select straight_join * from t1 as table1
left join
(t1 as table2 join t1 as table3 on table2.pk = table3.b)
on table1.pk = table2.b;
--sorted_result
select straight_join * from t1 as table1
left join
(t1 as table2 join t1 as table3 on table2.pk = table3.b)
on table1.pk = table2.b;
#############
# Testcase for 'sledgehammer' fix for scan -> outer join scan:
# Pushing of outer joined has to be dissabled as incomplete child batches
# may cause the parent row to be returned multiple times:
# Push scan-scan when inner joined
explain extended select straight_join * from t1 as x1
inner join t1 as x2 on x2.b = x1.a;
# Outer joined scans are not pushed.
explain extended select straight_join * from t1 as x1
left join t1 as x2 on x2.b = x1.a;
explain extended select straight_join * from t1 as x1
right join t1 as x2 on x2.b = x1.a;
# If there is a lookup operation(s) inbetween the scans
# pushing is disabled if any of these are outer joined
# inner joined lookups, push allowed
explain extended select straight_join * from
t1 as x1 inner join
(t1 as x2 inner join t1 as x3 on x3.b = x2.a)
on x2.pk = x1.a;
# Even if x3 is inner joined with x2 (lookup)
# push is dissabled as x2 is outer joined with embedding scan operation
# which makes join relation between the scans on x1 & x3 an 'indirect' outer join
explain extended select straight_join * from
t1 as x1 left join
(t1 as x2 inner join t1 as x3 on x3.b = x2.a)
on x2.pk = x1.a;
#############
# Test bushy-scans:
# These should be allowed to be executed in 'parallel', depending on
# only the root operation
#
eval $save_scan_rows_returned;
explain extended select straight_join count(*) from t1 as x1
join t1 as x2 on x2.b = x1.a
join t1 as x3 on x3.b = x1.b;
set ndb_join_pushdown=off;
select straight_join count(*) from t1 as x1
join t1 as x2 on x2.b = x1.a
join t1 as x3 on x3.b = x1.b;
set ndb_join_pushdown=on;
select straight_join count(*) from t1 as x1
join t1 as x2 on x2.b = x1.a
join t1 as x3 on x3.b = x1.b;
# A really bushy scan - would take almost forever to execute if
# we had to force the child scan to be non-bushy (serialized by
# adding artificial parent dependencies)
#
explain extended select straight_join count(*) from t1 as x1
join t1 as x2 on x2.b = x1.a
join t1 as x3 on x3.b = x1.a
join t1 as x4 on x4.b = x1.a
join t1 as x5 on x5.b = x1.a
join t1 as x6 on x6.b = x1.a
join t1 as x7 on x7.b = x1.a
where x3.a < x2.pk and x4.a < x3.pk;
select straight_join count(*) from t1 as x1
join t1 as x2 on x2.b = x1.a
join t1 as x3 on x3.b = x1.a
join t1 as x4 on x4.b = x1.a
join t1 as x5 on x5.b = x1.a
join t1 as x6 on x6.b = x1.a
join t1 as x7 on x7.b = x1.a
where x3.a < x2.pk and x4.a < x3.pk;
eval $compensate_scan_rows_returned;
#############
# If we have an outer join, we can't create an artificial dep. 'through' the outer join.
# In this case the child scan can't be part of the pushed query.
#
explain extended select straight_join count(*) from t1 as x1
left join t1 as x2 on x2.b = x1.a
join t1 as x3 on x3.b = x1.b;
set ndb_join_pushdown=off;
select straight_join count(*) from t1 as x1
left join t1 as x2 on x2.b = x1.a
join t1 as x3 on x3.b = x1.b;
set ndb_join_pushdown=on;
select straight_join count(*) from t1 as x1
left join t1 as x2 on x2.b = x1.a
join t1 as x3 on x3.b = x1.b;
############
# However, When the scanchild itself is an outer join, we *can* push that scan operation
#
explain extended select straight_join count(*) from t1 as x1
join t1 as x2 on x2.b = x1.a
left join t1 as x3 on x3.b = x1.b;
set ndb_join_pushdown=off;
select straight_join count(*) from t1 as x1
join t1 as x2 on x2.b = x1.a
left join t1 as x3 on x3.b = x1.b;
set ndb_join_pushdown=on;
select straight_join count(*) from t1 as x1
join t1 as x2 on x2.b = x1.a
left join t1 as x3 on x3.b = x1.b;
##############
# If we have a bushy lookup, with scandescendants depending on these lookups,
# the query is 'scan-bushy' through these lookups.
#
# Bushy execution is expected for these scans (x2 & x4) wrt. root (x1)
#
eval $save_scan_rows_returned;
explain extended
select straight_join count(*) from t1 as x1
join t1 as x2 on x2.b = x1.a
join t1 as x3 on x3.pk = x1.a join t1 as x4 on x4.b = x3.a;
set ndb_join_pushdown=off;
select straight_join count(*) from t1 as x1
join t1 as x2 on x2.b = x1.a
join t1 as x3 on x3.pk = x1.a join t1 as x4 on x4.b = x3.a;
set ndb_join_pushdown=on;
select straight_join count(*) from t1 as x1
join t1 as x2 on x2.b = x1.a
join t1 as x3 on x3.pk = x1.a join t1 as x4 on x4.b = x3.a;
eval $compensate_scan_rows_returned;
#############
# Test bushy lookups + 1scan,
# (Regression test for previous commit: http://lists.mysql.com/commits/117571)
# Repeatable child rangescan with same parent should be allowed to be in
# 'm_iterState != Iter_finished' if the child row didn't exist (outer join):
explain extended select straight_join count(*) from t1 as x1
left join t1 as x3 on x3.b = x1.a
join t1 as x2 on x2.pk = x1.a;
select straight_join count(*) from t1 as x1
left join t1 as x3 on x3.b = x1.a
join t1 as x2 on x2.pk = x1.a;
# Modify rows to force null rows from outer join
update t1 set b=b+10;
select straight_join count(*) from t1 as x1
left join t1 as x3 on x3.b = x1.a
join t1 as x2 on x2.pk = x1.a;
#Undo update
update t1 set b=b-10;
##############
# Testcase for: http://lists.mysql.com/commits/118917
# There used to be a bug in SPJ API resulthandling of incomplete
# child batches where we tested for incomplete fetch for any
# childs (in a bushy scan) instead if this particular child batch
# being incomplete.
#
# In this testcase (x inner join y) will have incomplete
# childbatches, while (x left join z) will be complete.
# Modify rows to force null rows from lookup(z) below
update t1 set u=u+100;
set ndb_join_pushdown=on;
explain extended select straight_join count(*) from
(t1 as x join t1 as y on y.b = x.a)
left outer join t1 as z on z.u = x.a;
select straight_join count(*) from
(t1 as x join t1 as y on y.b = x.a)
left outer join t1 as z on z.u = x.a;
#Undo update
update t1 set u=u-100;
##############
connection ddl;
drop index ix2 on t1;
create unique index ix2 on t1(a,u);
connection spj;
set ndb_join_pushdown=on;
explain extended
select straight_join * from
t1 as table1 join
(t1 as table2 join t1 as table3 on table3.a = table2.a)
on table3.u = table1.u
where table2.pk = 3;
--sorted_result
select straight_join * from
t1 as table1 join
(t1 as table2 join t1 as table3 on table3.a = table2.a)
on table3.u = table1.u
where table2.pk = 3;
##############
connection ddl;
drop table t1;
##############
# Test that branches of a bushy scan are correctly reset.
connection ddl;
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;
connection spj;
insert into t1 values (1,1,1,1), (1,2,1,1), (1,3,1,1), (1,4,1,2);
connection ddl;
CREATE TABLE t2 (
a int NOT NULL,
PRIMARY KEY (`a`)
) ENGINE=ndbcluster;
CREATE TABLE t3 (
a int NOT NULL,
b int NOT NULL,
PRIMARY KEY (`a`,`b`)
) ENGINE=ndbcluster;
connection ddl;
insert into t2 values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
connection spj;
# Make t3 so big that it takes multiple batches to scan it.
insert into t3 select 1, x1.a * 10+x2.a from t2 as x1 cross join t2 as x2;
explain select straight_join count(*) from t1 as x0
join t3 as x1 on x0.c=x1.a
join t1 as x2 on x0.c=x2.a
join t3 as x3 on x2.c=x3.a
join t1 as x4 on x0.d=x4.a and x3.b=x4.b;
eval $save_scan_rows_returned;
select straight_join count(*) from t1 as x0
join t3 as x1 on x0.c=x1.a
join t1 as x2 on x0.c=x2.a
join t3 as x3 on x2.c=x3.a
join t1 as x4 on x0.d=x4.a and x3.b=x4.b;
eval $compensate_scan_rows_returned;
connection ddl;
drop table t1;
drop table t2;
drop table t3;
#############################################
# Test pruned index scan:
connection ddl;
create table t1(
d int not null,
e int null,
f int null,
a int not null,
b int not null,
c int not null,
primary key (a,b,c))
engine = ndb partition by key (b);
connection spj;
insert into t1(a,b,c,d,e,f) values
(1, 2, 3, 1, 2, 3),
(1, 2, 4, 1, 2, 3),
(2, 3, 4, 1, 2, 3),
(3, 4, 5, 1, 2, 3),
(4, 5, 6, 1, 2, 3),
(5, 6, 7, 1, 2, 3),
(6, 7, 8, 1, 2, 3),
(7, 8, 9, 1, 2, 3);
# Find the total number of pruned range scans so far
let $pruned_range = query_get_value(select sum(val) as Value from ndbinfo.counters where block_name='DBSPJ' and counter_name='PRUNED_RANGE_SCANS_RECEIVED', Value, 1);
let $const_pruned_range = query_get_value(select sum(val) as Value from ndbinfo.counters where block_name='DBSPJ' and counter_name='CONST_PRUNED_RANGE_SCANS_RECEIVED', Value, 1);
set ndb_join_pushdown=on;
explain extended
select straight_join * from t1 x, t1 y where y.a=x.d and y.b=x.e;
--sorted_result
select straight_join * from t1 x, t1 y where y.a=x.d and y.b=x.e;
connection ddl;
alter table t1 partition by key (a);
connection spj;
--sorted_result
select straight_join * from t1 x, t1 y where y.a=x.d and y.b=x.e;
connection ddl;
alter table t1 partition by key (a,b);
connection spj;
--sorted_result
select straight_join * from t1 x, t1 y where y.a=x.d and y.b=x.e;
connection ddl;
alter table t1 partition by key (b,a);
connection spj;
--sorted_result
select straight_join * from t1 x, t1 y where y.a=x.d and y.b=x.e;
#########
# const pruned testcase
#########
connection ddl;
alter table t1 partition by key (b);
connection spj;
--sorted_result
select straight_join * from t1 x, t1 y where y.a=x.d and y.b=2;
connection ddl;
alter table t1 partition by key (a);
connection spj;
--sorted_result
select straight_join * from t1 x, t1 y where y.a=1 and y.b=x.e;
select straight_join * from t1 x, t1 y where y.a=0 and y.b=x.e;
# Non-const pruned as both partition keys are not const
connection ddl;
alter table t1 partition by key (a,b);
connection spj;
--sorted_result
select straight_join * from t1 x, t1 y where y.a=1 and y.b=x.e;
--sorted_result
select straight_join * from t1 x, t1 y where y.a=x.d and y.b=2;
##########
# Test pruned scan using an index:
# Declaring PK as 'using hash' will prevent that PK is used as index
# Declare PK / ix1 with mismatching column order will test correct
# usage of NdbRecord::distkey_indexes[]
##########
connection ddl;
alter table t1 drop primary key, add primary key using hash (d,b,a,c);
alter table t1 partition by key (b);
create index ix1 on t1(b,d,a);
connection spj;
explain extended
select straight_join * from t1 x, t1 y where y.a=x.d and y.b=x.e;
###########
# Partition keys may evaluate to null-values:
###########
insert into t1(a,b,c,d,e,f) values
(8, 9, 0, 1, null, 3),
(9, 9, 0, 1, 2, null);
connection ddl;
alter table t1 partition by key (b);
connection spj;
--sorted_result
select straight_join * from t1 x, t1 y where y.a=x.d and y.b=x.e;
# Verify pruned execution by comparing the NDB$INFO counters
--disable_query_log
--eval select sum(val) - $pruned_range as pruned from ndbinfo.counters where block_name='DBSPJ' and counter_name='PRUNED_RANGE_SCANS_RECEIVED'
--eval select sum(val) - $const_pruned_range as const_pruned from ndbinfo.counters where block_name='DBSPJ' and counter_name='CONST_PRUNED_RANGE_SCANS_RECEIVED'
--enable_query_log
connection ddl;
drop table t1;
###
# Test that sorted scan with sub scan is *not* pushed.
###
connection ddl;
create table t1 (pk int primary key, a int, b int) engine=ndb;
create index ix1 on t1(b,a);
connection spj;
insert into t1 values (0,10,10);
insert into t1 values (1,10,20);
insert into t1 values (2,20,20);
insert into t1 values (3,10,10);
insert into t1 values (4,10,20);
insert into t1 values (5,10,20);
insert into t1 values (6,10,10);
insert into t1 values (7,10,10);
insert into t1 values (8,10,20);
insert into t1 values (9,10,10);
# Results would be sorted wrongly if pushed.
explain extended select x1.pk,x1.a,x1.b from t1 as x1
join t1 as x2 on x1.a=x2.b
join t1 as x3 on x2.a=x3.b
order by x1.pk limit 70;
select x1.pk,x1.a,x1.b from t1 as x1
join t1 as x2 on x1.a=x2.b
join t1 as x3 on x2.a=x3.b
order by x1.pk limit 70;
# This query should not be pushed, since mysqld requires sorted
# results for the root scan.
explain extended select * from t1 as x1, t1 as x2 where x1.a=x2.b and x1.b = 3;
select * from t1 as x1, t1 as x2 where x1.a=x2.b and x1.b = 3;
connection ddl;
drop table t1;
########
# Test correct cleanup of MRR accesses being executed multiple times.
# This used to be bug#57481, and this is a SPJ specific testcase in addition to
# the specific testcase commited together with patch for this bug.
#######
connection ddl;
create table t (pk int primary key, a int) engine=ndb;
insert into t values
(1,1), (2,1),
(4,3), (6,3),
(7,4), (8,4);
connection spj;
explain extended
select distinct straight_join table1.pk FROM
t as table1 join
(t as table2 join
(t as table3 join t as table4 on table3.pk = table4.a)
on table2.pk = table3.pk )
on table1.a = table4.pk
where table2.pk != 6;
--sorted_result
select distinct straight_join table1.pk FROM
t as table1 join
(t as table2 join
(t as table3 join t as table4 on table3.pk = table4.a)
on table2.pk = table3.pk )
on table1.a = table4.pk
where table2.pk != 6;
connection ddl;
drop table t;
########
# SPJ variant of bug#57396
# Test correct format of an 'open bound'
########
connection ddl;
create table t (b int, a int, primary key (a,b)) engine=ndb;
connection spj;
insert into t values(0,0);
explain extended
select * from t as t1 join t as t2 on t2.a=t1.a where t1.a < 8 or t1.a >= 8;
select * from t as t1 join t as t2 on t2.a=t1.a where t1.a < 8 or t1.a >= 8;
connection ddl;
drop table t;
#######
# Testcase for bug introduced by initial fix for
# bug#57601 'Optimizer is overly eager to request ordered access.'
# When we turned of 'sorted' for 'descending', we broke QUICK_SELECT_DESC
# which required result to be read as an ordered index access
#######
connection ddl;
create table t (pk1 int, pk2 int, primary key(pk1,pk2)) engine = ndb;
connection spj;
insert into t values (1,3), (3,6), (6,9), (9,1);
explain extended
select * from t as t1 join t as t2
on t1.pk2 = t2.pk1
where t1.pk1 != 6
order by t1.pk1 DESC;
select * from t as t1 join t as t2
on t1.pk2 = t2.pk1
where t1.pk1 != 6
order by t1.pk1 DESC;
connection ddl;
drop table t;
#######
# Testcase using 'REF_OR_NULL'
# 'ref_or_null' contains elements of left outer join wo/ being identical.
#
connection ddl;
create table t (k int, uq int, unique key ix1 (uq)) engine = ndb;
connection spj;
insert into t values (1,3), (3,NULL), (6,9), (9,1);
# Currently we do not handle 'ref_or_null' correctly.
# It is therefore disabled as pushable
explain extended
select straight_join * from t as a join t as b
on a.uq=b.uq or b.uq is null;
--sorted_result
select straight_join * from t as a join t as b
on a.uq=b.uq or b.uq is null;
connection ddl;
drop table t;
########
# JT_SYSTEM testcase, 'a.k is null' is known 'false' ->
# Join condition will always fail, and all 'left joins' can be NULL complemented wo/
# even requiring to access left table (b) which becomes 'system' -> No pushed joins !
########
connection ddl;
create table t (k int primary key, uq int) engine = ndb;
connection spj;
insert into t values (1,3), (3,NULL), (6,9), (9,1);
explain extended
select * from t as a left join t as b
on a.k is null and a.uq=b.uq;
--sorted_result
select * from t as a left join t as b
on a.k is null and a.uq=b.uq;
connection ddl;
drop table t;
#######
# Test of varchar query parameteres.
#######
connection ddl;
create table tc(
a varchar(10) not null,
b varchar(10),
c varchar(10),
primary key (a),
unique key uk1 (b, c)
)engine=ndbcluster;
connection spj;
insert into tc values ('aa','bb', 'x'), ('bb','cc', 'x'), ('cc', 'dd', 'x');
explain extended select * from tc as x1
right outer join tc as x2 on x1.b=x2.a
left outer join tc as x3 on x2.b = x3.b and x1.c=x3.c;
--sorted_result
select * from tc as x1
right outer join tc as x2 on x1.b=x2.a
left outer join tc as x3 on x2.b = x3.b and x1.c=x3.c;
####
# Test that 'select ... for update' is not pushed, since this requires locking.
####
explain extended select * from tc as x1, tc as x2 where x1.b=x2.a for update;
explain extended select * from tc as x1, tc as x2 where x1.b=x2.a;
connection ddl;
drop table tc;
###
# prune with xfrm set incorrect keylen
#
connection ddl;
create table t1 (
a varchar(16) not null,
b int not null,
c varchar(16) not null,
d int not null,
primary key (a,b)
) engine ndb partition by key (a);
connection spj;
insert into t1 values ('aaa', 1, 'aaa', 1);
explain extended
select * from t1 as q1, t1 as q2 where q1.a = 'aaa' and q1.c=q2.a;
select * from t1 as q1, t1 as q2 where q1.a = 'aaa' and q1.c=q2.a;
connection ddl;
drop table t1;
#######################################
# Some tests for nested left joins.
connection ddl;
CREATE TABLE t1 (
a int NOT NULL,
b int NOT NULL,
c int NOT NULL,
d int,
PRIMARY KEY (`a`,`b`),
unique key(c)
) ENGINE=ndbcluster;
connection spj;
insert into t1 values
(1,1,1,1),
(1,2,2,1),
(1,3,3,1),
(1,4,4,1),
(1,5,5,2),
(1,6,6,2),
(1,7,7,2),
(1,8,8,2);
explain extended select count(*) from t1 as x1
join (t1 as x2
left join (t1 as x3
cross join t1 as x4)
on x2.d=x3.a)
on x2.c is null or x1.a=x4.d;
select count(*) from t1 as x1
join (t1 as x2
left join (t1 as x3
cross join t1 as x4)
on x2.d=x3.a)
on x2.c is null or x1.a=x4.d;
explain extended select count(*) from t1 as x1
left join (t1 as x2
cross join t1 as x3)
on x1.d=x2.a;
select count(*) from t1 as x1
left join (t1 as x2
cross join t1 as x3)
on x1.d=x2.a;
explain extended select count(*) from t1 as x0
left join (t1 as x1
join (t1 as x2
left join (t1 as x3
join t1 as x4 on x3.d=x4.a)
on x2.d=x3.a)
on x2.c is null or x1.a=x4.d)
on x0.d=x1.a;
select count(*) from t1 as x0
left join (t1 as x1
join (t1 as x2
left join (t1 as x3
join t1 as x4 on x3.d=x4.a)
on x2.d=x3.a)
on x2.c is null or x1.a=x4.d)
on x0.d=x1.a;
connection ddl;
drop table t1;
## Test scan sorted on string field.
connection ddl;
create table t1 (pk char(10) primary key, u int not null) engine=ndb;
create table t2 (pk int primary key, u int not null) engine=ndb;
connection spj;
insert into t1 values ('wh',1);
insert into t1 values ('ik',2);
insert into t1 values ('cu',3);
insert into t1 values ('pw',4);
insert into t1 values ('cq',4);
insert into t2 values (1,2), (2,3), (3,4), (4,5);
explain select * from t1 join t2 on t1.u = t2.pk order by t1.pk;
select * from t1 join t2 on t1.u = t2.pk order by t1.pk;
connection ddl;
drop table t1;
drop table t2;
########################################
# Test query with very long records.
connection ddl;
create table t1 (
a char(10) primary key,
b char(10) not null,
c char(10) not null,
l00 char(255) not null,
l01 char(255) not null,
l02 char(255) not null,
l03 char(255) not null,
l04 char(255) not null,
l05 char(255) not null,
l06 char(255) not null,
l07 char(255) not null,
l08 char(255) not null,
l09 char(255) not null,
l10 char(255) not null,
l11 char(255) not null,
l12 char(255) not null,
l13 char(255) not null,
l14 char(255) not null,
l15 char(255) not null,
l16 char(255) not null,
l17 char(255) not null,
l18 char(255) not null,
l19 char(255) not null,
l20 char(255) not null,
l21 char(255) not null,
l22 char(255) not null,
l23 char(255) not null,
l24 char(255) not null,
l25 char(255) not null,
l26 char(255) not null,
l27 char(255) not null,
l28 char(255) not null,
l29 char(255) not null,
l30 char(255) not null,
l31 char(255) not null,
index(c, b)
) engine=ndb partition by key(a) partitions 8;
connection spj;
insert into t1 values ('a','a','a','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x');
insert into t1 values ('b','b','b','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x');
insert into t1 values ('c','c','c','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x');
explain select count(*) from t1 as x1 join t1 as x2 on x1.b = x2.c;
select count(*) from t1 as x1 join t1 as x2 on x1.b = x2.c;
connection ddl;
drop table t1;
########################################
# Verify DBSPJ counters for entire test:
# Note: These tables are 'temporary' withing 'connection spj'
# Get new counter values.
connection spj;
create temporary table spj_counts_at_end
select counter_name, sum(val) as val
from ndbinfo.counters
where block_name='DBSPJ'
group by counter_name;
connection spj;
# Compute & report the difference.
# Any change in SPJ counters will indicate a change in pushability which
# should be verifyed.
# - LOCAL/REMOTE counts can not be directly compared as they depends on
# data distribution, and which TC used for the test.
# - SCAN_BATCHES_RETURNED also seems to vary with a small delta - also assumed to be due to data distribution
--sorted_result
select spj_counts_at_end.counter_name, spj_counts_at_end.val - spj_counts_at_startup.val
from spj_counts_at_end, spj_counts_at_startup
where spj_counts_at_end.counter_name = spj_counts_at_startup.counter_name
and spj_counts_at_end.counter_name <> 'LOCAL_READS_SENT'
and spj_counts_at_end.counter_name <> 'REMOTE_READS_SENT'
and spj_counts_at_end.counter_name <> 'LOCAL_RANGE_SCANS_SENT'
and spj_counts_at_end.counter_name <> 'REMOTE_RANGE_SCANS_SENT'
and spj_counts_at_end.counter_name <> 'SCAN_BATCHES_RETURNED';
# The sum of LOCAL+REMOTE should be constant
select sum(spj_counts_at_end.val - spj_counts_at_startup.val) as 'LOCAL+REMOTE READS_SENT'
from spj_counts_at_end, spj_counts_at_startup
where spj_counts_at_end.counter_name = spj_counts_at_startup.counter_name
and (spj_counts_at_end.counter_name = 'LOCAL_READS_SENT'
or spj_counts_at_end.counter_name = 'REMOTE_READS_SENT');
connection spj;
drop table spj_save_counts;
drop table spj_counts_at_startup;
drop table spj_counts_at_end;
connection spj;
# Similar for the SPJ specific 'STATUS' counters
let $scan_count_at_end = query_get_value(show status like 'Ndb_scan_count', Value, 1);
let $pruned_scan_count_at_end = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
let $sorted_scan_count_at_end = query_get_value(show status like 'Ndb_sorted_scan_count', Value, 1);
let $pushed_queries_defined_at_end = query_get_value(show status like 'Ndb_pushed_queries_defined', Value, 1);
let $pushed_queries_dropped_at_end = query_get_value(show status like 'Ndb_pushed_queries_dropped', Value, 1);
let $pushed_queries_executed_at_end = query_get_value(show status like 'Ndb_pushed_queries_executed', Value, 1);
let $pushed_reads_at_end = query_get_value(show status like 'Ndb_pushed_reads', Value, 1);
# Calculate the change.
--disable_query_log
--eval select $scan_count_at_end - $scan_count_at_startup as scan_count
--eval select $pruned_scan_count_at_end - $pruned_scan_count_at_startup as pruned_scan_count
--eval select $sorted_scan_count_at_end - $sorted_scan_count_at_startup as sorted_scan_count
--eval select $pushed_queries_defined_at_end - $pushed_queries_defined_at_startup as pushed_queries_defined
--eval select $pushed_queries_dropped_at_end - $pushed_queries_dropped_at_startup as pushed_queries_dropped
--eval select $pushed_queries_executed_at_end - $pushed_queries_executed_at_startup as pushed_queries_executed
#--eval select $pushed_reads_at_end - $pushed_reads_at_startup as pushed_reads
--enable_query_log
--source ndbinfo_drop.inc
set ndb_join_pushdown = @save_ndb_join_pushdown;