- commits - lists.mariadb.org

[Commits] 5a73769ca43: MDEV-23645: Optimizer trace: print conditions after substitute_for_best_equal_field
by psergey 19 Mar '21

19 Mar '21

revision-id: 5a73769ca4377e76b4019b9cecbc942401052923 (mariadb-10.5.2-488-g5a73769ca43) parent(s): 2b3fd5dff04c699a4743867f73a1bacc8549abe1 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-03-19 17:32:08 +0300 message: MDEV-23645: Optimizer trace: print conditions after substitute_for_best_equal_field Print the conditions for WHERE, HAVING, and ON. --- mysql-test/main/opt_trace.result | 236 ++++++++++++++++++--- mysql-test/main/opt_trace.test | 53 +++++ mysql-test/main/opt_trace_index_merge.result | 7 +- .../main/opt_trace_index_merge_innodb.result | 7 +- mysql-test/main/opt_trace_security.result | 2 - sql/opt_trace.cc | 12 ++ sql/opt_trace.h | 4 + sql/sql_select.cc | 15 +- 8 files changed, 301 insertions(+), 35 deletions(-) diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index 36bbea8ae78..192f9040c35 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -153,9 +153,14 @@ select * from v1 { { "best_join_order": ["t1"] }, + { + "substitute_best_equal": { + "condition": "WHERE", + "resulting_condition": "t1.a = 1" + } + }, { "attaching_conditions_to_tables": { - "original_condition": "t1.a = 1", "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -299,9 +304,14 @@ select * from (select * from t1 where t1.a=1)q { { "best_join_order": ["t1"] }, + { + "substitute_best_equal": { + "condition": "WHERE", + "resulting_condition": "t1.a = 1" + } + }, { "attaching_conditions_to_tables": { - "original_condition": "t1.a = 1", "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -452,9 +462,14 @@ select * from v2 { { "best_join_order": ["t1"] }, + { + "substitute_best_equal": { + "condition": "WHERE", + "resulting_condition": "t1.a = 1" + } + }, { "attaching_conditions_to_tables": { - "original_condition": "t1.a = 1", "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -520,7 +535,6 @@ select * from v2 { }, { "attaching_conditions_to_tables": { - "original_condition": null, "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -659,7 +673,6 @@ explain select * from v2 { }, { "attaching_conditions_to_tables": { - "original_condition": null, "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -779,7 +792,6 @@ explain select * from v1 { }, { "attaching_conditions_to_tables": { - "original_condition": null, "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -845,7 +857,6 @@ explain select * from v1 { }, { "attaching_conditions_to_tables": { - "original_condition": null, "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -1103,9 +1114,14 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { { "best_join_order": ["t1", "t2"] }, + { + "substitute_best_equal": { + "condition": "WHERE", + "resulting_condition": "t2.a = t1.b and t1.a = t2.b + 2" + } + }, { "attaching_conditions_to_tables": { - "original_condition": "t2.a = t1.b and t1.a = t2.b + 2", "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -1274,7 +1290,6 @@ EXPLAIN SELECT DISTINCT a FROM t1 { }, { "attaching_conditions_to_tables": { - "original_condition": null, "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -1465,9 +1480,14 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { { "best_join_order": ["t1"] }, + { + "substitute_best_equal": { + "condition": "WHERE", + "resulting_condition": "t1.b = 2 and t1.c = 3" + } + }, { "attaching_conditions_to_tables": { - "original_condition": "t1.b = 2 and t1.c = 3", "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -1659,9 +1679,14 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id { { "best_join_order": ["t1"] }, + { + "substitute_best_equal": { + "condition": "WHERE", + "resulting_condition": "t1.a >= 20010104e0" + } + }, { "attaching_conditions_to_tables": { - "original_condition": "t1.a >= 20010104e0", "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -1842,9 +1867,14 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id { { "best_join_order": ["t1"] }, + { + "substitute_best_equal": { + "condition": "WHERE", + "resulting_condition": "t1.a = 20010104e0" + } + }, { "attaching_conditions_to_tables": { - "original_condition": "t1.a = 20010104e0", "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -2120,9 +2150,14 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { { "best_join_order": ["t1"] }, + { + "substitute_best_equal": { + "condition": "WHERE", + "resulting_condition": "t1.a = 1 and t1.b = 2" + } + }, { "attaching_conditions_to_tables": { - "original_condition": "t1.a = 1 and t1.b = 2", "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -2360,12 +2395,17 @@ select t1.a from t1 left join t2 on t1.a=t2.a { { "best_join_order": ["t2", "t1"] }, + { + "substitute_best_equal": { + "condition": "WHERE", + "resulting_condition": "1" + } + }, { "condition_on_constant_tables": "1" }, { "attaching_conditions_to_tables": { - "original_condition": "1", "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -2517,12 +2557,24 @@ explain select * from t1 left join t2 on t2.a=t1.a { { "best_join_order": ["t1", "t2"] }, + { + "substitute_best_equal": { + "condition": "WHERE", + "resulting_condition": "1" + } + }, + { + "substitute_best_equal": { + "condition": "ON expr", + "attached_to": "t2", + "resulting_condition": "t2.a = t1.a" + } + }, { "condition_on_constant_tables": "1" }, { "attaching_conditions_to_tables": { - "original_condition": "1", "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -2675,12 +2727,17 @@ explain select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and { "best_join_order": ["t3", "t2", "t1"] }, + { + "substitute_best_equal": { + "condition": "WHERE", + "resulting_condition": "1" + } + }, { "condition_on_constant_tables": "1" }, { "attaching_conditions_to_tables": { - "original_condition": "1", "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -2988,12 +3045,17 @@ explain extended select * from t1 where a in (select pk from t10) { { "best_join_order": ["t1", "<subquery2>"] }, + { + "substitute_best_equal": { + "condition": "WHERE", + "resulting_condition": "1" + } + }, { "condition_on_constant_tables": "1" }, { "attaching_conditions_to_tables": { - "original_condition": "1", "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -3349,9 +3411,14 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { { "best_join_order": ["t1"] }, + { + "substitute_best_equal": { + "condition": "WHERE", + "resulting_condition": "t1.pk = 2 and t1.a = 5 and t1.b = 1" + } + }, { "attaching_conditions_to_tables": { - "original_condition": "t1.pk = 2 and t1.a = 5 and t1.b = 1", "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -3475,7 +3542,6 @@ select f1(a) from t1 { }, { "attaching_conditions_to_tables": { - "original_condition": null, "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -3573,7 +3639,6 @@ select f2(a) from t1 { }, { "attaching_conditions_to_tables": { - "original_condition": null, "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -3611,7 +3676,7 @@ a 2 select length(trace) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; length(trace) -2183 +2141 set optimizer_trace_max_mem_size=100; select * from t1; a @@ -3625,7 +3690,7 @@ select * from t1 { "join_preparation": { "select_id": 1, "steps": [ - 2083 0 + 2041 0 set optimizer_trace_max_mem_size=0; select * from t1; a @@ -3633,7 +3698,7 @@ a 2 select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES -select * from t1 2183 0 +select * from t1 2141 0 drop table t1; set optimizer_trace='enabled=off'; set @@optimizer_trace_max_mem_size= @save_optimizer_trace_max_mem_size; @@ -4041,9 +4106,14 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { { "best_join_order": ["t0", "t1"] }, + { + "substitute_best_equal": { + "condition": "WHERE", + "resulting_condition": "t1.a = t0.a and t0.a < 3" + } + }, { "attaching_conditions_to_tables": { - "original_condition": "t1.a = t0.a and t0.a < 3", "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -4180,7 +4250,6 @@ explain select * from (select rand() from t1)q { }, { "attaching_conditions_to_tables": { - "original_condition": null, "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -4246,7 +4315,6 @@ explain select * from (select rand() from t1)q { }, { "attaching_conditions_to_tables": { - "original_condition": null, "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -4686,12 +4754,17 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ { "best_join_order": ["t1", "<subquery2>"] }, + { + "substitute_best_equal": { + "condition": "WHERE", + "resulting_condition": "1" + } + }, { "condition_on_constant_tables": "1" }, { "attaching_conditions_to_tables": { - "original_condition": "1", "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -6255,9 +6328,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "t_inner_3" ] }, + { + "substitute_best_equal": { + "condition": "WHERE", + "resulting_condition": "t_inner_1.a = t_outer_1.a and t_inner_3.a = t_outer_2.a" + } + }, { "attaching_conditions_to_tables": { - "original_condition": "t_inner_1.a = t_outer_1.a and t_inner_3.a = t_outer_2.a", "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -7332,12 +7410,17 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "<subquery3>" ] }, + { + "substitute_best_equal": { + "condition": "WHERE", + "resulting_condition": "1" + } + }, { "condition_on_constant_tables": "1" }, { "attaching_conditions_to_tables": { - "original_condition": "1", "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -8644,7 +8727,6 @@ select count(*) from seq_1_to_10000000 { }, { "attaching_conditions_to_tables": { - "original_condition": null, "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -8832,4 +8914,98 @@ S "cause": "no predicate for first keypart" } drop table t1,t2,t3; +# +# MDEV-23645: Optimizer trace: print conditions after substitute_for_best_equal_field +# +create table t1 (a int, b int, c int); +insert into t1 values (1,1,1),(2,2,2); +create table t2 as select * from t1; +insert into t2 select * from t2; +create table t3 as select * from t2; +insert into t3 select * from t3; +# Check how HAVING is printed +explain +select +a,b, count(*) +from t1 +where a=3 +group by b,b +having a+b < 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where; Using temporary; Using filesort +select +json_detailed(json_extract(trace, '$**.substitute_best_equal')) +from +information_schema.optimizer_trace; +json_detailed(json_extract(trace, '$**.substitute_best_equal')) +[ + + { + "condition": "WHERE", + "resulting_condition": "t1.a = 3" + }, + + { + "condition": "HAVING", + "resulting_condition": "t1.a + t1.b < 10" + } +] +# Check ON expression +explain +select +* +from t1 left join t2 on t2.a=t1.a and t2.a<3 +where +t1.b > 5555; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) +select +json_detailed(json_extract(trace, '$**.substitute_best_equal')) +from +information_schema.optimizer_trace; +json_detailed(json_extract(trace, '$**.substitute_best_equal')) +[ + + { + "condition": "WHERE", + "resulting_condition": "t1.b > 5555" + }, + + { + "condition": "ON expr", + "attached_to": "t2", + "resulting_condition": "t2.a = t1.a and t1.a < 3" + } +] +# Check nested ON expression +explain +select +* +from t1 left join (t2,t3) on t2.a=t1.a and t3.a=t2.a and t3.a + t2.a <1000 +where +t1.b > 5555; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (incremental, BNL join) +select +json_detailed(json_extract(trace, '$**.substitute_best_equal')) +from +information_schema.optimizer_trace; +json_detailed(json_extract(trace, '$**.substitute_best_equal')) +[ + + { + "condition": "WHERE", + "resulting_condition": "t1.b > 5555" + }, + + { + "condition": "ON expr", + "attached_to": "t2", + "resulting_condition": "t2.a = t1.a and t3.a = t1.a and t1.a + t1.a < 1000" + } +] +drop table t1,t2,t3; set optimizer_trace='enabled=off'; diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test index 83fd5ac0f40..c4166774ab1 100644 --- a/mysql-test/main/opt_trace.test +++ b/mysql-test/main/opt_trace.test @@ -727,4 +727,57 @@ select drop table t1,t2,t3; +--echo # +--echo # MDEV-23645: Optimizer trace: print conditions after substitute_for_best_equal_field +--echo # +create table t1 (a int, b int, c int); +insert into t1 values (1,1,1),(2,2,2); + +create table t2 as select * from t1; +insert into t2 select * from t2; + +create table t3 as select * from t2; +insert into t3 select * from t3; + +--echo # Check how HAVING is printed +explain +select + a,b, count(*) +from t1 +where a=3 +group by b,b +having a+b < 10; + +select + json_detailed(json_extract(trace, '$**.substitute_best_equal')) +from + information_schema.optimizer_trace; + +--echo # Check ON expression +explain +select + * +from t1 left join t2 on t2.a=t1.a and t2.a<3 +where + t1.b > 5555; + +select + json_detailed(json_extract(trace, '$**.substitute_best_equal')) +from + information_schema.optimizer_trace; + +--echo # Check nested ON expression +explain +select + * +from t1 left join (t2,t3) on t2.a=t1.a and t3.a=t2.a and t3.a + t2.a <1000 +where + t1.b > 5555; +select + json_detailed(json_extract(trace, '$**.substitute_best_equal')) +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 3b79a7a44e8..f1e13586eda 100644 --- a/mysql-test/main/opt_trace_index_merge.result +++ b/mysql-test/main/opt_trace_index_merge.result @@ -229,9 +229,14 @@ explain select * from t1 where a=1 or b=1 { { "best_join_order": ["t1"] }, + { + "substitute_best_equal": { + "condition": "WHERE", + "resulting_condition": "t1.a = 1 or t1.b = 1" + } + }, { "attaching_conditions_to_tables": { - "original_condition": "t1.a = 1 or t1.b = 1", "attached_conditions_computation": [], "attached_conditions_summary": [ { diff --git a/mysql-test/main/opt_trace_index_merge_innodb.result b/mysql-test/main/opt_trace_index_merge_innodb.result index 509569021a5..0ddaaeae89d 100644 --- a/mysql-test/main/opt_trace_index_merge_innodb.result +++ b/mysql-test/main/opt_trace_index_merge_innodb.result @@ -234,9 +234,14 @@ explain select * from t1 where pk1 != 0 and key1 = 1 { { "best_join_order": ["t1"] }, + { + "substitute_best_equal": { + "condition": "WHERE", + "resulting_condition": "t1.key1 = 1 and t1.pk1 <> 0" + } + }, { "attaching_conditions_to_tables": { - "original_condition": "t1.key1 = 1 and t1.pk1 <> 0", "attached_conditions_computation": [], "attached_conditions_summary": [ { diff --git a/mysql-test/main/opt_trace_security.result b/mysql-test/main/opt_trace_security.result index 2c09f6c7d5a..e1937e744a4 100644 --- a/mysql-test/main/opt_trace_security.result +++ b/mysql-test/main/opt_trace_security.result @@ -117,7 +117,6 @@ select * from db1.t1 { }, { "attaching_conditions_to_tables": { - "original_condition": null, "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -240,7 +239,6 @@ select * from db1.v1 { }, { "attaching_conditions_to_tables": { - "original_condition": null, "attached_conditions_computation": [], "attached_conditions_summary": [ { diff --git a/sql/opt_trace.cc b/sql/opt_trace.cc index ddec6d5ed2d..e1f402a4d7c 100644 --- a/sql/opt_trace.cc +++ b/sql/opt_trace.cc @@ -595,6 +595,18 @@ void Json_writer::add_table_name(const TABLE *table) } +void trace_condition(THD * thd, const char *name, const char *transform_type, + Item *item, const char *table_name) +{ + Json_writer_object trace_wrapper(thd); + Json_writer_object trace_cond(thd, transform_type); + trace_cond.add("condition", name); + if (table_name) + trace_cond.add("attached_to", table_name); + trace_cond.add("resulting_condition", item); +} + + void add_table_scan_values_to_trace(THD *thd, JOIN_TAB *tab) { DBUG_ASSERT(thd->trace_started()); diff --git a/sql/opt_trace.h b/sql/opt_trace.h index 550f18c0797..101fb5f707e 100644 --- a/sql/opt_trace.h +++ b/sql/opt_trace.h @@ -108,6 +108,10 @@ void print_final_join_order(JOIN *join); void print_best_access_for_table(THD *thd, POSITION *pos, enum join_type type); +void trace_condition(THD * thd, const char *name, const char *transform_type, + Item *item, const char *table_name= nullptr); + + /* Security related (need to add a proper comment here) */ diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 5447c398b66..05dc145f5cb 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -2408,6 +2408,10 @@ int JOIN::optimize_stage2() DBUG_RETURN(1); } conds->update_used_tables(); + + if (unlikely(thd->trace_started())) + trace_condition(thd, "WHERE", "substitute_best_equal", conds); + DBUG_EXECUTE("where", print_where(conds, "after substitute_best_equal", @@ -2424,7 +2428,12 @@ int JOIN::optimize_stage2() DBUG_RETURN(1); } if (having) + { having->update_used_tables(); + if (unlikely(thd->trace_started())) + trace_condition(thd, "HAVING", "substitute_best_equal", having); + } + DBUG_EXECUTE("having", print_where(having, "after substitute_best_equal", @@ -2451,6 +2460,11 @@ int JOIN::optimize_stage2() DBUG_RETURN(1); } (*tab->on_expr_ref)->update_used_tables(); + if (unlikely(thd->trace_started())) + { + trace_condition(thd, "ON expr", "substitute_best_equal", + (*tab->on_expr_ref), tab->table->alias.c_ptr()); + } } } @@ -11479,7 +11493,6 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) */ Json_writer_object trace_wrapper(thd); Json_writer_object trace_conditions(thd, "attaching_conditions_to_tables"); - trace_conditions.add("original_condition", cond); Json_writer_array trace_attached_comp(thd, "attached_conditions_computation"); uint i;

1 0

[Commits] 79822ff: MDEV-25128 Wrong result from join with materialized semi-join and
by IgorBabaev 19 Mar '21

19 Mar '21

revision-id: 79822ff6b24ce0f3e5a77819bb675370c7c48a65 (mariadb-10.3.26-111-g79822ff) parent(s): eb7c5530eccb7d6782077e5562f5a471d2ccbc01 author: Igor Babaev committer: Igor Babaev timestamp: 2021-03-18 20:43:11 -0700 message: MDEV-25128 Wrong result from join with materialized semi-join and splittable derived If one of joined tables of the processed query is a materialized derived table (or view or CTE) with GROUP BY clause then under some conditions it can be subject to split optimization. With this optimization new equalities are injected into the WHERE condition of the SELECT that specifies this derived table. The injected equalities are generated for all join orders with which the split optimization can employed. After the best join order has been chosen only certain of this equalities are really needed. The others can be safely removed. If it's not done and some of injected equalities involve expressions over semi-joins with look-up access then the query may return a wrong result set. This patch effectively removes equalities injected for split optimization that are needed only at the optimization stage and not needed for execution. Approved by dmitry.shulga(a)mariadb.com --- mysql-test/main/derived_cond_pushdown.result | 211 +++++++++++++++++++++++++++ mysql-test/main/derived_cond_pushdown.test | 37 +++++ sql/opt_split.cc | 55 ++++++- sql/sql_select.cc | 10 ++ 4 files changed, 307 insertions(+), 6 deletions(-) diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index 27ffffd..f3d63b5 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -17134,4 +17134,215 @@ a 3 DROP VIEW v1; DROP TABLE t1; +# +# MDEV-25128: Split optimization for join with materialized semi-join +# +create table t1 (id int, a int, index (a), index (id, a)) engine=myisam; +insert into t1 values +(17,1),(17,3010),(17,3013),(17,3053),(21,2446),(21,2467),(21,2); +create table t2 (a int) engine=myisam; +insert into t2 values (1),(2),(3); +create table t3 (id int) engine=myisam; +insert into t3 values (1),(2); +analyze table t1,t2,t3; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status OK +test.t3 analyze status OK +set optimizer_switch="split_materialized=off"; +select * from t1, (select a from t1 cp2 group by a) dt, t3 +where dt.a = t1.a and t1.a = t3.id and t1.a in (select a from t2); +id a a id +17 1 1 1 +21 2 2 2 +explain select * from t1, (select a from t1 cp2 group by a) dt, t3 +where dt.a = t1.a and t1.a = t3.id and t1.a in (select a from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY t1 ref a a 5 test.t3.id 1 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 +1 PRIMARY <derived2> ref key0 key0 5 test.t3.id 2 +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 +2 DERIVED cp2 index NULL a 5 NULL 7 Using index +explain format=json select * from t1, (select a from t1 cp2 group by a) dt, t3 +where dt.a = t1.a and t1.a = t3.id and t1.a in (select a from t2); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 2, + "filtered": 100, + "attached_condition": "t3.`id` is not null and t3.`id` is not null" + }, + "table": { + "table_name": "t1", + "access_type": "ref", + "possible_keys": ["a"], + "key": "a", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["test.t3.id"], + "rows": 1, + "filtered": 100 + }, + "table": { + "table_name": "<subquery3>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "4", + "used_key_parts": ["a"], + "ref": ["func"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 3, + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 3, + "filtered": 100 + } + } + } + }, + "table": { + "table_name": "<derived2>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["test.t3.id"], + "rows": 2, + "filtered": 100, + "materialized": { + "query_block": { + "select_id": 2, + "table": { + "table_name": "cp2", + "access_type": "index", + "key": "a", + "key_length": "5", + "used_key_parts": ["a"], + "rows": 7, + "filtered": 100, + "using_index": true + } + } + } + } + } +} +set optimizer_switch="split_materialized=default"; +select * from t1, (select a from t1 cp2 group by a) dt, t3 +where dt.a = t1.a and t1.a = t3.id and t1.a in (select a from t2); +id a a id +17 1 1 1 +21 2 2 2 +explain select * from t1, (select a from t1 cp2 group by a) dt, t3 +where dt.a = t1.a and t1.a = t3.id and t1.a in (select a from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY t1 ref a a 5 test.t3.id 1 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 +1 PRIMARY <derived2> ref key0 key0 5 test.t3.id 2 +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 +2 LATERAL DERIVED cp2 ref a a 5 test.t1.a 1 Using index +explain format=json select * from t1, (select a from t1 cp2 group by a) dt, t3 +where dt.a = t1.a and t1.a = t3.id and t1.a in (select a from t2); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 2, + "filtered": 100, + "attached_condition": "t3.`id` is not null and t3.`id` is not null" + }, + "table": { + "table_name": "t1", + "access_type": "ref", + "possible_keys": ["a"], + "key": "a", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["test.t3.id"], + "rows": 1, + "filtered": 100 + }, + "table": { + "table_name": "<subquery3>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "4", + "used_key_parts": ["a"], + "ref": ["func"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 3, + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 3, + "filtered": 100 + } + } + } + }, + "table": { + "table_name": "<derived2>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["test.t3.id"], + "rows": 2, + "filtered": 100, + "materialized": { + "lateral": 1, + "query_block": { + "select_id": 2, + "outer_ref_condition": "t1.a is not null", + "table": { + "table_name": "cp2", + "access_type": "ref", + "possible_keys": ["a"], + "key": "a", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["test.t1.a"], + "rows": 1, + "filtered": 100, + "using_index": true + } + } + } + } + } +} +prepare stmt from "select * from t1, (select a from t1 cp2 group by a) dt, t3 +where dt.a = t1.a and t1.a = t3.id and t1.a in (select a from t2)"; +execute stmt; +id a a id +17 1 1 1 +21 2 2 2 +execute stmt; +id a a id +17 1 1 1 +21 2 2 2 +deallocate prepare stmt; +drop table t1,t2,t3; # End of 10.3 tests diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test index 12d34a8..5936447 100644 --- a/mysql-test/main/derived_cond_pushdown.test +++ b/mysql-test/main/derived_cond_pushdown.test @@ -3479,4 +3479,41 @@ SELECT * from v1 WHERE a=3; DROP VIEW v1; DROP TABLE t1; +--echo # +--echo # MDEV-25128: Split optimization for join with materialized semi-join +--echo # + +create table t1 (id int, a int, index (a), index (id, a)) engine=myisam; +insert into t1 values +(17,1),(17,3010),(17,3013),(17,3053),(21,2446),(21,2467),(21,2); + +create table t2 (a int) engine=myisam; +insert into t2 values (1),(2),(3); + +create table t3 (id int) engine=myisam; +insert into t3 values (1),(2); + +analyze table t1,t2,t3; + +let $q= +select * from t1, (select a from t1 cp2 group by a) dt, t3 + where dt.a = t1.a and t1.a = t3.id and t1.a in (select a from t2); + +set optimizer_switch="split_materialized=off"; +eval $q; +eval explain $q; +eval explain format=json $q; + +set optimizer_switch="split_materialized=default"; +eval $q; +eval explain $q; +eval explain format=json $q; + +eval prepare stmt from "$q"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +drop table t1,t2,t3; + --echo # End of 10.3 tests diff --git a/sql/opt_split.cc b/sql/opt_split.cc index fd7836f..ce643a3 100644 --- a/sql/opt_split.cc +++ b/sql/opt_split.cc @@ -236,6 +236,8 @@ class SplM_opt_info : public Sql_alloc SplM_field_info *spl_fields; /* The number of elements in the above list */ uint spl_field_cnt; + /* The list of equalities injected into WHERE for split optimization */ + List<Item> inj_cond_list; /* Contains the structures to generate all KEYUSEs for pushable equalities */ List<KEY_FIELD> added_key_fields; /* The cache of evaluated execution plans for 'join' with pushed equalities */ @@ -1047,22 +1049,22 @@ SplM_plan_info * JOIN_TAB::choose_best_splitting(double record_count, bool JOIN::inject_best_splitting_cond(table_map remaining_tables) { Item *inj_cond= 0; - List<Item> inj_cond_list; + List<Item> *inj_cond_list= &spl_opt_info->inj_cond_list; List_iterator<KEY_FIELD> li(spl_opt_info->added_key_fields); KEY_FIELD *added_key_field; while ((added_key_field= li++)) { if (remaining_tables & added_key_field->val->used_tables()) continue; - if (inj_cond_list.push_back(added_key_field->cond, thd->mem_root)) + if (inj_cond_list->push_back(added_key_field->cond, thd->mem_root)) return true; } - DBUG_ASSERT(inj_cond_list.elements); - switch (inj_cond_list.elements) { + DBUG_ASSERT(inj_cond_list->elements); + switch (inj_cond_list->elements) { case 1: - inj_cond= inj_cond_list.head(); break; + inj_cond= inj_cond_list->head(); break; default: - inj_cond= new (thd->mem_root) Item_cond_and(thd, inj_cond_list); + inj_cond= new (thd->mem_root) Item_cond_and(thd, *inj_cond_list); if (!inj_cond) return true; } @@ -1082,6 +1084,47 @@ bool JOIN::inject_best_splitting_cond(table_map remaining_tables) /** @brief + Test if equality is injected for split optimization and is removable + + @param + eq_item equality to to test if it is to be removed + retain_ref_cond true <=> eq_item is not to be removed if it's used for ref + + @retval + true eq_item to be removed from conditions pushed to tables + false otherwise +*/ + +bool test_if_eq_injected_for_split_is_removable(Item_func_eq *eq_item, + bool retain_ref_cond) +{ + Item *left_item= eq_item->arguments()[0]->real_item(); + if (left_item->type() != Item::FIELD_ITEM) + return false; + Field *field= ((Item_field *) left_item)->field; + if (!field->table->reginfo.join_tab) + return false; + JOIN *join= field->table->reginfo.join_tab->join; + if (!join->spl_opt_info) + return false; + List_iterator_fast<Item> li(join->spl_opt_info->inj_cond_list); + Item *item; + while ((item= li++)) + { + if (item == eq_item) + { + if (!retain_ref_cond) + return true; + Item *right_item= eq_item->arguments()[1]->real_item(); + return !test_if_ref(0, (Item_field *) left_item, right_item); + } + } + return false; +} + + +/** + @brief Fix the splitting chosen for a splittable table in the final query plan @param diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 5f8bd24..5255929 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -292,6 +292,9 @@ void set_postjoin_aggr_write_func(JOIN_TAB *tab); static Item **get_sargable_cond(JOIN *join, TABLE *table); +bool test_if_eq_injected_for_split_is_removable(Item_func_eq *eq_item, + bool retain_ref_cond); + #ifndef DBUG_OFF /* @@ -6086,6 +6089,7 @@ add_key_part(DYNAMIC_ARRAY *keyuse_array, KEY_FIELD *key_field) { Field *field=key_field->field; TABLE *form= field->table; + if (key_field->eq_func && !(key_field->optimize & KEY_OPTIMIZE_EXISTS)) { @@ -21787,6 +21791,12 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond, cond->marker=3; // Checked when read return (COND*) 0; } + if (test_if_eq_injected_for_split_is_removable((Item_func_eq *) cond, + retain_ref_cond)) + { + cond->marker=3; + return (COND*) 0; + } } cond->marker=2; cond->set_join_tab_idx(join_tab_idx_arg);

1 0

[Commits] 77d8afd: MDEV-25002 ON expressions cannot contain outer references
by IgorBabaev 18 Mar '21

18 Mar '21

revision-id: 77d8afd5c39ee779cb083d07e9b70d330067dc88 (mariadb-10.4.11-576-g77d8afd) parent(s): 126725421e56293d7c8b816e066271606b59dcd5 author: Igor Babaev committer: Igor Babaev timestamp: 2021-03-18 15:14:56 -0700 message: MDEV-25002 ON expressions cannot contain outer references A bogus error message was issued for any outer references occurred in ON expressions used in subqueries. This prevented execution of queries containing subqueries as soon as they used outer references in their ON clauses. This happened because the Name_resolution_context structure created for any ON expression erroneously had the field outer_context set to NULL. The fields select_lex of this structure was not set correctly either. The idea of the fix was taken from mysql code of the function push_new_name_resolution_context(). Approved by dmitry.shulga(a)mariadb.com --- mysql-test/main/subselect.result | 38 ++++++++++++++++++++++++ mysql-test/main/subselect.test | 35 ++++++++++++++++++++++ mysql-test/main/subselect_no_exists_to_in.result | 38 ++++++++++++++++++++++++ mysql-test/main/subselect_no_mat.result | 38 ++++++++++++++++++++++++ mysql-test/main/subselect_no_opts.result | 38 ++++++++++++++++++++++++ mysql-test/main/subselect_no_scache.result | 38 ++++++++++++++++++++++++ mysql-test/main/subselect_no_semijoin.result | 38 ++++++++++++++++++++++++ sql/sql_parse.cc | 8 ++++- 8 files changed, 270 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/subselect.result b/mysql-test/main/subselect.result index 349e7dc..15ebaa3 100644 --- a/mysql-test/main/subselect.result +++ b/mysql-test/main/subselect.result @@ -7344,6 +7344,44 @@ WHERE (t2.i, t2.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo pk i c pk i c 1 10 foo 1 10 foo DROP TABLE t; +# +# MDEV-25002: Outer reference in ON clause of subselect +# +create table t1 ( +pk int primary key, +a int +) engine=myisam; +insert into t1 values (1,1), (2,2); +create table t2 ( +pk int primary key, +b int +) engine=myisam; +insert into t2 values (1,1), (2,3); +create table t3 (a int); +insert into t3 values (1),(2); +select a, +(select count(*) from t1, t2 +where t2.pk=t3.a and t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a, +(select count(*) from t1 join t2 on t2.pk=t3.a +where t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a from t3 +where a in (select t2.b from t1,t2 where t2.pk=t3.a and t1.pk=1); +a +1 +select a from t3 +where a in (select t2.b from t1 join t2 on t2.pk=t3.a where t1.pk=1); +a +1 +drop table t1,t2,t3; # End of 10.2 tests # # Start of 10.4 tests diff --git a/mysql-test/main/subselect.test b/mysql-test/main/subselect.test index be17254..d87aba5 100644 --- a/mysql-test/main/subselect.test +++ b/mysql-test/main/subselect.test @@ -6171,6 +6171,41 @@ SELECT * FROM t t1 RIGHT JOIN t t2 ON (t2.pk = t1.pk) DROP TABLE t; +--echo # +--echo # MDEV-25002: Outer reference in ON clause of subselect +--echo # + +create table t1 ( + pk int primary key, + a int +) engine=myisam; +insert into t1 values (1,1), (2,2); + +create table t2 ( + pk int primary key, + b int +) engine=myisam; +insert into t2 values (1,1), (2,3); + +create table t3 (a int); +insert into t3 values (1),(2); + +select a, + (select count(*) from t1, t2 + where t2.pk=t3.a and t1.pk=1) as sq +from t3; +select a, + (select count(*) from t1 join t2 on t2.pk=t3.a + where t1.pk=1) as sq +from t3; + +select a from t3 + where a in (select t2.b from t1,t2 where t2.pk=t3.a and t1.pk=1); +select a from t3 + where a in (select t2.b from t1 join t2 on t2.pk=t3.a where t1.pk=1); + +drop table t1,t2,t3; + --echo # End of 10.2 tests diff --git a/mysql-test/main/subselect_no_exists_to_in.result b/mysql-test/main/subselect_no_exists_to_in.result index 84c415d..b1432cf 100644 --- a/mysql-test/main/subselect_no_exists_to_in.result +++ b/mysql-test/main/subselect_no_exists_to_in.result @@ -7344,6 +7344,44 @@ WHERE (t2.i, t2.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo pk i c pk i c 1 10 foo 1 10 foo DROP TABLE t; +# +# MDEV-25002: Outer reference in ON clause of subselect +# +create table t1 ( +pk int primary key, +a int +) engine=myisam; +insert into t1 values (1,1), (2,2); +create table t2 ( +pk int primary key, +b int +) engine=myisam; +insert into t2 values (1,1), (2,3); +create table t3 (a int); +insert into t3 values (1),(2); +select a, +(select count(*) from t1, t2 +where t2.pk=t3.a and t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a, +(select count(*) from t1 join t2 on t2.pk=t3.a +where t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a from t3 +where a in (select t2.b from t1,t2 where t2.pk=t3.a and t1.pk=1); +a +1 +select a from t3 +where a in (select t2.b from t1 join t2 on t2.pk=t3.a where t1.pk=1); +a +1 +drop table t1,t2,t3; # End of 10.2 tests # # Start of 10.4 tests diff --git a/mysql-test/main/subselect_no_mat.result b/mysql-test/main/subselect_no_mat.result index 93035e2..da60f98 100644 --- a/mysql-test/main/subselect_no_mat.result +++ b/mysql-test/main/subselect_no_mat.result @@ -7337,6 +7337,44 @@ WHERE (t2.i, t2.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo pk i c pk i c 1 10 foo 1 10 foo DROP TABLE t; +# +# MDEV-25002: Outer reference in ON clause of subselect +# +create table t1 ( +pk int primary key, +a int +) engine=myisam; +insert into t1 values (1,1), (2,2); +create table t2 ( +pk int primary key, +b int +) engine=myisam; +insert into t2 values (1,1), (2,3); +create table t3 (a int); +insert into t3 values (1),(2); +select a, +(select count(*) from t1, t2 +where t2.pk=t3.a and t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a, +(select count(*) from t1 join t2 on t2.pk=t3.a +where t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a from t3 +where a in (select t2.b from t1,t2 where t2.pk=t3.a and t1.pk=1); +a +1 +select a from t3 +where a in (select t2.b from t1 join t2 on t2.pk=t3.a where t1.pk=1); +a +1 +drop table t1,t2,t3; # End of 10.2 tests # # Start of 10.4 tests diff --git a/mysql-test/main/subselect_no_opts.result b/mysql-test/main/subselect_no_opts.result index 09f664d..fb99e23 100644 --- a/mysql-test/main/subselect_no_opts.result +++ b/mysql-test/main/subselect_no_opts.result @@ -7335,6 +7335,44 @@ WHERE (t2.i, t2.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo pk i c pk i c 1 10 foo 1 10 foo DROP TABLE t; +# +# MDEV-25002: Outer reference in ON clause of subselect +# +create table t1 ( +pk int primary key, +a int +) engine=myisam; +insert into t1 values (1,1), (2,2); +create table t2 ( +pk int primary key, +b int +) engine=myisam; +insert into t2 values (1,1), (2,3); +create table t3 (a int); +insert into t3 values (1),(2); +select a, +(select count(*) from t1, t2 +where t2.pk=t3.a and t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a, +(select count(*) from t1 join t2 on t2.pk=t3.a +where t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a from t3 +where a in (select t2.b from t1,t2 where t2.pk=t3.a and t1.pk=1); +a +1 +select a from t3 +where a in (select t2.b from t1 join t2 on t2.pk=t3.a where t1.pk=1); +a +1 +drop table t1,t2,t3; # End of 10.2 tests # # Start of 10.4 tests diff --git a/mysql-test/main/subselect_no_scache.result b/mysql-test/main/subselect_no_scache.result index 765bb15..4b91000 100644 --- a/mysql-test/main/subselect_no_scache.result +++ b/mysql-test/main/subselect_no_scache.result @@ -7350,6 +7350,44 @@ WHERE (t2.i, t2.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo pk i c pk i c 1 10 foo 1 10 foo DROP TABLE t; +# +# MDEV-25002: Outer reference in ON clause of subselect +# +create table t1 ( +pk int primary key, +a int +) engine=myisam; +insert into t1 values (1,1), (2,2); +create table t2 ( +pk int primary key, +b int +) engine=myisam; +insert into t2 values (1,1), (2,3); +create table t3 (a int); +insert into t3 values (1),(2); +select a, +(select count(*) from t1, t2 +where t2.pk=t3.a and t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a, +(select count(*) from t1 join t2 on t2.pk=t3.a +where t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a from t3 +where a in (select t2.b from t1,t2 where t2.pk=t3.a and t1.pk=1); +a +1 +select a from t3 +where a in (select t2.b from t1 join t2 on t2.pk=t3.a where t1.pk=1); +a +1 +drop table t1,t2,t3; # End of 10.2 tests # # Start of 10.4 tests diff --git a/mysql-test/main/subselect_no_semijoin.result b/mysql-test/main/subselect_no_semijoin.result index 97d2f3b..f223071 100644 --- a/mysql-test/main/subselect_no_semijoin.result +++ b/mysql-test/main/subselect_no_semijoin.result @@ -7335,6 +7335,44 @@ WHERE (t2.i, t2.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo pk i c pk i c 1 10 foo 1 10 foo DROP TABLE t; +# +# MDEV-25002: Outer reference in ON clause of subselect +# +create table t1 ( +pk int primary key, +a int +) engine=myisam; +insert into t1 values (1,1), (2,2); +create table t2 ( +pk int primary key, +b int +) engine=myisam; +insert into t2 values (1,1), (2,3); +create table t3 (a int); +insert into t3 values (1),(2); +select a, +(select count(*) from t1, t2 +where t2.pk=t3.a and t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a, +(select count(*) from t1 join t2 on t2.pk=t3.a +where t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a from t3 +where a in (select t2.b from t1,t2 where t2.pk=t3.a and t1.pk=1); +a +1 +select a from t3 +where a in (select t2.b from t1 join t2 on t2.pk=t3.a where t1.pk=1); +a +1 +drop table t1,t2,t3; # End of 10.2 tests # # Start of 10.4 tests diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index dad7b80..2321402 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -8987,7 +8987,13 @@ push_new_name_resolution_context(THD *thd, left_op->first_leaf_for_name_resolution(); on_context->last_name_resolution_table= right_op->last_leaf_for_name_resolution(); - return thd->lex->push_context(on_context); + LEX *lex= thd->lex; + on_context->select_lex = lex->current_select; + st_select_lex *curr_select= lex->pop_select(); + st_select_lex *outer_sel= lex->select_stack_head(); + lex->push_select(curr_select); + on_context->outer_context = outer_sel ? &outer_sel->context : 0; + return lex->push_context(on_context); }

1 0

[Commits] e7670b3: MDEV-25002 ON expressions cannot contain outer references
by IgorBabaev 18 Mar '21

18 Mar '21

revision-id: e7670b37126cb54541f7f9a44d4188a30e571536 (mariadb-10.4.11-576-ge7670b3) parent(s): 126725421e56293d7c8b816e066271606b59dcd5 author: Igor Babaev committer: Igor Babaev timestamp: 2021-03-18 14:59:17 -0700 message: MDEV-25002 ON expressions cannot contain outer references A bogus error message was issued for any outer references occurred in ON expressions used in subqueries. This prevented execution of queries containing subqueries as soon as they used outer references in their ON clauses. This happened because the Name_resolution_context structure created for any ON expression erroneously had the field outer_context set to NULL. The fields select_lex of this structure was not set correctly either. The idea of the fix was taken from mysql code of the function push_new_name_resolution_context(). Approved by dmitry.shulga(a)mariadb.com --- mysql-test/main/subselect.result | 38 ++++++++++++++++++++++++ mysql-test/main/subselect.test | 35 ++++++++++++++++++++++ mysql-test/main/subselect_no_exists_to_in.result | 38 ++++++++++++++++++++++++ mysql-test/main/subselect_no_mat.result | 38 ++++++++++++++++++++++++ mysql-test/main/subselect_no_opts.result | 38 ++++++++++++++++++++++++ mysql-test/main/subselect_no_scache.result | 38 ++++++++++++++++++++++++ mysql-test/main/subselect_no_semijoin.result | 38 ++++++++++++++++++++++++ sql/sql_parse.cc | 5 ++++ 8 files changed, 268 insertions(+) diff --git a/mysql-test/main/subselect.result b/mysql-test/main/subselect.result index 349e7dc..15ebaa3 100644 --- a/mysql-test/main/subselect.result +++ b/mysql-test/main/subselect.result @@ -7344,6 +7344,44 @@ WHERE (t2.i, t2.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo pk i c pk i c 1 10 foo 1 10 foo DROP TABLE t; +# +# MDEV-25002: Outer reference in ON clause of subselect +# +create table t1 ( +pk int primary key, +a int +) engine=myisam; +insert into t1 values (1,1), (2,2); +create table t2 ( +pk int primary key, +b int +) engine=myisam; +insert into t2 values (1,1), (2,3); +create table t3 (a int); +insert into t3 values (1),(2); +select a, +(select count(*) from t1, t2 +where t2.pk=t3.a and t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a, +(select count(*) from t1 join t2 on t2.pk=t3.a +where t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a from t3 +where a in (select t2.b from t1,t2 where t2.pk=t3.a and t1.pk=1); +a +1 +select a from t3 +where a in (select t2.b from t1 join t2 on t2.pk=t3.a where t1.pk=1); +a +1 +drop table t1,t2,t3; # End of 10.2 tests # # Start of 10.4 tests diff --git a/mysql-test/main/subselect.test b/mysql-test/main/subselect.test index be17254..d87aba5 100644 --- a/mysql-test/main/subselect.test +++ b/mysql-test/main/subselect.test @@ -6171,6 +6171,41 @@ SELECT * FROM t t1 RIGHT JOIN t t2 ON (t2.pk = t1.pk) DROP TABLE t; +--echo # +--echo # MDEV-25002: Outer reference in ON clause of subselect +--echo # + +create table t1 ( + pk int primary key, + a int +) engine=myisam; +insert into t1 values (1,1), (2,2); + +create table t2 ( + pk int primary key, + b int +) engine=myisam; +insert into t2 values (1,1), (2,3); + +create table t3 (a int); +insert into t3 values (1),(2); + +select a, + (select count(*) from t1, t2 + where t2.pk=t3.a and t1.pk=1) as sq +from t3; +select a, + (select count(*) from t1 join t2 on t2.pk=t3.a + where t1.pk=1) as sq +from t3; + +select a from t3 + where a in (select t2.b from t1,t2 where t2.pk=t3.a and t1.pk=1); +select a from t3 + where a in (select t2.b from t1 join t2 on t2.pk=t3.a where t1.pk=1); + +drop table t1,t2,t3; + --echo # End of 10.2 tests diff --git a/mysql-test/main/subselect_no_exists_to_in.result b/mysql-test/main/subselect_no_exists_to_in.result index 84c415d..b1432cf 100644 --- a/mysql-test/main/subselect_no_exists_to_in.result +++ b/mysql-test/main/subselect_no_exists_to_in.result @@ -7344,6 +7344,44 @@ WHERE (t2.i, t2.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo pk i c pk i c 1 10 foo 1 10 foo DROP TABLE t; +# +# MDEV-25002: Outer reference in ON clause of subselect +# +create table t1 ( +pk int primary key, +a int +) engine=myisam; +insert into t1 values (1,1), (2,2); +create table t2 ( +pk int primary key, +b int +) engine=myisam; +insert into t2 values (1,1), (2,3); +create table t3 (a int); +insert into t3 values (1),(2); +select a, +(select count(*) from t1, t2 +where t2.pk=t3.a and t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a, +(select count(*) from t1 join t2 on t2.pk=t3.a +where t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a from t3 +where a in (select t2.b from t1,t2 where t2.pk=t3.a and t1.pk=1); +a +1 +select a from t3 +where a in (select t2.b from t1 join t2 on t2.pk=t3.a where t1.pk=1); +a +1 +drop table t1,t2,t3; # End of 10.2 tests # # Start of 10.4 tests diff --git a/mysql-test/main/subselect_no_mat.result b/mysql-test/main/subselect_no_mat.result index 93035e2..da60f98 100644 --- a/mysql-test/main/subselect_no_mat.result +++ b/mysql-test/main/subselect_no_mat.result @@ -7337,6 +7337,44 @@ WHERE (t2.i, t2.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo pk i c pk i c 1 10 foo 1 10 foo DROP TABLE t; +# +# MDEV-25002: Outer reference in ON clause of subselect +# +create table t1 ( +pk int primary key, +a int +) engine=myisam; +insert into t1 values (1,1), (2,2); +create table t2 ( +pk int primary key, +b int +) engine=myisam; +insert into t2 values (1,1), (2,3); +create table t3 (a int); +insert into t3 values (1),(2); +select a, +(select count(*) from t1, t2 +where t2.pk=t3.a and t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a, +(select count(*) from t1 join t2 on t2.pk=t3.a +where t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a from t3 +where a in (select t2.b from t1,t2 where t2.pk=t3.a and t1.pk=1); +a +1 +select a from t3 +where a in (select t2.b from t1 join t2 on t2.pk=t3.a where t1.pk=1); +a +1 +drop table t1,t2,t3; # End of 10.2 tests # # Start of 10.4 tests diff --git a/mysql-test/main/subselect_no_opts.result b/mysql-test/main/subselect_no_opts.result index 09f664d..fb99e23 100644 --- a/mysql-test/main/subselect_no_opts.result +++ b/mysql-test/main/subselect_no_opts.result @@ -7335,6 +7335,44 @@ WHERE (t2.i, t2.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo pk i c pk i c 1 10 foo 1 10 foo DROP TABLE t; +# +# MDEV-25002: Outer reference in ON clause of subselect +# +create table t1 ( +pk int primary key, +a int +) engine=myisam; +insert into t1 values (1,1), (2,2); +create table t2 ( +pk int primary key, +b int +) engine=myisam; +insert into t2 values (1,1), (2,3); +create table t3 (a int); +insert into t3 values (1),(2); +select a, +(select count(*) from t1, t2 +where t2.pk=t3.a and t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a, +(select count(*) from t1 join t2 on t2.pk=t3.a +where t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a from t3 +where a in (select t2.b from t1,t2 where t2.pk=t3.a and t1.pk=1); +a +1 +select a from t3 +where a in (select t2.b from t1 join t2 on t2.pk=t3.a where t1.pk=1); +a +1 +drop table t1,t2,t3; # End of 10.2 tests # # Start of 10.4 tests diff --git a/mysql-test/main/subselect_no_scache.result b/mysql-test/main/subselect_no_scache.result index 765bb15..4b91000 100644 --- a/mysql-test/main/subselect_no_scache.result +++ b/mysql-test/main/subselect_no_scache.result @@ -7350,6 +7350,44 @@ WHERE (t2.i, t2.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo pk i c pk i c 1 10 foo 1 10 foo DROP TABLE t; +# +# MDEV-25002: Outer reference in ON clause of subselect +# +create table t1 ( +pk int primary key, +a int +) engine=myisam; +insert into t1 values (1,1), (2,2); +create table t2 ( +pk int primary key, +b int +) engine=myisam; +insert into t2 values (1,1), (2,3); +create table t3 (a int); +insert into t3 values (1),(2); +select a, +(select count(*) from t1, t2 +where t2.pk=t3.a and t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a, +(select count(*) from t1 join t2 on t2.pk=t3.a +where t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a from t3 +where a in (select t2.b from t1,t2 where t2.pk=t3.a and t1.pk=1); +a +1 +select a from t3 +where a in (select t2.b from t1 join t2 on t2.pk=t3.a where t1.pk=1); +a +1 +drop table t1,t2,t3; # End of 10.2 tests # # Start of 10.4 tests diff --git a/mysql-test/main/subselect_no_semijoin.result b/mysql-test/main/subselect_no_semijoin.result index 97d2f3b..f223071 100644 --- a/mysql-test/main/subselect_no_semijoin.result +++ b/mysql-test/main/subselect_no_semijoin.result @@ -7335,6 +7335,44 @@ WHERE (t2.i, t2.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo pk i c pk i c 1 10 foo 1 10 foo DROP TABLE t; +# +# MDEV-25002: Outer reference in ON clause of subselect +# +create table t1 ( +pk int primary key, +a int +) engine=myisam; +insert into t1 values (1,1), (2,2); +create table t2 ( +pk int primary key, +b int +) engine=myisam; +insert into t2 values (1,1), (2,3); +create table t3 (a int); +insert into t3 values (1),(2); +select a, +(select count(*) from t1, t2 +where t2.pk=t3.a and t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a, +(select count(*) from t1 join t2 on t2.pk=t3.a +where t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a from t3 +where a in (select t2.b from t1,t2 where t2.pk=t3.a and t1.pk=1); +a +1 +select a from t3 +where a in (select t2.b from t1 join t2 on t2.pk=t3.a where t1.pk=1); +a +1 +drop table t1,t2,t3; # End of 10.2 tests # # Start of 10.4 tests diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index dad7b80..3c52dd2 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -8987,6 +8987,11 @@ push_new_name_resolution_context(THD *thd, left_op->first_leaf_for_name_resolution(); on_context->last_name_resolution_table= right_op->last_leaf_for_name_resolution(); + on_context->select_lex = thd->lex->current_select; + st_select_lex *top_select= thd->lex->pop_select(); + st_select_lex *outer_sel= thd->lex->select_stack_head(); + thd->lex->push_select(top_select); + on_context->outer_context = outer_sel ? &outer_sel->context : 0; return thd->lex->push_context(on_context); }

1 0

[Commits] 2b3fd5dff04: MDEV-23677: Optimizer trace: remove "no predicate for first keypart" (not)
by psergey 18 Mar '21

18 Mar '21

revision-id: 2b3fd5dff04c699a4743867f73a1bacc8549abe1 (mariadb-10.5.2-487-g2b3fd5dff04) parent(s): 4903031baa196dfc9a75638d141b515883cd254c author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-03-18 21:04:33 +0300 message: MDEV-23677: Optimizer trace: remove "no predicate for first keypart" (not) Don't remove (reasons given in Jira), instead add test coverage. Improve other printout in best_access_path. --- mysql-test/main/opt_trace.result | 38 ++++++++++++++++++++++++++++++++++++++ mysql-test/main/opt_trace.test | 30 ++++++++++++++++++++++++++++++ sql/sql_select.cc | 12 +++++------- 3 files changed, 73 insertions(+), 7 deletions(-) diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index 8f912e0500d..36bbea8ae78 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -4010,6 +4010,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { { "access_type": "ref", "index": "a", + "rec_per_key_stats_missing": true, "used_range_estimates": false, "cause": "not better than ref estimates", "rows": 2, @@ -8793,5 +8794,42 @@ json_detailed(json_extract(trace, '$**.in_to_subquery_conversion')) ] set in_predicate_conversion_threshold=@tmp; drop table t0; +# # End of 10.5 tests +# +# +# MDEV-23677: Optimizer trace ... (test coverage) +# +create table t1(a int, b int, c int, primary key (a,b,c)); +insert into t1 values (0,0,0),(1,1,1),(2,2,2),(3,3,3),(4,4,4); +create table t2 (a int, b int); +insert into t2 values (1,1),(2,2); +create table t3 (a int, b int, c int); +insert into t3 values (0,0,0),(1,1,1),(2,2,2),(3,3,3),(4,4,4); +explain select * from t2,t1,t3 where t2.b= t1.b and t1.a=t3.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE t3 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t1 ref PRIMARY PRIMARY 8 test.t3.a,test.t2.b 1 Using index +set @trace=(select trace from information_schema.optimizer_trace); +set @path= (select json_search(@trace, 'one', 'no predicate for first keypart')); +set @sub_path= substr(@path, 2, locate('.best_access_path', @path)-2); +select @sub_path; +@sub_path +$.steps[1].join_optimization.steps[4].considered_execution_plans[0].rest_of_plan[0] +select +json_detailed(json_extract( +@trace, +concat(@sub_path,'.best_access_path.considered_access_paths[0]') +)) as S; +S +{ + "access_type": "ref", + "index": "PRIMARY", + "rows": 1.79769e308, + "cost": 1.79769e308, + "chosen": false, + "cause": "no predicate for first keypart" +} +drop table t1,t2,t3; set optimizer_trace='enabled=off'; diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test index ecb6658e338..83fd5ac0f40 100644 --- a/mysql-test/main/opt_trace.test +++ b/mysql-test/main/opt_trace.test @@ -696,5 +696,35 @@ from information_schema.optimizer_trace; set in_predicate_conversion_threshold=@tmp; drop table t0; +--echo # --echo # End of 10.5 tests +--echo # + +--echo # +--echo # MDEV-23677: Optimizer trace ... (test coverage) +--echo # +create table t1(a int, b int, c int, primary key (a,b,c)); +insert into t1 values (0,0,0),(1,1,1),(2,2,2),(3,3,3),(4,4,4); + +create table t2 (a int, b int); +insert into t2 values (1,1),(2,2); + +create table t3 (a int, b int, c int); +insert into t3 values (0,0,0),(1,1,1),(2,2,2),(3,3,3),(4,4,4); + +explain select * from t2,t1,t3 where t2.b= t1.b and t1.a=t3.a; + +set @trace=(select trace from information_schema.optimizer_trace); +set @path= (select json_search(@trace, 'one', 'no predicate for first keypart')); +set @sub_path= substr(@path, 2, locate('.best_access_path', @path)-2); + +select @sub_path; +select + json_detailed(json_extract( + @trace, + concat(@sub_path,'.best_access_path.considered_access_paths[0]') + )) as S; + +drop table t1,t2,t3; + set optimizer_trace='enabled=off'; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 28b4a5f9b36..5447c398b66 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -7637,6 +7637,7 @@ best_access_path(JOIN *join, { if (!(records= keyinfo->actual_rec_per_key(key_parts-1))) { /* Prefer longer keys */ + trace_access_idx.add("rec_per_key_stats_missing", true); records= ((double) s->records / (double) rec * (1.0 + @@ -7663,7 +7664,7 @@ best_access_path(JOIN *join, records > (double) table->opt_range[key].rows) { records= (double) table->opt_range[key].rows; - trace_access_idx.add("used_range_estimates", true); + trace_access_idx.add("used_range_estimates", "clipped down"); } else { @@ -7780,19 +7781,15 @@ best_access_path(JOIN *join, if (!found_ref && // (1) records < rows) // (3) { - trace_access_idx.add("used_range_estimates", true); + trace_access_idx.add("used_range_estimates", "clipped up"); records= rows; } } - else /* (table->quick_key_parts[key] < max_key_part) */ - { - trace_access_idx.add("chosen", true); - cause= "range uses less keyparts"; - } } } else { + trace_access_idx.add("rec_per_key_stats_missing", true); /* Assume that the first key part matches 1% of the file and that the whole key matches 10 (duplicates) or 1 @@ -7856,6 +7853,7 @@ best_access_path(JOIN *join, const_part)) && records > (double) table->opt_range[key].rows) { + trace_access_idx.add("used_range_estimates", true); records= (double) table->opt_range[key].rows; } }

1 0

[Commits] 0038c24f2d3: MDEV-16146: MariaDB slave stops with following errors.
by sujatha 18 Mar '21

18 Mar '21

revision-id: 0038c24f2d388b9b0f3bff9219a53c8900153d02 (mariadb-10.5.4-539-g0038c24f2d3) parent(s): 190a8312f598fc4892331225104297f6288f23ac author: Sujatha committer: Sujatha timestamp: 2021-03-18 18:41:30 +0530 message: MDEV-16146: MariaDB slave stops with following errors. Problem: ======== 180511 11:07:58 [ERROR] Slave I/O: Unexpected master's heartbeat data: heartbeat is not compatible with local info;the event's data: log_file_name mysql-bin.000009 log_pos 1054262041, Error_code: 1623 Analysis: ========= In replication setup when master server doesn't have any events to send to slave server it sends an 'Heartbeat_log_event'. This event carries the current binary log filename and offset details. The offset values is stored within 4 bytes of event header. When the size of binary log is higher than UINT32_MAX the log_pos values will not fit in 4 bytes memory. It overflows and hence slave stops with an error. Fix: === Since we cannot extend the fixed header of Log_event class an additional header named 'extra_header' is introduced. 'extra_header' contains HB_FLAGS - 2 bytes HB_LONG_LOG_POS_OFFSET - 8 bytes This 'extra_header' is added only in a case where log_pos > UINT32_MAX and 'HB_LONG_LOG_POS_OFFSET_F' will be enabled. On slave while reading the 'Heartbeat_log_event' if 'HB_LONG_LOG_POS_OFFSET_F' is found to be set then value of 'log_pos' is extracted from 'extra_header'. --- .../suite/rpl/r/rpl_incompatible_heartbeat.result | 20 +++++++++ .../suite/rpl/t/rpl_incompatible_heartbeat.test | 47 ++++++++++++++++++++++ sql/log_event.h | 19 ++++++++- sql/log_event_server.cc | 8 +++- sql/slave.cc | 14 +++++++ sql/sql_repl.cc | 26 ++++++++++-- 6 files changed, 128 insertions(+), 6 deletions(-) diff --git a/mysql-test/suite/rpl/r/rpl_incompatible_heartbeat.result b/mysql-test/suite/rpl/r/rpl_incompatible_heartbeat.result new file mode 100644 index 00000000000..fe2b0436f2d --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_incompatible_heartbeat.result @@ -0,0 +1,20 @@ +include/master-slave.inc +[connection master] +connection master; +SET @saved_dbug = @@GLOBAL.debug_dbug; +SET @@global.debug_dbug= '+d,simulate_pos_4G'; +connection slave; +include/stop_slave.inc +SET @saved_dbug = @@GLOBAL.debug_dbug; +SET @@global.debug_dbug= '+d,simulate_pos_4G'; +CHANGE MASTER TO MASTER_HEARTBEAT_PERIOD=0.001; +include/start_slave.inc +connection master; +SET @@GLOBAL.debug_dbug = @saved_dbug; +connection slave; +SET @@GLOBAL.debug_dbug = @saved_dbug; +connection master; +CREATE TABLE t (f INT) ENGINE=INNODB; +INSERT INTO t VALUES (10); +DROP TABLE t; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_incompatible_heartbeat.test b/mysql-test/suite/rpl/t/rpl_incompatible_heartbeat.test new file mode 100644 index 00000000000..b1aa5435ff4 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_incompatible_heartbeat.test @@ -0,0 +1,47 @@ +# ==== Purpose ==== +# +# Test verifies that slave IO thread can process heartbeat events with log_pos +# values higher than UINT32_MAX. +# +# ==== Implementation ==== +# +# Steps: +# 0 - Stop slave threads. Configure a small master_heartbeat_period. +# 1 - Using debug points, simulate a huge binlog offset higher than +# UINT32_MAX on master. +# 2 - Start the slave and observe that slave IO thread is able to process +# the offset received through heartbeat event. +# +# ==== References ==== +# +# MDEV-16146: MariaDB slave stops with incompatible heartbeat +# +--source include/have_debug.inc +--source include/have_innodb.inc +--source include/have_binlog_format_mixed.inc +# Test simulates binarylog offsets higher than UINT32_MAX +--source include/have_64bit.inc +--source include/master-slave.inc + +--connection master +SET @saved_dbug = @@GLOBAL.debug_dbug; +SET @@global.debug_dbug= '+d,simulate_pos_4G'; + +--connection slave +--source include/stop_slave.inc +SET @saved_dbug = @@GLOBAL.debug_dbug; +SET @@global.debug_dbug= '+d,simulate_pos_4G'; +CHANGE MASTER TO MASTER_HEARTBEAT_PERIOD=0.001; +--source include/start_slave.inc + +--connection master +sleep 1; +SET @@GLOBAL.debug_dbug = @saved_dbug; +--sync_slave_with_master +SET @@GLOBAL.debug_dbug = @saved_dbug; + +--connection master +CREATE TABLE t (f INT) ENGINE=INNODB; +INSERT INTO t VALUES (10); +DROP TABLE t; +--source include/rpl_end.inc diff --git a/sql/log_event.h b/sql/log_event.h index 4e193232f4b..72d8766cea5 100644 --- a/sql/log_event.h +++ b/sql/log_event.h @@ -576,6 +576,22 @@ class String; #define MARIA_SLAVE_CAPABILITY_MINE MARIA_SLAVE_CAPABILITY_GTID +#define HB_EXTRA_HEADER_LEN 10 +/* + Extra header contains flags specific to Heartbeat_log_event and + respective data. The length needs to be updated when new memebers are + added to this header +*/ +#define HB_FLAGS_OFFSET 0 +/* + When the size of 'log_pos' within Heartbeat_log_event exceeds UINT_MAX32 it + cannot be stored in standard header as 'log_pos' is of 4 bytes size. Hence + extra_header is introduced. First 2 bytes represent flags. In case of long + 'log_pos' value 'HB_LONG_LOG_POS_OFFSET_F' bit within the flag will be set. + The log_pos is stored witin 'long_log_pos' variables. +*/ +#define HB_LONG_LOG_POS_OFFSET_F 0x1 +#define HB_LONG_LOG_POS_OFFSET 2 /** @enum Log_event_type @@ -5718,7 +5734,8 @@ bool copy_cache_to_file_wrapped(IO_CACHE *body, class Heartbeat_log_event: public Log_event { public: - Heartbeat_log_event(const char* buf, uint event_len, + uint16 hb_flags; + Heartbeat_log_event(const char* buf, ulong event_len, const Format_description_log_event* description_event); Log_event_type get_type_code() { return HEARTBEAT_LOG_EVENT; } bool is_valid() const diff --git a/sql/log_event_server.cc b/sql/log_event_server.cc index 6dee9b9adf6..2701f4f2f9a 100644 --- a/sql/log_event_server.cc +++ b/sql/log_event_server.cc @@ -8493,14 +8493,18 @@ void Ignorable_log_event::pack_info(Protocol *protocol) #if defined(HAVE_REPLICATION) -Heartbeat_log_event::Heartbeat_log_event(const char* buf, uint event_len, +Heartbeat_log_event::Heartbeat_log_event(const char* buf, ulong event_len, const Format_description_log_event* description_event) :Log_event(buf, description_event) { + uint16 hb_flags; uint8 header_size= description_event->common_header_len; - ident_len = event_len - header_size; + ident_len = event_len - (header_size + HB_EXTRA_HEADER_LEN); set_if_smaller(ident_len,FN_REFLEN-1); log_ident= buf + header_size; + hb_flags= uint2korr(buf + header_size + ident_len); + if (hb_flags & HB_LONG_LOG_POS_OFFSET_F) + log_pos= uint8korr(buf + header_size + ident_len + HB_LONG_LOG_POS_OFFSET); } #endif diff --git a/sql/slave.cc b/sql/slave.cc index 1da030084ef..f365a1e7b5c 100644 --- a/sql/slave.cc +++ b/sql/slave.cc @@ -6493,6 +6493,12 @@ static int queue_event(Master_info* mi,const char* buf, ulong event_len) TODO: handling `when' for SHOW SLAVE STATUS' snds behind */ + DBUG_EXECUTE_IF("simulate_pos_4G", + { + inc_pos= mi->master_log_pos; // temp_save + mi->master_log_pos= ((ulong)2394967295); + };); + if (memcmp(mi->master_log_name, hb.get_log_ident(), hb.get_ident_len()) || mi->master_log_pos > hb.log_pos) { /* missed events of heartbeat from the past */ @@ -6504,6 +6510,14 @@ static int queue_event(Master_info* mi,const char* buf, ulong event_len) error_msg.append_ulonglong(hb.log_pos); goto err; } + DBUG_EXECUTE_IF("simulate_pos_4G", + { + if (hb.log_pos > UINT32_MAX) + { + mi->master_log_pos= inc_pos; // restore + DBUG_SET("-d, simulate_pos_4G"); + } + };); /* Heartbeat events doesn't count in the binlog size, so we don't have to diff --git a/sql/sql_repl.cc b/sql/sql_repl.cc index 010ea794254..d68ae05b60e 100644 --- a/sql/sql_repl.cc +++ b/sql/sql_repl.cc @@ -824,12 +824,14 @@ get_slave_until_gtid(THD *thd, String *out_str) */ static int send_heartbeat_event(binlog_send_info *info, NET* net, String* packet, - const struct event_coordinates *coord, + struct event_coordinates *coord, enum enum_binlog_checksum_alg checksum_alg_arg) { DBUG_ENTER("send_heartbeat_event"); ulong ev_offset; + char extra_buf[HB_EXTRA_HEADER_LEN]; + uint16 hb_flags= 0; if (reset_transmit_packet(info, info->flags, &ev_offset, &info->errmsg)) DBUG_RETURN(1); @@ -850,19 +852,30 @@ static int send_heartbeat_event(binlog_send_info *info, size_t event_len = ident_len + LOG_EVENT_HEADER_LEN + (do_checksum ? BINLOG_CHECKSUM_LEN : 0); int4store(header + SERVER_ID_OFFSET, global_system_variables.server_id); + if (coord->pos <= UINT32_MAX) + { + int4store(header + LOG_POS_OFFSET, coord->pos); // log_pos + } + else + { + hb_flags|= HB_LONG_LOG_POS_OFFSET_F; + int2store(extra_buf + HB_FLAGS_OFFSET, hb_flags); + int8store(extra_buf + HB_LONG_LOG_POS_OFFSET, coord->pos); + event_len+= HB_EXTRA_HEADER_LEN; + } int4store(header + EVENT_LEN_OFFSET, event_len); int2store(header + FLAGS_OFFSET, 0); - int4store(header + LOG_POS_OFFSET, coord->pos); // log_pos - packet->append(header, sizeof(header)); packet->append(p, ident_len); // log_file_name + packet->append(extra_buf, sizeof(extra_buf)); if (do_checksum) { char b[BINLOG_CHECKSUM_LEN]; ha_checksum crc= my_checksum(0, (uchar*) header, sizeof(header)); crc= my_checksum(crc, (uchar*) p, ident_len); + crc= my_checksum(crc, (uchar*) extra_buf, sizeof(extra_buf)); int4store(b, crc); packet->append(b, sizeof(b)); } @@ -2525,6 +2538,13 @@ static int wait_new_events(binlog_send_info *info, /* in */ } #endif mysql_bin_log.unlock_binlog_end_pos(); + + DBUG_EXECUTE_IF("simulate_pos_4G", + { + coord.pos= ((ulong)5394967295); + DBUG_SET("-d, simulate_pos_4G"); + };); + ret= send_heartbeat_event(info, info->net, info->packet, &coord, info->current_checksum_alg);

1 0

[Commits] b01a8222582: MDEV-23677: Optimizer trace: remove print out with "no predicate for first keypart"
by psergey 17 Mar '21

17 Mar '21

revision-id: b01a8222582e1e05c166b0079113091ae19eb23c (mariadb-10.5.2-471-gb01a8222582) parent(s): c236f69d95321663c597a06f6e4e7ec440443ea5 author: Varun Gupta committer: Sergei Petrunia timestamp: 2021-03-17 21:05:41 +0300 message: MDEV-23677: Optimizer trace: remove print out with "no predicate for first keypart" --- sql/sql_select.cc | 4 ---- 1 file changed, 4 deletions(-) diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 28b4a5f9b36..73ea7eb201d 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -7869,11 +7869,7 @@ best_access_path(JOIN *join, tmp= COST_MULT(tmp, record_count); } else - { - if (!(found_part & 1)) - cause= "no predicate for first keypart"; tmp= best_time; // Do nothing - } } tmp= COST_ADD(tmp, s->startup_cost);

1 0

[Commits] 9ed36a8aa99: Rename IGNORED INDEX tests to match the SQL syntax
by psergey 16 Mar '21

16 Mar '21

revision-id: 9ed36a8aa9977db14947a90eff0bc8890e8e3220 (mariadb-10.5.2-446-g9ed36a8aa99) parent(s): 975ab539c330d924491b6c6c9fcc77bab7eb21e4 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-03-16 20:57:36 +0300 message: Rename IGNORED INDEX tests to match the SQL syntax ignore_indexes -> ignored_index ignore_indexes_innodb -> ignored_index_innodb --- mysql-test/main/{ignore_indexes.result => ignored_index.result} | 0 mysql-test/main/{ignore_indexes.test => ignored_index.test} | 0 .../main/{ignore_indexes_innodb.result => ignored_index_innodb.result} | 0 mysql-test/main/{ignore_indexes_innodb.test => ignored_index_innodb.test} | 0 4 files changed, 0 insertions(+), 0 deletions(-) diff --git a/mysql-test/main/ignore_indexes.result b/mysql-test/main/ignored_index.result similarity index 100% rename from mysql-test/main/ignore_indexes.result rename to mysql-test/main/ignored_index.result diff --git a/mysql-test/main/ignore_indexes.test b/mysql-test/main/ignored_index.test similarity index 100% rename from mysql-test/main/ignore_indexes.test rename to mysql-test/main/ignored_index.test diff --git a/mysql-test/main/ignore_indexes_innodb.result b/mysql-test/main/ignored_index_innodb.result similarity index 100% rename from mysql-test/main/ignore_indexes_innodb.result rename to mysql-test/main/ignored_index_innodb.result diff --git a/mysql-test/main/ignore_indexes_innodb.test b/mysql-test/main/ignored_index_innodb.test similarity index 100% rename from mysql-test/main/ignore_indexes_innodb.test rename to mysql-test/main/ignored_index_innodb.test

1 0

[Commits] 975ab539c33: MDEV-7317: Make an index ignorable to the optimizer: more tests
by psergey 16 Mar '21

16 Mar '21

revision-id: 975ab539c330d924491b6c6c9fcc77bab7eb21e4 (mariadb-10.5.2-445-g975ab539c33) parent(s): b85688df90600a39fd5f944c831ae8f777fbce4b author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-03-16 20:53:33 +0300 message: MDEV-7317: Make an index ignorable to the optimizer: more tests Add a test that marking index as [not] ignored uses algorithm=instant with InnoDB. --- mysql-test/main/ignore_indexes_innodb.result | 7 +++++++ mysql-test/main/ignore_indexes_innodb.test | 16 ++++++++++++++++ 2 files changed, 23 insertions(+) diff --git a/mysql-test/main/ignore_indexes_innodb.result b/mysql-test/main/ignore_indexes_innodb.result new file mode 100644 index 00000000000..cc7af31ef97 --- /dev/null +++ b/mysql-test/main/ignore_indexes_innodb.result @@ -0,0 +1,7 @@ +create table t1 (a int, b int, key a1(a)) engine=innodb; +insert into t1 values (1,1),(2,2),(3,3); +set alter_algorithm='instant'; +alter table t1 alter index a1 ignored; +alter table t1 alter index a1 not ignored; +set alter_algorithm=default; +drop table t1; diff --git a/mysql-test/main/ignore_indexes_innodb.test b/mysql-test/main/ignore_indexes_innodb.test new file mode 100644 index 00000000000..c25cdc8d928 --- /dev/null +++ b/mysql-test/main/ignore_indexes_innodb.test @@ -0,0 +1,16 @@ +--source include/have_innodb.inc + +# +# Check if marking index as [not] ignored is an instant operation with InnoDB +# +create table t1 (a int, b int, key a1(a)) engine=innodb; +insert into t1 values (1,1),(2,2),(3,3); + +set alter_algorithm='instant'; +alter table t1 alter index a1 ignored; + +alter table t1 alter index a1 not ignored; + +set alter_algorithm=default; +drop table t1; +

1 0

[Commits] b85688df906: MDEV-25078: ALTER INDEX is inconsistent with ADD/DROP/RENAME index
by psergey 16 Mar '21

16 Mar '21

revision-id: b85688df90600a39fd5f944c831ae8f777fbce4b (mariadb-10.5.2-444-gb85688df906) parent(s): dcf30322cabe61866cb0548dfb9fa70fcc387052 author: Varun Gupta committer: Sergei Petrunia timestamp: 2021-03-16 19:37:46 +0300 message: MDEV-25078: ALTER INDEX is inconsistent with ADD/DROP/RENAME index Allowing ALTER KEY syntax in ALTER TABLE,so one can use: ALTER TABLE tbl ALTER INDEX index_name IGNORED ALTER TABLE tbl ALTER KEY index_name IGNORED --- mysql-test/main/ignore_indexes.result | 21 +++++++++++++++++++++ mysql-test/main/ignore_indexes.test | 13 +++++++++++++ sql/sql_yacc.yy | 2 +- 3 files changed, 35 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/ignore_indexes.result b/mysql-test/main/ignore_indexes.result index a7b76146b04..733e44a3afa 100644 --- a/mysql-test/main/ignore_indexes.result +++ b/mysql-test/main/ignore_indexes.result @@ -458,3 +458,24 @@ BEGIN SET IGNORED= a+b; END | ERROR HY000: Unknown system variable 'IGNORED' +# +# ALLOWING ALTER KEY syntax in ALTER TABLE +# +CREATE TABLE t1 (a INT, KEY (a)); +ALTER TABLE t1 ALTER INDEX a IGNORED; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + KEY `a` (`a`) IGNORED +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a INT, KEY (a)); +ALTER TABLE t1 ALTER KEY a IGNORED; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + KEY `a` (`a`) IGNORED +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; diff --git a/mysql-test/main/ignore_indexes.test b/mysql-test/main/ignore_indexes.test index 185c653d52a..a1084f3eb9c 100644 --- a/mysql-test/main/ignore_indexes.test +++ b/mysql-test/main/ignore_indexes.test @@ -429,3 +429,16 @@ BEGIN SET IGNORED= a+b; END | DELIMITER ;| + +--echo # +--echo # ALLOWING ALTER KEY syntax in ALTER TABLE +--echo # + +CREATE TABLE t1 (a INT, KEY (a)); +ALTER TABLE t1 ALTER INDEX a IGNORED; +SHOW CREATE TABLE t1; +DROP TABLE t1; +CREATE TABLE t1 (a INT, KEY (a)); +ALTER TABLE t1 ALTER KEY a IGNORED; +SHOW CREATE TABLE t1; +DROP TABLE t1; diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 341668d674d..eb25d206f7b 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -7794,7 +7794,7 @@ alter_list_item: if (unlikely(Lex->add_alter_list($4, $7, $3))) MYSQL_YYABORT; } - | ALTER INDEX_SYM ident ignorability + | ALTER key_or_index ident ignorability { LEX *lex= Lex; Alter_index_ignorability *ac= new (thd->mem_root)

1 0