MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Georgi Kodinov Date:November 24 2008 3:31pm
Subject:bzr commit into mysql-5.0-bugteam branch (kgeorge:2712) Bug#39656
View as plain text  
#At file:///home/kgeorge/mysql/work/B39656-5.0-bugteam/

 2712 Georgi Kodinov	2008-11-24
      Bug #39656: Behaviour different for agg functions with & without where -
      ONLY_FULL_GROUP_BY
      
      The check for non-aggregated columns in queries with aggregate function, but without
      GROUP BY was treating all the parts of the query as if they are in the SELECT list.
      Fixed by ignoring the non-aggregated fields in the WHERE clause.
modified:
  mysql-test/r/func_group.result
  mysql-test/t/func_group.test
  sql/sql_select.cc

per-file messages:
  mysql-test/r/func_group.result
    Bug #39656: test case
  mysql-test/t/func_group.test
    Bug #39656: test case
  sql/sql_select.cc
    Bug #39656: ignore the new non-aggregated column refs in a WHERE
    by saving the state so far and then adding only the new values of the other
    parts of the bitmask.
=== modified file 'mysql-test/r/func_group.result'
--- a/mysql-test/r/func_group.result	2008-03-06 15:19:24 +0000
+++ b/mysql-test/r/func_group.result	2008-11-24 15:30:24 +0000
@@ -1425,4 +1425,27 @@ SELECT AVG(a), CAST(AVG(a) AS DECIMAL) F
 AVG(a)	CAST(AVG(a) AS DECIMAL)
 15	15
 DROP TABLE t1;
+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-03-06 15:19:24 +0000
+++ b/mysql-test/t/func_group.test	2008-11-24 15:30:24 +0000
@@ -926,5 +926,34 @@ SELECT AVG(a), CAST(AVG(a) AS DECIMAL) F
 
 DROP TABLE t1;
 
+#
+# 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-03 09:50:09 +0000
+++ b/sql/sql_select.cc	2008-11-24 15:30:24 +0000
@@ -390,11 +390,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);

Thread
bzr commit into mysql-5.0-bugteam branch (kgeorge:2712) Bug#39656Georgi Kodinov24 Nov