- commits - lists.mariadb.org

[Commits] ed221980fcd: MDEV-25305: MyRocks: Killing server during RESET MASTER can lose last transactions
by psergey 30 Mar '21

30 Mar '21

revision-id: ed221980fcd391dd349d80ea5b90b84a62ce043c (mariadb-10.5.2-536-ged221980fcd) parent(s): 75db05c5992e5a13ab0836120f46e241fc6de618 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-03-30 16:17:10 +0300 message: MDEV-25305: MyRocks: Killing server during RESET MASTER can lose last transactions rocksdb_checkpoint_request() should call FlushWAL(sync=true) (which does write-out and sync), not just SyncWAL() (which just syncs without writing out) --- sql/sql_repl.cc | 2 ++ storage/rocksdb/ha_rocksdb.cc | 2 +- .../rocksdb/r/binlog_rotate_crash.result | 19 ++++++++++++++ .../mysql-test/rocksdb/t/binlog_rotate_crash.test | 30 ++++++++++++++++++++++ 4 files changed, 52 insertions(+), 1 deletion(-) diff --git a/sql/sql_repl.cc b/sql/sql_repl.cc index 084259d863d..ce270351dc3 100644 --- a/sql/sql_repl.cc +++ b/sql/sql_repl.cc @@ -3966,6 +3966,8 @@ int reset_master(THD* thd, rpl_gtid *init_state, uint32 init_state_len, ret= mysql_bin_log.reset_logs(thd, 1, init_state, init_state_len, next_log_number); repl_semisync_master.after_reset_master(); + DBUG_EXECUTE_IF("crash_after_reset_master", DBUG_SUICIDE();); + return ret; } diff --git a/storage/rocksdb/ha_rocksdb.cc b/storage/rocksdb/ha_rocksdb.cc index 289a1ac07b9..43d9e60aa46 100644 --- a/storage/rocksdb/ha_rocksdb.cc +++ b/storage/rocksdb/ha_rocksdb.cc @@ -4113,7 +4113,7 @@ static int rocksdb_recover(handlerton* hton, XID* xid_list, uint len) static void rocksdb_checkpoint_request(void *cookie) { - const rocksdb::Status s= rdb->SyncWAL(); + const rocksdb::Status s= rdb->FlushWAL(true); //TODO: what to do on error? if (s.ok()) { diff --git a/storage/rocksdb/mysql-test/rocksdb/r/binlog_rotate_crash.result b/storage/rocksdb/mysql-test/rocksdb/r/binlog_rotate_crash.result new file mode 100644 index 00000000000..1b22141813a --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/r/binlog_rotate_crash.result @@ -0,0 +1,19 @@ +# +# MDEV-25305: MyRocks: Killing server during RESET MASTER can lose last transactions +# +set global rocksdb_flush_log_at_trx_commit=1; +create table t1 (a int, b int, key(a)) engine=rocksdb; +insert into t1 values (1,1),(2,2); +select * from t1; +a b +1 1 +2 2 +flush tables; +set @@debug_dbug="+d,crash_after_reset_master"; +RESET MASTER; +# Must show the inserted rows: +select * from t1; +a b +1 1 +2 2 +drop table t1; diff --git a/storage/rocksdb/mysql-test/rocksdb/t/binlog_rotate_crash.test b/storage/rocksdb/mysql-test/rocksdb/t/binlog_rotate_crash.test new file mode 100644 index 00000000000..d2261f508f8 --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/t/binlog_rotate_crash.test @@ -0,0 +1,30 @@ +--source include/have_rocksdb.inc +--source include/have_log_bin.inc + +--echo # +--echo # MDEV-25305: MyRocks: Killing server during RESET MASTER can lose last transactions +--echo # + +set global rocksdb_flush_log_at_trx_commit=1; + +create table t1 (a int, b int, key(a)) engine=rocksdb; +insert into t1 values (1,1),(2,2); +select * from t1; +flush tables; + +--write_file $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +restart +EOF + +set @@debug_dbug="+d,crash_after_reset_master"; + +--disable_reconnect +--error 0,2013 +RESET MASTER; +--enable_reconnect +--source include/wait_until_connected_again.inc +--echo # Must show the inserted rows: +select * from t1; + +drop table t1; +

1 0

[Commits] 0133e374b60: MDEV-24325: Optimizer trace doesn't cover LATERAL DERIVED
by psergey 28 Mar '21

28 Mar '21

revision-id: 0133e374b60404b8e71a673de980db23daba5a91 (mariadb-10.5.2-515-g0133e374b60) parent(s): e538cb095f6290c40e8928e3813db6ac679740a2 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-03-29 00:33:27 +0300 message: MDEV-24325: Optimizer trace doesn't cover LATERAL DERIVED Provide basic coverage in the Optimizer Trace --- mysql-test/main/opt_trace.result | 116 +++++++++++++++++++++++++++++++++++++++ mysql-test/main/opt_trace.test | 49 +++++++++++++++++ sql/opt_split.cc | 17 ++++++ sql/sql_select.cc | 2 +- 4 files changed, 183 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index 4d00e32c4a7..f9560b46e3e 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -9070,4 +9070,120 @@ json_detailed(json_extract(trace, '$**.condition_processing')) } ] drop table t1,t2,t3; +# +# MDEV-24325: Optimizer trace doesn't cover LATERAL DERIVED +# +create table t1 (a int, b int, index idx_b(b)) engine=myisam; +insert into t1 values +(8,3), (5,7), (1,2), (2,1), (9,7), (7,5), (2,2), (7,3), +(9,3), (8,1), (4,5), (2,3); +create table t2 (a int, b int, c char(127), index idx_a(a)) engine=myisam; +insert into t2 values +(7,10,'x'), (1,20,'a'), (2,23,'b'), (7,18,'z'), (1,30,'c'), +(4,71,'d'), (3,15,'x'), (7,82,'y'), (8,12,'t'), (4,15,'b'), +(11,33,'a'), (10,42,'u'), (4,53,'p'), (10,17,'r'), (2,90,'x'), +(17,10,'s'), (11,20,'v'), (12,23,'y'), (17,18,'a'), (11,30,'d'), +(24,71,'h'), (23,15,'i'), (27,82,'k'), (28,12,'p'), (24,15,'q'), +(31,33,'f'), (30,42,'h'), (40,53,'m'), (30,17,'o'), (21,90,'b'), +(37,10,'e'), (31,20,'g'), (32,23,'f'), (37,18,'n'), (41,30,'l'), +(54,71,'j'), (53,15,'w'), (57,82,'z'), (58,12,'k'), (54,15,'p'), +(61,33,'c'), (60,42,'a'), (62,53,'x'), (67,17,'g'), (64,90,'v'); +insert into t2 select a+10, b+10, concat(c,'f') from t2; +analyze table t1,t2; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK +explain +select t1.a,t.s,t.m +from t1 join +(select a, sum(t2.b) as s, min(t2.c) as m from t2 group by t2.a) t +on t1.a=t.a +where t1.b < 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 range idx_b idx_b 5 NULL 4 Using index condition; Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 2 +2 LATERAL DERIVED t2 ref idx_a idx_a 5 test.t1.a 1 +select +json_detailed(json_extract(trace, '$**.choose_best_splitting')) +from +information_schema.optimizer_trace; +json_detailed(json_extract(trace, '$**.choose_best_splitting')) +[ + + [ + + { + "considered_execution_plans": + [ + + { + "plan_prefix": + [ + ], + "table": "t2", + "best_access_path": + { + "considered_access_paths": + [ + + { + "access_type": "ref", + "index": "idx_a", + "used_range_estimates": false, + "cause": "not available", + "rows": 1.8367, + "cost": 2.000585794, + "chosen": true + }, + + { + "type": "scan", + "chosen": false, + "cause": "cost" + } + ], + "chosen_access_method": + { + "type": "ref", + "records": 1.8367, + "cost": 2.000585794, + "uses_join_buffering": false + } + }, + "rows_for_plan": 1.8367, + "cost_for_plan": 2.367925794, + "cost_for_sorting": 1.8367, + "estimated_join_cardinality": 1.8367 + } + ] + }, + + { + "best_splitting": + { + "table": "t2", + "key": "idx_a", + "record_count": 4, + "cost": 2.488945919, + "unsplit_cost": 25.72361682 + } + } + ] +] +select +json_detailed(json_extract(trace, '$**.lateral_derived')) +from +information_schema.optimizer_trace; +json_detailed(json_extract(trace, '$**.lateral_derived')) +[ + + { + "startup_cost": 9.955783677, + "splitting_cost": 2.488945919, + "records": 1 + } +] +drop table t1,t2; set optimizer_trace='enabled=off'; diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test index b351699c7a6..13be2d6f604 100644 --- a/mysql-test/main/opt_trace.test +++ b/mysql-test/main/opt_trace.test @@ -788,4 +788,53 @@ from drop table t1,t2,t3; + +--echo # +--echo # MDEV-24325: Optimizer trace doesn't cover LATERAL DERIVED +--echo # +create table t1 (a int, b int, index idx_b(b)) engine=myisam; +insert into t1 values +(8,3), (5,7), (1,2), (2,1), (9,7), (7,5), (2,2), (7,3), +(9,3), (8,1), (4,5), (2,3); + +create table t2 (a int, b int, c char(127), index idx_a(a)) engine=myisam; +insert into t2 values + (7,10,'x'), (1,20,'a'), (2,23,'b'), (7,18,'z'), (1,30,'c'), + (4,71,'d'), (3,15,'x'), (7,82,'y'), (8,12,'t'), (4,15,'b'), + (11,33,'a'), (10,42,'u'), (4,53,'p'), (10,17,'r'), (2,90,'x'), + (17,10,'s'), (11,20,'v'), (12,23,'y'), (17,18,'a'), (11,30,'d'), + (24,71,'h'), (23,15,'i'), (27,82,'k'), (28,12,'p'), (24,15,'q'), + (31,33,'f'), (30,42,'h'), (40,53,'m'), (30,17,'o'), (21,90,'b'), + (37,10,'e'), (31,20,'g'), (32,23,'f'), (37,18,'n'), (41,30,'l'), + (54,71,'j'), (53,15,'w'), (57,82,'z'), (58,12,'k'), (54,15,'p'), + (61,33,'c'), (60,42,'a'), (62,53,'x'), (67,17,'g'), (64,90,'v'); + +insert into t2 select a+10, b+10, concat(c,'f') from t2; + +analyze table t1,t2; + +explain +select t1.a,t.s,t.m +from t1 join + (select a, sum(t2.b) as s, min(t2.c) as m from t2 group by t2.a) t + on t1.a=t.a +where t1.b < 3; + +# +# Just show that choose_best_splitting function has coverage in the +# optimizer trace and re-optmization of child select inside it is distinct +# from the rest of join optimization. +select + json_detailed(json_extract(trace, '$**.choose_best_splitting')) +from + information_schema.optimizer_trace; + +# Same as above. just to show that splitting plan has some coverage in the +# trace. +select + json_detailed(json_extract(trace, '$**.lateral_derived')) +from + information_schema.optimizer_trace; + +drop table t1,t2; set optimizer_trace='enabled=off'; diff --git a/sql/opt_split.cc b/sql/opt_split.cc index 2aa65bdf03b..45053053002 100644 --- a/sql/opt_split.cc +++ b/sql/opt_split.cc @@ -187,6 +187,7 @@ #include "mariadb.h" #include "sql_select.h" +#include "opt_trace.h" /* Info on a splitting field */ struct SplM_field_info @@ -957,6 +958,7 @@ SplM_plan_info * JOIN_TAB::choose_best_splitting(double record_count, The key for splitting was chosen, look for the plan for this key in the cache */ + Json_writer_array spl_trace(thd, "choose_best_splitting"); spl_plan= spl_opt_info->find_plan(best_table, best_key, best_key_parts); if (!spl_plan && (spl_plan= (SplM_plan_info *) thd->alloc(sizeof(SplM_plan_info))) && @@ -988,6 +990,16 @@ SplM_plan_info * JOIN_TAB::choose_best_splitting(double record_count, spl_plan->cost= join->best_positions[join->table_count-1].read_time + + oper_cost; + if (unlikely(thd->trace_started())) + { + Json_writer_object wrapper(thd); + Json_writer_object find_trace(thd, "best_splitting"); + find_trace.add("table", best_table->alias.c_ptr()); + find_trace.add("key", best_table->key_info[best_key].name); + find_trace.add("record_count", record_count); + find_trace.add("cost", spl_plan->cost); + find_trace.add("unsplit_cost", spl_opt_info->unsplit_cost); + } memcpy((char *) spl_plan->best_positions, (char *) join->best_positions, sizeof(POSITION) * join->table_count); @@ -1014,6 +1026,11 @@ SplM_plan_info * JOIN_TAB::choose_best_splitting(double record_count, { startup_cost= record_count * spl_plan->cost; records= (ha_rows) (records * spl_plan->split_sel); + + Json_writer_object trace(thd, "lateral_derived"); + trace.add("startup_cost", startup_cost); + trace.add("splitting_cost", spl_plan->cost); + trace.add("records", records); } else startup_cost= spl_opt_info->unsplit_cost; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 794bc4d69ae..34d7958f20e 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -7441,7 +7441,6 @@ best_access_path(JOIN *join, DBUG_ENTER("best_access_path"); Json_writer_object trace_wrapper(thd, "best_access_path"); - Json_writer_array trace_paths(thd, "considered_access_paths"); bitmap_clear_all(eq_join_set); @@ -7449,6 +7448,7 @@ best_access_path(JOIN *join, if (s->table->is_splittable()) spl_plan= s->choose_best_splitting(record_count, remaining_tables); + Json_writer_array trace_paths(thd, "considered_access_paths"); if (s->keyuse) { /* Use key if possible */

1 0

[Commits] 1b35ebf9cb6: Update tests after rebasing over 8.0.20, part #2
by psergey 25 Mar '21

25 Mar '21

revision-id: 1b35ebf9cb6e2200fed25f1a3617253d649a4c5c () parent(s): b97920e3918b5163fb15c09a366109bbe120bb1c author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-03-25 20:39:59 +0300 message: Update tests after rebasing over 8.0.20, part #2 --- mysql-test/suite/rocksdb/t/db_max_index_num.test | 5 +++++ .../rocksdb/t/range_locking_deadlock_tracking.test | 18 +++++++++--------- 2 files changed, 14 insertions(+), 9 deletions(-) diff --git a/mysql-test/suite/rocksdb/t/db_max_index_num.test b/mysql-test/suite/rocksdb/t/db_max_index_num.test index 1adf7d2c644..10b5a7a4033 100644 --- a/mysql-test/suite/rocksdb/t/db_max_index_num.test +++ b/mysql-test/suite/rocksdb/t/db_max_index_num.test @@ -2,6 +2,11 @@ --source include/have_debug_sync.inc --source include/have_rocksdb.inc +# Does SELECT...FOR UPDATE; SELECT ... FROM I_S.ROCKSDB_LOCKS which produces +# different output in range locking mode. +--source suite/rocksdb/include/not_range_locking.inc + + --echo ## Test creating dropping database of same name while ## --echo ## keeping drop index thread suspended ## diff --git a/mysql-test/suite/rocksdb/t/range_locking_deadlock_tracking.test b/mysql-test/suite/rocksdb/t/range_locking_deadlock_tracking.test index 2a5966b65c3..57fb4da340a 100644 --- a/mysql-test/suite/rocksdb/t/range_locking_deadlock_tracking.test +++ b/mysql-test/suite/rocksdb/t/range_locking_deadlock_tracking.test @@ -29,29 +29,29 @@ let $con3= `SELECT CONNECTION_ID()`; connection default; eval create table t (i int primary key) engine=$engine; insert into t values (1), (2), (3); ---replace_regex /[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}/TIMESTAMP/ /WAITING KEY: [0-9a-f]{16}/KEY/ /TRANSACTION ID: [0-9]*/TXN_ID/ /TIMESTAMP: [0-9]*/TSTAMP/ +--replace_regex /[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}/TIMESTAMP/ /WAITING KEY: [0-9a-f]{24}/KEY/ /TRANSACTION ID: [0-9]*/TXN_ID/ /TIMESTAMP: [0-9]*/TSTAMP/ show engine rocksdb transaction status; echo Deadlock #1; --source include/simple_deadlock.inc connection default; ---replace_regex /[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}/TIMESTAMP/ /WAITING KEY: [0-9a-f]{16}/KEY/ /TRANSACTION ID: [0-9]*/TXN_ID/ /TIMESTAMP: [0-9]*/TSTAMP/ +--replace_regex /[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}/TIMESTAMP/ /WAITING KEY: [0-9a-f]{24}/KEY/ /TRANSACTION ID: [0-9]*/TXN_ID/ /TIMESTAMP: [0-9]*/TSTAMP/ show engine rocksdb transaction status; echo Deadlock #2; --source include/simple_deadlock.inc connection default; ---replace_regex /[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}/TIMESTAMP/ /WAITING KEY: [0-9a-f]{16}/KEY/ /TRANSACTION ID: [0-9]*/TXN_ID/ /TIMESTAMP: [0-9]*/TSTAMP/ +--replace_regex /[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}/TIMESTAMP/ /WAITING KEY: [0-9a-f]{24}/KEY/ /TRANSACTION ID: [0-9]*/TXN_ID/ /TIMESTAMP: [0-9]*/TSTAMP/ show engine rocksdb transaction status; set global rocksdb_max_latest_deadlocks = 10; echo Deadlock #3; --source include/simple_deadlock.inc connection default; ---replace_regex /[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}/TIMESTAMP/ /WAITING KEY: [0-9a-f]{16}/KEY/ /TRANSACTION ID: [0-9]*/TXN_ID/ /TIMESTAMP: [0-9]*/TSTAMP/ +--replace_regex /[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}/TIMESTAMP/ /WAITING KEY: [0-9a-f]{24}/KEY/ /TRANSACTION ID: [0-9]*/TXN_ID/ /TIMESTAMP: [0-9]*/TSTAMP/ show engine rocksdb transaction status; set global rocksdb_max_latest_deadlocks = 1; ---replace_regex /[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}/TIMESTAMP/ /WAITING KEY: [0-9a-f]{16}/KEY/ /TRANSACTION ID: [0-9]*/TXN_ID/ /TIMESTAMP: [0-9]*/TSTAMP/ +--replace_regex /[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}/TIMESTAMP/ /WAITING KEY: [0-9a-f]{24}/KEY/ /TRANSACTION ID: [0-9]*/TXN_ID/ /TIMESTAMP: [0-9]*/TSTAMP/ show engine rocksdb transaction status; connection con3; @@ -103,7 +103,7 @@ rollback; connection default; set global rocksdb_max_latest_deadlocks = 5; ---replace_regex /[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}/TIMESTAMP/ /WAITING KEY: [0-9a-f]{16}/KEY/ /TRANSACTION ID: [0-9]*/TXN_ID/ /TIMESTAMP: [0-9]*/TSTAMP/ +--replace_regex /[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}/TIMESTAMP/ /WAITING KEY: [0-9a-f]{24}/KEY/ /TRANSACTION ID: [0-9]*/TXN_ID/ /TIMESTAMP: [0-9]*/TSTAMP/ show engine rocksdb transaction status; --disable_testcase BUG#0000 @@ -146,7 +146,7 @@ connection con3; rollback; connection default; ---replace_regex /[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}/TIMESTAMP/ /WAITING KEY: [0-9a-f]{16}/KEY/ /TRANSACTION ID: [0-9]*/TXN_ID/ /TIMESTAMP: [0-9]*/TSTAMP/ +--replace_regex /[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}/TIMESTAMP/ /WAITING KEY: [0-9a-f]{24}/KEY/ /TRANSACTION ID: [0-9]*/TXN_ID/ /TIMESTAMP: [0-9]*/TSTAMP/ show engine rocksdb transaction status; --enable_testcase echo Deadlock #6; @@ -186,11 +186,11 @@ disconnect con3; set global rocksdb_lock_wait_timeout = @prior_lock_wait_timeout; set global rocksdb_deadlock_detect = @prior_deadlock_detect; drop table t; ---replace_regex /[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}/TIMESTAMP/ /WAITING KEY: [0-9a-f]{16}/KEY/ /TRANSACTION ID: [0-9]*/TXN_ID/ /INDEX_ID: [0-9a-f]*/IDX_ID/ /TIMESTAMP: [0-9]*/TSTAMP/ +--replace_regex /[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}/TIMESTAMP/ /WAITING KEY: [0-9a-f]{24}/KEY/ /TRANSACTION ID: [0-9]*/TXN_ID/ /INDEX_ID: [0-9a-f]*/IDX_ID/ /TIMESTAMP: [0-9]*/TSTAMP/ show engine rocksdb transaction status; set global rocksdb_max_latest_deadlocks = 0; --echo # Clears deadlock buffer of any existent deadlocks. set global rocksdb_max_latest_deadlocks = @prior_max_latest_deadlocks; ---replace_regex /[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}/TIMESTAMP/ /WAITING KEY: [0-9a-f]{16}/KEY/ /TRANSACTION ID: [0-9]*/TXN_ID/ /INDEX_ID: [0-9a-f]*/IDX_ID/ /TIMESTAMP: [0-9]*/TSTAMP/ +--replace_regex /[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}/TIMESTAMP/ /WAITING KEY: [0-9a-f]{24}/KEY/ /TRANSACTION ID: [0-9]*/TXN_ID/ /INDEX_ID: [0-9a-f]*/IDX_ID/ /TIMESTAMP: [0-9]*/TSTAMP/ show engine rocksdb transaction status; --source include/wait_until_count_sessions.inc

1 0

[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