bk commit into 5.0 tree (gkodinov:1.2479)

[kgeorge]

Below is the list of changes that have just been committed into a local
5.0 repository of kgeorge. When kgeorge does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-03-14 19:13:29+02:00, gkodinov@stripped +7 -0
  Merge gkodinov@stripped:/home/bk/mysql-5.0-opt
  into  magare.gmz:/home/kgeorge/mysql/autopush/B24484-5.0
  MERGE: 1.2377.47.1

  mysql-test/r/subselect3.result@stripped, 2007-03-14 19:13:25+02:00, gkodinov@stripped +0 -0
    Auto merged
    MERGE: 1.4.1.1

  sql/item.h@stripped, 2007-03-14 19:13:25+02:00, gkodinov@stripped +0 -0
    Auto merged
    MERGE: 1.213.7.1

  sql/item_sum.cc@stripped, 2007-03-14 19:13:26+02:00, gkodinov@stripped +0 -0
    Auto merged
    MERGE: 1.192.3.1

  sql/sql_base.cc@stripped, 2007-03-14 19:13:26+02:00, gkodinov@stripped +0 -0
    Auto merged
    MERGE: 1.365.3.1

  sql/sql_class.h@stripped, 2007-03-14 19:13:26+02:00, gkodinov@stripped +0 -0
    Auto merged
    MERGE: 1.317.3.1

  sql/sql_insert.cc@stripped, 2007-03-14 19:13:26+02:00, gkodinov@stripped +0 -0
    Auto merged
    MERGE: 1.211.7.1

  sql/sql_select.cc@stripped, 2007-03-14 19:13:27+02:00, gkodinov@stripped +0 -0
    Auto merged
    MERGE: 1.488.4.1

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	gkodinov
# Host:	magare.gmz
# Root:	/home/kgeorge/mysql/autopush/B24484-5.0/RESYNC

--- 1.222/sql/item.h	2007-03-12 10:39:55 +02:00
+++ 1.223/sql/item.h	2007-03-14 19:13:25 +02:00
@@ -1898,6 +1898,11 @@ public:
   {
     return depended_from ? OUTER_REF_TABLE_BIT : (*ref)->used_tables(); 
   }
+  void update_used_tables() 
+  { 
+    if (!depended_from) 
+      (*ref)->update_used_tables(); 
+  }
   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; }

--- 1.196/sql/item_sum.cc	2007-02-21 13:05:00 +02:00
+++ 1.197/sql/item_sum.cc	2007-03-14 19:13:26 +02:00
@@ -61,6 +61,7 @@ bool Item_sum::init_sum_func_check(THD *
   /* Save a pointer to object to be used in items for nested set functions */
   thd->lex->in_sum_func= this;
   nest_level= thd->lex->current_select->nest_level;
+  nest_level_tables_count= thd->lex->current_select->join->tables;
   ref_by= 0;
   aggr_level= -1;
   max_arg_level= -1;
@@ -176,6 +177,7 @@ bool Item_sum::check_sum_func(THD *thd, 
     */
     set_if_bigger(in_sum_func->max_sum_func_level, aggr_level);
   }
+  update_used_tables();
   thd->lex->in_sum_func= in_sum_func;
   return FALSE;
 }
@@ -271,8 +273,8 @@ bool Item_sum::register_sum_func(THD *th
 }
 
 
