revision-id: fe35806da7f279b9438d03f733a2c68db3a0a679 (mariadb-10.6.1-365-gfe35806da7f) parent(s): 785fc4acd0346f96248d9cd0643dbf0faac7ddb8 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-03-18 17:21:51 +0300 message: Update test results --- .../rocksdb/r/allow_no_primary_key.result | 4 +- .../mysql-test/rocksdb/r/bloomfilter3.result | 12 ++-- .../mysql-test/rocksdb/r/bloomfilter5.result | 12 ++-- storage/rocksdb/mysql-test/rocksdb/r/index.result | 4 +- .../rocksdb/r/index_merge_rocksdb.result | 12 ++-- .../rocksdb/mysql-test/rocksdb/r/issue290.result | 4 +- .../rocksdb/mysql-test/rocksdb/r/issue884.result | 4 +- .../rocksdb/mysql-test/rocksdb/r/issue896.result | 4 +- .../mysql-test/rocksdb/r/mariadb_port_fixes.result | 12 ++-- .../mysql-test/rocksdb/r/records_in_range.result | 8 +-- .../rocksdb/r/rocksdb_cf_per_partition.result | 24 ++++---- .../mysql-test/rocksdb/r/rocksdb_cf_reverse.result | 16 ++--- .../mysql-test/rocksdb/r/rocksdb_checksums.result | 8 +-- .../mysql-test/rocksdb/r/rocksdb_icp.result | 44 +++++++------- .../mysql-test/rocksdb/r/rocksdb_icp_rev.result | 40 ++++++------- .../mysql-test/rocksdb/r/rocksdb_parts.result | 8 +-- .../mysql-test/rocksdb/r/rocksdb_range.result | 56 +++++++++--------- .../rocksdb/r/type_binary_indexes.result | 16 ++--- .../mysql-test/rocksdb/r/type_bit_indexes.result | 8 +-- .../mysql-test/rocksdb/r/type_blob_indexes.result | 24 ++++---- .../mysql-test/rocksdb/r/type_char_indexes.result | 20 +++---- .../rocksdb/r/type_char_indexes_collation.result | 28 ++++----- .../rocksdb/r/type_date_time_indexes.result | 24 ++++---- .../mysql-test/rocksdb/r/type_decimal.result | 16 ++--- .../mysql-test/rocksdb/r/type_enum_indexes.result | 16 ++--- .../mysql-test/rocksdb/r/type_fixed_indexes.result | 20 +++---- .../mysql-test/rocksdb/r/type_float_indexes.result | 28 ++++----- .../mysql-test/rocksdb/r/type_int_indexes.result | 8 +-- .../mysql-test/rocksdb/r/type_set_indexes.result | 16 ++--- .../mysql-test/rocksdb/r/type_text_indexes.result | 12 ++-- .../mysql-test/rocksdb/r/type_varchar.result | 68 +++++++++++----------- storage/rocksdb/mysql-test/rocksdb/t/index.test | 2 +- storage/rocksdb/mysql-test/rocksdb/t/issue290.test | 2 +- .../mysql-test/rocksdb/t/type_blob_indexes.test | 2 +- 34 files changed, 291 insertions(+), 291 deletions(-) diff --git a/storage/rocksdb/mysql-test/rocksdb/r/allow_no_primary_key.result b/storage/rocksdb/mysql-test/rocksdb/r/allow_no_primary_key.result index bdbdea41a6c..b892a6c7f84 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/allow_no_primary_key.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/allow_no_primary_key.result @@ -242,8 +242,8 @@ SELECT * FROM t1 WHERE a = 2; a b 2 b EXPLAIN SELECT * FROM t1 WHERE a = 2; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL const a a 5 const 1 +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL const a a 5 const 1 100.00 DROP TABLE t1; CREATE TABLE t1 (a INT, b CHAR(8)) ENGINE=rocksdb; SHOW CREATE TABLE t1; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/bloomfilter3.result b/storage/rocksdb/mysql-test/rocksdb/r/bloomfilter3.result index 17c6997f2f5..fc7aeba9ca9 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/bloomfilter3.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/bloomfilter3.result @@ -31,14 +31,14 @@ test.linktable analyze status Engine-independent statistics collected test.linktable analyze status OK flush tables; explain select * from linktable; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE linktable NULL ALL NULL NULL NULL NULL 10000 +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE linktable NULL ALL NULL NULL NULL NULL 10000 100.00 # This must use range(id1_type2), key_len=24 explain select id1, id2, link_type, visibility, data, time, version from linktable FORCE INDEX(`id1_type2`) where id1 = 100 and link_type = 1 and time >= 0 and time <= 9223372036854775807 order by time desc; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE linktable NULL range id1_type2 id1_type2 24 NULL 1000 Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE linktable NULL range id1_type2 id1_type2 24 NULL 1000 100.00 Using where; Using index select variable_value into @c from information_schema.global_status where variable_name='rocksdb_bloom_filter_prefix_checked'; select id1, id2, link_type, visibility, data, time, version from linktable FORCE INDEX(`id1_type2`) where id1 = 100 and link_type = 1 and time >= 0 and time <= 9223372036854775807 order by time desc; id1 id2 link_type visibility data time version @@ -121,8 +121,8 @@ insert into t1 values (21,2,2,0x12FFFFFFFFFF,1); # restart explain select * from t1 where kp0=1 and kp1=1 and kp2=0x12FFFFFFFFFF order by kp3 desc; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ref kp12 kp12 20 const,const,const # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ref kp12 kp12 20 const,const,const # 100.00 Using where; Using index show status like '%rocksdb_bloom_filter_prefix%'; Variable_name Value Rocksdb_bloom_filter_prefix_checked 0 diff --git a/storage/rocksdb/mysql-test/rocksdb/r/bloomfilter5.result b/storage/rocksdb/mysql-test/rocksdb/r/bloomfilter5.result index ee8ee1c5ebb..0bab19967b2 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/bloomfilter5.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/bloomfilter5.result @@ -26,8 +26,8 @@ set global rocksdb_force_flush_memtable_now=1; # Full table scan explain select * from t1 limit 10; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 10000 +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 10000 100.00 select * from t1 limit 10; id1 id2 id3 id4 id5 value value2 1000 2000 2000 10000 10000 1000 aaabbbccc @@ -43,8 +43,8 @@ id1 id2 id3 id4 id5 value value2 # An index scan starting from the end of the table: explain select * from t1 order by id1 desc,id2 desc, id3 desc, id4 desc limit 1; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL index NULL PRIMARY 122 NULL 1 +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index NULL PRIMARY 122 NULL 1 100.00 select * from t1 order by id1 desc,id2 desc, id3 desc, id4 desc limit 1; id1 id2 id3 id4 id5 value value2 1000 2000 2000 10000 10000 1000 aaabbbccc @@ -77,8 +77,8 @@ set global rocksdb_force_flush_memtable_now=1; # An index scan starting from the end of the table: explain select * from t5 order by id1 desc,id2 desc, id3 desc, id4 desc limit 1; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t5 NULL index NULL PRIMARY 122 NULL 1 +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t5 NULL index NULL PRIMARY 122 NULL 1 100.00 select * from t5 order by id1 desc,id2 desc, id3 desc, id4 desc limit 1; id1 id2 id3 id4 id5 value value2 1000 2000 2000 10000 10000 1000 aaabbbccc diff --git a/storage/rocksdb/mysql-test/rocksdb/r/index.result b/storage/rocksdb/mysql-test/rocksdb/r/index.result index 8a7336be7bd..f505bdbfe7f 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/index.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/index.result @@ -84,6 +84,6 @@ key(a) insert into t2 select A.a, FLOOR(A.a/10), A.a from t1 A; # This must have type=range, index=a, and must not have 'Using filesort': explain select * from t2 force index (a) where a=0 and pk>=3 order by pk; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL range a a 8 NULL # Using index condition +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL range a a 8 NULL # FLTRD Using index condition drop table t0,t1,t2; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/index_merge_rocksdb.result b/storage/rocksdb/mysql-test/rocksdb/r/index_merge_rocksdb.result index a0d3f98b081..ecdeda9f9fd 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/index_merge_rocksdb.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/index_merge_rocksdb.result @@ -19,8 +19,8 @@ COUNT(*) 7201 SET GLOBAL rocksdb_force_flush_memtable_now = 1; EXPLAIN UPDATE t1 SET filler1='to be deleted' WHERE key1=100 AND key2=100; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL index_merge key1,key2 key1,key2 5,5 NULL # Using intersect(key1,key2); Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index_merge key1,key2 key1,key2 5,5 NULL # 100.00 Using intersect(key1,key2); Using where UPDATE t1 SET filler1='to be deleted' WHERE key1=100 and key2=100; DROP TABLE t0, t1; create table t1 (key1 int, key2 int, key3 int, key (key1), key (key2), key(key3)) engine=rocksdb; @@ -31,11 +31,11 @@ Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK explain select * from t1 where key1 = 1; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ref key1 key1 5 const # +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ref key1 key1 5 const # 100.00 explain select key1,key2 from t1 where key1 = 1 or key2 = 1; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL index_merge key1,key2 key1,key2 5,5 NULL # Using union(key1,key2); Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index_merge key1,key2 key1,key2 5,5 NULL # 100.00 Using union(key1,key2); Using where select * from t1 where key1 = 1; key1 key2 key3 1 100 100 diff --git a/storage/rocksdb/mysql-test/rocksdb/r/issue290.result b/storage/rocksdb/mysql-test/rocksdb/r/issue290.result index 148c519d2b9..fce15ce8df3 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/issue290.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/issue290.result @@ -23,6 +23,6 @@ insert into linktable (id1, link_type, id2) values (2, 1, 8); insert into linktable (id1, link_type, id2) values (2, 1, 9); insert into linktable (id1, link_type, id2) values (2, 1, 10); explain select id1, id2, link_type, data from linktable force index(primary) where id1=2 and link_type=1 and (id2=1 or id2=2 or id2=3 or id2=4 or id2=5); -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE linktable NULL range PRIMARY PRIMARY 24 NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE linktable NULL range PRIMARY PRIMARY 24 NULL # FLTRD Using where drop table linktable; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/issue884.result b/storage/rocksdb/mysql-test/rocksdb/r/issue884.result index c50e90f66e8..ba87ae30069 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/issue884.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/issue884.result @@ -22,8 +22,8 @@ test.test analyze status Engine-independent statistics collected test.test analyze status OK explain select * from test where d = 10 and a = 10 and b = 2; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE test NULL index_merge PRIMARY,d d,PRIMARY 24,16 NULL # Using intersect(d,PRIMARY); Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE test NULL index_merge PRIMARY,d d,PRIMARY 24,16 NULL # 100.00 Using intersect(d,PRIMARY); Using where select * from test where d = 10 and a = 10 and b = 2; a b c d e f g h i j k l m n 10 2 i 10 950 f g h i j k l m 950 diff --git a/storage/rocksdb/mysql-test/rocksdb/r/issue896.result b/storage/rocksdb/mysql-test/rocksdb/r/issue896.result index 0f35fc8ce2b..435a7366092 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/issue896.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/issue896.result @@ -8,8 +8,8 @@ KEY `d` (`d`) ) ENGINE=ROCKSDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='ttl_duration=1000;ttl_col=u'; INSERT INTO t1 VALUES (100, 'aaabbb', UNIX_TIMESTAMP(), 200); EXPLAIN SELECT COUNT(*) FROM t1 FORCE INDEX(d); -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL index NULL d 9 NULL # Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index NULL d 9 NULL # 100.00 Using index # segfault here without the fix SELECT COUNT(*) FROM t1 FORCE INDEX(d); COUNT(*) diff --git a/storage/rocksdb/mysql-test/rocksdb/r/mariadb_port_fixes.result b/storage/rocksdb/mysql-test/rocksdb/r/mariadb_port_fixes.result index c5411202f5b..0394e724b34 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/mariadb_port_fixes.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/mariadb_port_fixes.result @@ -17,8 +17,8 @@ insert into t1 select 2,1,1,1,0; insert into t1 select 3,1,1,1,0; explain select a from t1 force index(e) where e<10000; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL range e e 5 NULL # Using index condition +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range e e 5 NULL # 100.00 Using index condition select a from t1; a 1 @@ -41,8 +41,8 @@ KEY (a) ) ENGINE=ROCKSDB DEFAULT CHARSET=utf8; insert into t1 values (1,1,1),(2,2,2); explain select a from t1 where a <'zzz'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL range a a 32 NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range a a 32 NULL # 100.00 Using where CREATE TABLE t2( pk int, a varchar(10) NOT NULL, @@ -51,8 +51,8 @@ KEY (a) ) ENGINE=ROCKSDB DEFAULT CHARSET=utf8; insert into t2 values (1,1,1),(2,2,2); explain select a from t2 where a <'zzz'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL range a a 32 NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL range a a 32 NULL # 100.00 Using where drop table t1,t2; set global rocksdb_strict_collation_check=@tmp_rscc; # diff --git a/storage/rocksdb/mysql-test/rocksdb/r/records_in_range.result b/storage/rocksdb/mysql-test/rocksdb/r/records_in_range.result index 6dcfc4c6fd5..29ef4195bb4 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/records_in_range.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/records_in_range.result @@ -183,8 +183,8 @@ insert into linktable values (1,1,3,1,1,1,1,1,1); insert into linktable values (1,1,4,1,1,1,1,1,1); set global rocksdb_force_flush_memtable_now = true; explain select id1, id2, link_type, visibility, data, time, version from linktable where id1 = 1 and link_type = 1 and id2 in (1, 2); -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE linktable NULL range PRIMARY,id1_type PRIMARY 24 NULL 2 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE linktable NULL range PRIMARY,id1_type PRIMARY 24 NULL 2 0.01 Using where drop table linktable; CREATE TABLE `linktable` ( `id1` bigint(20) unsigned NOT NULL DEFAULT '0', @@ -205,7 +205,7 @@ insert into linktable values (1,1,3,1,1,1,1,1,1); insert into linktable values (1,1,4,1,1,1,1,1,1); set global rocksdb_force_flush_memtable_now = true; explain select id1, id2, link_type, visibility, data, time, version from linktable where id1 = 1 and link_type = 1 and id2 in (1, 2); -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE linktable NULL range PRIMARY,id1_type PRIMARY 24 NULL 2 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE linktable NULL range PRIMARY,id1_type PRIMARY 24 NULL 2 0.01 Using where drop table linktable; DROP TABLE t1; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_cf_per_partition.result b/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_cf_per_partition.result index 32bb70a7464..91b3c5a8aa7 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_cf_per_partition.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_cf_per_partition.result @@ -271,11 +271,11 @@ col1 HEX(col2) HEX(col3) col4 HEX(col5) 100 034567 04 1 05 400 089ABC 04 1 05 EXPLAIN PARTITIONS SELECT HEX(col2) FROM t2 where col2 = 0x12345; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 custom_p0 index NULL PRIMARY 332 NULL 3 Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 custom_p0 index NULL PRIMARY 332 NULL 3 100.00 Using where; Using index EXPLAIN PARTITIONS SELECT HEX(col2) FROM t2 where col2 = 0x23456; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 custom_p1 index NULL PRIMARY 332 NULL 2 Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 custom_p1 index NULL PRIMARY 332 NULL 2 100.00 Using where; Using index ALTER TABLE t2 DROP PRIMARY KEY; ALTER TABLE t2 ADD PRIMARY KEY (`col1`,`col2`,`col3`) COMMENT 'custom_p0_cfname=new_cf0;custom_p1_cfname=new_cf1'; set @@global.rocksdb_compact_cf = 'new_cf0'; @@ -289,11 +289,11 @@ new_cf1 INSERT INTO t2 VALUES (500, 0x12345, 0x5, 1, 0x2); INSERT INTO t2 VALUES (700, 0x23456, 0x7, 1, 0x3); EXPLAIN PARTITIONS SELECT HEX(col2) FROM t2 where col2 = 0x12345; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 custom_p0 index NULL PRIMARY 332 NULL 4 Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 custom_p0 index NULL PRIMARY 332 NULL 4 100.00 Using where; Using index EXPLAIN PARTITIONS SELECT HEX(col2) FROM t2 where col2 = 0x23456; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 custom_p1 index NULL PRIMARY 332 NULL 2 Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 custom_p1 index NULL PRIMARY 332 NULL 2 100.00 Using where; Using index SELECT col1, HEX(col2), HEX(col3), col4, HEX(col5) FROM t2; col1 HEX(col2) HEX(col3) col4 HEX(col5) 100 012345 01 1 02 @@ -341,8 +341,8 @@ INSERT INTO t2 VALUES (100, 0x34567, 0x4, 1, 0x5); INSERT INTO t2 VALUES (400, 0x89ABC, 0x4, 1, 0x5); INSERT INTO t2 VALUES (500, 0x6789A, 0x5, 1, 0x7); EXPLAIN PARTITIONS SELECT * FROM t2 WHERE col2 = 0x6789A AND col4 = 1; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 custom_p5 ref col2 col2 74 const,const 1 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 custom_p5 ref col2 col2 74 const,const 1 100.00 Using where ALTER TABLE t2 DROP KEY `col2`; ALTER TABLE t2 ADD KEY (`col3`, `col4`) COMMENT 'custom_p5_cfname=another_cf_for_p5'; SELECT DISTINCT(cf_name) FROM information_schema.rocksdb_cfstats WHERE cf_name='another_cf_for_p5'; @@ -354,8 +354,8 @@ test.t2 analyze status Engine-independent statistics collected test.t2 analyze Warning Engine-independent statistics are not collected for column 'col5' test.t2 analyze status OK EXPLAIN PARTITIONS SELECT * FROM t2 WHERE col3 = 0x4 AND col2 = 0x34567; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 custom_p2 ref col3 col3 258 const # Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 custom_p2 ref col3 col3 258 const # 100.00 Using where DROP TABLE t2; CREATE TABLE `t2` ( `col1` bigint(20) NOT NULL, diff --git a/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_cf_reverse.result b/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_cf_reverse.result index d99b92bc04b..032b201e994 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_cf_reverse.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_cf_reverse.result @@ -35,8 +35,8 @@ pk a b 19 19 19 explain select a from t1 order by a limit 5; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL index NULL a 4 NULL # Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index NULL a 4 NULL # 100.00 Using index select a from t1 order by a limit 5; a 0 @@ -46,8 +46,8 @@ a 4 explain select b from t1 order by b limit 5; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL index NULL b 4 NULL # Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index NULL b 4 NULL # 100.00 Using index select a from t1 order by a limit 5; a 0 @@ -57,8 +57,8 @@ a 4 explain select a from t1 order by a desc limit 5; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL index NULL a 4 NULL # Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index NULL a 4 NULL # 100.00 Using index select a from t1 order by a desc limit 5; a 19 @@ -68,8 +68,8 @@ a 15 explain select b from t1 order by b desc limit 5; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL index NULL b 4 NULL # Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index NULL b 4 NULL # 100.00 Using index select b from t1 order by b desc limit 5; b 19 diff --git a/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_checksums.result b/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_checksums.result index 286f6da8299..5b887ef3e8e 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_checksums.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_checksums.result @@ -93,8 +93,8 @@ set session debug_dbug= "-d,myrocks_simulate_bad_pk_checksum2"; # 3. Check if we catch checksum mismatches for secondary indexes explain select * from t3 force index(a) where a<4; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t3 NULL range a a 5 NULL # Using index condition +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t3 NULL range a a 5 NULL # 100.00 Using index condition select * from t3 force index(a) where a<4; pk a b 1 1 1 @@ -109,8 +109,8 @@ set session debug_dbug= "-d,myrocks_simulate_bad_key_checksum1"; # 4. The same for index-only reads? explain select a from t3 force index(a) where a<4; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t3 NULL range a a 5 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t3 NULL range a a 5 NULL # 100.00 Using where; Using index select a from t3 force index(a) where a<4; a 1 diff --git a/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_icp.result b/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_icp.result index 9b9643e3c90..e84853fc271 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_icp.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_icp.result @@ -17,8 +17,8 @@ insert into t2 select a,a,a,a from t1; # Try a basic case: explain select * from t2 where kp1 between 1 and 10 and mod(kp2,2)=0; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL range kp1 kp1 5 NULL # Using index condition +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL range kp1 kp1 5 NULL # 100.00 Using index condition select * from t2 where kp1 between 1 and 10 and mod(kp2,2)=0; pk kp1 kp2 col1 2 2 2 2 @@ -39,8 +39,8 @@ insert into t3 select a,a/10,a,a from t1; # This must not use ICP: explain select * from t3 where kp1=3 and kp2 like '%foo%'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t3 NULL ref kp1 kp1 5 const # Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t3 NULL ref kp1 kp1 5 const # 100.00 Using where explain format=json select * from t3 where kp1 between 2 and 4 and mod(kp1,3)=0 and kp2 like '%foo%'; EXPLAIN @@ -69,22 +69,22 @@ EXPLAIN # than matched index condition explain select * from t2 where kp1< 3 and kp2+1>50000; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL range kp1 kp1 5 NULL # Using index condition +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL range kp1 kp1 5 NULL # 100.00 Using index condition select * from t2 where kp1< 3 and kp2+1>50000; pk kp1 kp2 col1 explain select * from t2 where kp1< 3 and kp2+1>50000; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL range kp1 kp1 5 NULL # Using index condition +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL range kp1 kp1 5 NULL # 100.00 Using index condition select * from t2 where kp1< 3 and kp2+1>50000; pk kp1 kp2 col1 # Try doing backwards scans # MariaDB: ICP is not supported for reverse scans. explain select * from t2 where kp1 between 1 and 10 and mod(kp2,2)=0 order by kp1 desc; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL range kp1 kp1 5 NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL range kp1 kp1 5 NULL # 100.00 Using where select * from t2 where kp1 between 1 and 10 and mod(kp2,2)=0 order by kp1 desc; pk kp1 kp2 col1 10 10 10 10 @@ -94,8 +94,8 @@ pk kp1 kp2 col1 2 2 2 2 explain select * from t2 where kp1 >990 and mod(kp2,2)=0 order by kp1 desc; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL range kp1 kp1 5 NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL range kp1 kp1 5 NULL # 100.00 Using where select * from t2 where kp1 >990 and mod(kp2,2)=0 order by kp1 desc; pk kp1 kp2 col1 998 998 998 998 @@ -104,8 +104,8 @@ pk kp1 kp2 col1 992 992 992 992 explain select * from t2 where kp1< 3 and kp2+1>50000 order by kp1 desc; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL range kp1 kp1 5 NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL range kp1 kp1 5 NULL # 100.00 Using where select * from t2 where kp1< 3 and kp2+1>50000 order by kp1 desc; pk kp1 kp2 col1 drop table t0,t1,t2,t3; @@ -178,8 +178,8 @@ ROWS_READ_DIFF ROWS_INDEX_FIRST ROWS_INDEX_NEXT ICP_ATTEMPTS ICP_MATCHES # ============== index-only query ============== explain select id1,id2 from t4 force index (id1_id2) where id1=1 and id2 % 10 = 1; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t4 NULL ref id1_id2 id1_id2 5 const # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t4 NULL ref id1_id2 id1_id2 5 const # 100.00 Using where; Using index call save_read_stats(); select id1,id2 from t4 force index (id1_id2) where id1=1 and id2 % 10 = 1; id1 id2 @@ -194,8 +194,8 @@ ICP_MATCHES 0 set optimizer_switch='index_condition_pushdown=off'; explain select * from t4 force index (id1_id2) where id1=1 and id2 % 10 = 1; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t4 NULL ref id1_id2 id1_id2 5 const # Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t4 NULL ref id1_id2 id1_id2 5 const # 100.00 Using where call save_read_stats(); select * from t4 force index (id1_id2) where id1=1 and id2 % 10 = 1; id id1 id2 value value2 @@ -210,8 +210,8 @@ ICP_MATCHES 0 set optimizer_switch='index_condition_pushdown=on'; explain select * from t4 force index (id1_id2) where id1=1 and id2 % 10 = 1; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t4 NULL ref id1_id2 id1_id2 5 const # Using index condition +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t4 NULL ref id1_id2 id1_id2 5 const # 100.00 Using index condition call save_read_stats(); select * from t4 force index (id1_id2) where id1=1 and id2 % 10 = 1; id id1 id2 value value2 @@ -245,8 +245,8 @@ from t0 A, t0 B, t0 C; set @count=0; explain select * from t1 force index(key1) where key1=1; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ref key1 key1 9 const # +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ref key1 key1 9 const # 100.00 set @count_diff =(select (value - @count) from information_schema.rocksdb_perf_context where table_schema=database() and table_name='t1' and stat_type='INTERNAL_KEY_SKIPPED_COUNT'); select * from t1 force index(key1) where key1=1; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_icp_rev.result b/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_icp_rev.result index d6900446faa..521ad2db5bd 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_icp_rev.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_icp_rev.result @@ -17,8 +17,8 @@ insert into t2 select a,a,a,a from t1; # Try a basic case: explain select * from t2 where kp1 between 1 and 10 and mod(kp2,2)=0; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL range kp1 kp1 5 NULL # Using index condition +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL range kp1 kp1 5 NULL # 100.00 Using index condition select * from t2 where kp1 between 1 and 10 and mod(kp2,2)=0; pk kp1 kp2 col1 2 2 2 2 @@ -39,8 +39,8 @@ insert into t3 select a,a/10,a,a from t1; # This must not use ICP: explain select * from t3 where kp1=3 and kp2 like '%foo%'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t3 NULL ref kp1 kp1 5 const # Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t3 NULL ref kp1 kp1 5 const # 100.00 Using where explain format=json select * from t3 where kp1 between 2 and 4 and mod(kp1,3)=0 and kp2 like '%foo%'; EXPLAIN @@ -69,22 +69,22 @@ EXPLAIN # than matched index condition explain select * from t2 where kp1< 3 and kp2+1>50000; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL range kp1 kp1 5 NULL # Using index condition +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL range kp1 kp1 5 NULL # 100.00 Using index condition select * from t2 where kp1< 3 and kp2+1>50000; pk kp1 kp2 col1 explain select * from t2 where kp1< 3 and kp2+1>50000; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL range kp1 kp1 5 NULL # Using index condition +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL range kp1 kp1 5 NULL # 100.00 Using index condition select * from t2 where kp1< 3 and kp2+1>50000; pk kp1 kp2 col1 # Try doing backwards scans # MariaDB: ICP is not supported for reverse scans. explain select * from t2 where kp1 between 1 and 10 and mod(kp2,2)=0 order by kp1 desc; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL range kp1 kp1 5 NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL range kp1 kp1 5 NULL # 100.00 Using where select * from t2 where kp1 between 1 and 10 and mod(kp2,2)=0 order by kp1 desc; pk kp1 kp2 col1 10 10 10 10 @@ -94,8 +94,8 @@ pk kp1 kp2 col1 2 2 2 2 explain select * from t2 where kp1 >990 and mod(kp2,2)=0 order by kp1 desc; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL range kp1 kp1 5 NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL range kp1 kp1 5 NULL # 100.00 Using where select * from t2 where kp1 >990 and mod(kp2,2)=0 order by kp1 desc; pk kp1 kp2 col1 998 998 998 998 @@ -104,8 +104,8 @@ pk kp1 kp2 col1 992 992 992 992 explain select * from t2 where kp1< 3 and kp2+1>50000 order by kp1 desc; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL range kp1 kp1 5 NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL range kp1 kp1 5 NULL # 100.00 Using where select * from t2 where kp1< 3 and kp2+1>50000 order by kp1 desc; pk kp1 kp2 col1 drop table t0,t1,t2,t3; @@ -178,8 +178,8 @@ ROWS_READ_DIFF ROWS_INDEX_FIRST ROWS_INDEX_NEXT ICP_ATTEMPTS ICP_MATCHES # ============== index-only query ============== explain select id1,id2 from t4 force index (id1_id2) where id1=1 and id2 % 10 = 1; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t4 NULL ref id1_id2 id1_id2 5 const # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t4 NULL ref id1_id2 id1_id2 5 const # 100.00 Using where; Using index call save_read_stats(); select id1,id2 from t4 force index (id1_id2) where id1=1 and id2 % 10 = 1; id1 id2 @@ -194,8 +194,8 @@ ICP_MATCHES 0 set optimizer_switch='index_condition_pushdown=off'; explain select * from t4 force index (id1_id2) where id1=1 and id2 % 10 = 1; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t4 NULL ref id1_id2 id1_id2 5 const # Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t4 NULL ref id1_id2 id1_id2 5 const # 100.00 Using where call save_read_stats(); select * from t4 force index (id1_id2) where id1=1 and id2 % 10 = 1; id id1 id2 value value2 @@ -210,8 +210,8 @@ ICP_MATCHES 0 set optimizer_switch='index_condition_pushdown=on'; explain select * from t4 force index (id1_id2) where id1=1 and id2 % 10 = 1; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t4 NULL ref id1_id2 id1_id2 5 const # Using index condition +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t4 NULL ref id1_id2 id1_id2 5 const # 100.00 Using index condition call save_read_stats(); select * from t4 force index (id1_id2) where id1=1 and id2 % 10 = 1; id id1 id2 value value2 diff --git a/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_parts.result b/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_parts.result index 56971f7ccfd..48132a8b47e 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_parts.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_parts.result @@ -48,8 +48,8 @@ insert into t1 values (12,20,20); set @tmp_rfirr= @@rocksdb_force_index_records_in_range; set rocksdb_force_index_records_in_range= 12; explain select * from t1 force index(col1) where col1=10; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0,p1 ref col1 col1 5 const # +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 p0,p1 ref col1 col1 5 const # 100.00 select * from t1 force index(col1) where col1=10; pk col1 col2 1 10 10 @@ -75,8 +75,8 @@ insert into t1 values (11,20,20); insert into t1 values (12,20,20); # The following must use "Using index" explain select pk from t1 force index(col1) where col1=10; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0,p1 ref col1 col1 5 const 2000 Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 p0,p1 ref col1 col1 5 const 2000 100.00 Using index drop table t1; # # Issue #214: subqueries cause crash diff --git a/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_range.result b/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_range.result index 1170ffaa396..e87679c3e85 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_range.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_range.result @@ -20,8 +20,8 @@ insert into t2 select A.a, FLOOR(A.a/10), A.a from t1 A; # Original failure was here: explain select * from t2 force index (a) where a=0; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL ref a a 4 const # +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL ref a a 4 const # 100.00 select * from t2 force index (a) where a=0; pk a b 0 0 0 @@ -37,8 +37,8 @@ pk a b # The rest are for code coverage: explain select * from t2 force index (a) where a=2; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL ref a a 4 const # +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL ref a a 4 const # 100.00 select * from t2 force index (a) where a=2; pk a b 20 2 20 @@ -53,8 +53,8 @@ pk a b 29 2 29 explain select * from t2 force index (a) where a=3 and pk=33; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL const a a 8 const,const # +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL const a a 8 const,const # 100.00 select * from t2 force index (a) where a=3 and pk=33; pk a b 33 3 33 @@ -74,22 +74,22 @@ pk a b # explain select count(*) from t2 force index (a) where a>=0 and a <=1; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL range a a 4 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL range a a 4 NULL # 100.00 Using where; Using index select count(*) from t2 force index (a) where a>=0 and a <=1; count(*) 20 explain select count(*) from t2 force index (a) where a>=-1 and a <=1; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL range a a 4 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL range a a 4 NULL # 100.00 Using where; Using index select count(*) from t2 force index (a) where a>=-1 and a <=1; count(*) 20 explain select * from t2 force index (a) where a=0 and pk>=3; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL range a a 8 NULL # Using index condition +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL range a a 8 NULL # 5.00 Using index condition select * from t2 force index (a) where a=0 and pk>=3; pk a b 3 0 3 @@ -111,15 +111,15 @@ pk a b # explain select count(*) from t2 force index (a) where a>0; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL range a a 4 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL range a a 4 NULL # 100.00 Using where; Using index select count(*) from t2 force index (a) where a>0; count(*) 990 explain select count(*) from t2 force index (a) where a>99; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL range a a 4 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL range a a 4 NULL # 100.00 Using where; Using index select count(*) from t2 force index (a) where a>99; count(*) 0 @@ -129,8 +129,8 @@ select * from t3 where pk>1000000; pk a b explain select count(*) from t2 force index (a) where a=2 and pk>25; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL range a a 8 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL range a a 8 NULL # 5.00 Using where; Using index select count(*) from t2 force index (a) where a=2 and pk>25; count(*) 4 @@ -203,8 +203,8 @@ max(pk) # support ICP over reverse scans. explain select * from t2 where a between 99 and 2000 order by a desc; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL range a a 4 NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL range a a 4 NULL # 100.00 Using where select * from t2 where a between 99 and 2000 order by a desc; pk a b 999 99 999 @@ -257,15 +257,15 @@ key(a,b,c) insert into t4 select pk,pk,pk,pk from t2 where pk < 100; explain select * from t4 where a=1 and b in (1) order by c desc; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t4 NULL ref a a 10 const,const # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t4 NULL ref a a 10 const,const # 100.00 Using where; Using index select * from t4 where a=1 and b in (1) order by c desc; pk a b c 1 1 1 1 explain select * from t4 where a=5 and b in (4) order by c desc; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t4 NULL ref a a 10 const,const # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t4 NULL ref a a 10 const,const # 100.00 Using where; Using index select * from t4 where a=5 and b in (4) order by c desc; pk a b c # HA_READ_PREFIX_LAST for reverse-ordered CF @@ -279,15 +279,15 @@ key(a,b,c) comment 'rev:cf2' insert into t5 select pk,pk,pk,pk from t2 where pk < 100; explain select * from t5 where a=1 and b in (1) order by c desc; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t5 NULL ref a a 10 const,const # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t5 NULL ref a a 10 const,const # 100.00 Using where; Using index select * from t5 where a=1 and b in (1) order by c desc; pk a b c 1 1 1 1 explain select * from t5 where a=5 and b in (4) order by c desc; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t5 NULL ref a a 10 const,const # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t5 NULL ref a a 10 const,const # 100.00 Using where; Using index select * from t5 where a=5 and b in (4) order by c desc; pk a b c drop table t0,t1,t2,t3,t4,t5; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/type_binary_indexes.result b/storage/rocksdb/mysql-test/rocksdb/r/type_binary_indexes.result index fe64c699ec4..a3c3aad38ca 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/type_binary_indexes.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/type_binary_indexes.result @@ -11,8 +11,8 @@ Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_par t1 0 PRIMARY 1 b20 A 1000 NULL NULL LSMTREE NO INSERT INTO t1 (b,b20,v16,v128) VALUES ('a','char1','varchar1a','varchar1b'),('a','char2','varchar2a','varchar2b'),('b','char3','varchar1a','varchar1b'),('c','char4','varchar3a','varchar3b'); EXPLAIN SELECT HEX(b20) FROM t1 ORDER BY b20; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL index NULL PRIMARY 20 NULL # Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index NULL PRIMARY 20 NULL # 100.00 Using index SELECT HEX(b20) FROM t1 ORDER BY b20; HEX(b20) 6368617231000000000000000000000000000000 @@ -20,8 +20,8 @@ HEX(b20) 6368617233000000000000000000000000000000 6368617234000000000000000000000000000000 EXPLAIN SELECT HEX(b20) FROM t1 IGNORE INDEX (PRIMARY) ORDER BY b20 DESC; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # Using filesort +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # 100.00 Using filesort SELECT HEX(b20) FROM t1 ORDER BY b20 DESC; HEX(b20) 6368617234000000000000000000000000000000 @@ -43,8 +43,8 @@ t1 1 v16 1 v16 A 500 10 NULL YES LSMTREE NO INSERT INTO t1 (b,b20,v16,v128,pk) VALUES ('a','char1','varchar1a','varchar1b',1),('a','char2','varchar2a','varchar2b',2),('b','char3','varchar1a','varchar1b',3),('c','char4','varchar3a','varchar3b',4),('d','char5','varchar4a','varchar3b',5),('e','char6','varchar2a','varchar3b',6); INSERT INTO t1 (b,b20,v16,v128,pk) SELECT b,b20,v16,v128,pk+100 FROM t1; EXPLAIN SELECT HEX(SUBSTRING(v16,0,3)) FROM t1 WHERE v16 LIKE 'varchar%'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL range v16 v16 13 NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range v16 v16 13 NULL # 100.00 Using where SELECT HEX(SUBSTRING(v16,7,3)) FROM t1 WHERE v16 LIKE 'varchar%'; HEX(SUBSTRING(v16,7,3)) 723161 @@ -60,8 +60,8 @@ HEX(SUBSTRING(v16,7,3)) 723461 723461 EXPLAIN SELECT HEX(SUBSTRING(v16,0,3)) FROM t1 FORCE INDEX (v16) WHERE v16 LIKE 'varchar%'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL range v16 v16 13 NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range v16 v16 13 NULL # 100.00 Using where SELECT HEX(SUBSTRING(v16,7,3)) FROM t1 FORCE INDEX (v16) WHERE v16 LIKE 'varchar%'; HEX(SUBSTRING(v16,7,3)) 723161 diff --git a/storage/rocksdb/mysql-test/rocksdb/r/type_bit_indexes.result b/storage/rocksdb/mysql-test/rocksdb/r/type_bit_indexes.result index d8ac1e01c0d..1cf6fd4a335 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/type_bit_indexes.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/type_bit_indexes.result @@ -15,8 +15,8 @@ INSERT INTO t1 (a,b,c,d) VALUES (1,100,101,102),(0,12,13,14),(1,13,14,15),(0,101,201,202),(1,1000,1001,1002), (1,0xFFFF,0xFFFFFFFF,0xFFFFFFFFFFFFFFFF); EXPLAIN SELECT b+0 FROM t1 ORDER BY b; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL index NULL PRIMARY 3 NULL # Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index NULL PRIMARY 3 NULL # 100.00 Using index SELECT b+0 FROM t1 ORDER BY b; b+0 11 @@ -48,8 +48,8 @@ INSERT INTO t1 (a,b,c,d,pk) VALUES (1,100,101,102,5),(0,12,13,14,6),(1,13,14,15,7),(0,101,201,202,8),(1,1000,1001,1002,9), (1,0xFFFF,0xFFFFFFFF,0xFFFFFFFFFFFFFFFF,10); EXPLAIN SELECT DISTINCT a+0 FROM t1 ORDER BY a; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # Using temporary; Using filesort +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # 100.00 Using temporary; Using filesort SELECT DISTINCT a+0 FROM t1 ORDER BY a; a+0 0 diff --git a/storage/rocksdb/mysql-test/rocksdb/r/type_blob_indexes.result b/storage/rocksdb/mysql-test/rocksdb/r/type_blob_indexes.result index 7dc1b9fc544..0dcfcfb63ea 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/type_blob_indexes.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/type_blob_indexes.result @@ -26,15 +26,15 @@ INSERT INTO t1 (b,t,m,l) VALUES (REPEAT('b',128),REPEAT('f',128),REPEAT('e',128),REPEAT('d',128)), (REPEAT('c',128),REPEAT('b',128),REPEAT('c',128),REPEAT('e',128)); EXPLAIN SELECT SUBSTRING(b,16) AS f FROM t1 WHERE b IN ('test1','test2') ORDER BY f; -id select_type table partitions type possible_keys key key_len ref rows Extra -# # # # # PRIMARY # # # # # +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +# # # # # PRIMARY # # # # # # SELECT SUBSTRING(b,16) AS f FROM t1 WHERE b IN ('test1','test2') ORDER BY f; f EXPLAIN SELECT SUBSTRING(b,16) AS f FROM t1 USE INDEX () WHERE b IN ('test1','test2') ORDER BY f; -id select_type table partitions type possible_keys key key_len ref rows Extra -# # # # # NULL # # # # # +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +# # # # # NULL # # # # # Using where; Using filesort SELECT SUBSTRING(b,16) AS f FROM t1 USE INDEX () WHERE b IN ('test1','test2') ORDER BY f; f @@ -66,8 +66,8 @@ INSERT INTO t1 (b,t,m,l) VALUES (REPEAT('b',128),REPEAT('f',128),REPEAT('e',128),REPEAT('d',128)), (REPEAT('c',128),REPEAT('b',128),REPEAT('c',128),REPEAT('e',128)); EXPLAIN SELECT SUBSTRING(t,64), SUBSTRING(l,256) FROM t1 WHERE t!=l AND l NOT IN ('test1') ORDER BY t, l DESC; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL range l_t l_t 259 NULL # Using where; Using filesort +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range l_t l_t 259 NULL # 100.00 Using where; Using filesort SELECT SUBSTRING(t,64), SUBSTRING(l,256) FROM t1 WHERE t!=l AND l NOT IN ('test1') ORDER BY t, l DESC; SUBSTRING(t,64) SUBSTRING(l,256) @@ -81,8 +81,8 @@ fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff EXPLAIN SELECT SUBSTRING(t,64), SUBSTRING(l,256) FROM t1 FORCE INDEX (l_t) WHERE t!=l AND l NOT IN ('test1') ORDER BY t, l DESC; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL range l_t l_t 259 NULL # Using where; Using filesort +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range l_t l_t 259 NULL # 100.00 Using where; Using filesort SELECT SUBSTRING(t,64), SUBSTRING(l,256) FROM t1 FORCE INDEX (l_t) WHERE t!=l AND l NOT IN ('test1') ORDER BY t, l DESC; SUBSTRING(t,64) SUBSTRING(l,256) @@ -121,13 +121,13 @@ INSERT INTO t1 (b,t,m,l) VALUES (REPEAT('b',128),REPEAT('f',128),REPEAT('e',128),REPEAT('d',128)), (REPEAT('c',128),REPEAT('b',128),REPEAT('c',128),REPEAT('e',128)); EXPLAIN SELECT SUBSTRING(m,128) AS f FROM t1 WHERE m = 'test1' ORDER BY f DESC; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ref m m 131 const # Using where; Using filesort +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ref m m 131 const # 100.00 Using where; Using filesort SELECT SUBSTRING(m,128) AS f FROM t1 WHERE m = 'test1' ORDER BY f DESC; f EXPLAIN SELECT SUBSTRING(m,128) AS f FROM t1 IGNORE INDEX FOR ORDER BY (m) WHERE m = 'test1' ORDER BY f DESC; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ref m m 131 const # Using where; Using filesort +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ref m m 131 const # 100.00 Using where; Using filesort SELECT SUBSTRING(m,128) AS f FROM t1 IGNORE INDEX FOR ORDER BY (m) WHERE m = 'test1' ORDER BY f DESC; f DROP TABLE t1; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/type_char_indexes.result b/storage/rocksdb/mysql-test/rocksdb/r/type_char_indexes.result index 8e5348c7366..0fb71330ca0 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/type_char_indexes.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/type_char_indexes.result @@ -12,8 +12,8 @@ Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_par t1 0 PRIMARY 1 c20 A 1000 NULL NULL LSMTREE NO INSERT INTO t1 (c,c20,v16,v128) VALUES ('a','char1','varchar1a','varchar1b'),('a','char2','varchar2a','varchar2b'),('b','char3','varchar1a','varchar1b'),('c','char4','varchar3a','varchar3b'); EXPLAIN SELECT c20 FROM t1 ORDER BY c20; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL index NULL PRIMARY 20 NULL # Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index NULL PRIMARY 20 NULL # 100.00 Using index SELECT c20 FROM t1 ORDER BY c20; c20 char1 @@ -21,8 +21,8 @@ char2 char3 char4 EXPLAIN SELECT c20 FROM t1 FORCE INDEX FOR ORDER BY (PRIMARY) ORDER BY c20; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL index NULL PRIMARY 20 NULL # Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index NULL PRIMARY 20 NULL # 100.00 Using index SELECT c20 FROM t1 FORCE INDEX FOR ORDER BY (PRIMARY) ORDER BY c20; c20 char1 @@ -44,8 +44,8 @@ t1 0 PRIMARY 1 pk A 1000 NULL NULL LSMTREE NO t1 1 v16 1 v16 A 500 NULL NULL YES LSMTREE NO INSERT INTO t1 (c,c20,v16,v128,pk) VALUES ('a','char1','varchar1a','varchar1b','1'),('a','char2','varchar2a','varchar2b','2'),('b','char3','varchar1a','varchar1b','3'),('c','char4','varchar3a','varchar3b','4'); EXPLAIN SELECT SUBSTRING(v16,0,3) FROM t1 WHERE v16 LIKE 'varchar%'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL range v16 v16 19 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range v16 v16 19 NULL # 100.00 Using where; Using index SELECT SUBSTRING(v16,7,3) FROM t1 WHERE v16 LIKE 'varchar%'; SUBSTRING(v16,7,3) r1a @@ -53,8 +53,8 @@ r1a r2a r3a EXPLAIN SELECT SUBSTRING(v16,0,3) FROM t1 IGNORE INDEX (v16) WHERE v16 LIKE 'varchar%'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # 100.00 Using where SELECT SUBSTRING(v16,7,3) FROM t1 IGNORE INDEX (v16) WHERE v16 LIKE 'varchar%'; SUBSTRING(v16,7,3) r1a @@ -62,8 +62,8 @@ r1a r2a r3a EXPLAIN SELECT c,c20,v16,v128 FROM t1 WHERE v16 = 'varchar1a' OR v16 = 'varchar3a' ORDER BY v16; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ALL v16 NULL NULL NULL # Using where; Using filesort +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL v16 NULL NULL NULL # 100.00 Using where; Using filesort SELECT c,c20,v16,v128 FROM t1 WHERE v16 = 'varchar1a' OR v16 = 'varchar3a' ORDER BY v16; c c20 v16 v128 a char1 varchar1a varchar1b diff --git a/storage/rocksdb/mysql-test/rocksdb/r/type_char_indexes_collation.result b/storage/rocksdb/mysql-test/rocksdb/r/type_char_indexes_collation.result index 55eff51e679..fe8a432a925 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/type_char_indexes_collation.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/type_char_indexes_collation.result @@ -4,8 +4,8 @@ c varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci, key sk (c)); insert into t (c) values ('☀'), ('ß'); explain select c from t; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t NULL index NULL sk 27 NULL # Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t NULL index NULL sk 27 NULL # 100.00 Using index select c from t; c ß @@ -19,17 +19,17 @@ primary key (id), key sk1 (c1), key sk2 (c2)); explain select hex(c1) from t order by c1; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t NULL index NULL sk1 4 NULL # Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t NULL index NULL sk1 4 NULL # 100.00 Using index explain select hex(c1) from t IGNORE INDEX (sk1) order by c1; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t NULL ALL NULL NULL NULL NULL # Using filesort +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t NULL ALL NULL NULL NULL NULL # 100.00 Using filesort explain select hex(c2) from t order by c2; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t NULL index NULL sk2 2 NULL # Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t NULL index NULL sk2 2 NULL # 100.00 Using index explain select hex(c2) from t IGNORE INDEX (sk1) order by c2; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t NULL index NULL sk2 2 NULL # Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t NULL index NULL sk2 2 NULL # 100.00 Using index truncate t; insert into t (c1, c2) values ('Asdf ', 'Asdf '); Warnings: @@ -66,8 +66,8 @@ insert into t (c1) values ('bbbb '); insert into t (c1) values ('a '); ERROR 23000: Duplicate entry 'a' for key 'sk1' explain select c1 from t; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t NULL ALL NULL NULL NULL NULL # +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t NULL ALL NULL NULL NULL NULL # 100.00 select c1 from t; c1 Asdf @@ -77,8 +77,8 @@ set session rocksdb_verify_row_debug_checksums = on; create table t (id int primary key, email varchar(100), KEY email_i (email(30))) engine=rocksdb default charset=latin1; insert into t values (1, ' a'); explain select 'email_i' as index_name, count(*) AS count from t force index(email_i); -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t NULL ALL NULL NULL NULL NULL # +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t NULL ALL NULL NULL NULL NULL # 100.00 select 'email_i' as index_name, count(*) AS count from t force index(email_i); index_name count email_i 1 diff --git a/storage/rocksdb/mysql-test/rocksdb/r/type_date_time_indexes.result b/storage/rocksdb/mysql-test/rocksdb/r/type_date_time_indexes.result index e3e3c87276f..3745a9fa6fe 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/type_date_time_indexes.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/type_date_time_indexes.result @@ -20,16 +20,16 @@ INSERT INTO t1 (d,dt,ts,t,y) VALUES ('2011-03-31', '2011-08-28 20:33:56', '1997-01-31 11:54:01', '22:04:10', '1994'), (DATE(@tm),@tm,TIMESTAMP(@tm),TIME(@tm),YEAR(@tm)); EXPLAIN SELECT dt FROM t1 ORDER BY dt LIMIT 3; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL index NULL PRIMARY 5 NULL # Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index NULL PRIMARY 5 NULL # 100.00 Using index SELECT dt FROM t1 ORDER BY dt LIMIT 3; dt 2010-11-22 11:43:14 2010-11-22 12:33:54 2011-08-27 21:33:56 EXPLAIN SELECT dt FROM t1 FORCE INDEX FOR ORDER BY (PRIMARY) ORDER BY dt LIMIT 3; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL index NULL PRIMARY 5 NULL # Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index NULL PRIMARY 5 NULL # 100.00 Using index SELECT dt FROM t1 FORCE INDEX FOR ORDER BY (PRIMARY) ORDER BY dt LIMIT 3; dt 2010-11-22 11:43:14 @@ -61,13 +61,13 @@ INSERT INTO t1 (d,dt,ts,t,y,pk) VALUES ('2011-03-31', '2011-08-28 20:33:56', '1997-01-31 11:54:01', '22:04:10', '1994','12:04:00'), (DATE(@tm),@tm,TIMESTAMP(@tm),TIME(@tm),YEAR(@tm),'12:05:00'); EXPLAIN SELECT ts FROM t1 WHERE ts > NOW(); -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL range ts ts 5 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range ts ts 5 NULL # 100.00 Using where; Using index SELECT ts FROM t1 WHERE ts > NOW(); ts EXPLAIN SELECT ts FROM t1 USE INDEX () WHERE ts > NOW(); -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # 100.00 Using where SELECT ts FROM t1 USE INDEX () WHERE ts > NOW(); ts DROP TABLE t1; @@ -94,8 +94,8 @@ INSERT INTO t1 (d,dt,ts,t,y,pk) VALUES ('2011-03-31', '2011-08-28 20:33:56', '1997-01-31 11:54:01', '22:04:10', '1994','22:18:18'), (DATE(@tm),@tm,TIMESTAMP(@tm),TIME(@tm),YEAR(@tm),'23:18:18'); EXPLAIN SELECT y, COUNT(*) FROM t1 GROUP BY y; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL index NULL y 6 NULL # Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index NULL y 6 NULL # 100.00 Using index SELECT y, COUNT(*) FROM t1 GROUP BY y; y COUNT(*) 1994 1 @@ -105,8 +105,8 @@ y COUNT(*) 2001 1 2012 1 EXPLAIN SELECT y, COUNT(*) FROM t1 USE INDEX FOR GROUP BY () GROUP BY y; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL index NULL y 6 NULL # Using index; Using temporary; Using filesort +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index NULL y 6 NULL # 100.00 Using index; Using temporary; Using filesort SELECT y, COUNT(*) FROM t1 USE INDEX FOR GROUP BY () GROUP BY y; y COUNT(*) 1994 1 diff --git a/storage/rocksdb/mysql-test/rocksdb/r/type_decimal.result b/storage/rocksdb/mysql-test/rocksdb/r/type_decimal.result index 99b71e96d4b..4a3cdde60af 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/type_decimal.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/type_decimal.result @@ -29,13 +29,13 @@ test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK # The following can't use index-only: explain select * from t1 where col1 between -8 and 8; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL range key1 key1 3 NULL # Using index condition +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range key1 key1 3 NULL # 100.00 Using index condition # This will use index-only: explain select col1, col2 from t1 where col1 between -8 and 8; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL range key1 key1 3 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range key1 key1 3 NULL # 100.00 Using where; Using index select col1, col2 from t1 where col1 between -8 and 8; col1 col2 0.3 2.5 @@ -46,8 +46,8 @@ insert into t1 values (11, NULL, 0.9, 'row1-with-null'); insert into t1 values (10, -8.4, NULL, 'row2-with-null'); explain select col1, col2 from t1 force index(key1) where col1 is null or col1 < -7; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL range key1 key1 3 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range key1 key1 3 NULL # 100.00 Using where; Using index select col1, col2 from t1 force index(key1) where col1 is null or col1 < -7; col1 col2 NULL 0.9 @@ -93,8 +93,8 @@ test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK explain select col1, col2 from t1 force index(key1) where col1 between -800 and 800; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL range key1 key1 7 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range key1 key1 7 NULL # 100.00 Using where; Using index select col1, col2 from t1 force index(key1) where col1 between -800 and 800; col1 col2 -700.002000 100.006000 diff --git a/storage/rocksdb/mysql-test/rocksdb/r/type_enum_indexes.result b/storage/rocksdb/mysql-test/rocksdb/r/type_enum_indexes.result index 54271c1d0ad..d0f0a00e465 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/type_enum_indexes.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/type_enum_indexes.result @@ -13,8 +13,8 @@ SHOW INDEX IN t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored t1 0 PRIMARY 1 c A 1000 NULL NULL LSMTREE NO EXPLAIN SELECT c FROM t1 WHERE c BETWEEN '1d' AND '6u'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL index PRIMARY PRIMARY 1 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index PRIMARY PRIMARY 1 NULL # 100.00 Using where; Using index SELECT c FROM t1 WHERE c BETWEEN '1d' AND '6u'; c 1d @@ -23,8 +23,8 @@ c 5a 5b EXPLAIN SELECT c FROM t1 USE INDEX () WHERE c BETWEEN '1d' AND '6u'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # 100.00 Using where SELECT c FROM t1 USE INDEX () WHERE c BETWEEN '1d' AND '6u'; c 1d @@ -48,8 +48,8 @@ Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_par t1 0 PRIMARY 1 pk A 1000 NULL NULL LSMTREE NO t1 1 b 1 b A 500 NULL NULL YES LSMTREE NO EXPLAIN SELECT DISTINCT b FROM t1; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL index NULL b 2 NULL # +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index NULL b 2 NULL # 100.00 SELECT DISTINCT b FROM t1; b test1 @@ -57,8 +57,8 @@ test2 test3 test4 EXPLAIN SELECT DISTINCT b FROM t1 IGNORE INDEX (b); -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # Using temporary +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # 100.00 Using temporary SELECT DISTINCT b FROM t1 IGNORE INDEX (b); b test1 diff --git a/storage/rocksdb/mysql-test/rocksdb/r/type_fixed_indexes.result b/storage/rocksdb/mysql-test/rocksdb/r/type_fixed_indexes.result index 34a14058f1e..10150415b9d 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/type_fixed_indexes.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/type_fixed_indexes.result @@ -21,8 +21,8 @@ INSERT INTO t1 (d1,d2,n1,n2) VALUES Warnings: Warning 1264 Out of range value for column 'd1' at row 6 EXPLAIN SELECT d1 FROM t1 ORDER BY d1 DESC; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL index NULL PRIMARY 5 NULL # Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index NULL PRIMARY 5 NULL # 100.00 Using index SELECT d1 FROM t1 ORDER BY d1 DESC; d1 99999999.99 @@ -33,8 +33,8 @@ d1 10.00 0.00 EXPLAIN SELECT d1 FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY) ORDER BY d1 DESC; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL index NULL PRIMARY 5 NULL # Using index; Using filesort +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index NULL PRIMARY 5 NULL # 100.00 Using index; Using filesort SELECT d1 FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY) ORDER BY d1 DESC; d1 99999999.99 @@ -69,8 +69,8 @@ INSERT INTO t1 (d1,d2,n1,n2,pk) VALUES Warnings: Warning 1264 Out of range value for column 'd1' at row 6 EXPLAIN SELECT DISTINCT n1+n2 FROM t1; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL index NULL n1_n2 37 NULL # Using index; Using temporary +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index NULL n1_n2 37 NULL # 100.00 Using index; Using temporary SELECT DISTINCT n1+n2 FROM t1; n1+n2 0.0000 @@ -104,8 +104,8 @@ INSERT INTO t1 (d1,d2,n1,n2,pk) VALUES Warnings: Warning 1264 Out of range value for column 'd1' at row 6 EXPLAIN SELECT d2, COUNT(*) FROM t1 GROUP BY d2; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL index NULL d2 29 NULL # Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index NULL d2 29 NULL # 100.00 Using index SELECT d2, COUNT(*) FROM t1 GROUP BY d2; d2 COUNT(*) 0.0000000000 1 @@ -115,8 +115,8 @@ d2 COUNT(*) 3343303441.0000000000 1 60.1234500000 2 EXPLAIN SELECT d2, COUNT(*) FROM t1 IGNORE INDEX FOR GROUP BY (d2) GROUP BY d2; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL index NULL d2 29 NULL # Using index; Using temporary; Using filesort +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index NULL d2 29 NULL # 100.00 Using index; Using temporary; Using filesort SELECT d2, COUNT(*) FROM t1 IGNORE INDEX FOR GROUP BY (d2) GROUP BY d2; d2 COUNT(*) 0.0000000000 1 diff --git a/storage/rocksdb/mysql-test/rocksdb/r/type_float_indexes.result b/storage/rocksdb/mysql-test/rocksdb/r/type_float_indexes.result index 6c15f543c0b..b372ffcc44d 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/type_float_indexes.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/type_float_indexes.result @@ -17,8 +17,8 @@ INSERT INTO t1 (f,r,d,dp) VALUES (17.5843,4953453454.44,29229114.0,1111111.23), (4644,1422.22,466664.999,0.5); EXPLAIN SELECT f FROM t1 ORDER BY f; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL index NULL PRIMARY 4 NULL # Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index NULL PRIMARY 4 NULL # 100.00 Using index SELECT f FROM t1 ORDER BY f; f -1 @@ -27,8 +27,8 @@ f 17.5843 4644 EXPLAIN SELECT f FROM t1 IGNORE INDEX (PRIMARY) ORDER BY f; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # Using filesort +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # 100.00 Using filesort SELECT f FROM t1 IGNORE INDEX (PRIMARY) ORDER BY f; f -1 @@ -57,8 +57,8 @@ INSERT INTO t1 (f,r,d,dp,pk) VALUES (17.5843,4953453454.44,29229114.0,1111111.23,4), (4644,1422.22,466664.999,0.5,5); EXPLAIN SELECT r, dp FROM t1 WHERE r > 0 or dp > 0; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL index r_dp r_dp 18 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index r_dp r_dp 18 NULL # 100.00 Using where; Using index SELECT r, dp FROM t1 WHERE r > 0 or dp > 0; r dp 1422.220 0.5000000000 @@ -84,8 +84,8 @@ INSERT INTO t1 (f,r,d,dp,pk) VALUES (17.5843,4953453454.44,29229114.0,1111111.23,4), (4644,1422.22,466664.999,0.5,5); EXPLAIN SELECT DISTINCT d FROM t1 ORDER BY d; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL index NULL d 9 NULL # Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index NULL d 9 NULL # 100.00 Using index SELECT DISTINCT d FROM t1 ORDER BY d; d -1 @@ -113,8 +113,8 @@ INSERT INTO t1 (f,r,d,dp,pk) VALUES (17.5843,4953453454.44,29229114.0,1111111.23,4), (4644,1422.22,466664.999,0.5,5); EXPLAIN SELECT DISTINCT d FROM t1 ORDER BY d; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL range NULL d 9 NULL # Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range NULL d 9 NULL # 100.00 Using index for group-by SELECT DISTINCT d FROM t1 ORDER BY d; d -1 @@ -145,8 +145,8 @@ INSERT INTO t1 (f,r,d,dp,pk) VALUES (1.2345,0,0,0,6); ERROR 23000: Duplicate entry '1.2345' for key 'f' EXPLAIN SELECT DISTINCT f FROM t1 ORDER BY f; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL index NULL f 5 NULL # Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index NULL f 5 NULL # 100.00 Using index SELECT DISTINCT f FROM t1 ORDER BY f; f -1 @@ -176,8 +176,8 @@ INSERT INTO t1 (f,r,d,dp,pk) VALUES INSERT INTO t1 (f,r,d,dp,pk) VALUES (1.2345,0,0,0,6); EXPLAIN SELECT DISTINCT f FROM t1 ORDER BY f; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL range NULL f 5 NULL # Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range NULL f 5 NULL # 100.00 Using index for group-by SELECT DISTINCT f FROM t1 ORDER BY f; f -1 diff --git a/storage/rocksdb/mysql-test/rocksdb/r/type_int_indexes.result b/storage/rocksdb/mysql-test/rocksdb/r/type_int_indexes.result index cb0ee4933b4..228e11bf661 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/type_int_indexes.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/type_int_indexes.result @@ -10,8 +10,8 @@ b BIGINT ) ENGINE=rocksdb; INSERT INTO t1 (i,t,s,m,b) VALUES (1,2,3,4,5),(1000,100,10000,1000000,1000000000000000000),(5,100,10000,1000000,100000000000000000),(2,3,4,5,6),(3,4,5,6,7),(101,102,103,104,105),(10001,103,10002,10003,10004),(10,11,12,13,14),(11,12,13,14,15),(12,13,14,15,16); EXPLAIN SELECT i FROM t1 ORDER BY i; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL index NULL PRIMARY 4 NULL # Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index NULL PRIMARY 4 NULL # 100.00 Using index SELECT i FROM t1 ORDER BY i; i 1 @@ -36,8 +36,8 @@ INDEX s_m (s,m) ) ENGINE=rocksdb; INSERT INTO t1 (i,t,s,m,b) VALUES (1,2,3,4,5),(1000,100,10000,1000000,1000000000000000000),(5,100,10000,1000000,100000000000000000),(2,3,4,5,6),(3,4,5,6,7),(101,102,103,104,105),(10001,103,10002,10003,10004),(10,11,12,13,14),(11,12,13,14,15),(12,13,14,15,16); EXPLAIN SELECT s, m FROM t1 WHERE s != 10 AND m != 1; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL index s_m s_m 7 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index s_m s_m 7 NULL # 100.00 Using where; Using index SELECT s, m FROM t1 WHERE s != 10 AND m != 1; s m 10000 1000000 diff --git a/storage/rocksdb/mysql-test/rocksdb/r/type_set_indexes.result b/storage/rocksdb/mysql-test/rocksdb/r/type_set_indexes.result index 435f0458c05..9405bc6f2f8 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/type_set_indexes.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/type_set_indexes.result @@ -20,8 +20,8 @@ INSERT INTO t1 (a,b,c) VALUES Warnings: Warning 1265 Data truncated for column 'b' at row 7 EXPLAIN SELECT c FROM t1 ORDER BY c; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL index NULL PRIMARY 2 NULL # Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index NULL PRIMARY 2 NULL # 100.00 Using index SELECT c FROM t1 ORDER BY c; c @@ -32,8 +32,8 @@ c 01,23,34,44 50 EXPLAIN SELECT c FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY) ORDER BY c; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL index NULL PRIMARY 2 NULL # Using index; Using filesort +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index NULL PRIMARY 2 NULL # 100.00 Using index; Using filesort SELECT c FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY) ORDER BY c; c @@ -70,8 +70,8 @@ INSERT INTO t1 (a,b,c,pk) VALUES Warnings: Warning 1265 Data truncated for column 'b' at row 7 EXPLAIN SELECT a FROM t1 WHERE FIND_IN_SET('Europe',a) > 0; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # 100.00 Using where SELECT a FROM t1 WHERE FIND_IN_SET('Europe',a) > 0; a Africa,Europe,Asia @@ -100,8 +100,8 @@ INSERT INTO t1 (a,b,c,pk) VALUES Warnings: Warning 1265 Data truncated for column 'b' at row 7 EXPLAIN SELECT DISTINCT a, b FROM t1 ORDER BY b DESC, a; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # Using temporary; Using filesort +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # 100.00 Using temporary; Using filesort SELECT DISTINCT a, b FROM t1 ORDER BY b DESC, a; a b test1,test3 diff --git a/storage/rocksdb/mysql-test/rocksdb/r/type_text_indexes.result b/storage/rocksdb/mysql-test/rocksdb/r/type_text_indexes.result index ddb5a2f99f4..4a1274b59a0 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/type_text_indexes.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/type_text_indexes.result @@ -25,15 +25,15 @@ INSERT INTO t1 (t,tt,m,l) VALUES (REPEAT('b',128),REPEAT('f',128),REPEAT('e',128),REPEAT('d',128)), (REPEAT('c',128),REPEAT('b',128),REPEAT('c',128),REPEAT('e',128)); EXPLAIN SELECT SUBSTRING(t,16) AS f FROM t1 WHERE t IN ('test1','test2') ORDER BY f; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL range PRIMARY PRIMARY 34 NULL # Using where; Using filesort +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range PRIMARY PRIMARY 34 NULL # 100.00 Using where; Using filesort SELECT SUBSTRING(t,16) AS f FROM t1 WHERE t IN ('test1','test2') ORDER BY f; f EXPLAIN SELECT SUBSTRING(t,16) AS f FROM t1 IGNORE INDEX (PRIMARY) WHERE t IN ('test1','test2') ORDER BY f; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # Using where; Using filesort +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # 100.00 Using where; Using filesort SELECT SUBSTRING(t,16) AS f FROM t1 IGNORE INDEX (PRIMARY) WHERE t IN ('test1','test2') ORDER BY f; f @@ -75,8 +75,8 @@ INSERT INTO t1 (t,tt,m,l,pk) VALUES (REPEAT('b',128),REPEAT('f',128),REPEAT('e',128),REPEAT('d',128),'8'), (REPEAT('c',128),REPEAT('b',128),REPEAT('c',128),REPEAT('e',128),'9'); EXPLAIN SELECT SUBSTRING(m,128) AS f FROM t1 WHERE m = 'test1' ORDER BY f DESC; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ref m m 131 const # Using where; Using filesort +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ref m m 131 const # 100.00 Using where; Using filesort SELECT SUBSTRING(m,128) AS f FROM t1 WHERE m = 'test1' ORDER BY f DESC; f DROP TABLE t1; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/type_varchar.result b/storage/rocksdb/mysql-test/rocksdb/r/type_varchar.result index 5ebed6f293d..2a40000743f 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/type_varchar.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/type_varchar.result @@ -196,8 +196,8 @@ test.t1 analyze status OK # Must show 'using index' for latin1_bin and utf8_bin: explain select col1, hex(col1) from t1; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL index NULL col1 67 NULL # Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index NULL col1 67 NULL # 100.00 Using index select col1, hex(col1) from t1; col1 hex(col1) a 61202009 @@ -207,8 +207,8 @@ ab 6162 # Must show 'using index' for latin1_bin and utf8_bin: explain select col1, hex(col1) from t1 force index(col1) where col1 < 'b'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL # col1 col1 67 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL # col1 col1 67 NULL # 100.00 Using where; Using index select col1, hex(col1) from t1 force index(col1) where col1 < 'b'; col1 hex(col1) a 61202009 @@ -226,8 +226,8 @@ insert into t1 values(22, repeat(' ',17), '17x-space'); insert into t1 values(23, repeat(' ',18), '18x-space'); explain select pk, col1, hex(col1), length(col1) from t1; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL # NULL col1 67 NULL # Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL # NULL col1 67 NULL # 100.00 Using index select pk, col1, hex(col1), length(col1) from t1; pk col1 hex(col1) length(col1) 10 0 @@ -312,8 +312,8 @@ test.t1 analyze status OK # Must show 'using index' for latin1_bin and utf8_bin: explain select col1, hex(col1) from t1; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL index NULL col1 195 NULL # Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index NULL col1 195 NULL # 100.00 Using index select col1, hex(col1) from t1; col1 hex(col1) a 61202009 @@ -323,8 +323,8 @@ ab 6162 # Must show 'using index' for latin1_bin and utf8_bin: explain select col1, hex(col1) from t1 force index(col1) where col1 < 'b'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL # col1 col1 195 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL # col1 col1 195 NULL # 100.00 Using where; Using index select col1, hex(col1) from t1 force index(col1) where col1 < 'b'; col1 hex(col1) a 61202009 @@ -342,8 +342,8 @@ insert into t1 values(22, repeat(' ',17), '17x-space'); insert into t1 values(23, repeat(' ',18), '18x-space'); explain select pk, col1, hex(col1), length(col1) from t1; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL # NULL col1 195 NULL # Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL # NULL col1 195 NULL # 100.00 Using index select pk, col1, hex(col1), length(col1) from t1; pk col1 hex(col1) length(col1) 10 0 @@ -428,8 +428,8 @@ test.t1 analyze status OK # Must show 'using index' for latin1_bin and utf8_bin: explain select col1, hex(col1) from t1; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # 100.00 select col1, hex(col1) from t1; col1 hex(col1) ab 00610062 @@ -439,8 +439,8 @@ a 0061002000200009 # Must show 'using index' for latin1_bin and utf8_bin: explain select col1, hex(col1) from t1 force index(col1) where col1 < 'b'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL # col1 col1 131 NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL # col1 col1 131 NULL # 100.00 Using where select col1, hex(col1) from t1 force index(col1) where col1 < 'b'; col1 hex(col1) a 0061002000200009 @@ -458,8 +458,8 @@ insert into t1 values(22, repeat(' ',17), '17x-space'); insert into t1 values(23, repeat(' ',18), '18x-space'); explain select pk, col1, hex(col1), length(col1) from t1; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL # NULL NULL NULL NULL # +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL # NULL NULL NULL NULL # 100.00 select pk, col1, hex(col1), length(col1) from t1; pk col1 hex(col1) length(col1) 10 0 @@ -544,8 +544,8 @@ test.t1 analyze status OK # Must show 'using index' for latin1_bin and utf8_bin: explain select col1, hex(col1) from t1; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # 100.00 select col1, hex(col1) from t1; col1 hex(col1) ab 6162 @@ -555,8 +555,8 @@ a 61202009 # Must show 'using index' for latin1_bin and utf8_bin: explain select col1, hex(col1) from t1 force index(col1) where col1 < 'b'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL # col1 col1 259 NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL # col1 col1 259 NULL # 100.00 Using where select col1, hex(col1) from t1 force index(col1) where col1 < 'b'; col1 hex(col1) a 61202009 @@ -574,8 +574,8 @@ insert into t1 values(22, repeat(' ',17), '17x-space'); insert into t1 values(23, repeat(' ',18), '18x-space'); explain select pk, col1, hex(col1), length(col1) from t1; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL # NULL NULL NULL NULL # +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL # NULL NULL NULL NULL # 100.00 select pk, col1, hex(col1), length(col1) from t1; pk col1 hex(col1) length(col1) 10 0 @@ -660,8 +660,8 @@ test.t1 analyze status OK # Must show 'using index' for latin1_bin and utf8_bin: explain select col1, hex(col1) from t1; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # 100.00 select col1, hex(col1) from t1; col1 hex(col1) ab 00610062 @@ -671,8 +671,8 @@ a 0061002000200009 # Must show 'using index' for latin1_bin and utf8_bin: explain select col1, hex(col1) from t1 force index(col1) where col1 < 'b'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL # col1 col1 259 NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL # col1 col1 259 NULL # 100.00 Using where select col1, hex(col1) from t1 force index(col1) where col1 < 'b'; col1 hex(col1) a 0061002000200009 @@ -690,8 +690,8 @@ insert into t1 values(22, repeat(' ',17), '17x-space'); insert into t1 values(23, repeat(' ',18), '18x-space'); explain select pk, col1, hex(col1), length(col1) from t1; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL # NULL NULL NULL NULL # +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL # NULL NULL NULL NULL # 100.00 select pk, col1, hex(col1), length(col1) from t1; pk col1 hex(col1) length(col1) 10 0 @@ -739,8 +739,8 @@ drop table t1; create table t (id int primary key, email varchar(100), KEY email_i (email(30))); insert into t values (1, 'abcabcabcabcabcabcabcabcabcabcabc '); explain select 'email_i' as index_name, count(*) AS count from t force index(email_i); -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t NULL ALL NULL NULL NULL NULL # +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t NULL ALL NULL NULL NULL NULL # 100.00 select 'email_i' as index_name, count(*) AS count from t force index(email_i); index_name count email_i 1 @@ -752,8 +752,8 @@ set session rocksdb_verify_row_debug_checksums = on; create table t (id int primary key, email varchar(100), KEY email_i (email(30))); insert into t values (1, 'a'); explain select 'email_i' as index_name, count(*) AS count from t force index(email_i); -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t NULL ALL NULL NULL NULL NULL # +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t NULL ALL NULL NULL NULL NULL # 100.00 select 'email_i' as index_name, count(*) AS count from t force index(email_i); index_name count email_i 1 diff --git a/storage/rocksdb/mysql-test/rocksdb/t/index.test b/storage/rocksdb/mysql-test/rocksdb/t/index.test index 5eac8835b84..009074a66d4 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/index.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/index.test @@ -37,7 +37,7 @@ create table t2 ( insert into t2 select A.a, FLOOR(A.a/10), A.a from t1 A; --echo # This must have type=range, index=a, and must not have 'Using filesort': ---replace_column 10 # +--replace_column 10 # 11 FLTRD explain select * from t2 force index (a) where a=0 and pk>=3 order by pk; drop table t0,t1,t2; diff --git a/storage/rocksdb/mysql-test/rocksdb/t/issue290.test b/storage/rocksdb/mysql-test/rocksdb/t/issue290.test index 7ce018f19ca..eeb0fdd9f56 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/issue290.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/issue290.test @@ -34,7 +34,7 @@ insert into linktable (id1, link_type, id2) values (2, 1, 8); insert into linktable (id1, link_type, id2) values (2, 1, 9); insert into linktable (id1, link_type, id2) values (2, 1, 10); ---replace_column 10 # +--replace_column 10 # 11 FLTRD explain select id1, id2, link_type, data from linktable force index(primary) where id1=2 and link_type=1 and (id2=1 or id2=2 or id2=3 or id2=4 or id2=5); drop table linktable; diff --git a/storage/rocksdb/mysql-test/rocksdb/t/type_blob_indexes.test b/storage/rocksdb/mysql-test/rocksdb/t/type_blob_indexes.test index 85f1b54bcfe..7a0acff5a12 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/type_blob_indexes.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/type_blob_indexes.test @@ -35,7 +35,7 @@ INSERT INTO t1 (b,t,m,l) VALUES (REPEAT('b',128),REPEAT('f',128),REPEAT('e',128),REPEAT('d',128)), (REPEAT('c',128),REPEAT('b',128),REPEAT('c',128),REPEAT('e',128)); ---replace_column 1 # 2 # 3 # 4 # 5 # 7 # 8 # 9 # 10 # 11 # +--replace_column 1 # 2 # 3 # 4 # 5 # 7 # 8 # 9 # 10 # 11 # 12 # EXPLAIN SELECT SUBSTRING(b,16) AS f FROM t1 WHERE b IN ('test1','test2') ORDER BY f; SELECT SUBSTRING(b,16) AS f FROM t1 WHERE b IN ('test1','test2') ORDER BY f;