mirror of
https://github.com/MariaDB/server.git
synced 2025-11-04 04:46:15 +01:00
229 lines
6.6 KiB
Text
229 lines
6.6 KiB
Text
# 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;
|