- commits - lists.mariadb.org

[Commits] 108af298032: MDEV-15454: Nested SELECT IN returns wrong results
by varunraiko1803＠gmail.com 25 Jul '18

25 Jul '18

revision-id: 108af298032cb4cea316a08c304c306268a2badf (mariadb-5.5.56-213-g108af298032) parent(s): 318097bb8f6e12c546b5dcd287416158209dbb39 author: Varun Gupta committer: Varun Gupta timestamp: 2018-05-11 02:56:33 +0530 message: MDEV-15454: Nested SELECT IN returns wrong results In this case we are setting the field Item_func_eq::in_eqaulity_no for the semi-join equalities. This helps us to remove these equalites as the inner tables are not available during parent select execution while the outer tables are not available during materialization phase. We only have it set for the equalites for the fields involved with the IN subquery and reset it for the equalities which do not belong to the IN subquery. For example in case of nested IN subqueries: SELECT t1.a FROM t1 WHERE t1.a IN (SELECT t2.a FROM t2 where t2.b IN (select t3.b from t3 where t3.c=27 )) there are two equalites involving the fields of the IN subquery 1) t2.b = t3.b : the field Item_func_eq::in_eqaulity_no is set when we merge the grandchild select into the child select 2) t1.a = t2.a : the field Item_func_eq::in_eqaulity_no is set when we merge the child select into the parent select But when we perform case 2) we should ensure that we reset the equalities in the child's WHERE clause. --- mysql-test/r/subselect_sj_mat.result | 46 +++++++++++++++++++++++++ mysql-test/t/subselect_sj_mat.test | 49 ++++++++++++++++++++++++++ sql/opt_subselect.cc | 66 ++++++++++++++++++++++++++++++++++++ 3 files changed, 161 insertions(+) diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index cb5012a91c9..2af1ac1f7b3 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -2400,4 +2400,50 @@ ec70316637232000158bbfc8bcbe5d60 ebb4620037332000158bbfc8bcbe5d89 DROP TABLE t1,t2,t3; set optimizer_switch=@save_optimizer_switch; +# +# MDEV-15454: Nested SELECT IN returns wrong results +# +CREATE TABLE t1 ( a int NOT NULL PRIMARY KEY); +CREATE TABLE t2 ( a int, b int ); +INSERT INTO t2 VALUES (7878, 96),(3465, 96),(1403, 96),(4189, 96),(8732, 96), (5,96); +CREATE TABLE t3 (c int unsigned NOT NULL, b int unsigned NOT NULL, PRIMARY KEY (c,b)); +INSERT INTO t3 (c, b) VALUES (27, 96); +CREATE PROCEDURE prepare_data() +BEGIN +DECLARE i INT DEFAULT 1; +WHILE i < 1000 DO +INSERT INTO t1 (a) VALUES (i); +INSERT INTO t2 (a,b) VALUES (i,56); +INSERT INTO t3 (c,b) VALUES (i,i); +SET i = i + 1; +END WHILE; +END$$ +CALL prepare_data(); +SET optimizer_switch='materialization=off'; +explain +SELECT t1.a FROM t1 +WHERE t1.a IN (SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27)) LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 ref PRIMARY PRIMARY 4 const 2 Using index; Start temporary +1 PRIMARY t2 ALL NULL NULL NULL NULL 1005 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 Using index; End temporary +SELECT t1.a FROM t1 +WHERE t1.a IN (SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27)) LIMIT 5; +a +5 +SET optimizer_switch='materialization=on'; +explain +SELECT t1.a FROM t1 +WHERE t1.a IN (SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27)) LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 999 Using index +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t3 ref PRIMARY PRIMARY 4 const 2 Using index +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 1005 Using where; Using join buffer (flat, BNL join) +SELECT t1.a FROM t1 +WHERE t1.a IN (SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27)) LIMIT 5; +a +5 +drop procedure prepare_data; +drop table t1,t2,t3; # End of 5.5 tests diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test index 90f63bea561..67cba08b5df 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -2151,4 +2151,53 @@ eval $q; DROP TABLE t1,t2,t3; set optimizer_switch=@save_optimizer_switch; +--echo # +--echo # MDEV-15454: Nested SELECT IN returns wrong results +--echo # + +CREATE TABLE t1 ( a int NOT NULL PRIMARY KEY); + +CREATE TABLE t2 ( a int, b int ); +INSERT INTO t2 VALUES (7878, 96),(3465, 96),(1403, 96),(4189, 96),(8732, 96), (5,96); + +CREATE TABLE t3 (c int unsigned NOT NULL, b int unsigned NOT NULL, PRIMARY KEY (c,b)); +INSERT INTO t3 (c, b) VALUES (27, 96); + +DELIMITER $$; +CREATE PROCEDURE prepare_data() +BEGIN + DECLARE i INT DEFAULT 1; + + WHILE i < 1000 DO + INSERT INTO t1 (a) VALUES (i); + INSERT INTO t2 (a,b) VALUES (i,56); + INSERT INTO t3 (c,b) VALUES (i,i); + SET i = i + 1; + END WHILE; +END$$ +DELIMITER ;$$ + +CALL prepare_data(); + +SET optimizer_switch='materialization=off'; + +explain +SELECT t1.a FROM t1 +WHERE t1.a IN (SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27)) LIMIT 5; + +SELECT t1.a FROM t1 +WHERE t1.a IN (SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27)) LIMIT 5; + +SET optimizer_switch='materialization=on'; + +explain +SELECT t1.a FROM t1 +WHERE t1.a IN (SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27)) LIMIT 5; + +SELECT t1.a FROM t1 +WHERE t1.a IN (SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27)) LIMIT 5; + +drop procedure prepare_data; +drop table t1,t2,t3; + --echo # End of 5.5 tests diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index a7edd64e68b..31213a5cf1d 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -440,6 +440,7 @@ bool subquery_types_allow_materialization(Item_in_subselect *in_subs); static bool replace_where_subcondition(JOIN *, Item **, Item *, Item *, bool); static int subq_sj_candidate_cmp(Item_in_subselect* el1, Item_in_subselect* el2, void *arg); +static void reset_equality_number_for_subq_conds(Item * cond); static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred); static bool convert_subq_to_jtbm(JOIN *parent_join, Item_in_subselect *subq_pred, bool *remove); @@ -1453,6 +1454,67 @@ static int subq_sj_candidate_cmp(Item_in_subselect* el1, Item_in_subselect* el2, } +/** + @brief + reset the value of the field in_eqaulity_no for all Item_func_eq + items in the where clause of the subquery. + + Look for in_equality_no description in Item_func_eq class + + DESCRIPTION + Lets have an example: + SELECT t1.a FROM t1 WHERE t1.a IN + (SELECT t2.a FROM t2 where t2.b IN + (select t3.b from t3 where t3.c=27 )) + + So for such a query we have the parent, child and + grandchild select. + + So for the equality t2.b = t3.b we set the value for in_equality_no to + 0 according to its description. Wewe do the same for t1.a = t2.a. + But when we look at the child select (with the grandchild select merged), + the query would be + + SELECT t1.a FROM t1 WHERE t1.a IN + (SELECT t2.a FROM t2 where t2.b = t3.b and t3.c=27) + + and then when the child select is merged into the parent select the query + would look like + + SELECT t1.a FROM t1, semi-join-nest(t2,t3) + WHERE t1.a =t2.a and t2.b = t3.b and t3.c=27 + + Still we would have in_equality_no set for t2.b = t3.b + though it does not take part in the semi-join equality for the parent select, + so we should reset its value to UINT_MAX. + + @param cond WHERE clause of the subquery +*/ + +static void reset_equality_number_for_subq_conds(Item * cond) +{ + if (!cond) + return; + if (cond->type() == Item::COND_ITEM) + { + List_iterator<Item> li(*((Item_cond*) cond)->argument_list()); + Item *item; + while ((item=li++)) + { + if (item->type() == Item::FUNC_ITEM && + ((Item_func*)item)->functype()== Item_func::EQ_FUNC) + ((Item_func_eq*)item)->in_equality_no= UINT_MAX; + } + } + else + { + if (cond->type() == Item::FUNC_ITEM && + ((Item_func*)cond)->functype()== Item_func::EQ_FUNC) + ((Item_func_eq*)cond)->in_equality_no= UINT_MAX; + } + return; +} + /* Convert a subquery predicate into a TABLE_LIST semi-join nest @@ -1481,6 +1543,7 @@ static int subq_sj_candidate_cmp(Item_in_subselect* el1, Item_in_subselect* el2, TRUE Out of memory error */ + static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred) { SELECT_LEX *parent_lex= parent_join->select_lex; @@ -1713,6 +1776,7 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred) if (subq_pred->left_expr->cols() == 1) { + reset_equality_number_for_subq_conds(sj_nest->sj_on_expr); /* add left = select_list_element */ nested_join->sj_outer_expr_list.push_back(&subq_pred->left_expr); /* @@ -1735,6 +1799,7 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred) } else if (subq_pred->left_expr->type() == Item::ROW_ITEM) { + reset_equality_number_for_subq_conds(sj_nest->sj_on_expr); /* disassemple left expression and add left1 = select_list_element1 and left2 = select_list_element2 ... @@ -1759,6 +1824,7 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred) } else { + reset_equality_number_for_subq_conds(sj_nest->sj_on_expr); /* add row operation left = (select_list_element1, select_list_element2, ...)

