- commits - lists.mariadb.org

[Commits] b97920e3918: Update tests after rebasing over 8.0.20
by psergey 25 Mar '21

25 Mar '21

revision-id: b97920e3918b5163fb15c09a366109bbe120bb1c () parent(s): 90c4f22ad136cfb17dcdf6d6f78e8b5b3fc3c6f5 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-03-25 20:21:15 +0300 message: Update tests after rebasing over 8.0.20 --- .../suite/rocksdb/include/select_from_is_rowlocks.inc | 15 +++++++++++++-- .../rocksdb/r/level_repeatable_read-range_locking.result | 2 +- mysql-test/suite/rocksdb/r/range_locking.result | 2 +- .../suite/rocksdb/r/range_locking_escalation.result | 2 +- mysql-test/suite/rocksdb/r/range_locking_rev_cf.result | 2 +- .../suite/rocksdb/r/range_locking_shared_locks.result | 2 +- mysql-test/suite/rocksdb/t/range_locking_escalation.test | 1 + 7 files changed, 19 insertions(+), 7 deletions(-) diff --git a/mysql-test/suite/rocksdb/include/select_from_is_rowlocks.inc b/mysql-test/suite/rocksdb/include/select_from_is_rowlocks.inc index e5f54d68914..6dbd63c87c4 100644 --- a/mysql-test/suite/rocksdb/include/select_from_is_rowlocks.inc +++ b/mysql-test/suite/rocksdb/include/select_from_is_rowlocks.inc @@ -17,10 +17,21 @@ set @cf_id=(select column_family from information_schema.rocksdb_ddl where table_name='t1' and index_name='PRIMARY'); set @rtrx_id=(select transaction_id from information_schema.rocksdb_trx where thread_id=connection_id()); -set @indexnr= (select lower(lpad(hex(index_number),8,'0')) from information_schema.rocksdb_ddl +set @indexnr= (select lower( + concat( + lpad(hex(db_number),8,'0'), + lpad(hex(index_number),8,'0') + ) + ) + from information_schema.rocksdb_ddl where table_name='t1' and index_name='PRIMARY'); -set @indexnr_next= (select lower(lpad(hex(index_number+1),8,'0')) +set @indexnr_next= (select lower( + concat( + lpad(hex(db_number),8,'0'), + lpad(hex(index_number+1),8,'0') + ) + ) from information_schema.rocksdb_ddl where table_name='t1' and index_name='PRIMARY'); diff --git a/mysql-test/suite/rocksdb/r/level_repeatable_read-range_locking.result b/mysql-test/suite/rocksdb/r/level_repeatable_read-range_locking.result index 37dc45a17f4..0592b099238 100644 --- a/mysql-test/suite/rocksdb/r/level_repeatable_read-range_locking.result +++ b/mysql-test/suite/rocksdb/r/level_repeatable_read-range_locking.result @@ -64,7 +64,7 @@ SELECT a from t2; a 1 INSERT INTO t2 (a) VALUES (1), (3); -ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +ERROR 23000: Duplicate entry '1' for key 't2.PRIMARY' connection con2; INSERT INTO t2 (a) VALUES (2); COMMIT; diff --git a/mysql-test/suite/rocksdb/r/range_locking.result b/mysql-test/suite/rocksdb/r/range_locking.result index fc4a4c53e8a..603c0b99f09 100644 --- a/mysql-test/suite/rocksdb/r/range_locking.result +++ b/mysql-test/suite/rocksdb/r/range_locking.result @@ -64,7 +64,7 @@ insert into t2 values (1,1),(2,2); begin; insert into t2 values (3,3); insert into t2 values (10,2); -ERROR 23000: Duplicate entry '2' for key 'a' +ERROR 23000: Duplicate entry '2' for key 't2.a' connection con2; begin; select * from t2 where pk=3 for update; diff --git a/mysql-test/suite/rocksdb/r/range_locking_escalation.result b/mysql-test/suite/rocksdb/r/range_locking_escalation.result index ae4c01cad72..698b0f4a02f 100644 --- a/mysql-test/suite/rocksdb/r/range_locking_escalation.result +++ b/mysql-test/suite/rocksdb/r/range_locking_escalation.result @@ -23,5 +23,5 @@ count(*) 10000 show status like 'rocksdb_locktree_escalation_count'; Variable_name Value -rocksdb_locktree_escalation_count 3321 +rocksdb_locktree_escalation_count 128 drop table t0,t1; diff --git a/mysql-test/suite/rocksdb/r/range_locking_rev_cf.result b/mysql-test/suite/rocksdb/r/range_locking_rev_cf.result index 883cb88ec79..5e1c2cf98a5 100644 --- a/mysql-test/suite/rocksdb/r/range_locking_rev_cf.result +++ b/mysql-test/suite/rocksdb/r/range_locking_rev_cf.result @@ -64,7 +64,7 @@ insert into t2 values (1,1),(2,2); begin; insert into t2 values (3,3); insert into t2 values (10,2); -ERROR 23000: Duplicate entry '2' for key 'a' +ERROR 23000: Duplicate entry '2' for key 't2.a' connection con2; begin; select * from t2 where pk=3 for update; diff --git a/mysql-test/suite/rocksdb/r/range_locking_shared_locks.result b/mysql-test/suite/rocksdb/r/range_locking_shared_locks.result index 580108de6f6..90223043c08 100644 --- a/mysql-test/suite/rocksdb/r/range_locking_shared_locks.result +++ b/mysql-test/suite/rocksdb/r/range_locking_shared_locks.result @@ -208,7 +208,7 @@ $cf_id $TRX2_ID ${indexnr}800009ce S connection default; show status like 'rocksdb_locktree_current_lock_memory'; Variable_name Value -rocksdb_locktree_current_lock_memory 8792 +rocksdb_locktree_current_lock_memory 9016 set @save_mlm= @@rocksdb_max_lock_memory; # Set the limit to cause lock escalation: set @cur_mem_usage= (select diff --git a/mysql-test/suite/rocksdb/t/range_locking_escalation.test b/mysql-test/suite/rocksdb/t/range_locking_escalation.test index cd4f8a30a46..5a6e9fa6616 100644 --- a/mysql-test/suite/rocksdb/t/range_locking_escalation.test +++ b/mysql-test/suite/rocksdb/t/range_locking_escalation.test @@ -3,6 +3,7 @@ # --source include/have_rocksdb.inc +--source suite/rocksdb/include/have_range_locking.inc --enable_connect_log

1 0

[Commits] 480a067: MDEV-25128 Wrong result from join with materialized semi-join and
by IgorBabaev 24 Mar '21

24 Mar '21

revision-id: 480a06718d137c9ee7784012ccb609b9e79ff08c (mariadb-10.3.26-118-g480a067) parent(s): 7d5ec9f1aeec1ea8d00a0aa5bfec521948cfb8be author: Igor Babaev committer: Igor Babaev timestamp: 2021-03-23 20:54:54 -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 serg(a)mariadb.com --- mysql-test/main/derived_cond_pushdown.result | 211 +++++++++++++++++++++++++++ mysql-test/main/derived_cond_pushdown.test | 37 +++++ sql/opt_split.cc | 48 +++++- sql/sql_select.cc | 17 +++ 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..c3a2d03 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,40 @@ bool JOIN::inject_best_splitting_cond(table_map remaining_tables) /** @brief + Test if equality is injected for split optimization + + @param + eq_item equality to to test + + @retval + true eq_item is equality injected for split optimization + false otherwise +*/ + +bool is_eq_cond_injected_for_split_opt(Item_func_eq *eq_item) +{ + 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) + return true; + } + 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..1eb2378 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -292,6 +292,8 @@ void set_postjoin_aggr_write_func(JOIN_TAB *tab); static Item **get_sargable_cond(JOIN *join, TABLE *table); +bool is_eq_cond_injected_for_split_opt(Item_func_eq *eq_item); + #ifndef DBUG_OFF /* @@ -21787,6 +21789,21 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond, cond->marker=3; // Checked when read return (COND*) 0; } + /* + If cond is an equality injected for split optimization then + a. when retain_ref_cond == false : cond is removed unconditionally + (cond that supports ref access is removed by the preceding code) + b. when retain_ref_cond == true : cond is removed if it does not + support ref access + */ + if (left_item->type() == Item::FIELD_ITEM && + is_eq_cond_injected_for_split_opt((Item_func_eq *) cond) && + (!retain_ref_cond || + !test_if_ref(root_cond, (Item_field*) left_item,right_item))) + { + cond->marker=3; + return (COND*) 0; + } } cond->marker=2; cond->set_join_tab_idx(join_tab_idx_arg);

1 0

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

24 Mar '21

revision-id: c1f61f42405aea9b114fd7c2bd1f462ad5e2a76e (mariadb-10.3.26-111-gc1f61f4) parent(s): eb7c5530eccb7d6782077e5562f5a471d2ccbc01 author: Igor Babaev committer: Igor Babaev timestamp: 2021-03-23 20:37:10 -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 | 48 +++++- sql/sql_select.cc | 17 +++ 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..c3a2d03 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,40 @@ bool JOIN::inject_best_splitting_cond(table_map remaining_tables) /** @brief + Test if equality is injected for split optimization + + @param + eq_item equality to to test + + @retval + true eq_item is equality injected for split optimization + false otherwise +*/ + +bool is_eq_cond_injected_for_split_opt(Item_func_eq *eq_item) +{ + 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) + return true; + } + 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..1eb2378 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -292,6 +292,8 @@ void set_postjoin_aggr_write_func(JOIN_TAB *tab); static Item **get_sargable_cond(JOIN *join, TABLE *table); +bool is_eq_cond_injected_for_split_opt(Item_func_eq *eq_item); + #ifndef DBUG_OFF /* @@ -21787,6 +21789,21 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond, cond->marker=3; // Checked when read return (COND*) 0; } + /* + If cond is an equality injected for split optimization then + a. when retain_ref_cond == false : cond is removed unconditionally + (cond that supports ref access is removed by the preceding code) + b. when retain_ref_cond == true : cond is removed if it does not + support ref access + */ + if (left_item->type() == Item::FIELD_ITEM && + is_eq_cond_injected_for_split_opt((Item_func_eq *) cond) && + (!retain_ref_cond || + !test_if_ref(root_cond, (Item_field*) left_item,right_item))) + { + cond->marker=3; + return (COND*) 0; + } } cond->marker=2; cond->set_join_tab_idx(join_tab_idx_arg);

