diff options
Diffstat (limited to 'mysql-test/main/innodb_ext_key.test')
| -rw-r--r-- | mysql-test/main/innodb_ext_key.test | 628 |
1 files changed, 628 insertions, 0 deletions
diff --git a/mysql-test/main/innodb_ext_key.test b/mysql-test/main/innodb_ext_key.test new file mode 100644 index 00000000000..04ebcc1e41b --- /dev/null +++ b/mysql-test/main/innodb_ext_key.test @@ -0,0 +1,628 @@ +--source include/innodb_prefix_index_cluster_optimization.inc + +SET SESSION STORAGE_ENGINE='InnoDB'; + +CREATE DATABASE dbt3_s001; + +use dbt3_s001; + +--disable_query_log +--disable_result_log +--disable_warnings +--source include/dbt3_s001.inc +--enable_warnings +--enable_result_log +--enable_query_log + +explain +select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01'; +flush status; +select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01'; +show status like 'handler_read%'; + +explain +select count(*) from lineitem + where l_orderkey=130 and l_linenumber=2 and l_shipdate='1992-07-01'; +flush status; +select count(*) from lineitem + where l_orderkey=130 and l_linenumber=2 and l_shipdate='1992-07-01'; +show status like 'handler_read%'; + +explain +select count(*) from lineitem + where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000; +flush status; +select count(*) from lineitem + where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000; +show status like 'handler_read%'; + +explain +select l_orderkey, l_linenumber from lineitem + where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000; +flush status; +select l_orderkey, l_linenumber from lineitem + where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000; +show status like 'handler_read%'; + +explain +select min(l_orderkey) from lineitem where l_shipdate='1992-07-01'; +flush status; +select min(l_orderkey) from lineitem where l_shipdate='1992-07-01'; +show status like 'handler_read%'; + +explain +select min(l_orderkey) from lineitem + where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000; +flush status; +select min(l_orderkey) from lineitem + where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000; +show status like 'handler_read%'; + +explain +select max(l_linenumber) from lineitem + where l_shipdate='1992-07-01' and l_orderkey=130; +flush status; +select max(l_linenumber) from lineitem + where l_shipdate='1992-07-01' and l_orderkey=130; +show status like 'handler_read%'; + +explain +select l_orderkey, l_linenumber + from lineitem use index (i_l_shipdate, i_l_receiptdate) + where l_shipdate='1992-07-01' and l_orderkey=130 + or l_receiptdate='1992-07-01' and l_orderkey=5603; +flush status; +select l_orderkey, l_linenumber + from lineitem use index (i_l_shipdate, i_l_receiptdate) + where l_shipdate='1992-07-01' and l_orderkey=130 + or l_receiptdate='1992-07-01' and l_orderkey=5603; +show status like 'handler_read%'; + +explain +select l_orderkey, l_linenumber + from lineitem use index (i_l_shipdate, i_l_receiptdate) + where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000 + or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000; +flush status; +select l_orderkey, l_linenumber + from lineitem use index (i_l_shipdate, i_l_receiptdate) + where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000 + or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000; +show status like 'handler_read%'; + +explain +select l_orderkey, l_linenumber from lineitem + where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000 + or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000; +flush status; +select l_orderkey, l_linenumber from lineitem + where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000 + or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000; +show status like 'handler_read%'; + +--replace_column 9 # +explain +select max(l_orderkey) from lineitem + where l_partkey between 1 and 10 group by l_partkey; +flush status; +select max(l_orderkey) from lineitem + where l_partkey between 1 and 10 group by l_partkey; +show status like 'handler_read%'; + +--replace_column 9 # +explain +select max(l_orderkey) from lineitem + where l_suppkey in (1,4) group by l_suppkey; +flush status; +select max(l_orderkey) from lineitem + where l_suppkey in (1,4) group by l_suppkey; +show status like 'handler_read%'; + +create index i_p_retailprice on part(p_retailprice); + +--replace_column 9 # +explain +select o_orderkey, p_partkey + from part use index (i_p_retailprice), + lineitem use index (i_l_partkey), orders + where p_retailprice > 1100 and o_orderdate='1997-01-01' + and o_orderkey=l_orderkey and p_partkey=l_partkey; +flush status; +select o_orderkey, p_partkey + from part use index (i_p_retailprice), + lineitem use index (i_l_partkey), orders + where p_retailprice > 1100 and o_orderdate='1997-01-01' + and o_orderkey=l_orderkey and p_partkey=l_partkey; +show status like 'handler_read%'; + +--echo # +--echo # Bug mdev-3851: ref access used instead of expected eq_ref access +--echo # when extended_keys=on +--echo # + +create table t0 (a int); +insert into t0 values (1), (2), (3), (4), (5); +create index i_p_size on part(p_size); + +explain +select * from t0, part ignore index (primary) + where p_partkey=t0.a and p_size=1; + +select * from t0, part ignore index (primary) + where p_partkey=t0.a and p_size=1; + +drop table t0; +drop index i_p_size on part; + +DROP DATABASE dbt3_s001; + +use test; + +--echo # +--echo # LP Bug #914560: query containing IN subquery +--echo # + extended_keys = on +--echo # + +set @save_optimizer_switch=@@optimizer_switch; + +SET optimizer_switch='materialization=on,semijoin=on'; + +CREATE TABLE t1 (a int, b int) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,1), (2,2); + +SELECT * FROM t1 WHERE 2 IN (SELECT MAX(s1.a) FROM t1 AS s1, t1 AS s2); +EXPLAIN +SELECT * FROM t1 WHERE 2 IN (SELECT MAX(s1.a) FROM t1 AS s1, t1 AS s2); + +DROP TABLE t1; + +set optimizer_switch=@save_optimizer_switch; + +--echo # +--echo # LP Bug #915291: query using a materialized view +--echo # + extended_keys = on +--echo # (valgrinf complains fixed by the patch for bug #914560) +--echo # + +SET optimizer_switch = 'derived_with_keys=on'; + +CREATE TABLE t1 (a varchar(1)) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('j'), ('v'); + +CREATE TABLE t2 (b varchar(1)) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('j'), ('v'); + +CREATE TABLE t3 (c varchar(1)); +INSERT INTO t2 VALUES ('m'), ('n'); + +CREATE VIEW v + AS SELECT DISTINCT * FROM t2 STRAIGHT_JOIN t3; + +SELECT * FROM t1, v WHERE a = b; + +DROP VIEW v; +DROP TABLE t1,t2,t3; + +set optimizer_switch=@save_optimizer_switch; + +--echo # +--echo # LP Bug #921167: query containing IN subquery +--echo # + extended_keys = on +--echo # + +CREATE TABLE t1 ( + a int NOT NULL, b varchar(1) NOT NULL, KEY(a), KEY(b,a) +) ENGINE=MyISAM; +INSERT INTO t1 VALUES + (0,'j'), (8,'v'), (1,'c'), (8,'m'), (9,'d'), + (24,'d'), (6,'y'), (1,'t'), (6,'d'), (2,'s'); + +CREATE TABLE t2 ( + c int NOT NULL PRIMARY KEY +) ENGINE=MyISAM; +INSERT INTO t2 VALUES + (10), (11), (12), (13), (14), + (15), (16), (17), (18), (19), (24); + +EXPLAIN +SELECT a FROM t1 AS t, t2 + WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b); +SELECT a FROM t1 AS t, t2 + WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b); + +DROP TABLE t1,t2; + +--echo # +--echo # LP Bug #923236: hash join + extended_keys = on +--echo # + +CREATE TABLE t1 (a int) ENGINE=MyISAM; + +CREATE TABLE t2 (b int) ENGINE=MyISAM; + +INSERT INTO t1 (a) VALUES (4), (6); +INSERT INTO t2 (b) VALUES (0), (8); + +set @save_join_cache_level=@@join_cache_level; + +SET join_cache_level=3; +SET optimizer_switch='join_cache_hashed=on'; +SET optimizer_switch='join_cache_bka=on'; + +EXPLAIN +SELECT * FROM t1, t2 WHERE b=a; +SELECT * FROM t1, t2 WHERE b=a; + +set join_cache_level=@save_join_cache_level; +set optimizer_switch=@save_optimizer_switch; + +DROP TABLE t1,t2; + + +--echo # +--echo # Bug mdev-3888: INSERT with UPDATE on duplicate keys +--echo # with extended_keys=on +--echo # + +CREATE TABLE t1 ( +c1 bigint(20) unsigned NOT NULL AUTO_INCREMENT, +c2 bigint(20) unsigned NOT NULL, +c3 bigint(20) unsigned NOT NULL, +c4 varchar(128) DEFAULT NULL, +PRIMARY KEY (c1), +UNIQUE KEY uq (c2,c3), +KEY c3 (c3), +KEY c4 (c4) +) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; + + +INSERT INTO t1 (c2, c3, c4) VALUES (58291525, 2580, 'foobar') + ON DUPLICATE KEY UPDATE c4 = VALUES(c4); +INSERT INTO t1 (c2, c3, c4) VALUES (58291525, 2580, 'foobar') + ON DUPLICATE KEY UPDATE c4 = VALUES(c4); + +DROP TABLE t1; + +--echo # +--echo # Bug mdev-4220: using ref instead of eq_ref +--echo # with extended_keys=on +--echo # (performance regression introduced in the patch for mdev-3851) +--echo # + +create table t1 (a int not null) engine=innodb; + +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t2 ( + pk int primary key, a int not null, b int, unique(a) +)engine=innodb; + +insert into t2 +select + A.a + 10 * B.a, A.a + 10 * B.a, A.a + 10 * B.a +from t1 A, t1 B; + +explain +select * from t1, t2 where t2.a=t1.a and t2.b < 2; +flush status; +select * from t1, t2 where t2.a=t1.a and t2.b < 2; +show status like 'handler_read%'; + +drop table t1,t2; + +# this test case did not demonstrate any regression +# it is added for better testing + +create table t1(a int) engine=myisam; +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t2(a int) engine=myisam; +insert into t2 select A.a + 10*B.a + 100*C.a from t1 A, t1 B, t1 C; + +create table t3 ( + pk1 int not null, pk2 int not null, col1 int not null, col2 int not null) +engine=innodb; +insert into t3 select a,a,a,a from t2; +alter table t3 add primary key (pk1, pk2); +alter table t3 add key (col1, col2); +analyze table t1,t3; + +--replace_column 9 # +explain +select * from t1, t3 where t3.col1=t1.a and t3.col2=t1.a; +--replace_column 9 # +explain +select * from t1, t3 where t3.col1=t1.a and t3.col2=t1.a and t3.pk1=t1.a; + +drop table t1,t2,t3; + +--echo # +--echo # Bug mdev-4340: performance regression with extended_keys=on +--echo # + +CREATE TABLE t1 ( + page_id int(8) unsigned NOT NULL AUTO_INCREMENT, + page_namespace int(11) NOT NULL DEFAULT '0', + page_title varbinary(255) NOT NULL DEFAULT '', + page_restrictions tinyblob NOT NULL, + page_counter bigint(20) unsigned NOT NULL DEFAULT '0', + page_is_redirect tinyint(1) unsigned NOT NULL DEFAULT '0', + page_is_new tinyint(1) unsigned NOT NULL DEFAULT '0', + page_random double unsigned NOT NULL DEFAULT '0', + page_touched varbinary(14) NOT NULL DEFAULT '', + page_latest int(8) unsigned NOT NULL DEFAULT '0', + page_len int(8) unsigned NOT NULL DEFAULT '0', + PRIMARY KEY (page_id), + UNIQUE KEY name_title (page_namespace,page_title), + KEY page_random (page_random), + KEY page_len (page_len), + KEY page_redirect_namespace_len (page_is_redirect,page_namespace,page_len) +) ENGINE=InnoDB AUTO_INCREMENT=38929100 DEFAULT CHARSET=binary; +INSERT INTO t1 VALUES +(38928077,0,'Sandbox','',0,0,0,0,'',0,0),(38928078,1,'Sandbox','',0,0,0,1,'',0,0), +(38928079,2,'Sandbox','',0,0,0,2,'',0,0),(38928080,3,'Sandbox','',0,0,0,3,'',0,0), +(38928081,4,'Sandbox','',0,0,0,4,'',0,0),(38928082,5,'Sandbox','',0,0,0,5,'',0,0); + +CREATE TABLE t2 ( + rev_id int(8) unsigned NOT NULL AUTO_INCREMENT, + rev_page int(8) unsigned NOT NULL DEFAULT '0', + rev_text_id int(8) unsigned NOT NULL DEFAULT '0', + rev_comment varbinary(255) DEFAULT NULL, + rev_user int(5) unsigned NOT NULL DEFAULT '0', + rev_user_text varbinary(255) NOT NULL DEFAULT '', + rev_timestamp varbinary(14) NOT NULL DEFAULT '', + rev_minor_edit tinyint(1) unsigned NOT NULL DEFAULT '0', + rev_deleted tinyint(1) unsigned NOT NULL DEFAULT '0', + rev_len int(8) unsigned DEFAULT NULL, + rev_parent_id int(8) unsigned DEFAULT NULL, + rev_sha1 varbinary(32) NOT NULL DEFAULT '', + PRIMARY KEY (rev_page,rev_id), + UNIQUE KEY rev_id (rev_id), + KEY rev_timestamp (rev_timestamp), + KEY page_timestamp (rev_page,rev_timestamp), + KEY user_timestamp (rev_user,rev_timestamp), + KEY usertext_timestamp (rev_user_text,rev_timestamp,rev_user,rev_deleted,rev_minor_edit,rev_text_id,rev_comment) +) ENGINE=InnoDB DEFAULT CHARSET=binary; +INSERT INTO t2 VALUES +(547116222,20,0,NULL,3,'','',0,0,NULL,NULL,''),(547117245,20,0,NULL,4,'','',0,0,NULL,NULL,''), +(547118268,20,0,NULL,5,'','',0,0,NULL,NULL,''),(547114177,21,0,NULL,1,'','',0,0,NULL,NULL,''), +(547115200,21,0,NULL,2,'','',0,0,NULL,NULL,''),(547116223,21,0,NULL,3,'','',0,0,NULL,NULL,''), +(547117246,21,0,NULL,4,'','',0,0,NULL,NULL,''),(547118269,21,0,NULL,5,'','',0,0,NULL,NULL,''), +(547114178,22,0,NULL,1,'','',0,0,NULL,NULL,''),(547115201,22,0,NULL,2,'','',0,0,NULL,NULL,''), +(547116224,22,0,NULL,3,'','',0,0,NULL,NULL,''),(547117247,22,0,NULL,4,'','',0,0,NULL,NULL,''), +(547116226,24,0,NULL,3,'','',0,0,NULL,NULL,''),(547117249,24,0,NULL,4,'','',0,0,NULL,NULL,''), +(547118272,24,0,NULL,5,'','',0,0,NULL,NULL,''),(547114181,25,0,NULL,1,'','',0,0,NULL,NULL,''), +(547115204,25,0,NULL,2,'','',0,0,NULL,NULL,''),(547116227,25,0,NULL,3,'','',0,0,NULL,NULL,''), +(547116157,978,0,NULL,2,'','',0,0,NULL,NULL,''),(547117180,978,0,NULL,3,'','',0,0,NULL,NULL,''), +(547118203,978,0,NULL,4,'','',0,0,NULL,NULL,''),(547119226,978,0,NULL,5,'','',0,0,NULL,NULL,''), +(547115135,979,0,NULL,1,'','',0,0,NULL,NULL,''),(547116158,979,0,NULL,2,'','',0,0,NULL,NULL,''), +(547116173,994,0,NULL,2,'','',0,0,NULL,NULL,''),(547117196,994,0,NULL,3,'','',0,0,NULL,NULL,''), +(547118219,994,0,NULL,4,'','',0,0,NULL,NULL,''),(547119242,994,0,NULL,5,'','',0,0,NULL,NULL,''), +(547115151,995,0,NULL,1,'','',0,0,NULL,NULL,''),(547116174,995,0,NULL,2,'','',0,0,NULL,NULL,''), +(547117197,995,0,NULL,3,'','',0,0,NULL,NULL,''),(547118220,995,0,NULL,4,'','',0,0,NULL,NULL,''), +(547118223,998,0,NULL,4,'','',0,0,NULL,NULL,''),(547119246,998,0,NULL,5,'','',0,0,NULL,NULL,''), +(547115155,999,0,NULL,1,'','',0,0,NULL,NULL,''),(547116178,999,0,NULL,2,'','',0,0,NULL,NULL,''), +(547117201,999,0,NULL,3,'','',0,0,NULL,NULL,''),(547118224,999,0,NULL,4,'','',0,0,NULL,NULL,''), +(547119271,38928081,0,NULL,10,'','',0,0,NULL,NULL,''),(547119272,38928081,0,NULL,11,'','',0,0,NULL,NULL,''), +(547119273,38928081,0,NULL,12,'','',0,0,NULL,NULL,''),(547119274,38928081,0,NULL,13,'','',0,0,NULL,NULL,''), +(547119275,38928081,0,NULL,14,'','',0,0,NULL,NULL,''),(547119276,38928081,0,NULL,15,'','',0,0,NULL,NULL,''), +(547119277,38928081,0,NULL,16,'','',0,0,NULL,NULL,''),(547119278,38928081,0,NULL,17,'','',0,0,NULL,NULL,''), +(547119279,38928081,0,NULL,18,'','',0,0,NULL,NULL,''),(547119280,38928081,0,NULL,19,'','',0,0,NULL,NULL,''); + +CREATE TABLE t3 ( + old_id int(10) unsigned NOT NULL AUTO_INCREMENT, + old_text mediumblob NOT NULL, + old_flags tinyblob NOT NULL, + PRIMARY KEY (old_id) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +INSERT INTO t3 VALUES +(1,'text-0',''),(2,'text-1000',''),(3,'text-2000',''),(4,'text-3000',''), +(5,'text-4000',''),(6,'text-5000',''),(7,'text-6000',''),(8,'text-7000',''), +(9,'text-8000',''),(10,'text-9000',''),(11,'text-1',''),(12,'text-1001',''), +(13,'text-2001',''),(14,'text-3001',''),(15,'text-4001',''),(16,'text-5001',''), +(17,'text-6001',''),(18,'text-7001',''),(19,'text-8001',''),(20,'text-9001',''), +(21,'text-2',''),(22,'text-1002',''),(23,'text-2002',''),(24,'text-3002',''), +(25,'text-4002',''),(26,'text-5002',''),(27,'text-6002',''),(28,'text-7002',''), +(29,'text-8002',''),(30,'text-9002',''),(31,'text-3',''),(32,'text-1003',''), +(33,'text-2003',''),(34,'text-3003',''),(35,'text-4003',''),(36,'text-5003',''), +(37,'text-6003',''),(38,'text-7003',''),(39,'text-8003',''),(40,'text-9003',''), +(41,'text-4',''),(42,'text-1004',''),(43,'text-2004',''),(44,'text-3004',''), +(45,'text-4004',''),(46,'text-5004',''),(47,'text-6004',''),(48,'text-7004',''), +(49,'text-8004',''),(50,'text-9004',''),(51,'text-5',''),(52,'text-1005',''), +(53,'text-2005',''),(54,'text-3005',''),(55,'text-4005',''),(56,'text-5005',''), +(57,'text-6005',''),(58,'text-7005',''),(59,'text-8005',''),(60,'text-9005',''), +(61,'text-6',''),(62,'text-1006',''),(63,'text-2006',''),(64,'text-3006',''), +(65,'text-4006',''),(66,'text-5006',''),(67,'text-6006',''),(68,'text-7006',''), +(69,'text-8006',''),(70,'text-9006',''),(71,'text-7',''),(72,'text-1007',''), +(73,'text-2007',''),(74,'text-3007',''),(75,'text-4007',''),(76,'text-5007',''), +(77,'text-6007',''),(78,'text-7007',''),(79,'text-8007',''),(80,'text-9007',''), +(81,'text-8',''),(82,'text-1008',''),(83,'text-2008',''),(84,'text-3008',''), +(85,'text-4008',''),(86,'text-5008',''),(87,'text-6008',''),(88,'text-7008',''), +(89,'text-8008',''),(90,'text-9008',''),(91,'text-9',''),(92,'text-1009',''), +(93,'text-2009',''),(94,'text-3009',''),(95,'text-4009',''),(96,'text-5009',''), +(97,'text-6009',''),(98,'text-7009',''),(99,'text-8009',''),(100,'text-9009',''); + + +EXPLAIN +SELECT * FROM t1, t2 IGNORE INDEX (PRIMARY), t3 + WHERE page_id=rev_page AND rev_text_id=old_id AND page_namespace=4 AND page_title='Sandbox' +ORDER BY rev_timestamp ASC LIMIT 10; + +DROP TABLE t1,t2,t3; + +--echo # +--echo # MDEV-5424 SELECT using ORDER BY DESC and LIMIT produces unexpected +--echo # results (InnoDB/XtraDB) +--echo # + +create table t1 (a bigint not null unique auto_increment, b varchar(10), primary key (a), key (b(2))) engine = myisam default character set utf8; +create table t2 (a bigint not null unique auto_increment, b varchar(10), primary key (a), key (b(2))) engine = innodb default character set utf8; + +insert into t1 (b) values (null), (null), (null); +insert into t2 (b) values (null), (null), (null); + +explain select a from t1 where b is null order by a desc limit 2; +select a from t1 where b is null order by a desc limit 2; +explain select a from t2 where b is null order by a desc limit 2; +select a from t2 where b is null order by a desc limit 2; + +explain select a from t2 where b is null order by a desc; +select a from t2 where b is null order by a desc; + +explain select a from t2 where b is null order by a desc,a,a; +select a from t2 where b is null order by a desc,a,a; + +drop table t1, t2; + +--echo # +--echo # MDEV-10325: Queries examines all rows of a tables when it should not +--echo # +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t1 ( + pk int not null, + col1 varchar(32), + filler varchar(100), + key idx1(col1(10)), + primary key (pk) +)engine=innodb; + +insert into t1 +select + A.a + 10*B.a + 100*C.a, + concat('1234567890-', 1000+ A.a + 10*B.a + 100*C.a), + repeat('filler-data-', 4) +from + t0 A, t0 B, t0 C; + +let $q=explain select * from t1 where col1='1234567890-a'; +let $rows=query_get_value($q, rows, 1); +if ($rows < 2) +{ + --echo The EXPLAIN should not produce a query plan with type=ref, rows=1 + --die Fix for MDEV-10325 didnt work; +} + +drop table t0,t1; + +--echo # +--echo # +--echo # MDEV-10360: Extended keys: index properties depend on index order +--echo # +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t1 ( + index_id bigint(20) unsigned NOT NULL, + index_class varchar(265) COLLATE latin1_general_ci DEFAULT NULL , + index_object_id int(10) unsigned NOT NULL DEFAULT '0' , + index_date_updated int(10) unsigned DEFAULT NULL , + + PRIMARY KEY (index_id), + KEY object (index_class(181),index_object_id), + KEY index_date_updated (index_date_updated) +) engine=innodb; + +create table t2 ( + index_id bigint(20) unsigned NOT NULL, + index_class varchar(265) COLLATE latin1_general_ci DEFAULT NULL , + index_object_id int(10) unsigned NOT NULL DEFAULT '0' , + index_date_updated int(10) unsigned DEFAULT NULL , + + PRIMARY KEY (index_id), + KEY index_date_updated (index_date_updated), + KEY object (index_class(181),index_object_id) +) engine=innodb; + +insert into t1 select + @a:=A.a + 10*B.a + 100*C.a, + concat('val-', @a), + 123456, + A.a + 10*B.a +from + t0 A, t0 B, t0 C; + +insert into t2 select * from t1; + +--echo # This must have the same query plan as the query below it: +--echo # type=range, key=index_date_updated, key_len=13 +--replace_column 9 # +explain +select * from t1 force index(index_date_updated) +where index_date_updated= 10 and index_id < 800; + +--echo # This used to work from the start: +--replace_column 9 # +explain +select * from t2 force index(index_date_updated) +where index_date_updated= 10 and index_id < 800; + +drop table t0,t1,t2; + + +--echo # +--echo # MDEV-11196: Error:Run-Time Check Failure #2 - Stack around the variable 'key_buff' +--echo # was corrupted, server crashes in opt_sum_query + +SET @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity,@@optimizer_use_condition_selectivity=1; + +CREATE TABLE t1 ( + pk INT, + f1 VARCHAR(3), + f2 VARCHAR(1024), + PRIMARY KEY (pk), + KEY(f2) +) ENGINE=InnoDB CHARSET utf8 ROW_FORMAT= DYNAMIC; + +INSERT INTO t1 VALUES (1,'foo','abc'),(2,'bar','def'); +SELECT MAX(t2.pk) FROM t1 t2 INNER JOIN t1 t3 ON t2.f1 = t3.f1 WHERE t2.pk <= 4; +drop table t1; + +CREATE TABLE t1 ( + pk1 INT, + pk2 INT, + f1 VARCHAR(3), + f2 VARCHAR(1021), + PRIMARY KEY (pk1,pk2), + KEY(f2) +) ENGINE=InnoDB CHARSET utf8 ROW_FORMAT= DYNAMIC; + +INSERT INTO t1 VALUES (1,2,'2','abc'),(2,3,'3','def'); +explain format= json +select * from t1 force index(f2) where pk1 <= 5 and pk2 <=5 and f2 = 'abc' and f1 <= '3'; +drop table t1; + +CREATE TABLE t1 ( +f2 INT, +pk2 INT, +f1 VARCHAR(3), +pk1 VARCHAR(1000), +PRIMARY KEY (pk1,pk2), +KEY k1(pk1,f2) +) ENGINE=InnoDB CHARSET utf8 ROW_FORMAT= DYNAMIC; +INSERT INTO t1 VALUES (1,2,'2','abc'),(2,3,'3','def'); +explain format= json +select * from t1 force index(k1) where f2 <= 5 and pk2 <=5 and pk1 = 'abc' and f1 <= '3'; +drop table t1; +SET optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; + +--echo # +--echo # MDEV-11172: EXPLAIN shows non-sensical value for key_len with type=index +--echo # + +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +CREATE TABLE t2 ( + pk VARCHAR(50), + a VARCHAR(20), + KEY k1(a), + PRIMARY KEY(pk) +)ENGINE=INNODB; + +INSERT INTO t2 SELECT a,a FROM t1; +--replace_column 9 # +EXPLAIN SELECT pk FROM t2 FORCE INDEX(k1); + +DROP TABLE t1,t2; |