2 1

[Commits] MDEV-16821 Set password for user makes rpl test to fail
by Sachin Setiya 25 Jul '18

25 Jul '18

commit 969939e89c29bb3f3d91f8b4ab539601b5daa4d8 Author: Sachin <sachin.setiya(a)mariadb.com> Date: Wed Jul 25 12:54:37 2018 +0530 MDEV-16821 Set password for user makes rpl test to fail Actually if we use "set password for " command this changes the checksum of mysql.user table -localhost root Y Y Y Y Y Y Y Y YY Y Y Y Y Y Y Y Y Y Y Y $ Y Y Y Y Y Y Y 0 00 0 N N 0.000000 +localhost root Y Y Y Y Y Y Y Y YY Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y 0 00 0 mysql_native_password N N 0.000000 In short we replace '' with mysql_native_password which make checksum to be different, and hence check test case fails. So we use UPDATE mysql.user command. diff --git a/mysql-test/extra/rpl_tests/rpl_row_001.test b/mysql-test/extra/rpl_tests/rpl_row_001.test index 8eb684e0dff..2706336bdea 100644 --- a/mysql-test/extra/rpl_tests/rpl_row_001.test +++ b/mysql-test/extra/rpl_tests/rpl_row_001.test @@ -14,7 +14,7 @@ connection slave; sync_with_master; STOP SLAVE; connection master; -SET PASSWORD FOR root@"localhost" = PASSWORD('foo'); +UPDATE mysql.user SET password=password('foo') WHERE host='localhost' AND user='root'; connection slave; START SLAVE; connection master; @@ -22,7 +22,7 @@ connection master; # Give slave time to do at last one failed connect retry # This one must be short so that the slave will not stop retrying real_sleep 2; -SET PASSWORD FOR root@"localhost" = PASSWORD(''); +UPDATE mysql.user SET password=password('') WHERE host='localhost' AND user='root'; # Give slave time to connect (will retry every second) sleep 2; diff --git a/mysql-test/suite/rpl/r/rpl_row_001.result b/mysql-test/suite/rpl/r/rpl_row_001.result index 94985548f59..3ca694a8e76 100644 --- a/mysql-test/suite/rpl/r/rpl_row_001.result +++ b/mysql-test/suite/rpl/r/rpl_row_001.result @@ -18,11 +18,11 @@ Ababa connection slave; STOP SLAVE; connection master; -SET PASSWORD FOR root@"localhost" = PASSWORD('foo'); +UPDATE mysql.user SET password=password('foo') WHERE host='localhost' AND user='root'; connection slave; START SLAVE; connection master; -SET PASSWORD FOR root@"localhost" = PASSWORD(''); +UPDATE mysql.user SET password=password('') WHERE host='localhost' AND user='root'; CREATE TABLE t3(n INT); INSERT INTO t3 VALUES(1),(2); connection slave; diff --git a/mysql-test/suite/rpl/r/rpl_stm_000001.result b/mysql-test/suite/rpl/r/rpl_stm_000001.result index 5680d22f233..0b9ed6fc09c 100644 --- a/mysql-test/suite/rpl/r/rpl_stm_000001.result +++ b/mysql-test/suite/rpl/r/rpl_stm_000001.result @@ -19,11 +19,11 @@ abandons connection slave; stop slave; connection master; -set password for root@"localhost" = password('foo'); +UPDATE mysql.user SET password=password('foo') WHERE host='localhost' AND user='root'; connection slave; start slave; connection master; -set password for root@"localhost" = password(''); +UPDATE mysql.user SET password=password('') WHERE host='localhost' AND user='root'; create table t3(n int); insert into t3 values(1),(2); connection slave; diff --git a/mysql-test/suite/rpl/t/rpl_stm_000001.test b/mysql-test/suite/rpl/t/rpl_stm_000001.test index 7e8f669ff34..119fd6168e0 100644 --- a/mysql-test/suite/rpl/t/rpl_stm_000001.test +++ b/mysql-test/suite/rpl/t/rpl_stm_000001.test @@ -19,7 +19,7 @@ select * from t1 limit 10; sync_slave_with_master; stop slave; connection master; -set password for root@"localhost" = password('foo'); +UPDATE mysql.user SET password=password('foo') WHERE host='localhost' AND user='root'; connection slave; start slave; connection master; @@ -27,7 +27,7 @@ connection master; # Give slave time to do at last one failed connect retry # This one must be short so that the slave will not stop retrying real_sleep 2; -set password for root@"localhost" = password(''); +UPDATE mysql.user SET password=password('') WHERE host='localhost' AND user='root'; # Give slave time to connect (will retry every second) sleep 2;

