diff options
Diffstat (limited to 'mysql-test/t/myisam_mrr.test')
| -rw-r--r-- | mysql-test/t/myisam_mrr.test | 326 |
1 files changed, 0 insertions, 326 deletions
diff --git a/mysql-test/t/myisam_mrr.test b/mysql-test/t/myisam_mrr.test deleted file mode 100644 index b15fdf60ce7..00000000000 --- a/mysql-test/t/myisam_mrr.test +++ /dev/null @@ -1,326 +0,0 @@ -# -# MRR/MyISAM tests. -# - ---disable_warnings -drop table if exists t0, t1, t2, t3; ---enable_warnings - -set @myisam_mrr_tmp=@@optimizer_switch; -set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; -set @mrr_buffer_size_save= @@mrr_buffer_size; -set mrr_buffer_size=79; - --- source include/mrr_tests.inc - -set @@mrr_buffer_size= @mrr_buffer_size_save; - -# -# BUG#30622: Incorrect query results for MRR + filesort -# -CREATE TABLE t1 ( - ID int(10) unsigned NOT NULL AUTO_INCREMENT, - col1 int(10) unsigned DEFAULT NULL, - key1 int(10) unsigned NOT NULL DEFAULT '0', - key2 int(10) unsigned DEFAULT NULL, - text1 text, - text2 text, - col2 smallint(6) DEFAULT '100', - col3 enum('headers','bodyandsubject') NOT NULL DEFAULT 'bodyandsubject', - col4 tinyint(3) unsigned NOT NULL DEFAULT '0', - PRIMARY KEY (ID), - KEY (key1), - KEY (key2) -) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; - -INSERT INTO t1 VALUES -(1,NULL,1130,NULL,'Hello',NULL,100,'bodyandsubject',0), -(2,NULL,1130,NULL,'bye',NULL,100,'bodyandsubject',0), -(3,NULL,1130,NULL,'red',NULL,100,'bodyandsubject',0), -(4,NULL,1130,NULL,'yellow',NULL,100,'bodyandsubject',0), -(5,NULL,1130,NULL,'blue',NULL,100,'bodyandsubject',0); - -select * FROM t1 WHERE key1=1130 AND col1 IS NULL ORDER BY text1; - -drop table t1; - - ---echo ---echo BUG#37851: Crash in test_if_skip_sort_order tab->select is zero ---echo -CREATE TABLE t1 ( - pk int(11) NOT NULL AUTO_INCREMENT, - PRIMARY KEY (pk) -); -INSERT INTO t1 VALUES (1); - -CREATE TABLE t2 ( - pk int(11) NOT NULL AUTO_INCREMENT, - int_key int(11) DEFAULT NULL, - PRIMARY KEY (pk), - KEY int_key (int_key) -); -INSERT INTO t2 VALUES (1,1),(2,6),(3,0); - -EXPLAIN EXTENDED -SELECT MIN(t1.pk) -FROM t1 WHERE EXISTS ( - SELECT t2.pk - FROM t2 - WHERE t2.int_key IS NULL - GROUP BY t2.pk -); - -DROP TABLE t1, t2; - --- echo # --- echo # BUG#42048 Discrepancy between MyISAM and Maria's ICP implementation --- echo # -create table t0 (a int); -insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); -create table t1 (a int, b char(20), filler char(200), key(a,b(10))); -insert into t1 select A.a + 10*(B.a + 10*C.a), 'bbb','filler' from t0 A, t0 B, t0 C; -update t1 set b=repeat(char(65+a), 20) where a < 25; - ---echo This must show range + using index condition: -explain select * from t1 where a < 10 and b = repeat(char(65+a), 20); -select * from t1 where a < 10 and b = repeat(char(65+a), 20); -drop table t0,t1; - --- echo # --- echo # BUG#41136: ORDER BY + range access: EXPLAIN shows "Using MRR" while MRR is actually not used --- echo # -create table t0 (a int); -insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); -create table t1 (a int, b int, key(a)); -insert into t1 select A.a + 10 *(B.a + 10*C.a), A.a + 10 *(B.a + 10*C.a) from t0 A, t0 B, t0 C; --- echo This mustn't show "Using MRR": -explain select * from t1 where a < 20 order by a; -drop table t0, t1; - --- echo # --- echo # Part of MWL#67: DS-MRR backport: add an @@optimizer_switch flag for --- echo # index_condition pushdown: --- echo # - engine_condition_pushdown does not affect ICP - - -# Check that optimizer_switch is present -select @@optimizer_switch like '%index_condition_pushdown=on%'; - -# Check if it affects ICP -create table t0 (a int); -insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); -create table t1 (a int, b int, key(a)); -insert into t1 select A.a + 10 *(B.a + 10*C.a), A.a + 10 *(B.a + 10*C.a) from t0 A, t0 B, t0 C; - --- echo A query that will use ICP: -explain select * from t1 where a < 20; - -set @save_optimizer_switch=@@optimizer_switch; -set optimizer_switch='index_condition_pushdown=off'; -explain select * from t1 where a < 20; - -set optimizer_switch='index_condition_pushdown=on'; -explain select * from t1 where a < 20; - -set optimizer_switch=@save_optimizer_switch; - - ---echo # ---echo # BUG#629684: Unreachable code in multi_range_read.cc in maria-5.3-dsmrr-cpk ---echo # - -delete from t0 where a > 2; -insert into t0 values (NULL),(NULL); -insert into t1 values (NULL, 1234), (NULL, 5678); - -set @save_join_cache_level=@@join_cache_level; -set @@join_cache_level=6; -explain -select * from t0, t1 where t0.a<=>t1.a; -select * from t0, t1 where t0.a<=>t1.a; - -set @@join_cache_level=@save_join_cache_level; -drop table t0, t1; - ---echo # ---echo # BUG#625841: Assertion `!table || (!table->read_set || bitmap_is_set ---echo # (table->read_set, field_index))' on REPLACE ... SELECT with MRR ---echo # -create table t0 (a int); -insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); - -create table t1 ( - key1 varchar(10), - col1 char(255), col2 char(255), - col3 char(244), col4 char(255), - key(key1) -); -create table t2 like t1; - -insert into t1 -select - 1000+A.a+100*B.a + 10*C.a, - 'col1val', 'col2val', - 'col3val', 'col4val' -from t0 A, t0 B, t0 C; - -REPLACE INTO t2(col2,col3,col4) -SELECT col2,col3,col4 -FROM t1 -WHERE `key1` LIKE CONCAT( LEFT( '1' , 7 ) , '%' ) -ORDER BY col1 LIMIT 7; -drop table t0, t1, t2; - ---echo # ---echo # BUG#670417: Diverging results in maria-5.3-mwl128-dsmrr-cpk with join buffer (incremental, BKA join) ---echo # - -set @save_join_cache_level = @@join_cache_level; -set join_cache_level = 6; -set @save_join_buffer_size=@@join_buffer_size; ---disable_warnings -set join_buffer_size = 136; ---enable_warnings - -CREATE TABLE t1 ( - pk int(11) NOT NULL AUTO_INCREMENT, - col_int_key int(11) NOT NULL, - col_varchar_key varchar(1) NOT NULL, - col_varchar_nokey varchar(1) NOT NULL, - PRIMARY KEY (pk), - KEY col_varchar_key (col_varchar_key,col_int_key) -); -INSERT INTO t1 VALUES - (10,8,'v','v'),(11,8,'f','f'), (12,5,'v','v'), - (13,8,'s','s'),(14,8,'a','a'),(15,6,'p','p'), - (16,7,'z','z'),(17,2,'a','a'),(18,5,'h','h'), - (19,7,'h','h'),(20,2,'v','v'),(21,9,'v','v'), - (22,142,'b','b'),(23,3,'y','y'),(24,0,'v','v'), - (25,3,'m','m'),(26,5,'z','z'),(27,9,'n','n'), - (28,1,'d','d'),(29,107,'a','a'); -INSERT INTO t1 VALUES - (110,8,'v','v'),(111,8,'f','f'), (112,5,'v','v'), - (113,8,'s','s'),(114,8,'a','a'),(115,6,'p','p'), - (116,7,'z','z'),(117,2,'a','a'),(118,5,'h','h'), - (119,7,'h','h'),(120,2,'v','v'),(121,9,'v','v'), - (122,142,'b','b'),(123,3,'y','y'),(124,0,'v','v'), - (125,3,'m','m'),(126,5,'z','z'),(127,9,'n','n'), - (128,1,'d','d'),(129,107,'a','a'); - -SELECT COUNT(*) -FROM - t1 AS table2, t1 AS table3 -where - table3.col_varchar_key = table2.col_varchar_key AND - table3.col_varchar_key = table2.col_varchar_nokey AND - table3.pk<>0; - -EXPLAIN SELECT COUNT(*) -FROM - t1 AS table2, t1 AS table3 -where - table3.col_varchar_key = table2.col_varchar_key AND - table3.col_varchar_key = table2.col_varchar_nokey AND - table3.pk<>0; - -set join_cache_level= @save_join_cache_level; -set join_buffer_size= @save_join_buffer_size; -drop table t1; - - ---echo # ---echo # BUG#730133: Wrong result with jkl = 7, BKA, ICP in maria-5.3 + compound index ---echo # -set @tmp_730133_jcl= @@join_cache_level; -set join_cache_level = 7; - -set @tmp_730133_os= @@optimizer_switch; -set optimizer_switch= 'join_cache_hashed=off,join_cache_bka=on,index_condition_pushdown=on,optimize_join_buffer_size=on'; - -CREATE TABLE t1 (f1 int, f2 int, f3 int, f4 int, f5 int, KEY (f4,f3)); -INSERT IGNORE INTO t1 VALUES ('2','9','5','0','0'),('4','7','0','0','0'), - ('6','97','190','0','0'),('7','3','6','0','0'),('11','101','186','0','0'), - ('14','194','226','0','0'),('15','148','133','0','0'), - ('16','9','6','0','0'),('17','9','3','0','0'),('18','1','8','0','0'), - ('19','1','5','0','0'),('20','5','7','0','0'); - -explain -SELECT COUNT(alias2.f2) -FROM - t1 STRAIGHT_JOIN - t1 AS alias3 STRAIGHT_JOIN - t1 AS alias2 FORCE KEY (f4) -WHERE - alias2.f4=alias3.f5 AND - alias2.f3 > alias3.f1; - -SELECT COUNT(alias2.f2) -FROM - t1 STRAIGHT_JOIN - t1 AS alias3 STRAIGHT_JOIN - t1 AS alias2 FORCE KEY (f4) -WHERE - alias2.f4=alias3.f5 AND - alias2.f3 > alias3.f1; - -set @@join_cache_level= @tmp_730133_jcl; -set @@optimizer_switch= @tmp_730133_os; -drop table t1; - ---echo # ---echo # Test of MRR handler counters ---echo # -flush status; -show status like 'Handler_mrr%'; -create table t0 (a int); -insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); -create table t1 (a int, b int, filler char(200), key(a)); -insert into t1 -select A.a+10*B.a+100*C.a+1000*D.a, 123,'filler' from t0 A, t0 B, t0 C, t0 D; - -explain select sum(b) from t1 where a < 10; ---echo # This should show one MRR scan and no re-fills: -flush status; -select sum(b) from t1 where a < 10; -show status like 'handler_mrr%'; - -set @mrr_buffer_size_save= @@mrr_buffer_size; ---disable_warnings -set mrr_buffer_size=128; ---enable_warnings - -explain select sum(b) from t1 where a < 1600; ---echo # This should show one MRR scan and one extra rowid sort: -flush status; -select sum(b) from t1 where a < 1600; -show status like 'handler_mrr%'; -set @@mrr_buffer_size= @mrr_buffer_size_save; - ---echo #Now, let's check BKA: -set @join_cache_level_save= @@join_cache_level; -set @join_buffer_size_save= @@join_buffer_size; -set join_cache_level=6; - -explain select sum(t1.b) from t0,t1 where t0.a=t1.a; -flush status; -select sum(t1.b) from t0,t1 where t0.a=t1.a; -show status like 'handler_mrr%'; - ---disable_warnings -set join_buffer_size=10; ---enable_warnings -explain select sum(t1.b) from t0,t1 where t0.a=t1.a; -flush status; -select sum(t1.b) from t0,t1 where t0.a=t1.a; -# the values below depend on sizeof(void*) ---replace_result 1 ok 2 ok 3 ok -show status like 'handler_mrr%'; - -set join_cache_level= @join_cache_level_save; -set join_buffer_size= @join_buffer_size_save; - -drop table t0, t1; - -## This must be last line in the file: -set optimizer_switch= @myisam_mrr_tmp; |