1 0

[Commits] 8f7a6cd: MDEV-24767 Wrong result when forced BNLH is used for join supported
by IgorBabaev 23 Mar '21

23 Mar '21

revision-id: 8f7a6cde580298116b50b144984f996dc6af60ae (mariadb-10.2.31-811-g8f7a6cd) parent(s): 56274bd5e4115d86059936ddd3bf656dab1b4354 author: Igor Babaev committer: Igor Babaev timestamp: 2021-03-22 22:04:54 -0700 message: MDEV-24767 Wrong result when forced BNLH is used for join supported by compound index This typo bug may lead to wrong result sets for equi-join queries where the join operation is supported by a compound index such that the order of its components differs from the order of the corresponding columns in the table the index belongs to. The bug manifests itself only when usage of the BNLH algorithm is forced. The fix for the bug was provided by Chu Huaxing. --- mysql-test/r/join_cache.result | 30 ++++++++++++++++++++++++++++++ mysql-test/t/join_cache.test | 24 ++++++++++++++++++++++++ sql/sql_join_cache.cc | 2 +- 3 files changed, 55 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result index 87c4079..6b458e0 100644 --- a/mysql-test/r/join_cache.result +++ b/mysql-test/r/join_cache.result @@ -6107,4 +6107,34 @@ WHERE e IS NULL; a b c d e DROP TABLE t1,t2,t3,t4; set join_cache_level=@save_join_cache_level; +# +# MDEV-24767: forced BNLH used for equi-join supported by compound index +# +create table t1 (a int, b int, c int ) engine=myisam ; +create table t2 (a int, b int, c int, primary key (c,a,b)) engine=myisam ; +insert into t1 values (3,4,2), (5,6,4); +insert into t2 values (3,4,2), (5,6,4); +select t1.a, t1.b, t1.c from t1,t2 +where t2.a = t1.a and t2.b = t1.b and t2.c=t1.c; +a b c +3 4 2 +5 6 4 +explain select t1.a, t1.b, t1.c from t1,t2 +where t2.a = t1.a and t2.b = t1.b and t2.c=t1.c; +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 eq_ref PRIMARY PRIMARY 12 test.t1.c,test.t1.a,test.t1.b 1 Using index +set join_cache_level=3; +select t1.a, t1.b, t1.c from t1,t2 +where t2.a = t1.a and t2.b = t1.b and t2.c=t1.c; +a b c +3 4 2 +5 6 4 +explain select t1.a, t1.b, t1.c from t1,t2 +where t2.a = t1.a and t2.b = t1.b and t2.c=t1.c; +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 hash_index PRIMARY #hash#PRIMARY:PRIMARY 12:12 test.t1.c,test.t1.a,test.t1.b 2 Using index; Using join buffer (flat, BNLH join) +drop table t1,t2; +set join_cache_level=@save_join_cache_level; set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test index 15cd1e9..55ae6af 100644 --- a/mysql-test/t/join_cache.test +++ b/mysql-test/t/join_cache.test @@ -4050,5 +4050,29 @@ DROP TABLE t1,t2,t3,t4; set join_cache_level=@save_join_cache_level; +--echo # +--echo # MDEV-24767: forced BNLH used for equi-join supported by compound index +--echo # + +create table t1 (a int, b int, c int ) engine=myisam ; +create table t2 (a int, b int, c int, primary key (c,a,b)) engine=myisam ; +insert into t1 values (3,4,2), (5,6,4); +insert into t2 values (3,4,2), (5,6,4); + +let $q= +select t1.a, t1.b, t1.c from t1,t2 + where t2.a = t1.a and t2.b = t1.b and t2.c=t1.c; + +eval $q; +eval explain $q; + +set join_cache_level=3; +eval $q; +eval explain $q; + +drop table t1,t2; + +set join_cache_level=@save_join_cache_level; + # The following command must be the last one in the file set @@optimizer_switch=@save_optimizer_switch; diff --git a/sql/sql_join_cache.cc b/sql/sql_join_cache.cc index 594094a..4f9facc 100644 --- a/sql/sql_join_cache.cc +++ b/sql/sql_join_cache.cc @@ -1199,7 +1199,7 @@ bool JOIN_CACHE::check_emb_key_usage() Item *item= ref->items[i]->real_item(); Field *fld= ((Item_field *) item)->field; CACHE_FIELD *init_copy= field_descr+flag_fields+i; - for (j= i, copy= init_copy; i < local_key_arg_fields; i++, copy++) + for (j= i, copy= init_copy; j < local_key_arg_fields; j++, copy++) { if (fld->eq(copy->field)) {

