- commits - lists.mariadb.org

[Commits] 15f97e3fd92: MDEV-19634: Assertion `0' failed in row_sel_convert_mysql_key_to_innobase, [Warning] InnoDB: Using a partial-field key prefix in search
by Varun 03 Jun '19

03 Jun '19

revision-id: 15f97e3fd922933987e78ef3c2b6490f0edf8172 (mariadb-10.4.4-141-g15f97e3fd92) parent(s): 92df31dfbfcf6068f4f4a7e7794a15333158c569 author: Varun Gupta committer: Varun Gupta timestamp: 2019-06-03 11:22:39 +0530 message: MDEV-19634: Assertion `0' failed in row_sel_convert_mysql_key_to_innobase, [Warning] InnoDB: Using a partial-field key prefix in search For a key with keyparts (k1,k2,k3) , if we are building a range over the keyparts we should make sure that if min_value/max_value for a keypart is not added to key buffer then the keyparts following should also not be allowed. --- mysql-test/main/range_innodb.result | 19 +++++++++++++++++++ mysql-test/main/range_innodb.test | 19 +++++++++++++++++++ sql/opt_range.h | 10 ++++++---- 3 files changed, 44 insertions(+), 4 deletions(-) diff --git a/mysql-test/main/range_innodb.result b/mysql-test/main/range_innodb.result index 30161a2711d..7d8c6e5b475 100644 --- a/mysql-test/main/range_innodb.result +++ b/mysql-test/main/range_innodb.result @@ -80,3 +80,22 @@ ERROR HY000: Table definition has changed, please retry transaction DROP TABLE t0,t1; set @@global.debug_dbug="-d"; set @@optimizer_switch= @optimizer_switch_save; +# +# MDEV-19634: Assertion `0' failed in row_sel_convert_mysql_key_to_innobase, +# [Warning] InnoDB: Using a partial-field key prefix in search +# +CREATE TABLE t1 ( +pk INT, +a VARCHAR(1), +b INT, +PRIMARY KEY (pk), +KEY (a,b) +) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1,'a',1),(2,'b',2); +explain +SELECT a FROM t1 WHERE pk < 0 AND a <= 'w' and b > 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index PRIMARY,a a 9 NULL 2 Using where; Using index +SELECT a FROM t1 WHERE pk < 0 AND a <= 'w' and b > 0; +a +drop table t1; diff --git a/mysql-test/main/range_innodb.test b/mysql-test/main/range_innodb.test index a17ef3f1146..2c225df27fe 100644 --- a/mysql-test/main/range_innodb.test +++ b/mysql-test/main/range_innodb.test @@ -87,3 +87,22 @@ select * from t1 where a=10 and b=10; DROP TABLE t0,t1; set @@global.debug_dbug="-d"; set @@optimizer_switch= @optimizer_switch_save; + +--echo # +--echo # MDEV-19634: Assertion `0' failed in row_sel_convert_mysql_key_to_innobase, +--echo # [Warning] InnoDB: Using a partial-field key prefix in search +--echo # + +CREATE TABLE t1 ( + pk INT, + a VARCHAR(1), + b INT, + PRIMARY KEY (pk), + KEY (a,b) +) ENGINE=InnoDB; + +INSERT INTO t1 VALUES (1,'a',1),(2,'b',2); + +explain SELECT a FROM t1 WHERE pk < 0 AND a <= 'w' and b > 0; +SELECT a FROM t1 WHERE pk < 0 AND a <= 'w' and b > 0; +drop table t1; diff --git a/sql/opt_range.h b/sql/opt_range.h index ae0e3822272..73def7bde92 100644 --- a/sql/opt_range.h +++ b/sql/opt_range.h @@ -459,8 +459,9 @@ class SEL_ARG :public Sql_alloc uint res= key_tree->store_min(key[key_tree->part].store_length, range_key, *range_key_flag); // add flags only if a key_part is written to the buffer - if (res) - *range_key_flag|= key_tree->min_flag; + if (!res) + return 0; + *range_key_flag|= key_tree->min_flag; if (key_tree->next_key_part && key_tree->next_key_part->type == SEL_ARG::KEY_RANGE && key_tree->part != last_part && @@ -482,8 +483,9 @@ class SEL_ARG :public Sql_alloc SEL_ARG *key_tree= last(); uint res=key_tree->store_max(key[key_tree->part].store_length, range_key, *range_key_flag); - if (res) - (*range_key_flag)|= key_tree->max_flag; + if (!res) + return 0; + *range_key_flag|= key_tree->max_flag; if (key_tree->next_key_part && key_tree->next_key_part->type == SEL_ARG::KEY_RANGE && key_tree->part != last_part &&

1 0

[Commits] 32818f5e57d: MDEV-18791 Wrong error upon creating Aria table with long index on BLOB
by sachin.setiya＠mariadb.com 02 Jun '19

02 Jun '19

revision-id: 32818f5e57ddb262dff8b2e72ed612748d29d3d0 (mariadb-10.4.5-25-g32818f5e57d) parent(s): 8b545d3d41416772203cb98f06243873a4f0d8f7 author: Sachin committer: Sachin timestamp: 2019-06-02 13:05:54 +0530 message: MDEV-18791 Wrong error upon creating Aria table with long index on BLOB Create a Create_table_error_handler to mask ER_KEY_BASED_ON_GENERATED_VIRTUAL_COLUMN error. --- mysql-test/main/long_unique_bugs.result | 2 ++ mysql-test/main/long_unique_bugs.test | 6 ++++++ sql/handler.cc | 9 +++++++++ sql/share/errmsg-utf8.txt | 2 ++ sql/sql_class.cc | 25 +++++++++++++++++++++++++ sql/sql_class.h | 24 ++++++++++++++++++++++++ 6 files changed, 68 insertions(+) diff --git a/mysql-test/main/long_unique_bugs.result b/mysql-test/main/long_unique_bugs.result index 33496c4e20d..4a65cc69ced 100644 --- a/mysql-test/main/long_unique_bugs.result +++ b/mysql-test/main/long_unique_bugs.result @@ -265,3 +265,5 @@ ERROR 40001: Deadlock found when trying to get lock; try restarting transaction disconnect con1; connection default; DROP TABLE t1, t2; +CREATE TABLE t1 (a TEXT, UNIQUE(a)) ENGINE=Aria; +ERROR HY000: Aria Storage engine does not support long unique keys diff --git a/mysql-test/main/long_unique_bugs.test b/mysql-test/main/long_unique_bugs.test index dc78f6c7067..ce76211184d 100644 --- a/mysql-test/main/long_unique_bugs.test +++ b/mysql-test/main/long_unique_bugs.test @@ -317,3 +317,9 @@ INSERT IGNORE INTO t1 VALUES (4, 1)/*4*/; --disconnect con1 --connection default DROP TABLE t1, t2; + +# +# MDEV-18791 Wrong error upon creating Aria table with long index on BLOB +# +--error ER_NO_LONG_UNIQUE_ENGINE_SUPPORT +CREATE TABLE t1 (a TEXT, UNIQUE(a)) ENGINE=Aria; diff --git a/sql/handler.cc b/sql/handler.cc index 124f5c8e9ce..b5303fbf67e 100644 --- a/sql/handler.cc +++ b/sql/handler.cc @@ -5135,6 +5135,7 @@ int ha_create_table(THD *thd, const char *path, TABLE_SHARE share; bool temp_table __attribute__((unused)) = create_info->options & (HA_LEX_CREATE_TMP_TABLE | HA_CREATE_TMP_ALTER); + Create_table_error_handler err_handler; DBUG_ENTER("ha_create_table"); init_tmp_table_share(thd, &share, db, 0, table_name, path); @@ -5170,12 +5171,20 @@ int ha_create_table(THD *thd, const char *path, name= get_canonical_filename(table.file, share.path.str, name_buff); + thd->push_internal_handler(&err_handler); error= table.file->ha_create(name, &table, create_info); + thd->pop_internal_handler(); if (unlikely(error)) { if (!thd->is_error()) + { + if (err_handler.safely_trapped_errors()) + { + my_error(ER_NO_LONG_UNIQUE_ENGINE_SUPPORT, MYF(0), hton_name(table.file->ht)->str); + } my_error(ER_CANT_CREATE_TABLE, MYF(0), db, table_name, error); + } table.file->print_error(error, MYF(ME_WARNING)); PSI_CALL_drop_table_share(temp_table, share.db.str, (uint)share.db.length, share.table_name.str, (uint)share.table_name.length); diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt index c64f60f3562..3fb53ad55e6 100644 --- a/sql/share/errmsg-utf8.txt +++ b/sql/share/errmsg-utf8.txt @@ -7931,3 +7931,5 @@ ER_PERIOD_CONSTRAINT_DROP eng "Can't DROP CONSTRAINT `%s`. Use DROP PERIOD `%s` for this" ER_TOO_LONG_KEYPART 42000 S1009 eng "Specified key part was too long; max key part length is %u bytes" +ER_NO_LONG_UNIQUE_ENGINE_SUPPORT + eng "%s Storage engine does not support long unique keys" diff --git a/sql/sql_class.cc b/sql/sql_class.cc index 94e2b518fa4..4cd3c49cd41 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -563,6 +563,31 @@ bool Drop_table_error_handler::handle_condition(THD *thd, sql_errno == ER_TRG_NO_DEFINER); } +/** + Implementation of Create_table_error_handler::handle_condition(). + The reason in having this implementation is to silence technical low-level + error during CREATE TABLE operation. Currently we don't want to expose + the following warnings during DROP TABLE: + - When using long unique on ARIA storage engine show long unique not + avaliable in ARIA instead of ER_KEY_BASED_ON_GENERATED_VIRTUAL_COLUMN + @return TRUE if the condition is handled. +*/ +bool Create_table_error_handler::handle_condition(THD *thd, + uint sql_errno, + const char* sqlstate, + Sql_condition::enum_warning_level *level, + const char* msg, + Sql_condition ** cond_hdl) +{ + *cond_hdl= NULL; + if (sql_errno == ER_KEY_BASED_ON_GENERATED_VIRTUAL_COLUMN) + { + m_handled_error++; + return true; + } + return false; +} + /** Handle an error from MDL_context::upgrade_lock() and mysql_lock_tables(). diff --git a/sql/sql_class.h b/sql/sql_class.h index dd9cfbbd1c4..c5c3058cc06 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -1808,6 +1808,30 @@ class Drop_table_error_handler : public Internal_error_handler private: }; +/** + This class is an internal error handler implementation for + CREATE TABLE statements. The thing is that there may be error during + execution of these statements, which should not be exposed to the user. + This class is intended to silence such error. +*/ + +class Create_table_error_handler : public Internal_error_handler +{ +public: + Create_table_error_handler(): m_handled_error(0) {} + +public: + bool handle_condition(THD *thd, + uint sql_errno, + const char* sqlstate, + Sql_condition::enum_warning_level *level, + const char* msg, + Sql_condition ** cond_hdl); + bool safely_trapped_errors(){return m_handled_error > 0;} +private: + int m_handled_error; +}; + /** Internal error handler to process an error from MDL_context::upgrade_lock()

