From: Date: February 12 2007 4:36am
Subject: bk commit into 5.0 tree (igor:1.2412) BUG#26209
List-Archive: http://lists.mysql.com/commits/19668
X-Bug: 26209
Message-Id: <20070212033652.3C5B81829A3@olga.mysql.com>

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-02-11 19:36:46-08:00, igor@stripped +3 -0
  Fixed bug #26209.
  The function make_unireg_sortorder ignored the fact that any
  view field is represented by a 'ref' object.
  This could lead to wrong results for the queries containing
  both GROUP BY and ORDER BY clauses.

  mysql-test/r/view.result@stripped, 2007-02-11 19:36:44-08:00, igor@stripped +18 -0
    Added a test case for bug #26209.

  mysql-test/t/view.test@stripped, 2007-02-11 19:36:44-08:00, igor@stripped +20 -0
    Added a test case for bug #26209.

  sql/sql_select.cc@stripped, 2007-02-11 19:36:44-08:00, igor@stripped +8 -8
    Fixed bug #26209.
    The function make_unireg_sortorder ignored the fact that any
    view field is represented by a 'ref' object.
    This could lead to wrong results for the queries containing
    both GROUP BY and ORDER BY clauses.
    
    
     

# 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-bug26209

--- 1.488/sql/sql_select.cc	2007-02-11 19:36:52 -08:00
+++ 1.489/sql/sql_select.cc	2007-02-11 19:36:52 -08:00
@@ -12744,15 +12744,15 @@
 
   for (;order;order=order->next,pos++)
   {
-    pos->field=0; pos->item=0;
-    if (order->item[0]->type() == Item::FIELD_ITEM)
-      pos->field= ((Item_field*) (*order->item))->field;
-    else if (order->item[0]->type() == Item::SUM_FUNC_ITEM &&
-	     !order->item[0]->const_item())
-      pos->field= ((Item_sum*) order->item[0])->get_tmp_table_field();
-    else if (order->item[0]->type() == Item::COPY_STR_ITEM)
+    Item *item= order->item[0]->real_item();
+    pos->field= 0; pos->item= 0;
+    if (item->type() == Item::FIELD_ITEM)
+      pos->field= ((Item_field*) item)->field;
+    else if (item->type() == Item::SUM_FUNC_ITEM && !item->const_item())
+      pos->field= ((Item_sum*) item)->get_tmp_table_field();
+    else if (item->type() == Item::COPY_STR_ITEM)
     {						// Blob patch
-      pos->item= ((Item_copy_string*) (*order->item))->item;
+      pos->item= ((Item_copy_string*) item)->item;
     }
     else
       pos->item= *order->item;

--- 1.190/mysql-test/r/view.result	2007-02-11 19:36:52 -08:00
+++ 1.191/mysql-test/r/view.result	2007-02-11 19:36:52 -08:00
@@ -3117,4 +3117,22 @@
 Note	1003	select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` order by `test`.`t1`.`f1`,`test`.`t1`.`f2`
 drop view v1;
 drop table t1;
+CREATE TABLE t1 (
+id int(11) NOT NULL PRIMARY KEY,
+country varchar(32),
+code int(11) default NULL
+);
+INSERT INTO t1 VALUES
+(1,'ITALY',100),(2,'ITALY',200),(3,'FRANCE',100), (4,'ITALY',100);
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT code, COUNT(DISTINCT country) FROM t1 GROUP BY code ORDER BY MAX(id);
+code	COUNT(DISTINCT country)
+200	1
+100	2
+SELECT code, COUNT(DISTINCT country) FROM v1 GROUP BY code ORDER BY MAX(id);
+code	COUNT(DISTINCT country)
+200	1
+100	2
+DROP VIEW v1;
+DROP TABLE t1;
 End of 5.0 tests.

--- 1.175/mysql-test/t/view.test	2007-02-11 19:36:52 -08:00
+++ 1.176/mysql-test/t/view.test	2007-02-11 19:36:52 -08:00
@@ -3038,4 +3038,24 @@
 drop view v1;
 drop table t1;
 
+#
+# Bug#26209: queries with GROUP BY and ORDER BY using views 
+#
+
+CREATE TABLE t1 (
+  id int(11) NOT NULL PRIMARY KEY,
+  country varchar(32),
+  code int(11) default NULL
+);
+INSERT INTO t1 VALUES
+  (1,'ITALY',100),(2,'ITALY',200),(3,'FRANCE',100), (4,'ITALY',100);
+
+CREATE VIEW v1 AS SELECT * FROM t1;
+
+SELECT code, COUNT(DISTINCT country) FROM t1 GROUP BY code ORDER BY MAX(id);
+SELECT code, COUNT(DISTINCT country) FROM v1 GROUP BY code ORDER BY MAX(id);
+
+DROP VIEW v1;
+DROP TABLE t1;
+
 --echo End of 5.0 tests.