maria-developers team mailing list archive

[Sergey Petrunia <psergey@xxxxxxxxxxxx>]

#At lp:maria based on revid:psergey@xxxxxxxxxxxx-20090608135546-ut1yrzbah4gdw6e6

 2711 Sergey Petrunia	2009-06-10
      MWL#17: Table elimination
      - Make elimination work with aggregate functions. The problem was that aggregate functions 
        reported all table bits in used_tables(), and that prevented table elimination. Fixed by 
        making aggregate functions return more correct value from used_tables(). 
      modified:
        mysql-test/r/ps_11bugs.result
        mysql-test/r/subselect.result
        mysql-test/r/table_elim.result
        mysql-test/t/table_elim.test
        sql/item.h
        sql/item_sum.cc
        sql/item_sum.h

per-file messages:
  mysql-test/r/ps_11bugs.result
    MWL#17: Table elimination
    - Update test results. The difference is because of Item_ref change: outer references to constants
      are now recognized as constants, too.
  mysql-test/r/subselect.result
    - Update test results. The difference is because of Item_ref change: outer references to constants
      are now recognized as constants, too.
  mysql-test/r/table_elim.result
    MWL#17: Table elimination
    - Check that elimination works in presense of aggreagate functions
  mysql-test/t/table_elim.test
    MWL#17: Table elimination
    - Check that elimination works in presense of aggreagate functions
  sql/item.h
    MWL#17: Table elimination
    - Add Item_ref::const_item() which calls (*ref)->const_item(). Before this diff Item_ref used the 
      default implementation of const_item(){ return used_tables()==0; }. This is no longer true, as 
      COUNT(*) now has used_tables()==0 but const_item()==FALSE.
  sql/item_sum.cc
    MWL#17: Table elimination
    - Make Item_sum() and it descendants not to return all bits in used_tables(). This is needed 
      because otherwise table elimination can't work in presense of aggregate functions
    - COUNT(*) now has used_tables()==0 and const_item()==FALSE. Had to change 
      Item_ref::const_item() to account for this.
  sql/item_sum.h
    MWL#17: Table elimination
    - Add comments
=== modified file 'mysql-test/r/ps_11bugs.result'
--- a/mysql-test/r/ps_11bugs.result	2008-10-08 11:23:53 +0000
+++ b/mysql-test/r/ps_11bugs.result	2009-06-09 21:11:33 +0000
@@ -121,8 +121,8 @@ insert into t1 values (1);
 explain select * from t1 where 3 in (select (1+1) union select 1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
-2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-3	DEPENDENT UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible HAVING
+3	DEPENDENT UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible HAVING
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
 select * from t1 where 3 in (select (1+1) union select 1);
 a

=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result	2009-04-25 09:04:38 +0000
+++ b/mysql-test/r/subselect.result	2009-06-09 21:11:33 +0000
@@ -4353,13 +4353,13 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
 2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using temporary; Using filesort
 Warnings:
-Note	1003	select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a` having (<cache>(1) = <ref_null_helper>(1))))
+Note	1003	select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a` having 1))
 EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using temporary; Using filesort
 Warnings:
-Note	1003	select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` having (<cache>(1) = <ref_null_helper>(1))))
+Note	1003	select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` having 1))
 DROP TABLE t1;
 End of 5.0 tests.
 CREATE TABLE t1 (a INT, b INT);

=== modified file 'mysql-test/r/table_elim.result'
--- a/mysql-test/r/table_elim.result	2009-06-03 13:10:45 +0000
+++ b/mysql-test/r/table_elim.result	2009-06-09 21:11:33 +0000
@@ -53,4 +53,19 @@ t3.a=t1.a) on t0.a=t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	4	
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
+# Elimination with aggregate functions
+explain select count(*) from t1 left join t2 on t2.a=t1.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
+explain select count(1) from t1 left join t2 on t2.a=t1.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
+explain select count(1) from t1 left join t2 on t2.a=t1.a group by t1.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using temporary; Using filesort
+This must not use elimination:
+explain select count(1) from t1 left join t2 on t2.a=t1.a group by t2.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using temporary; Using filesort
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using index
 drop table t0, t1, t2, t3;

