bk commit into 4.0 tree (hf:1.2161) BUG#3874

[holyfoot]

Below is the list of changes that have just been committed into a local
4.0 repository of hf. When hf 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.2161 05/10/11 18:54:19 hf@deer.(none) +3 -0
  Fix for the bug #3874 (Group field is not considered)

  sql/sql_select.cc
    1.292 05/10/11 18:54:10 hf@deer.(none) +2 -1
    we won't skip items that use fields from the 'first_table'.
    It leads to errors if these fields are not from the ON list.

  mysql-test/t/select.test
    1.29 05/10/11 18:54:10 hf@deer.(none) +18 -0
    test case added

  mysql-test/r/select.result
    1.39 05/10/11 18:54:10 hf@deer.(none) +29 -0
    result fixed

# 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:	hf
# Host:	deer.(none)
# Root:	/home/hf/work/mysql-4.0.clean

--- 1.291/sql/sql_select.cc	Mon Oct 10 00:01:31 2005
+++ 1.292/sql/sql_select.cc	Tue Oct 11 18:54:10 2005
@@ -3264,7 +3264,8 @@
 	  DBUG_PRINT("info",("removing: %s", order->item[0]->full_name()));
 	  continue;
 	}
-	if ((ref=order_tables & (not_const_tables ^ first_table)))
+	if ((ref=order_tables & (not_const_tables ^ first_table)) &&
+            !(order_tables & first_table))
 	{
 	  if (only_eq_ref_tables(join,first_order,ref))
 	  {

--- 1.38/mysql-test/r/select.result	Mon Oct 10 00:04:20 2005
+++ 1.39/mysql-test/r/select.result	Tue Oct 11 18:54:10 2005
@@ -2441,3 +2441,32 @@
 SELECT a, b AS c FROM t1 ORDER BY b+1;
 a	c
 drop table t1;
+CREATE TABLE t1 ( a INT NOT NULL, b INT NOT NULL, UNIQUE idx (a,b) );
+INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4);
+CREATE TABLE t2 ( a INT NOT NULL, b INT NOT NULL, c INT );
+INSERT INTO t2 VALUES ( 1,10,1), (1,10,2), (1,11,1), (1,11,2), (1,2,1), (1,2,2),
+(1,2,3);
+SELECT t2.a, t2.b, IF(t1.b IS NULL,'',c) AS c, COUNT(*) AS d FROM t2 LEFT JOIN
+t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY a, b, c;
+a	b	c	d
+1	2	1	1
+1	2	2	1
+1	2	3	1
+1	10		2
+1	11		2
+SELECT t2.a, t2.b, IF(t1.b IS NULL,'',c) AS c, COUNT(*) AS d FROM t2 LEFT JOIN
+t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY t1.a, t1.b, c;
+a	b	c	d
+1	10		4
+1	2	1	1
+1	2	2	1
+1	2	3	1
+SELECT t2.a, t2.b, IF(t1.b IS NULL,'',c) AS c, COUNT(*) AS d FROM t2 LEFT JOIN
+t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY t2.a, t2.b, c;
+a	b	c	d
+1	2	1	1
+1	2	2	1
+1	2	3	1
+1	10		2
+1	11		2
+drop table t2, t1;

--- 1.28/mysql-test/t/select.test	Mon Oct 10 00:03:15 2005
+++ 1.29/mysql-test/t/select.test	Tue Oct 11 18:54:10 2005
@@ -1992,3 +1992,21 @@
 SELECT a, b AS c FROM t1 ORDER BY c+1;
 SELECT a, b AS c FROM t1 ORDER BY b+1;
 drop table t1;
+
+
+#
+# Bug #3874 (function in GROUP and LEFT JOIN)
+#
+
+CREATE TABLE t1 ( a INT NOT NULL, b INT NOT NULL, UNIQUE idx (a,b) );
+INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4);
+CREATE TABLE t2 ( a INT NOT NULL, b INT NOT NULL, c INT );
+INSERT INTO t2 VALUES ( 1,10,1), (1,10,2), (1,11,1), (1,11,2), (1,2,1), (1,2,2),
+ (1,2,3);
+SELECT t2.a, t2.b, IF(t1.b IS NULL,'',c) AS c, COUNT(*) AS d FROM t2 LEFT JOIN
+t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY a, b, c;
+SELECT t2.a, t2.b, IF(t1.b IS NULL,'',c) AS c, COUNT(*) AS d FROM t2 LEFT JOIN
+t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY t1.a, t1.b, c;
+SELECT t2.a, t2.b, IF(t1.b IS NULL,'',c) AS c, COUNT(*) AS d FROM t2 LEFT JOIN
+t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY t2.a, t2.b, c;
+drop table t2, t1;