MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Evgeny Potemkin Date:November 13 2009 3:21pm
Subject:bzr commit into mysql-5.1-bugteam branch (epotemkin:3202) Bug#33546
View as plain text  
#At file:///work/bzrroot/33546-bug-5.1-bugteam/ based on revid:epotemkin@stripped

 3202 Evgeny Potemkin	2009-11-13
      Bug#33546: Slowdown on re-evaluation of constant expressions.
      
      Constant expressions in WHERE/HAVING/ON clauses aren't cached and evaluated
      for each row. This causes slowdown of query execution especially if constant
      UDF/SP function are used.
      
      Now WHERE/HAVING/ON expressions are analyzed in the top-bottom direction with
      help of the compile function. When analyzer meets a constant item it
      sets a flag for the tree transformer to cache the item and doesn't allow tree
      walker to go deeper. Thus, the topmost item of a constant expression if
      cached. This is done after all other optimizations were applied to
      WHERE/HAVING/ON expressions
      
      A helper function called cache_const_exprs is added to the JOIN class.
      It calls compile method with caching analyzer and transformer on WHERE,
      HAVING, ON expressions if they're present.
      The cache_const_expr_analyzer and cache_const_expr_transformer functions are
      added to the Item class. The first one check if the item can be cached and
      the second caches it if so.
      A new Item_cache_datetime class is derived from the Item_cache class.
      It caches both int and string values of the underlying item independently to
      avoid DATETIME aware int-to-string conversion. Thus it completely relies on
      the ability of the underlying item to correctly convert DATETIME value from
      int to string and vice versa.
     @ mysql-test/r/func_like.result
        A test case result is corrected after fixing bug#33546.
     @ mysql-test/r/select.result
        Added a test case for the bug#33546.
     @ mysql-test/r/subselect.result
        A test case result is corrected after fixing bug#33546.
     @ mysql-test/t/select.test
        Added a test case for the bug#33546.
     @ sql/item.cc
        Bug#33546: Slowdown on re-evaluation of constant expressions.
        The cache_const_expr_analyzer and cache_const_expr_transformer functions are
        added to the Item class. The first one check if the item can be cached and
        the second caches it if so.
        Item_cache_datetime class implementation is added.
     @ sql/item.h
        Bug#33546: Slowdown on re-evaluation of constant expressions.
        Item_ref and Item_cache classes now returns basic_const_item
        from underlying item.
        The cache_const_expr_analyzer and cache_const_expr_transformer functions are
        added to the Item class.
     @ sql/item_cmpfunc.cc
        Bug#33546: Slowdown on re-evaluation of constant expressions.
        
        The Item_cond::compile function now is PS-safe.
     @ sql/sql_select.cc
        Bug#33546: Slowdown on re-evaluation of constant expressions.
        
        A helper function called cache_const_exprs is added to the JOIN class.
        It calls compile method with caching analyzer and transformer on WHERE,
        HAVING, ON expressions if they're present.
     @ sql/sql_select.h
        Bug#33546: Slowdown on re-evaluation of constant expressions.
        A helper function called cache_const_exprs is added to the JOIN class.

    modified:
      mysql-test/r/func_like.result
      mysql-test/r/select.result
      mysql-test/r/subselect.result
      mysql-test/t/select.test
      sql/item.cc
      sql/item.h
      sql/item_cmpfunc.cc
      sql/sql_select.cc
      sql/sql_select.h
=== modified file 'mysql-test/r/func_like.result'
--- a/mysql-test/r/func_like.result	2008-02-12 19:09:16 +0000
+++ b/mysql-test/r/func_like.result	2009-11-13 15:21:37 +0000
@@ -10,7 +10,7 @@ explain extended select * from t1 where 
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	index	a	a	13	NULL	5	20.00	Using where; Using index
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` like concat('abc','%'))
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` like <cache>(concat('abc','%')))
 select * from t1 where a like "abc%";
 a
 abc