1 0

[Commits] 1fde449: MDEV-16820 Lost 'Impossible where' from query with inexpensive subquery
by IgorBabaev 25 Jul '18

25 Jul '18

revision-id: 1fde449f1d4e8ebe12e989754bfdc84b123cf0b2 (mariadb-10.1.34-30-g1fde449) parent(s): 57cde8ccd19675dc98e3cbacf0ef5c72cb188e49 author: Igor Babaev committer: Igor Babaev timestamp: 2018-07-24 23:53:12 -0700 message: MDEV-16820 Lost 'Impossible where' from query with inexpensive subquery This patch fixes another problem introduced by the patch for mdev-4817. The latter changed Item_cond::fix_fields() in such a way that it could call the virtual method is_expensive(). With the first its call the method saves the result in Item::is_expensive_cache. For all next calls the method returns the result from this cache. So if the item once was determined as expensive the method always returns true. For subqueries it's not good, because non-optimized subqueries always is considered as expensive. It means that the cache should be invalidated after the call of optimize_constant_subqueries(). --- mysql-test/r/subselect.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_exists_to_in.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_mat.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_opts.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_scache.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_semijoin.result | 16 ++++++++++++++++ mysql-test/t/subselect.test | 15 +++++++++++++++ sql/item.h | 5 +++++ sql/sql_select.cc | 7 +++++++ 9 files changed, 123 insertions(+) diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 40f936f..69db095 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -7235,6 +7235,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests # End of 10.0 tests # diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result index a238208..ef3bf7e 100644 --- a/mysql-test/r/subselect_no_exists_to_in.result +++ b/mysql-test/r/subselect_no_exists_to_in.result @@ -7235,6 +7235,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests # End of 10.0 tests # diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index baa7430..703572e 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -7228,6 +7228,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests # End of 10.0 tests # diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index 039f2fe..39a098b 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -7226,6 +7226,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests # End of 10.0 tests # diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index 0ce77bb..b9a59bf 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -7241,6 +7241,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests # End of 10.0 tests # diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 574e781..f1df85f 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -7226,6 +7226,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests # End of 10.0 tests # diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 717871d..a4c4c21 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -6098,6 +6098,21 @@ and t1.a in (select `test`.`t3`.`c` from `test`.`t3`); SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +--echo # +--echo # MDEV-16820: impossible where with inexpensive subquery +--echo # + +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); + +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); + +explain select * from t1 where (select max(b) from t2) = 10; +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; + +drop table t1,t2; + --echo End of 5.5 tests --echo # End of 10.0 tests diff --git a/sql/item.h b/sql/item.h index 139ce40..dcd773f 100644 --- a/sql/item.h +++ b/sql/item.h @@ -1449,6 +1449,11 @@ class Item: public Value_source, virtual bool exists2in_processor(uchar *opt_arg) { return 0; } virtual bool find_selective_predicates_list_processor(uchar *opt_arg) { return 0; } + bool cleanup_is_expensive_cache_processor(uchar *arg) + { + is_expensive_cache= (int8)(-1); + return 0; + } /* To call bool function for all arguments */ struct bool_func_call_args diff --git a/sql/sql_select.cc b/sql/sql_select.cc index e3b725c..460a64e 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1212,6 +1212,13 @@ JOIN::optimize_inner() if (optimize_constant_subqueries()) DBUG_RETURN(1); + if (conds && conds->has_subquery()) + (void) conds->walk(&Item::cleanup_is_expensive_cache_processor, + 0, (uchar*)0); + if (having && having->has_subquery()) + (void) having->walk(&Item::cleanup_is_expensive_cache_processor, + 0, (uchar*)0); + if (setup_jtbm_semi_joins(this, join_list, &conds)) DBUG_RETURN(1);

1 0

[Commits] c631060: MDEV-16820 Lost 'Impossible where' from query with inexpensive subquery
by IgorBabaev 25 Jul '18

25 Jul '18

