diff options
Diffstat (limited to 'mysql-test/main/opt_trace.result')
| -rw-r--r-- | mysql-test/main/opt_trace.result | 234 |
1 files changed, 230 insertions, 4 deletions
diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index fdf2d2a338a..94bf20aaa4f 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -6,7 +6,7 @@ OPTIMIZER_TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OPTIMIZER_TRACE INSUFFICIENT_PRIVILEGES show variables like 'optimizer_trace'; Variable_name Value -optimizer_trace enabled=off +optimizer_trace enabled=on set optimizer_trace="enabled=on"; show variables like 'optimizer_trace'; Variable_name Value @@ -89,6 +89,13 @@ select * from v1 { } }, { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, + { "table_dependencies": [ { "table": "t1", @@ -240,6 +247,13 @@ select * from (select * from t1 where t1.a=1)q { } }, { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, + { "table_dependencies": [ { "table": "t1", @@ -372,6 +386,13 @@ select * from v2 { "select_id": 1, "steps": [ { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, + { "join_optimization": { "select_id": 2, "steps": [ @@ -396,6 +417,13 @@ select * from v2 { } }, { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, + { "table_dependencies": [ { "table": "t1", @@ -627,6 +655,13 @@ explain select * from v2 { "select_id": 1, "steps": [ { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, + { "table_dependencies": [ { "table": "t2", @@ -746,10 +781,24 @@ explain select * from v1 { "select_id": 1, "steps": [ { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, + { "join_optimization": { "select_id": 2, "steps": [ { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, + { "table_dependencies": [ { "table": "t1", @@ -970,6 +1019,13 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { } }, { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, + { "table_dependencies": [ { "table": "t1", @@ -1201,6 +1257,13 @@ EXPLAIN SELECT DISTINCT a FROM t1 { "select_id": 1, "steps": [ { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, + { "table_dependencies": [ { "table": "t1", @@ -1391,6 +1454,13 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { } }, { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, + { "table_dependencies": [ { "table": "t1", @@ -1609,6 +1679,13 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id { } }, { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, + { "table_dependencies": [ { "table": "t1", @@ -1805,6 +1882,13 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id { } }, { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, + { "table_dependencies": [ { "table": "t1", @@ -2028,6 +2112,13 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { } }, { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, + { "table_dependencies": [ { "table": "t1", @@ -2384,6 +2475,13 @@ select t1.a from t1 left join t2 on t1.a=t2.a { "select_id": 1, "steps": [ { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": ["t1.a = t2.a"], + "transformed_expr": ["multiple equal(t1.a, t2.a)"] + } + }, + { "table_dependencies": [ { "table": "t1", @@ -2514,6 +2612,13 @@ explain select * from t1 left join t2 on t2.a=t1.a { "select_id": 1, "steps": [ { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": ["t2.a = t1.a"], + "transformed_expr": ["multiple equal(t2.a, t1.a)"] + } + }, + { "table_dependencies": [ { "table": "t1", @@ -2679,6 +2784,15 @@ explain select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and "select_id": 1, "steps": [ { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": ["t2.a = t1.a and t3.a = t1.a and t2.b = t3.b"], + "transformed_expr": [ + "multiple equal(t2.a, t1.a, t3.a) and multiple equal(t2.b, t3.b)" + ] + } + }, + { "table_dependencies": [ { "table": "t1", @@ -2917,6 +3031,13 @@ explain extended select * from t1 where a in (select pk from t10) { } }, { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, + { "table_dependencies": [ { "table": "t1", @@ -3206,6 +3327,13 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { } }, { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, + { "table_dependencies": [ { "table": "t1", @@ -3548,6 +3676,13 @@ select f1(a) from t1 { "select_id": 1, "steps": [ { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, + { "table_dependencies": [ { "table": "t1", @@ -3651,6 +3786,13 @@ select f2(a) from t1 { "select_id": 1, "steps": [ { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, + { "table_dependencies": [ { "table": "t1", @@ -3746,7 +3888,7 @@ a 2 select length(trace) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; length(trace) -2342 +2531 set optimizer_trace_max_mem_size=100; select * from t1; a @@ -3760,7 +3902,7 @@ select * from t1 { "join_preparation": { "select_id": 1, "steps": [ - 2242 0 + 2431 0 set optimizer_trace_max_mem_size=0; select * from t1; a @@ -3768,7 +3910,7 @@ a 2 select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES -select * from t1 2342 0 +select * from t1 2531 0 drop table t1; set optimizer_trace='enabled=off'; set @@optimizer_trace_max_mem_size= @save_optimizer_trace_max_mem_size; @@ -3893,6 +4035,13 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { } }, { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, + { "table_dependencies": [ { "table": "t0", @@ -4263,10 +4412,24 @@ explain select * from (select rand() from t1)q { } }, { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, + { "join_optimization": { "select_id": 2, "steps": [ { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, + { "table_dependencies": [ { "table": "t1", @@ -4514,6 +4677,13 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ } }, { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, + { "table_dependencies": [ { "table": "t1", @@ -4999,6 +5169,13 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { } }, { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, + { "table_dependencies": [ { "table": "t_outer_1", @@ -6579,6 +6756,13 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { } }, { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, + { "table_dependencies": [ { "table": "t_outer_1", @@ -8755,6 +8939,48 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.on_clause_after_substitution')) "t3.a = t1.a" ] ] +# +# MDEV-23646: Optimizer trace: optimize_cond() should show ON expression processing +# +SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.condition_processing')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; +JSON_DETAILED(JSON_EXTRACT(trace, '$**.condition_processing')) +[ + + { + "condition": "WHERE", + "original_condition": "t1.a = t2.a", + "steps": + [ + + { + "transformation": "equality_propagation", + "resulting_condition": "multiple equal(t1.a, t2.a)" + }, + + { + "transformation": "constant_propagation", + "resulting_condition": "multiple equal(t1.a, t2.a)" + }, + + { + "transformation": "trivial_condition_removal", + "resulting_condition": "multiple equal(t1.a, t2.a)" + } + ] + }, + + { + "condition": "ON CLAUSE", + "original_expr": + [ + "t2.a = t3.a" + ], + "transformed_expr": + [ + "multiple equal(t1.a, t2.a, t3.a)" + ] + } +] DROP TABLE t1,t2,t3; set optimizer_trace='enabled=off'; # End of 10.4 tests |