path: root/mysql-test/main/innodb_mrr_cpk.test

# 
# Tests for DS-MRR over clustered primary key. The only engine that supports
# this is InnoDB/XtraDB.
#
# Basic idea about testing
#  - DS-MRR/CPK works only with BKA
#  - Should also test index condition pushdown
#  - Should also test whatever uses RANGE_SEQ_IF::skip_record() for filtering
#  - Also test access using prefix of primary key
# 
#  - Forget about cost model, BKA's multi_range_read_info() call passes 10 for
#    #rows, the call is there at all only for applicability check
# 
-- source include/have_innodb.inc

--disable_warnings
drop table if exists t0,t1,t2,t3;
--enable_warnings

set @innodb_mrr_cpk_tmp=@@optimizer_switch;
set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';

set @save_join_cache_level=@@join_cache_level;
set join_cache_level=6;

set @save_storage_engine=@@default_storage_engine;
set default_storage_engine=innodb;

create table t0(a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1(a char(8), b char(8), filler char(100), primary key(a));
show create table t1;

insert into t1 select 
  concat('a-', 1000 + A.a + B.a*10 + C.a*100, '=A'),
  concat('b-', 1000 + A.a + B.a*10 + C.a*100, '=B'),
  'filler'
from t0 A, t0 B, t0 C;

create table t2 (a char(8));
insert into t2 values ('a-1010=A'), ('a-1030=A'), ('a-1020=A');

--echo This should use join buffer:
explain select * from t1, t2 where t1.a=t2.a;

--echo This output must be sorted by value of t1.a:
select * from t1, t2 where t1.a=t2.a;
drop table t1, t2;

# Try multi-column indexes
create table t1(
  a char(8) character set utf8, b int, filler char(100), 
  primary key(a,b)
);

insert into t1 select 
  concat('a-', 1000 + A.a + B.a*10 + C.a*100, '=A'),
  1000 + A.a + B.a*10 + C.a*100,
  'filler'
from t0 A, t0 B, t0 C;

create table t2 (a char(8) character set utf8, b int);
insert into t2 values ('a-1010=A', 1010), ('a-1030=A', 1030), ('a-1020=A', 1020);
explain select * from t1, t2 where t1.a=t2.a and t1.b=t2.b;
select * from t1, t2 where t1.a=t2.a and t1.b=t2.b;

# Try with dataset that causes identical lookup keys:
insert into t2 values ('a-1030=A', 1030), ('a-1020=A', 1020);
explain select * from t1, t2 where t1.a=t2.a and t1.b=t2.b;
select * from t1, t2 where t1.a=t2.a and t1.b=t2.b;

drop table t1, t2;

create table t1(
  a varchar(8) character set utf8, b int, filler char(100), 
  primary key(a,b)
);

insert into t1 select 
  concat('a-', 1000 + A.a + B.a*10 + C.a*100, '=A'),
  1000 + A.a + B.a*10 + C.a*100,
  'filler'
from t0 A, t0 B, t0 C;

create table t2 (a char(8) character set utf8, b int);
insert into t2 values ('a-1010=A', 1010), ('a-1030=A', 1030), ('a-1020=A', 1020);
explain select * from t1, t2 where t1.a=t2.a and t1.b=t2.b;
select * from t1, t2 where t1.a=t2.a and t1.b=t2.b;

# 
# Try scanning on a CPK prefix
#
explain select * from t1, t2 where t1.a=t2.a;
select * from t1, t2 where t1.a=t2.a;
drop table t1, t2;

#
# The above example is not very interesting, as CPK prefix has 
# only one match.  Create a dataset where scan on CPK prefix 
# would produce multiple matches:
#
create table t1 (a int, b int, c int, filler char(100), primary key(a,b,c));
insert into t1 select A.a, B.a, C.a, 'filler' from t0 A, t0 B, t0 C;

insert into t1 values (11, 11, 11,   'filler');
insert into t1 values (11, 11, 12,   'filler');
insert into t1 values (11, 11, 13,   'filler');
insert into t1 values (11, 22, 1234, 'filler');
insert into t1 values (11, 33, 124,  'filler');
insert into t1 values (11, 33, 125,  'filler');

create table t2 (a int, b int);
insert into t2 values (11,33), (11,22), (11,11);

explain select * from t1, t2 where t1.a=t2.a and t1.b=t2.b;
select * from t1, t2 where t1.a=t2.a and t1.b=t2.b;

# Check a real resultset for comaprison:
set join_cache_level=0;
select * from t1, t2 where t1.a=t2.a and t1.b=t2.b;
set join_cache_level=6;


#
# Check that Index Condition Pushdown (BKA) actually works:
#
explain select * from t1, t2 where t1.a=t2.a and t2.b + t1.b > 100;
select * from t1, t2 where t1.a=t2.a and t2.b + t1.b > 100;

set optimizer_switch='index_condition_pushdown=off';
explain select * from t1, t2 where t1.a=t2.a and t2.b + t1.b > 100;
select * from t1, t2 where t1.a=t2.a and t2.b + t1.b > 100;
set optimizer_switch='index_condition_pushdown=on';

drop table t1,t2;

drop table t0;

--echo #
--echo # MDEV-6878: Use of uninitialized saved_primary_key in Mrr_ordered_index_reader::resume_read()
--echo #
create table t0(a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

create table t1 (
  pk varchar(32) character set utf8 primary key,
  kp1 char(32) not null,
  col1 varchar(32),
  key (kp1)
) engine=innodb;

insert into t1
select
  concat('pk-', 1000 +A.a),
  concat('kp1-', 1000 +A.a),
  concat('val-', 1000 +A.a)
from test.t0 A ;

create table t2 as select kp1 as a from t1;

set join_cache_level=8;
set optimizer_switch='mrr=on,mrr_sort_keys=on';
explain
select * from t2 straight_join t1 force index(kp1) where t1.kp1=t2.a;
select * from t2 straight_join t1 force index(kp1) where t1.kp1=t2.a;

drop table t0,t1,t2;

--echo #
--echo #
--echo # MDEV-3817: Wrong result with index_merge+index_merge_intersection, InnoDB table, join, AND and OR conditions
--echo #

set @tmp_mdev3817=@@optimizer_switch;
SET optimizer_switch='index_merge=on,index_merge_intersection=on';

CREATE TABLE t1 (
 a INT PRIMARY KEY,
 b INT,
 c VARCHAR(1024) CHARACTER SET utf8,
 d INT,
 KEY (b)
) ENGINE=InnoDB;

INSERT INTO t1 VALUES 
(1, 9, 'one', 11), (2, 6, 'two', 12), (3, 2, 'three', 13), (4, 5, 'four', 14);

CREATE TABLE t2 (e INT, g INT) ENGINE=InnoDB;
INSERT INTO t2 VALUES (1,9), (2,6) ;

SELECT * FROM t1, t2 WHERE g = b AND ( a < 7 OR a > e );

DROP TABLE t1, t2;
set optimizer_switch=@tmp_mdev3817;

--echo #
--echo # MDEV-5037: Server crash on a JOIN on a derived table with join_cache_level > 2
--echo #
create table  t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

CREATE TABLE t1 (
  id char(8) CHARACTER SET utf8 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE t2 (
  id char(8) CHARACTER SET utf8 DEFAULT NULL,
  url text CHARACTER SET utf8
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into t1 select '03b2ca8c' from t0 A, t0 B limit 80;
insert into t2 select '03b2ca8c','' from t0 A, t0 B, t0 C;

set @tmp_mdev5037=@@join_cache_level;
set join_cache_level=3;

--replace_column 9 #
explain SELECT 1 FROM (SELECT url, id FROM t2 LIMIT 1 OFFSET 20) derived RIGHT JOIN t1 ON t1.id = derived.id;

set join_cache_level= @tmp_mdev5037;
drop table t0,t1,t2;

--echo #
--echo # This must be at the end:
--echo #

set @@join_cache_level= @save_join_cache_level;
set default_storage_engine=@save_storage_engine;
set optimizer_switch=@innodb_mrr_cpk_tmp;