revision-id: c631060713a2af2890284feb7aea96c0cf4ba49f (mariadb-10.0.35-73-gc631060) parent(s): 1bda5e3a8f21d14b4af416259f293a92c49daebb author: Igor Babaev committer: Igor Babaev timestamp: 2018-07-24 23:45:55 -0700 message: MDEV-16820 Lost 'Impossible where' from query with inexpensive subquery This patch fixes another problem introduced by the patch for mdev-4817. The latter changed Item_cond::fix_fields() in such a way that it could call the virtual method is_expensive(). With the first its call the method saves the result in Item::is_expensive_cache. For all next calls the method returns the result from this cache. So if the item once was determined as expensive the method always returns true. For subqueries it's not good, because non-optimized subqueries always is considered as expensive. It means that the cache should be invalidated after the call of optimize_constant_subqueries(). --- mysql-test/r/subselect.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_exists_to_in.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_mat.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_opts.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_scache.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_semijoin.result | 16 ++++++++++++++++ mysql-test/t/subselect.test | 15 +++++++++++++++ sql/item.h | 5 +++++ sql/sql_select.cc | 7 +++++++ 9 files changed, 123 insertions(+) diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 79b2810..b074fb3 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -7211,4 +7211,20 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result index 7b2054f..d5aa16a 100644 --- a/mysql-test/r/subselect_no_exists_to_in.result +++ b/mysql-test/r/subselect_no_exists_to_in.result @@ -7211,6 +7211,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests set optimizer_switch=default; select @@optimizer_switch like '%exists_to_in=off%'; diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index 51c5263..aff68bd 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -7204,6 +7204,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests set optimizer_switch=default; select @@optimizer_switch like '%materialization=on%'; diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index 574af7b..f118178 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -7202,5 +7202,21 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests set @optimizer_switch_for_subselect_test=null; diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index 03285cf..6cefce2 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -7217,6 +7217,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests set optimizer_switch=default; select @@optimizer_switch like '%subquery_cache=on%'; diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 6669c27..884374a 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -7202,6 +7202,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests set @optimizer_switch_for_subselect_test=null; set @join_cache_level_for_subselect_test=NULL; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 8c9a489..390d363 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -6096,4 +6096,19 @@ and t1.a in (select `test`.`t3`.`c` from `test`.`t3`); SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +--echo # +--echo # MDEV-16820: impossible where with inexpensive subquery +--echo # + +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); + +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); + +explain select * from t1 where (select max(b) from t2) = 10; +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; + +drop table t1,t2; + --echo End of 5.5 tests diff --git a/sql/item.h b/sql/item.h index b111d74..7c95239 100644 --- a/sql/item.h +++ b/sql/item.h @@ -1275,6 +1275,11 @@ class Item { virtual bool exists2in_processor(uchar *opt_arg) { return 0; } virtual bool find_selective_predicates_list_processor(uchar *opt_arg) { return 0; } + bool cleanup_is_expensive_cache_processor(uchar *arg) + { + is_expensive_cache= (int8)(-1); + return 0; + } /* To call bool function for all arguments */ struct bool_func_call_args diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 43b8d2b..19a727f 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1207,6 +1207,13 @@ TODO: make view to decide if it is possible to write to WHERE directly or make S if (optimize_constant_subqueries()) DBUG_RETURN(1); + if (conds && conds->has_subquery()) + (void) conds->walk(&Item::cleanup_is_expensive_cache_processor, + 0, (uchar*)0); + if (having && having->has_subquery()) + (void) having->walk(&Item::cleanup_is_expensive_cache_processor, + 0, (uchar*)0); + if (setup_jtbm_semi_joins(this, join_list, &conds)) DBUG_RETURN(1);

1 0

[Commits] 92cccf8: MDEV-16820 Lost 'Impossible where' from query with inexpensive subquery
by IgorBabaev 25 Jul '18

25 Jul '18

revision-id: 92cccf8708b156f22602f035e0fdc4e3cc893d2f (mariadb-10.0.35-73-g92cccf8) parent(s): 1bda5e3a8f21d14b4af416259f293a92c49daebb author: Igor Babaev committer: Igor Babaev timestamp: 2018-07-24 23:27:28 -0700 message: MDEV-16820 Lost 'Impossible where' from query with inexpensive subquery This patch fixes another problem introduced by the patch for mdev-4817. The latter changed Item_cond::fix_fields() in such a way that it could call the virtual method is_expensive(). With the first its call the method saves the result in Item::is_expensive_cache. For all next calls the method returns the result from this cache. So if the item once was determined as expensive the method always returns true. For subqueries it's not good, because non-optimized subqueries always is considered as expensive. It means that the cache should be invalidated after the call of optimize_constant_subqueries(). --- mysql-test/r/subselect.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_mat.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_opts.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_scache.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_semijoin.result | 16 ++++++++++++++++ mysql-test/t/subselect.test | 15 +++++++++++++++ sql/item.h | 5 +++++ sql/sql_select.cc | 7 +++++++ 8 files changed, 107 insertions(+) diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 79b2810..b074fb3 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -7211,4 +7211,20 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index 51c5263..aff68bd 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -7204,6 +7204,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests set optimizer_switch=default; select @@optimizer_switch like '%materialization=on%'; diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index 574af7b..f118178 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -7202,5 +7202,21 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests set @optimizer_switch_for_subselect_test=null; diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index 03285cf..6cefce2 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -7217,6 +7217,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests set optimizer_switch=default; select @@optimizer_switch like '%subquery_cache=on%'; diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 6669c27..884374a 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -7202,6 +7202,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests set @optimizer_switch_for_subselect_test=null; set @join_cache_level_for_subselect_test=NULL; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 8c9a489..390d363 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -6096,4 +6096,19 @@ and t1.a in (select `test`.`t3`.`c` from `test`.`t3`); SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +--echo # +--echo # MDEV-16820: impossible where with inexpensive subquery +--echo # + +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); + +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); + +explain select * from t1 where (select max(b) from t2) = 10; +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; + +drop table t1,t2; + --echo End of 5.5 tests diff --git a/sql/item.h b/sql/item.h index b111d74..7c95239 100644 --- a/sql/item.h +++ b/sql/item.h @@ -1275,6 +1275,11 @@ class Item { virtual bool exists2in_processor(uchar *opt_arg) { return 0; } virtual bool find_selective_predicates_list_processor(uchar *opt_arg) { return 0; } + bool cleanup_is_expensive_cache_processor(uchar *arg) + { + is_expensive_cache= (int8)(-1); + return 0; + } /* To call bool function for all arguments */ struct bool_func_call_args diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 43b8d2b..19a727f 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1207,6 +1207,13 @@ TODO: make view to decide if it is possible to write to WHERE directly or make S if (optimize_constant_subqueries()) DBUG_RETURN(1); + if (conds && conds->has_subquery()) + (void) conds->walk(&Item::cleanup_is_expensive_cache_processor, + 0, (uchar*)0); + if (having && having->has_subquery()) + (void) having->walk(&Item::cleanup_is_expensive_cache_processor, + 0, (uchar*)0); + if (setup_jtbm_semi_joins(this, join_list, &conds)) DBUG_RETURN(1);

