bzr commit into mysql-5.1-bugteam branch (Sergey.Glukhov:3354) Bug#50995

[Sergey Glukhov]

#At file:///home/gluh/MySQL/mysql-5.1-bugteam/ based on revid:magne.mahre@stripped

 3354 Sergey Glukhov	2010-02-25
      Bug#50995 Having clause on subquery result produces incorrect results.
      The problem is that cond->fix_fields(thd, 0) breaks condition(cuts off 'having').
      The bug actually not in fix_fields but in the base_list_iterator::replace(&list) function.
      The base_list::last points to the 'next' member of the last element in list.
      This code in the base_list_iterator::replace:
       if ((list->last == &current->next) && (new_list.elements > 1))
         list->last= new_list.last;
      should extend the list when we actually append on list to another. But because
      the 'next' member is the first in the list_node structure the 'last' is the same
      as &current->next when current is one prior to the last element in list.
      All this leads to stripping off last part of AND/OR expression while optimizing
      AND-OR condition tree.
      Another probelm is fixed here is that we should not add an item to condition if
      this item is 0.
     @ mysql-test/r/having.result
        test result
     @ mysql-test/t/having.test
        test case
     @ sql/item_cmpfunc.h
        added ASSERT to make sure that we do not add zero item
     @ sql/sql_list.h
        The base_list::last points to the 'next' member of the last element in list.
        This code in the base_list_iterator::replace:
         if ((list->last == &current->next) && (new_list.elements > 1))
           list->last= new_list.last;
        should extend the list when we actually append on list to another. But because
        the 'next' member is the first in the list_node structure the 'last' is the same
        as &current->next when current is one prior to the last element in list.
        The fix is to move list_node *next from first position in struct.
     @ sql/sql_select.cc
        skip adding an item to condition if
        this item is 0 or empty list.

    modified:
      mysql-test/r/having.result
      mysql-test/t/having.test
      sql/item_cmpfunc.h
      sql/sql_list.h
      sql/sql_select.cc
=== modified file 'mysql-test/r/having.result'
--- a/mysql-test/r/having.result	2009-01-16 15:38:38 +0000
+++ b/mysql-test/r/having.result	2010-02-25 12:42:52 +0000
@@ -430,4 +430,24 @@ SELECT b, COUNT(DISTINCT a) FROM t1 GROU
 b	COUNT(DISTINCT a)
 NULL	1
 DROP TABLE t1;
+#
+# Bug#50995 Having clause on subquery result produces incorrect results.
+#
+CREATE TABLE t1
+(
+id1 INT,
+id2 INT NOT NULL,
+INDEX id1(id2)
+);
+INSERT INTO t1 SET id1=1, id2=1;
+INSERT INTO t1 SET id1=2, id2=1;
+INSERT INTO t1 SET id1=3, id2=1;
+SELECT t1.id1,
+(SELECT 0 FROM DUAL
+WHERE t1.id1=t1.id1) AS amount FROM t1
+WHERE t1.id2 = 1
+HAVING amount > 0
+ORDER BY t1.id1;
+id1	amount
+DROP TABLE t1;
 End of 5.0 tests

=== modified file 'mysql-test/t/having.test'
--- a/mysql-test/t/having.test	2009-01-16 15:38:38 +0000
+++ b/mysql-test/t/having.test	2010-02-25 12:42:52 +0000
@@ -442,4 +442,30 @@ INSERT INTO t1 VALUES (1, 1), (2,2), (3,
 SELECT b, COUNT(DISTINCT a) FROM t1 GROUP BY b HAVING b is NULL;
 DROP TABLE t1;
 
+
+--echo #
+--echo # Bug#50995 Having clause on subquery result produces incorrect results.
+--echo #
+
+CREATE TABLE t1
+(
+ id1 INT,
+ id2 INT NOT NULL,
+ INDEX id1(id2)
+);
+
+INSERT INTO t1 SET id1=1, id2=1;
+INSERT INTO t1 SET id1=2, id2=1;
+INSERT INTO t1 SET id1=3, id2=1;
+
+SELECT t1.id1,
+(SELECT 0 FROM DUAL
+ WHERE t1.id1=t1.id1) AS amount FROM t1
+WHERE t1.id2 = 1
+HAVING amount > 0
+ORDER BY t1.id1;
+
+DROP TABLE t1;
+
+
 --echo End of 5.0 tests

=== modified file 'sql/item_cmpfunc.h'
--- a/sql/item_cmpfunc.h	2010-01-13 10:28:42 +0000
+++ b/sql/item_cmpfunc.h	2010-02-25 12:42:52 +0000
@@ -1474,9 +1474,21 @@ public:
   Item_cond(THD *thd, Item_cond *item);
   Item_cond(List<Item> &nlist)
     :Item_bool_func(), list(nlist), abort_on_null(0) {}
-  bool add(Item *item) { return list.push_back(item); }
-  bool add_at_head(Item *item) { return list.push_front(item); }
-  void add_at_head(List<Item> *nlist) { list.prepand(nlist); }
+  bool add(Item *item)
+  {
+    DBUG_ASSERT(item);
+    return list.push_back(item);
+  }
+  bool add_at_head(Item *item)
+  {
+    DBUG_ASSERT(item);
+    return list.push_front(item);
+  }
+  void add_at_head(List<Item> *nlist)
+  {
+    DBUG_ASSERT(nlist->elements);
+    list.prepand(nlist);
+  }
   bool fix_fields(THD *, Item **ref);
 
   enum Type type() const { return COND_ITEM; }

=== modified file 'sql/sql_list.h'
--- a/sql/sql_list.h	2009-06-10 08:59:49 +0000
+++ b/sql/sql_list.h	2010-02-25 12:42:52 +0000
@@ -73,10 +73,10 @@ public:
 
 struct list_node :public Sql_alloc
 {
-  list_node *next;
   void *info;
+  list_node *next;
   list_node(void *info_par,list_node *next_par)
-    :next(next_par),info(info_par)
+    :info(info_par), next(next_par)
   {}
   list_node()					/* For end_of_list */
   {

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-02-16 09:13:49 +0000
+++ b/sql/sql_select.cc	2010-02-25 12:42:52 +0000
@@ -8198,7 +8198,8 @@ static Item *eliminate_item_equal(COND *
   else
   {
     DBUG_ASSERT(cond->type() == Item::COND_ITEM);
-    ((Item_cond *) cond)->add_at_head(&eq_list);
+    if (eq_list.elements)
+      ((Item_cond *) cond)->add_at_head(&eq_list);
   }
 
   cond->quick_fix_field();
@@ -15655,7 +15656,7 @@ static bool add_ref_to_table_cond(THD *t
 
   Item_cond_and *cond=new Item_cond_and();
   TABLE *table=join_tab->table;
-  int error;
+  int error= 0;
   if (!cond)
     DBUG_RETURN(TRUE);
 
@@ -15673,7 +15674,8 @@ static bool add_ref_to_table_cond(THD *t
     cond->fix_fields(thd, (Item**)&cond);
   if (join_tab->select)
   {
-    error=(int) cond->add(join_tab->select->cond);
+    if (join_tab->select->cond)
+      error=(int) cond->add(join_tab->select->cond);
     join_tab->select_cond=join_tab->select->cond=cond;
   }
   else if ((join_tab->select= make_select(join_tab->table, 0, 0, cond, 0,


Attachment: [text/bzr-bundle] bzr/[e-mail]