revision-id: 27e74f54ec63623065aaf840517c6f92ac5d52a8 (mariadb-10.6.1-363-g27e74f54ec6) parent(s): f203c5ac94cfe2f75c7764e8ab63ed083b2c4197 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-03-18 15:18:29 +0300 message: Test updates #6 --- .../suite/galera/r/galera_many_indexes.result | 12 +- mysql-test/suite/s3/basic.test | 3 +- .../mysql-test/rocksdb/include/group_min_max.inc | 100 ++-- .../mysql-test/rocksdb/include/index_merge1.inc | 96 ++-- .../mysql-test/rocksdb/include/index_merge2.inc | 6 +- .../mysql-test/rocksdb/include/index_merge_ror.inc | 22 +- .../rocksdb/include/index_merge_ror_cpk.inc | 12 +- .../mysql-test/rocksdb/include/rocksdb_icp.inc | 22 +- .../rocksdb/r/allow_no_primary_key.result | 4 +- .../mysql-test/rocksdb/r/bloomfilter5.result | 12 +- .../mysql-test/rocksdb/r/group_min_max.result | 536 ++++++++++----------- storage/rocksdb/mysql-test/rocksdb/r/index.result | 4 +- .../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 | 124 ++--- .../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/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 +- storage/rocksdb/mysql-test/rocksdb/t/index.test | 2 +- storage/rocksdb/mysql-test/rocksdb/t/issue290.test | 2 +- storage/rocksdb/mysql-test/rocksdb/t/issue884.test | 2 +- storage/rocksdb/mysql-test/rocksdb/t/issue896.test | 2 +- .../mysql-test/rocksdb/t/mariadb_port_fixes.test | 6 +- .../mysql-test/rocksdb/t/rocksdb_checksums.test | 4 +- .../rocksdb/mysql-test/rocksdb/t/rocksdb_icp.test | 2 +- .../mysql-test/rocksdb/t/rocksdb_range2.test | 2 +- .../mysql-test/rocksdb/t/type_bit_indexes.test | 8 +- .../mysql-test/rocksdb/t/type_blob_indexes.test | 12 +- .../mysql-test/rocksdb/t/type_char_indexes.test | 18 +- .../rocksdb/t/type_char_indexes_collation.test | 14 +- .../rocksdb/t/type_date_time_indexes.test | 16 +- .../rocksdb/mysql-test/rocksdb/t/type_decimal.test | 8 +- .../mysql-test/rocksdb/t/type_enum_indexes.test | 12 +- .../mysql-test/rocksdb/t/type_fixed_indexes.test | 10 +- .../mysql-test/rocksdb/t/type_float_indexes.test | 14 +- .../mysql-test/rocksdb/t/type_int_indexes.test | 4 +- .../mysql-test/rocksdb/t/type_set_indexes.test | 8 +- .../mysql-test/rocksdb/t/type_text_indexes.test | 6 +- 52 files changed, 720 insertions(+), 721 deletions(-) diff --git a/mysql-test/suite/galera/r/galera_many_indexes.result b/mysql-test/suite/galera/r/galera_many_indexes.result index 963d3552252..440d128b8fc 100644 --- a/mysql-test/suite/galera/r/galera_many_indexes.result +++ b/mysql-test/suite/galera/r/galera_many_indexes.result @@ -73,20 +73,20 @@ SELECT LENGTH(f1) = 767 FROM t1; LENGTH(f1) = 767 1 EXPLAIN SELECT COUNT(*) = 1 FROM t1 FORCE KEY (PRIMARY) WHERE f1 = REPEAT('a', 767); -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 const PRIMARY PRIMARY 769 const 1 Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL const PRIMARY PRIMARY 769 const 1 Using index SELECT COUNT(*) = 1 FROM t1 FORCE KEY (PRIMARY) WHERE f1 = REPEAT('a', 767); COUNT(*) = 1 1 EXPLAIN SELECT COUNT(*) = 1 FROM t1 FORCE KEY (i1) WHERE f1 = REPEAT('a', 767); -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 const i1 i1 769 const 1 Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL const i1 i1 769 const 1 Using index SELECT COUNT(*) = 1 FROM t1 FORCE KEY (i1) WHERE f1 = REPEAT('a', 767); COUNT(*) = 1 1 EXPLAIN SELECT COUNT(*) = 1 FROM t1 FORCE KEY (i63) WHERE f1 = REPEAT('a', 767); -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 const i63 i63 769 const 1 Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL const i63 i63 769 const 1 Using index SELECT COUNT(*) = 1 FROM t1 FORCE KEY (i63) WHERE f1 = REPEAT('a', 767); COUNT(*) = 1 1 diff --git a/mysql-test/suite/s3/basic.test b/mysql-test/suite/s3/basic.test index 99c2d8adb5d..9f73f6da378 100644 --- a/mysql-test/suite/s3/basic.test +++ b/mysql-test/suite/s3/basic.test @@ -18,7 +18,6 @@ insert into t1 select seq, seq+10, repeat(char(65+ mod(seq, 20)),mod(seq,1000)) alter table t1 engine=s3; show create table t1; ---replace_column 2 # 11 # 15 # 16 # 17 # 23 # --replace_result $database database --eval select * from information_schema.tables where table_schema="$database" and table_name="t1"; --replace_column 8 # 12 # 13 # 14 # 19 # @@ -26,7 +25,7 @@ show table status like "t1"; select a,b from t1 limit 10; select count(*) from t1; select a,b from t1 where a between 10 and 20; ---replace_column 9 # +--replace_column 10 # explain select * from t1 where a between 10 and 20; --error ER_OPEN_AS_READONLY insert into t1 values (1,1); diff --git a/storage/rocksdb/mysql-test/rocksdb/include/group_min_max.inc b/storage/rocksdb/mysql-test/rocksdb/include/group_min_max.inc index 40fabce0517..d1863f77ea9 100644 --- a/storage/rocksdb/mysql-test/rocksdb/include/group_min_max.inc +++ b/storage/rocksdb/mysql-test/rocksdb/include/group_min_max.inc @@ -176,7 +176,7 @@ explain select a1, max(a2) from t1 group by a1; explain select a1, min(a2), max(a2) from t1 group by a1; explain select a1, a2, b, min(c), max(c) from t1 group by a1,a2,b; explain select a1,a2,b,max(c),min(c) from t1 group by a1,a2,b; ---replace_column 7 # 9 # +--replace_column 8 # 10 # explain select a1,a2,b,max(c),min(c) from t2 group by a1,a2,b; # Select fields in different order explain select min(a2), a1, max(a2), min(a2), a1 from t1 group by a1; @@ -215,29 +215,29 @@ explain select a1,a2,b,min(c),max(c) from t1 where (a1 = 'b' or a1 = 'd' or a1 = explain select a1,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b; explain select a1, max(c) from t1 where a1 in ('a','b','d') group by a1,a2,b; ---replace_column 9 # +--replace_column 10 # explain select a1,a2,b, max(c) from t2 where a1 < 'd' group by a1,a2,b; ---replace_column 9 # +--replace_column 10 # explain select a1,a2,b,min(c),max(c) from t2 where a1 < 'd' group by a1,a2,b; ---replace_column 9 # +--replace_column 10 # explain select a1,a2,b,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b; ---replace_column 9 # +--replace_column 10 # explain select a1,a2,b, max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b; ---replace_column 9 # +--replace_column 10 # explain select a1, max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b; ---replace_column 9 # +--replace_column 10 # explain select a1,a2,b,min(c),max(c) from t2 where a1 >= 'c' or a2 < 'b' group by a1,a2,b; ---replace_column 9 # +--replace_column 10 # explain select a1,a2,b, max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b; ---replace_column 9 # +--replace_column 10 # explain select a1,a2,b,min(c),max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b; ---replace_column 9 # +--replace_column 10 # explain select a1,a2,b, max(c) from t2 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b; ---replace_column 9 # +--replace_column 10 # explain select a1,a2,b,min(c),max(c) from t2 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b; ---replace_column 9 # +--replace_column 10 # explain select a1,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b; ---replace_column 9 # +--replace_column 10 # explain select a1, max(c) from t2 where a1 in ('a','b','d') group by a1,a2,b; # queries @@ -393,7 +393,7 @@ select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2; # C) Range predicates for the MIN/MAX attribute # plans ---replace_column 9 # +--replace_column 10 # explain select a1,a2,b, max(c) from t1 where (c > 'b1') group by a1,a2,b; explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') group by a1,a2,b; explain select a1,a2,b, max(c) from t1 where (c > 'f123') group by a1,a2,b; @@ -411,35 +411,35 @@ explain select a1,a2,b,min(c),max(c) from t1 where (c < 'c5') or (c = 'g412') or explain select a1,a2,b,min(c),max(c) from t1 where ((c > 'b111') and (c <= 'g112')) or ((c > 'd000') and (c <= 'i110')) group by a1,a2,b; explain select a1,a2,b,min(c),max(c) from t1 where (c between 'b111' and 'g112') or (c between 'd000' and 'i110') group by a1,a2,b; ---replace_column 9 # +--replace_column 10 # explain select a1,a2,b, max(c) from t2 where (c > 'b1') group by a1,a2,b; ---replace_column 9 # +--replace_column 10 # explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') group by a1,a2,b; ---replace_column 9 # +--replace_column 10 # explain select a1,a2,b, max(c) from t2 where (c > 'f123') group by a1,a2,b; ---replace_column 9 # +--replace_column 10 # explain select a1,a2,b,min(c),max(c) from t2 where (c > 'f123') group by a1,a2,b; ---replace_column 9 # +--replace_column 10 # explain select a1,a2,b, max(c) from t2 where (c < 'a0') group by a1,a2,b; ---replace_column 9 # +--replace_column 10 # explain select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') group by a1,a2,b; ---replace_column 9 # +--replace_column 10 # explain select a1,a2,b, max(c) from t2 where (c < 'k321') group by a1,a2,b; ---replace_column 9 # +--replace_column 10 # explain select a1,a2,b,min(c),max(c) from t2 where (c < 'k321') group by a1,a2,b; ---replace_column 9 # +--replace_column 10 # explain select a1,a2,b, max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b; ---replace_column 9 # +--replace_column 10 # explain select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b; ---replace_column 9 # +--replace_column 10 # explain select a1,a2,b, max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; ---replace_column 9 # +--replace_column 10 # explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; ---replace_column 9 # +--replace_column 10 # explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b111') and (c <= 'g112') group by a1,a2,b; ---replace_column 9 # +--replace_column 10 # explain select a1,a2,b,min(c),max(c) from t2 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b; ---replace_column 9 # +--replace_column 10 # explain select a1,a2,b,min(c),max(c) from t2 where ((c > 'b111') and (c <= 'g112')) or ((c > 'd000') and (c <= 'i110')) group by a1,a2,b; # queries @@ -497,17 +497,17 @@ explain select a1,a2,b,min(c) from t1 where ((a1 > 'a') or (a1 < '9')) and ((a2 explain select a1,a2,b,min(c) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; explain select a1,a2,b,min(c) from t1 where (ord(a1) > 97) and (ord(a2) + ord(a1) > 194) and (b = 'c') group by a1,a2,b; ---replace_column 9 # +--replace_column 10 # explain select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; ---replace_column 9 # +--replace_column 10 # explain select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b; ---replace_column 9 # +--replace_column 10 # explain select a1,a2,b,min(c),max(c) from t2 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b; ---replace_column 9 # +--replace_column 10 # explain select a1,a2,b,min(c) from t2 where ((a1 > 'a') or (a1 < '9')) and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c < 'h112') or (c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122')) group by a1,a2,b; ---replace_column 9 # +--replace_column 10 # explain select a1,a2,b,min(c) from t2 where ((a1 > 'a') or (a1 < '9')) and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122') or (c < 'h112') or (c = 'c111')) group by a1,a2,b; ---replace_column 9 # +--replace_column 10 # explain select a1,a2,b,min(c) from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; # queries @@ -538,15 +538,15 @@ explain select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121' explain select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121' or c = 'i121') group by a1,a2,b; explain select a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; ---replace_column 9 # +--replace_column 10 # explain select a1,a2,b from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; ---replace_column 9 # +--replace_column 10 # explain select a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; ---replace_column 9 # +--replace_column 10 # explain select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; ---replace_column 9 # +--replace_column 10 # explain select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121' or c = 'i121') group by a1,a2,b; ---replace_column 9 # +--replace_column 10 # explain select a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; # queries @@ -575,12 +575,12 @@ explain select distinct b from t1 where (a2 >= 'b') and (b = 'a'); explain select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'b'; explain select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'e'; ---replace_column 9 # +--replace_column 10 # explain select distinct a1,a2,b from t2; ---replace_column 9 # +--replace_column 10 # explain select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a'); explain extended select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); ---replace_column 9 # +--replace_column 10 # explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); explain select distinct b from t2 where (a2 >= 'b') and (b = 'a'); explain select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'b'; @@ -627,19 +627,19 @@ explain select distinct b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2 explain select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'b' group by a1; explain select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'e' group by a1; ---replace_column 9 # +--replace_column 10 # explain select distinct a1,a2,b from t2; ---replace_column 9 # +--replace_column 10 # explain select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; ---replace_column 9 # +--replace_column 10 # explain select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; ---replace_column 9 # +--replace_column 10 # explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; ---replace_column 9 # +--replace_column 10 # explain select distinct b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; ---replace_column 9 # +--replace_column 10 # explain select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'b' group by a1; ---replace_column 9 # +--replace_column 10 # explain select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'e' group by a1; # queries diff --git a/storage/rocksdb/mysql-test/rocksdb/include/index_merge1.inc b/storage/rocksdb/mysql-test/rocksdb/include/index_merge1.inc index c1462e7817a..b5952aca286 100644 --- a/storage/rocksdb/mysql-test/rocksdb/include/index_merge1.inc +++ b/storage/rocksdb/mysql-test/rocksdb/include/index_merge1.inc @@ -76,13 +76,13 @@ select * from t0 where key1 < 3 or key2 > 1020; if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select * from t0 where key1 < 2 or key2 <3; if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select * from t0 where (key1 > 30 and key1<35) or (key2 >32 and key2 < 40); @@ -92,31 +92,31 @@ select * from t0 where (key1 > 30 and key1<35) or (key2 >32 and key2 < 40); # 3. Check that index_merge doesn't break "ignore/force/use index" if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select * from t0 ignore index (i2) where key1 < 3 or key2 <4; if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select * from t0 where (key1 < 3 or key2 <4) and key3 = 50; if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select * from t0 use index (i1,i2) where (key1 < 2 or key2 <3) and key3 = 50; if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select * from t0 where (key1 > 1 or key2 > 2); if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select * from t0 force index (i1,i2) where (key1 > 1 or key2 > 2); @@ -124,7 +124,7 @@ explain select * from t0 force index (i1,i2) where (key1 > 1 or key2 > 2); # 4. Check if conjuncts are grouped by keyuse if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select * from t0 where key1<2 or key2<3 or (key1>5 and key1<7) or @@ -134,33 +134,33 @@ explain # verify fallback to "range" if there is only one non-confluent condition if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select * from t0 where key2 = 45 or key1 <=> null; if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select * from t0 where key2 = 45 or key1 is not null; if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select * from t0 where key2 = 45 or key1 is null; # the last conj. is always false and will be discarded if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select * from t0 where key2=10 or key3=3 or key4 <=> null; # the last conj. is always true and will cause 'all' scan if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select * from t0 where key2=10 or key3=3 or key4 is null; @@ -168,14 +168,14 @@ explain select * from t0 where key2=10 or key3=3 or key4 is null; if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select key1 from t0 where (key1 <=> null) or (key2 < 2) or (key3=10) or (key4 <=> null); if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select key1 from t0 where (key1 <=> null) or (key1 < 5) or (key3=10) or (key4 <=> null); @@ -183,14 +183,14 @@ explain select key1 from t0 where (key1 <=> null) or (key1 < 5) or # 6.Several ways to do index_merge, (ignored) index_merge vs. range if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select * from t0 where (key1 < 2 or key2 < 2) and (key3 < 3 or key4 < 3) and (key5 < 5 or key6 < 5); if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select * from t0 where (key1 < 2 or key2 < 4) and (key1 < 5 or key3 < 3); @@ -200,21 +200,21 @@ select * from t0 where (key1 < 2 or key2 < 4) and (key1 < 5 or key3 < 3); if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select * from t0 where (key1 < 3 or key2 < 2) and (key3 < 3 or key4 < 3) and (key5 < 2 or key6 < 2); if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select * from t0 where (key1 < 3 or key2 < 3) and (key3 < 70); if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select * from t0 where (key1 < 3 or key2 < 3) and (key3 < 1000); @@ -224,7 +224,7 @@ explain select * from t0 where # tree_or(List<SEL_IMERGE>, range SEL_TREE). if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select * from t0 where ((key1 < 3 or key2 < 3) and (key2 <4 or key3 < 3)) @@ -233,7 +233,7 @@ explain select * from t0 where if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select * from t0 where ((key1 < 4 or key2 < 4) and (key2 <4 or key3 < 3)) @@ -248,7 +248,7 @@ select * from t0 where # tree_or(List<SEL_IMERGE>, List<SEL_IMERGE>). if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select * from t0 where ((key1 < 2 or key2 < 2) and (key3 <4 or key5 < 3)) @@ -257,7 +257,7 @@ explain select * from t0 where if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select * from t0 where ((key3 <3 or key5 < 4) and (key1 < 3 or key2 < 3)) @@ -266,7 +266,7 @@ explain select * from t0 where if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select * from t0 where ((key3 <3 or key5 < 4) and (key1 < 3 or key2 < 4)) @@ -275,7 +275,7 @@ explain select * from t0 where if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select * from t0 where ((key3 <4 or key5 < 3) and (key1 < 3 or key2 < 3)) @@ -284,7 +284,7 @@ explain select * from t0 where if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select * from t0 where ((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4)) @@ -293,7 +293,7 @@ explain select * from t0 where if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where ((key3 <3 or key5 < 4) and (key1 < 3 or key2 < 3)) @@ -303,7 +303,7 @@ explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where # Can't merge any indexes here (predicate on key3 is always true) if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where ((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4)) @@ -315,7 +315,7 @@ select * from t0 where key1 < 3 or key8 < 2 order by key1; if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select * from t0 where key1 < 3 or key8 < 2 order by key1; @@ -343,21 +343,21 @@ if ($engine_type == RocksDB) # index_merge vs 'index', index_merge is better. if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select key3 from t2 where key1 = 100 or key2 = 100; # index_merge vs 'index', 'index' is better. if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select key3 from t2 where key1 <100 or key2 < 100; # index_merge vs 'all', index_merge is better. if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select key7 from t2 where key1 <100 or key2 < 100; @@ -398,7 +398,7 @@ explain select * from t4 where key2 = 1 and (key2_1 = 1 or key2_2 = 5); if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select * from t4 where key2_1 = 1 or key2_2 = 5; @@ -420,7 +420,7 @@ analyze table t1; # index_merge on first table in join if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select * from t0 left join t1 on (t0.key1=t1.key1) where t0.key1=3 or t0.key2=4; @@ -430,7 +430,7 @@ select * from t0 left join t1 on (t0.key1=t1.key1) if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select * from t0,t1 where (t0.key1=t1.key1) and ( t0.key1=3 or t0.key2=4); @@ -456,7 +456,7 @@ explain select * from t0,t1 where t0.key1 = 5 and # Fix for bug#1974 if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select * from t0,t1 where t0.key1 < 3 and (t1.key1 = t0.key1 or t1.key8 = t0.key1); @@ -550,7 +550,7 @@ set join_buffer_size= 4096; if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) from t0 as A force index(i1,i2), t0 as B force index (i1,i2) @@ -575,7 +575,7 @@ analyze table t0; if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) from t0 as A force index(i1,i2), t0 as B force index (i1,i2) @@ -606,7 +606,7 @@ analyze table t0; if (!$index_merge_random_rows_in_EXPLAIN) { # Too unstable for innodb - --replace_column 9 # + --replace_column 10 # --replace_result "4,4,4,4,4,4,4" X "4,4,4,4,4,4" X "i6,i7" "i6,i7?" "i6" "i6,i7?" explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) from t0 as A, t0 as B @@ -662,13 +662,13 @@ analyze table t1; if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select * from t1 WHERE cola = 'foo' AND colb = 'bar'; if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select * from t1 force index(cola,colb) WHERE cola = 'foo' AND colb = 'bar'; drop table t1; @@ -707,9 +707,9 @@ analyze table t3; -- enable_result_log -- enable_query_log ---replace_column 9 # +--replace_column 10 # explain select * from t1 where a=1 and b=1; ---replace_column 9 # +--replace_column 10 # explain select * from t3 where a=1 and b=1; drop table t3; @@ -791,7 +791,7 @@ analyze table t2; -- enable_query_log --echo must use sort-union rather than union: ---replace_column 9 # +--replace_column 10 # explain select * from t1 where a=4 or b=4; --sorted_result select * from t1 where a=4 or b=4; @@ -799,7 +799,7 @@ select * from t1 where a=4 or b=4; select * from t1 ignore index(a,b) where a=4 or b=4; --echo must use union, not sort-union: ---replace_column 9 # +--replace_column 10 # explain select * from t2 where a=4 or b=4; --sorted_result select * from t2 where a=4 or b=4; @@ -852,7 +852,7 @@ analyze table t3; if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select * from t1 where exists (select 1 from t2, t3 @@ -891,7 +891,7 @@ analyze table t1; if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } EXPLAIN SELECT * FROM t1 FORCE INDEX(a,b) WHERE a LIKE 'a%' OR b LIKE 'b%' diff --git a/storage/rocksdb/mysql-test/rocksdb/include/index_merge2.inc b/storage/rocksdb/mysql-test/rocksdb/include/index_merge2.inc index 7e5cec40a80..9235e044d6d 100644 --- a/storage/rocksdb/mysql-test/rocksdb/include/index_merge2.inc +++ b/storage/rocksdb/mysql-test/rocksdb/include/index_merge2.inc @@ -167,7 +167,7 @@ if (!$skip_ror_EXPLAIN_for_MyRocks) { if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select count(*) from t1 where key1a = 2 and key1b is null and key2a = 2 and key2b is null; @@ -180,7 +180,7 @@ if (!$skip_ror_EXPLAIN_for_MyRocks) { if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select count(*) from t1 where key1a = 2 and key1b is null and key3a = 2 and key3b is null; @@ -407,7 +407,7 @@ if (!$skip_ror_EXPLAIN_for_MyRocks) { if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } # to test the bug, the following must use "sort_union": explain select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40); diff --git a/storage/rocksdb/mysql-test/rocksdb/include/index_merge_ror.inc b/storage/rocksdb/mysql-test/rocksdb/include/index_merge_ror.inc index 21219d1aa95..13f734ce81b 100644 --- a/storage/rocksdb/mysql-test/rocksdb/include/index_merge_ror.inc +++ b/storage/rocksdb/mysql-test/rocksdb/include/index_merge_ror.inc @@ -129,7 +129,7 @@ analyze table t1; -- enable_query_log # One row results tests for cases where a single row matches all conditions ---replace_column 9 # +--replace_column 10 # explain select key1,key2 from t1 where key1=100 and key2=100; select key1,key2 from t1 where key1=100 and key2=100; if (!$skip_ror_EXPLAIN_for_MyRocks) @@ -323,68 +323,68 @@ if (!$skip_ror_EXPLAIN_for_MyRocks) # Different value on 32 and 64 bit if ($random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } --replace_result sta_swt12a sta_swt21a sta_swt12a, sta_swt12a, explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1; if ($random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select * from t1 where st_b=1 and swt1b=1 and swt2b=1; if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1; if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select * from t1 ignore index (sta_swt21a, stb_swt1a_2b) where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1; if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select * from t1 ignore index (sta_swt21a, sta_swt12a, stb_swt1a_2b) where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1; if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select * from t1 ignore index (sta_swt21a, sta_swt12a, stb_swt1a_2b, stb_swt1b) where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1; if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1; if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select * from t1 where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1; if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select st_a from t1 where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1; if ($index_merge_random_rows_in_EXPLAIN) { - --replace_column 9 # + --replace_column 10 # } explain select st_a from t1 where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1; diff --git a/storage/rocksdb/mysql-test/rocksdb/include/index_merge_ror_cpk.inc b/storage/rocksdb/mysql-test/rocksdb/include/index_merge_ror_cpk.inc index f0d18a50bff..5d2de560119 100644 --- a/storage/rocksdb/mysql-test/rocksdb/include/index_merge_ror_cpk.inc +++ b/storage/rocksdb/mysql-test/rocksdb/include/index_merge_ror_cpk.inc @@ -79,7 +79,7 @@ analyze table t1; # Verify that range scan on CPK is ROR # (use index_intersection because it is impossible to check that for index union) # Column 9, rows, can change depending on innodb-page-size. ---replace_column 9 ROWS +--replace_column 10 ROWS explain select * from t1 where pk1 = 1 and pk2 < 80 and key1=0; # CPK scan + 1 ROR range scan is a special case --sorted_result @@ -92,10 +92,10 @@ select pk1,pk2 from t1 where key1 = 10 and key2=10 and 2*pk1+1 < 2*96+1; # Verify that CPK is always used for index intersection scans # (this is because it is used as a filter, not for retrieval) # The expected number of rows can vary depending on page size ---replace_column 9 ROWS +--replace_column 10 ROWS explain select * from t1 where badkey=1 and key1=10; # The expected number of rows can vary depending on page size ---replace_column 9 ROWS +--replace_column 10 ROWS explain select * from t1 where pk1 < 7500 and key1 = 10; # Verify that keys with 'tails' of PK members are ok. @@ -105,7 +105,7 @@ explain select * from t1 where pktail2ok=1 and key1=10; # Note: The following is actually a deficiency, it uses sort_union currently. # This comment refers to InnoDB and is probably not valid for other engines. # The expected number of rows can vary depending on page size ---replace_column 9 ROWS +--replace_column 10 ROWS explain select * from t1 where (pktail2ok=1 and pk1< 50000) or key1=10; # The expected column used for KEY vary depending on page size @@ -113,10 +113,10 @@ explain select * from t1 where (pktail2ok=1 and pk1< 50000) or key1=10; --replace_column 6 EITHER_KEY 9 ROWS explain select * from t1 where pktail3bad=1 and key1=10; # The expected column used for KEY vary depending on page size ---replace_column 9 ROWS +--replace_column 10 ROWS explain select * from t1 where pktail4bad=1 and key1=10; # The expected column used for KEY vary depending on page size ---replace_column 9 ROWS +--replace_column 10 ROWS explain select * from t1 where pktail5bad=1 and key1=10; # Test for problem with innodb key values prefetch buffer: diff --git a/storage/rocksdb/mysql-test/rocksdb/include/rocksdb_icp.inc b/storage/rocksdb/mysql-test/rocksdb/include/rocksdb_icp.inc index c76b52d4cc1..f8e41412433 100644 --- a/storage/rocksdb/mysql-test/rocksdb/include/rocksdb_icp.inc +++ b/storage/rocksdb/mysql-test/rocksdb/include/rocksdb_icp.inc @@ -26,7 +26,7 @@ create table t2 ( insert into t2 select a,a,a,a from t1; --echo # Try a basic case: ---replace_column 9 # +--replace_column 10 # explain select * from t2 where kp1 between 1 and 10 and mod(kp2,2)=0; select * from t2 where kp1 between 1 and 10 and mod(kp2,2)=0; @@ -45,22 +45,22 @@ create table t3 ( insert into t3 select a,a/10,a,a from t1; --echo # This must not use ICP: ---replace_column 9 # +--replace_column 10 # explain select * from t3 where kp1=3 and kp2 like '%foo%'; ---replace_column 9 # +--replace_column 10 # explain format=json select * from t3 where kp1 between 2 and 4 and mod(kp1,3)=0 and kp2 like '%foo%'; --echo # Check that we handle the case where out-of-range is encountered sooner --echo # than matched index condition ---replace_column 9 # +--replace_column 10 # explain select * from t2 where kp1< 3 and kp2+1>50000; select * from t2 where kp1< 3 and kp2+1>50000; ---replace_column 9 # +--replace_column 10 # explain select * from t2 where kp1< 3 and kp2+1>50000; select * from t2 where kp1< 3 and kp2+1>50000; @@ -68,17 +68,17 @@ select * from t2 where kp1< 3 and kp2+1>50000; --echo # Try doing backwards scans --echo # MariaDB: ICP is not supported for reverse scans. ---replace_column 9 # +--replace_column 10 # explain select * from t2 where kp1 between 1 and 10 and mod(kp2,2)=0 order by kp1 desc; select * from t2 where kp1 between 1 and 10 and mod(kp2,2)=0 order by kp1 desc; ---replace_column 9 # +--replace_column 10 # explain select * from t2 where kp1 >990 and mod(kp2,2)=0 order by kp1 desc; select * from t2 where kp1 >990 and mod(kp2,2)=0 order by kp1 desc; ---replace_column 9 # +--replace_column 10 # explain select * from t2 where kp1< 3 and kp2+1>50000 order by kp1 desc; select * from t2 where kp1< 3 and kp2+1>50000 order by kp1 desc; @@ -168,7 +168,7 @@ call get_read_stats(); --echo # ============== index-only query ============== ---replace_column 9 # +--replace_column 10 # explain select id1,id2 from t4 force index (id1_id2) where id1=1 and id2 % 10 = 1; call save_read_stats(); @@ -177,7 +177,7 @@ query_vertical call get_read_stats(); --echo # ============== Query without ICP ============== set optimizer_switch='index_condition_pushdown=off'; ---replace_column 9 # +--replace_column 10 # explain select * from t4 force index (id1_id2) where id1=1 and id2 % 10 = 1; call save_read_stats(); @@ -186,7 +186,7 @@ query_vertical call get_read_stats(); --echo # ============== Query with ICP ============== set optimizer_switch='index_condition_pushdown=on'; ---replace_column 9 # +--replace_column 10 # explain select * from t4 force index (id1_id2) where id1=1 and id2 % 10 = 1; call save_read_stats(); 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 96d25b2e669..bdbdea41a6c 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 const a a 5 const 1 +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL const a a 5 const 1 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/bloomfilter5.result b/storage/rocksdb/mysql-test/rocksdb/r/bloomfilter5.result index daf4f5e30ba..ee8ee1c5ebb 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 10000 +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 10000 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 122 NULL 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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t5 index NULL PRIMARY 122 NULL 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 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/group_min_max.result b/storage/rocksdb/mysql-test/rocksdb/r/group_min_max.result index a070ba91a55..291d5939a57 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/group_min_max.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/group_min_max.result @@ -1016,26 +1016,26 @@ a h112 a111 b h212 a211 c h312 a311 explain select a1,a2,b,min(c) from t2 where (a2 = 'a') and b is NULL group by a1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range NULL idx_t2_1 163 NULL 6 Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL range NULL idx_t2_1 163 NULL 6 Using where; Using index for group-by explain select a1,a2,b,min(c) from t2 where (a2 = 'a' or a2 = 'b') and b is NULL group by a1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL index NULL idx_t2_1 163 NULL 164 Using where; Using index explain select a1,a2,b,max(c) from t2 where (a2 = 'a') and b is NULL group by a1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range NULL idx_t2_1 146 NULL 6 Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL range NULL idx_t2_1 146 NULL 6 Using where; Using index for group-by explain select a1,a2,b,max(c) from t2 where (a2 = 'a' or a2 = 'b') and b is NULL group by a1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL index NULL idx_t2_1 163 NULL 164 Using where; Using index explain select a1,a2,b,min(c) from t2 where b is NULL group by a1,a2; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range NULL idx_t2_1 163 NULL 10 Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL range NULL idx_t2_1 163 NULL 10 Using where; Using index for group-by explain select a1,a2,b,max(c) from t2 where b is NULL group by a1,a2; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range NULL idx_t2_1 146 NULL 10 Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL range NULL idx_t2_1 146 NULL 10 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range NULL idx_t2_1 163 NULL 10 Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL range NULL idx_t2_1 163 NULL 10 Using where; Using index for group-by select a1,a2,b,min(c) from t2 where (a2 = 'a') and b is NULL group by a1; a1 a2 b min(c) a a NULL a777 @@ -1069,98 +1069,98 @@ a1 a2 b min(c) max(c) a a NULL a777 a999 c a NULL c777 c999 explain select a1,a2,b, max(c) from t1 where (c > 'b1') group by a1,a2,b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 147 NULL # Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range NULL idx_t1_1 147 NULL # Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') group by a1,a2,b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by explain select a1,a2,b, max(c) from t1 where (c > 'f123') group by a1,a2,b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t1 where (c > 'f123') group by a1,a2,b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by explain select a1,a2,b, max(c) from t1 where (c < 'a0') group by a1,a2,b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') group by a1,a2,b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by explain select a1,a2,b, max(c) from t1 where (c < 'k321') group by a1,a2,b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t1 where (c < 'k321') group by a1,a2,b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by explain select a1,a2,b, max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by explain select a1,a2,b, max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b111') and (c <= 'g112') group by a1,a2,b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t1 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t1 where ((c > 'b111') and (c <= 'g112')) or ((c > 'd000') and (c <= 'i110')) group by a1,a2,b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t1 where (c between 'b111' and 'g112') or (c between 'd000' and 'i110') group by a1,a2,b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by explain select a1,a2,b, max(c) from t2 where (c > 'b1') group by a1,a2,b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL range NULL idx_t2_1 146 NULL # Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') group by a1,a2,b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL range NULL idx_t2_1 163 NULL # Using where; Using index for group-by explain select a1,a2,b, max(c) from t2 where (c > 'f123') group by a1,a2,b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL range NULL idx_t2_1 146 NULL # Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t2 where (c > 'f123') group by a1,a2,b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL range NULL idx_t2_1 163 NULL # Using where; Using index for group-by explain select a1,a2,b, max(c) from t2 where (c < 'a0') group by a1,a2,b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL range NULL idx_t2_1 163 NULL # Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') group by a1,a2,b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL range NULL idx_t2_1 163 NULL # Using where; Using index for group-by explain select a1,a2,b, max(c) from t2 where (c < 'k321') group by a1,a2,b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL range NULL idx_t2_1 163 NULL # Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t2 where (c < 'k321') group by a1,a2,b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL range NULL idx_t2_1 163 NULL # Using where; Using index for group-by explain select a1,a2,b, max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL range NULL idx_t2_1 163 NULL # Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL range NULL idx_t2_1 163 NULL # Using where; Using index for group-by explain select a1,a2,b, max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL range NULL idx_t2_1 146 NULL # Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL range NULL idx_t2_1 163 NULL # Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b111') and (c <= 'g112') group by a1,a2,b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL range NULL idx_t2_1 163 NULL # Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t2 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL range NULL idx_t2_1 163 NULL # Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t2 where ((c > 'b111') and (c <= 'g112')) or ((c > 'd000') and (c <= 'i110')) group by a1,a2,b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL range NULL idx_t2_1 163 NULL # Using where; Using index for group-by select a1,a2,b, max(c) from t1 where (c > 'b1') group by a1,a2,b; a1 a2 b max(c) a a a d111 @@ -2240,8 +2240,8 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a1` = 'b' or `test`.`t1`.`a1` = 'd' or `test`.`t1`.`a1` = 'a' or `test`.`t1`.`a1` = 'c') and `test`.`t1`.`a2` > 'a' and `test`.`t1`.`c` > 'a111' group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b` explain select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL index NULL idx_t2_1 163 NULL 164 Using where; Using index select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1; a1 a2 min(b) c a a a a111 @@ -2549,49 +2549,49 @@ Handler_read_next 0 Handler_read_retry 0 EXPLAIN (SELECT max(b), a FROM t1 GROUP BY a) UNION (SELECT max(b), a FROM t1 GROUP BY a); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range NULL a 5 NULL 6 Using index for group-by -2 UNION t1 range NULL a 5 NULL 6 Using index for group-by -NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL +id select_type table partitions type possible_keys key key_len ref rows Extra +1 PRIMARY t1 NULL range NULL a 5 NULL 6 Using index for group-by +2 UNION t1 NULL range NULL a 5 NULL 6 Using index for group-by +NULL UNION RESULT <union1,2> NULL ALL NULL NULL NULL NULL NULL EXPLAIN SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x FROM t1 AS t1_outer; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1_outer index NULL a 10 NULL 15 Using index -2 SUBQUERY t1 range a a 5 NULL 6 Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 PRIMARY t1_outer NULL index NULL a 10 NULL 15 Using index +2 SUBQUERY t1 NULL range a a 5 NULL 6 Using where; Using index for group-by EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE EXISTS (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1_outer index NULL a 10 NULL 15 Using index -2 SUBQUERY t1 index NULL a 10 NULL 15 Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 PRIMARY t1_outer NULL index NULL a 10 NULL 15 Using index +2 SUBQUERY t1 NULL index NULL a 10 NULL 15 Using index EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE -2 SUBQUERY t1 range a a 5 NULL 6 Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY t1 NULL range a a 5 NULL 6 Using where; Using index for group-by EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 6 -1 PRIMARY t1_outer ref a a 5 <subquery2>.max(b) 3 Using index -2 MATERIALIZED t1 range a a 5 NULL 6 Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> NULL ALL distinct_key NULL NULL NULL 6 +1 PRIMARY t1_outer NULL ref a a 5 <subquery2>.max(b) 3 Using index +2 MATERIALIZED t1 NULL range a a 5 NULL 6 Using where; Using index for group-by EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1_outer range NULL a 5 NULL 6 Using index for group-by -2 SUBQUERY t1 range a a 5 NULL 6 Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 PRIMARY t1_outer NULL range NULL a 5 NULL 6 Using index for group-by +2 SUBQUERY t1 NULL range a a 5 NULL 6 Using where; Using index for group-by EXPLAIN SELECT 1 FROM t1 AS t1_outer1 JOIN t1 AS t1_outer2 ON t1_outer1.a = (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) AND t1_outer1.b = t1_outer2.b; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1_outer2 index NULL a 10 NULL 15 Using where; Using index -1 PRIMARY t1_outer1 ref a a 10 const,test.t1_outer2.b 1 Using where; Using index -2 SUBQUERY t1 range a a 5 NULL 6 Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 PRIMARY t1_outer2 NULL index NULL a 10 NULL 15 Using where; Using index +1 PRIMARY t1_outer1 NULL ref a a 10 const,test.t1_outer2.b 1 Using where; Using index +2 SUBQUERY t1 NULL range a a 5 NULL 6 Using where; Using index for group-by EXPLAIN SELECT (SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x FROM t1 AS t1_outer) x2 FROM t1 AS t1_outer2; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1_outer2 index NULL a 10 NULL 15 Using index -2 SUBQUERY t1_outer index NULL a 10 NULL 15 Using index -3 SUBQUERY t1 range a a 5 NULL 6 Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 PRIMARY t1_outer2 NULL index NULL a 10 NULL 15 Using index +2 SUBQUERY t1_outer NULL index NULL a 10 NULL 15 Using index +3 SUBQUERY t1 NULL range a a 5 NULL 6 Using where; Using index for group-by CREATE TABLE t3 LIKE t1; FLUSH STATUS; INSERT INTO t3 SELECT a,MAX(b) FROM t1 GROUP BY a; @@ -2635,16 +2635,16 @@ Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK EXPLAIN SELECT DISTINCT(a) FROM t1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx 5 NULL 4 Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range NULL idx 5 NULL 4 Using index for group-by SELECT DISTINCT(a) FROM t1; a 1 2 4 EXPLAIN SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx 5 NULL 4 Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range NULL idx 5 NULL 4 Using index for group-by SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1; a 1 @@ -2661,8 +2661,8 @@ test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK EXPLAIN SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 12 Using temporary; Using filesort +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 12 Using temporary; Using filesort SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC; a MIN(b) MAX(b) 4 1 3 @@ -2672,8 +2672,8 @@ a MIN(b) MAX(b) CREATE INDEX break_it ON t1 (a, b); EXPLAIN SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL break_it 10 NULL 4 Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range NULL break_it 10 NULL 4 Using index for group-by SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a; a MIN(b) MAX(b) 1 1 3 @@ -2682,8 +2682,8 @@ a MIN(b) MAX(b) 4 1 3 EXPLAIN SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL break_it 10 NULL 4 Using index for group-by; Using temporary; Using filesort +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range NULL break_it 10 NULL 4 Using index for group-by; Using temporary; Using filesort SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC; a MIN(b) MAX(b) 4 1 3 @@ -2692,8 +2692,8 @@ a MIN(b) MAX(b) 1 1 3 EXPLAIN SELECT a, MIN(b), MAX(b), AVG(b) FROM t1 GROUP BY a ORDER BY a DESC; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL break_it 10 NULL 12 Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL index NULL break_it 10 NULL 12 Using index SELECT a, MIN(b), MAX(b), AVG(b) FROM t1 GROUP BY a ORDER BY a DESC; a MIN(b) MAX(b) AVG(b) 4 1 3 2.0000 @@ -2776,8 +2776,8 @@ a b 3 12 3 13 explain extended select sql_buffer_result a, max(b)+1 from t1 where a = 0 group by a; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range PRIMARY,index PRIMARY 4 NULL 1 100.00 Using where; Using index for group-by; Using temporary +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range PRIMARY,index PRIMARY 4 NULL 1 100.00 Using where; Using index for group-by; Using temporary Warnings: Note 1003 select sql_buffer_result `test`.`t1`.`a` AS `a`,max(`test`.`t1`.`b`) + 1 AS `max(b)+1` from `test`.`t1` where `test`.`t1`.`a` = 0 group by `test`.`t1`.`a` drop table t1; @@ -2792,8 +2792,8 @@ Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK EXPLAIN SELECT DISTINCT c FROM t1 WHERE d=4; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL foo 10 NULL 3 Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range NULL foo 10 NULL 3 Using where; Using index for group-by SELECT DISTINCT c FROM t1 WHERE d=4; c 1 @@ -2815,8 +2815,8 @@ test.t analyze status OK #should use range with index for group by EXPLAIN SELECT a, MIN(b) FROM t WHERE b <> 0 GROUP BY a; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t range NULL a 10 NULL 2 Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t NULL range NULL a 10 NULL 2 Using where; Using index for group-by #should return 1 row SELECT a, MIN(b) FROM t WHERE b <> 0 GROUP BY a; a MIN(b) @@ -2825,8 +2825,8 @@ a MIN(b) #should use range with index for group by EXPLAIN SELECT a, MAX(b) FROM t WHERE b <> 1 GROUP BY a; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t range NULL a 10 NULL 2 Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t NULL range NULL a 10 NULL 2 Using where; Using index for group-by #should return 1 row SELECT a, MAX(b) FROM t WHERE b <> 1 GROUP BY a; a MAX(b) @@ -2836,8 +2836,8 @@ INSERT INTO t SELECT a, 2 FROM t; #should use range with index for group by EXPLAIN SELECT a, MAX(b) FROM t WHERE b > 0 AND b < 2 GROUP BY a; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t range NULL a 10 NULL 2 Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t NULL range NULL a 10 NULL 2 Using where; Using index for group-by #should return 1 row SELECT a, MAX(b) FROM t WHERE b > 0 AND b < 2 GROUP BY a; a MAX(b) @@ -2897,72 +2897,72 @@ test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK EXPLAIN SELECT MIN( a ) FROM t1 WHERE a = NULL; -id select_type table type possible_keys key key_len ref rows Extra -x x x x x x x x x Impossible WHERE noticed after reading const tables +id select_type table partitions type possible_keys key key_len ref rows Extra +x x x x x x x x x x Impossible WHERE noticed after reading const tables SELECT MIN( a ) FROM t1 WHERE a = NULL; MIN( a ) NULL EXPLAIN SELECT MIN( a ) FROM t1 WHERE a <> NULL; -id select_type table type possible_keys key key_len ref rows Extra -x x x x x x x x x Impossible WHERE noticed after reading const tables +id select_type table partitions type possible_keys key key_len ref rows Extra +x x x x x x x x x x Impossible WHERE noticed after reading const tables SELECT MIN( a ) FROM t1 WHERE a <> NULL; MIN( a ) NULL EXPLAIN SELECT MIN( a ) FROM t1 WHERE a > NULL; -id select_type table type possible_keys key key_len ref rows Extra -x x x x x x x x x Impossible WHERE noticed after reading const tables +id select_type table partitions type possible_keys key key_len ref rows Extra +x x x x x x x x x x Impossible WHERE noticed after reading const tables SELECT MIN( a ) FROM t1 WHERE a > NULL; MIN( a ) NULL EXPLAIN SELECT MIN( a ) FROM t1 WHERE a < NULL; -id select_type table type possible_keys key key_len ref rows Extra -x x x x x x x x x Impossible WHERE noticed after reading const tables +id select_type table partitions type possible_keys key key_len ref rows Extra +x x x x x x x x x x Impossible WHERE noticed after reading const tables SELECT MIN( a ) FROM t1 WHERE a < NULL; MIN( a ) NULL EXPLAIN SELECT MIN( a ) FROM t1 WHERE a <=> NULL; -id select_type table type possible_keys key key_len ref rows Extra -x x x x x x x x x No matching min/max row +id select_type table partitions type possible_keys key key_len ref rows Extra +x x x x x x x x x x No matching min/max row SELECT MIN( a ) FROM t1 WHERE a <=> NULL; MIN( a ) NULL EXPLAIN SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10; -id select_type table type possible_keys key key_len ref rows Extra -x x x x x x x x x Impossible WHERE noticed after reading const tables +id select_type table partitions type possible_keys key key_len ref rows Extra +x x x x x x x x x x Impossible WHERE noticed after reading const tables SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10; MIN( a ) NULL EXPLAIN SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL; -id select_type table type possible_keys key key_len ref rows Extra -x x x x x x x x x Impossible WHERE noticed after reading const tables +id select_type table partitions type possible_keys key key_len ref rows Extra +x x x x x x x x x x Impossible WHERE noticed after reading const tables SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL; MIN( a ) NULL EXPLAIN SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL; -id select_type table type possible_keys key key_len ref rows Extra -x x x x x x x x x Impossible WHERE noticed after reading const tables +id select_type table partitions type possible_keys key key_len ref rows Extra +x x x x x x x x x x Impossible WHERE noticed after reading const tables SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL; MIN( a ) NULL EXPLAIN SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); -id select_type table type possible_keys key key_len ref rows Extra -x x x x x x x x x Impossible WHERE noticed after reading const tables -x x x x x x x x x Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +x x x x x x x x x x Impossible WHERE noticed after reading const tables +x x x x x x x x x x Using where; Using index SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); MIN( a ) NULL EXPLAIN SELECT MIN( a ) FROM t1 WHERE a IS NULL; -id select_type table type possible_keys key key_len ref rows Extra -x x x x x x x x x No matching min/max row +id select_type table partitions type possible_keys key key_len ref rows Extra +x x x x x x x x x x No matching min/max row SELECT MIN( a ) FROM t1 WHERE a IS NULL; MIN( a ) NULL @@ -2973,72 +2973,72 @@ test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK EXPLAIN SELECT MIN( a ) FROM t1 WHERE a = NULL; -id select_type table type possible_keys key key_len ref rows Extra -x x x x x x x x x Impossible WHERE noticed after reading const tables +id select_type table partitions type possible_keys key key_len ref rows Extra +x x x x x x x x x x Impossible WHERE noticed after reading const tables SELECT MIN( a ) FROM t1 WHERE a = NULL; MIN( a ) NULL EXPLAIN SELECT MIN( a ) FROM t1 WHERE a <> NULL; -id select_type table type possible_keys key key_len ref rows Extra -x x x x x x x x x Impossible WHERE noticed after reading const tables +id select_type table partitions type possible_keys key key_len ref rows Extra +x x x x x x x x x x Impossible WHERE noticed after reading const tables SELECT MIN( a ) FROM t1 WHERE a <> NULL; MIN( a ) NULL EXPLAIN SELECT MIN( a ) FROM t1 WHERE a > NULL; -id select_type table type possible_keys key key_len ref rows Extra -x x x x x x x x x Impossible WHERE noticed after reading const tables +id select_type table partitions type possible_keys key key_len ref rows Extra +x x x x x x x x x x Impossible WHERE noticed after reading const tables SELECT MIN( a ) FROM t1 WHERE a > NULL; MIN( a ) NULL EXPLAIN SELECT MIN( a ) FROM t1 WHERE a < NULL; -id select_type table type possible_keys key key_len ref rows Extra -x x x x x x x x x Impossible WHERE noticed after reading const tables +id select_type table partitions type possible_keys key key_len ref rows Extra +x x x x x x x x x x Impossible WHERE noticed after reading const tables SELECT MIN( a ) FROM t1 WHERE a < NULL; MIN( a ) NULL EXPLAIN SELECT MIN( a ) FROM t1 WHERE a <=> NULL; -id select_type table type possible_keys key key_len ref rows Extra -x x x x x x x x x Select tables optimized away +id select_type table partitions type possible_keys key key_len ref rows Extra +x x x x x x x x x x Select tables optimized away SELECT MIN( a ) FROM t1 WHERE a <=> NULL; MIN( a ) NULL EXPLAIN SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10; -id select_type table type possible_keys key key_len ref rows Extra -x x x x x x x x x Impossible WHERE noticed after reading const tables +id select_type table partitions type possible_keys key key_len ref rows Extra +x x x x x x x x x x Impossible WHERE noticed after reading const tables SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10; MIN( a ) NULL EXPLAIN SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL; -id select_type table type possible_keys key key_len ref rows Extra -x x x x x x x x x Impossible WHERE noticed after reading const tables +id select_type table partitions type possible_keys key key_len ref rows Extra +x x x x x x x x x x Impossible WHERE noticed after reading const tables SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL; MIN( a ) NULL EXPLAIN SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL; -id select_type table type possible_keys key key_len ref rows Extra -x x x x x x x x x Impossible WHERE noticed after reading const tables +id select_type table partitions type possible_keys key key_len ref rows Extra +x x x x x x x x x x Impossible WHERE noticed after reading const tables SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL; MIN( a ) NULL EXPLAIN SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); -id select_type table type possible_keys key key_len ref rows Extra -x x x x x x x x x Impossible WHERE noticed after reading const tables -x x x x x x x x x Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +x x x x x x x x x x Impossible WHERE noticed after reading const tables +x x x x x x x x x x Using where; Using index SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); MIN( a ) NULL EXPLAIN SELECT MIN( a ) FROM t1 WHERE a IS NULL; -id select_type table type possible_keys key key_len ref rows Extra -x x x x x x x x x Select tables optimized away +id select_type table partitions type possible_keys key key_len ref rows Extra +x x x x x x x x x x Select tables optimized away SELECT MIN( a ) FROM t1 WHERE a IS NULL; MIN( a ) NULL @@ -3063,65 +3063,65 @@ test.t1 analyze status OK # EXPLAIN SELECT MIN( a ) FROM t1 WHERE a = NULL; -id select_type table type possible_keys key key_len ref rows Extra -x x x x x x x x x Impossible WHERE noticed after reading const tables +id select_type table partitions type possible_keys key key_len ref rows Extra +x x x x x x x x x x Impossible WHERE noticed after reading const tables SELECT MIN( a ) FROM t1 WHERE a = NULL; MIN( a ) NULL EXPLAIN SELECT MIN( a ) FROM t1 WHERE a <> NULL; -id select_type table type possible_keys key key_len ref rows Extra -x x x x x x x x x Impossible WHERE noticed after reading const tables +id select_type table partitions type possible_keys key key_len ref rows Extra +x x x x x x x x x x Impossible WHERE noticed after reading const tables SELECT MIN( a ) FROM t1 WHERE a <> NULL; MIN( a ) NULL EXPLAIN SELECT MIN( a ) FROM t1 WHERE a > NULL; -id select_type table type possible_keys key key_len ref rows Extra -x x x x x x x x x Impossible WHERE noticed after reading const tables +id select_type table partitions type possible_keys key key_len ref rows Extra +x x x x x x x x x x Impossible WHERE noticed after reading const tables SELECT MIN( a ) FROM t1 WHERE a > NULL; MIN( a ) NULL EXPLAIN SELECT MIN( a ) FROM t1 WHERE a < NULL; -id select_type table type possible_keys key key_len ref rows Extra -x x x x x x x x x Impossible WHERE noticed after reading const tables +id select_type table partitions type possible_keys key key_len ref rows Extra +x x x x x x x x x x Impossible WHERE noticed after reading const tables SELECT MIN( a ) FROM t1 WHERE a < NULL; MIN( a ) NULL EXPLAIN SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10; -id select_type table type possible_keys key key_len ref rows Extra -x x x x x x x x x Impossible WHERE noticed after reading const tables +id select_type table partitions type possible_keys key key_len ref rows Extra +x x x x x x x x x x Impossible WHERE noticed after reading const tables SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10; MIN( a ) NULL EXPLAIN SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL; -id select_type table type possible_keys key key_len ref rows Extra -x x x x x x x x x Impossible WHERE noticed after reading const tables +id select_type table partitions type possible_keys key key_len ref rows Extra +x x x x x x x x x x Impossible WHERE noticed after reading const tables SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL; MIN( a ) NULL EXPLAIN SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL; -id select_type table type possible_keys key key_len ref rows Extra -x x x x x x x x x Impossible WHERE noticed after reading const tables +id select_type table partitions type possible_keys key key_len ref rows Extra +x x x x x x x x x x Impossible WHERE noticed after reading const tables SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL; MIN( a ) NULL EXPLAIN SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); -id select_type table type possible_keys key key_len ref rows Extra -x x x x x x x x x Impossible WHERE noticed after reading const tables -x x x x x x x x x Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +x x x x x x x x x x Impossible WHERE noticed after reading const tables +x x x x x x x x x x Using where; Using index SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); MIN( a ) NULL EXPLAIN SELECT MIN( a ) FROM t1 WHERE a IS NULL; -id select_type table type possible_keys key key_len ref rows Extra -x x x x x x x x x Impossible WHERE +id select_type table partitions type possible_keys key key_len ref rows Extra +x x x x x x x x x x Impossible WHERE SELECT MIN( a ) FROM t1 WHERE a IS NULL; MIN( a ) NULL @@ -3161,46 +3161,46 @@ Table Op Msg_type Msg_text test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK EXPLAIN SELECT COUNT(DISTINCT a) FROM t1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL a 5 NULL 3 Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range NULL a 5 NULL 3 Using index for group-by SELECT COUNT(DISTINCT a) FROM t1; COUNT(DISTINCT a) 2 EXPLAIN SELECT COUNT(DISTINCT a,b) FROM t1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL a 10 NULL 17 Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range NULL a 10 NULL 17 Using index for group-by SELECT COUNT(DISTINCT a,b) FROM t1; COUNT(DISTINCT a,b) 16 EXPLAIN SELECT COUNT(DISTINCT b,a) FROM t1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL a 10 NULL 17 Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range NULL a 10 NULL 17 Using index for group-by SELECT COUNT(DISTINCT b,a) FROM t1; COUNT(DISTINCT b,a) 16 EXPLAIN SELECT COUNT(DISTINCT b) FROM t1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL a 10 NULL 16 Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL index NULL a 10 NULL 16 Using index SELECT COUNT(DISTINCT b) FROM t1; COUNT(DISTINCT b) 8 EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 GROUP BY a; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL a 5 NULL 3 Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range NULL a 5 NULL 3 Using index for group-by SELECT COUNT(DISTINCT a) FROM t1 GROUP BY a; COUNT(DISTINCT a) 1 1 EXPLAIN SELECT COUNT(DISTINCT b) FROM t1 GROUP BY a; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL a 10 NULL 17 Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range NULL a 10 NULL 17 Using index for group-by SELECT COUNT(DISTINCT b) FROM t1 GROUP BY a; COUNT(DISTINCT b) 8 8 EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 GROUP BY b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL a 10 NULL 16 Using index; Using filesort +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL index NULL a 10 NULL 16 Using index; Using filesort SELECT COUNT(DISTINCT a) FROM t1 GROUP BY b; COUNT(DISTINCT a) 2 @@ -3212,95 +3212,95 @@ COUNT(DISTINCT a) 2 2 EXPLAIN SELECT DISTINCT COUNT(DISTINCT a) FROM t1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL a 10 NULL 16 Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL index NULL a 10 NULL 16 Using index SELECT DISTINCT COUNT(DISTINCT a) FROM t1; COUNT(DISTINCT a) 2 EXPLAIN SELECT COUNT(DISTINCT a, b + 0) FROM t1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL a 10 NULL 16 Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL index NULL a 10 NULL 16 Using index SELECT COUNT(DISTINCT a, b + 0) FROM t1; COUNT(DISTINCT a, b + 0) 16 EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT b) < 10; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL a 10 NULL 16 Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL index NULL a 10 NULL 16 Using index SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT b) < 10; COUNT(DISTINCT a) 2 EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT c) < 10; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 16 +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 16 SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT c) < 10; COUNT(DISTINCT a) 2 EXPLAIN SELECT 1 FROM t1 HAVING COUNT(DISTINCT a) < 10; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL a 5 NULL 3 Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range NULL a 5 NULL 3 Using index for group-by SELECT 1 FROM t1 HAVING COUNT(DISTINCT a) < 10; 1 1 EXPLAIN SELECT 1 FROM t1 GROUP BY a HAVING COUNT(DISTINCT b) > 1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL a 10 NULL 17 Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range NULL a 10 NULL 17 Using index for group-by SELECT 1 FROM t1 GROUP BY a HAVING COUNT(DISTINCT b) > 1; 1 1 1 EXPLAIN SELECT COUNT(DISTINCT t1_1.a) FROM t1 t1_1, t1 t1_2 GROUP BY t1_1.a; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1_1 index NULL a 10 NULL 16 Using index; Using temporary; Using filesort -1 SIMPLE t1_2 index NULL a 10 NULL 16 Using index; Using join buffer (flat, BNL join) +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1_1 NULL index NULL a 10 NULL 16 Using index; Using temporary; Using filesort +1 SIMPLE t1_2 NULL index NULL a 10 NULL 16 Using index; Using join buffer (flat, BNL join) SELECT COUNT(DISTINCT t1_1.a) FROM t1 t1_1, t1 t1_2 GROUP BY t1_1.a; COUNT(DISTINCT t1_1.a) 1 1 EXPLAIN SELECT COUNT(DISTINCT a), 12 FROM t1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL a 5 NULL 3 Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range NULL a 5 NULL 3 Using index for group-by SELECT COUNT(DISTINCT a), 12 FROM t1; COUNT(DISTINCT a) 12 2 12 EXPLAIN SELECT COUNT(DISTINCT a, b, c) FROM t2; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range NULL a 15 NULL 17 Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL range NULL a 15 NULL 17 Using index for group-by SELECT COUNT(DISTINCT a, b, c) FROM t2; COUNT(DISTINCT a, b, c) 16 EXPLAIN SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT a) FROM t2; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range NULL a 5 NULL 3 Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL range NULL a 5 NULL 3 Using index for group-by SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT a) FROM t2; COUNT(DISTINCT a) SUM(DISTINCT a) AVG(DISTINCT a) 2 3 1.5000 EXPLAIN SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT f) FROM t2; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 16 +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 16 SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT f) FROM t2; COUNT(DISTINCT a) SUM(DISTINCT a) AVG(DISTINCT f) 2 3 1.0000 EXPLAIN SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, a) FROM t2; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range NULL a 10 NULL 17 Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL range NULL a 10 NULL 17 Using index for group-by SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, a) FROM t2; COUNT(DISTINCT a, b) COUNT(DISTINCT b, a) 16 16 EXPLAIN SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, f) FROM t2; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 16 +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 16 SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, f) FROM t2; COUNT(DISTINCT a, b) COUNT(DISTINCT b, f) 16 8 EXPLAIN SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, d) FROM t2; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 16 +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 16 SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, d) FROM t2; COUNT(DISTINCT a, b) COUNT(DISTINCT b, d) 16 8 EXPLAIN SELECT a, c, COUNT(DISTINCT c, a, b) FROM t2 GROUP BY a, b, c; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range NULL a 15 NULL 17 Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL range NULL a 15 NULL 17 Using index for group-by SELECT a, c, COUNT(DISTINCT c, a, b) FROM t2 GROUP BY a, b, c; a c COUNT(DISTINCT c, a, b) 1 1 1 @@ -3321,63 +3321,63 @@ a c COUNT(DISTINCT c, a, b) 2 1 1 EXPLAIN SELECT COUNT(DISTINCT c, a, b) FROM t2 WHERE a > 5 AND b BETWEEN 10 AND 20 GROUP BY a, b, c; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range a a 15 NULL 17 Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL range a a 15 NULL 17 Using where; Using index for group-by SELECT COUNT(DISTINCT c, a, b) FROM t2 WHERE a > 5 AND b BETWEEN 10 AND 20 GROUP BY a, b, c; COUNT(DISTINCT c, a, b) EXPLAIN SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 WHERE a = 5 GROUP BY b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref a a 5 const 16 Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL ref a a 5 const 16 Using where; Using index SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 WHERE a = 5 GROUP BY b; COUNT(DISTINCT b) SUM(DISTINCT b) EXPLAIN SELECT a, COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range NULL a 10 NULL 17 Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL range NULL a 10 NULL 17 Using index for group-by SELECT a, COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a; a COUNT(DISTINCT b) SUM(DISTINCT b) 1 8 36 2 8 36 EXPLAIN SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range NULL a 10 NULL 17 Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL range NULL a 10 NULL 17 Using index for group-by SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a; COUNT(DISTINCT b) SUM(DISTINCT b) 8 36 8 36 EXPLAIN SELECT COUNT(DISTINCT a, b) FROM t2 WHERE c = 13 AND d = 42; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 16 Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 16 Using where SELECT COUNT(DISTINCT a, b) FROM t2 WHERE c = 13 AND d = 42; COUNT(DISTINCT a, b) 0 EXPLAIN SELECT a, COUNT(DISTINCT a), SUM(DISTINCT a) FROM t2 WHERE b = 13 AND c = 42 GROUP BY a; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range NULL a 15 NULL 3 Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL range NULL a 15 NULL 3 Using where; Using index for group-by SELECT a, COUNT(DISTINCT a), SUM(DISTINCT a) FROM t2 WHERE b = 13 AND c = 42 GROUP BY a; a COUNT(DISTINCT a) SUM(DISTINCT a) # This query could have been resolved using loose index scan since # the second part of count(..) is defined by a constant predicate EXPLAIN SELECT COUNT(DISTINCT a, b), SUM(DISTINCT a) FROM t2 WHERE b = 42; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index NULL a 15 NULL 16 Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL index NULL a 15 NULL 16 Using where; Using index SELECT COUNT(DISTINCT a, b), SUM(DISTINCT a) FROM t2 WHERE b = 42; COUNT(DISTINCT a, b) SUM(DISTINCT a) 0 NULL EXPLAIN SELECT SUM(DISTINCT a), MAX(b) FROM t2 GROUP BY a; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index NULL a 15 NULL 16 Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL index NULL a 15 NULL 16 Using index SELECT SUM(DISTINCT a), MAX(b) FROM t2 GROUP BY a; SUM(DISTINCT a) MAX(b) 1 8 2 8 EXPLAIN SELECT 42 * (a + c + COUNT(DISTINCT c, a, b)) FROM t2 GROUP BY a, b, c; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range NULL a 15 NULL 17 Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL range NULL a 15 NULL 17 Using index for group-by SELECT 42 * (a + c + COUNT(DISTINCT c, a, b)) FROM t2 GROUP BY a, b, c; 42 * (a + c + COUNT(DISTINCT c, a, b)) 126 @@ -3397,8 +3397,8 @@ SELECT 42 * (a + c + COUNT(DISTINCT c, a, b)) FROM t2 GROUP BY a, b, c; 168 168 EXPLAIN SELECT (SUM(DISTINCT a) + MAX(b)) FROM t2 GROUP BY a; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index NULL a 15 NULL 16 Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL index NULL a 15 NULL 16 Using index SELECT (SUM(DISTINCT a) + MAX(b)) FROM t2 GROUP BY a; (SUM(DISTINCT a) + MAX(b)) 9 @@ -3426,8 +3426,8 @@ f1 COUNT(DISTINCT f2) 2 1 3 4 explain SELECT f1, COUNT(DISTINCT f2) FROM t1 GROUP BY f1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 5 NULL 8 Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL index NULL PRIMARY 5 NULL 8 Using index drop table t1; # End of test#50539. # @@ -3451,8 +3451,8 @@ a SUM(DISTINCT a) MIN(b) 3 3 2 4 4 4 EXPLAIN SELECT a, SUM(DISTINCT a), MIN(b) FROM t GROUP BY a; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t index NULL a 10 NULL 7 Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t NULL index NULL a 10 NULL 7 Using index SELECT a, SUM(DISTINCT a), MAX(b) FROM t GROUP BY a; a SUM(DISTINCT a) MAX(b) 1 1 1 @@ -3460,8 +3460,8 @@ a SUM(DISTINCT a) MAX(b) 3 3 3 4 4 5 EXPLAIN SELECT a, SUM(DISTINCT a), MAX(b) FROM t GROUP BY a; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t index NULL a 10 NULL 7 Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t NULL index NULL a 10 NULL 7 Using index SELECT a, MAX(b) FROM t GROUP BY a HAVING SUM(DISTINCT a); a MAX(b) 1 1 @@ -3469,14 +3469,14 @@ a MAX(b) 3 3 4 5 EXPLAIN SELECT a, MAX(b) FROM t GROUP BY a HAVING SUM(DISTINCT a); -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t index NULL a 10 NULL 7 Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t NULL index NULL a 10 NULL 7 Using index SELECT SUM(DISTINCT a), MIN(b), MAX(b) FROM t; SUM(DISTINCT a) MIN(b) MAX(b) 10 0 5 EXPLAIN SELECT SUM(DISTINCT a), MIN(b), MAX(b) FROM t; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t index NULL a 10 NULL 7 Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t NULL index NULL a 10 NULL 7 Using index SELECT a, SUM(DISTINCT a), MIN(b), MAX(b) FROM t GROUP BY a; a SUM(DISTINCT a) MIN(b) MAX(b) 1 1 0 1 @@ -3484,8 +3484,8 @@ a SUM(DISTINCT a) MIN(b) MAX(b) 3 3 2 3 4 4 4 5 EXPLAIN SELECT a, SUM(DISTINCT a), MIN(b), MAX(b) FROM t GROUP BY a; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t index NULL a 10 NULL 7 Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t NULL index NULL a 10 NULL 7 Using index DROP TABLE t; # # Bug#18109609: LOOSE INDEX SCAN IS NOT USED WHEN IT SHOULD @@ -3504,8 +3504,8 @@ Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK EXPLAIN SELECT MAX(c2), c1 FROM t1 WHERE c1 = 4 GROUP BY c1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range c1 c1 5 NULL 6 Using where; Using index for group-by +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range c1 c1 5 NULL 6 Using where; Using index for group-by FLUSH STATUS; SELECT MAX(c2), c1 FROM t1 WHERE c1 = 4 GROUP BY c1; MAX(c2) c1 diff --git a/storage/rocksdb/mysql-test/rocksdb/r/index.result b/storage/rocksdb/mysql-test/rocksdb/r/index.result index aaae2c52d1a..8a7336be7bd 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range a a 8 NULL # Using index condition +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 drop table t0,t1,t2; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/issue290.result b/storage/rocksdb/mysql-test/rocksdb/r/issue290.result index 1a83a93bcbb..148c519d2b9 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 type possible_keys key key_len ref rows Extra -1 SIMPLE linktable range PRIMARY PRIMARY 24 NULL # Using where +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 drop table linktable; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/issue884.result b/storage/rocksdb/mysql-test/rocksdb/r/issue884.result index 60c9674516a..c50e90f66e8 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 type possible_keys key key_len ref rows Extra -1 SIMPLE test 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 Extra +1 SIMPLE test NULL index_merge PRIMARY,d d,PRIMARY 24,16 NULL # 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 6b742ebaf0c..0f35fc8ce2b 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL d 9 NULL # Using index +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 # 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 730e12d02f6..c5411202f5b 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range e e 5 NULL # Using index condition +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 32 NULL # Using where +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range a a 32 NULL # Using where +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 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 feeabd7ee79..6dcfc4c6fd5 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/records_in_range.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/records_in_range.result @@ -9,143 +9,143 @@ KEY ka(a), KEY kb(b) comment 'rev:cf1' ) ENGINE = rocksdb; explain extended select * from t1 where a> 500 and a< 750; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range ka ka 5 NULL 1000 100.00 Using index condition +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range ka ka 5 NULL 1000 100.00 Using index condition Warnings: Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` > 500 and `test`.`t1`.`a` < 750 explain extended select * from t1 where a< 750; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range ka ka 5 NULL 1000 100.00 Using index condition +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range ka ka 5 NULL 1000 100.00 Using index condition Warnings: Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` < 750 explain extended select * from t1 where a> 500; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range ka ka 5 NULL 1000 100.00 Using index condition +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range ka ka 5 NULL 1000 100.00 Using index condition Warnings: Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` > 500 explain extended select * from t1 where a>=0 and a<=1000; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range ka ka 5 NULL 1000 100.00 Using index condition +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range ka ka 5 NULL 1000 100.00 Using index condition Warnings: Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` >= 0 and `test`.`t1`.`a` <= 1000 explain extended select * from t1 where b> 500 and b< 750; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range kb kb 5 NULL 1000 100.00 Using index condition +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range kb kb 5 NULL 1000 100.00 Using index condition Warnings: Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` > 500 and `test`.`t1`.`b` < 750 explain extended select * from t1 where b< 750; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range kb kb 5 NULL 1000 100.00 Using index condition +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range kb kb 5 NULL 1000 100.00 Using index condition Warnings: Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` < 750 explain extended select * from t1 where b> 500; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range kb kb 5 NULL 1000 100.00 Using index condition +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range kb kb 5 NULL 1000 100.00 Using index condition Warnings: Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` > 500 explain extended select * from t1 where b>=0 and b<=1000; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range kb kb 5 NULL 1000 100.00 Using index condition +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range kb kb 5 NULL 1000 100.00 Using index condition Warnings: Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` >= 0 and `test`.`t1`.`b` <= 1000 set @save_rocksdb_records_in_range = @@session.rocksdb_records_in_range; set rocksdb_records_in_range = 15000; explain extended select a from t1 where a < 750; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range ka ka 5 NULL 15000 100.00 Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range ka ka 5 NULL 15000 100.00 Using where; Using index Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 750 explain extended select a, b from t1 where a < 750; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL ka NULL NULL NULL 20000 75.00 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL ka NULL NULL NULL 20000 75.00 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` < 750 explain extended select a from t1 where a = 700; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ref ka ka 5 const 15000 100.00 Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ref ka ka 5 const 15000 100.00 Using index Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 700 explain extended select a,b from t1 where a = 700; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL ka NULL NULL NULL 20000 75.00 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL ka NULL NULL NULL 20000 75.00 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` = 700 explain extended select a from t1 where a in (700, 800); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 index ka ka 5 NULL 20000 100.00 Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index ka ka 5 NULL 20000 100.00 Using where; Using index Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` in (700,800) explain extended select a,b from t1 where a in (700, 800); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL ka NULL NULL NULL 20000 100.00 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL ka NULL NULL NULL 20000 100.00 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` in (700,800) set rocksdb_records_in_range=8000; explain extended select a from t1 where a in (700, 800); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range ka ka 5 NULL 16000 100.00 Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range ka ka 5 NULL 16000 100.00 Using where; Using index Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` in (700,800) explain extended select a,b from t1 where a in (700, 800); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL ka NULL NULL NULL 20000 80.00 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL ka NULL NULL NULL 20000 80.00 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` in (700,800) set rocksdb_records_in_range = @save_rocksdb_records_in_range; set global rocksdb_force_flush_memtable_now = true; explain extended select * from t1 where a> 500 and a< 750; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range ka ka 5 NULL 1000 100.00 Using index condition +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range ka ka 5 NULL 1000 100.00 Using index condition Warnings: Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` > 500 and `test`.`t1`.`a` < 750 explain extended select * from t1 where a< 750; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range ka ka 5 NULL 1000 100.00 Using index condition +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range ka ka 5 NULL 1000 100.00 Using index condition Warnings: Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` < 750 explain extended select * from t1 where a> 500; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range ka ka 5 NULL 1000 100.00 Using index condition +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range ka ka 5 NULL 1000 100.00 Using index condition Warnings: Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` > 500 explain extended select * from t1 where a>=0 and a<=1000; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range ka ka 5 NULL 1000 100.00 Using index condition +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range ka ka 5 NULL 1000 100.00 Using index condition Warnings: Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` >= 0 and `test`.`t1`.`a` <= 1000 explain extended select * from t1 where b> 500 and b< 750; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range kb kb 5 NULL 1000 100.00 Using index condition +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range kb kb 5 NULL 1000 100.00 Using index condition Warnings: Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` > 500 and `test`.`t1`.`b` < 750 explain extended select * from t1 where b< 750; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range kb kb 5 NULL 1000 100.00 Using index condition +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range kb kb 5 NULL 1000 100.00 Using index condition Warnings: Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` < 750 explain extended select * from t1 where b> 500; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range kb kb 5 NULL 1000 100.00 Using index condition +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range kb kb 5 NULL 1000 100.00 Using index condition Warnings: Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` > 500 explain extended select * from t1 where b>=0 and b<=1000; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range kb kb 5 NULL 1000 100.00 Using index condition +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range kb kb 5 NULL 1000 100.00 Using index condition Warnings: Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` >= 0 and `test`.`t1`.`b` <= 1000 explain extended select * from t1 where a>= 500 and a<= 500; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range ka ka 5 NULL 1000 100.00 Using index condition +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range ka ka 5 NULL 1000 100.00 Using index condition Warnings: Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` >= 500 and `test`.`t1`.`a` <= 500 explain extended select * from t1 where b>= 500 and b<= 500; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range kb kb 5 NULL 1000 100.00 Using index condition +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range kb kb 5 NULL 1000 100.00 Using index condition Warnings: Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` >= 500 and `test`.`t1`.`b` <= 500 explain extended select * from t1 where a< 750 and b> 500 and b< 750; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range ka,kb ka 5 NULL 1000 5.00 Using index condition; Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range ka,kb ka 5 NULL 1000 5.00 Using index condition; Using where Warnings: Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` < 750 and `test`.`t1`.`b` > 500 and `test`.`t1`.`b` < 750 drop index ka on t1; @@ -153,14 +153,14 @@ drop index kb on t1; create index kab on t1(a,b); set global rocksdb_force_flush_memtable_now = true; explain extended select * from t1 where a< 750 and b> 500 and b< 750; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range kab kab 5 NULL 1000 100.00 Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range kab kab 5 NULL 1000 100.00 Using where; Using index Warnings: Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` < 750 and `test`.`t1`.`b` > 500 and `test`.`t1`.`b` < 750 set rocksdb_records_in_range=444; explain extended select * from t1 where a< 750 and b> 500 and b< 750; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range kab kab 5 NULL 444 100.00 Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range kab kab 5 NULL 444 100.00 Using where; Using index Warnings: Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` < 750 and `test`.`t1`.`b` > 500 and `test`.`t1`.`b` < 750 set rocksdb_records_in_range=0; @@ -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 type possible_keys key key_len ref rows Extra -1 SIMPLE linktable range PRIMARY,id1_type PRIMARY 24 NULL 2 Using where +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE linktable range PRIMARY,id1_type PRIMARY 24 NULL 2 Using where +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 drop table linktable; DROP TABLE t1; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_checksums.result b/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_checksums.result index e4b757ef3b0..286f6da8299 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range a a 5 NULL # Using index condition +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range a a 5 NULL # Using where; Using index +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 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 f9e3129c73f..9b9643e3c90 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range kp1 kp1 5 NULL # Using index condition +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ref kp1 kp1 5 const # Using where +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range kp1 kp1 5 NULL # Using index condition +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range kp1 kp1 5 NULL # Using index condition +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range kp1 kp1 5 NULL # Using where +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range kp1 kp1 5 NULL # Using where +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range kp1 kp1 5 NULL # Using where +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t4 ref id1_id2 id1_id2 5 const # Using where; Using index +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t4 ref id1_id2 id1_id2 5 const # Using where +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t4 ref id1_id2 id1_id2 5 const # Using index condition +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref key1 key1 9 const # +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref key1 key1 9 const # 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 3634f8c023e..d6900446faa 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range kp1 kp1 5 NULL # Using index condition +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ref kp1 kp1 5 const # Using where +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range kp1 kp1 5 NULL # Using index condition +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range kp1 kp1 5 NULL # Using index condition +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range kp1 kp1 5 NULL # Using where +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range kp1 kp1 5 NULL # Using where +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range kp1 kp1 5 NULL # Using where +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t4 ref id1_id2 id1_id2 5 const # Using where; Using index +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t4 ref id1_id2 id1_id2 5 const # Using where +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t4 ref id1_id2 id1_id2 5 const # Using index condition +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 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/type_bit_indexes.result b/storage/rocksdb/mysql-test/rocksdb/r/type_bit_indexes.result index 9e28a402c38..d8ac1e01c0d 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 3 NULL # Using index +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL # Using temporary; Using filesort +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 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 40e31e9fe6d..7dc1b9fc544 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 type possible_keys key key_len ref rows Extra -# # # # # PRIMARY # # # # +id select_type table partitions type possible_keys key key_len ref rows 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 type possible_keys key key_len ref rows Extra -# # # # # NULL # # # # +id select_type table partitions type possible_keys key key_len ref rows Extra +# # # # # NULL # # # # # 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range l_t l_t 259 NULL # Using where; Using filesort +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range l_t l_t 259 NULL # Using where; Using filesort +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref m m 131 const # Using where; Using filesort +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref m m 131 const # Using where; Using filesort +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 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 3c9c30bb617..8e5348c7366 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 20 NULL # Using index +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 20 NULL # Using index +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range v16 v16 19 NULL # Using where; Using index +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL # Using where +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL v16 NULL NULL NULL # Using where; Using filesort +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 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 cb56089595b..55eff51e679 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t index NULL sk 27 NULL # Using index +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t index NULL sk1 4 NULL # Using index +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 explain select hex(c1) from t IGNORE INDEX (sk1) order by c1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t ALL NULL NULL NULL NULL # Using filesort +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 explain select hex(c2) from t order by c2; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t index NULL sk2 2 NULL # Using index +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 explain select hex(c2) from t IGNORE INDEX (sk1) order by c2; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t index NULL sk2 2 NULL # Using index +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t ALL NULL NULL NULL NULL # +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t NULL ALL NULL NULL NULL NULL # 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t ALL NULL NULL NULL NULL # +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t NULL ALL NULL NULL NULL NULL # 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 bd40e32f94d..e3e3c87276f 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 5 NULL # Using index +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 5 NULL # Using index +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range ts ts 5 NULL # Using where; Using index +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 SELECT ts FROM t1 WHERE ts > NOW(); ts EXPLAIN SELECT ts FROM t1 USE INDEX () WHERE ts > NOW(); -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL # Using where +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL y 6 NULL # Using index +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL y 6 NULL # Using index; Using temporary; Using filesort +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 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 668a927669a..99b71e96d4b 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range key1 key1 3 NULL # Using index condition +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 # This will use index-only: explain select col1, col2 from t1 where col1 between -8 and 8; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range key1 key1 3 NULL # Using where; Using index +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range key1 key1 3 NULL # Using where; Using index +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range key1 key1 7 NULL # Using where; Using index +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 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 b0bcfd7075c..54271c1d0ad 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index PRIMARY PRIMARY 1 NULL # Using where; Using index +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL # Using where +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL b 2 NULL # +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL index NULL b 2 NULL # 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL # Using temporary +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 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 38f4b82c4fa..34a14058f1e 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 5 NULL # Using index +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 5 NULL # Using index; Using filesort +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL n1_n2 37 NULL # Using index; Using temporary +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL d2 29 NULL # Using index +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL d2 29 NULL # Using index; Using temporary; Using filesort +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 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 89dc65e56f8..6c15f543c0b 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 4 NULL # Using index +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL # Using filesort +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index r_dp r_dp 18 NULL # Using where; Using index +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL d 9 NULL # Using index +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL d 9 NULL # Using index for group-by +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL f 5 NULL # Using index +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL f 5 NULL # Using index for group-by +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 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 e4c47f04c24..cb0ee4933b4 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 4 NULL # Using index +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index s_m s_m 7 NULL # Using where; Using index +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 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 a4135004414..435f0458c05 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 2 NULL # Using index +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 2 NULL # Using index; Using filesort +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL # Using where +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL # Using temporary; Using filesort +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 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 ddab60cc5c3..ddb5a2f99f4 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 34 NULL # Using where; Using filesort +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL # Using where; Using filesort +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 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 type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref m m 131 const # Using where; Using filesort +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 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/t/index.test b/storage/rocksdb/mysql-test/rocksdb/t/index.test index e0c6175b397..5eac8835b84 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 9 # +--replace_column 10 # 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 5ea8799c627..7ce018f19ca 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 9 # +--replace_column 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); drop table linktable; diff --git a/storage/rocksdb/mysql-test/rocksdb/t/issue884.test b/storage/rocksdb/mysql-test/rocksdb/t/issue884.test index 6bf3e5177f2..b281cc1167a 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/issue884.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/issue884.test @@ -34,7 +34,7 @@ set global rocksdb_force_flush_memtable_now = true; analyze table test; --enable_query_log ---replace_column 9 # +--replace_column 10 # explain select * from test where d = 10 and a = 10 and b = 2; select * from test where d = 10 and a = 10 and b = 2; diff --git a/storage/rocksdb/mysql-test/rocksdb/t/issue896.test b/storage/rocksdb/mysql-test/rocksdb/t/issue896.test index ba57fb99832..28885aa5b89 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/issue896.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/issue896.test @@ -10,7 +10,7 @@ PRIMARY KEY (`a`,`b`), 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); ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT COUNT(*) FROM t1 FORCE INDEX(d); --echo # segfault here without the fix SELECT COUNT(*) FROM t1 FORCE INDEX(d); diff --git a/storage/rocksdb/mysql-test/rocksdb/t/mariadb_port_fixes.test b/storage/rocksdb/mysql-test/rocksdb/t/mariadb_port_fixes.test index 99d4e2d117c..96559171d67 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/mariadb_port_fixes.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/mariadb_port_fixes.test @@ -20,7 +20,7 @@ insert into t1 select 1,1,1,1,0; insert into t1 select 2,1,1,1,0; insert into t1 select 3,1,1,1,0; ---replace_column 9 # +--replace_column 10 # explain select a from t1 force index(e) where e<10000; select a from t1; @@ -38,7 +38,7 @@ CREATE TABLE t1( KEY (a) ) ENGINE=ROCKSDB DEFAULT CHARSET=utf8; insert into t1 values (1,1,1),(2,2,2); ---replace_column 9 # +--replace_column 10 # explain select a from t1 where a <'zzz'; CREATE TABLE t2( @@ -48,7 +48,7 @@ CREATE TABLE t2( KEY (a) ) ENGINE=ROCKSDB DEFAULT CHARSET=utf8; insert into t2 values (1,1,1),(2,2,2); ---replace_column 9 # +--replace_column 10 # explain select a from t2 where a <'zzz'; drop table t1,t2; diff --git a/storage/rocksdb/mysql-test/rocksdb/t/rocksdb_checksums.test b/storage/rocksdb/mysql-test/rocksdb/t/rocksdb_checksums.test index 42a4c83ff04..075ca6cee38 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/rocksdb_checksums.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/rocksdb_checksums.test @@ -114,7 +114,7 @@ select * from t4; set session debug_dbug= "-d,myrocks_simulate_bad_pk_checksum2"; --echo # 3. Check if we catch checksum mismatches for secondary indexes ---replace_column 9 # +--replace_column 10 # explain select * from t3 force index(a) where a<4; select * from t3 force index(a) where a<4; @@ -130,7 +130,7 @@ set session debug_dbug= "-d,myrocks_simulate_bad_key_checksum1"; --disable_query_log set global rocksdb_force_flush_memtable_now=1; --enable_query_log ---replace_column 9 # +--replace_column 10 # explain select a from t3 force index(a) where a<4; select a from t3 force index(a) where a<4; diff --git a/storage/rocksdb/mysql-test/rocksdb/t/rocksdb_icp.test b/storage/rocksdb/mysql-test/rocksdb/t/rocksdb_icp.test index 8d0ec89e85a..0e789e3a223 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/rocksdb_icp.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/rocksdb_icp.test @@ -30,7 +30,7 @@ let $save_query= 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'); ---replace_column 9 # +--replace_column 10 # explain select * from t1 force index(key1) where key1=1; diff --git a/storage/rocksdb/mysql-test/rocksdb/t/rocksdb_range2.test b/storage/rocksdb/mysql-test/rocksdb/t/rocksdb_range2.test index 28010d13753..53795a92ace 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/rocksdb_range2.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/rocksdb_range2.test @@ -15,7 +15,7 @@ while ($i<10000) --enable_query_log analyze table t1; select count(*) from t1; ---replace_column 9 # +--replace_column 10 # explain select c1 from t1 where c1 > 5 limit 10; drop table t1; diff --git a/storage/rocksdb/mysql-test/rocksdb/t/type_bit_indexes.test b/storage/rocksdb/mysql-test/rocksdb/t/type_bit_indexes.test index e4f4bb81819..bfd3e6dca92 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/type_bit_indexes.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/type_bit_indexes.test @@ -25,7 +25,7 @@ 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); ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT b+0 FROM t1 ORDER BY b; SELECT b+0 FROM t1 ORDER BY b; @@ -51,7 +51,7 @@ 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); ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT HEX(b+c) FROM t1 WHERE c > 1 OR HEX(b) < 0xFFFFFF; --sorted_result SELECT HEX(b+c) FROM t1 WHERE c > 1 OR HEX(b) < 0xFFFFFF; @@ -76,7 +76,7 @@ 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); ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT DISTINCT a+0 FROM t1 ORDER BY a; SELECT DISTINCT a+0 FROM t1 ORDER BY a; @@ -101,7 +101,7 @@ 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); ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT d FROM t1 WHERE d BETWEEN 1 AND 10000; --sorted_result SELECT d+0 FROM t1 WHERE d BETWEEN 1 AND 10000; 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 24c70e8e733..85f1b54bcfe 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/type_blob_indexes.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/type_blob_indexes.test @@ -35,11 +35,11 @@ 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 # +--replace_column 1 # 2 # 3 # 4 # 5 # 7 # 8 # 9 # 10 # 11 # 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; ---replace_column 1 # 2 # 3 # 4 # 5 # 7 # 8 # 9 # 10 # +--replace_column 1 # 2 # 3 # 4 # 5 # 7 # 8 # 9 # 10 # 11 # EXPLAIN SELECT SUBSTRING(b,16) AS f FROM t1 USE INDEX () WHERE b IN ('test1','test2') ORDER BY f; SELECT SUBSTRING(b,16) AS f FROM t1 USE INDEX () WHERE b IN ('test1','test2') ORDER BY f; @@ -72,11 +72,11 @@ INSERT INTO t1 (b,t,m,l) VALUES (REPEAT('c',128),REPEAT('b',128),REPEAT('c',128),REPEAT('e',128)); # Here we are getting possible key l_t, but not the final key ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT SUBSTRING(t,64), SUBSTRING(l,256) FROM t1 WHERE t!=l AND l NOT IN ('test1') ORDER BY t, l DESC; SELECT SUBSTRING(t,64), SUBSTRING(l,256) FROM t1 WHERE t!=l AND l NOT IN ('test1') ORDER BY t, l DESC; ---replace_column 9 # +--replace_column 10 # 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; 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; @@ -107,11 +107,11 @@ 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 9 # +--replace_column 10 # EXPLAIN SELECT SUBSTRING(m,128) AS f FROM t1 WHERE m = 'test1' ORDER BY f DESC; SELECT SUBSTRING(m,128) AS f FROM t1 WHERE m = 'test1' ORDER BY f DESC; ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT SUBSTRING(m,128) AS f FROM t1 IGNORE INDEX FOR ORDER BY (m) WHERE m = 'test1' ORDER BY f DESC; SELECT SUBSTRING(m,128) AS f FROM t1 IGNORE INDEX FOR ORDER BY (m) WHERE m = 'test1' ORDER BY f DESC; diff --git a/storage/rocksdb/mysql-test/rocksdb/t/type_char_indexes.test b/storage/rocksdb/mysql-test/rocksdb/t/type_char_indexes.test index 6ee2f03e74d..7c5411e6972 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/type_char_indexes.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/type_char_indexes.test @@ -22,11 +22,11 @@ SHOW INDEX IN t1; INSERT INTO t1 (c,c20,v16,v128) VALUES ('a','char1','varchar1a','varchar1b'),('a','char2','varchar2a','varchar2b'),('b','char3','varchar1a','varchar1b'),('c','char4','varchar3a','varchar3b'); ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT c20 FROM t1 ORDER BY c20; SELECT c20 FROM t1 ORDER BY c20; ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT c20 FROM t1 FORCE INDEX FOR ORDER BY (PRIMARY) ORDER BY c20; SELECT c20 FROM t1 FORCE INDEX FOR ORDER BY (PRIMARY) ORDER BY c20; @@ -48,23 +48,23 @@ SHOW INDEX IN t1; INSERT INTO t1 (c,c20,v16,v128) VALUES ('a','char1','varchar1a','varchar1b'),('a','char2','varchar2a','varchar2b'),('b','char3','varchar1a','varchar1b'),('c','char4','varchar3a','varchar3b'); ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT c, v128 FROM t1 WHERE c != 'a' AND v128 > 'varchar'; --sorted_result SELECT c, v128 FROM t1 WHERE c != 'a' AND v128 > 'varchar'; ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT v128, COUNT(*) FROM t1 GROUP BY v128; --sorted_result SELECT v128, COUNT(*) FROM t1 GROUP BY v128; ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT v128, COUNT(*) FROM t1 USE INDEX FOR GROUP BY (c_v) GROUP BY v128; --sorted_result SELECT v128, COUNT(*) FROM t1 USE INDEX FOR GROUP BY (c_v) GROUP BY v128; SET SESSION optimizer_switch = 'engine_condition_pushdown=on'; ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT c,c20,v16,v128 FROM t1 WHERE c > 'a'; --sorted_result SELECT c,c20,v16,v128 FROM t1 WHERE c > 'a'; @@ -87,17 +87,17 @@ SHOW INDEX IN t1; 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'); ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT SUBSTRING(v16,0,3) FROM t1 WHERE v16 LIKE 'varchar%'; --sorted_result SELECT SUBSTRING(v16,7,3) FROM t1 WHERE v16 LIKE 'varchar%'; ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT SUBSTRING(v16,0,3) FROM t1 IGNORE INDEX (v16) WHERE v16 LIKE 'varchar%'; --sorted_result SELECT SUBSTRING(v16,7,3) FROM t1 IGNORE INDEX (v16) WHERE v16 LIKE 'varchar%'; ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT c,c20,v16,v128 FROM t1 WHERE v16 = 'varchar1a' OR v16 = 'varchar3a' ORDER BY v16; --sorted_result SELECT c,c20,v16,v128 FROM t1 WHERE v16 = 'varchar1a' OR v16 = 'varchar3a' ORDER BY v16; diff --git a/storage/rocksdb/mysql-test/rocksdb/t/type_char_indexes_collation.test b/storage/rocksdb/mysql-test/rocksdb/t/type_char_indexes_collation.test index d231236bd92..c5a1a5fdc58 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/type_char_indexes_collation.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/type_char_indexes_collation.test @@ -7,7 +7,7 @@ create table t (id int not null auto_increment primary key, c varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci, key sk (c)); insert into t (c) values ('☀'), ('ß'); ---replace_column 9 # +--replace_column 10 # explain select c from t; select c from t; drop table t; @@ -32,14 +32,14 @@ while ($i < 256) } --enable_query_log ---replace_column 9 # +--replace_column 10 # explain select hex(c1) from t order by c1; ---replace_column 9 # +--replace_column 10 # explain select hex(c1) from t IGNORE INDEX (sk1) order by c1; ---replace_column 9 # +--replace_column 10 # explain select hex(c2) from t order by c2; ---replace_column 9 # +--replace_column 10 # explain select hex(c2) from t IGNORE INDEX (sk1) order by c2; --let $file1=$MYSQLTEST_VARDIR/tmp/filesort_order @@ -104,7 +104,7 @@ insert into t (c1) values ('bbbb '); --error ER_DUP_ENTRY insert into t (c1) values ('a '); ---replace_column 9 # +--replace_column 10 # explain select c1 from t; select c1 from t; @@ -114,7 +114,7 @@ drop table t; 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'); ---replace_column 9 # +--replace_column 10 # explain select 'email_i' as index_name, count(*) AS count from t force index(email_i); select 'email_i' as index_name, count(*) AS count from t force index(email_i); drop table t; diff --git a/storage/rocksdb/mysql-test/rocksdb/t/type_date_time_indexes.test b/storage/rocksdb/mysql-test/rocksdb/t/type_date_time_indexes.test index 06cf86b7661..5095cd52157 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/type_date_time_indexes.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/type_date_time_indexes.test @@ -32,11 +32,11 @@ 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)); ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT dt FROM t1 ORDER BY dt LIMIT 3; SELECT dt FROM t1 ORDER BY dt LIMIT 3; ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT dt FROM t1 FORCE INDEX FOR ORDER BY (PRIMARY) ORDER BY dt LIMIT 3; SELECT dt FROM t1 FORCE INDEX FOR ORDER BY (PRIMARY) ORDER BY dt LIMIT 3; @@ -67,12 +67,12 @@ 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'); ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT ts FROM t1 WHERE ts > NOW(); --sorted_result SELECT ts FROM t1 WHERE ts > NOW(); ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT ts FROM t1 USE INDEX () WHERE ts > NOW(); --sorted_result SELECT ts FROM t1 USE INDEX () WHERE ts > NOW(); @@ -103,12 +103,12 @@ 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','1994'), (DATE(@tm),@tm,TIMESTAMP(@tm),TIME(@tm),YEAR(@tm),'1995'); ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT d, t FROM t1 WHERE CONCAT(d,' ',t) != CURRENT_DATE(); --sorted_result SELECT d, t FROM t1 WHERE CONCAT(d,' ',t) != CURRENT_DATE(); ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT d, t FROM t1 IGNORE INDEX (d_t) WHERE CONCAT(d,' ',t) != CURRENT_DATE(); --sorted_result SELECT d, t FROM t1 IGNORE INDEX (d_t) WHERE CONCAT(d,' ',t) != CURRENT_DATE(); @@ -142,12 +142,12 @@ 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'); ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT y, COUNT(*) FROM t1 GROUP BY y; --sorted_result SELECT y, COUNT(*) FROM t1 GROUP BY y; ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT y, COUNT(*) FROM t1 USE INDEX FOR GROUP BY () GROUP BY y; --sorted_result SELECT y, COUNT(*) FROM t1 USE INDEX FOR GROUP BY () GROUP BY y; diff --git a/storage/rocksdb/mysql-test/rocksdb/t/type_decimal.test b/storage/rocksdb/mysql-test/rocksdb/t/type_decimal.test index ee325b34eff..0d8efdbb966 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/type_decimal.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/type_decimal.test @@ -33,18 +33,18 @@ insert into t1 select pk+100000, 9.0, 9.0, 'extra-data' from t1; analyze table t1; --echo # The following can't use index-only: ---replace_column 9 # +--replace_column 10 # explain select * from t1 where col1 between -8 and 8; --echo # This will use index-only: ---replace_column 9 # +--replace_column 10 # explain select col1, col2 from t1 where col1 between -8 and 8; select col1, col2 from t1 where col1 between -8 and 8; insert into t1 values (11, NULL, 0.9, 'row1-with-null'); insert into t1 values (10, -8.4, NULL, 'row2-with-null'); ---replace_column 9 # +--replace_column 10 # explain select col1, col2 from t1 force index(key1) where col1 is null or col1 < -7; select col1, col2 from t1 force index(key1) where col1 is null or col1 < -7; @@ -80,7 +80,7 @@ insert into t1 select pk+10000, col1+20000, col2+20000, 'extra-data' from t1; insert into t1 select pk+100000, col1+20000, col2+20000, 'extra-data' from t1; analyze table t1; ---replace_column 9 # +--replace_column 10 # explain select col1, col2 from t1 force index(key1) where col1 between -800 and 800; select col1, col2 from t1 force index(key1) where col1 between -800 and 800; diff --git a/storage/rocksdb/mysql-test/rocksdb/t/type_enum_indexes.test b/storage/rocksdb/mysql-test/rocksdb/t/type_enum_indexes.test index d7086a45fe1..c356427e203 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/type_enum_indexes.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/type_enum_indexes.test @@ -28,11 +28,11 @@ INSERT INTO t1 (a,b,c,pk) VALUES SHOW INDEX IN t1; ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT a FROM t1 WHERE b > 'test2' ORDER BY a; SELECT a FROM t1 WHERE b > 'test2' ORDER BY a; ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT a FROM t1 FORCE INDEX (a_b) WHERE b > 'test2' ORDER BY a; SELECT a FROM t1 FORCE INDEX (a_b) WHERE b > 'test2' ORDER BY a; @@ -52,12 +52,12 @@ INSERT INTO t1 (a,b,c) VALUES SHOW INDEX IN t1; ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT c FROM t1 WHERE c BETWEEN '1d' AND '6u'; --sorted_result SELECT c FROM t1 WHERE c BETWEEN '1d' AND '6u'; ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT c FROM t1 USE INDEX () WHERE c BETWEEN '1d' AND '6u'; --sorted_result SELECT c FROM t1 USE INDEX () WHERE c BETWEEN '1d' AND '6u'; @@ -78,12 +78,12 @@ INSERT INTO t1 (a,b,c,pk) VALUES SHOW INDEX IN t1; ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT DISTINCT b FROM t1; --sorted_result SELECT DISTINCT b FROM t1; ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT DISTINCT b FROM t1 IGNORE INDEX (b); --sorted_result SELECT DISTINCT b FROM t1 IGNORE INDEX (b); diff --git a/storage/rocksdb/mysql-test/rocksdb/t/type_fixed_indexes.test b/storage/rocksdb/mysql-test/rocksdb/t/type_fixed_indexes.test index e9e6df58d21..d1bc53dc5b4 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/type_fixed_indexes.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/type_fixed_indexes.test @@ -30,11 +30,11 @@ INSERT INTO t1 (d1,d2,n1,n2) VALUES (4540424564.23,3343303441.0,12,13), (15,17,23,100000); ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT d1 FROM t1 ORDER BY d1 DESC; SELECT d1 FROM t1 ORDER BY d1 DESC; ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT d1 FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY) ORDER BY d1 DESC; SELECT d1 FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY) ORDER BY d1 DESC; @@ -63,7 +63,7 @@ INSERT INTO t1 (d1,d2,n1,n2,pk) VALUES (4540424564.23,3343303441.0,12,13,6), (15,17,23,100000,7); ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT DISTINCT n1+n2 FROM t1; --sorted_result SELECT DISTINCT n1+n2 FROM t1; @@ -92,12 +92,12 @@ INSERT INTO t1 (d1,d2,n1,n2,pk) VALUES (4540424564.23,3343303441.0,12,13,6), (15,17,23,100000,7); ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT d2, COUNT(*) FROM t1 GROUP BY d2; --sorted_result SELECT d2, COUNT(*) FROM t1 GROUP BY d2; ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT d2, COUNT(*) FROM t1 IGNORE INDEX FOR GROUP BY (d2) GROUP BY d2; --sorted_result SELECT d2, COUNT(*) FROM t1 IGNORE INDEX FOR GROUP BY (d2) GROUP BY d2; diff --git a/storage/rocksdb/mysql-test/rocksdb/t/type_float_indexes.test b/storage/rocksdb/mysql-test/rocksdb/t/type_float_indexes.test index 907bc614d49..6bf33bf902b 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/type_float_indexes.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/type_float_indexes.test @@ -27,11 +27,11 @@ INSERT INTO t1 (f,r,d,dp) VALUES (17.5843,4953453454.44,29229114.0,1111111.23), (4644,1422.22,466664.999,0.5); ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT f FROM t1 ORDER BY f; SELECT f FROM t1 ORDER BY f; ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT f FROM t1 IGNORE INDEX (PRIMARY) ORDER BY f; SELECT f FROM t1 IGNORE INDEX (PRIMARY) ORDER BY f; @@ -58,7 +58,7 @@ 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); ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT r, dp FROM t1 WHERE r > 0 or dp > 0; --sorted_result SELECT r, dp FROM t1 WHERE r > 0 or dp > 0; @@ -83,7 +83,7 @@ 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); ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT DISTINCT d FROM t1 ORDER BY d; SELECT DISTINCT d FROM t1 ORDER BY d; @@ -109,7 +109,7 @@ 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); ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT DISTINCT d FROM t1 ORDER BY d; SELECT DISTINCT d FROM t1 ORDER BY d; @@ -138,7 +138,7 @@ INSERT INTO t1 (f,r,d,dp,pk) VALUES INSERT INTO t1 (f,r,d,dp,pk) VALUES (1.2345,0,0,0,6); ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT DISTINCT f FROM t1 ORDER BY f; SELECT DISTINCT f FROM t1 ORDER BY f; @@ -166,7 +166,7 @@ INSERT INTO t1 (f,r,d,dp,pk) VALUES INSERT INTO t1 (f,r,d,dp,pk) VALUES (1.2345,0,0,0,6); ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT DISTINCT f FROM t1 ORDER BY f; SELECT DISTINCT f FROM t1 ORDER BY f; diff --git a/storage/rocksdb/mysql-test/rocksdb/t/type_int_indexes.test b/storage/rocksdb/mysql-test/rocksdb/t/type_int_indexes.test index c95c3d88b8a..4d37575a0df 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/type_int_indexes.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/type_int_indexes.test @@ -21,7 +21,7 @@ CREATE TABLE t1 ( 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); ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT i FROM t1 ORDER BY i; SELECT i FROM t1 ORDER BY i; @@ -39,7 +39,7 @@ CREATE TABLE t1 ( 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); ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT s, m FROM t1 WHERE s != 10 AND m != 1; --sorted_result SELECT s, m FROM t1 WHERE s != 10 AND m != 1; diff --git a/storage/rocksdb/mysql-test/rocksdb/t/type_set_indexes.test b/storage/rocksdb/mysql-test/rocksdb/t/type_set_indexes.test index 983c70256b0..6d663854376 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/type_set_indexes.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/type_set_indexes.test @@ -28,11 +28,11 @@ INSERT INTO t1 (a,b,c) VALUES ('Asia','test5','50'), ('Europe,S.America','test1,','39'); ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT c FROM t1 ORDER BY c; SELECT c FROM t1 ORDER BY c; ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT c FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY) ORDER BY c; SELECT c FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY) ORDER BY c; @@ -63,7 +63,7 @@ INSERT INTO t1 (a,b,c,pk) VALUES ('Asia','test5','50',6), ('Europe,S.America','test1,','39',7); ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT a FROM t1 WHERE FIND_IN_SET('Europe',a) > 0; --sorted_result SELECT a FROM t1 WHERE FIND_IN_SET('Europe',a) > 0; @@ -90,7 +90,7 @@ INSERT INTO t1 (a,b,c,pk) VALUES ('Asia','test5','50',6), ('Europe,S.America','test1,','39',7); ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT DISTINCT a, b FROM t1 ORDER BY b DESC, a; --sorted_result SELECT DISTINCT a, b FROM t1 ORDER BY b DESC, a; diff --git a/storage/rocksdb/mysql-test/rocksdb/t/type_text_indexes.test b/storage/rocksdb/mysql-test/rocksdb/t/type_text_indexes.test index 5fdc4bff39d..18d4eec3d5a 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/type_text_indexes.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/type_text_indexes.test @@ -35,11 +35,11 @@ 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)); ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT SUBSTRING(t,16) AS f FROM t1 WHERE t IN ('test1','test2') ORDER BY f; SELECT SUBSTRING(t,16) AS f FROM t1 WHERE t IN ('test1','test2') ORDER BY f; ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT SUBSTRING(t,16) AS f FROM t1 IGNORE INDEX (PRIMARY) WHERE t IN ('test1','test2') ORDER BY f; SELECT SUBSTRING(t,16) AS f FROM t1 IGNORE INDEX (PRIMARY) WHERE t IN ('test1','test2') ORDER BY f; DROP TABLE t1; @@ -78,7 +78,7 @@ 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'); ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT SUBSTRING(m,128) AS f FROM t1 WHERE m = 'test1' ORDER BY f DESC; SELECT SUBSTRING(m,128) AS f FROM t1 WHERE m = 'test1' ORDER BY f DESC;