path: root/mysql-test/main/myisam_mrr.test

#
# 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;