1#
2# ICP/InnoDB tests (Index Condition Pushdown)
3#
4
5--source include/have_innodb.inc
6--source include/default_optimizer_switch.inc
7
8set @save_storage_engine= @@default_storage_engine;
9set default_storage_engine=InnoDB;
10
11set @innodb_stats_persistent_save= @@innodb_stats_persistent;
12set @innodb_stats_persistent_sample_pages_save=
13      @@innodb_stats_persistent_sample_pages;
14
15set global innodb_stats_persistent= 1;
16set global innodb_stats_persistent_sample_pages=100;
17
18set @innodb_icp_tmp=@@optimizer_switch;
19set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
20
21--source include/icp_tests.inc
22
23--echo #
24--echo # BUG#920132: Assert trx->n_active_thrs == 1 failed at que0que.c line 1050
25--echo #
26CREATE TABLE t1 ( a INT )
27  ENGINE=InnoDB;
28INSERT INTO t1 VALUES (7),(7);
29
30CREATE TABLE t2 ( b VARCHAR(1) );
31INSERT INTO t2 VALUES ('j'),('v');
32
33CREATE TABLE t3 (
34  c INT, d VARCHAR(1), e VARCHAR(1),
35  KEY (d,c)
36) ENGINE=InnoDB;
37INSERT INTO t3
38  VALUES (6,'w','w'),
39    (1,'v','v'),(7,'s','s'),(4,'l','l'),
40    (7,'y','y'),(0,'c','c'),(2,'i','i'),
41    (9,'h','h'),(4,'q','q'),(0,'a','a'),
42    (9,'v','v'),(1,'u','u'),(3,'s','s'),
43    (8,'z','z'),(1,'h','h'),(8,'p','p'),
44    (6,'e','e'),(3,'i','i'),(6,'y','y');
45
46SELECT *
47FROM t1 INNER JOIN t2 INNER JOIN t3
48  ON d = b
49WHERE
50  NOT EXISTS ( SELECT * FROM t3 )
51  OR a = c
52ORDER BY e;
53
54DROP TABLE t1,t2,t3;
55
56--echo #
57--echo # MDEV-5337: Wrong result in mariadb 5.5.32 with ORDER BY + LIMIT when index_condition_pushdown=on
58--echo # MDEV-5512: Wrong result (WHERE clause ignored) with multiple clauses using Percona-XtraDB engine
59--echo #
60
61create table t1(a int);
62insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
63
64create table t2 (pk int primary key,
65  key1 char(32),
66  key2 char(32),
67  key(key1),
68  key(key2)
69) engine=innodb;
70
71insert into t2 select
72  A.a+10*B.a+100*C.a,
73  concat('rare-', A.a+10*B.a),
74  concat('rare-', A.a+10*B.a)
75from
76  t1 A, t1 B, t1 C;
77update t2 set key1='frequent-val' where pk between 100 and 350;
78select * from t2 ignore key(PRIMARY)
79where key1='frequent-val' and key2 between 'rare-400' and 'rare-450' order by pk limit 2;
80
81drop table t1, t2;
82
83
84set optimizer_switch=@innodb_icp_tmp;
85set default_storage_engine= @save_storage_engine;
86
87set global innodb_stats_persistent= @innodb_stats_persistent_save;
88set global innodb_stats_persistent_sample_pages=
89             @innodb_stats_persistent_sample_pages_save;
90
91