1 0

[Commits] 90c4f22ad13: Update RocksDB submodule to a version that includes Range Locking code
by psergey 22 Mar '21

22 Mar '21

revision-id: 90c4f22ad136cfb17dcdf6d6f78e8b5b3fc3c6f5 () parent(s): bc468ab5dac998894e041b60ad3f3c70e2100c7c author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-03-22 22:47:31 +0300 message: Update RocksDB submodule to a version that includes Range Locking code * fb-mysql-8.0.20 is based on RocksDB 6.16.3 * Update RocksDB to a 6.19-based branch with fix for RocksDB PR#7938 This requires making updates to non-range-locking code: - rocksdb::StopWatchNano ctor now has different signature - storage_/rocksdb/get_rocksdb_files.sh should use a different way to create rocksdb/util/build_version.cc file. (note: not sure this part is fully done) --- rocksdb | 2 +- storage/rocksdb/get_rocksdb_files.sh | 11 +++++------ storage/rocksdb/ha_rocksdb.cc | 4 ++-- 3 files changed, 8 insertions(+), 9 deletions(-) diff --git a/rocksdb b/rocksdb index e32a64aa547..c9878baa872 160000 --- a/rocksdb +++ b/rocksdb @@ -1 +1 @@ -Subproject commit e32a64aa547ad7f17af74648147e521acbc21d3f +Subproject commit c9878baa872a1841a993680b1787337e0c93b52a diff --git a/storage/rocksdb/get_rocksdb_files.sh b/storage/rocksdb/get_rocksdb_files.sh index 121d664a086..550c6682f0c 100755 --- a/storage/rocksdb/get_rocksdb_files.sh +++ b/storage/rocksdb/get_rocksdb_files.sh @@ -16,12 +16,11 @@ rm $MKFILE bv=rocksdb/util/build_version.cc date=$(date +%F) git_sha=$(pushd rocksdb >/dev/null && git rev-parse HEAD 2>/dev/null && popd >/dev/null) +git_mod=$(pushd rocksdb >/dev/null && git diff-index HEAD --quiet && echo $? && popd >/dev/null) + if [ ! -f $bv ] || [ -z $git_sha ] || [ ! `grep $git_sha $bv` ] then -echo "#include \"build_version.h\" -const char* rocksdb_build_git_sha = -\"rocksdb_build_git_sha:$git_sha\"; -const char* rocksdb_build_git_date = -\"rocksdb_build_git_date:$date\"; -const char* rocksdb_build_compile_date = __DATE__;" > $bv +sed -e "s/@GIT_SHA@/$git_sha/" \ + -e "s/@GIT_DATE@/$date/" \ + -e "s/@GIT_MOD@/$git_mod/" < $bv.in > $bv fi diff --git a/storage/rocksdb/ha_rocksdb.cc b/storage/rocksdb/ha_rocksdb.cc index c4df0ae6c17..fdd3b0e08e1 100644 --- a/storage/rocksdb/ha_rocksdb.cc +++ b/storage/rocksdb/ha_rocksdb.cc @@ -4675,7 +4675,7 @@ static xa_status_code rocksdb_commit_by_xid( DBUG_ASSERT(xid != nullptr); DBUG_ASSERT(commit_latency_stats != nullptr); - rocksdb::StopWatchNano timer(rocksdb::Env::Default(), true); + rocksdb::StopWatchNano timer(rocksdb::SystemClock::Default(), true); const auto name = rdb_xid_to_string(*xid); DBUG_ASSERT(!name.empty()); @@ -4865,7 +4865,7 @@ static int rocksdb_commit(handlerton *const hton MY_ATTRIBUTE((__unused__)), DBUG_ASSERT(thd != nullptr); DBUG_ASSERT(commit_latency_stats != nullptr); - rocksdb::StopWatchNano timer(rocksdb::Env::Default(), true); + rocksdb::StopWatchNano timer(rocksdb::SystemClock::Default(), true); /* note: h->external_lock(F_UNLCK) is called after this function is called) */ Rdb_transaction *tx = get_tx_from_thd(thd);

