[Commits] 215dcab7273: more fixes

24 Oct 2018

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;
     }
   }

    