bk commit into 5.0 tree (igor:1.2493) BUG#29104

[igor]

Below is the list of changes that have just been committed into a local
5.0 repository of igor. When igor 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-06-20 12:43:14-07:00, igor@stripped +3 -0
  Fixed bug #29104: assertion abort for grouping queries using views.
  The abort happened when a query contained a conjunctive predicate
  of the form 'view column = constant' in the WHERE condition and 
  the grouping list also contained a reference to a view column yet
  a different one.
  
  Removed the failing assertion as invalid in a general case.
  
  Also fixed a bug that prevented applying some optimization for grouping
  queries using views. If the WHERE condition of such a query contains
  a conjunctive condition of the form 'view column = constant' and
  this view column is used in the grouping list then grouping by this
  column can be eliminated. The bug blocked performing this elimination.

  mysql-test/r/view.result@stripped, 2007-06-20 12:43:10-07:00, igor@stripped +24 -0
    Added a test case for bug #29104.

  mysql-test/t/view.test@stripped, 2007-06-20 12:43:10-07:00, igor@stripped +24 -0
    Added a test case for bug #29104.

  sql/item.cc@stripped, 2007-06-20 12:43:10-07:00, igor@stripped +4 -6
    Fixed bug #29104: assertion abort for grouping queries using views.
    The abort happened when a query contained a conjunctive predicate
    of the form 'view column = constant' in the WHERE condition and 
    the grouping list also contained a reference to a view column yet
    a different one.
    
    Removed the failing assertion as invalid in a general case.
    
    Also fixed a bug that prevented applying some optimization for grouping
    queries using views. If the WHERE condition of such a query contains
    a conjunctive condition of the form 'view column = constant' and
    this view column is used in the grouping list then grouping by this
    column can be eliminated. The bug blocked performing this elimination.
    This bug was in the function Item_field::eq while the failing
    assertion was in the function Item_direct_view_ref::eq.

# 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:	igor
# Host:	olga.mysql.com
# Root:	/home/igor/dev-opt/mysql-5.0-opt-bug29104

--- 1.272/sql/item.cc	2007-06-20 12:43:19 -07:00
+++ 1.273/sql/item.cc	2007-06-20 12:43:19 -07:00
@@ -1938,10 +1938,11 @@
 
 bool Item_field::eq(const Item *item, bool binary_cmp) const
 {
-  if (item->type() != FIELD_ITEM)
+  Item *real_item= ((Item *) item)->real_item();
+  if (real_item->type() != FIELD_ITEM)
     return 0;
   
-  Item_field *item_field= (Item_field*) item;
+  Item_field *item_field= (Item_field*) real_item;
   if (item_field->field && field)
     return item_field->field == field;
   /*
@@ -5627,8 +5628,7 @@
   DESCRIPTION
     A view column reference is considered equal to another column
     reference if the second one is a view column and if both column
-    references resolve to the same item. It is assumed that both
-    items are of the same type.
+    references resolve to the same item.
 
   RETURN
     TRUE    Referenced item is equal to given item
@@ -5644,8 +5644,6 @@
     if (item_ref->ref_type() == VIEW_REF)
     {
       Item *item_ref_ref= *(item_ref->ref);
-      DBUG_ASSERT((*ref)->real_item()->type() == 
-                  item_ref_ref->real_item()->type());
       return ((*ref)->real_item() == item_ref_ref->real_item());
     }
   }

--- 1.207/mysql-test/r/view.result	2007-06-20 12:43:19 -07:00
+++ 1.208/mysql-test/r/view.result	2007-06-20 12:43:19 -07:00
@@ -3476,4 +3476,28 @@
 2	0
 DROP VIEW v1,v2;
 DROP TABLE t1,t2,t3,t4;
+CREATE TABLE t1 (a int, b int);
+INSERT INTO t1 VALUES (1,2), (2,2), (1,3), (1,2);
+CREATE VIEW v1 AS SELECT a, b+1 as b FROM t1;
+SELECT b, SUM(a) FROM v1 WHERE b=3 GROUP BY b;
+b	SUM(a)
+3	4
+EXPLAIN SELECT b, SUM(a) FROM v1 WHERE b=3 GROUP BY b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
+SELECT a, SUM(b) FROM v1 WHERE b=3 GROUP BY a;
+a	SUM(b)
+1	6
+2	3
+EXPLAIN SELECT a, SUM(b) FROM v1 WHERE b=3 GROUP BY a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using where; Using temporary; Using filesort
+SELECT a, SUM(b) FROM v1 WHERE a=1 GROUP BY a;
+a	SUM(b)
+1	10
+EXPLAIN SELECT a, SUM(b) FROM v1 WHERE a=1 GROUP BY a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
+DROP VIEW v1;
+DROP TABLE t1;
 End of 5.0 tests.

--- 1.187/mysql-test/t/view.test	2007-06-20 12:43:19 -07:00
+++ 1.188/mysql-test/t/view.test	2007-06-20 12:43:19 -07:00
@@ -3324,4 +3324,28 @@
 DROP VIEW v1,v2;
 DROP TABLE t1,t2,t3,t4;
 
+#
+# Bug #29104: assertion abort for a query with a view column reference
+#             in the GROUP BY list and a condition requiring the value  
+#             of another view column to be equal to a constant
+#
+
+CREATE TABLE t1 (a int, b int);
+INSERT INTO t1 VALUES (1,2), (2,2), (1,3), (1,2);
+
+CREATE VIEW v1 AS SELECT a, b+1 as b FROM t1;
+
+
+SELECT b, SUM(a) FROM v1 WHERE b=3 GROUP BY b;
+EXPLAIN SELECT b, SUM(a) FROM v1 WHERE b=3 GROUP BY b;
+
+SELECT a, SUM(b) FROM v1 WHERE b=3 GROUP BY a;
+EXPLAIN SELECT a, SUM(b) FROM v1 WHERE b=3 GROUP BY a;
+
+SELECT a, SUM(b) FROM v1 WHERE a=1 GROUP BY a;
+EXPLAIN SELECT a, SUM(b) FROM v1 WHERE a=1 GROUP BY a;
+
+DROP VIEW v1;
+DROP TABLE t1;
+
 --echo End of 5.0 tests.