1 0

[Commits] a116e787869: MDEV-15822: WSREP: BF lock wait long for trx
by jan 25 Jul '18

25 Jul '18

revision-id: a116e7878696cf1e34de93eb2b8ca06db4b776bf (mariadb-10.1.34-11-ga116e787869) parent(s): be5698265a4195586142d1a34fdd1cce9d95d8a1 author: Jan Lindström committer: Jan Lindström timestamp: 2018-07-25 08:21:25 +0300 message: MDEV-15822: WSREP: BF lock wait long for trx In Galera BF (brute force) transactions may not wait for lock requests and normally BF-transaction would select transaction holding conflicting locks as a victim for rollback. However, background statistic calculation transaction is InnoDB internal transaction and it has no thd i.e. it can't be selected as a victim. If background statistics calculation transaction holds conflicting locks to statistics tables it will cause BF lock wait long error message. Correct way to handle background statistics calculation is to acquire thd for transaction but that change is too big for GA-releases and there are other reported problems on background statistics calculation. This fix avoids adding a table to background statistics calculation if --- .../r/galera_bf_background_statistics.result | 95 ++++++++++++++++++++++ .../galera/t/galera_bf_background_statistics.opt | 1 + .../galera/t/galera_bf_background_statistics.test | 49 +++++++++++ storage/innobase/row/row0mysql.cc | 25 +++++- 4 files changed, 167 insertions(+), 3 deletions(-) diff --git a/mysql-test/suite/galera/r/galera_bf_background_statistics.result b/mysql-test/suite/galera/r/galera_bf_background_statistics.result new file mode 100644 index 00000000000..dc367b065f6 --- /dev/null +++ b/mysql-test/suite/galera/r/galera_bf_background_statistics.result @@ -0,0 +1,95 @@ +SELECT @@innodb_stats_persistent; +@@innodb_stats_persistent +1 +CREATE TABLE t1 (f1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, f2 INTEGER DEFAULT NULL) ENGINE=InnoDB; +INSERT INTO t1(f1) values (NULL); +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +SELECT count(1) from t1; +count(1) +16384 +SET AUTOCOMMIT=OFF; +INSERT INTO t1 VALUES (9999999,NULL); +SELECT SLEEP(1000);; +ALTER TABLE t1 CHANGE f2 f2 INTEGER NOT NULL DEFAULT 1; +Warnings: +Warning 1265 Data truncated for column 'f2' at row 1 +Warning 1265 Data truncated for column 'f2' at row 2 +Warning 1265 Data truncated for column 'f2' at row 3 +Warning 1265 Data truncated for column 'f2' at row 4 +Warning 1265 Data truncated for column 'f2' at row 5 +Warning 1265 Data truncated for column 'f2' at row 6 +Warning 1265 Data truncated for column 'f2' at row 7 +Warning 1265 Data truncated for column 'f2' at row 8 +Warning 1265 Data truncated for column 'f2' at row 9 +Warning 1265 Data truncated for column 'f2' at row 10 +Warning 1265 Data truncated for column 'f2' at row 11 +Warning 1265 Data truncated for column 'f2' at row 12 +Warning 1265 Data truncated for column 'f2' at row 13 +Warning 1265 Data truncated for column 'f2' at row 14 +Warning 1265 Data truncated for column 'f2' at row 15 +Warning 1265 Data truncated for column 'f2' at row 16 +Warning 1265 Data truncated for column 'f2' at row 17 +Warning 1265 Data truncated for column 'f2' at row 18 +Warning 1265 Data truncated for column 'f2' at row 19 +Warning 1265 Data truncated for column 'f2' at row 20 +Warning 1265 Data truncated for column 'f2' at row 21 +Warning 1265 Data truncated for column 'f2' at row 22 +Warning 1265 Data truncated for column 'f2' at row 23 +Warning 1265 Data truncated for column 'f2' at row 24 +Warning 1265 Data truncated for column 'f2' at row 25 +Warning 1265 Data truncated for column 'f2' at row 26 +Warning 1265 Data truncated for column 'f2' at row 27 +Warning 1265 Data truncated for column 'f2' at row 28 +Warning 1265 Data truncated for column 'f2' at row 29 +Warning 1265 Data truncated for column 'f2' at row 30 +Warning 1265 Data truncated for column 'f2' at row 31 +Warning 1265 Data truncated for column 'f2' at row 32 +Warning 1265 Data truncated for column 'f2' at row 33 +Warning 1265 Data truncated for column 'f2' at row 34 +Warning 1265 Data truncated for column 'f2' at row 35 +Warning 1265 Data truncated for column 'f2' at row 36 +Warning 1265 Data truncated for column 'f2' at row 37 +Warning 1265 Data truncated for column 'f2' at row 38 +Warning 1265 Data truncated for column 'f2' at row 39 +Warning 1265 Data truncated for column 'f2' at row 40 +Warning 1265 Data truncated for column 'f2' at row 41 +Warning 1265 Data truncated for column 'f2' at row 42 +Warning 1265 Data truncated for column 'f2' at row 43 +Warning 1265 Data truncated for column 'f2' at row 44 +Warning 1265 Data truncated for column 'f2' at row 45 +Warning 1265 Data truncated for column 'f2' at row 46 +Warning 1265 Data truncated for column 'f2' at row 47 +Warning 1265 Data truncated for column 'f2' at row 48 +Warning 1265 Data truncated for column 'f2' at row 49 +Warning 1265 Data truncated for column 'f2' at row 50 +Warning 1265 Data truncated for column 'f2' at row 51 +Warning 1265 Data truncated for column 'f2' at row 52 +Warning 1265 Data truncated for column 'f2' at row 53 +Warning 1265 Data truncated for column 'f2' at row 54 +Warning 1265 Data truncated for column 'f2' at row 55 +Warning 1265 Data truncated for column 'f2' at row 56 +Warning 1265 Data truncated for column 'f2' at row 57 +Warning 1265 Data truncated for column 'f2' at row 58 +Warning 1265 Data truncated for column 'f2' at row 59 +Warning 1265 Data truncated for column 'f2' at row 60 +Warning 1265 Data truncated for column 'f2' at row 61 +Warning 1265 Data truncated for column 'f2' at row 62 +Warning 1265 Data truncated for column 'f2' at row 63 +Warning 1265 Data truncated for column 'f2' at row 64 +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +wsrep_local_aborts_increment +1 +DROP TABLE t1; diff --git a/mysql-test/suite/galera/t/galera_bf_background_statistics.opt b/mysql-test/suite/galera/t/galera_bf_background_statistics.opt new file mode 100644 index 00000000000..f9b1414a974 --- /dev/null +++ b/mysql-test/suite/galera/t/galera_bf_background_statistics.opt @@ -0,0 +1 @@ +--innodb_stats_persistent=ON diff --git a/mysql-test/suite/galera/t/galera_bf_background_statistics.test b/mysql-test/suite/galera/t/galera_bf_background_statistics.test new file mode 100644 index 00000000000..1030e8d4154 --- /dev/null +++ b/mysql-test/suite/galera/t/galera_bf_background_statistics.test @@ -0,0 +1,49 @@ +--source include/galera_cluster.inc +--source include/have_innodb.inc + +# +# Test a local transaction being aborted by a slave one while it is running a SLEEP() +# + +SELECT @@innodb_stats_persistent; + +CREATE TABLE t1 (f1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, f2 INTEGER DEFAULT NULL) ENGINE=InnoDB; +INSERT INTO t1(f1) values (NULL); +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +SELECT count(1) from t1; + +--connection node_2 +SET AUTOCOMMIT=OFF; +--let $wsrep_local_bf_aborts_before = `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_local_bf_aborts'` +INSERT INTO t1 VALUES (9999999,NULL); +--send SELECT SLEEP(1000); + +--connection node_1 +ALTER TABLE t1 CHANGE f2 f2 INTEGER NOT NULL DEFAULT 1; + +--connection node_2 +--error ER_LOCK_DEADLOCK +--reap + +--let $wsrep_local_bf_aborts_after = `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_local_bf_aborts'` + +# Check that wsrep_local_bf_aborts has been incremented by exactly 1 +--disable_query_log +--eval SELECT $wsrep_local_bf_aborts_after - $wsrep_local_bf_aborts_before = 1 AS wsrep_local_aborts_increment; +--enable_query_log + +DROP TABLE t1; + diff --git a/storage/innobase/row/row0mysql.cc b/storage/innobase/row/row0mysql.cc index 6a971a973f5..963d939380c 100644 --- a/storage/innobase/row/row0mysql.cc +++ b/storage/innobase/row/row0mysql.cc @@ -70,6 +70,13 @@ Created 9/17/2000 Heikki Tuuri #include "ha_prototypes.h" #include <algorithm> +#ifdef WITH_WSREP +#include "mysql/service_wsrep.h" +#include "wsrep.h" +#include "log.h" +#include "wsrep_mysqld.h" +#endif + /** Provide optional 4.x backwards compatibility for 5.0 and above */ UNIV_INTERN ibool row_rollback_on_timeout = FALSE; @@ -1093,6 +1100,7 @@ UNIV_INLINE void row_update_statistics_if_needed( /*============================*/ + trx_t* trx, dict_table_t* table) /*!< in: table */ { ib_uint64_t counter; @@ -1114,6 +1122,16 @@ row_update_statistics_if_needed( if (counter > n_rows / 10 /* 10% */ && dict_stats_auto_recalc_is_enabled(table)) { +#ifdef WITH_WSREP + if (wsrep_on(trx->mysql_thd) && + wsrep_thd_is_BF(trx->mysql_thd, FALSE)) { + WSREP_DEBUG("Avoiding background statistics" + " calculation for table %s", + table->name); + return; + } +#endif /* WITH_WSREP */ + dict_stats_recalc_pool_add(table); table->stat_modified_counter = 0; } @@ -1537,7 +1555,8 @@ row_insert_for_mysql( ut_memcpy(prebuilt->row_id, node->row_id_buf, DATA_ROW_ID_LEN); } - row_update_statistics_if_needed(table); + row_update_statistics_if_needed(trx, table); + trx->op_info = ""; return(err); @@ -1921,7 +1940,7 @@ row_update_for_mysql( that changes indexed columns, UPDATEs that change only non-indexed columns would not affect statistics. */ if (node->is_delete || !(node->cmpl_info & UPD_NODE_NO_ORD_CHANGE)) { - row_update_statistics_if_needed(prebuilt->table); + row_update_statistics_if_needed(trx, prebuilt->table); } else { /* Update the table modification counter even when non-indexed columns change if statistics is initialized. */ @@ -2158,7 +2177,7 @@ row_update_cascade_for_mysql( } } - row_update_statistics_if_needed(table); + row_update_statistics_if_needed(trx, table); return(err); }

