diff options
| -rw-r--r-- | mysql-test/r/func_group.result | 23 | ||||
| -rw-r--r-- | mysql-test/t/func_group.test | 29 | ||||
| -rw-r--r-- | sql/sql_select.cc | 10 |
3 files changed, 62 insertions, 0 deletions
diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index 772e432355b..a7f4c58f4af 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -1425,4 +1425,27 @@ SELECT AVG(a), CAST(AVG(a) AS DECIMAL) FROM t1; 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 diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index dbe6d3113d5..38779ac1a2f 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -926,5 +926,34 @@ SELECT AVG(a), CAST(AVG(a) AS DECIMAL) FROM t1; 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 diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 428d1709f94..2ac33c4e07f 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -390,11 +390,21 @@ inline int setup_without_group(THD *thd, Item **ref_pointer_array, { 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); |