Merge lp:~roccoblues/drizzle/port-constants-evalutation-fixes into lp:~drizzle-trunk/drizzle/development

[Dennis Schön]

[Dennis Schön (roccoblues)]

This ports the fixes for MySQL Bug#33546 to drizzle. Test-suite passes.

One small thing I've noticed is that in the test result file this query

EXPLAIN EXTENDED SELECT * FROM t2 LEFT JOIN t1 ON a = b + 1;

leads to different results in drizzle and mysql:

MySQL:
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 Using where
Warnings:
Note 1003 select '2' AS `b`,`test`.`t1`.`a` AS `a` from `test`.`t1` where 1

Drizzle:
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 ALL NULL NULL NULL NULL 1 100.00
1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00
Warnings:
Note 1003 select `test`.`t2`.`b` AS `b`,`test`.`t1`.`a` AS `a` from `test`.`t2` left join `test`.`t1` on((`test`.`t1`.`a` = (`test`.`t2`.`b` + 1))) where 1

I don't think that's because of my changes but please double check. Jay said on IRC: "might be some constant propogation in the optimizer that is not in Drizzle. I wouldn't worry about it too much. The case that is being optimized is pretty minimal."

[Jay Pipes (jaypipes)]

Looks good. I downloaded and all tests pass. Approved from me.

review: Approve

=== modified file 'drizzled/item.cc'
--- drizzled/item.cc	2009-12-05 23:29:59 +0000
+++ drizzled/item.cc	2009-12-16 14:56:20 +0000
@@ -1272,6 +1272,65 @@
   return error;
 }
 
+/**
+  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(unsigned char **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(unsigned char *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::send(plugin::Client *client, String *buffer)
 {
   bool result= false;
 
=== modified file 'drizzled/item.h'
--- drizzled/item.h	2009-12-05 20:06:30 +0000
+++ drizzled/item.h	2009-12-16 14:56:20 +0000
@@ -684,6 +684,9 @@
   virtual bool register_field_in_read_map(unsigned char *arg);
   virtual bool subst_argument_checker(unsigned char **arg);
 
+  virtual bool cache_const_expr_analyzer(unsigned char **arg);
+  virtual Item* cache_const_expr_transformer(unsigned char *arg);
+
   virtual Item *equal_fields_propagator(unsigned char * arg);
   virtual bool set_no_const_sub(unsigned char *arg);
   virtual Item *replace_equal_field(unsigned char * arg);
 
=== modified file 'drizzled/item/cache.h'
--- drizzled/item/cache.h	2009-12-04 23:47:14 +0000
+++ drizzled/item/cache.h	2009-12-16 14:56:20 +0000
@@ -78,6 +78,10 @@
   {
     return this == item;
   }
+  bool basic_const_item() const
+  {
+    return test(example && example->basic_const_item());
+  }
 };
 
 #endif /* DRIZZLED_ITEM_CACHE_H */
 
=== modified file 'drizzled/item/ref.h'
--- drizzled/item/ref.h	2009-12-05 23:13:49 +0000
+++ drizzled/item/ref.h	2009-12-16 14:56:20 +0000
@@ -146,7 +146,10 @@
     if (ref && result_type() == ROW_RESULT)
       (*ref)->bring_value();
   }
-
+  bool basic_const_item() const
+  {
+    return (*ref)->basic_const_item();
+  }
 };
 
 #endif /* DRIZZLED_ITEM_REF_H */
 
=== modified file 'drizzled/join.cc'
--- drizzled/join.cc	2009-12-07 19:18:46 +0000
+++ drizzled/join.cc	2009-12-16 14:56:20 +0000
@@ -621,6 +621,7 @@
   {
     conds=new Item_int((int64_t) 0,1);  // Always false
   }
