maria-developers team mailing list archive

[Igor Babaev <igor@xxxxxxxxxxxx>]

#At lp:maria/5.2 based on revid:igor@xxxxxxxxxxxx-20100513065914-yq9y2pbd9zn2fm7w

 2791 Igor Babaev	2010-05-18
      Fixed bugs in the backport of derived tables (mwl106). 
      modified:
        mysql-test/r/derived_view.result
        mysql-test/r/table_elim.result
        sql/item_cmpfunc.cc
        sql/item_cmpfunc.h
        sql/sql_class.h
        sql/sql_select.cc
        sql/sql_select.h
        sql/sql_union.cc

=== modified file 'mysql-test/r/derived_view.result'
--- a/mysql-test/r/derived_view.result	2010-04-29 21:10:39 +0000
+++ b/mysql-test/r/derived_view.result	2010-05-18 17:46:32 +0000
@@ -442,7 +442,7 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	11	100.00	Using where
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	11	100.00	Using where; Using join buffer
 Warnings:
-Note	1003	select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11`,`test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11` from `test`.`t1` join `test`.`t1` where ((`test`.`t1`.`f1` = `test`.`t1`.`f1`) and (`test`.`t1`.`f1` > 2) and (`test`.`t1`.`f1` < 7) and ((`test`.`t1`.`f1` > 2) and (`test`.`t1`.`f1` < 7)))
+Note	1003	select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11`,`test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11` from `test`.`t1` join `test`.`t1` where ((`test`.`t1`.`f1` = `test`.`t1`.`f1`) and (`test`.`t1`.`f1` > 2) and (`test`.`t1`.`f1` < 7) and (`test`.`t1`.`f1` > 2) and (`test`.`t1`.`f1` < 7))
 select * from 
 (select * from 
 (select * from t1 where f1 < 7 ) tt where f1 > 2 ) x

=== modified file 'mysql-test/r/table_elim.result'
--- a/mysql-test/r/table_elim.result	2010-03-20 12:01:47 +0000
+++ b/mysql-test/r/table_elim.result	2010-05-18 17:46:32 +0000
@@ -117,58 +117,58 @@ t2 where id=f.id);
 This should use one table:
 explain select id from v1 where id=2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	f	const	PRIMARY	PRIMARY	4	const	1	Using index
+1	SIMPLE	f	const	PRIMARY	PRIMARY	4	const	1	Using index
 This should use one table:
 explain extended select id from v1 where id in (1,2,3,4);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	f	range	PRIMARY	PRIMARY	4	NULL	4	100.00	Using where; Using index
+1	SIMPLE	f	range	PRIMARY	PRIMARY	4	NULL	4	100.00	Using where; Using index
 Warnings:
-Note	1276	Field or reference 'test.a2.id' of SELECT #3 was resolved in SELECT #1
+Note	1276	Field or reference 'test.a2.id' of SELECT #3 was resolved in SELECT #2
 Note	1003	select `f`.`id` AS `id` from `test`.`t0` `f` where (`f`.`id` in (1,2,3,4))
 This should use facts and a1 tables:
 explain extended select id from v1 where attr1 between 12 and 14;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	a1	range	PRIMARY,attr1	attr1	5	NULL	2	100.00	Using index condition; Using MRR
-1	PRIMARY	f	eq_ref	PRIMARY	PRIMARY	4	test.a1.id	1	100.00	Using index
+1	SIMPLE	a1	range	PRIMARY,attr1	attr1	5	NULL	2	100.00	Using index condition; Using MRR
+1	SIMPLE	f	eq_ref	PRIMARY	PRIMARY	4	test.a1.id	1	100.00	Using index
 Warnings:
-Note	1276	Field or reference 'test.a2.id' of SELECT #3 was resolved in SELECT #1
+Note	1276	Field or reference 'test.a2.id' of SELECT #3 was resolved in SELECT #2
 Note	1003	select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t1` `a1` where ((`f`.`id` = `a1`.`id`) and (`a1`.`attr1` between 12 and 14))
 This should use facts, a2 and its subquery:
 explain extended select id from v1 where attr2 between 12 and 14;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	a2	range	PRIMARY,attr2	attr2	5	NULL	5	100.00	Using index condition; Using where; Using MRR
-1	PRIMARY	f	eq_ref	PRIMARY	PRIMARY	4	test.a2.id	1	100.00	Using index
+1	SIMPLE	a2	range	PRIMARY,attr2	attr2	5	NULL	5	100.00	Using index condition; Using where; Using MRR
+1	SIMPLE	f	eq_ref	PRIMARY	PRIMARY	4	test.a2.id	1	100.00	Using index
 3	DEPENDENT SUBQUERY	t2	ref	PRIMARY	PRIMARY	4	test.a2.id	2	100.00	Using index
 Warnings:
-Note	1276	Field or reference 'test.a2.id' of SELECT #3 was resolved in SELECT #1
+Note	1276	Field or reference 'test.a2.id' of SELECT #3 was resolved in SELECT #2
 Note	1003	select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t2` `a2` where ((`f`.`id` = `a2`.`id`) and (`a2`.`attr2` between 12 and 14) and (`a2`.`fromdate` = (select max(`test`.`t2`.`fromdate`) AS `MAX(fromdate)` from `test`.`t2` where (`test`.`t2`.`id` = `a2`.`id`))))
 This should use one table:
 explain select id from v2 where id=2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	f	const	PRIMARY	PRIMARY	4	const	1	Using index