1 0

[Commits] a602998: MDEV-19580 Unrelated JOINs corrupt usage of 'WHERE function() IN (subquery)'
by IgorBabaev 01 Jun '19

01 Jun '19

revision-id: a6029989cc289f80eed5da707815838c6848d24b (mariadb-5.5.64-13-ga602998) parent(s): cbb90f77cdbf57c02145dc6cd86acf8ebb8a88f0 author: Igor Babaev committer: Igor Babaev timestamp: 2019-06-01 13:07:09 -0700 message: MDEV-19580 Unrelated JOINs corrupt usage of 'WHERE function() IN (subquery)' Handling of top level conjuncts in WHERE whose used_tables() contained RAND_TABLE_BIT in the function make_join_select() was incorrect. As a result if such a conjunct referred to fields non of which belonged to the last joined table it was pushed twice. (This could be seen for a test case from subselect.test whose output was changed after this patch had been applied. In 10.1 when running EXPLAIN FORMAT=JSON for the query from this test case we clearly see that one of the conjuncts is pushed twice.) This fact by itself was not good. Besides, if such a conjunct was pushed to a table that was the result of materialization of a semi-join the query could return a wrong result set. In particular we could watch it for queries with semi-join subqueries whose left parts used stored functions without "deterministic' specifier. --- mysql-test/r/subselect.result | 2 +- mysql-test/r/subselect_mat.result | 68 ++++++++++++++++++++++++ mysql-test/r/subselect_no_mat.result | 2 +- mysql-test/r/subselect_no_opts.result | 2 +- mysql-test/r/subselect_no_scache.result | 2 +- mysql-test/r/subselect_no_semijoin.result | 2 +- mysql-test/r/subselect_sj_mat.result | 68 ++++++++++++++++++++++++ mysql-test/t/subselect_sj_mat.test | 66 ++++++++++++++++++++++++ sql/sql_select.cc | 86 ++++++++++++++++++++++++++----- 9 files changed, 279 insertions(+), 19 deletions(-) diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index cdedc02..2999f4c 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -6939,7 +6939,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where -1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index +1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away set @tmp_mdev410=@@global.userstat; set global userstat=on; diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index efc348a..ae24485 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -2453,6 +2453,74 @@ SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); 1 drop table t1,t2; drop view v1; +# +# MDEV-19580: function invocation in the left part of IN subquery +# +create table t1 (id int, a varchar(50), b int); +insert into t1 values +(1,'mrs',2), (2,'joe',2), (3,'paul',1), (4,'art',1); +create table t2 (id int, a varchar(50), x int); +insert into t2 values +(1,'grand',1),(2,'average',1),(3,'serf',0); +create table t3 (d1 date, d2 date, t1_id int, t2_id int ); +insert into t3 values +('1972-01-01','1988-12-31',3,1), ('1972-01-01','1988-12-31',4,1), +('1972-01-01','1988-12-31',1,2), ('1972-01-01','1988-12-31',2,3); +create table t4 ( id int, a varchar(50) ); +insert into t4 values +(1,'songwriter'),(2,'song character'); +create function f1(who int, dt date) returns int +deterministic +begin +declare result int; +select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; +return result; +end$$ +create function f2(who int, dt date) returns int +begin +declare result int; +select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; +return result; +end$$ +# Deterministic function in left part of IN subquery: semi-join is OK +select * from t1 +left join t4 on t1.b = t4.id +where f1(t1.id, '1980-01-01') in (select id from t2 where x=1); +id a b id a +3 paul 1 1 songwriter +4 art 1 1 songwriter +1 mrs 2 2 song character +explain extended select * from t1 +left join t4 on t1.b = t4.id +where f1(t1.id, '1980-01-01') in (select id from t2 where x=1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f1`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`)) +# Non-deterministic function in left part of IN subq: semi-join is OK +select * from t1 +left join t4 on t1.b = t4.id +where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); +id a b id a +3 paul 1 1 songwriter +4 art 1 1 songwriter +1 mrs 2 2 song character +explain extended select * from t1 +left join t4 on t1.b = t4.id +where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f2`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`)) +drop function f1; +drop function f2; +drop table t1,t2,t3,t4; # End of 5.5 tests set @subselect_mat_test_optimizer_switch_value=null; set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off'; diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index a729129..5c21e74 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -6937,7 +6937,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where -1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index +1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away set @tmp_mdev410=@@global.userstat; set global userstat=on; diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index c41fa1b..8ab1f1b 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -6934,7 +6934,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where -1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index +1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away set @tmp_mdev410=@@global.userstat; set global userstat=on; diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index 1c18135..f0afa7e 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -6945,7 +6945,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where -1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index +1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away set @tmp_mdev410=@@global.userstat; set global userstat=on; diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 89c6712..0d5fbc3 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -6934,7 +6934,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where -1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index +1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away set @tmp_mdev410=@@global.userstat; set global userstat=on; diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index fd9435e..9dde115 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -2493,4 +2493,72 @@ SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); 1 drop table t1,t2; drop view v1; +# +# MDEV-19580: function invocation in the left part of IN subquery +# +create table t1 (id int, a varchar(50), b int); +insert into t1 values +(1,'mrs',2), (2,'joe',2), (3,'paul',1), (4,'art',1); +create table t2 (id int, a varchar(50), x int); +insert into t2 values +(1,'grand',1),(2,'average',1),(3,'serf',0); +create table t3 (d1 date, d2 date, t1_id int, t2_id int ); +insert into t3 values +('1972-01-01','1988-12-31',3,1), ('1972-01-01','1988-12-31',4,1), +('1972-01-01','1988-12-31',1,2), ('1972-01-01','1988-12-31',2,3); +create table t4 ( id int, a varchar(50) ); +insert into t4 values +(1,'songwriter'),(2,'song character'); +create function f1(who int, dt date) returns int +deterministic +begin +declare result int; +select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; +return result; +end$$ +create function f2(who int, dt date) returns int +begin +declare result int; +select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; +return result; +end$$ +# Deterministic function in left part of IN subquery: semi-join is OK +select * from t1 +left join t4 on t1.b = t4.id +where f1(t1.id, '1980-01-01') in (select id from t2 where x=1); +id a b id a +3 paul 1 1 songwriter +4 art 1 1 songwriter +1 mrs 2 2 song character +explain extended select * from t1 +left join t4 on t1.b = t4.id +where f1(t1.id, '1980-01-01') in (select id from t2 where x=1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f1`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`)) +# Non-deterministic function in left part of IN subq: semi-join is OK +select * from t1 +left join t4 on t1.b = t4.id +where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); +id a b id a +3 paul 1 1 songwriter +4 art 1 1 songwriter +1 mrs 2 2 song character +explain extended select * from t1 +left join t4 on t1.b = t4.id +where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f2`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`)) +drop function f1; +drop function f2; +drop table t1,t2,t3,t4; # End of 5.5 tests diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test index c82c1e7..997e71a 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -2230,4 +2230,70 @@ explain SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); drop table t1,t2; drop view v1; + + +--echo # +--echo # MDEV-19580: function invocation in the left part of IN subquery +--echo # + +create table t1 (id int, a varchar(50), b int); +insert into t1 values +(1,'mrs',2), (2,'joe',2), (3,'paul',1), (4,'art',1); + +create table t2 (id int, a varchar(50), x int); +insert into t2 values +(1,'grand',1),(2,'average',1),(3,'serf',0); + +create table t3 (d1 date, d2 date, t1_id int, t2_id int ); +insert into t3 values +('1972-01-01','1988-12-31',3,1), ('1972-01-01','1988-12-31',4,1), +('1972-01-01','1988-12-31',1,2), ('1972-01-01','1988-12-31',2,3); + +create table t4 ( id int, a varchar(50) ); +insert into t4 values +(1,'songwriter'),(2,'song character'); + +delimiter $$; + +create function f1(who int, dt date) returns int +deterministic +begin + declare result int; + select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; + return result; +end$$ + +create function f2(who int, dt date) returns int +begin + declare result int; + select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; + return result; +end$$ + +delimiter ;$$ + +--echo # Deterministic function in left part of IN subquery: semi-join is OK + +let $q1= +select * from t1 + left join t4 on t1.b = t4.id + where f1(t1.id, '1980-01-01') in (select id from t2 where x=1); + +eval $q1; +eval explain extended $q1; + +--echo # Non-deterministic function in left part of IN subq: semi-join is OK + +let $q2= +select * from t1 + left join t4 on t1.b = t4.id + where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); + +eval $q2; +eval explain extended $q2; + +drop function f1; +drop function f2; +drop table t1,t2,t3,t4; + --echo # End of 5.5 tests diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 37f8292..bc69817 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -204,7 +204,8 @@ static COND *make_cond_for_table_from_pred(THD *thd, Item *root_cond, table_map used_table, int join_tab_idx_arg, bool exclude_expensive_cond, - bool retain_ref_cond); + bool retain_ref_cond, + bool is_top_and_level); static Item* part_of_refkey(TABLE *form,Field *field); uint find_shortest_key(TABLE *table, const key_map *usable_keys); @@ -8922,12 +8923,6 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) RAND_TABLE_BIT; } - /* - Following force including random expression in last table condition. - It solve problem with select like SELECT * FROM t1 WHERE rand() > 0.5 - */ - if (tab == join->join_tab + last_top_base_tab_idx) - current_map|= RAND_TABLE_BIT; used_tables|=current_map; if (tab->type == JT_REF && tab->quick && @@ -8968,6 +8963,20 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) { tmp= make_cond_for_table(thd, cond, used_tables, current_map, i, FALSE, FALSE); + if (tab == join->join_tab + last_top_base_tab_idx) + { + /* + This pushes conjunctive conditions of WHERE condition such that: + - their used_tables() contain RAND_TABLE_BIT + - the conditions does not refer to any fields + (such like rand() > 0.5) + */ + table_map rand_table_bit= (table_map) RAND_TABLE_BIT; + COND *rand_cond= make_cond_for_table(thd, cond, used_tables, + rand_table_bit, -1, + FALSE, FALSE); + add_cond_and_fix(thd, &tmp, rand_cond); + } } /* Add conditions added by add_not_null_conds(). */ if (tab->select_cond) @@ -9283,8 +9292,24 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) psergey: have put the -1 below. It's bad, will need to fix it. */ COND *tmp_cond= make_cond_for_table(thd, on_expr, used_tables2, - current_map, /*(tab - first_tab)*/ -1, - FALSE, FALSE); + current_map, + /*(tab - first_tab)*/ -1, + FALSE, FALSE); + if (tab == last_tab) + { + /* + This pushes conjunctive conditions of ON expression of an outer + join such that: + - their used_tables() contain RAND_TABLE_BIT + - the conditions does not refer to any fields + (such like rand() > 0.5) + */ + table_map rand_table_bit= (table_map) RAND_TABLE_BIT; + COND *rand_cond= make_cond_for_table(thd, on_expr, used_tables2, + rand_table_bit, -1, + FALSE, FALSE); + add_cond_and_fix(thd, &tmp_cond, rand_cond); + } bool is_sjm_lookup_tab= FALSE; if (tab->bush_children) { @@ -18824,7 +18849,7 @@ make_cond_for_table(THD *thd, Item *cond, table_map tables, return make_cond_for_table_from_pred(thd, cond, cond, tables, used_table, join_tab_idx_arg, exclude_expensive_cond, - retain_ref_cond); + retain_ref_cond, true); } @@ -18834,9 +18859,12 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond, int join_tab_idx_arg, bool exclude_expensive_cond __attribute__ ((unused)), - bool retain_ref_cond) + bool retain_ref_cond, + bool is_top_and_level) { + table_map rand_table_bit= (table_map) RAND_TABLE_BIT; + if (used_table && !(cond->used_tables() & used_table)) return (COND*) 0; // Already checked @@ -18852,11 +18880,27 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond, Item *item; while ((item=li++)) { + /* + Special handling of top level conjuncts with RAND_TABLE_BIT: + if such a conjunct contains a reference to a field then it is pushed + to the corresponding table by the same rule as all other conjuncts. + Otherwise, if the conjunct is used in WHERE is is pushed to the last + joined table, if is it is used in ON condition of an outer join it + is pushed into the last inner table of the outer join. Such conjuncts + are pushed in a call of make_cond_for_table_from_pred() with the + parameter 'used_table' equal to RAND_TABLE_BIT. + */ + if (is_top_and_level && used_table == rand_table_bit && + item->used_tables() != rand_table_bit) + { + /* The conjunct with RAND_TABLE_BIT has been allready pushed */ + continue; + } Item *fix=make_cond_for_table_from_pred(thd, root_cond, item, tables, used_table, - join_tab_idx_arg, + join_tab_idx_arg, exclude_expensive_cond, - retain_ref_cond); + retain_ref_cond, false); if (fix) new_cond->argument_list()->push_back(fix); } @@ -18880,6 +18924,13 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond, } else { // Or list + if (is_top_and_level && used_table == rand_table_bit && + cond->used_tables() != rand_table_bit) + { + /* This top level formula with RAND_TABLE_BIT has been already pushed */ + return (COND*) 0; + } + Item_cond_or *new_cond=new Item_cond_or; if (!new_cond) return (COND*) 0; // OOM /* purecov: inspected */ @@ -18891,7 +18942,7 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond, tables, 0L, join_tab_idx_arg, exclude_expensive_cond, - retain_ref_cond); + retain_ref_cond, false); if (!fix) return (COND*) 0; // Always true new_cond->argument_list()->push_back(fix); @@ -18908,6 +18959,13 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond, } } + if (is_top_and_level && used_table == rand_table_bit && + cond->used_tables() != rand_table_bit) + { + /* This top level formula with RAND_TABLE_BIT has been already pushed */ + return (COND*) 0; + } + /* Because the following test takes a while and it can be done table_count times, we mark each item that we have examined with the result