1 0

[Commits] 2553fc0432a: Range Locking: update RocksDB to use master branch with fixes
by psergey 22 Mar '21

22 Mar '21

revision-id: 2553fc0432ad1a0643123971870c3ff1da458e89 () parent(s): bc468ab5dac998894e041b60ad3f3c70e2100c7c author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-03-22 18:58:26 +0300 message: Range Locking: update RocksDB to use master branch with fixes Need a fix for https://github.com/facebook/rocksdb/pull/7938 to be in the tree. --- rocksdb | 2 +- storage/rocksdb/ha_rocksdb.cc | 4 ++-- 2 files changed, 3 insertions(+), 3 deletions(-) diff --git a/rocksdb b/rocksdb index e32a64aa547..c9878baa872 160000 --- a/rocksdb +++ b/rocksdb @@ -1 +1 @@ -Subproject commit e32a64aa547ad7f17af74648147e521acbc21d3f +Subproject commit c9878baa872a1841a993680b1787337e0c93b52a diff --git a/storage/rocksdb/ha_rocksdb.cc b/storage/rocksdb/ha_rocksdb.cc index c4df0ae6c17..74f18d39dfd 100644 --- a/storage/rocksdb/ha_rocksdb.cc +++ b/storage/rocksdb/ha_rocksdb.cc @@ -4675,7 +4675,7 @@ static xa_status_code rocksdb_commit_by_xid( DBUG_ASSERT(xid != nullptr); DBUG_ASSERT(commit_latency_stats != nullptr); - rocksdb::StopWatchNano timer(rocksdb::Env::Default(), true); + rocksdb::StopWatchNano timer(rocksdb::Env::Default()->GetSystemClock(), true); const auto name = rdb_xid_to_string(*xid); DBUG_ASSERT(!name.empty()); @@ -4865,7 +4865,7 @@ static int rocksdb_commit(handlerton *const hton MY_ATTRIBUTE((__unused__)), DBUG_ASSERT(thd != nullptr); DBUG_ASSERT(commit_latency_stats != nullptr); - rocksdb::StopWatchNano timer(rocksdb::Env::Default(), true); + rocksdb::StopWatchNano timer(rocksdb::Env::Default()->GetSystemClock(), true); /* note: h->external_lock(F_UNLCK) is called after this function is called) */ Rdb_transaction *tx = get_tx_from_thd(thd);