1 0

[Commits] 9f8d0aed11e: MDEV-15822: WSREP: BF lock wait long for trx
by jan 25 Jul '18

25 Jul '18

revision-id: 9f8d0aed11eb06e3cb765f4fff8c2d61232d9b48 (mariadb-10.1.34-11-g9f8d0aed11e) parent(s): be5698265a4195586142d1a34fdd1cce9d95d8a1 author: Jan Lindström committer: Jan Lindström timestamp: 2018-07-25 07:54:23 +0300 message: MDEV-15822: WSREP: BF lock wait long for trx In Galera BF (brute force) transactions may not wait for lock requests and normally BF-transaction would select transaction holding conflicting locks as a victim for rollback. However, background statistic calculation transaction is InnoDB internal transaction and it has no thd i.e. it can't be selected as a victim. If background statistics calculation transaction holds conflicting locks to statistics tables it will cause BF lock wait long error message. Correct way to handle background statistics calculation is to acquire thd for transaction but that change is too big for GA-releases and there are other reported problems on background statistics calculation. This fix avoids adding a table to background statistics calculation if transaction is running Galera BF-transaction. --- .../galera/t/galera_bf_background_statistics.opt | 1 + .../galera/t/galera_bf_background_statistics.test | 49 ++++++++++++++++++++++ storage/innobase/row/row0mysql.cc | 25 +++++++++-- 3 files changed, 72 insertions(+), 3 deletions(-) diff --git a/mysql-test/suite/galera/t/galera_bf_background_statistics.opt b/mysql-test/suite/galera/t/galera_bf_background_statistics.opt new file mode 100644 index 00000000000..f9b1414a974 --- /dev/null +++ b/mysql-test/suite/galera/t/galera_bf_background_statistics.opt @@ -0,0 +1 @@ +--innodb_stats_persistent=ON diff --git a/mysql-test/suite/galera/t/galera_bf_background_statistics.test b/mysql-test/suite/galera/t/galera_bf_background_statistics.test new file mode 100644 index 00000000000..1030e8d4154 --- /dev/null +++ b/mysql-test/suite/galera/t/galera_bf_background_statistics.test @@ -0,0 +1,49 @@ +--source include/galera_cluster.inc +--source include/have_innodb.inc + +# +# Test a local transaction being aborted by a slave one while it is running a SLEEP() +# + +SELECT @@innodb_stats_persistent; + +CREATE TABLE t1 (f1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, f2 INTEGER DEFAULT NULL) ENGINE=InnoDB; +INSERT INTO t1(f1) values (NULL); +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +SELECT count(1) from t1; + +--connection node_2 +SET AUTOCOMMIT=OFF; +--let $wsrep_local_bf_aborts_before = `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_local_bf_aborts'` +INSERT INTO t1 VALUES (9999999,NULL); +--send SELECT SLEEP(1000); + +--connection node_1 +ALTER TABLE t1 CHANGE f2 f2 INTEGER NOT NULL DEFAULT 1; + +--connection node_2 +--error ER_LOCK_DEADLOCK +--reap + +--let $wsrep_local_bf_aborts_after = `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_local_bf_aborts'` + +# Check that wsrep_local_bf_aborts has been incremented by exactly 1 +--disable_query_log +--eval SELECT $wsrep_local_bf_aborts_after - $wsrep_local_bf_aborts_before = 1 AS wsrep_local_aborts_increment; +--enable_query_log + +DROP TABLE t1; + diff --git a/storage/innobase/row/row0mysql.cc b/storage/innobase/row/row0mysql.cc index 6a971a973f5..963d939380c 100644 --- a/storage/innobase/row/row0mysql.cc +++ b/storage/innobase/row/row0mysql.cc @@ -70,6 +70,13 @@ Created 9/17/2000 Heikki Tuuri #include "ha_prototypes.h" #include <algorithm> +#ifdef WITH_WSREP +#include "mysql/service_wsrep.h" +#include "wsrep.h" +#include "log.h" +#include "wsrep_mysqld.h" +#endif + /** Provide optional 4.x backwards compatibility for 5.0 and above */ UNIV_INTERN ibool row_rollback_on_timeout = FALSE; @@ -1093,6 +1100,7 @@ UNIV_INLINE void row_update_statistics_if_needed( /*============================*/ + trx_t* trx, dict_table_t* table) /*!< in: table */ { ib_uint64_t counter; @@ -1114,6 +1122,16 @@ row_update_statistics_if_needed( if (counter > n_rows / 10 /* 10% */ && dict_stats_auto_recalc_is_enabled(table)) { +#ifdef WITH_WSREP + if (wsrep_on(trx->mysql_thd) && + wsrep_thd_is_BF(trx->mysql_thd, FALSE)) { + WSREP_DEBUG("Avoiding background statistics" + " calculation for table %s", + table->name); + return; + } +#endif /* WITH_WSREP */ + dict_stats_recalc_pool_add(table); table->stat_modified_counter = 0; } @@ -1537,7 +1555,8 @@ row_insert_for_mysql( ut_memcpy(prebuilt->row_id, node->row_id_buf, DATA_ROW_ID_LEN); } - row_update_statistics_if_needed(table); + row_update_statistics_if_needed(trx, table); + trx->op_info = ""; return(err); @@ -1921,7 +1940,7 @@ row_update_for_mysql( that changes indexed columns, UPDATEs that change only non-indexed columns would not affect statistics. */ if (node->is_delete || !(node->cmpl_info & UPD_NODE_NO_ORD_CHANGE)) { - row_update_statistics_if_needed(prebuilt->table); + row_update_statistics_if_needed(trx, prebuilt->table); } else { /* Update the table modification counter even when non-indexed columns change if statistics is initialized. */ @@ -2158,7 +2177,7 @@ row_update_cascade_for_mysql( } } - row_update_statistics_if_needed(table); + row_update_statistics_if_needed(trx, table); return(err); }

