From 987e146604d741f13fa03b7cb5e4af1cf3e30324 Mon Sep 17 00:00:00 2001 From: Evgeny Potemkin Date: Wed, 2 Dec 2009 00:25:51 +0300 Subject: 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. --- mysql-test/t/select.test | 41 ++++++++++++++++++++++++++++++++++++++++- mysql-test/t/udf.test | 14 ++++++++++++++ 2 files changed, 54 insertions(+), 1 deletion(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index 51f0cd73374..95c16849633 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -3767,9 +3767,14 @@ INSERT INTO t1 VALUES (2),(3); --echo # Should not crash --error ER_SUBQUERY_NO_1_ROW SELECT 1 FROM t1 WHERE a <> 1 AND NOT -ROW(a,a) <=> ROW((SELECT 1 FROM t1 WHERE 1=2),(SELECT 1 FROM t1)) +ROW(1,a) <=> ROW(1,(SELECT 1 FROM t1)) INTO @var0; +# Query correctly return 2 rows since comparison a <=> fisrt_subquery is +# always false, thus the second query is never executed. +SELECT 1 FROM t1 WHERE a <> 1 AND NOT +ROW(a,a) <=> ROW((SELECT 1 FROM t1 WHERE 1=2),(SELECT 1 FROM t1)); + DROP TABLE t1; @@ -3918,4 +3923,38 @@ SELECT table1 .`time_key` field2 FROM B table1 LEFT JOIN BB JOIN A table5 ON 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 diff --git a/mysql-test/t/udf.test b/mysql-test/t/udf.test index 7bf252040e5..7e383ce69fa 100644 --- a/mysql-test/t/udf.test +++ b/mysql-test/t/udf.test @@ -449,3 +449,17 @@ SELECT IF( a = 1, a, a ) AS `b` FROM t1 ORDER BY field( `b`, 1 ); DROP TABLE t1; --echo End of 5.0 tests. + +--echo # +--echo # Bug#33546: Slowdown on re-evaluation of constant expressions. +--echo # +CREATE TABLE t1 (f1 INT); +INSERT INTO t1 VALUES(1),(50); +--replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB +eval CREATE FUNCTION myfunc_double RETURNS INTEGER SONAME "$UDF_EXAMPLE_LIB"; +EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE f1=1 + myfunc_double(1); +DROP FUNCTION myfunc_double; +DROP TABLE t1; +--echo # +--echo End of 5.1 tests. + -- cgit v1.2.1