1 0

[Commits] e26d75d3: MDEV-25206 Crash with CREATE VIEW .. SELECT with non-existing field
by IgorBabaev 21 Mar '21

21 Mar '21

revision-id: e26d75d38995762735efe7d3a67edb08185e1ba5 (mariadb-10.4.11-578-ge26d75d3) parent(s): 550cf13eb3e8a25826a0fa67935fc28ee7adb0c8 author: Igor Babaev committer: Igor Babaev timestamp: 2021-03-21 12:08:54 -0700 message: MDEV-25206 Crash with CREATE VIEW .. SELECT with non-existing field in ON condition The fix of the bug MDEV-25002 for 10.4 turned out to be incomplete. It caused crashes when executing CREATE VIEW, CREATE TABLE .. SELECT, INSERT .. SELECT statements if their SELECTs contained references to non-existing fields. This patch complements the fix for MDEV-25002 in order to avoid such crashes. Approved by Oleksandr Byelkin <sanja(a)mariadb.com> --- mysql-test/main/view.result | 16 ++++++++++++++++ mysql-test/main/view.test | 21 +++++++++++++++++++++ sql/item.cc | 5 +++-- 3 files changed, 40 insertions(+), 2 deletions(-) diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result index c55c93e..de4be4e 100644 --- a/mysql-test/main/view.result +++ b/mysql-test/main/view.result @@ -6824,3 +6824,19 @@ Drop table t1; # # End of 10.3 tests # +# +# MDEV-25206: view specification contains unknown column reference +# +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b int); +INSERT INTO t2 VALUES (2),(3); +CREATE TABLE t3 (c int); +CREATE VIEW v1 AS SELECT * FROM t1 JOIN t2 ON t1.x > t2.b; +ERROR 42S22: Unknown column 't1.x' in 'on clause' +INSERT INTO t3 SELECT * FROM t1 JOIN t2 ON t1.x > t2.b; +ERROR 42S22: Unknown column 't1.x' in 'on clause' +CREATE TABLE t4 AS SELECT * FROM t1 JOIN t2 ON t1.x > t2.b; +ERROR 42S22: Unknown column 't1.x' in 'on clause' +DROP TABLE t1,t2,t3; +# End of 10.4 tests diff --git a/mysql-test/main/view.test b/mysql-test/main/view.test index 1429642..ca54569 100644 --- a/mysql-test/main/view.test +++ b/mysql-test/main/view.test @@ -6542,3 +6542,24 @@ Drop table t1; --echo # --echo # End of 10.3 tests --echo # + +--echo # +--echo # MDEV-25206: view specification contains unknown column reference +--echo # + +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b int); +INSERT INTO t2 VALUES (2),(3); +CREATE TABLE t3 (c int); + +--error ER_BAD_FIELD_ERROR +CREATE VIEW v1 AS SELECT * FROM t1 JOIN t2 ON t1.x > t2.b; +--error ER_BAD_FIELD_ERROR +INSERT INTO t3 SELECT * FROM t1 JOIN t2 ON t1.x > t2.b; +--error ER_BAD_FIELD_ERROR +CREATE TABLE t4 AS SELECT * FROM t1 JOIN t2 ON t1.x > t2.b; + +DROP TABLE t1,t2,t3; + +--echo # End of 10.4 tests diff --git a/sql/item.cc b/sql/item.cc index b8eb0d4..ab1916c 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -5422,8 +5422,9 @@ Item_field::fix_outer_field(THD *thd, Field **from_field, Item **reference) Name_resolution_context *outer_context= 0; SELECT_LEX *select= 0; /* Currently derived tables cannot be correlated */ - if (current_sel->master_unit()->first_select()->get_linkage() != - DERIVED_TABLE_TYPE) + if ((current_sel->master_unit()->first_select()->get_linkage() != + DERIVED_TABLE_TYPE) && + current_sel->master_unit()->outer_select()) outer_context= context->outer_context; /*