=== modified file 'mysql-test/t/table_elim.test'
--- a/mysql-test/t/table_elim.test	2009-06-03 13:10:45 +0000
+++ b/mysql-test/t/table_elim.test	2009-06-09 21:11:33 +0000
@@ -29,8 +29,6 @@ explain select t1.a from t1 left join t2
 --echo # This will not be eliminated as t2.b is in group list:
 explain select t1.a from t1 left join t2 on t2.a=t1.a group by t2.b;
 
-## TODO: Aggregate functions prevent table elimination ATM.
-
 --echo # This will not be eliminated as t2.b is in the WHERE
 explain select t1.a from t1 left join t2 on t2.a=t1.a where t2.b < 3 or t2.b is null;
 
@@ -47,6 +45,13 @@ from
   t0 left join (t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and
   t3.a=t1.a) on t0.a=t1.a;
 
+--echo # Elimination with aggregate functions
+explain select count(*) from t1 left join t2 on t2.a=t1.a;
+explain select count(1) from t1 left join t2 on t2.a=t1.a;
+explain select count(1) from t1 left join t2 on t2.a=t1.a group by t1.a;
+
+--echo This must not use elimination:
+explain select count(1) from t1 left join t2 on t2.a=t1.a group by t2.a;
 
 drop table t0, t1, t2, t3;
 

=== modified file 'sql/item.h'
--- a/sql/item.h	2009-04-25 10:05:32 +0000
+++ b/sql/item.h	2009-06-09 21:11:33 +0000
@@ -2203,6 +2203,10 @@ public:
     if (!depended_from) 
       (*ref)->update_used_tables(); 
   }
+  bool const_item() const 
+  {
+    return (*ref)->const_item();
+  }
   table_map not_null_tables() const { return (*ref)->not_null_tables(); }
   void set_result_field(Field *field)	{ result_field= field; }
   bool is_result_field() { return 1; }

=== modified file 'sql/item_sum.cc'
--- a/sql/item_sum.cc	2009-04-25 09:04:38 +0000
+++ b/sql/item_sum.cc	2009-06-09 21:11:33 +0000
@@ -542,11 +542,6 @@ void Item_sum::update_used_tables ()
       args[i]->update_used_tables();
       used_tables_cache|= args[i]->used_tables();
     }
-
-    used_tables_cache&= PSEUDO_TABLE_BITS;
-
-    /* the aggregate function is aggregated into its local context */
-    used_tables_cache |=  (1 << aggr_sel->join->tables) - 1;
   }
 }
 

=== modified file 'sql/item_sum.h'
--- a/sql/item_sum.h	2008-12-09 19:43:10 +0000
+++ b/sql/item_sum.h	2009-06-09 21:11:33 +0000
@@ -255,6 +255,12 @@ protected:  
   */
   Item **orig_args, *tmp_orig_args[2];
   table_map used_tables_cache;
+  
+  /*
+    TRUE <=> We've managed to calculate the value of this Item in
+    opt_sum_query(), hence it can be considered constant at all subsequent
+    steps.
+  */
   bool forced_const;
 
 public:  
@@ -341,6 +347,14 @@ public:  
   virtual const char *func_name() const= 0;
   virtual Item *result_item(Field *field)
     { return new Item_field(field); }
+  /*
+    Return bitmap of tables that are needed to evaluate the item.
+
+    The implementation takes into account the used strategy: items resolved
+    at optimization phase report 0.
+    Items that depend on the number of rows only, e.g. COUNT(*) will report
+    zero, but will still false from const_item().
+  */
   table_map used_tables() const { return used_tables_cache; }
   void update_used_tables ();
   void cleanup()