diff options
author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 18:00:34 +0000 |
---|---|---|
committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 18:00:34 +0000 |
commit | 3f619478f796eddbba6e39502fe941b285dd97b1 (patch) | |
tree | e2c7b5777f728320e5b5542b6213fd3591ba51e2 /mysql-test/main/index_merge_innodb.test | |
parent | Initial commit. (diff) | |
download | mariadb-3f619478f796eddbba6e39502fe941b285dd97b1.tar.xz mariadb-3f619478f796eddbba6e39502fe941b285dd97b1.zip |
Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'mysql-test/main/index_merge_innodb.test')
-rw-r--r-- | mysql-test/main/index_merge_innodb.test | 229 |
1 files changed, 229 insertions, 0 deletions
diff --git a/mysql-test/main/index_merge_innodb.test b/mysql-test/main/index_merge_innodb.test new file mode 100644 index 00000000..82f3c756 --- /dev/null +++ b/mysql-test/main/index_merge_innodb.test @@ -0,0 +1,229 @@ +# t/index_merge_innodb.test +# +# Index merge tests +# +# Last update: +# 2006-08-07 ML test refactored (MySQL 5.1) +# Main code of several index_merge tests +# -> include/index_merge*.inc +# wrapper t/index_merge_innodb.test sources now several +# include/index_merge*.inc files +# + +# Slow test, don't run during staging part +--source include/long_test.inc +--source include/not_staging.inc +--source include/have_innodb.inc + +connect disable_purge,localhost,root,,; +--echo # Disable the purge of InnoDB history, to make the test run faster. +START TRANSACTION WITH CONSISTENT SNAPSHOT; +connection default; + +SET DEFAULT_STORAGE_ENGINE = InnoDB; +# InnoDB does not support Merge tables (affects include/index_merge1.inc) +let $merge_table_support= 0; + +set @optimizer_switch_save= @@optimizer_switch; +set optimizer_switch='index_merge_sort_intersection=off'; +set optimizer_switch='rowid_filter=off'; + +SET @save_stats_persistent=@@GLOBAL.innodb_stats_persistent; +SET GLOBAL innodb_stats_persistent=0; + +# The first two tests are disabled because of non deterministic explain output. +# If include/index_merge1.inc can be enabled for InnoDB and all other +# storage engines, please remove the subtest for Bug#21277 from +# include/index_merge2.inc. +# This test exists already in include/index_merge1.inc. +# --source include/index_merge1.inc +# --source include/index_merge_ror.inc +#the next one is disabled in MySQL too: Bug#45727 +--source include/index_merge2.inc + +--source include/index_merge_2sweeps.inc +--source include/index_merge_ror_cpk.inc + +--echo # +--echo # BUG#56862/640419: Wrong result with sort_union index merge when one +--echo # of the merged index scans is the primary key scan +--echo # + +CREATE TABLE t0(a int, b int) ENGINE=MyISAM; + +CREATE TABLE t1 ( + pk int NOT NULL AUTO_INCREMENT PRIMARY KEY, + a int, + b int, + INDEX idx(a)) +ENGINE=INNODB; + +INSERT INTO t0(a,b) VALUES + (11, 1100), (2, 200), (1, 100), (14, 1400), (5, 500), + (3, 300), (17, 1700), (4, 400), (12, 1200), (8, 800), + (6, 600), (18, 1800), (9, 900), (10, 1000), (7, 700), + (13, 1300), (15, 1500), (19, 1900), (16, 1600), (20, 2000); +INSERT INTO t0(a,b) SELECT a+20, b+2000 FROM t0; +INSERT INTO t0(a,b) SELECT a+40, b+4000 FROM t0; +INSERT INTO t0(a,b) SELECT a+80, b+8000 FROM t0; +begin; +INSERT INTO t1(a,b) SELECT t0.a,t0.b FROM t0, seq_1_to_1024; +INSERT INTO t1 VALUES (1000000, 0, 0); +commit; +DROP TABLE t0; + +SET SESSION sort_buffer_size = 1024*36; +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; + +# We have to use FORCE INDEX here as Innodb gives inconsistent estimates +# which causes different query plans. +--replace_column 9 # +EXPLAIN +SELECT COUNT(*) FROM + (SELECT * FROM t1 FORCE INDEX(primary,idx) + WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; +SELECT COUNT(*) FROM + (SELECT * FROM t1 FORCE INDEX(primary,idx) + WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; + +--replace_column 9 # +EXPLAIN +SELECT COUNT(*) FROM + (SELECT * FROM t1 IGNORE INDEX(idx) + WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; +SELECT COUNT(*) FROM + (SELECT * FROM t1 IGNORE INDEX(idx) + WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; + +DROP TABLE t1; +set optimizer_switch=@tmp_optimizer_switch; + +--echo # +--echo # Testcase Backport: BUG#48093: 6.0 Server not processing equivalent IN clauses properly +--echo # with Innodb tables +--echo # + +CREATE TABLE t1 ( + i int(11) DEFAULT NULL, + v1 varchar(1) DEFAULT NULL, + v2 varchar(20) DEFAULT NULL, + KEY i (i), + KEY v (v1,i) +) ENGINE=innodb; + +INSERT INTO t1 VALUES (1,'f','no'); +INSERT INTO t1 VALUES (2,'u','yes-u'); +INSERT INTO t1 VALUES (2,'h','yes-h'); +INSERT INTO t1 VALUES (3,'d','no'); + +--echo +SELECT v2 +FROM t1 +WHERE v1 IN ('f', 'd', 'h', 'u' ) AND i = 2; + +--echo +--echo # Should not use index_merge +EXPLAIN +SELECT v2 +FROM t1 +WHERE v1 IN ('f', 'd', 'h', 'u' ) AND i = 2; + +DROP TABLE t1; + +--echo # +--echo # BUG#1006164: Multi-table DELETE that uses innodb + index_merge/intersect may fail to delete rows +--echo # + +create table t1 ( + pk int auto_increment, + zone_id int, + modified tinyint, + primary key(pk), + key (zone_id), + key (modified) +) engine=innodb; + +insert into t1 (zone_id, modified) select 0,0 from seq_1_to_10000; +update t1 set zone_id=487, modified=9 where pk=7259; +update t1 set zone_id=487, modified=9 where pk=7260; +update t1 set zone_id=830, modified=9 where pk=8434; +update t1 set zone_id=830, modified=9 where pk=8435; +update t1 set zone_id=830, modified=9 where pk=8436; +update t1 set zone_id=830, modified=9 where pk=8437; + +select * from t1 where t1.zone_id=830 AND modified=9; +begin; +DELETE t1 FROM t1 WHERE t1.zone_id=830 AND modified=9; +commit; +select * from t1 where t1.zone_id=830 AND modified=9; + +drop table t1; + +--echo # +--echo # MDEV-376: Wrong result (missing rows) with index_merge+index_merge_intersection, join +--echo # +CREATE TABLE t1 ( + a INT, b CHAR(1), c CHAR(1), KEY(a), KEY(b) +) ENGINE=InnoDB; + +INSERT INTO t1 VALUES (8,'v','v'),(8,'m','m'),(9,'d','d'); + + +SELECT ta.* FROM t1 AS ta, t1 AS tb +WHERE ( tb.b != ta.b OR tb.a = ta.a ) + AND ( tb.b = ta.c OR tb.b = ta.b ); + +DROP TABLE t1; +set optimizer_switch= @optimizer_switch_save; + +--echo # +--echo # MDEV-10927: Crash When Using sort_union Optimization +--echo # + +set @tmp_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='index_merge_sort_intersection=on'; +SET SESSION sort_buffer_size = 1024; + +create table t1 ( +pk int(11) NOT NULL AUTO_INCREMENT, +col1 int(11) NOT NULL, +col2 int(11) NOT NULL, +col3 int(11) NOT NULL, +key2 int(11) NOT NULL, +col4 int(11) NOT NULL, +key1 int(11) NOT NULL, +PRIMARY KEY (pk), +KEY key1 (key1), +KEY key2 (key2) +) ENGINE=InnoDB AUTO_INCREMENT=12860259 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT; + +insert into t1 (key1, key2, col1,col2,col3,col4) +select seq,seq,seq,seq,seq,seq from seq_1_to_10000; +SELECT sum(col1) FROM t1 FORCE INDEX (key1,key2) WHERE (key1 between 10 and 8191+10) or (key2= 5); +drop table t1; +set optimizer_switch=@tmp_optimizer_switch; + +--echo # +--echo # MDEV-22728: SIGFPE in Unique::get_cost_calc_buff_size from prepare_search_best_index_intersect +--echo # on optimized builds +--echo # + +SET @save_sort_buffer_size=@@sort_buffer_size; +SET sort_buffer_size=2048; + +CREATE TABLE t1 ( + a VARCHAR(1024) CHARACTER SET UTF8 PRIMARY KEY, + b INT, + c INT, + INDEX (b) +) ENGINE=InnoDB CHARSET utf8 ROW_FORMAT= DYNAMIC; +INSERT INTO t1 SELECT seq, seq, seq from seq_1_to_100; +EXPLAIN SELECT * FROM t1 WHERE a='1' OR b < 5; +SELECT * FROM t1 WHERE a='1' OR b < 5; +DROP TABLE t1; + +SET sort_buffer_size= @save_sort_buffer_size; + +disconnect disable_purge; +SET GLOBAL innodb_stats_persistent=@save_stats_persistent; |