1 0

[Commits] 35515ec18fd: MDEV-7317: Make an index ignorable to the optimizer
by psergey 21 Mar '21

21 Mar '21

revision-id: 35515ec18fd546b658b6aa63c8a7724aae9bcc4b (mariadb-10.5.2-496-g35515ec18fd) parent(s): b3c470a3c7e8bb497bca5bc9fca4cf52cfc9e88e author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-03-21 14:18:46 +0300 message: MDEV-7317: Make an index ignorable to the optimizer Update test results: rocksdb.type_blob_indexes, rocksdb.type_text_indexes --- .../mysql-test/rocksdb/r/type_blob_indexes.result | 18 +++++++++--------- .../mysql-test/rocksdb/r/type_text_indexes.result | 10 +++++----- 2 files changed, 14 insertions(+), 14 deletions(-) diff --git a/storage/rocksdb/mysql-test/rocksdb/r/type_blob_indexes.result b/storage/rocksdb/mysql-test/rocksdb/r/type_blob_indexes.result index 1e614a2bbf1..40e31e9fe6d 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/type_blob_indexes.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/type_blob_indexes.result @@ -11,8 +11,8 @@ PRIMARY KEY b (b(32)) Warnings: Warning 1280 Name 'b' ignored for PRIMARY key. SHOW INDEX IN t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 PRIMARY 1 b A 1000 32 NULL LSMTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 PRIMARY 1 b A 1000 32 NULL LSMTREE NO INSERT INTO t1 (b,t,m,l) VALUES ('','','',''), ('a','b','c','d'), @@ -49,10 +49,10 @@ pk INT AUTO_INCREMENT PRIMARY KEY, UNIQUE INDEX l_t (l(256),t(64)) ) ENGINE=rocksdb; SHOW INDEX IN t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 PRIMARY 1 pk # # NULL NULL # # -t1 0 l_t 1 l # # 256 NULL # # -t1 0 l_t 2 t # # 64 NULL # # +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 PRIMARY 1 pk # # NULL NULL # # NO +t1 0 l_t 1 l # # 256 NULL # # NO +t1 0 l_t 2 t # # 64 NULL # # NO INSERT INTO t1 (b,t,m,l) VALUES ('','','',''), ('a','b','c','d'), @@ -105,9 +105,9 @@ pk INT AUTO_INCREMENT PRIMARY KEY, INDEX (m(128)) ) ENGINE=rocksdb; SHOW INDEX IN t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 PRIMARY 1 pk A 1000 NULL NULL LSMTREE -t1 1 m 1 m A 500 128 NULL YES LSMTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 PRIMARY 1 pk A 1000 NULL NULL LSMTREE NO +t1 1 m 1 m A 500 128 NULL YES LSMTREE NO INSERT INTO t1 (b,t,m,l) VALUES ('','','',''), ('a','b','c','d'), diff --git a/storage/rocksdb/mysql-test/rocksdb/r/type_text_indexes.result b/storage/rocksdb/mysql-test/rocksdb/r/type_text_indexes.result index 7db5c23c53f..ddab60cc5c3 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/type_text_indexes.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/type_text_indexes.result @@ -11,8 +11,8 @@ PRIMARY KEY t (t(32)) Warnings: Warning 1280 Name 't' ignored for PRIMARY key. SHOW INDEX IN t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 PRIMARY 1 t A 1000 32 NULL LSMTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 PRIMARY 1 t A 1000 32 NULL LSMTREE NO INSERT INTO t1 (t,tt,m,l) VALUES ('','','',''), ('a','b','c','d'), @@ -60,9 +60,9 @@ INDEX (m(128)) Warnings: Warning 1280 Name 'mt' ignored for PRIMARY key. SHOW INDEX IN t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 PRIMARY 1 pk A 1000 1 NULL LSMTREE -t1 1 m 1 m A 500 128 NULL YES LSMTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 PRIMARY 1 pk A 1000 1 NULL LSMTREE NO +t1 1 m 1 m A 500 128 NULL YES LSMTREE NO INSERT INTO t1 (t,tt,m,l,pk) VALUES ('','','','','0'), ('a','b','c','d','1'),

