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/func_time.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/r/udf.result:
  Added a test case for the bug#33546.
mysql-test/t/select.test:
  Added a test case for the bug#33546.
mysql-test/t/udf.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/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.

1 files changed, 15 insertions, 0 deletions

diff --git a/mysql-test/r/udf.result b/mysql-test/r/udf.result
index 601b364fbbe..fbf87b2e4cc 100644
--- a/mysql-test/r/udf.result
+++ b/mysql-test/r/udf.result
@@ -409,3 +409,18 @@ b
 1
 DROP TABLE t1;
 End of 5.0 tests.
+#
+# Bug#33546: Slowdown on re-evaluation of constant expressions.
+#
+CREATE TABLE t1 (f1 INT);
+INSERT INTO t1 VALUES(1),(50);
+CREATE FUNCTION myfunc_double RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB";
+EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE f1=1 + myfunc_double(1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+Warnings:
+Note	1003	select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`f1` = <cache>((1 + myfunc_double(1 AS `1`))))
+DROP FUNCTION myfunc_double;
+DROP TABLE t1;
+#
+End of 5.1 tests.