#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