1 0

[Commits] fd06ac7: MDEV-25206 Crash with CREATE VIEW .. SELECT with non-existing field
by IgorBabaev 20 Mar '21

20 Mar '21

revision-id: fd06ac711caab2245de1cd994fbf301a4f0543e1 (mariadb-10.4.11-577-gfd06ac7) parent(s): 77d8afd5c39ee779cb083d07e9b70d330067dc88 author: Igor Babaev committer: Igor Babaev timestamp: 2021-03-19 20:51:10 -0700 message: MDEV-25206 Crash with CREATE VIEW .. SELECT with non-existing field in ON condition The fix of the bug MDEV-25002 for 10.4 turned out to be incomplete. It caused crashes when executing CREATE VIEW, CREATE TABLE .. SELECT, INSERT .. SELECT statements if their SELECTs contained references to non-existing fields. This patch complements the fix for MDEV-25002 in order to avoid such crashes. --- mysql-test/main/view.result | 16 ++++++++++++++++ mysql-test/main/view.test | 21 +++++++++++++++++++++ sql/item.cc | 5 +++-- 3 files changed, 40 insertions(+), 2 deletions(-) diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result index c55c93e..de4be4e 100644 --- a/mysql-test/main/view.result +++ b/mysql-test/main/view.result @@ -6824,3 +6824,19 @@ Drop table t1; # # End of 10.3 tests # +# +# MDEV-25206: view specification contains unknown column reference +# +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b int); +INSERT INTO t2 VALUES (2),(3); +CREATE TABLE t3 (c int); +CREATE VIEW v1 AS SELECT * FROM t1 JOIN t2 ON t1.x > t2.b; +ERROR 42S22: Unknown column 't1.x' in 'on clause' +INSERT INTO t3 SELECT * FROM t1 JOIN t2 ON t1.x > t2.b; +ERROR 42S22: Unknown column 't1.x' in 'on clause' +CREATE TABLE t4 AS SELECT * FROM t1 JOIN t2 ON t1.x > t2.b; +ERROR 42S22: Unknown column 't1.x' in 'on clause' +DROP TABLE t1,t2,t3; +# End of 10.4 tests diff --git a/mysql-test/main/view.test b/mysql-test/main/view.test index 1429642..ca54569 100644 --- a/mysql-test/main/view.test +++ b/mysql-test/main/view.test @@ -6542,3 +6542,24 @@ Drop table t1; --echo # --echo # End of 10.3 tests --echo # + +--echo # +--echo # MDEV-25206: view specification contains unknown column reference +--echo # + +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b int); +INSERT INTO t2 VALUES (2),(3); +CREATE TABLE t3 (c int); + +--error ER_BAD_FIELD_ERROR +CREATE VIEW v1 AS SELECT * FROM t1 JOIN t2 ON t1.x > t2.b; +--error ER_BAD_FIELD_ERROR +INSERT INTO t3 SELECT * FROM t1 JOIN t2 ON t1.x > t2.b; +--error ER_BAD_FIELD_ERROR +CREATE TABLE t4 AS SELECT * FROM t1 JOIN t2 ON t1.x > t2.b; + +DROP TABLE t1,t2,t3; + +--echo # End of 10.4 tests diff --git a/sql/item.cc b/sql/item.cc index 3c73631..7a3b90b 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -5422,8 +5422,9 @@ Item_field::fix_outer_field(THD *thd, Field **from_field, Item **reference) Name_resolution_context *outer_context= 0; SELECT_LEX *select= 0; /* Currently derived tables cannot be correlated */ - if (current_sel->master_unit()->first_select()->get_linkage() != - DERIVED_TABLE_TYPE) + if ((current_sel->master_unit()->first_select()->get_linkage() != + DERIVED_TABLE_TYPE) && + current_sel->master_unit()->outer_select()) outer_context= context->outer_context; /*

