MySQL Lists are EOL. Please join:

List:Internals« Previous MessageNext Message »
From:sanja Date:August 13 2005 4:45am
Subject:bk commit into 4.1 tree (bell:1.2370) BUG#11867
View as plain text  
Below is the list of changes that have just been committed into a local
4.1 repository of bell. When bell 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
  1.2370 05/08/13 07:45:14 bell@stripped +6 -0
  fixed convertion and handling IN subqueries with rows (BUG#11867)

  sql/sql_select.cc
    1.429 05/08/13 07:45:08 bell@stripped +2 -1
    fixed debug print

  sql/item_subselect.cc
    1.139 05/08/13 07:45:07 bell@stripped +135 -36
    fixed converting row IN subqueries

  sql/item_cmpfunc.h
    1.108 05/08/13 07:45:07 bell@stripped +5 -0
    Prevented removing of Item_test_isnotnull from HAVING

  sql/item.h
    1.189 05/08/13 07:45:07 bell@stripped +9 -0
    add method to prevent of removing Item_ref_null_helper from HAVING

  mysql-test/t/subselect.test
    1.150 05/08/13 07:45:07 bell@stripped +12 -1
    tests of ion subqueries with row

  mysql-test/r/subselect.result
    1.170 05/08/13 07:45:06 bell@stripped +57 -3
    testst of IN subqueries with row

# 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:	bell
# Host:	50.0.168.192.in-addr.arpa
# Root:	/Users/bell/mysql/bk/work-bug2-4.1

--- 1.188/sql/item.h	2005-07-26 10:50:52 +03:00
+++ 1.189/sql/item.h	2005-08-13 07:45:07 +03:00
@@ -1023,6 +1023,15 @@
   String* val_str(String* s);
   bool get_date(TIME *ltime, uint fuzzydate);
   void print(String *str);
+  /*
+    we add RAND_TABLE_BIT to prevent moving this item from HAVING to WHERE
+  */
+  table_map used_tables() const
+  {
+    return (depended_from ?
+            OUTER_REF_TABLE_BIT :
+            (*ref)->used_tables() | RAND_TABLE_BIT);
+  }
 };
 
 class Item_null_helper :public Item_ref_null_helper

--- 1.107/sql/item_cmpfunc.h	2005-06-28 22:20:22 +03:00
+++ 1.108/sql/item_cmpfunc.h	2005-08-13 07:45:07 +03:00
@@ -832,6 +832,11 @@
   longlong val_int();
   const char *func_name() const { return "<is_not_null_test>"; }
   void update_used_tables();
+  /*
+    we add RAND_TABLE_BIT to prevent moving this item from HAVING to WHERE
+  */
+  table_map used_tables() const
+    { return used_tables_cache | RAND_TABLE_BIT; }
 };
 
 

--- 1.428/sql/sql_select.cc	2005-08-09 00:13:46 +03:00
+++ 1.429/sql/sql_select.cc	2005-08-13 07:45:08 +03:00
@@ -1439,7 +1439,8 @@
 	curr_join->tmp_having= make_cond_for_table(curr_join->tmp_having,
 						   ~ (table_map) 0,
 						   ~used_tables);
