summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/func_like.result2
-rw-r--r--mysql-test/r/func_time.result1
-rw-r--r--mysql-test/r/select.result73
-rw-r--r--mysql-test/r/subselect.result2
-rw-r--r--mysql-test/r/udf.result15
-rw-r--r--mysql-test/t/select.test41
-rw-r--r--mysql-test/t/udf.test14
-rw-r--r--sql/item.cc131
-rw-r--r--sql/item.h38
-rw-r--r--sql/sql_select.cc39
-rw-r--r--sql/sql_select.h1
11 files changed, 352 insertions, 5 deletions
diff --git a/mysql-test/r/func_like.result b/mysql-test/r/func_like.result
index 9338a76e320..8d0f34f3bda 100644
--- a/mysql-test/r/func_like.result
+++ b/mysql-test/r/func_like.result
@@ -10,7 +10,7 @@ explain extended select * from t1 where a like concat('abc','%');
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
diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result
index 59527e0b418..7bcaf5567cf 100644
--- a/mysql-test/r/func_time.result
+++ b/mysql-test/r/func_time.result
@@ -928,7 +928,6 @@ select f1 from t1 where cast("2006-1-1" as date) between f1 and cast('zzz' as da
f1
Warnings:
Warning 1292 Incorrect datetime value: 'zzz'
-Warning 1292 Incorrect datetime value: 'zzz'
select f1 from t1 where makedate(2006,1) between date(f1) and date(f3);
f1
2006-01-01
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index 1944edf6ade..604ff2be532 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -4423,9 +4423,14 @@ CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES (2),(3);
# Should not crash
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;
ERROR 21000: Subquery returns more than 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));
+1
+1
+1
DROP TABLE t1;
End of 5.0 tests
create table t1(a INT, KEY (a));
@@ -4577,4 +4582,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` 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`.`t1` 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
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 6325dc4f2fc..c77912e69cb 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -735,7 +735,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
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
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.
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.
+
diff --git a/sql/item.cc b/sql/item.cc
index 3bd4744764e..7de32423927 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -5766,6 +5766,67 @@ bool Item::send(Protocol *protocol, String *buffer)
}
+/**
+ 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);
@@ -7127,6 +7188,10 @@ Item_cache* Item_cache::get_cache(const Item *item, const Item_result type)
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();
@@ -7208,6 +7273,72 @@ longlong Item_cache_int::val_int()
return value;
}
+void Item_cache_datetime::cache_value_int()
+{
+ value_cached= TRUE;
+ /* 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::cache_value()
+{
+ str_value_cached= TRUE;
+ /* Assume here that the underlying item will do correct conversion.*/
+ String *res= example->str_result(&str_value);
+ if (res && res != &str_value)
+ str_value.copy(*res);
+ 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 (!str_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_int();
+ 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_int();
+ return (double) int_value;
+}
+
+longlong Item_cache_datetime::val_int()
+{
+ DBUG_ASSERT(fixed == 1);
+ if (!value_cached)
+ cache_value_int();
+ return int_value;
+}
+
void Item_cache_real::cache_value()
{
value_cached= TRUE;
diff --git a/sql/item.h b/sql/item.h
index f69fa742744..0556c330088 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -903,6 +903,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
@@ -2292,6 +2295,7 @@ public:
if (ref && result_type() == ROW_RESULT)
(*ref)->bring_value();
}
+ bool basic_const_item() { return (*ref)->basic_const_item(); }
};
@@ -2977,6 +2981,8 @@ public:
}
virtual void store(Item *item);
virtual void cache_value()= 0;
+ bool basic_const_item() const
+ { return test(example && example->basic_const_item());}
};
@@ -3127,6 +3133,38 @@ public:
};
+class Item_cache_datetime: public Item_cache
+{
+protected:
+ String str_value;
+ ulonglong int_value;
+ bool str_value_cached;
+public:
+ Item_cache_datetime(enum_field_types field_type_arg):
+ Item_cache(field_type_arg), int_value(0), str_value_cached(0)
+ {
+ cmp_context= STRING_RESULT;
+ }
+
+ 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; }
+ /*
+ In order to avoid INT <-> STRING conversion of a DATETIME value
+ two cache_value functions are introduced. One (cache_value) caches STRING
+ value, another (cache_value_int) - INT value. Thus this cache item
+ completely relies on the ability of the underlying item to do the
+ correct conversion.
+ */
+ void cache_value_int();
+ void cache_value();
+};
+
+
/*
Item_type_holder used to store type. name, length of Item for UNIONS &
derived tables.
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index f035e28da5c..65ff44622a8 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1076,6 +1076,10 @@ JOIN::optimize()
{
conds=new Item_int((longlong) 0,1); // Always false
}
+
+ /* Cache constant expressions in WHERE, HAVING, ON clauses. */
+ cache_const_exprs();
+
if (make_join_select(this, select, conds))
{
zero_result_cause=
@@ -17142,5 +17146,40 @@ bool JOIN::change_result(select_result *res)
}
/**
+ Cache constant expressions in WHERE, HAVING, ON conditions.
+*/
+
+void JOIN::cache_const_exprs()
+{
+ bool cache_flag= FALSE;
+ bool *analyzer_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 **)&analyzer_arg,
+ &Item::cache_const_expr_transformer, (uchar *)&cache_flag);
+ cache_flag= FALSE;
+ if (having)
+ having->compile(&Item::cache_const_expr_analyzer, (uchar **)&analyzer_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)
+ {
+ cache_flag= FALSE;
+ (*tab->on_expr_ref)->compile(&Item::cache_const_expr_analyzer,
+ (uchar **)&analyzer_arg,
+ &Item::cache_const_expr_transformer,
+ (uchar *)&cache_flag);
+ }
+ }
+}
+
+
+/**
@} (end of group Query_Optimizer)
*/
diff --git a/sql/sql_select.h b/sql/sql_select.h
index e049e4ed765..bdca4b196bc 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -555,6 +555,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
View Lorry Controller Status