1 0

[Commits] d567f16: MDEV-16820 Lost 'Impossible where' from query with inexpensive subquery
by IgorBabaev 25 Jul '18

25 Jul '18

revision-id: d567f1611e27a17427380e9aae67939792f68ad1 (mariadb-5.5.60-47-gd567f16) parent(s): 9cea4ccf12cb6e8746b9b440d9c62408a9ef04af author: Igor Babaev committer: Igor Babaev timestamp: 2018-07-24 20:00:28 -0700 message: MDEV-16820 Lost 'Impossible where' from query with inexpensive subquery This patch fixes another problem introduced by the patch for mdev-4817. The latter changed Item_cond::fix_fields() in such a way that it could call the virtual method is_expensive(). With the first its call the method saves the result in Item::is_expensive_cache. For all next calls the method returns the result from this cache. So if the item once was determined as expensive the method always returns true. For subqueries it's not good, because non-optimized subqueries always is considered as expensive. It means that the cache should be invalidated after the call of optimize_constant_subqueries(). --- mysql-test/r/subselect.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_mat.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_opts.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_scache.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_semijoin.result | 16 ++++++++++++++++ mysql-test/t/subselect.test | 15 +++++++++++++++ sql/item.h | 5 +++++ sql/sql_select.cc | 7 +++++++ 8 files changed, 107 insertions(+) diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index e2f2b65..cdedc02 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -7171,4 +7171,20 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index 25ef4a7..a729129 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -7168,6 +7168,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests set optimizer_switch=default; select @@optimizer_switch like '%materialization=on%'; diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index 074874f..c41fa1b 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -7166,5 +7166,21 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests set @optimizer_switch_for_subselect_test=null; diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index de49585..1c18135 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -7177,6 +7177,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests set optimizer_switch=default; select @@optimizer_switch like '%subquery_cache=on%'; diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 46a46c9..89c6712 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -7166,6 +7166,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests set @optimizer_switch_for_subselect_test=null; set @join_cache_level_for_subselect_test=NULL; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index e6233e9..4e35032 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -6060,4 +6060,19 @@ and t1.a in (select `test`.`t3`.`c` from `test`.`t3`); SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +--echo # +--echo # MDEV-16820: impossible where with inexpensive subquery +--echo # + +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); + +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); + +explain select * from t1 where (select max(b) from t2) = 10; +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; + +drop table t1,t2; + --echo End of 5.5 tests diff --git a/sql/item.h b/sql/item.h index d756cf8..1bded73 100644 --- a/sql/item.h +++ b/sql/item.h @@ -1234,6 +1234,11 @@ class Item { { return FALSE; } + bool cleanup_is_expensive_cache_processor(uchar *arg) + { + is_expensive_cache= (int8)(-1); + return 0; + } /* To call bool function for all arguments */ struct bool_func_call_args diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 6a64a0e..5228dd4 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1098,6 +1098,13 @@ JOIN::optimize() if (optimize_constant_subqueries()) DBUG_RETURN(1); + if (conds && conds->has_subquery()) + (void) conds->walk(&Item::cleanup_is_expensive_cache_processor, + 0, (uchar*)0); + if (having && having->has_subquery()) + (void) having->walk(&Item::cleanup_is_expensive_cache_processor, + 0, (uchar*)0); + if (setup_jtbm_semi_joins(this, join_list, &conds)) DBUG_RETURN(1);

