From: Date: November 24 2008 7:54pm
Subject: bzr commit into mysql-6.0-backup branch (horst:2745)
List-Archive: http://lists.mysql.com/commits/59710
Message-Id: <200811241854.mAOIse8r017692@hh-nb.hungers>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

#At file:///work/bzr/mysql-6.0-bug/

 2745 Horst Hunger	2008-11-24 [merge]
      due to merge.
modified:
  mysql-test/r/func_group.result
  mysql-test/t/func_group.test
  sql/sql_select.cc

=== modified file 'mysql-test/r/func_group.result'
--- a/mysql-test/r/func_group.result	2008-10-03 09:55:06 +0000
+++ b/mysql-test/r/func_group.result	2008-11-24 16:51:44 +0000
@@ -1453,4 +1453,27 @@ LIMIT 1)
 1
 DROP TABLE derived1;
 DROP TABLE D;
+CREATE TABLE t1 (a INT, b INT);
+INSERT INTO t1 VALUES (1,1), (1,2), (1,3);
+SET SQL_MODE='ONLY_FULL_GROUP_BY';
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+3
+SELECT COUNT(*) FROM t1 where a=1;
+COUNT(*)
+3
+SELECT COUNT(*),a FROM t1;
+ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
+SELECT COUNT(*) FROM t1 a JOIN t1 b ON a.a= b.a;
+COUNT(*)
+9
+SELECT COUNT(*), (SELECT count(*) FROM t1 inr WHERE inr.a = outr.a) 
+FROM t1 outr;
+ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
+SELECT COUNT(*) FROM t1 a JOIN t1 outr 
+ON a.a= (SELECT count(*) FROM t1 inr WHERE inr.a = outr.a);
+COUNT(*)
+0
+SET SQL_MODE=default;
+DROP TABLE t1;
 End of 5.0 tests

=== modified file 'mysql-test/t/func_group.test'
--- a/mysql-test/t/func_group.test	2008-10-02 14:44:49 +0000
+++ b/mysql-test/t/func_group.test	2008-11-24 16:00:09 +0000
@@ -973,5 +973,34 @@ GROUP BY int_nokey LIMIT 1;
 DROP TABLE derived1;
 DROP TABLE D;
 
+#
+# Bug #39656: Behaviour different for agg functions with & without where -
+# ONLY_FULL_GROUP_BY
+#
+
+CREATE TABLE t1 (a INT, b INT);
+INSERT INTO t1 VALUES (1,1), (1,2), (1,3);
+
+SET SQL_MODE='ONLY_FULL_GROUP_BY';
+
+SELECT COUNT(*) FROM t1;
+SELECT COUNT(*) FROM t1 where a=1;
+
+--error ER_MIX_OF_GROUP_FUNC_AND_FIELDS
+SELECT COUNT(*),a FROM t1;
+
+SELECT COUNT(*) FROM t1 a JOIN t1 b ON a.a= b.a;
+
+--error ER_MIX_OF_GROUP_FUNC_AND_FIELDS
+SELECT COUNT(*), (SELECT count(*) FROM t1 inr WHERE inr.a = outr.a) 
+  FROM t1 outr;
+
+SELECT COUNT(*) FROM t1 a JOIN t1 outr 
+  ON a.a= (SELECT count(*) FROM t1 inr WHERE inr.a = outr.a);
+
+SET SQL_MODE=default;
+DROP TABLE t1;
+
+
 ###
 --echo End of 5.0 tests

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2008-11-06 18:39:27 +0000
+++ b/sql/sql_select.cc	2008-11-24 16:51:44 +0000
@@ -437,11 +437,21 @@ inline int setup_without_group(THD *thd,
 {
   int res;
   nesting_map save_allow_sum_func=thd->lex->allow_sum_func ;
+  /* 
+    Need to save the value, so we can turn off only the new NON_AGG_FIELD
+    additions coming from the WHERE
+  */
+  uint8 saved_flag= thd->lex->current_select->full_group_by_flag;
   DBUG_ENTER("setup_without_group");
 
   thd->lex->allow_sum_func&= ~(1 << thd->lex->current_select->nest_level);
   res= setup_conds(thd, tables, leaves, conds);
 
+  /* it's not wrong to have non-aggregated columns in a WHERE */
+  if (thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY)
+    thd->lex->current_select->full_group_by_flag= saved_flag |
+      (thd->lex->current_select->full_group_by_flag & ~NON_AGG_FIELD_USED);
+
   thd->lex->allow_sum_func|= 1 << thd->lex->current_select->nest_level;
   res= res || setup_order(thd, ref_pointer_array, tables, fields, all_fields,
                           order);