- commits - lists.mariadb.org

[Commits] b000d6952f6: MDEV-23221: A subquery causes crash
by Sergei Petrunia 24 Jul '20

24 Jul '20

revision-id: b000d6952f655f837bc8c241add196f80a997e63 (mariadb-10.1.43-225-gb000d6952f6) parent(s): 4b97f14a3d692721a089afe8050a00fcfc230816 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2020-07-24 22:32:04 +0300 message: MDEV-23221: A subquery causes crash * Fix the crash: IN-to-EXISTS rewrite causes an error (and so JOIN::optimize() fails with an error, too), don't call update_used_tables(). Terminate the query execution instead. * Fix the cause of the error in the IN-to-EXISTS rewrite: don't do the rewrite if doing it will cause an error of this kind: This version of MariaDB doesn't yet support 'SUBQUERY in ROW in left expression of IN/ALL/ANY' * Fix another issue exposed by this testcase: JOIN::setup_subquery_caches() may be invoked before any select has saved its query plan, and will crash because none of the SELECTs has called create_explain_query_if_not_exists() to create the Explain Data Structure for this SELECT. TODO: When merging this to 10.2, remove the poorly-placed call to create_explain_query_if_not_exists made by fix for M_D_E_V-16153 --- mysql-test/r/subselect_exists2in.result | 70 +++++++++++++++++++++++++++++++++ mysql-test/t/subselect_exists2in.test | 48 ++++++++++++++++++++++ sql/item_subselect.cc | 20 +++++++++- sql/sql_lex.cc | 3 +- sql/sql_select.cc | 2 +- 5 files changed, 139 insertions(+), 4 deletions(-) diff --git a/mysql-test/r/subselect_exists2in.result b/mysql-test/r/subselect_exists2in.result index 18cd0567989..5a66aa2cd02 100644 --- a/mysql-test/r/subselect_exists2in.result +++ b/mysql-test/r/subselect_exists2in.result @@ -972,4 +972,74 @@ id DROP PROCEDURE p1; DROP TABLE t1; # End of 10.0 tests +# +# MDEV-23221: A subquery causes crash +# +create table t1 ( +location_code varchar(10), +country_id varchar(10) +); +insert into t1 values ('HKG', 'HK'); +insert into t1 values ('NYC', 'US'); +insert into t1 values ('LAX', 'US'); +create table t2 ( +container_id varchar(10), +cntr_activity_type varchar(10), +cntr_dest varchar(10) +); +insert into t2 values ('AAAA1111', 'VSL', 'NYC'); +insert into t2 values ('AAAA1111', 'CUV', 'NYC'); +insert into t2 values ('BBBB2222', 'VSL', 'LAX'); +insert into t2 values ('BBBB2222', 'XYZ', 'LAX'); +# Must not crash or return an error: +select +(select country_id from t1 where location_code = cl1.cntr_dest) as dest_cntry, +(select +max(container_id) +from t2 as cl2 +where +cl2.container_id = cl1.container_id and +cl2.cntr_activity_type = 'CUV' and +exists (select location_code +from t1 +where +location_code = cl2.cntr_dest and +country_id = dest_cntry) +) as CUV +from +t2 cl1; +dest_cntry CUV +US AAAA1111 +US AAAA1111 +US NULL +US NULL +prepare s from "select +(select country_id from t1 where location_code = cl1.cntr_dest) as dest_cntry, +(select +max(container_id) +from t2 as cl2 +where +cl2.container_id = cl1.container_id and +cl2.cntr_activity_type = 'CUV' and +exists (select location_code +from t1 +where +location_code = cl2.cntr_dest and +country_id = dest_cntry) +) as CUV +from +t2 cl1"; +execute s; +dest_cntry CUV +US AAAA1111 +US AAAA1111 +US NULL +US NULL +execute s; +dest_cntry CUV +US AAAA1111 +US AAAA1111 +US NULL +US NULL +drop table t1,t2; set optimizer_switch=default; diff --git a/mysql-test/t/subselect_exists2in.test b/mysql-test/t/subselect_exists2in.test index 5a8ddb3612f..877475f97ed 100644 --- a/mysql-test/t/subselect_exists2in.test +++ b/mysql-test/t/subselect_exists2in.test @@ -828,5 +828,53 @@ DROP TABLE t1; --echo # End of 10.0 tests +--echo # +--echo # MDEV-23221: A subquery causes crash +--echo # +create table t1 ( +location_code varchar(10), +country_id varchar(10) +); +insert into t1 values ('HKG', 'HK'); +insert into t1 values ('NYC', 'US'); +insert into t1 values ('LAX', 'US'); + +create table t2 ( +container_id varchar(10), +cntr_activity_type varchar(10), +cntr_dest varchar(10) +); +insert into t2 values ('AAAA1111', 'VSL', 'NYC'); +insert into t2 values ('AAAA1111', 'CUV', 'NYC'); +insert into t2 values ('BBBB2222', 'VSL', 'LAX'); +insert into t2 values ('BBBB2222', 'XYZ', 'LAX'); + +let $query= +select + (select country_id from t1 where location_code = cl1.cntr_dest) as dest_cntry, + (select + max(container_id) + from t2 as cl2 + where + cl2.container_id = cl1.container_id and + cl2.cntr_activity_type = 'CUV' and + exists (select location_code + from t1 + where + location_code = cl2.cntr_dest and + country_id = dest_cntry) + ) as CUV +from + t2 cl1; + +--echo # Must not crash or return an error: +eval $query; + +eval prepare s from "$query"; +execute s; +execute s; + +drop table t1,t2; + #restore defaults set optimizer_switch=default; diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index b5f3cd052e2..0b28c15027d 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -2727,6 +2727,8 @@ bool Item_exists_subselect::select_prepare_to_be_in() Check if 'func' is an equality in form "inner_table.column = outer_expr" @param func Expression to check + @param allow_subselect If true, the outer_expr part can have a subquery + If false, it cannot. @param local_field OUT Return "inner_table.column" here @param outer_expr OUT Return outer_expr here @@ -2734,6 +2736,7 @@ bool Item_exists_subselect::select_prepare_to_be_in() */ static bool check_equality_for_exist2in(Item_func *func, + bool allow_subselect, Item_ident **local_field, Item **outer_exp) { @@ -2744,7 +2747,8 @@ static bool check_equality_for_exist2in(Item_func *func, args= func->arguments(); if (args[0]->real_type() == Item::FIELD_ITEM && args[0]->all_used_tables() != OUTER_REF_TABLE_BIT && - args[1]->all_used_tables() == OUTER_REF_TABLE_BIT) + args[1]->all_used_tables() == OUTER_REF_TABLE_BIT && + (allow_subselect || !args[1]->has_subquery())) { /* It is Item_field or Item_direct_view_ref) */ DBUG_ASSERT(args[0]->type() == Item::FIELD_ITEM || @@ -2755,7 +2759,8 @@ static bool check_equality_for_exist2in(Item_func *func, } else if (args[1]->real_type() == Item::FIELD_ITEM && args[1]->all_used_tables() != OUTER_REF_TABLE_BIT && - args[0]->all_used_tables() == OUTER_REF_TABLE_BIT) + args[0]->all_used_tables() == OUTER_REF_TABLE_BIT && + (allow_subselect || !args[0]->has_subquery())) { /* It is Item_field or Item_direct_view_ref) */ DBUG_ASSERT(args[1]->type() == Item::FIELD_ITEM || @@ -2784,6 +2789,13 @@ typedef struct st_eq_field_outer outer1=inner_tbl1.col1 AND ... AND outer2=inner_tbl1.col2 AND remainder_cond + if there is just one outer_expr=inner_expr pair, then outer_expr can have a + subselect in it. If there are many such pairs, then none of outer_expr can + have a subselect in it. If we allow this, the query will fail with an error: + + This version of MariaDB doesn't yet support 'SUBQUERY in ROW in left + expression of IN/ALL/ANY' + @param conds Condition to be checked @parm result Array to collect EQ_FIELD_OUTER elements describing inner-vs-outer equalities the function has found. @@ -2801,14 +2813,17 @@ static bool find_inner_outer_equalities(Item **conds, { List_iterator<Item> li(*((Item_cond*)*conds)->argument_list()); Item *item; + bool allow_subselect= true; while ((item= li++)) { if (item->type() == Item::FUNC_ITEM && check_equality_for_exist2in((Item_func *)item, + allow_subselect, &element.local_field, &element.outer_exp)) { found= TRUE; + allow_subselect= false; element.eq_ref= li.ref(); if (result.append(element)) goto alloc_err; @@ -2817,6 +2832,7 @@ static bool find_inner_outer_equalities(Item **conds, } else if ((*conds)->type() == Item::FUNC_ITEM && check_equality_for_exist2in((Item_func *)*conds, + true, &element.local_field, &element.outer_exp)) { diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 02c0df97cf3..e863308159e 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -3802,7 +3802,8 @@ bool st_select_lex::optimize_unflattened_subqueries(bool const_only) sl->options|= SELECT_DESCRIBE; inner_join->select_options|= SELECT_DESCRIBE; } - res= inner_join->optimize(); + if ((res= inner_join->optimize())) + return TRUE; if (!inner_join->cleaned) sl->update_used_tables(); sl->update_correlated_cache(); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 647dee80188..4c6e87e4f27 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1055,11 +1055,11 @@ int JOIN::optimize() if (optimization_state != JOIN::NOT_OPTIMIZED) return FALSE; optimization_state= JOIN::OPTIMIZATION_IN_PROGRESS; + create_explain_query_if_not_exists(thd->lex, thd->mem_root); int res= optimize_inner(); if (!res && have_query_plan != QEP_DELETED) { - create_explain_query_if_not_exists(thd->lex, thd->mem_root); have_query_plan= QEP_AVAILABLE; save_explain_data(thd->lex->explain, false /* can overwrite */, need_tmp,

1 0

[Commits] 44faa0ffb79: MDEV-23221: A subquery causes crash
by Sergei Petrunia 24 Jul '20

24 Jul '20

revision-id: 44faa0ffb79786c635c5ee4e21713541804e3963 (mariadb-10.1.43-225-g44faa0ffb79) parent(s): 4b97f14a3d692721a089afe8050a00fcfc230816 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2020-07-24 22:31:29 +0300 message: MDEV-23221: A subquery causes crash * Fix the crash: IN-to-EXISTS rewrite causes an error (and so JOIN::optimize() fails with an error, too), don't call update_used_tables(). Terminate the query execution instead. * Fix the cause of the error in the IN-to-EXISTS rewrite: don't do the rewrite if doing it will cause an error of this kind: This version of MariaDB doesn't yet support 'SUBQUERY in ROW in left expression of IN/ALL/ANY' * Fix another issue exposed by this testcase: JOIN::setup_subquery_caches() may be invoked before any select has saved its query plan, and will crash because none of the SELECTs has called create_explain_query_if_not_exists() to create the Explain Data Structure for this SELECT. TODO: When merging this to 10.2, remove the poorly-placed call to create_explain_query_if_not_exists made by fix for M_D_E_V-16153 --- mysql-test/r/subselect_exists2in.result | 70 +++++++++++++++++++++++++++++++++ mysql-test/t/subselect_exists2in.test | 48 ++++++++++++++++++++++ sql/item_subselect.cc | 20 +++++++++- sql/sql_lex.cc | 3 +- sql/sql_select.cc | 2 + 5 files changed, 140 insertions(+), 3 deletions(-) diff --git a/mysql-test/r/subselect_exists2in.result b/mysql-test/r/subselect_exists2in.result index 18cd0567989..5a66aa2cd02 100644 --- a/mysql-test/r/subselect_exists2in.result +++ b/mysql-test/r/subselect_exists2in.result @@ -972,4 +972,74 @@ id DROP PROCEDURE p1; DROP TABLE t1; # End of 10.0 tests +# +# MDEV-23221: A subquery causes crash +# +create table t1 ( +location_code varchar(10), +country_id varchar(10) +); +insert into t1 values ('HKG', 'HK'); +insert into t1 values ('NYC', 'US'); +insert into t1 values ('LAX', 'US'); +create table t2 ( +container_id varchar(10), +cntr_activity_type varchar(10), +cntr_dest varchar(10) +); +insert into t2 values ('AAAA1111', 'VSL', 'NYC'); +insert into t2 values ('AAAA1111', 'CUV', 'NYC'); +insert into t2 values ('BBBB2222', 'VSL', 'LAX'); +insert into t2 values ('BBBB2222', 'XYZ', 'LAX'); +# Must not crash or return an error: +select +(select country_id from t1 where location_code = cl1.cntr_dest) as dest_cntry, +(select +max(container_id) +from t2 as cl2 +where +cl2.container_id = cl1.container_id and +cl2.cntr_activity_type = 'CUV' and +exists (select location_code +from t1 +where +location_code = cl2.cntr_dest and +country_id = dest_cntry) +) as CUV +from +t2 cl1; +dest_cntry CUV +US AAAA1111 +US AAAA1111 +US NULL +US NULL +prepare s from "select +(select country_id from t1 where location_code = cl1.cntr_dest) as dest_cntry, +(select +max(container_id) +from t2 as cl2 +where +cl2.container_id = cl1.container_id and +cl2.cntr_activity_type = 'CUV' and +exists (select location_code +from t1 +where +location_code = cl2.cntr_dest and +country_id = dest_cntry) +) as CUV +from +t2 cl1"; +execute s; +dest_cntry CUV +US AAAA1111 +US AAAA1111 +US NULL +US NULL +execute s; +dest_cntry CUV +US AAAA1111 +US AAAA1111 +US NULL +US NULL +drop table t1,t2; set optimizer_switch=default; diff --git a/mysql-test/t/subselect_exists2in.test b/mysql-test/t/subselect_exists2in.test index 5a8ddb3612f..877475f97ed 100644 --- a/mysql-test/t/subselect_exists2in.test +++ b/mysql-test/t/subselect_exists2in.test @@ -828,5 +828,53 @@ DROP TABLE t1; --echo # End of 10.0 tests +--echo # +--echo # MDEV-23221: A subquery causes crash +--echo # +create table t1 ( +location_code varchar(10), +country_id varchar(10) +); +insert into t1 values ('HKG', 'HK'); +insert into t1 values ('NYC', 'US'); +insert into t1 values ('LAX', 'US'); + +create table t2 ( +container_id varchar(10), +cntr_activity_type varchar(10), +cntr_dest varchar(10) +); +insert into t2 values ('AAAA1111', 'VSL', 'NYC'); +insert into t2 values ('AAAA1111', 'CUV', 'NYC'); +insert into t2 values ('BBBB2222', 'VSL', 'LAX'); +insert into t2 values ('BBBB2222', 'XYZ', 'LAX'); + +let $query= +select + (select country_id from t1 where location_code = cl1.cntr_dest) as dest_cntry, + (select + max(container_id) + from t2 as cl2 + where + cl2.container_id = cl1.container_id and + cl2.cntr_activity_type = 'CUV' and + exists (select location_code + from t1 + where + location_code = cl2.cntr_dest and + country_id = dest_cntry) + ) as CUV +from + t2 cl1; + +--echo # Must not crash or return an error: +eval $query; + +eval prepare s from "$query"; +execute s; +execute s; + +drop table t1,t2; + #restore defaults set optimizer_switch=default; diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index b5f3cd052e2..0b28c15027d 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -2727,6 +2727,8 @@ bool Item_exists_subselect::select_prepare_to_be_in() Check if 'func' is an equality in form "inner_table.column = outer_expr" @param func Expression to check + @param allow_subselect If true, the outer_expr part can have a subquery + If false, it cannot. @param local_field OUT Return "inner_table.column" here @param outer_expr OUT Return outer_expr here @@ -2734,6 +2736,7 @@ bool Item_exists_subselect::select_prepare_to_be_in() */ static bool check_equality_for_exist2in(Item_func *func, + bool allow_subselect, Item_ident **local_field, Item **outer_exp) { @@ -2744,7 +2747,8 @@ static bool check_equality_for_exist2in(Item_func *func, args= func->arguments(); if (args[0]->real_type() == Item::FIELD_ITEM && args[0]->all_used_tables() != OUTER_REF_TABLE_BIT && - args[1]->all_used_tables() == OUTER_REF_TABLE_BIT) + args[1]->all_used_tables() == OUTER_REF_TABLE_BIT && + (allow_subselect || !args[1]->has_subquery())) { /* It is Item_field or Item_direct_view_ref) */ DBUG_ASSERT(args[0]->type() == Item::FIELD_ITEM || @@ -2755,7 +2759,8 @@ static bool check_equality_for_exist2in(Item_func *func, } else if (args[1]->real_type() == Item::FIELD_ITEM && args[1]->all_used_tables() != OUTER_REF_TABLE_BIT && - args[0]->all_used_tables() == OUTER_REF_TABLE_BIT) + args[0]->all_used_tables() == OUTER_REF_TABLE_BIT && + (allow_subselect || !args[0]->has_subquery())) { /* It is Item_field or Item_direct_view_ref) */ DBUG_ASSERT(args[1]->type() == Item::FIELD_ITEM || @@ -2784,6 +2789,13 @@ typedef struct st_eq_field_outer outer1=inner_tbl1.col1 AND ... AND outer2=inner_tbl1.col2 AND remainder_cond + if there is just one outer_expr=inner_expr pair, then outer_expr can have a + subselect in it. If there are many such pairs, then none of outer_expr can + have a subselect in it. If we allow this, the query will fail with an error: + + This version of MariaDB doesn't yet support 'SUBQUERY in ROW in left + expression of IN/ALL/ANY' + @param conds Condition to be checked @parm result Array to collect EQ_FIELD_OUTER elements describing inner-vs-outer equalities the function has found. @@ -2801,14 +2813,17 @@ static bool find_inner_outer_equalities(Item **conds, { List_iterator<Item> li(*((Item_cond*)*conds)->argument_list()); Item *item; + bool allow_subselect= true; while ((item= li++)) { if (item->type() == Item::FUNC_ITEM && check_equality_for_exist2in((Item_func *)item, + allow_subselect, &element.local_field, &element.outer_exp)) { found= TRUE; + allow_subselect= false; element.eq_ref= li.ref(); if (result.append(element)) goto alloc_err; @@ -2817,6 +2832,7 @@ static bool find_inner_outer_equalities(Item **conds, } else if ((*conds)->type() == Item::FUNC_ITEM && check_equality_for_exist2in((Item_func *)*conds, + true, &element.local_field, &element.outer_exp)) { diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 02c0df97cf3..e863308159e 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -3802,7 +3802,8 @@ bool st_select_lex::optimize_unflattened_subqueries(bool const_only) sl->options|= SELECT_DESCRIBE; inner_join->select_options|= SELECT_DESCRIBE; } - res= inner_join->optimize(); + if ((res= inner_join->optimize())) + return TRUE; if (!inner_join->cleaned) sl->update_used_tables(); sl->update_correlated_cache(); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 647dee80188..0e9be5a5267 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1182,6 +1182,8 @@ JOIN::optimize_inner() } #endif + create_explain_query_if_not_exists(thd->lex, thd->mem_root); + SELECT_LEX *sel= select_lex; if (sel->first_cond_optimization) {

1 0

[Commits] 9b999e79a35: MDEV-23108: Point in time recovery of binary log fails when sql_mode=ORACLE
by sujatha 24 Jul '20

24 Jul '20

revision-id: 9b999e79a358de2c86ae44fe83cf6c62cff1d2ef (mariadb-10.3.21-180-g9b999e79a35) parent(s): 73aa31fbfd793bdb597bb19c52118ab4e637f4bc author: Sujatha committer: Sujatha timestamp: 2020-07-17 12:23:32 +0530 message: MDEV-23108: Point in time recovery of binary log fails when sql_mode=ORACLE Problem: ======== During point in time recovery of binary log syntax error is reported for BEGIN statement and recovery fails. Analysis: ========= In MariaDB 10.3 and later, setting the sql_mode system variable to Oracle allows the server to understand a subset of Oracle's PL/SQL language. When sql_mode=ORACLE is set, it switches the parser from the MariaDB parser to Oracle compatible parser. With this change 'BEGIN' is not considered as 'START TRANSACTION'. Hence the syntax error is reported. Fix: === Add a new option to 'mysqlbinlog' tool named 'sql_mode_oracle'. When 'sql_mode_oracle' option is specified 'BEGIN' statements will be replaced with 'START TRANSACTION' in the mysqlbinlog output. --- client/mysqlbinlog.cc | 7 +- .../compat/oracle/r/binlog_ptr_mysqlbinlog.result | 100 +++++++++++++++++ .../oracle/t/binlog_ptr_mysqlbinlog-master.opt | 1 + .../compat/oracle/t/binlog_ptr_mysqlbinlog.test | 120 +++++++++++++++++++++ sql/log_event.cc | 15 +-- sql/log_event.h | 1 + 6 files changed, 237 insertions(+), 7 deletions(-) diff --git a/client/mysqlbinlog.cc b/client/mysqlbinlog.cc index a732a6ab8c3..ad995629272 100644 --- a/client/mysqlbinlog.cc +++ b/client/mysqlbinlog.cc @@ -145,6 +145,7 @@ static MYSQL* mysql = NULL; static const char* dirname_for_local_load= 0; static bool opt_skip_annotate_row_events= 0; +static my_bool opt_sql_mode_oracle= 0; static my_bool opt_flashback; #ifdef WHEN_FLASHBACK_REVIEW_READY static my_bool opt_flashback_review; @@ -1045,7 +1046,8 @@ Exit_status process_event(PRINT_EVENT_INFO *print_event_info, Log_event *ev, DBUG_ENTER("process_event"); Exit_status retval= OK_CONTINUE; IO_CACHE *const head= &print_event_info->head_cache; - + /* Propogate sql_mode_oracle settings to event */ + ev->is_sql_mode_oracle= opt_sql_mode_oracle; /* Bypass flashback settings to event */ ev->is_flashback= opt_flashback; #ifdef WHEN_FLASHBACK_REVIEW_READY @@ -1704,6 +1706,9 @@ static struct my_option my_options[] = "use --base64-output=never", &short_form, &short_form, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0}, + {"sql_mode_oracle", 0, "Converts BEGIN statement to 'ORALCE' " + "sql_mode compatible START TRANSACTION.", &opt_sql_mode_oracle, + &opt_sql_mode_oracle, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0}, {"socket", 'S', "The socket file to use for connection.", &sock, &sock, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0}, diff --git a/mysql-test/suite/compat/oracle/r/binlog_ptr_mysqlbinlog.result b/mysql-test/suite/compat/oracle/r/binlog_ptr_mysqlbinlog.result new file mode 100644 index 00000000000..3080a4b69a0 --- /dev/null +++ b/mysql-test/suite/compat/oracle/r/binlog_ptr_mysqlbinlog.result @@ -0,0 +1,100 @@ +SET @@SQL_MODE = 'ORACLE'; +########################################################################## +# Test verifies Gtid_log_event/Xid_log_event specific print # +########################################################################## +CREATE TABLE tm (f INT) ENGINE=MYISAM; +INSERT INTO tm VALUES (10); +CREATE TABLE t(f INT) ENGINE=INNODB; +INSERT INTO t VALUES (10); +CREATE OR REPLACE PROCEDURE simpleproc (param1 OUT INT) AS +BEGIN +SELECT COUNT(*) INTO param1 FROM t; +END; +/ +CREATE FUNCTION f1 RETURN INT +AS +BEGIN +RETURN 10; +END; +/ +FLUSH LOGS; +########################################################################## +# Delete data from master so that it can be restored from binlog # +########################################################################## +DROP FUNCTION f1; +DROP PROCEDURE simpleproc; +DROP TABLE tm; +DROP TABLE t; +########################################################################## +# Post recovery using mysqlbinlog --sql-mode-oracle # +########################################################################## +SHOW TABLES; +Tables_in_test +t +tm +SELECT * FROM tm; +f +10 +SELECT * FROM t; +f +10 +SELECT f1(); +f1() +10 +CALL simpleproc(@a); +SELECT @a; +@a +1 +"***** Clean Up *****" +DROP TABLE t,tm; +DROP PROCEDURE simpleproc; +DROP FUNCTION f1; +RESET MASTER; +########################################################################## +# Test verifies Gtid_log_event/Xid_log_event/Qery_log_event # +# specific print along with flashback option # +########################################################################## +CREATE TABLE tm(f INT) ENGINE=MYISAM; +INSERT INTO tm VALUES (10); +INSERT INTO tm VALUES (20); +CREATE TABLE t(f INT) ENGINE=INNODB; +INSERT INTO t VALUES (10); +INSERT INTO t VALUES (20); +########################################################################## +# Initial data # +########################################################################## +SELECT * FROM tm; +f +10 +20 +SELECT * FROM t; +f +10 +20 +FLUSH LOGS; +DELETE FROM tm WHERE f=20; +DELETE FROM t WHERE f=20; +FLUSH LOGS; +########################################################################## +# Data after deletion # +########################################################################## +SELECT * FROM tm; +f +10 +SELECT * FROM t; +f +10 +FOUND 2 /START TRANSACTION/ in test_sql_mode.sql +########################################################################## +# Data after recovery using flashback, sql-mode-oracle # +########################################################################## +SELECT * FROM tm; +f +10 +20 +SELECT * FROM t; +f +10 +20 +"***** Clean Up *****" +DROP TABLE t,tm; diff --git a/mysql-test/suite/compat/oracle/t/binlog_ptr_mysqlbinlog-master.opt b/mysql-test/suite/compat/oracle/t/binlog_ptr_mysqlbinlog-master.opt new file mode 100644 index 00000000000..8f0cc182f51 --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/binlog_ptr_mysqlbinlog-master.opt @@ -0,0 +1 @@ +--flashback diff --git a/mysql-test/suite/compat/oracle/t/binlog_ptr_mysqlbinlog.test b/mysql-test/suite/compat/oracle/t/binlog_ptr_mysqlbinlog.test new file mode 100644 index 00000000000..07a1b5c4fdb --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/binlog_ptr_mysqlbinlog.test @@ -0,0 +1,120 @@ +# ==== Purpose ==== +# +# Test verifies that a binary log which was generated by setting +# 'sql_mod=ORACLE' can be succefully used for point in time recovery with +# following options. +# 1) mysqlbinlog --sql-mode-oracle +# 2) mysqlbinlog --flashback --sql-mode-oracle +# +# BEGIN statement is printed in three places +# 1) "Gtid_log_event::print" +# 2) "Xid_log_event::print" if flashback is enabled +# 3) "Query_log_event::print" if flashback is enabled and engine is +# non-transacional. +# +# Test verifies all these cases. +# +# ==== References ==== +# +# MDEV-23108: Point in time recovery of binary log fails when sql_mode=ORACLE +# +--source include/have_log_bin.inc +--source include/have_innodb.inc + +let $MYSQLD_DATADIR= `select @@datadir`; +SET @@SQL_MODE = 'ORACLE'; + +--echo ########################################################################## +--echo # Test verifies Gtid_log_event/Xid_log_event specific print # +--echo ########################################################################## +CREATE TABLE tm (f INT) ENGINE=MYISAM; +INSERT INTO tm VALUES (10); + +CREATE TABLE t(f INT) ENGINE=INNODB; +INSERT INTO t VALUES (10); + +DELIMITER /; +CREATE OR REPLACE PROCEDURE simpleproc (param1 OUT INT) AS + BEGIN + SELECT COUNT(*) INTO param1 FROM t; + END; +/ +CREATE FUNCTION f1 RETURN INT +AS +BEGIN + RETURN 10; +END; +/ +DELIMITER ;/ + +FLUSH LOGS; +--echo ########################################################################## +--echo # Delete data from master so that it can be restored from binlog # +--echo ########################################################################## +DROP FUNCTION f1; +DROP PROCEDURE simpleproc; +DROP TABLE tm; +DROP TABLE t; + +--exec $MYSQL_BINLOG --sql-mode-oracle $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/test.sql +--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/test.sql + +--echo ########################################################################## +--echo # Post recovery using mysqlbinlog --sql-mode-oracle # +--echo ########################################################################## +SHOW TABLES; +SELECT * FROM tm; +SELECT * FROM t; +--horizontal_results +SELECT f1(); +CALL simpleproc(@a); +SELECT @a; + +--echo "***** Clean Up *****" +DROP TABLE t,tm; +DROP PROCEDURE simpleproc; +DROP FUNCTION f1; +--remove_file $MYSQLTEST_VARDIR/tmp/test.sql +RESET MASTER; + +--echo ########################################################################## +--echo # Test verifies Gtid_log_event/Xid_log_event/Qery_log_event # +--echo # specific print along with flashback option # +--echo ########################################################################## +CREATE TABLE tm(f INT) ENGINE=MYISAM; +INSERT INTO tm VALUES (10); +INSERT INTO tm VALUES (20); +CREATE TABLE t(f INT) ENGINE=INNODB; +INSERT INTO t VALUES (10); +INSERT INTO t VALUES (20); +--echo ########################################################################## +--echo # Initial data # +--echo ########################################################################## +SELECT * FROM tm; +SELECT * FROM t; +FLUSH LOGS; +DELETE FROM tm WHERE f=20; +DELETE FROM t WHERE f=20; +FLUSH LOGS; + +--echo ########################################################################## +--echo # Data after deletion # +--echo ########################################################################## +SELECT * FROM tm; +SELECT * FROM t; +--exec $MYSQL_BINLOG --flashback --sql-mode-oracle $MYSQLD_DATADIR/master-bin.000002 > $MYSQLTEST_VARDIR/tmp/test_sql_mode.sql + +--let SEARCH_FILE=$MYSQLTEST_VARDIR/tmp/test_sql_mode.sql +--let SEARCH_PATTERN=START TRANSACTION +--source include/search_pattern_in_file.inc +--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/test_sql_mode.sql + +--echo ########################################################################## +--echo # Data after recovery using flashback, sql-mode-oracle # +--echo ########################################################################## +SELECT * FROM tm; +SELECT * FROM t; + +--echo "***** Clean Up *****" +DROP TABLE t,tm; +--remove_file $MYSQLTEST_VARDIR/tmp/test_sql_mode.sql diff --git a/sql/log_event.cc b/sql/log_event.cc index 57a39085e86..e06e6a1b1d6 100644 --- a/sql/log_event.cc +++ b/sql/log_event.cc @@ -5358,8 +5358,9 @@ bool Query_log_event::print(FILE* file, PRINT_EVENT_INFO* print_event_info) } else if (strcmp("COMMIT", query) == 0) { - if (my_b_write(&cache, (uchar*) "BEGIN", 5) || - my_b_printf(&cache, "\n%s\n", print_event_info->delimiter)) + if (my_b_printf(&cache, is_sql_mode_oracle ? + "START TRANSACTION\n%s\n" : "BEGIN\n%s\n" , + print_event_info->delimiter)) goto err; } } @@ -8254,9 +8255,10 @@ Gtid_log_event::print(FILE *file, PRINT_EVENT_INFO *print_event_info) goto err; } if (!(flags2 & FL_STANDALONE)) - if (my_b_printf(&cache, is_flashback ? "COMMIT\n%s\n" : "BEGIN\n%s\n", print_event_info->delimiter)) + if (my_b_printf(&cache, is_flashback ? "COMMIT\n%s\n" : + (is_sql_mode_oracle ? "START TRANSACTION\n%s\n" : + "BEGIN\n%s\n"), print_event_info->delimiter)) goto err; - return cache.flush_data(); err: return 1; @@ -8937,8 +8939,9 @@ bool Xid_log_event::print(FILE* file, PRINT_EVENT_INFO* print_event_info) my_b_printf(&cache, "\tXid = %s\n", buf)) goto err; } - if (my_b_printf(&cache, is_flashback ? "BEGIN%s\n" : "COMMIT%s\n", - print_event_info->delimiter)) + if (my_b_printf(&cache, is_flashback ? (is_sql_mode_oracle ? + "START TRANSACTION\n%s\n" : "BEGIN\n%s\n") : + "COMMIT\n%s\n", print_event_info->delimiter)) goto err; return cache.flush_data(); diff --git a/sql/log_event.h b/sql/log_event.h index 73809953d70..66e29579fbc 100644 --- a/sql/log_event.h +++ b/sql/log_event.h @@ -1270,6 +1270,7 @@ class Log_event /* The following code used for Flashback */ #ifdef MYSQL_CLIENT + my_bool is_sql_mode_oracle; my_bool is_flashback; my_bool need_flashback_review; String output_buf; // Storing the event output

2 4

[Commits] 15bba6a9f04: MDEV-23216: LONGTEXT column with collation doesn't sort
by Varun 22 Jul '20

22 Jul '20

revision-id: 15bba6a9f04a36b603bfa67a791b1753bbae1222 (mariadb-10.5.2-576-g15bba6a9f04) parent(s): 92014bd1c611fd27c487bcc47a7521baa435214c author: Varun Gupta committer: Varun Gupta timestamp: 2020-07-22 20:41:12 +0530 message: MDEV-23216: LONGTEXT column with collation doesn't sort An overflow was happening with LONGTEXT columns, when the length was converted to the length in the strxfrm form (mem-comparable keys). Introduced a function to truncate the length to the max_sort_length before calculating the length of the strxfrm form. --- mysql-test/main/order_by.result | 12 ++++++++++++ mysql-test/main/order_by.test | 10 ++++++++++ sql/filesort.cc | 16 +++++++++++----- sql/sql_class.h | 1 + 4 files changed, 34 insertions(+), 5 deletions(-) diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result index dd04ae42e5a..ec5ce661470 100644 --- a/mysql-test/main/order_by.result +++ b/mysql-test/main/order_by.result @@ -4079,4 +4079,16 @@ COUNT(DISTINCT a) 34 SET @@tmp_memory_table_size= @save_tmp_memory_table_size; DROP TABLE t1; +# +# MDEV-23216: LONGTEXT column with collation doesn't sort +# +CREATE TABLE t1 (a LONGTEXT COLLATE utf8mb4_swedish_ci); +INSERT INTO t1 VALUES ('A'),('Z'),('B'),('Y'); +SELECT * FROM t1 ORDER BY a; +a +A +B +Y +Z +DROP TABLE t1; # End of 10.5 tests diff --git a/mysql-test/main/order_by.test b/mysql-test/main/order_by.test index 8398246cae9..51a1a6582ac 100644 --- a/mysql-test/main/order_by.test +++ b/mysql-test/main/order_by.test @@ -2522,5 +2522,15 @@ SELECT COUNT(DISTINCT a) FROM t1; SET @@tmp_memory_table_size= @save_tmp_memory_table_size; DROP TABLE t1; +--echo # +--echo # MDEV-23216: LONGTEXT column with collation doesn't sort +--echo # + +CREATE TABLE t1 (a LONGTEXT COLLATE utf8mb4_swedish_ci); +INSERT INTO t1 VALUES ('A'),('Z'),('B'),('Y'); +SELECT * FROM t1 ORDER BY a; + +DROP TABLE t1; + --echo # End of 10.5 tests diff --git a/sql/filesort.cc b/sql/filesort.cc index 7a3f16e0ee4..99fe1019159 100644 --- a/sql/filesort.cc +++ b/sql/filesort.cc @@ -2102,9 +2102,7 @@ Type_handler_string_result::sort_length(THD *thd, SORT_FIELD_ATTR *sortorder) const { CHARSET_INFO *cs; - sortorder->length= item->max_length; - set_if_smaller(sortorder->length, thd->variables.max_sort_length); - sortorder->original_length= item->max_length; + sortorder->set_length_and_original_length(thd, item->max_length); if (use_strnxfrm((cs= item->collation.collation))) { @@ -2211,9 +2209,9 @@ sortlength(THD *thd, Sort_keys *sort_keys, bool *allow_packing_for_sortkeys) { Field *field= sortorder->field; CHARSET_INFO *cs= sortorder->field->sort_charset(); - sortorder->length= sortorder->field->sort_length(); + sortorder->set_length_and_original_length(thd, field->sort_length()); + sortorder->suffix_length= sortorder->field->sort_suffix_length(); - sortorder->original_length= sortorder->length; sortorder->type= field->is_packable() ? SORT_FIELD_ATTR::VARIABLE_SIZE : SORT_FIELD_ATTR::FIXED_SIZE; @@ -2749,6 +2747,14 @@ bool SORT_FIELD_ATTR::check_if_packing_possible(THD *thd) const } +void SORT_FIELD_ATTR::set_length_and_original_length(THD *thd, uint length_arg) +{ + length= length_arg; + set_if_smaller(length, thd->variables.max_sort_length); + original_length= length_arg; +} + + /* Compare function used for packing sort keys */ diff --git a/sql/sql_class.h b/sql/sql_class.h index 2e388ef7d72..a466ce5bc90 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -6455,6 +6455,7 @@ struct SORT_FIELD_ATTR uchar *b, size_t *b_len); bool check_if_packing_possible(THD *thd) const; bool is_variable_sized() { return type == VARIABLE_SIZE; } + void set_length_and_original_length(THD *thd, uint length_arg); };

1 0

[Commits] 75c244fc2e9: MDEV-17066: Bytes lost or Assertion `status_var.local_memory_used == 0 after DELETE with subquery with ROLLUP
by Varun 22 Jul '20

22 Jul '20

revision-id: 75c244fc2e9d964f114f270cd0065fc879e04cd3 (mariadb-10.1.43-219-g75c244fc2e9) parent(s): 62d73df6b270cc94ba577e96d3bf325170f306fe author: Varun Gupta committer: Varun Gupta timestamp: 2020-07-22 16:02:39 +0530 message: MDEV-17066: Bytes lost or Assertion `status_var.local_memory_used == 0 after DELETE with subquery with ROLLUP The issue here is when records are read from the temp table via a cache(rr_from_cache), the cache is allocated for each execution of the correlated subquery. But the deallocation of the cache only happened after the entire execution of the parent select was done. The fix here would be free the cache on each execution of the subquery. --- mysql-test/r/subselect4.result | 25 +++++++++++++++++++++++++ mysql-test/t/subselect4.test | 22 ++++++++++++++++++++++ sql/records.cc | 25 +++++++++++++++++++------ sql/records.h | 1 + sql/sql_select.cc | 1 + 5 files changed, 68 insertions(+), 6 deletions(-) diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index 606ab847028..441ee8acff8 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -2608,3 +2608,28 @@ region area population Central America and the Caribbean 442 66422 SET @@optimizer_switch= @save_optimizer_switch; DROP TABLE t1; +# +# MDEV-17066: Bytes lost or Assertion `status_var.local_memory_used == 0 after DELETE +# with subquery with ROLLUP +# +CREATE TABLE t1 (i INT DEFAULT 0, c VARCHAR(2048)); +INSERT INTO t1 SELECT 0, seq FROM seq_1_to_6000; +CREATE TABLE t2 (f VARCHAR(2048) DEFAULT ''); +INSERT INTO t2 VALUES ('1'),('bar'); +EXPLAIN +SELECT * FROM t2 WHERE f IN ( SELECT MAX(c) FROM t1 GROUP BY c WITH ROLLUP); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6000 Using filesort +SELECT * FROM t2 WHERE f IN ( SELECT MAX(c) FROM t1 GROUP BY c WITH ROLLUP); +f +1 +SELECT * FROM t2; +f +1 +bar +DELETE FROM t2 WHERE f IN ( SELECT MAX(c) FROM t1 GROUP BY c WITH ROLLUP ); +SELECT * FROM t2; +f +bar +DROP TABLE t1, t2; diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index 21ec28b1c03..3333668f554 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -4,6 +4,7 @@ drop table if exists t0,t1,t2,t3,t4,t5,t6; drop view if exists v1, v2; --enable_warnings +--source include/have_sequence.inc set @subselect4_tmp= @@optimizer_switch; set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; @@ -2138,3 +2139,24 @@ WHERE population/area = (SELECT MAX(population/area) from t1 B where A.region = SET @@optimizer_switch= @save_optimizer_switch; DROP TABLE t1; + +--echo # +--echo # MDEV-17066: Bytes lost or Assertion `status_var.local_memory_used == 0 after DELETE +--echo # with subquery with ROLLUP +--echo # + +CREATE TABLE t1 (i INT DEFAULT 0, c VARCHAR(2048)); +INSERT INTO t1 SELECT 0, seq FROM seq_1_to_6000; + +CREATE TABLE t2 (f VARCHAR(2048) DEFAULT ''); +INSERT INTO t2 VALUES ('1'),('bar'); + +EXPLAIN +SELECT * FROM t2 WHERE f IN ( SELECT MAX(c) FROM t1 GROUP BY c WITH ROLLUP); +SELECT * FROM t2 WHERE f IN ( SELECT MAX(c) FROM t1 GROUP BY c WITH ROLLUP); + +SELECT * FROM t2; +DELETE FROM t2 WHERE f IN ( SELECT MAX(c) FROM t1 GROUP BY c WITH ROLLUP ); +SELECT * FROM t2; + +DROP TABLE t1, t2; diff --git a/sql/records.cc b/sql/records.cc index 795f1197840..c8cec7759f2 100644 --- a/sql/records.cc +++ b/sql/records.cc @@ -304,12 +304,9 @@ bool init_read_record(READ_RECORD *info,THD *thd, TABLE *table, void end_read_record(READ_RECORD *info) -{ /* free cache if used */ - if (info->cache) - { - my_free_lock(info->cache); - info->cache=0; - } +{ + /* free cache if used */ + free_cache(info); if (info->table) { filesort_free_buffers(info->table,0); @@ -321,6 +318,22 @@ void end_read_record(READ_RECORD *info) } } + +/* + @brief + Free the cache used for reading records via rr_from_cache +*/ + +void free_cache(READ_RECORD *info) +{ + if (info->cache) + { + my_free_lock(info->cache); + info->cache= NULL; + } +} + + static int rr_handle_error(READ_RECORD *info, int error) { if (info->thd->killed) diff --git a/sql/records.h b/sql/records.h index fe3e9556bcf..ad940640217 100644 --- a/sql/records.h +++ b/sql/records.h @@ -77,6 +77,7 @@ bool init_read_record(READ_RECORD *info, THD *thd, TABLE *reg_form, bool init_read_record_idx(READ_RECORD *info, THD *thd, TABLE *table, bool print_error, uint idx, bool reverse); void end_read_record(READ_RECORD *info); +void free_cache(READ_RECORD *info); void rr_unlock_row(st_join_table *tab); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 647dee80188..2311fbb5017 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -12225,6 +12225,7 @@ void JOIN::cleanup(bool full) tab->table->s->table_name.str, tab->table->alias.c_ptr())); tab->table->file->ha_index_or_rnd_end(); + free_cache(&tab->read_record); } } }

1 0

[Commits] 27b046d2e95: MDEV-14203: rpl.rpl_extra_col_master_myisam, rpl.rpl_slave_load_tmpdir_not_exist failed in buildbot with a warning
by sujatha 21 Jul '20

21 Jul '20

revision-id: 27b046d2e95add5ba87e231279d2a7774db5b0c7 (mariadb-10.2.31-317-g27b046d2e95) parent(s): a1e52e7f32cee7c204078db4c4beb88250d7e1f6 author: Sujatha committer: Sujatha timestamp: 2020-07-21 11:16:40 +0530 message: MDEV-14203: rpl.rpl_extra_col_master_myisam, rpl.rpl_slave_load_tmpdir_not_exist failed in buildbot with a warning Problem: ======= rpl.rpl_slave_load_tmpdir_not_exist 'stmt' w3 [ fail ] Found warnings/errors in server log file! Test ended at 2017-09-27 20:34:55 [Warning] Master is configured to log replication events with checksum, but will not send such events to slaves that cannot process them ^ Found warnings in /mnt/buildbot/build/mariadb-10.2.10/mysql-test/var/3/log/mysqld.1.err ok Analysis: ======== When slave tries to connect to master 'get_master_version_and_clock' function is invoked to perform elaborated slave-master handshake. During this process slave server queries master server, to know if it is checksum aware and at the same time master is notified about its CRC-awareness. The master's side instant value of @@global.binlog_checksum is stored in the dump thread's uservar area as well as cached locally to become known in consensus by master and slave. Post hand-shake slave requests master for binlog dump. It sends 'COM_BINLOG_DUMP'. This command is sent to master by 'cli_advanced_command' call. If there is some temporary network failure during this request_dump call, 'end_server' is invoked to close the current connection between master and slave. Upon connection close the dump thread on the master gets terminated and it clears the 'uservar' data it got through master-slave handshake. The 'COM_BINLOG_DUMP' command is sent once again without master-slave handshake. Since the checksum data is not available with new dump thread a warning gets reported. Fix: === Upon network write error donot attempt reconnect, proceed to master-slave handshake. This ensures that master is aware of slave's capability to use checksums. --- .../rpl/r/rpl_dump_request_retry_warning.result | 22 ++++++++ .../rpl/t/rpl_dump_request_retry_warning.test | 60 ++++++++++++++++++++++ sql-common/client.c | 2 + sql/net_serv.cc | 13 +++++ sql/slave.cc | 3 +- 5 files changed, 99 insertions(+), 1 deletion(-) diff --git a/mysql-test/suite/rpl/r/rpl_dump_request_retry_warning.result b/mysql-test/suite/rpl/r/rpl_dump_request_retry_warning.result new file mode 100644 index 00000000000..3a80d5b5f31 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_dump_request_retry_warning.result @@ -0,0 +1,22 @@ +include/master-slave.inc +[connection master] +CREATE TABLE t1(a int); +INSERT INTO t1 VALUES(1); +connection slave; +SET @saved_dbug = @@GLOBAL.debug_dbug; +SET @@global.debug_dbug= 'd,simulate_error_on_packet_write'; +START SLAVE; +SET DEBUG_SYNC= 'now WAIT_FOR parked'; +SET @@GLOBAL.debug_dbug = @saved_dbug; +SET DEBUG_SYNC= 'now SIGNAL continue'; +SET DEBUG_SYNC= 'RESET'; +include/wait_for_slave_io_to_start.inc +include/wait_for_slave_sql_to_start.inc +connection master; +include/sync_slave_sql_with_master.inc +SELECT * FROM t1; +a +1 +connection master; +DROP TABLE t1; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_dump_request_retry_warning.test b/mysql-test/suite/rpl/t/rpl_dump_request_retry_warning.test new file mode 100644 index 00000000000..d9fb8258765 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_dump_request_retry_warning.test @@ -0,0 +1,60 @@ +# ==== Purpose ==== +# +# Test verifies that, due to a temporary network error, if request dump +# command specific packet write operation fails then the write error gets +# handled appropriately. Further retry will be initiated with appropriate +# slave registration on master. This will ensure that master has all the +# details of slave and no warnings are reported on the master side. +# +# ==== Implementation ==== +# +# Steps: +# 0 - Skip the slave start. +# 1 - Enable debug simulation which will simulate packet write error during +# dump request command execution. +# 2 - Start the slave. Observe that slave is able to reconnect post +# temporary network write error. +# +# ==== References ==== +# +# MDEV-14203: rpl.rpl_extra_col_master_myisam, +# rpl.rpl_slave_load_tmpdir_not_exist failed in buildbot with a +# warning +# +# MDEV-13258: rpl.rpl_skip_replication, rpl.rpl_set_statement_default_master +# failed in buildbot +# + +--source include/have_debug.inc +--source include/have_debug_sync.inc +--let $rpl_skip_start_slave=1 +--source include/master-slave.inc + +# Do an insert on master +CREATE TABLE t1(a int); +INSERT INTO t1 VALUES(1); + +# Add a debug point and start the slave so that dump request fails. +connection slave; +SET @saved_dbug = @@GLOBAL.debug_dbug; +SET @@global.debug_dbug= 'd,simulate_error_on_packet_write'; + +START SLAVE; +SET DEBUG_SYNC= 'now WAIT_FOR parked'; +SET @@GLOBAL.debug_dbug = @saved_dbug; +SET DEBUG_SYNC= 'now SIGNAL continue'; +SET DEBUG_SYNC= 'RESET'; + +--source include/wait_for_slave_io_to_start.inc +--source include/wait_for_slave_sql_to_start.inc + +# Sync the slave and verify that slave has caught up with the master. +connection master; +--source include/sync_slave_sql_with_master.inc +SELECT * FROM t1; + +# Cleanup +connection master; +DROP TABLE t1; + +--source include/rpl_end.inc diff --git a/sql-common/client.c b/sql-common/client.c index 227759f4163..b66eee2a508 100644 --- a/sql-common/client.c +++ b/sql-common/client.c @@ -721,6 +721,8 @@ cli_advanced_command(MYSQL *mysql, enum enum_server_command command, set_mysql_error(mysql, CR_NET_PACKET_TOO_LARGE, unknown_sqlstate); goto end; } + if (net->last_errno == ER_NET_ERROR_ON_WRITE && command == COM_BINLOG_DUMP) + goto end; end_server(mysql); if (mysql_reconnect(mysql) || stmt_skip) goto end; diff --git a/sql/net_serv.cc b/sql/net_serv.cc index f39eaaadf46..e625b307343 100644 --- a/sql/net_serv.cc +++ b/sql/net_serv.cc @@ -45,6 +45,7 @@ #include <violite.h> #include <signal.h> #include "probes_mysql.h" +#include <debug_sync.h> #ifdef EMBEDDED_LIBRARY #undef MYSQL_SERVER @@ -486,6 +487,18 @@ net_write_command(NET *net,uchar command, DBUG_ENTER("net_write_command"); DBUG_PRINT("enter",("length: %lu", (ulong) len)); + DBUG_EXECUTE_IF("simulate_error_on_packet_write", + { + if (command == COM_BINLOG_DUMP) + { + net->last_errno = ER_NET_ERROR_ON_WRITE; + socket_errno = 2; + DBUG_ASSERT(!debug_sync_set_action( + (THD *)net->thd, + STRING_WITH_LEN("now SIGNAL parked WAIT_FOR continue"))); + DBUG_RETURN(true); + } + };); MYSQL_NET_WRITE_START(length); buff[4]=command; /* For first packet */ diff --git a/sql/slave.cc b/sql/slave.cc index 1bf83aa9652..87eacfcfd0a 100644 --- a/sql/slave.cc +++ b/sql/slave.cc @@ -3297,7 +3297,8 @@ static int request_dump(THD *thd, MYSQL* mysql, Master_info* mi, in the future, we should do a better error analysis, but for now we just fill up the error log :-) */ - if (mysql_errno(mysql) == ER_NET_READ_INTERRUPTED) + if (mysql_errno(mysql) == ER_NET_READ_INTERRUPTED || + mysql_errno(mysql) == ER_NET_ERROR_ON_WRITE) *suppress_warnings= TRUE; // Suppress reconnect warning else sql_print_error("Error on COM_BINLOG_DUMP: %d %s, will retry in %d secs",

1 0

[Commits] 1aba801dbfc: MDEV-19232: Floating point precision / value comparison problem
by Varun 20 Jul '20

20 Jul '20

revision-id: 1aba801dbfc67f788d85faee12189c0e5a6c6c72 (mariadb-10.1.43-215-g1aba801dbfc) parent(s): c400ef2586bd188858ff43f2120fdf17475f2fdb author: Varun Gupta committer: Varun Gupta timestamp: 2020-07-20 16:15:02 +0530 message: MDEV-19232: Floating point precision / value comparison problem The issue occurs when the subquery_cache is enabled. When there is a cache miss the division was leading to a value with scale 9. In the case of cache hit the value returned was of scale 9 and due to the different values for the scales the where condition evaluated to FALSE, hence the output was incomplete. To fix this problem we need to round up the decimal to the limit mentioned in Item::decimals. This would make sure the values are compared with the same scale. --- mysql-test/r/parser_precedence.result | 2 +- mysql-test/r/subselect4.result | 24 ++++++++++++++++++++++++ mysql-test/r/type_newdecimal.result | 5 ----- mysql-test/r/type_ranges.result | 6 ++---- mysql-test/t/subselect4.test | 26 ++++++++++++++++++++++++++ sql/item_func.cc | 2 ++ 6 files changed, 55 insertions(+), 10 deletions(-) diff --git a/mysql-test/r/parser_precedence.result b/mysql-test/r/parser_precedence.result index 4330c8a2045..f23295bd61b 100644 --- a/mysql-test/r/parser_precedence.result +++ b/mysql-test/r/parser_precedence.result @@ -619,7 +619,7 @@ select 4 - 3 * 2, (4 - 3) * 2, 4 - (3 * 2); Testing that / is left associative select 15 / 5 / 3, (15 / 5) / 3, 15 / (5 / 3); 15 / 5 / 3 (15 / 5) / 3 15 / (5 / 3) -1.00000000 1.00000000 9.0000 +1.00000000 1.00000000 8.9998 Testing that / has precedence over | select 105 / 5 | 2, (105 / 5) | 2, 105 / (5 | 2); 105 / 5 | 2 (105 / 5) | 2 105 / (5 | 2) diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index 22d4938fb78..606ab847028 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -2584,3 +2584,27 @@ l1 i2 e 2 o 6 DROP TABLE t1, t2; +# +# MDEV-19232: Floating point precision / value comparison problem +# +CREATE TABLE t1 (region varchar(60), area decimal(10,0), population decimal(11,0)); +INSERT INTO t1 VALUES ('Central America and the Caribbean',91,11797); +INSERT INTO t1 VALUES ('Central America and the Caribbean',442,66422); +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='subquery_cache=on'; +SELECT +population, area, population/area, +cast(population/area as DECIMAL(20,9)) FROM t1 LIMIT 1; +population area population/area cast(population/area as DECIMAL(20,9)) +11797 91 129.6374 129.637400000 +SELECT * FROM t1 A +WHERE population/area = (SELECT MAX(population/area) from t1 B where A.region = B.region); +region area population +Central America and the Caribbean 442 66422 +SET optimizer_switch='subquery_cache=off'; +SELECT * FROM t1 A +WHERE population/area = (SELECT MAX(population/area) from t1 B where A.region = B.region); +region area population +Central America and the Caribbean 442 66422 +SET @@optimizer_switch= @save_optimizer_switch; +DROP TABLE t1; diff --git a/mysql-test/r/type_newdecimal.result b/mysql-test/r/type_newdecimal.result index dc6ef3c59e2..7b6629bc7b2 100644 --- a/mysql-test/r/type_newdecimal.result +++ b/mysql-test/r/type_newdecimal.result @@ -1530,11 +1530,8 @@ select (1.20396873 * 0.89550000 * 0.68000000 * 1.08721696 * 0.99500000 * 1.01500000 * 1.01500000 * 0.99500000) 0.812988073953673124592306939480 create table t1 as select 5.05 / 0.014; -Warnings: -Note 1265 Data truncated for column '5.05 / 0.014' at row 1 show warnings; Level Code Message -Note 1265 Data truncated for column '5.05 / 0.014' at row 1 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -1649,8 +1646,6 @@ my_col 0.123456789123456789123456789123 DROP TABLE t1; CREATE TABLE t1 SELECT 1 / .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS my_col; -Warnings: -Note 1265 Data truncated for column 'my_col' at row 1 DESCRIBE t1; Field Type Null Key Default Extra my_col decimal(65,4) YES NULL diff --git a/mysql-test/r/type_ranges.result b/mysql-test/r/type_ranges.result index 101bf0cfb2c..a10d2a56eae 100644 --- a/mysql-test/r/type_ranges.result +++ b/mysql-test/r/type_ranges.result @@ -94,8 +94,6 @@ DROP INDEX test ON t1; insert into t1 values (10, 1,1,1,1,1,1,1,1,1,1,1,1,1,NULL,0,0,0,1,1,1,1,'one','one'); insert into t1 values (NULL,2,2,2,2,2,2,2,2,2,2,2,2,2,NULL,NULL,NULL,NULL,NULL,NULL,2,2,'two','two,one'); insert into t1 values (0,1/3,3,3,3,3,3,3,3,3,3,3,3,3,NULL,'19970303','10:10:10','19970303101010','','','','3',3,3); -Warnings: -Warning 1265 Data truncated for column 'string' at row 1 insert into t1 values (0,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,NULL,19970807,080706,19970403090807,-1,-1,-1,'-1',-1,-1); Warnings: Warning 1264 Out of range value for column 'utiny' at row 1 @@ -136,7 +134,7 @@ select auto,string,tiny,short,medium,long_int,longlong,real_float,real_double,ut auto string tiny short medium long_int longlong real_float real_double utiny ushort umedium ulong ulonglong mod(floor(time_stamp/1000000),1000000)-mod(curdate(),1000000) date_field time_field date_time blob_col tinyblob_col mediumblob_col longblob_col 10 1 1 1 1 1 1 1.0 1.0000 1 00001 1 1 1 0 0000-00-00 00:00:00 0000-00-00 00:00:00 1 1 1 1 11 2 2 2 2 2 2 2.0 2.0000 2 00002 2 2 2 0 NULL NULL NULL NULL NULL 2 2 -12 0.33333333 3 3 3 3 3 3.0 3.0000 3 00003 3 3 3 0 1997-03-03 10:10:10 1997-03-03 10:10:10 3 +12 0.3333 3 3 3 3 3 3.0 3.0000 3 00003 3 3 3 0 1997-03-03 10:10:10 1997-03-03 10:10:10 3 13 -1 -1 -1 -1 -1 -1 -1.0 -1.0000 0 00000 0 0 0 0 1997-08-07 08:07:06 1997-04-03 09:08:07 -1 -1 -1 -1 14 -429496729 -128 -32768 -8388608 -2147483648 -4294967295 -4294967296.0 -4294967295.0000 0 00000 0 0 0 0 0000-00-00 00:00:00 0000-00-00 00:00:00 -4294967295 -4294967295 -4294967295 -4294967295 15 4294967295 127 32767 8388607 2147483647 4294967295 4294967296.0 4294967295.0000 255 65535 16777215 4294967295 4294967295 0 0000-00-00 00:00:00 0000-00-00 00:00:00 4294967295 4294967295 4294967295 4294967295 @@ -188,7 +186,7 @@ Warning 1265 Data truncated for column 'new_field' at row 7 select * from t2; auto string mediumblob_col new_field 1 2 2 ne -2 0.33333333 ne +2 0.3333 ne 3 -1 -1 ne 4 -429496729 -4294967295 ne 5 4294967295 4294967295 ne diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index b7a9c95abe7..21ec28b1c03 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -2112,3 +2112,29 @@ INSERT INTO t2 VALUES ('k'),('rid'),('f'),('x'); EXPLAIN EXTENDED SELECT * FROM t1 where ( t1.l1 < ANY (SELECT MAX(t2.v1) FROM t2)); SELECT * FROM t1 where ( t1.l1 < ANY (SELECT MAX(t2.v1) FROM t2)); DROP TABLE t1, t2; + +--echo # +--echo # MDEV-19232: Floating point precision / value comparison problem +--echo # + +CREATE TABLE t1 (region varchar(60), area decimal(10,0), population decimal(11,0)); +INSERT INTO t1 VALUES ('Central America and the Caribbean',91,11797); +INSERT INTO t1 VALUES ('Central America and the Caribbean',442,66422); + +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='subquery_cache=on'; + +SELECT +population, area, population/area, +cast(population/area as DECIMAL(20,9)) FROM t1 LIMIT 1; + +SELECT * FROM t1 A +WHERE population/area = (SELECT MAX(population/area) from t1 B where A.region = B.region); + +SET optimizer_switch='subquery_cache=off'; +SELECT * FROM t1 A +WHERE population/area = (SELECT MAX(population/area) from t1 B where A.region = B.region); + +SET @@optimizer_switch= @save_optimizer_switch; + +DROP TABLE t1; diff --git a/sql/item_func.cc b/sql/item_func.cc index c41d7809bf9..5e605855d80 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -1719,6 +1719,8 @@ my_decimal *Item_func_div::decimal_op(my_decimal *decimal_value) null_value= 1; return 0; } + my_decimal_round(E_DEC_FATAL_ERROR, decimal_value, + decimals, FALSE, decimal_value); return decimal_value; }

1 0

[Commits] 0e4a9337719: MDEV-18916: crash in Window_spec::print_partition() with decimals
by Varun 20 Jul '20

20 Jul '20

revision-id: 0e4a9337719479bffb141b52ab9535dd88fbf607 (mariadb-10.2.31-317-g0e4a9337719) parent(s): a1e52e7f32cee7c204078db4c4beb88250d7e1f6 author: Varun Gupta committer: Varun Gupta timestamp: 2020-07-20 15:50:17 +0530 message: MDEV-18916: crash in Window_spec::print_partition() with decimals Removed an unnecessary ifndef which was printing the window name for a named window only in the case of debug build. The print() for the window function should behave in the same way on both release and debug builds. --- mysql-test/r/win.result | 7 +++++++ mysql-test/t/win.test | 9 +++++++++ sql/item_windowfunc.cc | 6 ++---- 3 files changed, 18 insertions(+), 4 deletions(-) diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result index 019cfd6115d..081aaedd323 100644 --- a/mysql-test/r/win.result +++ b/mysql-test/r/win.result @@ -3843,5 +3843,12 @@ ROW_NUMBER() OVER w2 5 DROP TABLE t1; # +# MDEV-18916: crash in Window_spec::print_partition() with decimals +# +SELECT cast((rank() over w1) as decimal (53,56)); +ERROR 42000: Too big scale 56 specified for 'rank() over w1'. Maximum is 38 +SELECT cast((rank() over w1) as decimal (53,30)); +ERROR HY000: Window specification with name 'w1' is not defined +# # End of 10.2 tests # diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test index deed7de2d23..b749b235082 100644 --- a/mysql-test/t/win.test +++ b/mysql-test/t/win.test @@ -2497,6 +2497,15 @@ SELECT a,b FROM t1 WINDOW w2 AS (PARTITION BY -1,1,0,2,3,4); SELECT ROW_NUMBER() OVER w2 FROM t1 WINDOW w2 AS (PARTITION BY -1,0,1,2,3,4,5,6); DROP TABLE t1; +--echo # +--echo # MDEV-18916: crash in Window_spec::print_partition() with decimals +--echo # + +--error ER_TOO_BIG_SCALE +SELECT cast((rank() over w1) as decimal (53,56)); +--error ER_WRONG_WINDOW_SPEC_NAME +SELECT cast((rank() over w1) as decimal (53,30)); + --echo # --echo # End of 10.2 tests --echo # diff --git a/sql/item_windowfunc.cc b/sql/item_windowfunc.cc index a3edacd880e..87dddbfb439 100644 --- a/sql/item_windowfunc.cc +++ b/sql/item_windowfunc.cc @@ -443,10 +443,8 @@ void Item_window_func::print(String *str, enum_query_type query_type) { window_func()->print(str, query_type); str->append(" over "); -#ifndef DBUG_OFF - if (!window_spec) // one can call dbug_print_item() anytime in gdb + if (!window_spec) str->append(window_name); else -#endif - window_spec->print(str, query_type); + window_spec->print(str, query_type); }

1 0

[Commits] 0e4a9337719: MDEV-18916: crash in Window_spec::print_partition() with decimals
by Varun 20 Jul '20

20 Jul '20

revision-id: 0e4a9337719479bffb141b52ab9535dd88fbf607 (mariadb-10.2.31-317-g0e4a9337719) parent(s): a1e52e7f32cee7c204078db4c4beb88250d7e1f6 author: Varun Gupta committer: Varun Gupta timestamp: 2020-07-20 15:50:17 +0530 message: MDEV-18916: crash in Window_spec::print_partition() with decimals Removed an unnecessary ifndef which was printing the window name for a named window only in the case of debug build. The print() for the window function should behave in the same way on both release and debug builds. --- mysql-test/r/win.result | 7 +++++++ mysql-test/t/win.test | 9 +++++++++ sql/item_windowfunc.cc | 6 ++---- 3 files changed, 18 insertions(+), 4 deletions(-) diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result index 019cfd6115d..081aaedd323 100644 --- a/mysql-test/r/win.result +++ b/mysql-test/r/win.result @@ -3843,5 +3843,12 @@ ROW_NUMBER() OVER w2 5 DROP TABLE t1; # +# MDEV-18916: crash in Window_spec::print_partition() with decimals +# +SELECT cast((rank() over w1) as decimal (53,56)); +ERROR 42000: Too big scale 56 specified for 'rank() over w1'. Maximum is 38 +SELECT cast((rank() over w1) as decimal (53,30)); +ERROR HY000: Window specification with name 'w1' is not defined +# # End of 10.2 tests # diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test index deed7de2d23..b749b235082 100644 --- a/mysql-test/t/win.test +++ b/mysql-test/t/win.test @@ -2497,6 +2497,15 @@ SELECT a,b FROM t1 WINDOW w2 AS (PARTITION BY -1,1,0,2,3,4); SELECT ROW_NUMBER() OVER w2 FROM t1 WINDOW w2 AS (PARTITION BY -1,0,1,2,3,4,5,6); DROP TABLE t1; +--echo # +--echo # MDEV-18916: crash in Window_spec::print_partition() with decimals +--echo # + +--error ER_TOO_BIG_SCALE +SELECT cast((rank() over w1) as decimal (53,56)); +--error ER_WRONG_WINDOW_SPEC_NAME +SELECT cast((rank() over w1) as decimal (53,30)); + --echo # --echo # End of 10.2 tests --echo # diff --git a/sql/item_windowfunc.cc b/sql/item_windowfunc.cc index a3edacd880e..87dddbfb439 100644 --- a/sql/item_windowfunc.cc +++ b/sql/item_windowfunc.cc @@ -443,10 +443,8 @@ void Item_window_func::print(String *str, enum_query_type query_type) { window_func()->print(str, query_type); str->append(" over "); -#ifndef DBUG_OFF - if (!window_spec) // one can call dbug_print_item() anytime in gdb + if (!window_spec) str->append(window_name); else -#endif - window_spec->print(str, query_type); + window_spec->print(str, query_type); }

1 0

[Commits] 7417ab24c30: MDEV-23221: A subquery causes crash
by Sergei Petrunia 20 Jul '20

20 Jul '20

revision-id: 7417ab24c3008bca7dc0f3e79508266171aff1b0 (mariadb-10.4.11-287-g7417ab24c30) parent(s): 956f21c3b0beba89835e8d24fde4b67b589ab142 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2020-07-20 11:44:20 +0300 message: MDEV-23221: A subquery causes crash * Fix the crash: IN-to-EXISTS rewrite causes an error (and so JOIN::optimize() fails with an error, too), don't call update_used_tables(). Terminate the query execution instead. * Fix the cause of the error in the IN-to-EXISTS rewrite: don't do the rewrite if doing it will cause an error of this kind: This version of MariaDB doesn't yet support 'SUBQUERY in ROW in left expression of IN/ALL/ANY' --- mysql-test/main/subselect_exists2in.result | 42 ++++++++++++++++++++++++++++++ mysql-test/main/subselect_exists2in.test | 40 ++++++++++++++++++++++++++++ sql/item_subselect.cc | 18 +++++++++++-- sql/sql_lex.cc | 3 ++- 4 files changed, 100 insertions(+), 3 deletions(-) diff --git a/mysql-test/main/subselect_exists2in.result b/mysql-test/main/subselect_exists2in.result index 576559c2e86..6d27ab25963 100644 --- a/mysql-test/main/subselect_exists2in.result +++ b/mysql-test/main/subselect_exists2in.result @@ -974,5 +974,47 @@ id id DROP PROCEDURE p1; DROP TABLE t1; +# +# MDEV-23221: A subquery causes crash +# +create table t1 ( +location_code varchar(10), +country_id varchar(10) +); +insert into t1 values ('HKG', 'HK'); +insert into t1 values ('NYC', 'US'); +insert into t1 values ('LAX', 'US'); +create table t2 ( +container_id varchar(10), +cntr_activity_type varchar(10), +cntr_dest varchar(10) +); +insert into t2 values ('AAAA1111', 'VSL', 'NYC'); +insert into t2 values ('AAAA1111', 'CUV', 'NYC'); +insert into t2 values ('BBBB2222', 'VSL', 'LAX'); +insert into t2 values ('BBBB2222', 'XYZ', 'LAX'); +# Must not crash or return an error: +select +(select country_id from t1 where location_code = cl1.cntr_dest) as dest_cntry, +(select +max(container_id) +from t2 as cl2 +where +cl2.container_id = cl1.container_id and +cl2.cntr_activity_type = 'CUV' and +exists (select location_code +from t1 +where +location_code = cl2.cntr_dest and +country_id = dest_cntry) +) as CUV +from +t2 cl1; +dest_cntry CUV +US AAAA1111 +US AAAA1111 +US NULL +US NULL +drop table t1,t2; # End of 10.0 tests set optimizer_switch=default; diff --git a/mysql-test/main/subselect_exists2in.test b/mysql-test/main/subselect_exists2in.test index 2a9947123d4..6da1fe14e1c 100644 --- a/mysql-test/main/subselect_exists2in.test +++ b/mysql-test/main/subselect_exists2in.test @@ -827,6 +827,46 @@ CALL p1(); DROP PROCEDURE p1; DROP TABLE t1; +--echo # +--echo # MDEV-23221: A subquery causes crash +--echo # +create table t1 ( +location_code varchar(10), +country_id varchar(10) +); +insert into t1 values ('HKG', 'HK'); +insert into t1 values ('NYC', 'US'); +insert into t1 values ('LAX', 'US'); + +create table t2 ( +container_id varchar(10), +cntr_activity_type varchar(10), +cntr_dest varchar(10) +); +insert into t2 values ('AAAA1111', 'VSL', 'NYC'); +insert into t2 values ('AAAA1111', 'CUV', 'NYC'); +insert into t2 values ('BBBB2222', 'VSL', 'LAX'); +insert into t2 values ('BBBB2222', 'XYZ', 'LAX'); + +--echo # Must not crash or return an error: +select + (select country_id from t1 where location_code = cl1.cntr_dest) as dest_cntry, + (select + max(container_id) + from t2 as cl2 + where + cl2.container_id = cl1.container_id and + cl2.cntr_activity_type = 'CUV' and + exists (select location_code + from t1 + where + location_code = cl2.cntr_dest and + country_id = dest_cntry) + ) as CUV +from + t2 cl1; +drop table t1,t2; + --echo # End of 10.0 tests #restore defaults diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 889f9902e12..38b40c4473d 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -2793,6 +2793,7 @@ bool Item_exists_subselect::select_prepare_to_be_in() */ static bool check_equality_for_exist2in(Item_func *func, + bool allow_subselect, Item_ident **local_field, Item **outer_exp) { @@ -2803,7 +2804,8 @@ static bool check_equality_for_exist2in(Item_func *func, args= func->arguments(); if (args[0]->real_type() == Item::FIELD_ITEM && args[0]->all_used_tables() != OUTER_REF_TABLE_BIT && - args[1]->all_used_tables() == OUTER_REF_TABLE_BIT) + args[1]->all_used_tables() == OUTER_REF_TABLE_BIT && + (allow_subselect || !args[1]->with_subquery())) { /* It is Item_field or Item_direct_view_ref) */ DBUG_ASSERT(args[0]->type() == Item::FIELD_ITEM || @@ -2814,7 +2816,8 @@ static bool check_equality_for_exist2in(Item_func *func, } else if (args[1]->real_type() == Item::FIELD_ITEM && args[1]->all_used_tables() != OUTER_REF_TABLE_BIT && - args[0]->all_used_tables() == OUTER_REF_TABLE_BIT) + args[0]->all_used_tables() == OUTER_REF_TABLE_BIT && + (allow_subselect || !args[0]->with_subquery())) { /* It is Item_field or Item_direct_view_ref) */ DBUG_ASSERT(args[1]->type() == Item::FIELD_ITEM || @@ -2843,6 +2846,13 @@ typedef struct st_eq_field_outer outer1=inner_tbl1.col1 AND ... AND outer2=inner_tbl1.col2 AND remainder_cond + if there is just one outer_expr=inner_expr pair, then outer_expr can have a + subselect in it. If there are many such pairs, then none of outer_expr can + have a subselect in it. If we allow this, the query will fail with an error: + + This version of MariaDB doesn't yet support 'SUBQUERY in ROW in left + expression of IN/ALL/ANY' + @param conds Condition to be checked @parm result Array to collect EQ_FIELD_OUTER elements describing inner-vs-outer equalities the function has found. @@ -2860,14 +2870,17 @@ static bool find_inner_outer_equalities(Item **conds, { List_iterator<Item> li(*((Item_cond*)*conds)->argument_list()); Item *item; + bool allow_subselect= true; while ((item= li++)) { if (item->type() == Item::FUNC_ITEM && check_equality_for_exist2in((Item_func *)item, + allow_subselect, &element.local_field, &element.outer_exp)) { found= TRUE; + allow_subselect= false; element.eq_ref= li.ref(); if (result.append(element)) goto alloc_err; @@ -2876,6 +2889,7 @@ static bool find_inner_outer_equalities(Item **conds, } else if ((*conds)->type() == Item::FUNC_ITEM && check_equality_for_exist2in((Item_func *)*conds, + true, &element.local_field, &element.outer_exp)) { diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index eb22534f4fb..90f9ea70851 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -4197,7 +4197,8 @@ bool st_select_lex::optimize_unflattened_subqueries(bool const_only) sl->options|= SELECT_DESCRIBE; inner_join->select_options|= SELECT_DESCRIBE; } - res= inner_join->optimize(); + if ((res= inner_join->optimize())) + return TRUE; if (!inner_join->cleaned) sl->update_used_tables(); sl->update_correlated_cache();

1 0