revision-id: fc00182d74513287810949e06173599b63e12085 (mariadb-10.6.1-361-gfc00182d745) parent(s): bccd15ec7354ea4db189633e6c74eb27dbd4d826 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-03-17 22:32:44 +0300 message: Test updates #4 --- mysql-test/main/named_pipe.result | 176 ++++++++++----------- mysql-test/main/windows.result | 6 +- .../rocksdb/r/index_merge_rocksdb.result | 12 +- .../mysql-test/rocksdb/r/rocksdb_cf_reverse.result | 16 +- .../mysql-test/rocksdb/r/rocksdb_parts.result | 8 +- .../mysql-test/rocksdb/r/rocksdb_range.result | 56 +++---- .../rocksdb/r/type_binary_indexes.result | 16 +- .../mysql-test/rocksdb/r/type_varchar.result | 68 ++++---- .../mysql-test/rocksdb/t/index_merge_rocksdb.test | 6 +- .../mysql-test/rocksdb/t/rocksdb_cf_reverse.test | 8 +- .../mysql-test/rocksdb/t/rocksdb_parts.test | 2 +- .../mysql-test/rocksdb/t/rocksdb_range.test | 28 ++-- .../mysql-test/rocksdb/t/type_binary_indexes.test | 14 +- .../rocksdb/mysql-test/rocksdb/t/type_varchar.test | 4 +- .../mysql-test/rocksdb/t/type_varchar_endspace.inc | 6 +- 15 files changed, 213 insertions(+), 213 deletions(-) diff --git a/mysql-test/main/named_pipe.result b/mysql-test/main/named_pipe.result index 2baa3471ec9..3630891ec91 100644 --- a/mysql-test/main/named_pipe.result +++ b/mysql-test/main/named_pipe.result @@ -128,20 +128,20 @@ fld3 select t2.fld3 from t2 where fld3 LIKE 'don_t_find_me_please%'; fld3 explain select t2.fld3 from t2 where fld3 = 'honeysuckle'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref fld3 fld3 30 const 1 Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL ref fld3 fld3 30 const 1 Using where; Using index explain select fld3 from t2 ignore index (fld3) where fld3 = 'honeysuckle'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 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 1199 Using where explain select fld3 from t2 use index (fld1) where fld3 = 'honeysuckle'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 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 1199 Using where explain select fld3 from t2 use index (fld3) where fld3 = 'honeysuckle'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref fld3 fld3 30 const 1 Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL ref fld3 fld3 30 const 1 Using where; Using index explain select fld3 from t2 use index (fld1,fld3) where fld3 = 'honeysuckle'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref fld3 fld3 30 const 1 Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL ref fld3 fld3 30 const 1 Using where; Using index explain select fld3 from t2 ignore index (fld3,not_used); ERROR 42000: Key 'not_used' doesn't exist in table 't2' explain select fld3 from t2 use index (not_used); @@ -151,8 +151,8 @@ fld3 honeysuckle honoring explain select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range fld3 fld3 30 NULL 2 Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL range fld3 fld3 30 NULL 2 Using where; Using index select fld1,fld3 from t2 where fld3="Colombo" or fld3 = "nondecreasing" order by fld3; fld1 fld3 148504 Colombo @@ -171,8 +171,8 @@ fld1 250501 250502 explain select fld1 from t2 where fld1=250501 or fld1="250502"; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range fld1 fld1 4 NULL 2 Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL range fld1 fld1 4 NULL 2 Using where; Using index select fld1 from t2 where fld1=250501 or fld1=250502 or fld1 >= 250505 and fld1 <= 250601 or fld1 between 250501 and 250502; fld1 250501 @@ -180,8 +180,8 @@ fld1 250505 250601 explain select fld1 from t2 where fld1=250501 or fld1=250502 or fld1 >= 250505 and fld1 <= 250601 or fld1 between 250501 and 250502; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range fld1 fld1 4 NULL 4 Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL range fld1 fld1 4 NULL 4 Using where; Using index select fld1,fld3 from t2 where companynr = 37 and fld3 like 'f%'; fld1 fld3 012001 flanking @@ -597,21 +597,21 @@ companynr rtrim(space(512+companynr)) select distinct fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by fld3; fld3 explain select t3.t2nr,fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by t3.t2nr,fld3; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL fld1 NULL NULL NULL 1199 Using where; Using temporary; Using filesort -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.fld1 1 Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL ALL fld1 NULL NULL NULL 1199 Using where; Using temporary; Using filesort +1 SIMPLE t3 NULL eq_ref PRIMARY PRIMARY 4 test.t2.fld1 1 Using where; Using index explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using filesort -1 SIMPLE t3 ref period period 4 test.t1.period 4181 +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ALL period NULL NULL NULL 41810 Using filesort +1 SIMPLE t3 NULL ref period period 4 test.t1.period 4181 explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 index period period 4 NULL 1 -1 SIMPLE t1 ref period period 4 test.t3.period 4181 +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t3 NULL index period period 4 NULL 1 +1 SIMPLE t1 NULL ref period period 4 test.t3.period 4181 explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index period period 4 NULL 1 -1 SIMPLE t3 ref period period 4 test.t1.period 4181 +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL index period period 4 NULL 1 +1 SIMPLE t3 NULL ref period period 4 test.t1.period 4181 select period from t1; period 9410 @@ -624,9 +624,9 @@ select fld3,period from t2,t3 where t2.fld1 = 011401 and t2.fld1=t3.t2nr and t3. fld3 period breaking 1001 explain select fld3,period from t2,t3 where t2.fld1 = 011401 and t3.t2nr=t2.fld1 and 1001 = t3.period; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 const fld1 fld1 4 const 1 -1 SIMPLE t3 const PRIMARY,period PRIMARY 4 const 1 +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL const fld1 fld1 4 const 1 +1 SIMPLE t3 NULL const PRIMARY,period PRIMARY 4 const 1 select fld3,period from t2,t1 where companynr*10 = 37*10; fld3 period breaking 9410 @@ -1360,82 +1360,82 @@ select count(*) from t2 left join t4 using (companynr) where t4.companynr is not count(*) 1199 explain select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 1200 -1 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 Using where; Not exists +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1200 +1 SIMPLE t4 NULL eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 Using where; Not exists explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr is null; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 -1 SIMPLE t2 ALL NULL NULL NULL NULL 1200 Using where; Not exists +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t4 NULL ALL NULL NULL NULL NULL 12 +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1200 Using where; Not exists select companynr,companyname from t2 left join t4 using (companynr) where companynr is null; companynr companyname select count(*) from t2 left join t4 using (companynr) where companynr is not null; count(*) 1200 explain select companynr,companyname from t2 left join t4 using (companynr) where companynr is null; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE explain select companynr,companyname from t4 left join t2 using (companynr) where companynr is null; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE delete from t2 where fld1=999999; explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where -1 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 Using where +1 SIMPLE t4 NULL eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where -1 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 Using where +1 SIMPLE t4 NULL eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 and t4.companynr > 0; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where -1 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 Using where +1 SIMPLE t4 NULL eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t4 NULL ALL PRIMARY NULL NULL NULL 12 Using where +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t4 NULL ALL PRIMARY NULL NULL NULL 12 Using where +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t4 NULL ALL PRIMARY NULL NULL NULL 12 Using where +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 Using where explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t4 NULL ALL NULL NULL NULL NULL 12 +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 Using where explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0 or t4.companynr > 0; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t4 NULL ALL PRIMARY NULL NULL NULL 12 +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 Using where explain select t2.companynr,companyname from t4 left join t2 using (companynr) where ifnull(t2.companynr,1)>0; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t4 NULL ALL NULL NULL NULL NULL 12 +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t4 NULL ALL PRIMARY NULL NULL NULL 12 Using where +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t4 NULL ALL PRIMARY NULL NULL NULL 12 Using where +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t4 NULL ALL NULL NULL NULL NULL 12 Using where +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 Using where SET @@optimizer_switch=@local_optimizer_switch; select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; companynr companynr 37 36 41 40 explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t4 index NULL PRIMARY 1 NULL 12 Using index; Using temporary -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer (flat, BNL join) +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t4 NULL index NULL PRIMARY 1 NULL 12 Using index; Using temporary +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 Using where; Using join buffer (flat, BNL join) SET @@join_cache_level=@local_join_cache_level; select t2.fld1,t2.companynr,fld3,period from t3,t2 where t2.fld1 = 38208 and t2.fld1=t3.t2nr and period = 1008 or t2.fld1 = 38008 and t2.fld1 =t3.t2nr and period = 1008; fld1 companynr fld3 period @@ -1509,8 +1509,8 @@ select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) count(*) min(fld4) max(fld4) sum(fld1) avg(fld1) std(fld1) variance(fld1) 70 absentee vest 17788966 254128.0857 3272.5940 10709871.3069 explain extended select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>""; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 100.00 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 100.00 Using where Warnings: Note 1003 select count(0) AS `count(*)`,min(`test`.`t2`.`fld4`) AS `min(fld4)`,max(`test`.`t2`.`fld4`) AS `max(fld4)`,sum(`test`.`t2`.`fld1`) AS `sum(fld1)`,avg(`test`.`t2`.`fld1`) AS `avg(fld1)`,std(`test`.`t2`.`fld1`) AS `std(fld1)`,variance(`test`.`t2`.`fld1`) AS `variance(fld1)` from `test`.`t2` where `test`.`t2`.`companynr` = 34 and `test`.`t2`.`fld4` <> '' select companynr,count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 group by companynr limit 3; @@ -1950,11 +1950,11 @@ select t2.fld1,sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = fld1 sum(price) 038008 234298 explain select fld3 from t2 where 1>2 or 2>3; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE explain select fld3 from t2 where fld1=fld1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 select companynr,fld1 from t2 HAVING fld1=250501 or fld1=250502; companynr fld1 34 250501 @@ -2005,8 +2005,8 @@ select count(*) from t3 where companynr=512 and price2=76234234; count(*) 4181 explain select min(fld1),max(fld1),count(*) from t2; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away select min(fld1),max(fld1),count(*) from t2; min(fld1) max(fld1) count(*) 0 1232609 1199 diff --git a/mysql-test/main/windows.result b/mysql-test/main/windows.result index 05e4600f4b7..1e4db32606d 100644 --- a/mysql-test/main/windows.result +++ b/mysql-test/main/windows.result @@ -15,9 +15,9 @@ drop table t1; CREATE TABLE t1 (a int, b int); INSERT INTO t1 VALUES (1,1); EXPLAIN SELECT * FROM t1 WHERE b = (SELECT max(2)); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used +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 noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used DROP TABLE t1; CREATE DATABASE `TESTDB`; USE `TESTDB`; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/index_merge_rocksdb.result b/storage/rocksdb/mysql-test/rocksdb/r/index_merge_rocksdb.result index 7c4f57b61bd..a0d3f98b081 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/index_merge_rocksdb.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/index_merge_rocksdb.result @@ -19,8 +19,8 @@ COUNT(*) 7201 SET GLOBAL rocksdb_force_flush_memtable_now = 1; EXPLAIN UPDATE t1 SET filler1='to be deleted' WHERE key1=100 AND key2=100; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL # Using intersect(key1,key2); Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL index_merge key1,key2 key1,key2 5,5 NULL # Using intersect(key1,key2); Using where UPDATE t1 SET filler1='to be deleted' WHERE key1=100 and key2=100; DROP TABLE t0, t1; create table t1 (key1 int, key2 int, key3 int, key (key1), key (key2), key(key3)) engine=rocksdb; @@ -31,11 +31,11 @@ Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK explain select * from t1 where key1 = 1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref key1 key1 5 const # +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref key1 key1 5 const # explain select key1,key2 from t1 where key1 = 1 or key2 = 1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL # Using union(key1,key2); Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL index_merge key1,key2 key1,key2 5,5 NULL # Using union(key1,key2); Using where select * from t1 where key1 = 1; key1 key2 key3 1 100 100 diff --git a/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_cf_reverse.result b/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_cf_reverse.result index 1c85343cabb..d99b92bc04b 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_cf_reverse.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_cf_reverse.result @@ -35,8 +35,8 @@ pk a b 19 19 19 explain select a from t1 order by a limit 5; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL a 4 NULL # Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL index NULL a 4 NULL # Using index select a from t1 order by a limit 5; a 0 @@ -46,8 +46,8 @@ a 4 explain select b from t1 order by b limit 5; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL b 4 NULL # Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL index NULL b 4 NULL # Using index select a from t1 order by a limit 5; a 0 @@ -57,8 +57,8 @@ a 4 explain select a from t1 order by a desc limit 5; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL a 4 NULL # Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL index NULL a 4 NULL # Using index select a from t1 order by a desc limit 5; a 19 @@ -68,8 +68,8 @@ a 15 explain select b from t1 order by b desc limit 5; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL b 4 NULL # Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL index NULL b 4 NULL # Using index select b from t1 order by b desc limit 5; b 19 diff --git a/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_parts.result b/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_parts.result index 70bef39eceb..56971f7ccfd 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_parts.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_parts.result @@ -48,8 +48,8 @@ insert into t1 values (12,20,20); set @tmp_rfirr= @@rocksdb_force_index_records_in_range; set rocksdb_force_index_records_in_range= 12; explain select * from t1 force index(col1) where col1=10; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref col1 col1 5 const # +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0,p1 ref col1 col1 5 const # select * from t1 force index(col1) where col1=10; pk col1 col2 1 10 10 @@ -75,8 +75,8 @@ insert into t1 values (11,20,20); insert into t1 values (12,20,20); # The following must use "Using index" explain select pk from t1 force index(col1) where col1=10; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref col1 col1 5 const 2000 Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0,p1 ref col1 col1 5 const 2000 Using index drop table t1; # # Issue #214: subqueries cause crash diff --git a/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_range.result b/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_range.result index 918859ea036..1170ffaa396 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_range.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_range.result @@ -20,8 +20,8 @@ insert into t2 select A.a, FLOOR(A.a/10), A.a from t1 A; # Original failure was here: explain select * from t2 force index (a) where a=0; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref a a 4 const # +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL ref a a 4 const # select * from t2 force index (a) where a=0; pk a b 0 0 0 @@ -37,8 +37,8 @@ pk a b # The rest are for code coverage: explain select * from t2 force index (a) where a=2; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref a a 4 const # +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL ref a a 4 const # select * from t2 force index (a) where a=2; pk a b 20 2 20 @@ -53,8 +53,8 @@ pk a b 29 2 29 explain select * from t2 force index (a) where a=3 and pk=33; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 const a a 8 const,const # +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL const a a 8 const,const # select * from t2 force index (a) where a=3 and pk=33; pk a b 33 3 33 @@ -74,22 +74,22 @@ pk a b # explain select count(*) from t2 force index (a) where a>=0 and a <=1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range a a 4 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL range a a 4 NULL # Using where; Using index select count(*) from t2 force index (a) where a>=0 and a <=1; count(*) 20 explain select count(*) from t2 force index (a) where a>=-1 and a <=1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range a a 4 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL range a a 4 NULL # Using where; Using index select count(*) from t2 force index (a) where a>=-1 and a <=1; count(*) 20 explain select * from t2 force index (a) where a=0 and pk>=3; -id select_type table 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 select * from t2 force index (a) where a=0 and pk>=3; pk a b 3 0 3 @@ -111,15 +111,15 @@ pk a b # explain select count(*) from t2 force index (a) where a>0; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range a a 4 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL range a a 4 NULL # Using where; Using index select count(*) from t2 force index (a) where a>0; count(*) 990 explain select count(*) from t2 force index (a) where a>99; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range a a 4 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL range a a 4 NULL # Using where; Using index select count(*) from t2 force index (a) where a>99; count(*) 0 @@ -129,8 +129,8 @@ select * from t3 where pk>1000000; pk a b explain select count(*) from t2 force index (a) where a=2 and pk>25; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range a a 8 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL range a a 8 NULL # Using where; Using index select count(*) from t2 force index (a) where a=2 and pk>25; count(*) 4 @@ -203,8 +203,8 @@ max(pk) # support ICP over reverse scans. explain select * from t2 where a between 99 and 2000 order by a desc; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range a a 4 NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL range a a 4 NULL # Using where select * from t2 where a between 99 and 2000 order by a desc; pk a b 999 99 999 @@ -257,15 +257,15 @@ key(a,b,c) insert into t4 select pk,pk,pk,pk from t2 where pk < 100; explain select * from t4 where a=1 and b in (1) order by c desc; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t4 ref a a 10 const,const # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t4 NULL ref a a 10 const,const # Using where; Using index select * from t4 where a=1 and b in (1) order by c desc; pk a b c 1 1 1 1 explain select * from t4 where a=5 and b in (4) order by c desc; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t4 ref a a 10 const,const # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t4 NULL ref a a 10 const,const # Using where; Using index select * from t4 where a=5 and b in (4) order by c desc; pk a b c # HA_READ_PREFIX_LAST for reverse-ordered CF @@ -279,15 +279,15 @@ key(a,b,c) comment 'rev:cf2' insert into t5 select pk,pk,pk,pk from t2 where pk < 100; explain select * from t5 where a=1 and b in (1) order by c desc; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t5 ref a a 10 const,const # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t5 NULL ref a a 10 const,const # Using where; Using index select * from t5 where a=1 and b in (1) order by c desc; pk a b c 1 1 1 1 explain select * from t5 where a=5 and b in (4) order by c desc; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t5 ref a a 10 const,const # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t5 NULL ref a a 10 const,const # Using where; Using index select * from t5 where a=5 and b in (4) order by c desc; pk a b c drop table t0,t1,t2,t3,t4,t5; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/type_binary_indexes.result b/storage/rocksdb/mysql-test/rocksdb/r/type_binary_indexes.result index f61b9950c48..fe64c699ec4 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/type_binary_indexes.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/type_binary_indexes.result @@ -11,8 +11,8 @@ Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_par t1 0 PRIMARY 1 b20 A 1000 NULL NULL LSMTREE NO INSERT INTO t1 (b,b20,v16,v128) VALUES ('a','char1','varchar1a','varchar1b'),('a','char2','varchar2a','varchar2b'),('b','char3','varchar1a','varchar1b'),('c','char4','varchar3a','varchar3b'); EXPLAIN SELECT HEX(b20) FROM t1 ORDER BY b20; -id select_type table 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 HEX(b20) FROM t1 ORDER BY b20; HEX(b20) 6368617231000000000000000000000000000000 @@ -20,8 +20,8 @@ HEX(b20) 6368617233000000000000000000000000000000 6368617234000000000000000000000000000000 EXPLAIN SELECT HEX(b20) FROM t1 IGNORE INDEX (PRIMARY) ORDER BY b20 DESC; -id select_type table 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 HEX(b20) FROM t1 ORDER BY b20 DESC; HEX(b20) 6368617234000000000000000000000000000000 @@ -43,8 +43,8 @@ t1 1 v16 1 v16 A 500 10 NULL YES LSMTREE NO INSERT INTO t1 (b,b20,v16,v128,pk) VALUES ('a','char1','varchar1a','varchar1b',1),('a','char2','varchar2a','varchar2b',2),('b','char3','varchar1a','varchar1b',3),('c','char4','varchar3a','varchar3b',4),('d','char5','varchar4a','varchar3b',5),('e','char6','varchar2a','varchar3b',6); INSERT INTO t1 (b,b20,v16,v128,pk) SELECT b,b20,v16,v128,pk+100 FROM t1; EXPLAIN SELECT HEX(SUBSTRING(v16,0,3)) FROM t1 WHERE v16 LIKE 'varchar%'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range v16 v16 13 NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range v16 v16 13 NULL # Using where SELECT HEX(SUBSTRING(v16,7,3)) FROM t1 WHERE v16 LIKE 'varchar%'; HEX(SUBSTRING(v16,7,3)) 723161 @@ -60,8 +60,8 @@ HEX(SUBSTRING(v16,7,3)) 723461 723461 EXPLAIN SELECT HEX(SUBSTRING(v16,0,3)) FROM t1 FORCE INDEX (v16) WHERE v16 LIKE 'varchar%'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range v16 v16 13 NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range v16 v16 13 NULL # Using where SELECT HEX(SUBSTRING(v16,7,3)) FROM t1 FORCE INDEX (v16) WHERE v16 LIKE 'varchar%'; HEX(SUBSTRING(v16,7,3)) 723161 diff --git a/storage/rocksdb/mysql-test/rocksdb/r/type_varchar.result b/storage/rocksdb/mysql-test/rocksdb/r/type_varchar.result index 745cfa5991e..5ebed6f293d 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/type_varchar.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/type_varchar.result @@ -196,8 +196,8 @@ test.t1 analyze status OK # Must show 'using index' for latin1_bin and utf8_bin: explain select col1, hex(col1) from t1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL col1 67 NULL # Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL index NULL col1 67 NULL # Using index select col1, hex(col1) from t1; col1 hex(col1) a 61202009 @@ -207,8 +207,8 @@ ab 6162 # Must show 'using index' for latin1_bin and utf8_bin: explain select col1, hex(col1) from t1 force index(col1) where col1 < 'b'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 # col1 col1 67 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL # col1 col1 67 NULL # Using where; Using index select col1, hex(col1) from t1 force index(col1) where col1 < 'b'; col1 hex(col1) a 61202009 @@ -226,8 +226,8 @@ insert into t1 values(22, repeat(' ',17), '17x-space'); insert into t1 values(23, repeat(' ',18), '18x-space'); explain select pk, col1, hex(col1), length(col1) from t1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 # NULL col1 67 NULL # Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL # NULL col1 67 NULL # Using index select pk, col1, hex(col1), length(col1) from t1; pk col1 hex(col1) length(col1) 10 0 @@ -312,8 +312,8 @@ test.t1 analyze status OK # Must show 'using index' for latin1_bin and utf8_bin: explain select col1, hex(col1) from t1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL col1 195 NULL # Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL index NULL col1 195 NULL # Using index select col1, hex(col1) from t1; col1 hex(col1) a 61202009 @@ -323,8 +323,8 @@ ab 6162 # Must show 'using index' for latin1_bin and utf8_bin: explain select col1, hex(col1) from t1 force index(col1) where col1 < 'b'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 # col1 col1 195 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL # col1 col1 195 NULL # Using where; Using index select col1, hex(col1) from t1 force index(col1) where col1 < 'b'; col1 hex(col1) a 61202009 @@ -342,8 +342,8 @@ insert into t1 values(22, repeat(' ',17), '17x-space'); insert into t1 values(23, repeat(' ',18), '18x-space'); explain select pk, col1, hex(col1), length(col1) from t1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 # NULL col1 195 NULL # Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL # NULL col1 195 NULL # Using index select pk, col1, hex(col1), length(col1) from t1; pk col1 hex(col1) length(col1) 10 0 @@ -428,8 +428,8 @@ test.t1 analyze status OK # Must show 'using index' for latin1_bin and utf8_bin: explain select col1, hex(col1) from t1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL # +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # select col1, hex(col1) from t1; col1 hex(col1) ab 00610062 @@ -439,8 +439,8 @@ a 0061002000200009 # Must show 'using index' for latin1_bin and utf8_bin: explain select col1, hex(col1) from t1 force index(col1) where col1 < 'b'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 # col1 col1 131 NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL # col1 col1 131 NULL # Using where select col1, hex(col1) from t1 force index(col1) where col1 < 'b'; col1 hex(col1) a 0061002000200009 @@ -458,8 +458,8 @@ insert into t1 values(22, repeat(' ',17), '17x-space'); insert into t1 values(23, repeat(' ',18), '18x-space'); explain select pk, col1, hex(col1), length(col1) from t1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 # NULL NULL NULL NULL # +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL # NULL NULL NULL NULL # select pk, col1, hex(col1), length(col1) from t1; pk col1 hex(col1) length(col1) 10 0 @@ -544,8 +544,8 @@ test.t1 analyze status OK # Must show 'using index' for latin1_bin and utf8_bin: explain select col1, hex(col1) from t1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL # +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # select col1, hex(col1) from t1; col1 hex(col1) ab 6162 @@ -555,8 +555,8 @@ a 61202009 # Must show 'using index' for latin1_bin and utf8_bin: explain select col1, hex(col1) from t1 force index(col1) where col1 < 'b'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 # col1 col1 259 NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL # col1 col1 259 NULL # Using where select col1, hex(col1) from t1 force index(col1) where col1 < 'b'; col1 hex(col1) a 61202009 @@ -574,8 +574,8 @@ insert into t1 values(22, repeat(' ',17), '17x-space'); insert into t1 values(23, repeat(' ',18), '18x-space'); explain select pk, col1, hex(col1), length(col1) from t1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 # NULL NULL NULL NULL # +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL # NULL NULL NULL NULL # select pk, col1, hex(col1), length(col1) from t1; pk col1 hex(col1) length(col1) 10 0 @@ -660,8 +660,8 @@ test.t1 analyze status OK # Must show 'using index' for latin1_bin and utf8_bin: explain select col1, hex(col1) from t1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL # +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # select col1, hex(col1) from t1; col1 hex(col1) ab 00610062 @@ -671,8 +671,8 @@ a 0061002000200009 # Must show 'using index' for latin1_bin and utf8_bin: explain select col1, hex(col1) from t1 force index(col1) where col1 < 'b'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 # col1 col1 259 NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL # col1 col1 259 NULL # Using where select col1, hex(col1) from t1 force index(col1) where col1 < 'b'; col1 hex(col1) a 0061002000200009 @@ -690,8 +690,8 @@ insert into t1 values(22, repeat(' ',17), '17x-space'); insert into t1 values(23, repeat(' ',18), '18x-space'); explain select pk, col1, hex(col1), length(col1) from t1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 # NULL NULL NULL NULL # +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL # NULL NULL NULL NULL # select pk, col1, hex(col1), length(col1) from t1; pk col1 hex(col1) length(col1) 10 0 @@ -739,8 +739,8 @@ drop table t1; create table t (id int primary key, email varchar(100), KEY email_i (email(30))); insert into t values (1, 'abcabcabcabcabcabcabcabcabcabcabc '); explain select 'email_i' as index_name, count(*) AS count from t force index(email_i); -id select_type table 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 @@ -752,8 +752,8 @@ set session rocksdb_verify_row_debug_checksums = on; create table t (id int primary key, email varchar(100), KEY email_i (email(30))); insert into t values (1, 'a'); explain select 'email_i' as index_name, count(*) AS count from t force index(email_i); -id select_type table 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/t/index_merge_rocksdb.test b/storage/rocksdb/mysql-test/rocksdb/t/index_merge_rocksdb.test index 887b4dd6a65..c79a6a327e5 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/index_merge_rocksdb.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/index_merge_rocksdb.test @@ -79,7 +79,7 @@ ANALYZE TABLE t1; -- enable_result_log -- enable_query_log ---replace_column 9 # +--replace_column 10 # EXPLAIN UPDATE t1 SET filler1='to be deleted' WHERE key1=100 AND key2=100; UPDATE t1 SET filler1='to be deleted' WHERE key1=100 and key2=100; @@ -99,9 +99,9 @@ while ($i <= 1000) { set global rocksdb_force_flush_memtable_now=1; analyze table t1; ---replace_column 9 # +--replace_column 10 # explain select * from t1 where key1 = 1; ---replace_column 9 # +--replace_column 10 # explain select key1,key2 from t1 where key1 = 1 or key2 = 1; select * from t1 where key1 = 1; select key1,key2 from t1 where key1 = 1 or key2 = 1; diff --git a/storage/rocksdb/mysql-test/rocksdb/t/rocksdb_cf_reverse.test b/storage/rocksdb/mysql-test/rocksdb/t/rocksdb_cf_reverse.test index 8e30332bafe..303a9c4d8b8 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/rocksdb_cf_reverse.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/rocksdb_cf_reverse.test @@ -22,22 +22,22 @@ insert into t1 select a+10,a+10,a+10 from t0; --echo # returns rows in ascending order: select * from t1; ---replace_column 9 # +--replace_column 10 # explain select a from t1 order by a limit 5; select a from t1 order by a limit 5; ---replace_column 9 # +--replace_column 10 # explain select b from t1 order by b limit 5; select a from t1 order by a limit 5; ---replace_column 9 # +--replace_column 10 # explain select a from t1 order by a desc limit 5; select a from t1 order by a desc limit 5; ---replace_column 9 # +--replace_column 10 # explain select b from t1 order by b desc limit 5; select b from t1 order by b desc limit 5; diff --git a/storage/rocksdb/mysql-test/rocksdb/t/rocksdb_parts.test b/storage/rocksdb/mysql-test/rocksdb/t/rocksdb_parts.test index b8d41ae942a..b4b88429471 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/rocksdb_parts.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/rocksdb_parts.test @@ -59,7 +59,7 @@ insert into t1 values (12,20,20); set @tmp_rfirr= @@rocksdb_force_index_records_in_range; set rocksdb_force_index_records_in_range= 12; ---replace_column 9 # +--replace_column 10 # explain select * from t1 force index(col1) where col1=10; select * from t1 force index(col1) where col1=10; select * from t1 use index () where col1=10; diff --git a/storage/rocksdb/mysql-test/rocksdb/t/rocksdb_range.test b/storage/rocksdb/mysql-test/rocksdb/t/rocksdb_range.test index f4b6096c696..ef4a68b7643 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/rocksdb_range.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/rocksdb_range.test @@ -30,18 +30,18 @@ insert into t2 select A.a, FLOOR(A.a/10), A.a from t1 A; --echo # --echo # Original failure was here: ---replace_column 9 # +--replace_column 10 # explain select * from t2 force index (a) where a=0; select * from t2 force index (a) where a=0; --echo # The rest are for code coverage: ---replace_column 9 # +--replace_column 10 # explain select * from t2 force index (a) where a=2; select * from t2 force index (a) where a=2; ---replace_column 9 # +--replace_column 10 # explain select * from t2 force index (a) where a=3 and pk=33; select * from t2 force index (a) where a=3 and pk=33; @@ -56,17 +56,17 @@ select * from t2 force index (a) where a=100 and pk in (101,102); --echo # --echo # #36: Range in form tbl.key >= const doesn't work in reverse column family --echo # ---replace_column 9 # +--replace_column 10 # explain select count(*) from t2 force index (a) where a>=0 and a <=1; select count(*) from t2 force index (a) where a>=0 and a <=1; ---replace_column 9 # +--replace_column 10 # explain select count(*) from t2 force index (a) where a>=-1 and a <=1; select count(*) from t2 force index (a) where a>=-1 and a <=1; ---replace_column 9 # +--replace_column 10 # explain select * from t2 force index (a) where a=0 and pk>=3; select * from t2 force index (a) where a=0 and pk>=3; @@ -81,12 +81,12 @@ select * from t2 where pk>=1000000; --echo # --echo # #42: Range in form tbl.key > const doesn't work in reverse column family --echo # ---replace_column 9 # +--replace_column 10 # explain select count(*) from t2 force index (a) where a>0; select count(*) from t2 force index (a) where a>0; ---replace_column 9 # +--replace_column 10 # explain select count(*) from t2 force index (a) where a>99; select count(*) from t2 force index (a) where a>99; @@ -94,7 +94,7 @@ select count(*) from t2 force index (a) where a>99; select * from t2 where pk>1000000; select * from t3 where pk>1000000; ---replace_column 9 # +--replace_column 10 # explain select count(*) from t2 force index (a) where a=2 and pk>25; select count(*) from t2 force index (a) where a=2 and pk>25; @@ -131,7 +131,7 @@ select max(pk) from t3 where a=3 and pk < 33; --echo # Note: the next explain has "Using index condition" in fb/mysql-5.6 --echo # but "Using where" in MariaDB because the latter does not --echo # support ICP over reverse scans. ---replace_column 9 # +--replace_column 10 # explain select * from t2 where a between 99 and 2000 order by a desc; select * from t2 where a between 99 and 2000 order by a desc; @@ -162,12 +162,12 @@ create table t4 ( insert into t4 select pk,pk,pk,pk from t2 where pk < 100; ---replace_column 9 # +--replace_column 10 # explain select * from t4 where a=1 and b in (1) order by c desc; select * from t4 where a=1 and b in (1) order by c desc; ---replace_column 9 # +--replace_column 10 # explain select * from t4 where a=5 and b in (4) order by c desc; select * from t4 where a=5 and b in (4) order by c desc; @@ -183,12 +183,12 @@ create table t5 ( insert into t5 select pk,pk,pk,pk from t2 where pk < 100; ---replace_column 9 # +--replace_column 10 # explain select * from t5 where a=1 and b in (1) order by c desc; select * from t5 where a=1 and b in (1) order by c desc; ---replace_column 9 # +--replace_column 10 # explain select * from t5 where a=5 and b in (4) order by c desc; select * from t5 where a=5 and b in (4) order by c desc; diff --git a/storage/rocksdb/mysql-test/rocksdb/t/type_binary_indexes.test b/storage/rocksdb/mysql-test/rocksdb/t/type_binary_indexes.test index f4360ed629b..bac577f01e0 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/type_binary_indexes.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/type_binary_indexes.test @@ -27,11 +27,11 @@ SHOW INDEX IN t1; INSERT INTO t1 (b,b20,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 HEX(b20) FROM t1 ORDER BY b20; SELECT HEX(b20) FROM t1 ORDER BY b20; ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT HEX(b20) FROM t1 IGNORE INDEX (PRIMARY) ORDER BY b20 DESC; SELECT HEX(b20) FROM t1 ORDER BY b20 DESC; @@ -52,17 +52,17 @@ SHOW INDEX IN t1; INSERT INTO t1 (b,b20,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 HEX(b), HEX(v128) FROM t1 WHERE b != 'a' AND v128 > 'varchar'; --sorted_result SELECT HEX(b), HEX(v128) FROM t1 WHERE b != 'a' AND v128 > 'varchar'; ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT HEX(b), HEX(v128) FROM t1 USE INDEX (b_v) WHERE b != 'a' AND v128 > 'varchar'; --sorted_result SELECT HEX(b), HEX(v128) FROM t1 USE INDEX (b_v) WHERE b != 'a' AND v128 > 'varchar'; ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT HEX(v128), COUNT(*) FROM t1 GROUP BY HEX(v128); --sorted_result SELECT HEX(v128), COUNT(*) FROM t1 GROUP BY HEX(v128); @@ -84,12 +84,12 @@ SHOW INDEX IN t1; INSERT INTO t1 (b,b20,v16,v128,pk) VALUES ('a','char1','varchar1a','varchar1b',1),('a','char2','varchar2a','varchar2b',2),('b','char3','varchar1a','varchar1b',3),('c','char4','varchar3a','varchar3b',4),('d','char5','varchar4a','varchar3b',5),('e','char6','varchar2a','varchar3b',6); INSERT INTO t1 (b,b20,v16,v128,pk) SELECT b,b20,v16,v128,pk+100 FROM t1; ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT HEX(SUBSTRING(v16,0,3)) FROM t1 WHERE v16 LIKE 'varchar%'; --sorted_result SELECT HEX(SUBSTRING(v16,7,3)) FROM t1 WHERE v16 LIKE 'varchar%'; ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT HEX(SUBSTRING(v16,0,3)) FROM t1 FORCE INDEX (v16) WHERE v16 LIKE 'varchar%'; --sorted_result SELECT HEX(SUBSTRING(v16,7,3)) FROM t1 FORCE INDEX (v16) WHERE v16 LIKE 'varchar%'; diff --git a/storage/rocksdb/mysql-test/rocksdb/t/type_varchar.test b/storage/rocksdb/mysql-test/rocksdb/t/type_varchar.test index b631615c266..ee85c8902bf 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/type_varchar.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/type_varchar.test @@ -52,7 +52,7 @@ drop table t1; # Issue #306 - Do not store trailing spaces for prefixed keys. create table t (id int primary key, email varchar(100), KEY email_i (email(30))); insert into t values (1, 'abcabcabcabcabcabcabcabcabcabcabc '); ---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; @@ -63,7 +63,7 @@ set global rocksdb_checksums_pct = 100; set session rocksdb_verify_row_debug_checksums = on; create table t (id int primary key, email varchar(100), KEY email_i (email(30))); insert into t values (1, 'a'); ---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_varchar_endspace.inc b/storage/rocksdb/mysql-test/rocksdb/t/type_varchar_endspace.inc index 494f0ea1395..c72c2a3f91a 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/type_varchar_endspace.inc +++ b/storage/rocksdb/mysql-test/rocksdb/t/type_varchar_endspace.inc @@ -45,13 +45,13 @@ insert into t1 values (3, 'a \t', 'a-tab'); analyze table t1; --echo # Must show 'using index' for latin1_bin and utf8_bin: ---replace_column 9 # +--replace_column 10 # explain select col1, hex(col1) from t1; select col1, hex(col1) from t1; --echo # Must show 'using index' for latin1_bin and utf8_bin: ---replace_column 4 # 9 # +--replace_column 5 # 10 # explain select col1, hex(col1) from t1 force index(col1) where col1 < 'b'; select col1, hex(col1) from t1 force index(col1) where col1 < 'b'; @@ -67,7 +67,7 @@ insert into t1 values(21, repeat(' ', 9), '9x-space'); insert into t1 values(22, repeat(' ',17), '17x-space'); insert into t1 values(23, repeat(' ',18), '18x-space'); ---replace_column 4 # 9 # +--replace_column 5 # 10 # explain select pk, col1, hex(col1), length(col1) from t1; select pk, col1, hex(col1), length(col1) from t1;