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