+1	SIMPLE	f	const	PRIMARY	PRIMARY	4	const	1	Using index
 This should use one table:
 explain extended select id from v2 where id in (1,2,3,4);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	f	range	PRIMARY	PRIMARY	4	NULL	4	100.00	Using where; Using index
+1	SIMPLE	f	range	PRIMARY	PRIMARY	4	NULL	4	100.00	Using where; Using index
 Warnings:
-Note	1276	Field or reference 'test.f.id' of SELECT #3 was resolved in SELECT #1
+Note	1276	Field or reference 'test.f.id' of SELECT #3 was resolved in SELECT #2
 Note	1003	select `f`.`id` AS `id` from `test`.`t0` `f` where (`f`.`id` in (1,2,3,4))
 This should use facts and a1 tables:
 explain extended select id from v2 where attr1 between 12 and 14;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	a1	range	PRIMARY,attr1	attr1	5	NULL	2	100.00	Using index condition; Using MRR
-1	PRIMARY	f	eq_ref	PRIMARY	PRIMARY	4	test.a1.id	1	100.00	Using index
+1	SIMPLE	a1	range	PRIMARY,attr1	attr1	5	NULL	2	100.00	Using index condition; Using MRR
+1	SIMPLE	f	eq_ref	PRIMARY	PRIMARY	4	test.a1.id	1	100.00	Using index
 Warnings:
-Note	1276	Field or reference 'test.f.id' of SELECT #3 was resolved in SELECT #1
+Note	1276	Field or reference 'test.f.id' of SELECT #3 was resolved in SELECT #2
 Note	1003	select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t1` `a1` where ((`f`.`id` = `a1`.`id`) and (`a1`.`attr1` between 12 and 14))
 This should use facts, a2 and its subquery:
 explain extended select id from v2 where attr2 between 12 and 14;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	a2	range	PRIMARY,attr2	attr2	5	NULL	5	100.00	Using index condition; Using MRR
-1	PRIMARY	f	eq_ref	PRIMARY	PRIMARY	4	test.a2.id	1	100.00	Using where; Using index
+1	SIMPLE	a2	range	PRIMARY,attr2	attr2	5	NULL	5	100.00	Using index condition; Using MRR
+1	SIMPLE	f	eq_ref	PRIMARY	PRIMARY	4	test.a2.id	1	100.00	Using where; Using index
 3	DEPENDENT SUBQUERY	t2	ref	PRIMARY	PRIMARY	4	test.f.id	2	100.00	Using index
 Warnings:
-Note	1276	Field or reference 'test.f.id' of SELECT #3 was resolved in SELECT #1
+Note	1276	Field or reference 'test.f.id' of SELECT #3 was resolved in SELECT #2
 Note	1003	select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t2` `a2` where ((`f`.`id` = `a2`.`id`) and (`a2`.`attr2` between 12 and 14) and (`a2`.`fromdate` = (select max(`test`.`t2`.`fromdate`) AS `MAX(fromdate)` from `test`.`t2` where (`test`.`t2`.`id` = `f`.`id`))))
 drop view v1, v2;
 drop table t0, t1, t2;

=== modified file 'sql/item_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc	2010-04-29 21:10:39 +0000
+++ b/sql/item_cmpfunc.cc	2010-05-18 17:46:32 +0000
@@ -4232,8 +4232,20 @@ Item_cond::fix_fields(THD *thd, Item **r
 	(item= *li.ref())->check_cols(1))
       return TRUE; /* purecov: inspected */
     used_tables_cache|=     item->used_tables();
+#if 0
     if (!item->const_item())
       const_item_cache= FALSE;
+#else
+    if (item->const_item())
+      and_tables_cache= (table_map) 0;
+    else
+    {
+      table_map tmp_table_map= item->not_null_tables();
+      not_null_tables_cache|= tmp_table_map;
+      and_tables_cache&= tmp_table_map;
+      const_item_cache= FALSE;
+    } 
+#endif 
   
     with_sum_func=	    with_sum_func || item->with_sum_func;
     with_subselect|=        item->with_subselect;