+
   if (make_join_select(this, select, conds))
   {
     zero_result_cause=
@@ -828,6 +829,9 @@
   if (setup_subquery_materialization())
     return 1;
 
+  /* Cache constant expressions in WHERE, HAVING, ON clauses. */
+  cache_const_exprs();
+
   /*
     is this simple IN subquery?
   */
@@ -2379,6 +2383,40 @@
 }
 
 /**
+  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, (unsigned char **)&analyzer_arg,
+                  &Item::cache_const_expr_transformer, (unsigned char *)&cache_flag);
+  cache_flag= false;
+  if (having)
+    having->compile(&Item::cache_const_expr_analyzer, (unsigned char **)&analyzer_arg,
+                    &Item::cache_const_expr_transformer, (unsigned char *)&cache_flag);
+
+  for (JoinTable *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,
+                                 (unsigned char **)&analyzer_arg,
+                                 &Item::cache_const_expr_transformer,
+                                 (unsigned char *)&cache_flag);
+    }
+  }
+}
+
+/**
   @brief
   
   Process one record of the nested loop join.
 
=== modified file 'drizzled/join.h'
--- drizzled/join.h	2009-12-07 19:18:46 +0000
+++ drizzled/join.h	2009-12-16 14:56:20 +0000
@@ -472,6 +472,8 @@
            sizeof(drizzled::optimizer::Position) * size);
   }
 
+  void cache_const_exprs();
+
   /**
    * @param[in] index the index of the position to retrieve
    * @return a reference to the specified position in the optimal
 
=== modified file 'tests/r/errors.result'
--- tests/r/errors.result	2008-12-16 07:07:50 +0000
+++ tests/r/errors.result	2009-12-16 14:56:20 +0000
@@ -30,17 +30,20 @@
 CREATE TABLE t1 (a INT);
 SELECT a FROM t1 WHERE a IN(1, (SELECT IF(1=0,1,2/0)));
 a
+Warnings:
+Error	1365	Division by 0
 INSERT INTO t1 VALUES(1);
 SELECT a FROM t1 WHERE a IN(1, (SELECT IF(1=0,1,2/0)));
 a
 1
+Warnings:
+Error	1365	Division by 0
 INSERT INTO t1 VALUES(2),(3);
 SELECT a FROM t1 WHERE a IN(1, (SELECT IF(1=0,1,2/0)));
 a
 1
 Warnings:
 Error	1365	Division by 0
-Error	1365	Division by 0
 DROP TABLE t1;
 CREATE TABLE t1( a INT );
 SELECT b FROM t1;
 
=== modified file 'tests/r/func_in.result'
--- tests/r/func_in.result	2009-07-27 23:00:57 +0000
+++ tests/r/func_in.result	2009-12-16 14:56:20 +0000
@@ -460,7 +460,6 @@
 id
 Warnings:
 Error	1365	Division by 0
-Error	1365	Division by 0
 DROP TABLE t1;
 End of 5.0 tests
 create TEMPORARY table t1(f1 char(1)) ENGINE=MYISAM;
 
=== modified file 'tests/r/func_like.result'
--- tests/r/func_like.result	2008-12-06 22:41:03 +0000
+++ tests/r/func_like.result	2009-12-16 14:56:20 +0000
@@ -10,7 +10,7 @@
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	index	a	a	43	NULL	5	40.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 'tests/r/func_str.result'
--- tests/r/func_str.result	2009-10-31 18:12:20 +0000
+++ tests/r/func_str.result	2009-12-16 14:56:20 +0000
@@ -847,7 +847,7 @@
 1	SIMPLE	t2	const	PRIMARY	PRIMARY	42	const	1	100.00	Using index
 1	SIMPLE	t1	ref	code	code	43	const	2	100.00	Using where; Using index
 Warnings:
-Note	1003	select `test`.`t1`.`code` AS `code`,'a12' AS `id` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`code` = 'a12') and ('a12' = 'a12') and ((length(`test`.`t1`.`code`) = 5) or ('a12' < 'a00')))
+Note	1003	select `test`.`t1`.`code` AS `code`,'a12' AS `id` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`code` = 'a12') and <cache>(('a12' = 'a12')) and ((length(`test`.`t1`.`code`) = 5) or <cache>(('a12' < 'a00'))))
 DROP TABLE t1,t2;
 select benchmark(-1, 1);
 benchmark(-1, 1)
 
=== modified file 'tests/r/func_time.result'
--- tests/r/func_time.result	2009-06-16 00:53:22 +0000
+++ tests/r/func_time.result	2009-12-16 14:56:20 +0000
@@ -714,7 +714,6 @@
 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
 
=== modified file 'tests/r/select.result'
--- tests/r/select.result	2009-11-13 00:56:59 +0000
+++ tests/r/select.result	2009-12-16 14:56:20 +0000
@@ -3661,3 +3661,46 @@
 4
 5
 DROP TABLE t1;
+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	ALL	NULL	NULL	NULL	NULL	1	100.00	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where; Using join buffer
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where (`test`.`t1`.`a` = (`test`.`t2`.`b` + <cache>((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	ALL	NULL	NULL	NULL	NULL	1	100.00	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	100.00	
+Warnings:
+Note	1003	select `test`.`t2`.`b` AS `b`,`test`.`t1`.`a` AS `a` from `test`.`t2` left join `test`.`t1` on((`test`.`t1`.`a` = (`test`.`t2`.`b` + 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')))
 
=== modified file 'tests/r/subselect.result'
--- tests/r/subselect.result	2009-11-19 18:26:26 +0000
+++ tests/r/subselect.result	2009-12-16 14:56:20 +0000
@@ -720,7 +720,7 @@
 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 'tests/r/subselect_no_mat.result'
--- tests/r/subselect_no_mat.result	2009-11-19 18:26:26 +0000
+++ tests/r/subselect_no_mat.result	2009-12-16 14:56:20 +0000
@@ -724,7 +724,7 @@
 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 'tests/r/subselect_no_mat_and_semi_join.result'
--- tests/r/subselect_no_mat_and_semi_join.result	2009-11-19 18:26:26 +0000
+++ tests/r/subselect_no_mat_and_semi_join.result	2009-12-16 14:56:20 +0000
@@ -724,7 +724,7 @@
 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 'tests/r/subselect_no_opts.result'
--- tests/r/subselect_no_opts.result	2009-11-19 18:26:26 +0000
+++ tests/r/subselect_no_opts.result	2009-12-16 14:56:20 +0000
@@ -724,7 +724,7 @@
 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 'tests/r/subselect_no_semijoin.result'
--- tests/r/subselect_no_semijoin.result	2009-11-19 18:26:26 +0000
+++ tests/r/subselect_no_semijoin.result	2009-12-16 14:56:20 +0000
@@ -724,7 +724,7 @@
 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 'tests/t/select.test'
--- tests/t/select.test	2009-11-13 00:56:59 +0000
+++ tests/t/select.test	2009-12-16 14:56:20 +0000
@@ -3454,3 +3454,20 @@
 SELECT a FROM t1 ORDER BY a LIMIT 2,4294967296;
 SELECT a FROM t1 ORDER BY a LIMIT 2,4294967297;
 DROP TABLE t1;
+
+#
+# MySQL 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;
+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');
 \ No newline at end of file