1 0

[Commits] 21e1d0bba8c: make plugins.processlist more robust
by Oleksandr Byelkin 24 Jul '18

24 Jul '18

revision-id: 21e1d0bba8c7cabb07b2c809dfaad651c7b66f6d (mariadb-10.0.35-71-g21e1d0bba8c) parent(s): e0139c2b92ba1f8819a3ed75d020d9665545e2bf author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2018-07-24 18:24:21 +0200 message: make plugins.processlist more robust # Conflicts: # mysql-test/suite/plugins/r/processlist.result --- mysql-test/suite/plugins/r/processlist.result | 4 +--- mysql-test/suite/plugins/t/processlist.test | 5 +++-- 2 files changed, 4 insertions(+), 5 deletions(-) diff --git a/mysql-test/suite/plugins/r/processlist.result b/mysql-test/suite/plugins/r/processlist.result index d08ea9d3523..9200c865533 100644 --- a/mysql-test/suite/plugins/r/processlist.result +++ b/mysql-test/suite/plugins/r/processlist.result @@ -1,8 +1,6 @@ create table t1 (a int) engine=innodb; start transaction; insert t1 values (1); -state from show engine innodb status +state from show engine innodb status, must be empty -state from show processlist - drop table t1; diff --git a/mysql-test/suite/plugins/t/processlist.test b/mysql-test/suite/plugins/t/processlist.test index e8f03aacb10..39b715b867b 100644 --- a/mysql-test/suite/plugins/t/processlist.test +++ b/mysql-test/suite/plugins/t/processlist.test @@ -8,11 +8,12 @@ start transaction; insert t1 values (1); let id=`select connection_id()`; connect con2,localhost,root; +let $wait_condition=select state='' from information_schema.processlist where id = $id; +--source include/wait_condition.inc replace_regex /\"/-/; #" let s=`show engine innodb status`; disable_query_log; -eval select regexp_replace("$s", '(?s)^.*MySQL thread id $id,.*root([^\n]*)\n.*', '\\\\1') as `state from show engine innodb status`; -eval select state as `state from show processlist` from information_schema.processlist where id = $id; +eval select regexp_replace("$s", '(?s)^.*MySQL thread id $id,.*root([^\n]*)\n.*', '\\\\1') as `state from show engine innodb status, must be empty`; enable_query_log; disconnect con2; connection default;

1 0

[Commits] e0139c2b92b: fix plugins.processlist
by Oleksandr Byelkin 24 Jul '18

24 Jul '18

revision-id: e0139c2b92ba1f8819a3ed75d020d9665545e2bf (mariadb-10.0.35-70-ge0139c2b92b) parent(s): 5e67567b158db5d294b11b310ff35a454f58d034 author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2018-07-24 18:16:41 +0200 message: fix plugins.processlist make it not to fail when `show engine innodb status` output contains a double quote --- mysql-test/suite/plugins/t/processlist.test | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) diff --git a/mysql-test/suite/plugins/t/processlist.test b/mysql-test/suite/plugins/t/processlist.test index 5aacef317b9..e8f03aacb10 100644 --- a/mysql-test/suite/plugins/t/processlist.test +++ b/mysql-test/suite/plugins/t/processlist.test @@ -8,7 +8,8 @@ start transaction; insert t1 values (1); let id=`select connection_id()`; connect con2,localhost,root; -let s=query_get_value(show engine innodb status,Status,1); +replace_regex /\"/-/; #" +let s=`show engine innodb status`; disable_query_log; eval select regexp_replace("$s", '(?s)^.*MySQL thread id $id,.*root([^\n]*)\n.*', '\\\\1') as `state from show engine innodb status`; eval select state as `state from show processlist` from information_schema.processlist where id = $id;

1 0