@@ -4253,6 +4265,7 @@ Item_cond::eval_not_null_tables(uchar *o
 {
   Item *item;
   List_iterator<Item> li(list);
+  and_tables_cache= ~(table_map) 0;
   while ((item=li++))
   {
     table_map tmp_table_map;

=== modified file 'sql/item_cmpfunc.h'
--- a/sql/item_cmpfunc.h	2010-04-29 21:10:39 +0000
+++ b/sql/item_cmpfunc.h	2010-05-18 17:46:32 +0000
@@ -1778,19 +1778,6 @@ inline Item *and_conds(Item *a, Item *b)
 {
   if (!b) return a;
   if (!a) return b;
-  /* Try to minimize item tree by adding to already present AND functions. */
-  if (a->type() == Item::COND_ITEM &&
-      ((Item_cond*) a)->functype() == Item_func::COND_AND_FUNC)
-  {
-    ((Item_cond*)a)->add(b);
-    return a;
-  }
-  else if (b->type() == Item::COND_ITEM &&
-           ((Item_cond*) b)->functype() == Item_func::COND_AND_FUNC)
-  {
-    ((Item_cond*)b)->add(a);
-    return b;
-  }
   return new Item_cond_and(a, b);
 }
 

=== modified file 'sql/sql_class.h'
--- a/sql/sql_class.h	2010-04-29 21:10:39 +0000
+++ b/sql/sql_class.h	2010-05-18 17:46:32 +0000
@@ -2790,11 +2790,6 @@ public:
   */
   bool bit_fields_as_long;
 
-  /*
-    Whether to create or postpone actual creation of this temporary table.
-    TRUE <=> create_tmp_table will create only the TABLE structure.
-  */
-  bool skip_create_table;
   TMP_TABLE_PARAM()
     :copy_field(0), group_parts(0),
      group_length(0), group_null_parts(0), convert_blob_length(0),

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-05-12 04:09:58 +0000
+++ b/sql/sql_select.cc	2010-05-18 17:46:32 +0000
@@ -9845,8 +9845,9 @@ simplify_joins(JOIN *join, List<TABLE_LI
         {
           conds= and_conds(conds, table->on_expr);
           conds->top_level_item();
-          if (!conds->fixed)
-            conds->fix_fields(join->thd, &conds);
+          /* conds is always a new item as both cond and on_expr existed */
+          DBUG_ASSERT(!conds->fixed);
+          conds->fix_fields(join->thd, &conds);
         }
         else
           conds= table->on_expr; 
@@ -11035,7 +11036,7 @@ TABLE *
 create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields,
 		 ORDER *group, bool distinct, bool save_sum_fields,
 		 ulonglong select_options, ha_rows rows_limit,
-		 char *table_alias)
+                 char *table_alias, bool do_not_open)
 {
   MEM_ROOT *mem_root_save, own_root;
   TABLE *table;
@@ -11728,7 +11729,7 @@ create_tmp_table(THD *thd,TMP_TABLE_PARA
   if (thd->is_fatal_error)				// If end of memory
     goto err;					 /* purecov: inspected */
   share->db_record_offset= 1;
-  if (!param->skip_create_table)
+  if (!do_not_open)
   {
     if (share->db_type() == TMP_ENGINE_HTON)
     {

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2010-04-29 21:10:39 +0000
+++ b/sql/sql_select.h	2010-05-18 17:46:32 +0000
@@ -1984,7 +1984,7 @@ void push_index_cond(JOIN_TAB *tab, uint
 TABLE *create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields,
 			ORDER *group, bool distinct, bool save_sum_fields,
 			ulonglong select_options, ha_rows rows_limit,
-			char* alias);
+                        char* alias, bool do_not_open=FALSE);
 void free_tmp_table(THD *thd, TABLE *entry);
 bool create_internal_tmp_table_from_heap(THD *thd, TABLE *table,
                                          ENGINE_COLUMNDEF *start_recinfo,

=== modified file 'sql/sql_union.cc'
--- a/sql/sql_union.cc	2010-05-12 04:09:58 +0000
+++ b/sql/sql_union.cc	2010-05-18 17:46:32 +0000
@@ -126,12 +126,11 @@ select_union::create_result_table(THD *t
   tmp_table_param.init();
   tmp_table_param.field_count= column_types->elements;
   tmp_table_param.bit_fields_as_long= bit_fields_as_long;
-  tmp_table_param.skip_create_table= !create_table;
-
 
   if (! (table= create_tmp_table(thd_arg, &tmp_table_param, *column_types,
                                  (ORDER*) 0, is_union_distinct, 1,
-                                 options, HA_POS_ERROR, (char*) alias)))
+                                 options, HA_POS_ERROR, (char*) alias,
+                                 !create_table)))
     return TRUE;
   if (create_table)
   {