-	DBUG_EXECUTE("where",print_where(conds,"having after sort"););
+	DBUG_EXECUTE("where",print_where(curr_join->tmp_having,
+                                         "having after sort"););
       }
     }
     {

--- 1.169/mysql-test/r/subselect.result	2005-08-09 02:55:21 +03:00
+++ 1.170/mysql-test/r/subselect.result	2005-08-13 07:45:06 +03:00
@@ -2742,13 +2742,26 @@
 5	6	N
 7	8	N
 insert into t2 values (null,null,'N');
+insert into t2 values (null,3,'0');
+insert into t2 values (null,5,'0');
+insert into t2 values (10,null,'0');
+insert into t1 values (10,3,'0');
+insert into t1 values (10,5,'0');
+insert into t1 values (10,10,'0');
 SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N') as 'test' from t1;
 one	two	test
-1	2	0
-2	3	0
-3	4	0
+1	2	NULL
+2	3	NULL
+3	4	NULL
 5	6	1
 7	8	1
+10	3	NULL
+10	5	NULL
+10	10	NULL
+SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
+one	two
+5	6
+7	8
 SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N' group by one,two) as 'test' from t1;
 one	two	test
 1	2	NULL
@@ -2756,6 +2769,47 @@
 3	4	NULL
 5	6	1
 7	8	1
+10	3	NULL
+10	5	NULL
+10	10	NULL
+SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0') as 'test' from t1;
+one	two	test
+1	2	0
+2	3	NULL
+3	4	0
+5	6	0
+7	8	0
+10	3	NULL
+10	5	NULL
+10	10	NULL
+SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
+one	two	test
+1	2	0
+2	3	NULL
+3	4	0
+5	6	0
+7	8	0
+10	3	NULL
+10	5	NULL
+10	10	NULL
+explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0') as 'test' from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
+Warnings:
+Note	1003	select test.t1.one AS `one`,test.t1.two AS `two`,<in_optimizer>((test.t1.one,test.t1.two),<exists>(select test.t2.one AS `one`,test.t2.two AS `two` from test.t2 where ((test.t2.flag = _latin1'0') and ((<cache>(test.t1.one) = test.t2.one) or isnull(test.t2.one)) and ((<cache>(test.t1.two) = test.t2.two) or isnull(test.t2.two))) having (<is_not_null_test>(test.t2.one) and <is_not_null_test>(test.t2.two)))) AS `test` from test.t1
+explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
+Warnings:
+Note	1003	select test.t1.one AS `one`,test.t1.two AS `two` from test.t1 where <in_optimizer>((test.t1.one,test.t1.two),<exists>(select test.t2.one AS `one`,test.t2.two AS `two` from test.t2 where ((test.t2.flag = _latin1'N') and (<cache>(test.t1.one) = test.t2.one) and (<cache>(test.t1.two) = test.t2.two))))
+explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where; Using temporary; Using filesort
+Warnings:
+Note	1003	select test.t1.one AS `one`,test.t1.two AS `two`,<in_optimizer>((test.t1.one,test.t1.two),<exists>(select test.t2.one AS `one`,test.t2.two AS `two` from test.t2 where (test.t2.flag = _latin1'0') group by test.t2.one,test.t2.two having (((<cache>(test.t1.one) = test.t2.one) or isnull(test.t2.one)) and ((<cache>(test.t1.two) = test.t2.two) or isnull(test.t2.two)) and <is_not_null_test>(test.t2.one) and <is_not_null_test>(test.t2.two)))) AS `test` from test.t1
 DROP TABLE t1,t2;
 CREATE TABLE t1 (a char(5), b char(5));
 INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa');

--- 1.149/mysql-test/t/subselect.test	2005-08-09 02:55:21 +03:00
+++ 1.150/mysql-test/t/subselect.test	2005-08-13 07:45:07 +03:00
@@ -1769,9 +1769,20 @@
   WHERE ROW(one,two) IN (SELECT DISTINCT one,two FROM t1 WHERE flag = 'N');
 
 insert into t2 values (null,null,'N');
+insert into t2 values (null,3,'0');
+insert into t2 values (null,5,'0');
+insert into t2 values (10,null,'0');
+insert into t1 values (10,3,'0');
+insert into t1 values (10,5,'0');
+insert into t1 values (10,10,'0');
 SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N') as 'test' from t1;
+SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
 SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N' group by one,two) as 'test' from t1;
-
+SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0') as 'test' from t1;
+SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
+explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0') as 'test' from t1;
+explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
+explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
 DROP TABLE t1,t2;
 
 #

--- 1.138/sql/item_subselect.cc	2005-08-07 22:03:59 +03:00
+++ 1.139/sql/item_subselect.cc	2005-08-13 07:45:07 +03:00
@@ -859,6 +859,7 @@
 	argument (reference) to fix_fields()
       */
       select_lex->where= join->conds= and_items(join->conds, item);
+      select_lex->where->top_level_item();
       if (join->conds->fix_fields(thd, join->tables_list, 0))
 	DBUG_RETURN(RES_ERROR);
     }