=== modified file 'mysql-test/r/select.result'
--- a/mysql-test/r/select.result	2009-10-30 14:13:13 +0000
+++ b/mysql-test/r/select.result	2009-11-13 15:21:37 +0000
@@ -4425,7 +4425,7 @@ INSERT INTO t1 VALUES (2),(3);
 SELECT 1 FROM t1 WHERE a <> 1 AND NOT
 ROW(a,a) <=> ROW((SELECT 1 FROM t1 WHERE 1=2),(SELECT 1 FROM t1))
 INTO @var0;
-ERROR 21000: Subquery returns more than 1 row
+ERROR 42000: Result consisted of more than one row
 DROP TABLE t1;
 End of 5.0 tests
 create table t1(a INT, KEY (a));
@@ -4577,4 +4577,70 @@ field2
 15:13:38
 drop table A,AA,B,BB;
 #end of test for bug#45266
+#
+# Bug#33546: Slowdown on re-evaluation of constant expressions.
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (2);
+SELECT * FROM t1 WHERE a = 1 + 1;
+a
+2
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a = 1 + 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = <cache>((1 + 1)))
+SELECT * FROM t1 HAVING a = 1 + 1;
+a
+2
+EXPLAIN EXTENDED SELECT * FROM t1 HAVING a = 1 + 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	100.00	
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` having (`test`.`t1`.`a` = <cache>((1 + 1)))
+SELECT * FROM t1, t2 WHERE a = b + (1 + 1);
+a	b
+4	2
+EXPLAIN EXTENDED SELECT * FROM t1, t2 WHERE a = b + (1 + 1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	1	100.00	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,'2' AS `b` from `test`.`t1` join `test`.`t2` where (`test`.`t1`.`a` = <cache>(('2' + (1 + 1))))
+SELECT * FROM t2 LEFT JOIN t1 ON a = b + 1;
+b	a
+2	3
+EXPLAIN EXTENDED SELECT * FROM t2 LEFT JOIN t1 ON a = b + 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	1	100.00	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	100.00	
+Warnings:
+Note	1003	select '2' AS `b`,`test`.`t1`.`a` AS `a` from `test`.`t2` left join `test`.`t1` on((`test`.`t1`.`a` = <cache>(('2' + 1)))) where 1
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a > UNIX_TIMESTAMP('2009-03-10 00:00:00');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > <cache>(unix_timestamp('2009-03-10 00:00:00')))
+CREATE FUNCTION f1() RETURNS INT DETERMINISTIC
+BEGIN
+SET @cnt := @cnt + 1;
+RETURN 1;
+END;|
+SET @cnt := 0;
+SELECT * FROM t1 WHERE a = f1();
+a
+1
+SELECT @cnt;
+@cnt
+1
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a = f1();
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = <cache>(`f1`()))
+DROP TABLE t1, t2;
+DROP FUNCTION f1;
+# End of bug#33546
 End of 5.1 tests

=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result	2009-11-03 16:23:05 +0000
+++ b/mysql-test/r/subselect.result	2009-11-13 15:21:37 +0000
@@ -735,7 +735,7 @@ id	select_type	table	type	possible_keys	
 Warnings:
 Note	1249	Select 3 was reduced during optimization
 Note	1249	Select 2 was reduced during optimization
-Note	1003	select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = (1 + 1))
+Note	1003	select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = <cache>((1 + 1)))
 EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	index	NULL	id	5	NULL	2	100.00	Using where; Using index

=== modified file 'mysql-test/t/select.test'
--- a/mysql-test/t/select.test	2009-10-30 14:13:13 +0000
+++ b/mysql-test/t/select.test	2009-11-13 15:21:37 +0000
@@ -3765,7 +3765,7 @@ CREATE TABLE t1(a INT);
 INSERT INTO t1 VALUES (2),(3);
 
 --echo # Should not crash
---error ER_SUBQUERY_NO_1_ROW
+--error ER_TOO_MANY_ROWS
 SELECT 1 FROM t1 WHERE a <> 1 AND NOT
 ROW(a,a) <=> ROW((SELECT 1 FROM t1 WHERE 1=2),(SELECT 1 FROM t1))
 INTO @var0;
@@ -3918,4 +3918,38 @@ SELECT table1 .`time_key` field2  FROM B
 
 drop table A,AA,B,BB;
 --echo #end of test for bug#45266
+
+--echo #
+--echo # Bug#33546: Slowdown on re-evaluation of constant expressions.
+--echo #
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (2);
+SELECT * FROM t1 WHERE a = 1 + 1;
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a = 1 + 1;
+SELECT * FROM t1 HAVING a = 1 + 1;
+EXPLAIN EXTENDED SELECT * FROM t1 HAVING a = 1 + 1;
+SELECT * FROM t1, t2 WHERE a = b + (1 + 1);
+EXPLAIN EXTENDED SELECT * FROM t1, t2 WHERE a = b + (1 + 1);
+SELECT * FROM t2 LEFT JOIN t1 ON a = b + 1;
+EXPLAIN EXTENDED SELECT * FROM t2 LEFT JOIN t1 ON a = b + 1;
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a > UNIX_TIMESTAMP('2009-03-10 00:00:00');
+
+delimiter |;
+CREATE FUNCTION f1() RETURNS INT DETERMINISTIC
+BEGIN
+  SET @cnt := @cnt + 1;
+  RETURN 1;
+END;|
+delimiter ;|
+
+SET @cnt := 0;
+SELECT * FROM t1 WHERE a = f1();
+SELECT @cnt;
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a = f1();
+DROP TABLE t1, t2;
+DROP FUNCTION f1;
+--echo # End of bug#33546
+
 --echo End of 5.1 tests

=== modified file 'sql/item.cc'
--- a/sql/item.cc	2009-11-06 19:42:24 +0000
+++ b/sql/item.cc	2009-11-13 15:21:37 +0000
@@ -5602,6 +5602,67 @@ bool Item::send(Protocol *protocol, Stri
 }
 
 
+/**
+  Check if an item is a constant one and can be cached.
+
+  @param arg [out] TRUE <=> Cache this item.
+
+  @return TRUE  Go deeper in item tree.
+  @return FALSE Don't go deeper in item tree.
+*/
+
+bool Item::cache_const_expr_analyzer(uchar **arg)
+{
+  bool *cache_flag= (bool*)*arg;
+  if (!*cache_flag)
+  {
+    Item *item= real_item();
+    /*
+      Cache constant items unless it's a basic constant, constant field or
+      a subselect (they use their own cache).
+    */
+    if (const_item() &&
+        !(item->basic_const_item() || item->type() == Item::FIELD_ITEM ||
+          item->type() == SUBSELECT_ITEM ||
+           /*
+             Do not cache GET_USER_VAR() function as its const_item() may
+             return TRUE for the current thread but it still may change
+             during the execution.
+           */
+          (item->type() == Item::FUNC_ITEM &&
+           ((Item_func*)item)->functype() == Item_func::GUSERVAR_FUNC)))
+      *cache_flag= TRUE;
+    return TRUE;
+  }
+  return FALSE;
+}
+
+
+/**
+  Cache item if needed.
+
+  @param arg   TRUE <=> Cache this item.
+
+  @return cache if cache needed.
+  @return this otherwise.
+*/
+
+Item* Item::cache_const_expr_transformer(uchar *arg)
+{
+  if (*(bool*)arg)
+  {
+    *((bool*)arg)= FALSE;
+    Item_cache *cache= Item_cache::get_cache(this);
+    if (!cache)
+      return NULL;
+    cache->setup(this);
+    cache->store(this);
+    return cache;
+  }
+  return this;
+}
+
+
 bool Item_field::send(Protocol *protocol, String *buffer)
 {
   return protocol->store(result_field);
@@ -6963,6 +7024,10 @@ Item_cache* Item_cache::get_cache(const 
   case DECIMAL_RESULT:
     return new Item_cache_decimal();
   case STRING_RESULT:
+    if (item->field_type() == MYSQL_TYPE_DATE ||
+        item->field_type() == MYSQL_TYPE_DATETIME ||
+        item->field_type() == MYSQL_TYPE_TIME)
+      return new Item_cache_datetime(item->field_type());
     return new Item_cache_str(item);
   case ROW_RESULT:
     return new Item_cache_row();
@@ -7044,6 +7109,63 @@ longlong Item_cache_int::val_int()
   return value;
 }
 
+void  Item_cache_datetime::cache_value()
+{
+  value_cached= TRUE;
+  String *res= example->str_result(&str_value);
+  if (res && res != &str_value)
+    str_value.copy(*res);
+  /* Assume here that the underlying item will do correct conversion.*/
+  int_value= example->val_int_result();
+  null_value= example->null_value;
+  unsigned_flag= example->unsigned_flag;
+}
+
+
+void Item_cache_datetime::store(Item *item, longlong val_arg)
+{
+  /* An explicit values is given, save it. */
+  value_cached= TRUE;
+  int_value= val_arg;
+  null_value= item->null_value;
+  unsigned_flag= item->unsigned_flag;
+}
+
+
+String *Item_cache_datetime::val_str(String *str)
+{
+  DBUG_ASSERT(fixed == 1);
+  if (!value_cached)
+    cache_value();
+  return &str_value;
+}
+
+
+my_decimal *Item_cache_datetime::val_decimal(my_decimal *decimal_val)
+{
+  DBUG_ASSERT(fixed == 1);
+  if (!value_cached)
+    cache_value();
+  int2my_decimal(E_DEC_FATAL_ERROR, int_value, unsigned_flag, decimal_val);
+  return decimal_val;
+}
+
+double Item_cache_datetime::val_real()
+{
+  DBUG_ASSERT(fixed == 1);
+  if (!value_cached)
+    cache_value();
+  return (double) int_value;
+}
+
+longlong Item_cache_datetime::val_int()
+{
+  DBUG_ASSERT(fixed == 1);
+  if (!value_cached)
+    cache_value();
+  return int_value;
+}
+
 void Item_cache_real::cache_value()
 {
   value_cached= TRUE;

=== modified file 'sql/item.h'
--- a/sql/item.h	2009-11-06 19:42:24 +0000
+++ b/sql/item.h	2009-11-13 15:21:37 +0000
@@ -895,6 +895,9 @@ public:
   virtual bool reset_query_id_processor(uchar *query_id_arg) { return 0; }
   virtual bool is_expensive_processor(uchar *arg) { return 0; }
   virtual bool register_field_in_read_map(uchar *arg) { return 0; }
+
+  virtual bool cache_const_expr_analyzer(uchar **arg);
+  virtual Item* cache_const_expr_transformer(uchar *arg);
   /*
     Check if a partition function is allowed
     SYNOPSIS
@@ -2263,6 +2266,7 @@ public:
     if (ref && result_type() == ROW_RESULT)
       (*ref)->bring_value();
   }
+  bool basic_const_item() { return (*ref)->basic_const_item(); }
 
 };
 
@@ -2948,6 +2952,8 @@ public:
   }
   virtual void store(Item *item);
   virtual void cache_value()= 0;
+  bool basic_const_item() const
+  { return test(example && example->basic_const_item());}
 };
 
 
@@ -3098,6 +3104,26 @@ public:
 };
 
 
+class Item_cache_datetime: public Item_cache
+{
+protected:
+  String str_value;
+  ulonglong int_value;
+public:
+  Item_cache_datetime(enum_field_types field_type_arg):
+    Item_cache(field_type_arg), int_value(0) {}
+
+  void store(Item *item, longlong val_arg);
+  double val_real();
+  longlong val_int();
+  String* val_str(String *str);
+  my_decimal *val_decimal(my_decimal *);
+  enum Item_result result_type() const { return STRING_RESULT; }
+  bool result_as_longlong() { return TRUE; }
+  void cache_value();
+};
+
+
 /*
   Item_type_holder used to store type. name, length of Item for UNIONS &
   derived tables.

=== modified file 'sql/item_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc	2009-11-06 19:42:24 +0000
+++ b/sql/item_cmpfunc.cc	2009-11-13 15:21:37 +0000
@@ -980,6 +980,7 @@ Item** Arg_comparator::cache_converted_c
       (*value)->const_item() && type != (*value)->result_type())
   {
     Item_cache *cache= Item_cache::get_cache(*value, type);
+    cache->setup(*value);
     cache->store(*value);
     *cache_item= cache;
     return cache_item;
@@ -4122,7 +4123,7 @@ Item *Item_cond::compile(Item_analyzer a
     uchar *arg_v= *arg_p;
     Item *new_item= item->compile(analyzer, &arg_v, transformer, arg_t);
     if (new_item && new_item != item)
-      li.replace(new_item);
+      current_thd->change_item_tree(li.ref(), new_item);
   }
   return Item_func::transform(transformer, arg_t);
 }

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2009-11-06 14:54:19 +0000
+++ b/sql/sql_select.cc	2009-11-13 15:21:37 +0000
@@ -1078,6 +1078,9 @@ JOIN::optimize()
   {
     conds=new Item_int((longlong) 0,1);	// Always false
   }
+  /* Cache constant expressions in select list, WHERE, HAVING, ON. */
+  cache_const_exprs();
+
   if (make_join_select(this, select, conds))
   {
     zero_result_cause=
@@ -16950,5 +16953,38 @@ bool JOIN::change_result(select_result *
 }
 
 /**
+  Cache constant expressions in WHERE, HAVING, ON conditions.
+*/
+
+void JOIN::cache_const_exprs()
+{
+  bool cache_flag= FALSE;
+  bool *anlz_arg= &cache_flag;
+
+  /* No need in cache if all tables are constant. */
+  if (const_tables == tables)
+    return;
+
+  if (conds)
+    conds->compile(&Item::cache_const_expr_analyzer, (uchar **)&anlz_arg,
+                  &Item::cache_const_expr_transformer, (uchar *)&cache_flag);
+  if (having)
+    having->compile(&Item::cache_const_expr_analyzer, (uchar **)&anlz_arg,
+                    &Item::cache_const_expr_transformer, (uchar *)&cache_flag);
+
+  for (JOIN_TAB *tab= join_tab + const_tables; tab < join_tab + tables ; tab++)
+  {
+    if (*tab->on_expr_ref)
+    {
+      (*tab->on_expr_ref)->compile(&Item::cache_const_expr_analyzer,
+                                 (uchar **)&anlz_arg,
+                                 &Item::cache_const_expr_transformer,
+                                 (uchar *)&cache_flag);
+    }
+  }
+}
+
+
+/**
   @} (end of group Query_Optimizer)
 */

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2009-11-03 17:45:52 +0000
+++ b/sql/sql_select.h	2009-11-13 15:21:37 +0000
@@ -548,6 +548,7 @@ public:
     return (unit == &thd->lex->unit && (unit->fake_select_lex == 0 ||
                                         select_lex == unit->fake_select_lex));
   }
+  void cache_const_exprs();
 private:
   /**
     TRUE if the query contains an aggregate function but has no GROUP


Attachment: [text/bzr-bundle] bzr/epotemkin@mysql.com-20091113152137-lqze2ohc1x3onqc6.bundle
Thread
bzr commit into mysql-5.1-bugteam branch (epotemkin:3202) Bug#33546Evgeny Potemkin13 Nov
  • Re: bzr commit into mysql-5.1-bugteam branch (epotemkin:3202)Bug#33546Sergei Golubchik20 Nov
    • Re: bzr commit into mysql-5.1-bugteam branch (epotemkin:3202) Bug#33546Evgeny Potemkin21 Nov
      • Re: bzr commit into mysql-5.1-bugteam branch (epotemkin:3202)Bug#33546Sergei Golubchik21 Nov
        • Re: bzr commit into mysql-5.1-bugteam branch (epotemkin:3202) Bug#33546Evgeny Potemkin23 Nov