1 0

[Commits] eb89a6c: MDEV-19580 Unrelated JOINs corrupt usage of 'WHERE function() IN (subquery)'
by IgorBabaev 01 Jun '19

01 Jun '19

revision-id: eb89a6cd23df80cad091ef6f1d50e0969488de7c (mariadb-5.5.64-13-geb89a6c) parent(s): cbb90f77cdbf57c02145dc6cd86acf8ebb8a88f0 author: Igor Babaev committer: Igor Babaev timestamp: 2019-06-01 13:04:08 -0700 message: MDEV-19580 Unrelated JOINs corrupt usage of 'WHERE function() IN (subquery)' Handling of top level conjuncts in WHERE whose used_tables() contained RAND_TABLE_BIT in the function make_join_select() was incorrect. As a result if such a conjunct referred to fields non of which belonged to the last joined table it was pushed twice. (This could be seen for a test case from subselect.test whose output was changed after this patch had been applied. In 10.1 when running EXPLAIN FORMAT=JSON for the query from this test case we clearly see that one of the conjuncts is pushed twice.) This fact by itself was not good. Besides, if such a conjunct was pushed to a table that was the result of materialization of a semi-join the query could return a wrong result set. In particular we could watch it for queries with semi-join subqueries whose left parts used stored functions without "deterministic' specifier. --- mysql-test/r/subselect.result | 2 +- mysql-test/r/subselect_mat.result | 68 ++++++++++++++++++++++++ mysql-test/r/subselect_no_mat.result | 2 +- mysql-test/r/subselect_no_opts.result | 2 +- mysql-test/r/subselect_no_scache.result | 2 +- mysql-test/r/subselect_no_semijoin.result | 2 +- mysql-test/r/subselect_sj_mat.result | 68 ++++++++++++++++++++++++ mysql-test/t/subselect_sj_mat.test | 66 ++++++++++++++++++++++++ sql/sql_select.cc | 86 ++++++++++++++++++++++++++----- 9 files changed, 279 insertions(+), 19 deletions(-) diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index cdedc02..2999f4c 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -6939,7 +6939,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where -1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index +1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away set @tmp_mdev410=@@global.userstat; set global userstat=on; diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index efc348a..ae24485 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -2453,6 +2453,74 @@ SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); 1 drop table t1,t2; drop view v1; +# +# MDEV-19580: function invocation in the left part of IN subquery +# +create table t1 (id int, a varchar(50), b int); +insert into t1 values +(1,'mrs',2), (2,'joe',2), (3,'paul',1), (4,'art',1); +create table t2 (id int, a varchar(50), x int); +insert into t2 values +(1,'grand',1),(2,'average',1),(3,'serf',0); +create table t3 (d1 date, d2 date, t1_id int, t2_id int ); +insert into t3 values +('1972-01-01','1988-12-31',3,1), ('1972-01-01','1988-12-31',4,1), +('1972-01-01','1988-12-31',1,2), ('1972-01-01','1988-12-31',2,3); +create table t4 ( id int, a varchar(50) ); +insert into t4 values +(1,'songwriter'),(2,'song character'); +create function f1(who int, dt date) returns int +deterministic +begin +declare result int; +select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; +return result; +end$$ +create function f2(who int, dt date) returns int +begin +declare result int; +select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; +return result; +end$$ +# Deterministic function in left part of IN subquery: semi-join is OK +select * from t1 +left join t4 on t1.b = t4.id +where f1(t1.id, '1980-01-01') in (select id from t2 where x=1); +id a b id a +3 paul 1 1 songwriter +4 art 1 1 songwriter +1 mrs 2 2 song character +explain extended select * from t1 +left join t4 on t1.b = t4.id +where f1(t1.id, '1980-01-01') in (select id from t2 where x=1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f1`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`)) +# Non-deterministic function in left part of IN subq: semi-join is OK +select * from t1 +left join t4 on t1.b = t4.id +where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); +id a b id a +3 paul 1 1 songwriter +4 art 1 1 songwriter +1 mrs 2 2 song character +explain extended select * from t1 +left join t4 on t1.b = t4.id +where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f2`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`)) +drop function f1; +drop function f2; +drop table t1,t2,t3,t4; # End of 5.5 tests set @subselect_mat_test_optimizer_switch_value=null; set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off'; diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index a729129..5c21e74 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -6937,7 +6937,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where -1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index +1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away set @tmp_mdev410=@@global.userstat; set global userstat=on; diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index c41fa1b..8ab1f1b 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -6934,7 +6934,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where -1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index +1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away set @tmp_mdev410=@@global.userstat; set global userstat=on; diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index 1c18135..f0afa7e 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -6945,7 +6945,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where -1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index +1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away set @tmp_mdev410=@@global.userstat; set global userstat=on; diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 89c6712..0d5fbc3 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -6934,7 +6934,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where -1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index +1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away set @tmp_mdev410=@@global.userstat; set global userstat=on; diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index fd9435e..9dde115 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -2493,4 +2493,72 @@ SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); 1 drop table t1,t2; drop view v1; +# +# MDEV-19580: function invocation in the left part of IN subquery +# +create table t1 (id int, a varchar(50), b int); +insert into t1 values +(1,'mrs',2), (2,'joe',2), (3,'paul',1), (4,'art',1); +create table t2 (id int, a varchar(50), x int); +insert into t2 values +(1,'grand',1),(2,'average',1),(3,'serf',0); +create table t3 (d1 date, d2 date, t1_id int, t2_id int ); +insert into t3 values +('1972-01-01','1988-12-31',3,1), ('1972-01-01','1988-12-31',4,1), +('1972-01-01','1988-12-31',1,2), ('1972-01-01','1988-12-31',2,3); +create table t4 ( id int, a varchar(50) ); +insert into t4 values +(1,'songwriter'),(2,'song character'); +create function f1(who int, dt date) returns int +deterministic +begin +declare result int; +select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; +return result; +end$$ +create function f2(who int, dt date) returns int +begin +declare result int; +select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; +return result; +end$$ +# Deterministic function in left part of IN subquery: semi-join is OK +select * from t1 +left join t4 on t1.b = t4.id +where f1(t1.id, '1980-01-01') in (select id from t2 where x=1); +id a b id a +3 paul 1 1 songwriter +4 art 1 1 songwriter +1 mrs 2 2 song character +explain extended select * from t1 +left join t4 on t1.b = t4.id +where f1(t1.id, '1980-01-01') in (select id from t2 where x=1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f1`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`)) +# Non-deterministic function in left part of IN subq: semi-join is OK +select * from t1 +left join t4 on t1.b = t4.id +where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); +id a b id a +3 paul 1 1 songwriter +4 art 1 1 songwriter +1 mrs 2 2 song character +explain extended select * from t1 +left join t4 on t1.b = t4.id +where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f2`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`)) +drop function f1; +drop function f2; +drop table t1,t2,t3,t4; # End of 5.5 tests diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test index c82c1e7..997e71a 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -2230,4 +2230,70 @@ explain SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); drop table t1,t2; drop view v1; + + +--echo # +--echo # MDEV-19580: function invocation in the left part of IN subquery +--echo # + +create table t1 (id int, a varchar(50), b int); +insert into t1 values +(1,'mrs',2), (2,'joe',2), (3,'paul',1), (4,'art',1); + +create table t2 (id int, a varchar(50), x int); +insert into t2 values +(1,'grand',1),(2,'average',1),(3,'serf',0); + +create table t3 (d1 date, d2 date, t1_id int, t2_id int ); +insert into t3 values +('1972-01-01','1988-12-31',3,1), ('1972-01-01','1988-12-31',4,1), +('1972-01-01','1988-12-31',1,2), ('1972-01-01','1988-12-31',2,3); + +create table t4 ( id int, a varchar(50) ); +insert into t4 values +(1,'songwriter'),(2,'song character'); + +delimiter $$; + +create function f1(who int, dt date) returns int +deterministic +begin + declare result int; + select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; + return result; +end$$ + +create function f2(who int, dt date) returns int +begin + declare result int; + select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; + return result; +end$$ + +delimiter ;$$ + +--echo # Deterministic function in left part of IN subquery: semi-join is OK + +let $q1= +select * from t1 + left join t4 on t1.b = t4.id + where f1(t1.id, '1980-01-01') in (select id from t2 where x=1); + +eval $q1; +eval explain extended $q1; + +--echo # Non-deterministic function in left part of IN subq: semi-join is OK + +let $q2= +select * from t1 + left join t4 on t1.b = t4.id + where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); + +eval $q2; +eval explain extended $q2; + +drop function f1; +drop function f2; +drop table t1,t2,t3,t4; + --echo # End of 5.5 tests diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 37f8292..797b3a5 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -204,7 +204,8 @@ static COND *make_cond_for_table_from_pred(THD *thd, Item *root_cond, table_map used_table, int join_tab_idx_arg, bool exclude_expensive_cond, - bool retain_ref_cond); + bool retain_ref_cond, + bool is_top_and_level); static Item* part_of_refkey(TABLE *form,Field *field); uint find_shortest_key(TABLE *table, const key_map *usable_keys); @@ -8922,12 +8923,6 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) RAND_TABLE_BIT; } - /* - Following force including random expression in last table condition. - It solve problem with select like SELECT * FROM t1 WHERE rand() > 0.5 - */ - if (tab == join->join_tab + last_top_base_tab_idx) - current_map|= RAND_TABLE_BIT; used_tables|=current_map; if (tab->type == JT_REF && tab->quick && @@ -8968,6 +8963,20 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) { tmp= make_cond_for_table(thd, cond, used_tables, current_map, i, FALSE, FALSE); + if (tab == join->join_tab + last_top_base_tab_idx) + { + /* + This pushes conjunctive conditions of WHERE condition such that: + - their used_tables() contain RAND_TABLE_BIT + - the conditions does not refer to any fields + (such like rand() > 0.5) + */ + table_map rand_table_bit= (table_map) RAND_TABLE_BIT; + COND *rand_cond= make_cond_for_table(thd, cond, used_tables, + rand_table_bit, -1, + FALSE, FALSE); + add_cond_and_fix(thd, &tmp, rand_cond); + } } /* Add conditions added by add_not_null_conds(). */ if (tab->select_cond) @@ -9283,8 +9292,24 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) psergey: have put the -1 below. It's bad, will need to fix it. */ COND *tmp_cond= make_cond_for_table(thd, on_expr, used_tables2, - current_map, /*(tab - first_tab)*/ -1, - FALSE, FALSE); + current_map, + /*(tab - first_tab)*/ -1, + FALSE, FALSE); + if (tab == last_tab) + { + /* + This pushes conjunctive conditions of ON expression of an outer + join such that: + - their used_tables() contain RAND_TABLE_BIT + - the conditions does not refer to any fields + (such like rand() > 0.5) + */ + table_map rand_table_bit= (table_map) RAND_TABLE_BIT; + COND *rand_cond= make_cond_for_table(thd, on_expr, used_tables2, + rand_table_bit, -1, + FALSE, FALSE); + add_cond_and_fix(thd, &tmp_cond, rand_cond); + } bool is_sjm_lookup_tab= FALSE; if (tab->bush_children) { @@ -18824,7 +18849,7 @@ make_cond_for_table(THD *thd, Item *cond, table_map tables, return make_cond_for_table_from_pred(thd, cond, cond, tables, used_table, join_tab_idx_arg, exclude_expensive_cond, - retain_ref_cond); + retain_ref_cond, true); } @@ -18834,9 +18859,12 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond, int join_tab_idx_arg, bool exclude_expensive_cond __attribute__ ((unused)), - bool retain_ref_cond) + bool retain_ref_cond, + bool is_top_and_level) { + table_map rand_table_bit= (table_map) RAND_TABLE_BIT; + if (used_table && !(cond->used_tables() & used_table)) return (COND*) 0; // Already checked @@ -18852,11 +18880,27 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond, Item *item; while ((item=li++)) { + /* + Special handling of top level conjuncts with RAND_TABLE_BIT: + if such a conjunct contains a reference to a field then it is pushed + to the corresponding table by the same rule as all other conjuncts. + Otherwise, if the conjunct is used in WHERE is is pushed to the last + joined table, if is it is used in ON condition of an outer join it + is pushed into the last inner table of the outer join. Such conjuncts + are pushed in a call of make_cond_for_table_from_pred() with the + parameter 'used_table' equal to RAND_TABLE_BIT. + */ + if (is_top_and_level && used_table == rand_table_bit && + item->used_tables() != rand_table_bit) + { + /* The conjunct with RAND_TABLE_BIT has been allready pushed */ + continue; + } Item *fix=make_cond_for_table_from_pred(thd, root_cond, item, tables, used_table, - join_tab_idx_arg, + join_tab_idx_arg, exclude_expensive_cond, - retain_ref_cond); + retain_ref_cond, false); if (fix) new_cond->argument_list()->push_back(fix); } @@ -18880,6 +18924,13 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond, } else { // Or list + if (is_top_and_level && used_table == rand_table_bit && + cond->used_tables() != rand_table_bit) + { + /* This top level formula with RAND_TABLE_BIT has been already pushed */ + return (COND*) 0; + } + Item_cond_or *new_cond=new Item_cond_or; if (!new_cond) return (COND*) 0; // OOM /* purecov: inspected */ @@ -18891,7 +18942,7 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond, tables, 0L, join_tab_idx_arg, exclude_expensive_cond, - retain_ref_cond); + retain_ref_cond, false); if (!fix) return (COND*) 0; // Always true new_cond->argument_list()->push_back(fix); @@ -18908,6 +18959,13 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond, } } + if (is_top_and_level && used_table == rand_table_bit && + cond->used_tables() != rand_table_bit) + { + /* This top level formula with RAND_TABLE_BIT has been already pushed */ + return (COND*) 0; + } + /* Because the following test takes a while and it can be done table_count times, we mark each item that we have examined with the result

