diff options
author | Varun Gupta <varun.gupta@mariadb.com> | 2019-05-28 15:43:12 +0530 |
---|---|---|
committer | Varun Gupta <varun.gupta@mariadb.com> | 2019-05-28 17:17:44 +0530 |
commit | 5e36f5dd00d706baea7af623f09711d66ba0109c (patch) | |
tree | 27d460a04c08255b7b116a4138fac3f99a78eea6 /mysql-test/main/opt_trace.test | |
parent | 24773bf38024d32c9af4e6bc09e67043318bba6e (diff) | |
download | mariadb-git-5e36f5dd00d706baea7af623f09711d66ba0109c.tar.gz |
MDEV-18741: Optimizer trace: multi-part key ranges are printed incorrectly
Changed the function append_range_all_keyparts to use sel_arg_range_seq_init / sel_arg_range_seq_next to produce ranges.
Also adjusted to print format for the ranges, now the ranges are printed as:
(keypart1_min, keypart2_min,..) OP (keypart1_name,keypart2_name, ..) OP (keypart1_max,keypart2_max, ..)
Also added more tests for range and index merge access for optimizer trace
Diffstat (limited to 'mysql-test/main/opt_trace.test')
-rw-r--r-- | mysql-test/main/opt_trace.test | 57 |
1 files changed, 57 insertions, 0 deletions
diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test index 4ec7c338acd..981a53ac1ad 100644 --- a/mysql-test/main/opt_trace.test +++ b/mysql-test/main/opt_trace.test @@ -387,4 +387,61 @@ SELECT COUNT(*) FROM v1 WHERE MATCH (f) AGAINST ('fooba'); DROP VIEW v1; DROP TABLE t1; +--echo # +--echo # MDEV-18741: Optimizer trace: multi-part key ranges are printed incorrectly. +--echo # + +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table one_k (a int); +insert into one_k select A.a + B.a*10 + C.a*100 from t0 A, t0 B, t0 C; +create table t1 ( a int, b int, key a_b(a,b)); +insert into t1 select a,a from one_k; +set optimizer_trace='enabled=on'; + +explain select * from t1 force index (a_b) where a=2 and b=4; +select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; + +explain select * from t1 where a >= 900 and b between 10 and 20; +select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; + +drop table t0,t1; + +create table t1 (start_date date, end_date date, filler char(100), key(start_date, end_date)) ; +--disable_warnings +insert into t1 select date_add(now(), interval a day), date_add(now(), interval (a+7) day), 'data' from one_k; +--enable_warnings +explain select * from t1 force index(start_date) where start_date >= '2019-02-10' and end_date <'2019-04-01'; +select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; +drop table t1,one_k; + +create table ten(a int); +insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 ( + a int not null, + b int not null, + c int not null, + d int not null, + key a_b_c(a,b,c) +); + +insert into t1 select a,a, a,a from ten; +explain select * from t1 force index(a_b_c) where a between 1 and 4 and b < 50; +select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; +drop table ten,t1; + +--echo # Ported test from MYSQL for ranges involving Binary column + +CREATE TABLE t1(i INT PRIMARY KEY, b BINARY(16), INDEX i_b(b)); +INSERT INTO t1 VALUES (1, x'D95B94336A9946A39CF5B58CFE772D8C'); +INSERT INTO t1 VALUES (2, NULL); + +EXPLAIN SELECT * FROM t1 WHERE b IN (0xD95B94336A9946A39CF5B58CFE772D8C); +select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; + +EXPLAIN SELECT * FROM t1 WHERE b IS NULL; +select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; + +drop table t1; + set optimizer_trace='enabled=off'; |