Adding upstream version 1:10.5.12.upstream/1%10.5.12upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 328 insertions, 0 deletions

diff --git a/mysql-test/main/myisam_mrr.test b/mysql-test/main/myisam_mrr.test
new file mode 100644
index 00000000..11c9aa64
--- /dev/null
+++ b/mysql-test/main/myisam_mrr.test
@@ -0,0 +1,328 @@
+#
+# 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 optimizer_switch='optimize_join_buffer_size=on';
+set @mrr_buffer_size_save= @@mrr_buffer_size;
+set mrr_buffer_size=79;
+
+-- source include/mrr_tests.inc
+-- source include/word_size.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;