1 0

[Commits] 5469d88e7b4: MDEV-19049 Server crashes in check_duplicate_long_entry_key, ASAN stack-buffer-overflow in Field_blob::get_key_image
by sachin.setiya＠mariadb.com 31 May '19

31 May '19

revision-id: 5469d88e7b4e723e545cd7d511801a16a3642dc4 (mariadb-10.4.3-104-g5469d88e7b4) parent(s): 0bc42602266815b81fe86b08c2228912c1a95340 author: Sachin committer: Sachin timestamp: 2019-03-28 11:29:25 +0530 message: MDEV-19049 Server crashes in check_duplicate_long_entry_key, ASAN stack-buffer-overflow in Field_blob::get_key_image Long Unique keys should always be last unique key. --- mysql-test/main/long_unique.result | 64 +++++++++++++++---------------- mysql-test/main/long_unique_bugs.result | 46 ++++++++++++++++++++++ mysql-test/main/long_unique_bugs.test | 22 ++++++++++- mysql-test/main/long_unique_innodb.result | 4 +- mysql-test/main/long_unique_update.result | 8 ++-- sql/sql_table.cc | 9 +++++ 6 files changed, 114 insertions(+), 39 deletions(-) diff --git a/mysql-test/main/long_unique.result b/mysql-test/main/long_unique.result index 3843ff4aff0..8ea6d36c321 100644 --- a/mysql-test/main/long_unique.result +++ b/mysql-test/main/long_unique.result @@ -184,8 +184,8 @@ t1 CREATE TABLE `t1` ( `a` blob DEFAULT NULL, `c` int(11) DEFAULT NULL, `db_row_hash_1` int(11) DEFAULT NULL, - UNIQUE KEY `a` (`a`) USING HASH, - UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`) + UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`), + UNIQUE KEY `a` (`a`) USING HASH ) ENGINE=MyISAM DEFAULT CHARSET=latin1 insert into t1 values(45,1,55),(46,1,55); ERROR 23000: Duplicate entry '55' for key 'db_row_hash_1' @@ -507,13 +507,13 @@ t1 CREATE TABLE `t1` ( `db_row_hash_1` int(11) DEFAULT NULL, `db_row_hash_2` int(11) DEFAULT NULL, `db_row_hash_3` int(11) DEFAULT NULL, + UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`), + UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`), + UNIQUE KEY `db_row_hash_3` (`db_row_hash_3`), UNIQUE KEY `a` (`a`) USING HASH, UNIQUE KEY `c` (`c`) USING HASH, UNIQUE KEY `d` (`d`) USING HASH, - UNIQUE KEY `e` (`e`) USING HASH, - UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`), - UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`), - UNIQUE KEY `db_row_hash_3` (`db_row_hash_3`) + UNIQUE KEY `e` (`e`) USING HASH ) ENGINE=MyISAM DEFAULT CHARSET=latin1 alter table t1 add column db_row_hash_7 int, add column db_row_hash_5 int , add column db_row_hash_4 int ; alter table t1 drop column db_row_hash_7,drop column db_row_hash_3, drop column db_row_hash_4; @@ -543,17 +543,17 @@ t1 CREATE TABLE `t1` ( `db_row_hash_1` int(11) DEFAULT NULL, `db_row_hash_2` int(11) DEFAULT NULL, `db_row_hash_5` int(11) DEFAULT NULL, - UNIQUE KEY `d` (`d`) USING HASH, - UNIQUE KEY `e` (`e`) USING HASH, UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`), - UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`) + UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`), + UNIQUE KEY `d` (`d`) USING HASH, + UNIQUE KEY `e` (`e`) USING HASH ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 d 1 d A NULL NULL NULL YES HASH -t1 0 e 1 e A NULL NULL NULL YES HASH t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL NULL YES BTREE t1 0 db_row_hash_2 1 db_row_hash_2 A NULL NULL NULL YES BTREE +t1 0 d 1 d A NULL NULL NULL YES HASH +t1 0 e 1 e A NULL NULL NULL YES HASH #add column with unique index on blob; alter table t1 add column a blob unique; show create table t1; @@ -567,18 +567,18 @@ t1 CREATE TABLE `t1` ( `db_row_hash_2` int(11) DEFAULT NULL, `db_row_hash_5` int(11) DEFAULT NULL, `a` blob DEFAULT NULL, - UNIQUE KEY `d` (`d`) USING HASH, - UNIQUE KEY `e` (`e`) USING HASH, UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`), UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`), + UNIQUE KEY `d` (`d`) USING HASH, + UNIQUE KEY `e` (`e`) USING HASH, UNIQUE KEY `a` (`a`) USING HASH ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 d 1 d A NULL NULL NULL YES HASH -t1 0 e 1 e A NULL NULL NULL YES HASH t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL NULL YES BTREE t1 0 db_row_hash_2 1 db_row_hash_2 A NULL NULL NULL YES BTREE +t1 0 d 1 d A NULL NULL NULL YES HASH +t1 0 e 1 e A NULL NULL NULL YES HASH t1 0 a 1 a A NULL NULL NULL YES HASH #try to change the blob unique column name; #this will change index to b tree; @@ -594,19 +594,19 @@ t1 CREATE TABLE `t1` ( `db_row_hash_2` int(11) DEFAULT NULL, `db_row_hash_5` int(11) DEFAULT NULL, `a` int(11) DEFAULT NULL, - UNIQUE KEY `d` (`d`) USING HASH, - UNIQUE KEY `e` (`e`), UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`), UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`), - UNIQUE KEY `a` (`a`) + UNIQUE KEY `e` (`e`), + UNIQUE KEY `a` (`a`), + UNIQUE KEY `d` (`d`) USING HASH ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 d 1 d A NULL NULL NULL YES HASH -t1 0 e 1 e A NULL NULL NULL YES BTREE t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL NULL YES BTREE t1 0 db_row_hash_2 1 db_row_hash_2 A NULL NULL NULL YES BTREE +t1 0 e 1 e A NULL NULL NULL YES BTREE t1 0 a 1 a A NULL NULL NULL YES BTREE +t1 0 d 1 d A NULL NULL NULL YES HASH alter table t1 add column clm1 blob unique,add column clm2 blob unique; #try changing the name; alter table t1 change column clm1 clm_changed1 blob, change column clm2 clm_changed2 blob; @@ -623,21 +623,21 @@ t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `clm_changed1` blob DEFAULT NULL, `clm_changed2` blob DEFAULT NULL, - UNIQUE KEY `d` (`d`) USING HASH, - UNIQUE KEY `e` (`e`), UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`), UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`), + UNIQUE KEY `e` (`e`), UNIQUE KEY `a` (`a`), + UNIQUE KEY `d` (`d`) USING HASH, UNIQUE KEY `clm1` (`clm_changed1`) USING HASH, UNIQUE KEY `clm2` (`clm_changed2`) USING HASH ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 d 1 d A NULL NULL NULL YES HASH -t1 0 e 1 e A NULL NULL NULL YES BTREE t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL NULL YES BTREE t1 0 db_row_hash_2 1 db_row_hash_2 A NULL NULL NULL YES BTREE +t1 0 e 1 e A NULL NULL NULL YES BTREE t1 0 a 1 a A NULL NULL NULL YES BTREE +t1 0 d 1 d A NULL NULL NULL YES HASH t1 0 clm1 1 clm_changed1 A NULL NULL NULL YES HASH t1 0 clm2 1 clm_changed2 A NULL NULL NULL YES HASH #now drop the unique key; @@ -655,19 +655,19 @@ t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `clm_changed1` blob DEFAULT NULL, `clm_changed2` blob DEFAULT NULL, - UNIQUE KEY `d` (`d`) USING HASH, - UNIQUE KEY `e` (`e`), UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`), UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`), - UNIQUE KEY `a` (`a`) + UNIQUE KEY `e` (`e`), + UNIQUE KEY `a` (`a`), + UNIQUE KEY `d` (`d`) USING HASH ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 d 1 d A NULL NULL NULL YES HASH -t1 0 e 1 e A NULL NULL NULL YES BTREE t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL NULL YES BTREE t1 0 db_row_hash_2 1 db_row_hash_2 A NULL NULL NULL YES BTREE +t1 0 e 1 e A NULL NULL NULL YES BTREE t1 0 a 1 a A NULL NULL NULL YES BTREE +t1 0 d 1 d A NULL NULL NULL YES HASH drop table t1; #now the table with key on multiple columns; the ultimate test; create table t1(a blob, b int , c varchar(2000) , d text , e varchar(3000) , f longblob , g int , h text , @@ -1130,17 +1130,17 @@ t1 CREATE TABLE `t1` ( `c` blob DEFAULT NULL, `d` blob DEFAULT NULL, `e` int(11) DEFAULT NULL, + UNIQUE KEY `e` (`e`), UNIQUE KEY `a` (`a`,`c`) USING HASH, - UNIQUE KEY `b` (`b`,`d`) USING HASH, - UNIQUE KEY `e` (`e`) + UNIQUE KEY `b` (`b`,`d`) USING HASH ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment +t1 0 e 1 e A NULL NULL NULL YES BTREE t1 0 a 1 a A NULL NULL NULL YES HASH t1 0 a 2 c A NULL NULL NULL YES HASH t1 0 b 1 b A NULL NULL NULL YES HASH t1 0 b 2 d A NULL NULL NULL YES HASH -t1 0 e 1 e A 0 NULL NULL YES BTREE drop table t1; #visibility of db_row_hash create table t1 (a blob unique , b blob unique); diff --git a/mysql-test/main/long_unique_bugs.result b/mysql-test/main/long_unique_bugs.result index 87a57fb4614..48e74bdd564 100644 --- a/mysql-test/main/long_unique_bugs.result +++ b/mysql-test/main/long_unique_bugs.result @@ -239,3 +239,49 @@ CREATE TABLE t1 (a INT, UNIQUE USING HASH (a)) PARTITION BY HASH (a) PARTITIONS INSERT INTO t1 VALUES (2); REPLACE INTO t1 VALUES (2); DROP TABLE t1; +CREATE TABLE t1 (pk INT, a CHAR(4), b BLOB NOT NULL, PRIMARY KEY(pk)); +INSERT INTO t1 VALUES (1,'foo','bar'); +ALTER TABLE t1 ADD KEY (b(64)); +ALTER TABLE t1 ADD UNIQUE (b(165)); +ALTER TABLE t1 ADD KEY (b(1000)); +ALTER TABLE t1 ADD KEY (b(500)); +ALTER TABLE t1 ADD UNIQUE (a,b); +ALTER TABLE t1 ADD UNIQUE (b(95)); +ALTER TABLE t1 ADD KEY (b(30)); +ALTER TABLE t1 ADD UNIQUE (b(20)); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `pk` int(11) NOT NULL, + `a` char(4) DEFAULT NULL, + `b` blob NOT NULL, + PRIMARY KEY (`pk`), + UNIQUE KEY `b_2` (`b`(165)), + UNIQUE KEY `b_5` (`b`(95)), + UNIQUE KEY `b_7` (`b`(20)), + UNIQUE KEY `a` (`a`,`b`) USING HASH, + KEY `b` (`b`(64)), + KEY `b_3` (`b`(1000)), + KEY `b_4` (`b`(500)), + KEY `b_6` (`b`(30)) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +ALTER TABLE t1 ADD UNIQUE (b); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `pk` int(11) NOT NULL, + `a` char(4) DEFAULT NULL, + `b` blob NOT NULL, + PRIMARY KEY (`pk`), + UNIQUE KEY `b_2` (`b`(165)), + UNIQUE KEY `b_5` (`b`(95)), + UNIQUE KEY `b_7` (`b`(20)), + UNIQUE KEY `a` (`a`,`b`) USING HASH, + UNIQUE KEY `b_8` (`b`) USING HASH, + KEY `b` (`b`(64)), + KEY `b_3` (`b`(1000)), + KEY `b_4` (`b`(500)), + KEY `b_6` (`b`(30)) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +ALTER TABLE t1 FORCE; +DROP TABLE t1; diff --git a/mysql-test/main/long_unique_bugs.test b/mysql-test/main/long_unique_bugs.test index ed0daee426f..11b1c4f09b6 100644 --- a/mysql-test/main/long_unique_bugs.test +++ b/mysql-test/main/long_unique_bugs.test @@ -1,4 +1,5 @@ --source include/have_innodb.inc +--source include/have_partition.inc # # MDEV-18707 Server crash in my_hash_sort_bin, ASAN heap-use-after-free in Field::is_null, server hang, corrupted double-linked list @@ -269,8 +270,27 @@ drop table t1; # # MDEV-18904 Assertion `m_part_spec.start_part >= m_part_spec.end_part' failed in ha_partition::index_read_idx_map # ---source include/have_partition.inc CREATE TABLE t1 (a INT, UNIQUE USING HASH (a)) PARTITION BY HASH (a) PARTITIONS 2; INSERT INTO t1 VALUES (2); REPLACE INTO t1 VALUES (2); DROP TABLE t1; + +# +# MDEV-19049 Server crashes in check_duplicate_long_entry_key, ASAN stack-buffer-overflow in Field_blob::get_key_image +# +CREATE TABLE t1 (pk INT, a CHAR(4), b BLOB NOT NULL, PRIMARY KEY(pk)); +INSERT INTO t1 VALUES (1,'foo','bar'); + +ALTER TABLE t1 ADD KEY (b(64)); +ALTER TABLE t1 ADD UNIQUE (b(165)); +ALTER TABLE t1 ADD KEY (b(1000)); +ALTER TABLE t1 ADD KEY (b(500)); +ALTER TABLE t1 ADD UNIQUE (a,b); +ALTER TABLE t1 ADD UNIQUE (b(95)); +ALTER TABLE t1 ADD KEY (b(30)); +ALTER TABLE t1 ADD UNIQUE (b(20)); +show create table t1; +ALTER TABLE t1 ADD UNIQUE (b); +show create table t1; +ALTER TABLE t1 FORCE; +DROP TABLE t1; diff --git a/mysql-test/main/long_unique_innodb.result b/mysql-test/main/long_unique_innodb.result index cb8c3ea4858..135bb0808cc 100644 --- a/mysql-test/main/long_unique_innodb.result +++ b/mysql-test/main/long_unique_innodb.result @@ -9,8 +9,8 @@ Table Create Table t1 CREATE TABLE `t1` ( `a` blob DEFAULT NULL, `c` int(11) DEFAULT NULL, - UNIQUE KEY `a` (`a`) USING HASH, - UNIQUE KEY `c` (`c`) + UNIQUE KEY `c` (`c`), + UNIQUE KEY `a` (`a`) USING HASH ) ENGINE=InnoDB DEFAULT CHARSET=latin1 drop table t1; #test for concurrent insert of long unique in innodb diff --git a/mysql-test/main/long_unique_update.result b/mysql-test/main/long_unique_update.result index 60a4fb46558..b508583f47c 100644 --- a/mysql-test/main/long_unique_update.result +++ b/mysql-test/main/long_unique_update.result @@ -71,8 +71,8 @@ create table t1 (a int primary key, b blob unique , c int unique ); show keys from 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 a A 0 NULL NULL BTREE -t1 0 b 1 b A NULL NULL NULL YES HASH t1 0 c 1 c A NULL NULL NULL YES BTREE +t1 0 b 1 b A NULL NULL NULL YES HASH insert into t1 values(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),(6,6,6),(7,7,7); select * from t1 limit 3; a b c @@ -220,18 +220,18 @@ t1 CREATE TABLE `t1` ( `f` int(11) DEFAULT NULL, `g` text DEFAULT NULL, PRIMARY KEY (`a`), - UNIQUE KEY `b` (`b`,`c`) USING HASH, UNIQUE KEY `b_2` (`b`,`f`), + UNIQUE KEY `b` (`b`,`c`) USING HASH, UNIQUE KEY `e` (`e`,`g`) USING HASH, UNIQUE KEY `a` (`a`,`b`,`c`,`d`,`e`,`f`,`g`) USING HASH ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from 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 a A 0 NULL NULL BTREE -t1 0 b 1 b A NULL NULL NULL YES HASH -t1 0 b 2 c A NULL NULL NULL YES HASH t1 0 b_2 1 b A NULL NULL NULL YES BTREE t1 0 b_2 2 f A NULL NULL NULL YES BTREE +t1 0 b 1 b A NULL NULL NULL YES HASH +t1 0 b 2 c A NULL NULL NULL YES HASH t1 0 e 1 e A NULL NULL NULL YES HASH t1 0 e 2 g A NULL NULL NULL YES HASH t1 0 a 1 a A NULL NULL NULL HASH diff --git a/sql/sql_table.cc b/sql/sql_table.cc index ad62ecc1103..c755a74e174 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -2773,6 +2773,7 @@ bool quick_rm_table(THD *thd, handlerton *base, const LEX_CSTRING *db, - UNIQUE keys where all column are NOT NULL - UNIQUE keys that don't contain partial segments - Other UNIQUE keys + - LONG UNIQUE keys - Normal keys - Fulltext keys @@ -2796,6 +2797,14 @@ static int sort_keys(KEY *a, KEY *b) { if (!(b_flags & HA_NOSAME)) return -1; + /* + Long Unique keys should always be last unique key. + Before this patch they used to change order wrt to partial keys (MDEV-19049) + */ + if (a->algorithm == HA_KEY_ALG_LONG_HASH) + return 1; + if (b->algorithm == HA_KEY_ALG_LONG_HASH) + return -1; if ((a_flags ^ b_flags) & HA_NULL_PART_KEY) { /* Sort NOT NULL keys before other keys */

1 0

[Commits] 4dc2b6b7180: MDEV-18479, post fix after merge to 10.1
by Sergei Petrunia 31 May '19

31 May '19

revision-id: 4dc2b6b7180991e27c5a7d9cad1273791c14a6b5 (mariadb-10.1.39-53-g4dc2b6b7180) parent(s): a8abbeb66376bb990da685c9492abec8b4d623f3 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-05-31 12:47:15 +0300 message: MDEV-18479, post fix after merge to 10.1 Make the EXPLAIN [FORMAT=JSON] print very large #rows values properly, as unsigned int64. --- mysql-test/r/derived_view.result | 14 +++++++------- sql/my_json_writer.cc | 7 +++++++ sql/my_json_writer.h | 1 + sql/sql_explain.cc | 8 ++++---- 4 files changed, 19 insertions(+), 11 deletions(-) diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index 4320aec430e..f3c7c66a240 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -2953,13 +2953,13 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY p10 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join) 1 PRIMARY <derived17> ALL NULL NULL NULL NULL 50328437500000 Using where; Using join buffer (incremental, BNL join) 1 PRIMARY <derived14> ALL NULL NULL NULL NULL 27680640625000000 Using where; Using join buffer (incremental, BNL join) -1 PRIMARY <derived9> ALL NULL NULL NULL NULL -3222391729959550976 Using where; Using join buffer (incremental, BNL join) -1 PRIMARY <derived10> ALL NULL NULL NULL NULL -3222391729959550976 Using where; Using join buffer (incremental, BNL join) -1 PRIMARY <derived11> ALL NULL NULL NULL NULL -3222391729959550976 Using where; Using join buffer (incremental, BNL join) -1 PRIMARY <derived12> ALL NULL NULL NULL NULL -3222391729959550976 Using where; Using join buffer (incremental, BNL join) -1 PRIMARY <derived13> ALL NULL NULL NULL NULL -3222391729959550976 Using where; Using join buffer (incremental, BNL join) -1 PRIMARY <derived15> ALL NULL NULL NULL NULL -3222391729959550976 Using where; Using join buffer (incremental, BNL join) -1 PRIMARY <derived16> ALL NULL NULL NULL NULL -3222391729959550976 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY <derived9> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY <derived10> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY <derived11> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY <derived12> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY <derived13> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY <derived15> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY <derived16> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join) 1 PRIMARY <derived7> ALL NULL NULL NULL NULL 0 Using where; Using join buffer (incremental, BNL join) 1 PRIMARY <derived8> ALL NULL NULL NULL NULL 0 Using where; Using join buffer (incremental, BNL join) 17 DERIVED t2 system NULL NULL NULL NULL 1 diff --git a/sql/my_json_writer.cc b/sql/my_json_writer.cc index 72b000df639..22c66ba7a34 100644 --- a/sql/my_json_writer.cc +++ b/sql/my_json_writer.cc @@ -129,6 +129,13 @@ void Json_writer::add_ll(longlong val) add_unquoted_str(buf); } +void Json_writer::add_ull(ulonglong val) +{ + char buf[64]; + my_snprintf(buf, sizeof(buf), "%llu", val); + add_unquoted_str(buf); +} + /* Add a memory size, printing in Kb, Kb, Gb if necessary */ void Json_writer::add_size(longlong val) diff --git a/sql/my_json_writer.h b/sql/my_json_writer.h index 349a1f380da..ffee6db4c03 100644 --- a/sql/my_json_writer.h +++ b/sql/my_json_writer.h @@ -108,6 +108,7 @@ class Json_writer void add_str(const String &str); void add_ll(longlong val); + void add_ull(ulonglong val); void add_size(longlong val); void add_double(double val); void add_bool(bool val); diff --git a/sql/sql_explain.cc b/sql/sql_explain.cc index c8576136069..2fa4b2cb3ae 100644 --- a/sql/sql_explain.cc +++ b/sql/sql_explain.cc @@ -1223,7 +1223,7 @@ int Explain_table_access::print_explain(select_result_sink *output, uint8 explai if (rows_set) { item_list.push_back(new (mem_root) - Item_int(thd, (longlong) (ulonglong) rows, + Item_int(thd, (ulonglong) rows, MY_INT64_NUM_DECIMAL_DIGITS), mem_root); } @@ -1601,7 +1601,7 @@ void Explain_table_access::print_explain_json(Explain_query *query, /* `rows` */ if (rows_set) - writer->add_member("rows").add_ll(rows); + writer->add_member("rows").add_ull(rows); /* `r_rows` */ if (is_analyze) @@ -2239,7 +2239,7 @@ void Explain_update::print_explain_json(Explain_query *query, } /* `rows` */ - writer->add_member("rows").add_ll(rows); + writer->add_member("rows").add_ull(rows); if (mrr_type.length() != 0) @@ -2268,7 +2268,7 @@ void Explain_update::print_explain_json(Explain_query *query, r_rows= 0; r_filtered= buf_tracker.get_filtered_after_where() * 100.0; } - writer->add_member("r_rows").add_ll(r_rows); + writer->add_member("r_rows").add_ull(r_rows); writer->add_member("r_filtered").add_double(r_filtered); } else /* Not doing buffering */

1 0

Re: [Commits] MDEV-19653 Add class Sql_cmd_create_table
by Sergey Vojtovich 31 May '19

31 May '19

Hi Alexander, > > commit d070c68861670c0a556e3f29cb0b3d5fdd87a8c8 > Author: Alexander Barkov <bar(a)mariadb.com> > Date: Fri May 31 09:29:35 2019 +0400 > > MDEV-19653 Add class Sql_cmd_create_table > > diff --git a/sql/sql_alter.cc b/sql/sql_alter.cc > index e37d547..dfd421f 100644 > --- a/sql/sql_alter.cc > +++ b/sql/sql_alter.cc > @@ -193,6 +193,20 @@ bool Sql_cmd_alter_table::execute(THD *thd) > SELECT_LEX *select_lex= &lex->select_lex; > /* first table of first SELECT_LEX */ > TABLE_LIST *first_table= (TABLE_LIST*) select_lex->table_list.first; > + > + bool used_engine= lex->create_info.used_fields & HA_CREATE_USED_ENGINE; > + DBUG_ASSERT(m_storage_engine_name.str || !used_engine); I think it is possible to specify ALTER TABLE t1 ENGINE='' We should not crash in this case. > + if (used_engine && > + thd->resolve_storage_engine(&lex->create_info.db_type, > + m_storage_engine_name, > + lex->create_info.tmp_table())) > + return true; > + > + if ((lex->create_info.used_fields & HA_CREATE_USED_ENGINE) && > + !lex->create_info.db_type) > + lex->create_info.used_fields&= ~HA_CREATE_USED_ENGINE; > + > + Why not if (lex->create_info.used_fields & HA_CREATE_USED_ENGINE) { DBUG_ASSERT(m_storage_engine_name.str); if (thd->resolve_storage_engine(&lex->create_info.db_type, m_storage_engine_name, lex->create_info.tmp_table())) return true; if (!lex->create_info.db_type) lex->create_info.used_fields&= ~HA_CREATE_USED_ENGINE; } > /* > Code in mysql_alter_table() may modify its HA_CREATE_INFO argument, > so we have to use a copy of this structure to make execution > diff --git a/sql/sql_alter.h b/sql/sql_alter.h > index a503837..66c20fc 100644 > --- a/sql/sql_alter.h > +++ b/sql/sql_alter.h > @@ -385,7 +385,8 @@ class Sql_cmd_common_alter_table : public Sql_cmd > Sql_cmd_alter_table represents the generic ALTER TABLE statement. > @todo move Alter_info and other ALTER specific structures from Lex here. > */ > -class Sql_cmd_alter_table : public Sql_cmd_common_alter_table > +class Sql_cmd_alter_table : public Sql_cmd_common_alter_table, > + public Sql_cmd_options_table_dml s/dml/ddl? > { > public: > /** > @@ -397,6 +398,8 @@ class Sql_cmd_alter_table : public Sql_cmd_common_alter_table > ~Sql_cmd_alter_table() > {} > > + Sql_cmd_options_table_dml *options_table_dml() { return this; } > + > bool execute(THD *thd); > }; > > @@ -423,4 +426,27 @@ class Sql_cmd_discard_import_tablespace : public Sql_cmd_common_alter_table > const enum_tablespace_op_type m_tablespace_op; > }; > > + > +class Sql_cmd_create_table: public Sql_cmd, > + public Sql_cmd_options_table_dml > +{ > +public: > + Sql_cmd_create_table() > + {} > + > + ~Sql_cmd_create_table() > + { } > + > + enum_sql_command sql_command_code() const > + { > + return SQLCOM_ALTER_TABLE; > + } Did you mean SQLCOM_CREATE_TABLE? > + > + Sql_cmd_options_table_dml *options_table_dml() { return this; } > + > + bool execute(THD *thd); > + > +}; > + > + > #endif It doesn't feels like a proper place for Sql_cmd_create_table. May be move either to sql_table.h or sql_cmd.h? Why do you need empty constructor/destructor? > diff --git a/sql/sql_class.cc b/sql/sql_class.cc > index 8fabcd5..e107f57 100644 > --- a/sql/sql_class.cc > +++ b/sql/sql_class.cc > @@ -6151,6 +6151,32 @@ int THD::decide_logging_format(TABLE_LIST *tables) > > #ifndef MYSQL_CLIENT > > +bool THD::resolve_storage_engine(handlerton **ha, const LEX_CSTRING &name, > + bool tmp_table) I don't feel like THD is the right home for this. Can we have it as a regular function in handler.cc. Like ha_resolve_by_name_with_error(). > +{ > + LEX_STRING tmp_name= {(char*) name.str, name.length}; const_cast<char*>(name.str) precisely. > + plugin_ref plugin= ha_resolve_by_name(this, &tmp_name, tmp_table); > + > + if (plugin) May be if (plugin_ref plugin= ha_resolve_by_name(this, &tmp_name, tmp_table)) > + { > + *ha= plugin_hton(plugin); > + return false; > + } > + > + *ha= NULL; > + if (variables.sql_mode & MODE_NO_ENGINE_SUBSTITUTION) > + { > + my_error(ER_UNKNOWN_STORAGE_ENGINE, MYF(0), name.str); > + return true; > + } > + push_warning_printf(this, Sql_condition::WARN_LEVEL_WARN, > + ER_UNKNOWN_STORAGE_ENGINE, > + ER_THD(this, ER_UNKNOWN_STORAGE_ENGINE), > + name.str); > + return false; > +} > + > + > /* > Template member function for ensuring that there is an rows log > event of the apropriate type before proceeding. > diff --git a/sql/sql_cmd.h b/sql/sql_cmd.h > index 9583e01..3f6aa4d 100644 > --- a/sql/sql_cmd.h > +++ b/sql/sql_cmd.h > @@ -102,6 +102,23 @@ enum enum_sql_command { > SQLCOM_END > }; > > + > +// We should eventually move LEX::create_info here > +class Sql_cmd_options_table_dml > +{ > +protected: > + LEX_CSTRING m_storage_engine_name; > +public: > + Sql_cmd_options_table_dml() > + :m_storage_engine_name({0,0}) > + { } Not sure if we need initialize this. In fact it'd be nice to let ASAN catch bad accesses. > + void set_storage_engine_name(const LEX_CSTRING &name) > + { > + m_storage_engine_name= name; > + } > +}; > + > + > /** > @class Sql_cmd - Representation of an SQL command. > > @@ -145,6 +162,8 @@ class Sql_cmd : public Sql_alloc > */ > virtual bool execute(THD *thd) = 0; > > + virtual Sql_cmd_options_table_dml *options_table_dml() { return NULL; } > + > protected: > Sql_cmd() > {} > diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc > index 9cb65e8..c8b9219 100644 > --- a/sql/sql_parse.cc > +++ b/sql/sql_parse.cc > @@ -5699,6 +5431,7 @@ mysql_execute_command(THD *thd) > case SQLCOM_OPTIMIZE: > case SQLCOM_REPAIR: > case SQLCOM_TRUNCATE: > + case SQLCOM_CREATE_TABLE: > case SQLCOM_ALTER_TABLE: > DBUG_ASSERT(first_table == all_tables && first_table != 0); > /* fall through */ This assertion is already duplicated by Sql_cmd_create_table::execute(). I wonder if it'd make sense to move SQLCOM_CREATE_TABLE forward? > diff --git a/sql/sql_table.cc b/sql/sql_table.cc > index ca78c01..c4c3ef8 100644 > --- a/sql/sql_table.cc > +++ b/sql/sql_table.cc > @@ -10116,3 +10116,304 @@ bool check_engine(THD *thd, const char *db_name, > > DBUG_RETURN(false); > } > + > + > +bool Sql_cmd_create_table::execute(THD *thd) > +{ > + DBUG_ENTER("Sql_cmd_create_table::execute"); > + LEX *lex= thd->lex; > + TABLE_LIST *all_tables= lex->query_tables; > + SELECT_LEX *select_lex= &lex->select_lex; > + TABLE_LIST *first_table= select_lex->table_list.first; > + DBUG_ASSERT(first_table == all_tables && first_table != 0); > + bool link_to_local; > + TABLE_LIST *create_table= first_table; > + TABLE_LIST *select_tables= lex->create_last_non_select_table->next_global; > + /* most outer SELECT_LEX_UNIT of query */ > + SELECT_LEX_UNIT *unit= &lex->unit; > + int res= 0; > + > + bool used_engine= lex->create_info.used_fields & HA_CREATE_USED_ENGINE; > + DBUG_ASSERT(m_storage_engine_name.length || !used_engine); > + if (used_engine) > + { > + if (thd->resolve_storage_engine(&lex->create_info.db_type, > + m_storage_engine_name, > + lex->create_info.tmp_table())) > + DBUG_RETURN(true); // Engine not found, substitution is not allowed > + > + if (!lex->create_info.db_type) > + { > + lex->create_info.use_default_db_type(thd); > + push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN, > + ER_WARN_USING_OTHER_HANDLER, > + ER_THD(thd, ER_WARN_USING_OTHER_HANDLER), > + hton_name(lex->create_info.db_type)->str, > + create_table->table_name); > + } > + } It should probably be joined with "If no engine type was given..." condition. Then you won't need used_engine variable. > diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy > index 59cc73d..7de5bef 100644 > --- a/sql/sql_yacc.yy > +++ b/sql/sql_yacc.yy > @@ -2454,6 +2454,8 @@ create: > create_or_replace opt_temporary TABLE_SYM opt_if_not_exists table_ident > { > LEX *lex= thd->lex; > + if (!(lex->m_sql_cmd= new (thd->mem_root) Sql_cmd_create_table())) > + MYSQL_YYABORT; C++ allows to omit parenthesis if constructor has no parameters. > @@ -5515,10 +5507,19 @@ create_table_options: > ; > > create_table_option: > - ENGINE_SYM opt_equal storage_engines > + ENGINE_SYM opt_equal ident_or_text > { > - Lex->create_info.db_type= $3; > - Lex->create_info.used_fields|= HA_CREATE_USED_ENGINE; > + LEX *lex= Lex; > + if (!lex->m_sql_cmd) > + { > + DBUG_ASSERT(lex->sql_command == SQLCOM_ALTER_TABLE); > + if (!(lex->m_sql_cmd= new (thd->mem_root) Sql_cmd_alter_table())) > + MYSQL_YYABORT; > + } This looks ugly, but I guess we cannot fix it with low effort. > + Sql_cmd_options_table_dml *opt= lex->m_sql_cmd->options_table_dml(); > + DBUG_ASSERT(opt); // Expect a proper Sql_cmd Are there any other options to get Sql_cmd_options_table_dml here? > + opt->set_storage_engine_name({$3.str, $3.length}); > + lex->create_info.used_fields|= HA_CREATE_USED_ENGINE; > } > | MAX_ROWS opt_equal ulonglong_num > { > @@ -5783,21 +5784,10 @@ default_collation: > storage_engines: > ident_or_text > { > - plugin_ref plugin= ha_resolve_by_name(thd, &$1, > - thd->lex->create_info.tmp_table()); > - > - if (plugin) > - $$= plugin_hton(plugin); > - else > - { > - if (thd->variables.sql_mode & MODE_NO_ENGINE_SUBSTITUTION) > - my_yyabort_error((ER_UNKNOWN_STORAGE_ENGINE, MYF(0), $1.str)); > - $$= 0; > - push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN, > - ER_UNKNOWN_STORAGE_ENGINE, > - ER_THD(thd, ER_UNKNOWN_STORAGE_ENGINE), > - $1.str); > - } > + LEX_CSTRING tmp= {$1.str, $1.length}; > + if (thd->resolve_storage_engine(&$$, tmp, > + thd->lex->create_info.tmp_table())) > + MYSQL_YYABORT; I guess you could avoid temporary variable here. Regards, Sergey

1 0

Re: [Commits] b97452a6204: MDEV-19491 update query stopped working after mariadb upgrade 10.2.23 -> 10.2.24
by Sergey Vojtovich 30 May '19

30 May '19

Hi Sergei, On Fri, May 24, 2019 at 06:06:43PM +0200, Sergei Golubchik wrote: > revision-id: b97452a6204 (mariadb-5.5.64-11-gb97452a6204) > parent(s): 7fceef405ae > author: Sergei Golubchik <serg(a)mariadb.com> > committer: Sergei Golubchik <serg(a)mariadb.com> > timestamp: 2019-05-24 18:00:34 +0200 > message: > > MDEV-19491 update query stopped working after mariadb upgrade 10.2.23 -> 10.2.24 ...skip... > diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test > index 14c5574f61c..a3b64ffde8c 100644 > --- a/mysql-test/t/multi_update.test > +++ b/mysql-test/t/multi_update.test > @@ -1081,6 +1081,18 @@ select * from t2; > drop table t1,t2, t3; > drop user foo; > > +# > +# MDEV-19521 Update Table Fails with Trigger and Stored Function > +# > +create table t1 (a int, b varchar(50), c varchar(50)); > +insert t1 (a,b) values (1,'1'), (2,'2'), (3,'3'); > +create function f1() returns varchar(50) return 'result'; > +create trigger tr before update on t1 for each row set new.c = (select f1()); > +create table t2 select a, b from t1; > +update t1 join t2 using (a) set t1.b = t2.b; > +drop table t1, t2; > +drop function f1; I don't completely understand why this test was failing. It doesn't request backoff action, right? Or is this patch fixing more than just backoff retry? > diff --git a/mysql-test/t/multi_update_debug.test b/mysql-test/t/multi_update_debug.test > new file mode 100644 > index 00000000000..ccd7791f029 > --- /dev/null > +++ b/mysql-test/t/multi_update_debug.test > @@ -0,0 +1,30 @@ > +# > +# test MDL backoff-and-retry during multi-update > +# > +source include/have_debug_sync.inc; > +create table t1 (a int, b int); > +create table t2 (c int, d int); > +insert t1 values (1,2),(3,4); > +insert t2 values (5,6),(7,8); > +create table t0 (x int); > +insert t0 values (11), (22); > +create trigger tr1 before update on t1 for each row insert t0 values (new.b); > + > +set debug_sync='open_tables_after_open_and_process_table WAIT_FOR cont'; > +send update t1 join t2 on (a=c+4) set b=d; > + > +connect con1, localhost, root; You should wait here until update reaches open_tables_after_open_and_process_table. Also you should be able to do something like set debug_sync='mdl_acquire_lock_wait SIGNAL cont'; So that you don't need con2 at all. ...skip... > diff --git a/sql/sql_base.h b/sql/sql_base.h > index ea92e880db7..439052a28f5 100644 > --- a/sql/sql_base.h > +++ b/sql/sql_base.h > @@ -426,6 +426,7 @@ class Prelocking_strategy > public: > virtual ~Prelocking_strategy() { } > > + virtual void reset(THD *thd) { }; > virtual bool handle_routine(THD *thd, Query_tables_list *prelocking_ctx, > Sroutine_hash_entry *rt, sp_head *sp, > bool *need_prelocking) = 0; > @@ -433,6 +434,7 @@ class Prelocking_strategy > TABLE_LIST *table_list, bool *need_prelocking) = 0; > virtual bool handle_view(THD *thd, Query_tables_list *prelocking_ctx, > TABLE_LIST *table_list, bool *need_prelocking)= 0; > + virtual bool handle_end(THD *thd) { return 0; }; > }; May be just end()? It looks inconsistent with reset(). Also end() sounds multi-update specific. Isn't it "next nesting level dive"? May be level_end()? Or next_level()? I wonder if handle_table() alone can handle this? Or do we need to open all tables before we can tell which ones are go ing to be updated? ...skip... > +int mysql_multi_update_prepare(THD *thd) > +{ > + LEX *lex= thd->lex; > + TABLE_LIST *table_list= lex->query_tables; > + TABLE_LIST *tl; > + Multiupdate_prelocking_strategy prelocking_strategy; > + uint table_count= lex->table_count; Bad spacing. > + bool original_multiupdate= (thd->lex->sql_command == SQLCOM_UPDATE_MULTI); Do you really need this variable? > + DBUG_ENTER("mysql_multi_update_prepare"); > + > + /* > + Open tables and create derived ones, but do not lock and fill them yet. > + > + During prepare phase acquire only S metadata locks instead of SW locks to > + keep prepare of multi-UPDATE compatible with concurrent LOCK TABLES WRITE > + and global read lock. > + */ > + if (original_multiupdate) > + { > + if (open_tables(thd, &table_list, &table_count, > + thd->stmt_arena->is_stmt_prepare() ? MYSQL_OPEN_FORCE_SHARED_MDL : 0, > + &prelocking_strategy)) > + DBUG_RETURN(TRUE); Wrong indentation. Do we ever enter this branch with is_stmt_prepare()? > + } > + else > + { > + /* following need for prepared statements, to run next time multi-update */ > + thd->lex->sql_command= SQLCOM_UPDATE_MULTI; > + prelocking_strategy.reset(thd); > + if (prelocking_strategy.handle_end(thd)) > DBUG_RETURN(TRUE); > } > In 10.2 we got MDL_SHARED_READ_ONLY, which is acquired by LOCK TABLES ... READ for InnoDB. I guess it is going to be incompatible with multi update tables opened for reading. I don't see this approach making fix for this issue impossible, but still worth to double check. Looks good otherwise. Regards, Sergey

2 3

[Commits] 636b0ae9600: Enable Slave
by sujatha 30 May '19

30 May '19

revision-id: 636b0ae9600a48ba4ff5b5a9f645b38c9a289fad (mariadb-10.1.39-51-g636b0ae9600) parent(s): 78c1be8b6b427e1331bbf0e5d5a24d57dc1477e3 author: Sujatha committer: Sujatha timestamp: 2019-05-30 17:46:40 +0530 message: Enable Slave --- mysql-test/suite/rpl/r/bug.result | 15 +++++++++++++++ mysql-test/suite/rpl/r/rpl_set_statement.result | 6 +++--- mysql-test/suite/rpl/t/bug.test | 22 ++++++++++++++++++++++ sql/sql_parse.cc | 2 +- 4 files changed, 41 insertions(+), 4 deletions(-) diff --git a/mysql-test/suite/rpl/r/bug.result b/mysql-test/suite/rpl/r/bug.result new file mode 100644 index 00000000000..b89dcf7327e --- /dev/null +++ b/mysql-test/suite/rpl/r/bug.result @@ -0,0 +1,15 @@ +include/master-slave.inc +[connection master] +SET STATEMENT storage_engine=Aria FOR CREATE TABLE t (i INT); +SHOW CREATE TABLE t; +Table Create Table +t CREATE TABLE `t` ( + `i` int(11) DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1 +SHOW CREATE TABLE t; +Table Create Table +t CREATE TABLE `t` ( + `i` int(11) DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1 +DROP TABLE t; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_set_statement.result b/mysql-test/suite/rpl/r/rpl_set_statement.result index c9df74b0d3b..d2ed7fe3522 100644 --- a/mysql-test/suite/rpl/r/rpl_set_statement.result +++ b/mysql-test/suite/rpl/r/rpl_set_statement.result @@ -28,7 +28,7 @@ SELECT @@max_join_size; [connection slave] SELECT * FROM t1; a -18446744073709551615 +2 SELECT @@max_join_size; @@max_join_size 18446744073709551615 @@ -104,7 +104,7 @@ SELECT @@sql_mode; [connection slave] SELECT * FROM t2; a - +NO_DIR_IN_CREATE SELECT @@sql_mode; @@sql_mode @@ -131,7 +131,7 @@ SELECT @@max_heap_table_size; [connection slave] SELECT * FROM t1; a -1048576 +16384 SELECT @@max_heap_table_size; @@max_heap_table_size 1048576 diff --git a/mysql-test/suite/rpl/t/bug.test b/mysql-test/suite/rpl/t/bug.test new file mode 100644 index 00000000000..440d39e84c4 --- /dev/null +++ b/mysql-test/suite/rpl/t/bug.test @@ -0,0 +1,22 @@ +--source include/master-slave.inc + + + +SET STATEMENT storage_engine=Aria FOR CREATE TABLE t (i INT); + +SHOW CREATE TABLE t; + + + +--sync_slave_with_master + +SHOW CREATE TABLE t; + + + +--connection master + +DROP TABLE t; + +--source include/rpl_end.inc + diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 9cb65e82321..e9cd869d24c 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -2733,7 +2733,7 @@ mysql_execute_command(THD *thd) thd->get_binlog_format(&orig_binlog_format, &orig_current_stmt_binlog_format); - if (!lex->stmt_var_list.is_empty() && !thd->slave_thread) + if (!lex->stmt_var_list.is_empty()) { Query_arena backup; DBUG_PRINT("info", ("SET STATEMENT %d vars", lex->stmt_var_list.elements));

1 0

[Commits] 78c1be8b6b4: MDEV-18913: typo in error log
by sujatha 30 May '19

30 May '19

revision-id: 78c1be8b6b427e1331bbf0e5d5a24d57dc1477e3 (mariadb-10.1.39-50-g78c1be8b6b4) parent(s): a47464d1c12d773364e78f50090b08484fe76129 author: Sujatha committer: Sujatha timestamp: 2019-05-30 12:11:57 +0530 message: MDEV-18913: typo in error log Problem: ======== Following typo in error log: 2019-03-13 15:58:10 0 [Note] Reading of all Master_info entries succeded Should be 'succeeded' Fix: === Fixed the typo with the right word 'succeeded'. --- sql/rpl_mi.cc | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/sql/rpl_mi.cc b/sql/rpl_mi.cc index 58131ff771c..70e60b1d4ad 100644 --- a/sql/rpl_mi.cc +++ b/sql/rpl_mi.cc @@ -1233,7 +1233,7 @@ bool Master_info_index::init_all_master_info() if (!err_num) // No Error on read Master_info { if (global_system_variables.log_warnings > 1) - sql_print_information("Reading of all Master_info entries succeded"); + sql_print_information("Reading of all Master_info entries succeeded"); DBUG_RETURN(0); } if (succ_num) // Have some Error and some Success

1 0