diff options
Diffstat (limited to 'mysql-test/main/innodb_ext_key.result')
| -rw-r--r-- | mysql-test/main/innodb_ext_key.result | 808 |
1 files changed, 808 insertions, 0 deletions
diff --git a/mysql-test/main/innodb_ext_key.result b/mysql-test/main/innodb_ext_key.result new file mode 100644 index 00000000000..686038521f8 --- /dev/null +++ b/mysql-test/main/innodb_ext_key.result @@ -0,0 +1,808 @@ +SET SESSION STORAGE_ENGINE='InnoDB'; +CREATE DATABASE dbt3_s001; +use dbt3_s001; +explain +select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 8 const,const 1 Using index +flush status; +select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01'; +count(*) +1 +show status like 'handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 1 +Handler_read_last 0 +Handler_read_next 1 +Handler_read_prev 0 +Handler_read_retry 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 0 +explain +select count(*) from lineitem +where l_orderkey=130 and l_linenumber=2 and l_shipdate='1992-07-01'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE lineitem const PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 8 const,const 1 +flush status; +select count(*) from lineitem +where l_orderkey=130 and l_linenumber=2 and l_shipdate='1992-07-01'; +count(*) +1 +show status like 'handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 1 +Handler_read_last 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_retry 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 0 +explain +select count(*) from lineitem +where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 8 NULL 1 Using where; Using index +flush status; +select count(*) from lineitem +where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000; +count(*) +1 +show status like 'handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 1 +Handler_read_last 0 +Handler_read_next 1 +Handler_read_prev 0 +Handler_read_retry 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 0 +explain +select l_orderkey, l_linenumber from lineitem +where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 8 NULL 3 Using where; Using index +flush status; +select l_orderkey, l_linenumber from lineitem +where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000; +l_orderkey l_linenumber +1088 3 +1217 1 +1221 3 +show status like 'handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 1 +Handler_read_last 0 +Handler_read_next 3 +Handler_read_prev 0 +Handler_read_retry 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 0 +explain +select min(l_orderkey) from lineitem where l_shipdate='1992-07-01'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +flush status; +select min(l_orderkey) from lineitem where l_shipdate='1992-07-01'; +min(l_orderkey) +130 +show status like 'handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 1 +Handler_read_last 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_retry 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 0 +explain +select min(l_orderkey) from lineitem +where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +flush status; +select min(l_orderkey) from lineitem +where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000; +min(l_orderkey) +1088 +show status like 'handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 1 +Handler_read_last 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_retry 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 0 +explain +select max(l_linenumber) from lineitem +where l_shipdate='1992-07-01' and l_orderkey=130; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +flush status; +select max(l_linenumber) from lineitem +where l_shipdate='1992-07-01' and l_orderkey=130; +max(l_linenumber) +2 +show status like 'handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 1 +Handler_read_last 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_retry 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 0 +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; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE lineitem index_merge i_l_shipdate,i_l_receiptdate i_l_shipdate,i_l_receiptdate 8,8 NULL 2 Using union(i_l_shipdate,i_l_receiptdate); Using where +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; +l_orderkey l_linenumber +130 2 +5603 2 +show status like 'handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 2 +Handler_read_last 0 +Handler_read_next 2 +Handler_read_prev 0 +Handler_read_retry 0 +Handler_read_rnd 2 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 0 +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; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE lineitem index_merge i_l_shipdate,i_l_receiptdate i_l_shipdate,i_l_receiptdate 8,8 NULL 3 Using sort_union(i_l_shipdate,i_l_receiptdate); Using where +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; +l_orderkey l_linenumber +130 2 +5603 2 +5959 3 +show status like 'handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 2 +Handler_read_last 0 +Handler_read_next 3 +Handler_read_prev 0 +Handler_read_retry 0 +Handler_read_rnd 3 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 0 +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; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE lineitem index_merge PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate,i_l_receiptdate 8,8 NULL 3 Using sort_union(i_l_shipdate,i_l_receiptdate); Using where +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; +l_orderkey l_linenumber +130 2 +5603 2 +5959 3 +show status like 'handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 2 +Handler_read_last 0 +Handler_read_next 3 +Handler_read_prev 0 +Handler_read_retry 0 +Handler_read_rnd 3 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 0 +explain +select max(l_orderkey) from lineitem +where l_partkey between 1 and 10 group by l_partkey; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE lineitem range i_l_suppkey_partkey,i_l_partkey i_l_partkey 5 NULL # Using where; Using index for group-by +flush status; +select max(l_orderkey) from lineitem +where l_partkey between 1 and 10 group by l_partkey; +max(l_orderkey) +5984 +5957 +5892 +5856 +5959 +5957 +5794 +5894 +5859 +5632 +show status like 'handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 21 +Handler_read_last 1 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_retry 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 0 +explain +select max(l_orderkey) from lineitem +where l_suppkey in (1,4) group by l_suppkey; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE lineitem range i_l_suppkey i_l_suppkey 5 NULL # Using where; Using index for group-by +flush status; +select max(l_orderkey) from lineitem +where l_suppkey in (1,4) group by l_suppkey; +max(l_orderkey) +5988 +5984 +show status like 'handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 6 +Handler_read_last 1 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_retry 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 0 +create index i_p_retailprice on part(p_retailprice); +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; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE part range i_p_retailprice i_p_retailprice 9 NULL # Using where; Using index +1 SIMPLE orders ref PRIMARY,i_o_orderdate i_o_orderdate 4 const # Using index +1 SIMPLE lineitem ref i_l_partkey i_l_partkey 9 dbt3_s001.part.p_partkey,dbt3_s001.orders.o_orderkey # Using index +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; +o_orderkey p_partkey +5895 200 +show status like 'handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 3 +Handler_read_last 0 +Handler_read_next 3 +Handler_read_prev 0 +Handler_read_retry 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 0 +# +# Bug mdev-3851: ref access used instead of expected eq_ref access +# when extended_keys=on +# +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; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 5 Using where +1 SIMPLE part eq_ref i_p_size i_p_size 9 const,dbt3_s001.t0.a 1 +select * from t0, part ignore index (primary) +where p_partkey=t0.a and p_size=1; +a p_partkey p_name p_mfgr p_brand p_type p_size p_container p_retailprice p_comment +2 2 blush rosy metallic lemon navajo Manufacturer#1 Brand#13 LARGE BRUSHED BRASS 1 LG CASE 902 final platelets hang f +drop table t0; +drop index i_p_size on part; +DROP DATABASE dbt3_s001; +use test; +# +# LP Bug #914560: query containing IN subquery +# + extended_keys = on +# +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); +a b +1 1 +2 2 +EXPLAIN +SELECT * FROM t1 WHERE 2 IN (SELECT MAX(s1.a) FROM t1 AS s1, t1 AS s2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +2 MATERIALIZED s1 ALL NULL NULL NULL NULL 2 +2 MATERIALIZED s2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +DROP TABLE t1; +set optimizer_switch=@save_optimizer_switch; +# +# LP Bug #915291: query using a materialized view +# + extended_keys = on +# (valgrinf complains fixed by the patch for bug #914560) +# +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; +a b c +DROP VIEW v; +DROP TABLE t1,t2,t3; +set optimizer_switch=@save_optimizer_switch; +# +# LP Bug #921167: query containing IN subquery +# + extended_keys = on +# +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); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t index a,b b 7 NULL 10 Using index +1 PRIMARY t1 ref b b 3 test.t.b 2 Using index; Start temporary +1 PRIMARY t2 index NULL PRIMARY 4 NULL 11 Using index; End temporary; Using join buffer (flat, BNL join) +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t.a 1 Using index +SELECT a FROM t1 AS t, t2 +WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b); +a +24 +DROP TABLE t1,t2; +# +# LP Bug #923236: hash join + extended_keys = on +# +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; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE t2 hash_ALL NULL #hash#$hj 5 test.t1.a 2 Using where; Using join buffer (flat, BNLH join) +SELECT * FROM t1, t2 WHERE b=a; +a b +set join_cache_level=@save_join_cache_level; +set optimizer_switch=@save_optimizer_switch; +DROP TABLE t1,t2; +# +# Bug mdev-3888: INSERT with UPDATE on duplicate keys +# with extended_keys=on +# +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; +# +# Bug mdev-4220: using ref instead of eq_ref +# with extended_keys=on +# (performance regression introduced in the patch for mdev-3851) +# +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; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 +1 SIMPLE t2 eq_ref a a 4 test.t1.a 1 Using where +flush status; +select * from t1, t2 where t2.a=t1.a and t2.b < 2; +a pk a b +0 0 0 0 +1 1 1 1 +show status like 'handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 10 +Handler_read_last 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_retry 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 11 +drop table t1,t2; +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; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t3 analyze status OK +explain +select * from t1, t3 where t3.col1=t1.a and t3.col2=t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL # Using where +1 SIMPLE t3 ref col1 col1 8 test.t1.a,test.t1.a # Using index +explain +select * from t1, t3 where t3.col1=t1.a and t3.col2=t1.a and t3.pk1=t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL # Using where +1 SIMPLE t3 ref PRIMARY,col1 col1 12 test.t1.a,test.t1.a,test.t1.a # Using index +drop table t1,t2,t3; +# +# Bug mdev-4340: performance regression with extended_keys=on +# +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; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const PRIMARY,name_title name_title 261 const,const 1 +1 SIMPLE t2 ref page_timestamp page_timestamp 4 const 10 Using where +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.rev_text_id 1 +DROP TABLE t1,t2,t3; +# +# MDEV-5424 SELECT using ORDER BY DESC and LIMIT produces unexpected +# results (InnoDB/XtraDB) +# +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; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index b PRIMARY 8 NULL 3 Using where +select a from t1 where b is null order by a desc limit 2; +a +3 +2 +explain select a from t2 where b is null order by a desc limit 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range b b 9 NULL 3 Using where; Using filesort +select a from t2 where b is null order by a desc limit 2; +a +3 +2 +explain select a from t2 where b is null order by a desc; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index b PRIMARY 8 NULL 3 Using where +select a from t2 where b is null order by a desc; +a +3 +2 +1 +explain select a from t2 where b is null order by a desc,a,a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index b PRIMARY 8 NULL 3 Using where +select a from t2 where b is null order by a desc,a,a; +a +3 +2 +1 +drop table t1, t2; +# +# MDEV-10325: Queries examines all rows of a tables when it should not +# +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; +drop table t0,t1; +# +# +# MDEV-10360: Extended keys: index properties depend on index order +# +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; +# This must have the same query plan as the query below it: +# type=range, key=index_date_updated, key_len=13 +explain +select * from t1 force index(index_date_updated) +where index_date_updated= 10 and index_id < 800; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range index_date_updated index_date_updated 13 NULL # Using index condition +# This used to work from the start: +explain +select * from t2 force index(index_date_updated) +where index_date_updated= 10 and index_id < 800; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range index_date_updated index_date_updated 13 NULL # Using index condition +drop table t0,t1,t2; +# +# MDEV-11196: Error:Run-Time Check Failure #2 - Stack around the variable 'key_buff' +# 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; +MAX(t2.pk) +2 +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'; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "range", + "possible_keys": ["f2"], + "key": "f2", + "key_length": "3070", + "used_key_parts": ["f2", "pk1"], + "rows": 1, + "filtered": 100, + "index_condition": "t1.pk1 <= 5 and t1.pk2 <= 5 and t1.f2 = 'abc'", + "attached_condition": "t1.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'; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "range", + "possible_keys": ["k1"], + "key": "k1", + "key_length": "3011", + "used_key_parts": ["pk1", "f2", "pk2"], + "rows": 1, + "filtered": 100, + "index_condition": "t1.f2 <= 5 and t1.pk2 <= 5 and t1.pk1 = 'abc'", + "attached_condition": "t1.f1 <= '3'" + } + } +} +drop table t1; +SET optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +# +# MDEV-11172: EXPLAIN shows non-sensical value for key_len with type=index +# +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; +EXPLAIN SELECT pk FROM t2 FORCE INDEX(k1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index NULL k1 23 NULL 10 Using index +DROP TABLE t1,t2; |