revision-id: 500d909a795022255fb6f6c44cd310636afc0b5a (mariadb-10.3.6-226-g500d909) parent(s): 88d89ee0bae24b71416c2af4f4c2f2be7b6a033a author: Varun Gupta committer: Varun Gupta timestamp: 2019-03-11 20:20:35 +0530 message:

MDEV-18741: Optimizer trace: multi-part key ranges are printed incorrectly

Keys with multi-key parts were not being printed correctly, only the first key part was getting printed. Fixed it by making sure append_range_all_keyparts function is called for the remaining keyparts.

...

diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index 4c3e2b3..a4c2b86 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -6022,3 +6022,428 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { set @@optimizer_switch= @save_optimizer_switch; drop table t1,t2; set optimizer_trace='enabled=off'; +# +# MDEV-18741: Optimizer trace: multi-part key ranges are printed incorrectly +# +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, c int, d int, key (a,b), key(c,d)); +insert into t1 select a,a,a,a from one_k; +set optimizer_trace=1; +explain format=json select * from t1 where a > 10 and b < 10 and c=0 and d=1; ... +select * from INFORMATION_SCHEMA.OPTIMIZER_TRACe;

This produces a lot of irrelevant output. Can you use something like select JSON_DETAILED(JSON_EXTRACT(a, '$**.range_analysis')) from optimizer_trace to select on the part of the trace that we need?

+ "analyzing_range_alternatives": { + "range_scan_alternatives": [ + { + "index": "a", + "ranges": ["10 < a AND NULL < b < 10"],

This is not a valid range. If one forces this quick select to be picked for the query plan, they can look into .trace and see the ranges: explain format=json select * from t1 force index (a) where a > 10 and b < 10; T@19 : | | | | | | | | | | >print_quick quick range select, key a, length: 5 10 < X other_keys: 0x0: T@19 : | | | | | | | | | | <print_quick (you can also try with a >=10 to see a different kind of range being produced). (One could argue that what is printed is a representation of the SEL_ARG graph, but in that case it is extremely confusing. I think, we should start with showing ranges, not SEL_ARG graphs). I think, we should not try to intrepret the SEL_ARG graph ourselves but rather use sel_arg_range_seq_init / sel_arg_range_seq_next to produce ranges that can be printed. Any objections to this?

+ "rowid_ordered": false, + "using_mrr": false, + "index_only": false, + "rows": 989, + "cost": 1272.8, + "chosen": false, + "cause": "cost" + }, + { + "index": "c", + "ranges": ["0 <= c <= 0 AND 1 <= d <= 1"], + "rowid_ordered": true, + "using_mrr": false, + "index_only": false, + "rows": 1, + "cost": 2.3783, + "chosen": true + } + ],

BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog