revision-id: 215dcab727366f387ad1096f1335ed27523c5b5e (mariadb-5.5.61-31-g215dcab7273) parent(s): 8009d85cc2e9245f6e8be06397914433e4b20007 author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2018-10-24 11:31:36 +0200 message: more fixes --- mysql-test/r/subselect.result | 18 ++++++++++--- mysql-test/t/subselect.test | 13 +++++++--- sql/item.cc | 10 +------- sql/item_subselect.cc | 60 +++++++++++++++++++++++++++++++++++-------- sql/item_subselect.h | 16 ++++++++---- sql/opt_subselect.cc | 5 +--- sql/sql_lex.cc | 4 +++ 7 files changed, 91 insertions(+), 35 deletions(-) diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 934c145e995..3d3aafac464 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -1156,19 +1156,19 @@ CREATE TABLE t1 (a int(1)); EXPLAIN EXTENDED SELECT (SELECT RAND() FROM t1) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found -2 UNCACHEABLE SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table +2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: Note 1003 select (select rand() from `test`.`t1`) AS `(SELECT RAND() FROM t1)` from `test`.`t1` EXPLAIN EXTENDED SELECT (SELECT ENCRYPT('test') FROM t1) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found -2 UNCACHEABLE SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table +2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: Note 1003 select (select encrypt('test') from `test`.`t1`) AS `(SELECT ENCRYPT('test') FROM t1)` from `test`.`t1` EXPLAIN EXTENDED SELECT (SELECT BENCHMARK(1,1) FROM t1) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found -2 UNCACHEABLE SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table +2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: Note 1003 select (select benchmark(1,1) from `test`.`t1`) AS `(SELECT BENCHMARK(1,1) FROM t1)` from `test`.`t1` drop table t1; @@ -4059,6 +4059,18 @@ SELECT COUNT(*), a FROM t1 GROUP BY a HAVING (SELECT MIN(m) FROM t2 WHERE m = count(*)) > 1; COUNT(*) a +2 2 +3 3 +SELECT COUNT(*), a +FROM t1 GROUP BY "www" + HAVING (SELECT MAX(m) FROM t2 WHERE m < count(*)) > 1; +COUNT(*) a +6 2 +SELECT COUNT(*), a +FROM t1 +HAVING (SELECT MAX(m) FROM t2 WHERE m < count(*)) > 1; +COUNT(*) a +6 2 DROP TABLE t1,t2; CREATE TABLE t1 (a int, b int); CREATE TABLE t2 (m int, n int); diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 9f475ae3bf0..a3781823f5a 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -2899,6 +2899,14 @@ SELECT COUNT(*), a FROM t1 GROUP BY a HAVING (SELECT MIN(m) FROM t2 WHERE m = count(*)) > 1; +SELECT COUNT(*), a + FROM t1 GROUP BY "www" + HAVING (SELECT MAX(m) FROM t2 WHERE m < count(*)) > 1; + +SELECT COUNT(*), a + FROM t1 + HAVING (SELECT MAX(m) FROM t2 WHERE m < count(*)) > 1; + DROP TABLE t1,t2; @@ -6081,12 +6089,11 @@ drop table t1,t2; CREATE TABLE t2 (ser1 char(20) NOT NULL, ids1 int NOT NULL, nm1 char(32) NOT NULL); INSERT INTO t2 VALUES ('char1',1,'char2'),('char2',2,'char2'); - + CREATE TABLE t3 (id2 int ); INSERT INTO t3 VALUES (1),(2); - + CREATE TABLE t5 (nm1 varchar(255), ls1 varchar(64), state enum('Active','Inactive','Event')); - SELECT * FROM (SELECT nm1, diff --git a/sql/item.cc b/sql/item.cc index 1f1aeacf96e..d2e3b847f5c 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -7419,15 +7419,7 @@ void Item_ref::make_field(Send_field *field) Item *Item_ref::get_tmp_table_item(THD *thd) { if (!result_field) - { - if (!with_sum_func && !const_item()) - return (*ref)->get_tmp_table_item(thd); - else - { - DBUG_ASSERT((*ref)->const_item() || (*ref)->with_sum_func); - return copy_or_same(thd); - } - } + return (*ref)->get_tmp_table_item(thd); Item_field *item= new Item_field(result_field); if (item) diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 1938db86743..c578beb9fc9 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -296,9 +296,12 @@ bool Item_subselect::fix_fields(THD *thd_param, Item **ref) if ((uncacheable= engine->uncacheable() & ~UNCACHEABLE_EXPLAIN)) { const_item_cache= 0; - if (uncacheable & UNCACHEABLE_RAND) + if (uncacheable & (UNCACHEABLE_RAND || UNCACHEABLE_SIDEEFFECT)) used_tables_cache|= RAND_TABLE_BIT; } + else + const_item_cache= 1; + fixed= 1; end: @@ -393,7 +396,7 @@ bool Item_subselect::set_fake_select_as_master_processor(uchar *arg) } -bool Item_subselect::mark_as_dependent(THD *thd, st_select_lex *select, +bool Item_subselect::mark_as_dependent(THD *thd, st_select_lex *select, Item *item) { if (inside_first_fix_fields) @@ -518,6 +521,14 @@ void Item_subselect::recalc_used_tables(st_select_lex *new_parent, upper->item->update_used_tables(); */ } + else + { + /* + It is grouping so lets assume that all tables can influence + result to keep it more simple. + */ + used_tables_cache|= sel->join->all_tables_map(); + } } } if (!found) @@ -854,8 +865,7 @@ void Item_subselect::fix_length_and_dec() table_map Item_subselect::used_tables() const { - return (table_map) ((!forced_const && !const_item_cache && - (engine->uncacheable() & ~UNCACHEABLE_EXPLAIN))? + return (table_map) ((engine->uncacheable() & ~UNCACHEABLE_EXPLAIN)? used_tables_cache : 0L); } @@ -863,9 +873,10 @@ table_map Item_subselect::used_tables() const bool Item_subselect::const_item() const { DBUG_ASSERT(thd); + DBUG_ASSERT(!forced_const || (forced_const && used_tables_cache == 0)); return (thd->lex->context_analysis_only ? FALSE : - used_tables() == 0); + forced_const || const_item_cache); } Item *Item_subselect::get_tmp_table_item(THD *thd_arg) @@ -880,12 +891,29 @@ void Item_subselect::update_used_tables() if (!forced_const) { recalc_used_tables(parent_select, FALSE); - if (!(engine->uncacheable() & ~UNCACHEABLE_EXPLAIN)) - { + fix_const_according_to_uncachability(); + } +} + + +void Item_subselect::fix_const_according_to_uncachability() +{ + uint8 uncachability= (engine->uncacheable() & ~UNCACHEABLE_EXPLAIN); + if (uncachability == 0 || + (uncachability == UNCACHEABLE_DEPENDENT_GENERATED && // did all used tables become static? - if (!(used_tables_cache & ~engine->upper_select_const_tables())) - const_item_cache= 1; - } + !(used_tables_cache & ~engine->upper_select_const_tables()))) + { + used_tables_cache= 0; + const_item_cache= 1; + } + else + { + if (uncachability & (UNCACHEABLE_RAND | UNCACHEABLE_SIDEEFFECT)) + used_tables_cache|= RAND_TABLE_BIT; + + DBUG_ASSERT(used_tables_cache != 0); + const_item_cache= 0; } } @@ -2463,7 +2491,7 @@ bool Item_in_subselect::create_in_to_exists_cond(JOIN *join_arg) /* The IN=>EXISTS transformation makes non-correlated subqueries correlated. */ - if (!left_expr->const_item() || left_expr->is_expensive()) + if (!left_expr->const_item()) { join_arg->select_lex->uncacheable|= UNCACHEABLE_DEPENDENT_INJECTED; join_arg->select_lex->master_unit()->uncacheable|= @@ -3018,6 +3046,10 @@ bool subselect_union_engine::no_rows() void subselect_uniquesubquery_engine::cleanup() { DBUG_ENTER("subselect_uniquesubquery_engine::cleanup"); + Item_in_subselect *in_subs= (Item_in_subselect *) item; + tab->select->uncacheable= (in_subs->left_expr->const_item() ? + 0 : + UNCACHEABLE_DEPENDENT_INJECTED); /* Note for mergers: we don't have to, and actually must not de-initialize tab->table->file here. @@ -3441,6 +3473,12 @@ bool subselect_uniquesubquery_engine::copy_ref_key(bool skip_constants) } +uint8 subselect_uniquesubquery_engine::uncacheable() +{ + return tab->select->uncachable; +} + + /** Execute subselect via unique index lookup diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 75822ff8c6b..ebb2445fc4f 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -107,7 +107,7 @@ class Item_subselect :public Item_result_field What is being referred. This may be NULL when we're referring to an aggregate function. */ - Item *item; + Item *item; }; /* References from within this subquery to somewhere outside of it (i.e. to @@ -182,7 +182,7 @@ class Item_subselect :public Item_result_field void make_const() { used_tables_cache= 0; - const_item_cache= 0; + const_item_cache= 1; forced_const= TRUE; } virtual void fix_length_and_dec(); @@ -247,7 +247,8 @@ class Item_subselect :public Item_result_field bool limit_index_condition_pushdown_processor(uchar *opt_arg) { return TRUE; - } + } + void fix_const_according_to_uncachability(); friend class select_result_interceptor; friend class Item_in_optimizer; @@ -893,14 +894,19 @@ class subselect_uniquesubquery_engine: public subselect_engine subselect_uniquesubquery_engine(THD *thd_arg, st_join_table *tab_arg, Item_subselect *subs, Item *where) :subselect_engine(subs, 0), tab(tab_arg), cond(where) - {} + { + Item_in_subselect *in_subs= (Item_in_subselect *) item; + tab->select->uncacheable= (in_subs->left_expr->const_item() ? + 0 : + UNCACHEABLE_DEPENDENT_INJECTED); + } ~subselect_uniquesubquery_engine(); void cleanup(); int prepare(THD *); void fix_length_and_dec(Item_cache** row); int exec(); uint cols() { return 1; } - uint8 uncacheable() { return UNCACHEABLE_DEPENDENT_INJECTED; } + uint8 uncacheable(); void exclude(); table_map upper_select_const_tables() { return 0; } virtual void print (String *str, enum_query_type query_type); diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index f472cf16710..32c71154bcb 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -5898,10 +5898,7 @@ bool JOIN::choose_subquery_plan(table_map join_tables) If the injected predicate is correlated the IN->EXISTS transformation make the subquery dependent. */ - if ((in_to_exists_where && - in_to_exists_where->used_tables() & OUTER_REF_TABLE_BIT) || - (in_to_exists_having && - in_to_exists_having->used_tables() & OUTER_REF_TABLE_BIT)) + if (!in_subs->left_expr->const_item()) { in_subs->unit->uncacheable|= UNCACHEABLE_DEPENDENT_INJECTED; select_lex->uncacheable|= UNCACHEABLE_DEPENDENT_INJECTED; diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index cfbde25314b..d6426588a81 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -3568,7 +3568,11 @@ bool st_select_lex::optimize_unflattened_subqueries(bool const_only) if (empty_union_result) subquery_predicate->no_rows_in_result(); if (!is_correlated_unit) + { un->uncacheable&= ~UNCACHEABLE_DEPENDENT; + + un->item->fix_const_according_to_uncachability(); + } subquery_predicate->is_correlated= is_correlated_unit; } }