diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/main/opt_trace.result | 234 | ||||
| -rw-r--r-- | mysql-test/main/opt_trace.test | 6 | ||||
| -rw-r--r-- | mysql-test/main/opt_trace_index_merge.result | 7 | ||||
| -rw-r--r-- | mysql-test/main/opt_trace_index_merge_innodb.result | 7 | ||||
| -rw-r--r-- | mysql-test/main/opt_trace_security.result | 14 | ||||
| -rw-r--r-- | mysql-test/main/subselect2.result | 4 |
6 files changed, 266 insertions, 6 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 diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test index 99ddbae8079..9f83a596e38 100644 --- a/mysql-test/main/opt_trace.test +++ b/mysql-test/main/opt_trace.test @@ -642,6 +642,12 @@ where t1.a = t2.a; SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.where_clause_after_substitution')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.having_clause_after_substitution')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.on_clause_after_substitution')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; + +--echo # +--echo # MDEV-23646: Optimizer trace: optimize_cond() should show ON expression processing +--echo # + +SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.condition_processing')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; DROP TABLE t1,t2,t3; set optimizer_trace='enabled=off'; diff --git a/mysql-test/main/opt_trace_index_merge.result b/mysql-test/main/opt_trace_index_merge.result index c64758bcaa4..a938dc106fd 100644 --- a/mysql-test/main/opt_trace_index_merge.result +++ b/mysql-test/main/opt_trace_index_merge.result @@ -54,6 +54,13 @@ explain select * from t1 where a=1 or b=1 { } }, { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, + { "table_dependencies": [ { "table": "t1", diff --git a/mysql-test/main/opt_trace_index_merge_innodb.result b/mysql-test/main/opt_trace_index_merge_innodb.result index 614595c380a..427384c5ec4 100644 --- a/mysql-test/main/opt_trace_index_merge_innodb.result +++ b/mysql-test/main/opt_trace_index_merge_innodb.result @@ -62,6 +62,13 @@ explain select * from t1 where pk1 != 0 and key1 = 1 { } }, { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, + { "table_dependencies": [ { "table": "t1", diff --git a/mysql-test/main/opt_trace_security.result b/mysql-test/main/opt_trace_security.result index c94d7231ce0..c5386c39670 100644 --- a/mysql-test/main/opt_trace_security.result +++ b/mysql-test/main/opt_trace_security.result @@ -65,6 +65,13 @@ select * from db1.t1 { "select_id": 1, "steps": [ { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, + { "table_dependencies": [ { "table": "t1", @@ -193,6 +200,13 @@ select * from db1.v1 { "select_id": 1, "steps": [ { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, + { "table_dependencies": [ { "table": "t1", diff --git a/mysql-test/main/subselect2.result b/mysql-test/main/subselect2.result index a3d7fda7abc..6084d7e6209 100644 --- a/mysql-test/main/subselect2.result +++ b/mysql-test/main/subselect2.result @@ -339,10 +339,10 @@ where t1.a = t2.a and ( t1.a = ( select min(a) from t1 ) or 0 ); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) -1 PRIMARY t3 ref idx idx 6 func 2 100.00 Using where; Using index +1 PRIMARY t3 ref idx idx 6 const 1 100.00 Using where; Using index 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,`test`.`t3`.`a` AS `a` from `test`.`t1` join `test`.`t2` left join `test`.`t3` on(`test`.`t3`.`a` = `test`.`t1`.`a`) where `test`.`t1`.`a` = (/* select#2 */ select min(`test`.`t1`.`a`) from `test`.`t1`) and `test`.`t2`.`a` = (/* select#2 */ select min(`test`.`t1`.`a`) from `test`.`t1`) +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,`test`.`t3`.`a` AS `a` from `test`.`t1` join `test`.`t2` left join `test`.`t3` on(`test`.`t3`.`a` = (/* select#2 */ select min(`test`.`t1`.`a`) from `test`.`t1`)) where `test`.`t1`.`a` = (/* select#2 */ select min(`test`.`t1`.`a`) from `test`.`t1`) and `test`.`t2`.`a` = (/* select#2 */ select min(`test`.`t1`.`a`) from `test`.`t1`) select * from t1, t2 left join t3 on ( t2.a = t3.a ) where t1.a = t2.a and ( t1.a = ( select min(a) from t1 ) or 0 ); a a a |