1 0

[Commits] b3c470a3c7e: MDEV-23646: Optimizer trace: optimize_cond() should show ON expression processing
by psergey 19 Mar '21

19 Mar '21

revision-id: b3c470a3c7e8bb497bca5bc9fca4cf52cfc9e88e (mariadb-10.5.2-495-gb3c470a3c7e) parent(s): b9a45ba40fbf251f5635ecebad6ea7414be39d41 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-03-19 18:12:26 +0300 message: MDEV-23646: Optimizer trace: optimize_cond() should show ON expression processing Print the build_equal_items() step for ON expression processing --- mysql-test/main/opt_trace.result | 62 ++++++++++++++++++++++++++++++++++++++++ mysql-test/main/opt_trace.test | 8 ++++++ sql/sql_select.cc | 10 +++++++ 3 files changed, 80 insertions(+) diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index 192f9040c35..4d00e32c4a7 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -2319,6 +2319,13 @@ select t1.a from t1 left join t2 on t1.a=t2.a { "join_optimization": { "select_id": 1, "steps": [ + { + "build_equal_items": { + "condition": "ON expr", + "attached_to": "t2", + "resulting_condition": "multiple equal(t1.a, t2.a)" + } + }, { "table_dependencies": [ { @@ -2449,6 +2456,13 @@ explain select * from t1 left join t2 on t2.a=t1.a { "join_optimization": { "select_id": 1, "steps": [ + { + "build_equal_items": { + "condition": "ON expr", + "attached_to": "t2", + "resulting_condition": "multiple equal(t2.a, t1.a)" + } + }, { "table_dependencies": [ { @@ -2621,6 +2635,13 @@ explain select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and "join_optimization": { "select_id": 1, "steps": [ + { + "build_equal_items": { + "condition": "ON expr", + "attached_to": "t3", + "resulting_condition": "multiple equal(t2.a, t1.a, t3.a) and multiple equal(t2.b, t3.b)" + } + }, { "table_dependencies": [ { @@ -9007,5 +9028,46 @@ json_detailed(json_extract(trace, '$**.substitute_best_equal')) "resulting_condition": "t2.a = t1.a and t3.a = t1.a and t1.a + t1.a < 1000" } ] +# The next query is test for: +# 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.b > 5555", + "steps": + [ + + { + "build_equal_items": + { + "condition": "ON expr", + "attached_to": "t3", + "resulting_condition": "t3.a + t2.a < 1000 and multiple equal(t2.a, t1.a, t3.a)" + } + }, + + { + "transformation": "equality_propagation", + "resulting_condition": "t1.b > 5555" + }, + + { + "transformation": "constant_propagation", + "resulting_condition": "t1.b > 5555" + }, + + { + "transformation": "trivial_condition_removal", + "resulting_condition": "t1.b > 5555" + } + ] + } +] 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 c4166774ab1..b351699c7a6 100644 --- a/mysql-test/main/opt_trace.test +++ b/mysql-test/main/opt_trace.test @@ -773,11 +773,19 @@ 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; +--echo # The next query is test for: +--echo # MDEV-23646: Optimizer trace: optimize_cond() should show ON expression processing +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/sql/sql_select.cc b/sql/sql_select.cc index 05dc145f5cb..93f5d3591ed 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -15488,6 +15488,16 @@ static COND *build_equal_items(JOIN *join, COND *cond, table->on_expr= build_equal_items(join, table->on_expr, inherited, nested_join_list, ignore_on_conds, &table->cond_equal); + if (unlikely(join->thd->trace_started())) + { + const char *table_name; + if (table->nested_join) + table_name= table->nested_join->join_list.head()->alias.str; + else + table_name= table->alias.str; + trace_condition(join->thd, "ON expr", "build_equal_items", + table->on_expr, table_name); + } } } }

1 0