From d795963cba42fefd57a788eb1112bfc4cc13f6d3 Mon Sep 17 00:00:00 2001 From: Georgi Kodinov Date: Mon, 24 Nov 2008 17:30:47 +0200 Subject: 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. 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. --- mysql-test/t/func_group.test | 29 +++++++++++++++++++++++++++++ 1 file changed, 29 insertions(+) (limited to 'mysql-test/t/func_group.test') 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 -- cgit v1.2.1