-Item_sum::Item_sum(List<Item> &list)
-  :arg_count(list.elements)
+Item_sum::Item_sum(List<Item> &list) :arg_count(list.elements), 
+  forced_const(FALSE)
 {
   if ((args=(Item**) sql_alloc(sizeof(Item*)*arg_count)))
   {
@@ -296,7 +298,10 @@ Item_sum::Item_sum(List<Item> &list)
 
 Item_sum::Item_sum(THD *thd, Item_sum *item):
   Item_result_field(thd, item), arg_count(item->arg_count),
-  quick_group(item->quick_group)
+  nest_level(item->nest_level), aggr_level(item->aggr_level),
+  quick_group(item->quick_group), used_tables_cache(item->used_tables_cache),
+  forced_const(item->forced_const), 
+  nest_level_tables_count(item->nest_level_tables_count)
 {
   if (arg_count <= 2)
     args=tmp_args;
@@ -426,6 +431,26 @@ case DECIMAL_RESULT:
 }
 
 
+void Item_sum::update_used_tables ()
+{
+  if (!forced_const)
+  {
+    used_tables_cache= 0;
+    for (uint i=0 ; i < arg_count ; i++)
+    {
+      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 */
+    if (aggr_level == nest_level)
+      used_tables_cache |=  (1 << nest_level_tables_count) - 1;
+  }
+}
+
+
 String *
 Item_sum_num::val_str(String *str)
 {
@@ -485,7 +510,7 @@ Item_sum_num::fix_fields(THD *thd, Item 
 Item_sum_hybrid::Item_sum_hybrid(THD *thd, Item_sum_hybrid *item)
   :Item_sum(thd, item), value(item->value), hybrid_type(item->hybrid_type),
   hybrid_field_type(item->hybrid_field_type), cmp_sign(item->cmp_sign),
-  used_table_cache(item->used_table_cache), was_values(item->was_values)
+  was_values(item->was_values)
 {
   /* copy results from old value */
   switch (hybrid_type) {
@@ -1073,7 +1098,6 @@ void Item_sum_count::cleanup()
   DBUG_ENTER("Item_sum_count::cleanup");
   count= 0;
   Item_sum_int::cleanup();
-  used_table_cache= ~(table_map) 0;
   DBUG_VOID_RETURN;
 }
 
@@ -1554,7 +1578,7 @@ void Item_sum_hybrid::cleanup()
 {
   DBUG_ENTER("Item_sum_hybrid::cleanup");
   Item_sum::cleanup();
-  used_table_cache= ~(table_map) 0;
+  forced_const= FALSE;
 
   /*
     by default it is TRUE to avoid TRUE reporting by

--- 1.370/sql/sql_base.cc	2007-03-08 19:29:58 +02:00
+++ 1.371/sql/sql_base.cc	2007-03-14 19:13:26 +02:00
@@ -4864,6 +4864,7 @@ bool setup_tables_and_check_access(THD *
   TABLE_LIST *leaves_tmp = NULL;
   bool first_table= true;
 
+  thd->leaf_count= 0;
   if (setup_tables (thd, context, from_clause, tables, conds, 
                     &leaves_tmp, select_insert))
     return TRUE;
@@ -4881,6 +4882,7 @@ bool setup_tables_and_check_access(THD *
       return TRUE;
     }
     first_table= false;
+    thd->leaf_count++;
   }
   return FALSE;
 }

--- 1.324/sql/sql_class.h	2007-03-08 00:55:18 +02:00
+++ 1.325/sql/sql_class.h	2007-03-14 19:13:26 +02:00
@@ -1480,6 +1480,9 @@ public:
     query_id_t first_query_id;
   } binlog_evt_union;
 
+  /* pass up the count of "leaf" tables in a JOIN out of setup_tables() */
+  byte leaf_count;
+
   THD();
   ~THD();
 

--- 1.221/sql/sql_insert.cc	2007-03-08 19:29:58 +02:00
+++ 1.222/sql/sql_insert.cc	2007-03-14 19:13:26 +02:00
@@ -2334,12 +2334,14 @@ bool mysql_insert_select_prepare(THD *th
   DBUG_ASSERT(select_lex->leaf_tables != 0);
   lex->leaf_tables_insert= select_lex->leaf_tables;
   /* skip all leaf tables belonged to view where we are insert */
-  for (first_select_leaf_table= select_lex->leaf_tables->next_leaf;
+  for (first_select_leaf_table= select_lex->leaf_tables->next_leaf,
+       thd->leaf_count --;
        first_select_leaf_table &&
        first_select_leaf_table->belong_to_view &&
        first_select_leaf_table->belong_to_view ==
        lex->leaf_tables_insert->belong_to_view;
-       first_select_leaf_table= first_select_leaf_table->next_leaf)
+       first_select_leaf_table= first_select_leaf_table->next_leaf,
+       thd->leaf_count --)
   {}
   select_lex->leaf_tables= first_select_leaf_table;
   DBUG_RETURN(FALSE);

--- 1.498/sql/sql_select.cc	2007-03-09 11:44:40 +02:00
+++ 1.499/sql/sql_select.cc	2007-03-14 19:13:27 +02:00
@@ -406,12 +406,15 @@ JOIN::prepare(Item ***rref_pointer_array
 
   /* Check that all tables, fields, conds and order are ok */
 
-  if ((!(select_options & OPTION_SETUP_TABLES_DONE) &&
-       setup_tables_and_check_access(thd, &select_lex->context, join_list,
-                                     tables_list, &conds, 
-                                     &select_lex->leaf_tables, FALSE, 
-                                     SELECT_ACL, SELECT_ACL)) ||
-      setup_wild(thd, tables_list, fields_list, &all_fields, wild_num) ||
+  if (!(select_options & OPTION_SETUP_TABLES_DONE) &&
+      setup_tables_and_check_access(thd, &select_lex->context, join_list,
+                                    tables_list, &conds, 
+                                    &select_lex->leaf_tables, FALSE, 
+                                    SELECT_ACL, SELECT_ACL))
+      DBUG_RETURN(-1);
+  tables= thd->leaf_count;
+
+  if (setup_wild(thd, tables_list, fields_list, &all_fields, wild_num) ||
       select_lex->setup_ref_array(thd, og_num) ||
       setup_fields(thd, (*rref_pointer_array), fields_list, 1,
 		   &all_fields, 1) ||
@@ -516,11 +519,6 @@ JOIN::prepare(Item ***rref_pointer_array
 	DBUG_RETURN(-1);
       }
     }
-    TABLE_LIST *table_ptr;
-    for (table_ptr= select_lex->leaf_tables;
-	 table_ptr;
-	 table_ptr= table_ptr->next_leaf)
-      tables++;
   }
   {
     /* Caclulate the number of groups */
@@ -6461,7 +6459,8 @@ static void update_depend_map(JOIN *join
     order->item[0]->update_used_tables();
     order->depend_map=depend_map=order->item[0]->used_tables();
     // Not item_sum(), RAND() and no reference to table outside of sub select
-    if (!(order->depend_map & (OUTER_REF_TABLE_BIT | RAND_TABLE_BIT)))
+    if (!(order->depend_map & (OUTER_REF_TABLE_BIT | RAND_TABLE_BIT))
+        && !order->item[0]->with_sum_func)
     {
       for (JOIN_TAB **tab=join->map2table;
 	   depend_map ;

--- 1.5/mysql-test/r/subselect3.result	2007-02-21 21:59:51 +02:00
+++ 1.6/mysql-test/r/subselect3.result	2007-03-14 19:13:25 +02:00
@@ -645,3 +645,56 @@ a	b	Z
 2	2	0
 3	3	1
 drop table t1,t2;
+CREATE TABLE t1 (a int, b INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));
+INSERT INTO t1 VALUES (1,1,'a'), (1,2,'b'), (1,3,'c'), (1,4,'d'), (1,5,'e'),
+(2,1,'f'), (2,2,'g'), (2,3,'h'), (3,4,'i'),(3,3,'j'), (3,2,'k'), (3,1,'l'),
+(1,9,'m');
+CREATE TABLE t2 (a int, b INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));
+INSERT INTO t2 SELECT * FROM t1;
+SELECT a, MAX(b), (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b))
+as test FROM t1 GROUP BY a;
+a	MAX(b)	test
+1	9	m
+2	3	h
+3	4	i
+SELECT * FROM t1 GROUP by t1.a
+HAVING (MAX(t1.b) > (SELECT MAX(t2.b) FROM t2 WHERE t2.c < t1.c
+HAVING MAX(t2.b+t1.a) < 10));
+a	b	c
+SELECT a, AVG(b), (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=AVG(t1.b))
+AS test FROM t1 GROUP BY a;
+a	AVG(b)	test
+1	4.0000	NULL
+2	2.0000	k
+3	2.5000	NULL
+SELECT a,b,c FROM t1 WHERE b in (9,3,4) ORDER BY b,c;
+a	b	c
+1	3	c
+2	3	h
+3	3	j
+1	4	d
+3	4	i
+1	9	m
+SELECT a, MAX(b),
+(SELECT COUNT(DISTINCT t.c) FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) 
+LIMIT 1) 
+as cnt, 
+(SELECT t.b FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) 
+as t_b,
+(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) 
+as t_b,
+(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) ORDER BY t.c LIMIT 1)
+as t_b
+FROM t1 GROUP BY a;
+a	MAX(b)	cnt	t_b	t_b	t_b
+1	9	1	9	m	m
+2	3	1	3	h	h
+3	4	1	4	i	i
+SELECT a, MAX(b),
+(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) as test 
+FROM t1 GROUP BY a;
+a	MAX(b)	test
+1	9	m
+2	3	h
+3	4	i
+DROP TABLE t1, t2;