@@ -912,10 +913,13 @@
 Item_subselect::trans_res
 Item_in_subselect::row_value_transformer(JOIN *join)
 {
-  DBUG_ENTER("Item_in_subselect::row_value_transformer");
-
-  Item *item= 0;
   SELECT_LEX *select_lex= join->select_lex;
+  Item *having_item= 0;
+  uint cols_num= left_expr->cols();
+  bool is_having_used= (join->having || select_lex->with_sum_func ||
+                        select_lex->group_list.first ||
+                        !select_lex->table_list.elements);
+  DBUG_ENTER("Item_in_subselect::row_value_transformer");
 
   if (select_lex->item_list.elements != left_expr->cols())
   {
@@ -946,61 +950,156 @@
   }
 
   select_lex->uncacheable|= UNCACHEABLE_DEPENDENT;
+  if (is_having_used)
   {
-    uint n= left_expr->cols();
-    List_iterator_fast<Item> li(select_lex->item_list);
-    for (uint i= 0; i < n; i++)
+    /*
+      (l1, l2, l3) IN (SELECT v1, v2, v3 ... HAVING having) =>
+      EXISTS (SELECT ... HAVING having and
+                                (l1 = v1 or is null v1) and
+                                (l2 = v2 or is null v2) and
+                                (l3 = v3 or is null v3) and
+                                is_not_null_test(v1) and
+                                is_not_null_test(v2) and
+                                is_not_null_test(v3))
+      where is_not_null_test used to register nulls in case if we have
+      not found matching to return correct NULL value
+    */
+    Item *item_having_part2= 0;
+    for (uint i= 0; i < cols_num; i++)
     {
-      Item *func;
       DBUG_ASSERT(left_expr->fixed && select_lex->ref_pointer_array[i]->fixed);
       if (select_lex->ref_pointer_array[i]->
           check_cols(left_expr->el(i)->cols()))
         DBUG_RETURN(RES_ERROR);
-      if (join->having || select_lex->with_sum_func ||
-          select_lex->group_list.elements)
-        func= new Item_ref_null_helper(this,
-				       select_lex->ref_pointer_array+i,
-				       (char *) "<no matter>",
-				       (char *) "<list ref>");
-      else
-        func= li++;
-      func=
-	eq_creator.create(new Item_direct_ref((*optimizer->get_cache())->
-					      addr(i),
-					      (char *)"<no matter>",
-					      (char *)in_left_expr_name),
-			  func);
-      item= and_items(item, func);
+      Item *item_eq=
+        new Item_func_eq(new
+                         Item_direct_ref((*optimizer->get_cache())->
+                                         addr(i),
+                                         (char *)"<no matter>",
+                                         (char *)in_left_expr_name),
+                         new
+                         Item_direct_ref(select_lex->ref_pointer_array + i,
+                                         (char *)"<no matter>",
+                                         (char *)"<list ref>")
+                        );
+      Item *item_isnull=
+        new Item_func_isnull(new
+                             Item_direct_ref( select_lex->
+                                              ref_pointer_array+i,
+                                              (char *)"<no matter>",
+                                              (char *)"<list ref>")
+                            );
+      having_item=
+        and_items(having_item,
+                  new Item_cond_or(item_eq, item_isnull));
+      item_having_part2=
+        and_items(item_having_part2,
+                  new
+                  Item_is_not_null_test(this,
+                                        new
+                                        Item_direct_ref(select_lex->
+                                                        ref_pointer_array + i,
+                                                        (char *)"<no matter>",
+                                                        (char *)"<list ref>")
+                                       )
+                 );
+      item_having_part2->top_level_item();
     }
+    having_item= and_items(having_item, item_having_part2);
+    having_item->top_level_item();
   }
-  if (join->having || select_lex->with_sum_func ||
-      select_lex->group_list.first ||
-      !select_lex->table_list.elements)
+  else
   {
     /*
+      (l1, l2, l3) IN (SELECT v1, v2, v3 ... WHERE where) =>
+      EXISTS (SELECT ... WHERE where and
+                               (l1 = v1 or is null v1) and
+                               (l2 = v2 or is null v2) and
+                               (l3 = v3 or is null v3)
+                         HAVING is_not_null_test(v1) and
+                                is_not_null_test(v2) and
+                                is_not_null_test(v3))
+      where is_not_null_test register NULLs values but reject rows
+
+      in case when we do not need correct NULL, we have simplier construction:
+      EXISTS (SELECT ... WHERE where and
+                               (l1 = v1) and
+                               (l2 = v2) and
+                               (l3 = v3)
+    */
+    Item *where_item= 0;
+    for (uint i= 0; i < cols_num; i++)
+    {
+      Item *item, *item_isnull;
+      DBUG_ASSERT(left_expr->fixed && select_lex->ref_pointer_array[i]->fixed);
+      if (select_lex->ref_pointer_array[i]->
+          check_cols(left_expr->el(i)->cols()))
+        DBUG_RETURN(RES_ERROR);
+      item=
+        new Item_func_eq(new
+                         Item_direct_ref((*optimizer->get_cache())->
+                                         addr(i),
+                                         (char *)"<no matter>",
+                                         (char *)in_left_expr_name),
+                         new
+                         Item_direct_ref( select_lex->
+                                          ref_pointer_array+i,
+                                          (char *)"<no matter>",
+                                          (char *)"<list ref>")
+                        );
+      if (!abort_on_null)
+      {
+        having_item=
+          and_items(having_item,
+                    new
+                    Item_is_not_null_test(this,
+                                          new
+                                          Item_direct_ref(select_lex->
+                                                          ref_pointer_array + i,
+                                                          (char *)"<no matter>",
+                                                          (char *)"<list ref>")
+                                         )
+                  );
+        item_isnull= new
+          Item_func_isnull(new
+                           Item_direct_ref( select_lex->
+                                            ref_pointer_array+i,
+                                            (char *)"<no matter>",
+                                            (char *)"<list ref>")
+                          );
+
+        item= new Item_cond_or(item, item_isnull);
+      }
+
+      where_item= and_items(where_item, item);
+    }
+    /*
       AND can't be changed during fix_fields()
-      we can assign select_lex->having here, and pass 0 as last
+      we can assign select_lex->where here, and pass 0 as last
       argument (reference) to fix_fields()
     */
-    select_lex->having= join->having= and_items(join->having, item);
-    select_lex->having_fix_field= 1;
-    if (join->having->fix_fields(thd, join->tables_list, 0))
-    {
-      select_lex->having_fix_field= 0;
+    select_lex->where= join->conds= and_items(join->conds, where_item);
+    select_lex->where->top_level_item();
+    if (join->conds->fix_fields(thd, join->tables_list, 0))
       DBUG_RETURN(RES_ERROR);
-    }
-    select_lex->having_fix_field= 0;
   }
-  else
+  if (having_item)
   {
+    bool res;
+    select_lex->having= join->having= and_items(join->having, having_item);
+    select_lex->having->top_level_item();
     /*
       AND can't be changed during fix_fields()
       we can assign select_lex->having here, and pass 0 as last
       argument (reference) to fix_fields()
     */
-    select_lex->where= join->conds= and_items(join->conds, item);
-    if (join->conds->fix_fields(thd, join->tables_list, 0))
+    select_lex->having_fix_field= 1;
+    res= join->having->fix_fields(thd, join->tables_list, 0);
+    select_lex->having_fix_field= 0;
+    if (res)
+    {
       DBUG_RETURN(RES_ERROR);
+    }
   }
   DBUG_RETURN(RES_OK);
 }
Thread
bk commit into 4.1 tree (bell:1.2370) BUG#11867sanja13 Aug