Cleaned tests, added new tests.10.5-mdev-11588

Removed additional is_deterministic() checks.
Added check: subquery used in ON expression.

7 files changed, 749 insertions, 453 deletions

diff --git a/mysql-test/main/func_dep.result b/mysql-test/main/func_dep.result
index 0adf475b9e1..2e15a1a4787 100644
--- a/mysql-test/main/func_dep.result
+++ b/mysql-test/main/func_dep.result
@@ -299,6 +299,62 @@ SELECT ORD(t3.y)=ORD(t3.z),t3.y,t3.z
 FROM t3
 GROUP BY t3.y,t3.z;
 ERROR 42000: Non-grouping field 'ORD(t3.y)=ORD(t3.z)' is used in SELECT list
+SELECT LENGTH(LTRIM(t3.y)) = LENGTH(LTRIM(t3.z)),t3.y,t3.z
+FROM t3;
+LENGTH(LTRIM(t3.y)) = LENGTH(LTRIM(t3.z))	y	z
+0	aa	aa 
+1	Aa 	aa 
+SELECT LENGTH(LTRIM(t3.y)) = LENGTH(LTRIM(t3.z)),t3.y,t3.z
+FROM t3
+GROUP BY t3.y,t3.z;
+ERROR 42000: Non-grouping field 'LENGTH(LTRIM(t3.y)) = LENGTH(LTRIM(t3.z))' is used in SELECT list
+SELECT LENGTH(RTRIM(t3.y)) = LENGTH(RTRIM(t3.z)),t3.y,t3.z
+FROM t3;
+LENGTH(RTRIM(t3.y)) = LENGTH(RTRIM(t3.z))	y	z
+1	aa	aa 
+1	Aa 	aa 
+SELECT LENGTH(RTRIM(t3.y)) = LENGTH(RTRIM(t3.z)),t3.y,t3.z
+FROM t3
+GROUP BY t3.y,t3.z;
+ERROR 42000: Non-grouping field 'LENGTH(RTRIM(t3.y)) = LENGTH(RTRIM(t3.z))' is used in SELECT list
+SELECT RTRIM(LTRIM(t3.y)) = RTRIM(LTRIM(t3.z)),t3.y,t3.z
+FROM t3;
+RTRIM(LTRIM(t3.y)) = RTRIM(LTRIM(t3.z))	y	z
+1	aa	aa 
+1	Aa 	aa 
+SELECT RTRIM(LTRIM(t3.y)) = RTRIM(LTRIM(t3.z)),t3.y,t3.z
+FROM t3
+GROUP BY t3.y,t3.z;
+ERROR 42000: Non-grouping field 'RTRIM(LTRIM(t3.y)) = RTRIM(LTRIM(t3.z))' is used in SELECT list
+SELECT QUOTE(TRIM(t3.y)) = QUOTE(TRIM(t3.z)),t3.y,t3.z
+FROM t3;
+QUOTE(TRIM(t3.y)) = QUOTE(TRIM(t3.z))	y	z
+1	aa	aa 
+1	Aa 	aa 
+SELECT QUOTE(TRIM(t3.y)) = QUOTE(TRIM(t3.z)),t3.y,t3.z
+FROM t3
+GROUP BY t3.y,t3.z;
+ERROR 42000: Non-grouping field 'QUOTE(TRIM(t3.y)) = QUOTE(TRIM(t3.z))' is used in SELECT list
+SELECT RTRIM(TRIM(t3.y)) = RTRIM(TRIM(t3.z)),t3.y,t3.z
+FROM t3;
+RTRIM(TRIM(t3.y)) = RTRIM(TRIM(t3.z))	y	z
+1	aa	aa 
+1	Aa 	aa 
+SELECT RTRIM(TRIM(t3.y)) = RTRIM(TRIM(t3.z)),t3.y,t3.z
+FROM t3
+GROUP BY t3.y,t3.z;
+RTRIM(TRIM(t3.y)) = RTRIM(TRIM(t3.z))	y	z
+1	aa	aa 
+SELECT LCASE(TRIM(t3.y)) = LCASE(TRIM(t3.z)),t3.y,t3.z
+FROM t3;
+LCASE(TRIM(t3.y)) = LCASE(TRIM(t3.z))	y	z
+1	aa	aa 
+1	Aa 	aa 
+SELECT LCASE(TRIM(t3.y)) = LCASE(TRIM(t3.z)),t3.y,t3.z
+FROM t3
+GROUP BY t3.y,t3.z;
+LCASE(TRIM(t3.y)) = LCASE(TRIM(t3.z))	y	z
+1	aa	aa 
 EXPLAIN EXTENDED SELECT RAND()
 FROM t3
 GROUP BY t3.x;
@@ -316,7 +372,10 @@ Note	1003	select `test`.`t3`.`x` + rand() AS `t3.x + RAND()` from `test`.`t3` gr
 EXPLAIN EXTENDED SELECT LENGTH(t3.y + RAND())
 FROM t3
 GROUP BY t3.y;
-ERROR 42000: Non-grouping field 'LENGTH(t3.y + RAND())' is used in SELECT list
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using temporary; Using filesort
+Warnings:
+Note	1003	select octet_length(`test`.`t3`.`y` + rand()) AS `LENGTH(t3.y + RAND())` from `test`.`t3` group by `test`.`t3`.`y`
 DROP TABLE t3;
 # Forbid using functions that use FLOAT type
 CREATE TABLE t3(a INT, b DOUBLE, c FLOAT);
@@ -347,7 +406,7 @@ a	b	c
 2	2.2	2.2
 2	2.21	2.211
 DROP TABLE t3;
-# Forbid mixing INT and BIT
+# Mixing INT and BIT
 CREATE TABLE t3(a INT, b BIT(8));
 INSERT INTO t3 VALUES(2, '11111111');
 Warnings:
@@ -355,14 +414,15 @@ Warning	1264	Out of range value for column 'b' at row 1
 SELECT a + b
 FROM t3
 GROUP BY a,b;
-ERROR 42000: Non-grouping field 'a + b' is used in SELECT list
+a + b
+257
 SELECT a,b
 FROM t3
 GROUP BY a,b;
 a	b
 2	ÿ
 DROP TABLE t3;
-# Forbid using FLOAT
+# Using FLOAT
 CREATE TABLE t3(a INT, b FLOAT);
 INSERT INTO t3 VALUES(2, 2.22);
 SELECT a + b
@@ -372,7 +432,8 @@ ERROR 42000: Non-grouping field 'a + b' is used in SELECT list
 SELECT a,LENGTH(b)
 FROM t3
 GROUP BY a,b;
-ERROR 42000: Non-grouping field 'LENGTH(b)' is used in SELECT list
+a	LENGTH(b)
+2	4
 SELECT a,b
 FROM t3
 GROUP BY a,b;
@@ -483,6 +544,26 @@ FROM t1
 WHERE t1.b = 1.01 AND t1.a = t1.b
 GROUP BY t1.a;
 b
+SELECT t1.a,t1.b,t1.c
+FROM t1
+WHERE (t1.c,t1.c) = (1,1)
+GROUP BY t1.a,t1.b;
+ERROR 42000: Non-grouping field 'test.t1.c' is used in SELECT list
+SELECT t1.a,t1.b,t1.c
+FROM t1
+WHERE (t1.a,t1.c) = (1,1)
+GROUP BY t1.a,t1.b;
+ERROR 42000: Non-grouping field 'test.t1.c' is used in SELECT list
+SELECT t1.a,t1.b,t1.c
+FROM t1
+WHERE (t1.a,t1.b,t1.c) = (1,2,1)
+GROUP BY t1.a,t1.b;
+ERROR 42000: Non-grouping field 'test.t1.c' is used in SELECT list
+SELECT t1.a,t1.c
+FROM t1
+WHERE (t1.a,t1.b,t1.c) = (1,2,1)
+GROUP BY t1.a;
+ERROR 42000: Non-grouping field 'test.t1.c' is used in SELECT list
 # Non-deterministic function in WHERE
 CREATE TABLE t3 (x INT, y VARCHAR(3), z VARCHAR(5));
 INSERT INTO t3 VALUES (1, 'aa','a'),(2, 'aa ','a ');
@@ -948,6 +1029,11 @@ SELECT t3.c
 FROM t3
 WHERE t3.c = LTRIM(t3.b)
 GROUP BY t3.b;
+ERROR 42000: Non-grouping field 'test.t3.c' is used in SELECT list
+SELECT t3.c
+FROM t3
+WHERE t3.c = RTRIM(t3.b)
+GROUP BY t3.b;
 c
 Warnings:
 Warning	1292	Truncated incorrect DOUBLE value: 'aa'
@@ -1090,6 +1176,48 @@ ORDER BY LENGTH(t3.y);
 1
 1
 1
+#
+#
+# HAVING clause checking
+#
+#
+SELECT 1
+FROM t1
+GROUP BY t1.a
+HAVING t1.a > 1;
+1
+1
+SELECT t1.a
+FROM t1
+WHERE t1.a = t1.b
+GROUP BY t1.a
+HAVING t1.b > 1;
+ERROR 42S22: Unknown column 't1.b' in 'having clause'
+SELECT t1.a
+FROM t1
+WHERE t1.a = t1.b
+GROUP BY t1.a
+HAVING t1.a + t1.b > 1;
+ERROR 42S22: Unknown column 't1.b' in 'having clause'
+SELECT t1.a
+FROM t1
+WHERE t1.a + 1 = t1.b
+GROUP BY t1.a
+HAVING t1.a + t1.b > 1;
+ERROR 42S22: Unknown column 't1.b' in 'having clause'
+SELECT t3.y
+FROM t3
+GROUP BY t3.y
+HAVING TRIM(t3.y) > 1;
+y
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'aa'
+Warning	1292	Truncated incorrect DOUBLE value: 'Aa'
+SELECT t1.a
+FROM t1
+GROUP BY t1.a
+HAVING t1.b > 1;
+ERROR 42S22: Unknown column 't1.b' in 'having clause'
 DROP TABLE t3;
 #
 # Materialized derived tables/views
@@ -2438,26 +2566,6 @@ c
 NULL
 NULL
 2
-# Non-grouping left table LEFT JOIN field t1.b is used
-# in ON expression
-SELECT t1.a,t2.c
-FROM t1 LEFT JOIN t2 ON t1.a = 12 AND t1.b = t2.d;
-a	c
-1	NULL
-2	NULL
-NULL	NULL
-SELECT t1.a
-FROM t1 LEFT JOIN t2 ON t1.a = 12 AND t1.b = t2.d
-GROUP BY t2.c;
-ERROR 42000: Non-grouping field 'test.t1.a' is used in SELECT list
-SELECT t1.b
-FROM t1 LEFT JOIN t2 ON t1.a = t1.b
-GROUP BY t1.a;
-ERROR 42000: Non-grouping field 'test.t1.b' is used in SELECT list
-SELECT t1.b
-FROM t1 LEFT JOIN t2 ON t1.b = t2.c
-GROUP BY t1.a;
-ERROR 42000: Non-grouping field 'test.t1.b' is used in SELECT list
 SELECT t3.c
 FROM t1 LEFT JOIN (
 t2 LEFT JOIN t2 AS t3 ON (t3.c = 2) AND (t2.c = 1)
@@ -2466,25 +2574,6 @@ GROUP BY t2.c;
 c
 NULL
 NULL
-SELECT t2.c
-FROM t1 LEFT JOIN t2 ON (t2.c = 2) AND (t1.b = 2)
-GROUP BY t1.a;
-ERROR 42000: Non-grouping field 'test.t2.c' is used in SELECT list
-# ON expression equality predicate can't be used to state
-# that left LEFT JOIN field t1.a is functionally dependent field
-SELECT t1.a
-FROM t1 LEFT JOIN t2 ON t1.a = 12
-GROUP BY t2.c;
-ERROR 42000: Non-grouping field 'test.t1.a' is used in SELECT list
-# No left table field (t2 field) is used in the ON expression
-# of the most inner LEFT JOIN
-SELECT t3.c
-FROM t1
-LEFT JOIN (
-t2 LEFT JOIN t2 AS t3 ON (t3.c = 2)
-) ON (t1.a = 2)
-GROUP BY t1.a;
-ERROR 42000: Non-grouping field 'test.t3.c' is used in SELECT list
 SELECT t3.c
 FROM t1 LEFT JOIN (
 t2 LEFT JOIN t2 AS t3 ON (t3.c = 2) AND (t2.c = 1)
@@ -2514,26 +2603,6 @@ a	d	c	d
 NULL	NULL	NULL	NULL
 1	1	2	1
 2	NULL	NULL	NULL
-SELECT t1.a,t2.d,t3.c
-FROM t1 LEFT JOIN (
-t2 LEFT JOIN t2 AS t3 ON (t3.c = 2) AND
-(t2.c = t2.d)
-) ON (t1.a = t2.d)
-GROUP BY t1.a;
-ERROR 42000: Non-grouping field 'test.t3.c' is used in SELECT list
-SELECT t2.d,t3.c
-FROM t1 LEFT JOIN (
-t2 LEFT JOIN t2 AS t3 ON (t3.c = 2) AND
-(t2.c = t2.d)
-) ON 1 > 0
-GROUP BY t2.d;
-ERROR 42000: Non-grouping field 'test.t3.c' is used in SELECT list
-SELECT t2.d,t3.c
-FROM t1 LEFT JOIN (
-t2 LEFT JOIN t2 AS t3 ON (t3.c = 2)
-) ON 1 > 0
-GROUP BY t2.d;
-ERROR 42000: Non-grouping field 'test.t3.c' is used in SELECT list
 SELECT t3.c
 FROM t1 JOIN (
 t2 LEFT JOIN t2 AS t3 ON (t3.c = 2)
@@ -2541,53 +2610,6 @@ t2 LEFT JOIN t2 AS t3 ON (t3.c = 2)
 GROUP BY "";
 c
 2
-# Is transformed into:
-# "select t3.c AS c from t1 join t2 left join t2 t3 on
-#  (t3.c = 2) where t1.a = 1 group by t2.c"
-SELECT t3.c
-FROM t1 JOIN (
-t2 LEFT JOIN t2 AS t3 ON (t3.c = 2)
-) ON (t1.a = 1)
-GROUP BY t2.c;
-c
-2
-2
-SELECT t4.a
-FROM t1 LEFT JOIN (
-t2 LEFT JOIN (
-t2 AS t3
-LEFT JOIN t1 AS t4 ON (t4.a = 1)
-) ON (t2.c = 1)
-) ON (t1.a = 1)
-GROUP BY t1.a;
-ERROR 42000: Non-grouping field 'test.t4.a' is used in SELECT list
-SELECT t3.c
-FROM t1 LEFT JOIN (
-t2 LEFT JOIN (
-t2 AS t3
-LEFT JOIN t1 AS t4 ON (t4.a = 1)
-) ON (t3.c = 1)
-) ON (t1.a = 1)
-GROUP BY t1.a;
-ERROR 42000: Non-grouping field 'test.t3.c' is used in SELECT list
-SELECT t4.a
-FROM t1 LEFT JOIN (
-t2 LEFT JOIN (
-t2 AS t3
-LEFT JOIN t1 AS t4 ON (t4.a = 1)
-) ON (t3.c = 1)
-) ON (t1.a = 1)
-GROUP BY t3.c;
-ERROR 42000: Non-grouping field 'test.t4.a' is used in SELECT list
-SELECT t4.a
-FROM t1 LEFT JOIN (
-t2 LEFT JOIN (
-t2 AS t3
-LEFT JOIN t1 AS t4 ON (t4.a = 1)
-) ON (t2.c = t2.d)
-) ON (t1.a = t2.d)
-GROUP BY t3.c;
-ERROR 42000: Non-grouping field 'test.t4.a' is used in SELECT list
 SELECT t4.a
 FROM t1 LEFT JOIN (
 t2 LEFT JOIN (
@@ -2622,89 +2644,27 @@ c
 NULL
 2
 NULL
+# Is transformed into:
+# "select t3.c AS c from t1 join t2 left join t2 t3 on
+#  (t3.c = 2) where t1.a = 1 group by t2.c"
 SELECT t3.c
-FROM t1 LEFT JOIN (
-t2 JOIN
-t2 AS t3 ON (t3.c = 2)
-) ON (t1.a = 1)
-GROUP BY "";
-ERROR 42000: Non-grouping field 'test.t3.c' is used in SELECT list
-SELECT t3.c
-FROM t1 LEFT JOIN (
-t2 JOIN
-t2 AS t3 ON (t3.c = 2) AND (t2.c = t2.c)
+FROM t1 JOIN (
+t2 LEFT JOIN t2 AS t3 ON (t3.c = 2)
 ) ON (t1.a = 1)
-GROUP BY t1.a;
+GROUP BY t2.c;
 c
-NULL
 2
-NULL
+2
 SELECT t3.c
 FROM t1 LEFT JOIN (
 t2 JOIN
 t2 AS t3 ON (t3.c = 2) AND (t2.c = t2.c)
 ) ON (t1.a = 1)
-GROUP BY "";
-ERROR 42000: Non-grouping field 'test.t3.c' is used in SELECT list
-SELECT t3.c
-FROM t1 LEFT JOIN (
-t2 JOIN
-t2 AS t3 ON (t3.c = 2) AND (t2.c = t2.c)
-) ON 1 > 0
-GROUP BY "";
-ERROR 42000: Non-grouping field 'test.t3.c' is used in SELECT list
-SELECT t4.c
-FROM t1 LEFT JOIN (
-t2 LEFT JOIN (
-t1 AS t3
-LEFT JOIN t2 AS t4 ON (t4.c = 2) AND
-(t3.a > 0)
-) ON (t2.d = t3.a) AND (t2.d > 1)
-) ON t2.d = t1.a
 GROUP BY t1.a;
 c
 NULL
+2
 NULL
-NULL
-SELECT t4.c
-FROM t1 LEFT JOIN (
-t2 LEFT JOIN (
-t1 AS t3
-LEFT JOIN t2 AS t4 ON t4.c = 2
-) ON (t2.d = t3.a) AND (t2.d > 1)
-) ON t2.d = t1.a
-GROUP BY t1.a;
-ERROR 42000: Non-grouping field 'test.t4.c' is used in SELECT list
-SELECT t4.c
-FROM t1 LEFT JOIN (
-t2 LEFT JOIN (
-t1 AS t3
-LEFT JOIN t2 AS t4 ON (t4.c = 2) AND
-(t3.a > 1)
-) ON t2.d = t3.a AND t2.c > 1
-) ON t2.d = t1.a
-GROUP BY t1.a;
-ERROR 42000: Non-grouping field 'test.t4.c' is used in SELECT list
-SELECT t4.c
-FROM t1 LEFT JOIN (
-t2 LEFT JOIN (
-t1 AS t3
-LEFT JOIN t2 AS t4 ON (t4.c = 2) AND
-(t3.a > 1)
-) ON (t2.d = t3.a)
-) ON (t2.d = t1.a) AND (t1.b > 2)
-GROUP BY t1.a;
-ERROR 42000: Non-grouping field 'test.t4.c' is used in SELECT list
-SELECT t4.c
-FROM t1 LEFT JOIN (
-t2 LEFT JOIN (
-t1 AS t3
-LEFT JOIN t2 AS t4 ON (t4.c = t3.a) AND
-(t3.a > 1)
-) ON t2.d = t3.a
-) ON t2.d = t1.a AND t1.b > 2
-GROUP BY t1.a;
-ERROR 42000: Non-grouping field 'test.t4.c' is used in SELECT list
 SELECT t4.c
 FROM t1 LEFT JOIN (
 t2 LEFT JOIN (
@@ -2780,20 +2740,19 @@ a
 NULL
 NULL
 NULL
-SELECT t3.a
-FROM t1 LEFT JOIN (
-t2 LEFT JOIN
-t1 AS t3 ON (t3.a = 1) AND (t2.d > 1)
-) ON (t2.d = t1.a) AND (t1.b > 1)
-GROUP BY t1.a;
-ERROR 42000: Non-grouping field 'test.t3.a' is used in SELECT list
-SELECT t3.a
+SELECT t4.c
 FROM t1 LEFT JOIN (
-t2 LEFT JOIN
-t1 AS t3 ON (t2.d = t3.a) AND (t2.d > 1)
-) ON (t2.d = t1.a) AND (t1.b > 1)
+t2 LEFT JOIN (
+t1 AS t3
+LEFT JOIN t2 AS t4 ON (t4.c = 2) AND
+(t3.a > 0)
+) ON (t2.d = t3.a) AND (t2.d > 1)
+) ON t2.d = t1.a
 GROUP BY t1.a;
-ERROR 42000: Non-grouping field 'test.t3.a' is used in SELECT list
+c
+NULL
+NULL
+NULL
 SELECT t4.c
 FROM t1 LEFT JOIN (
 t2 LEFT JOIN (
@@ -2837,33 +2796,12 @@ SELECT t3.a
 FROM t1 LEFT JOIN (
 t2
 JOIN t1 AS t3 ON (t2.c = t3.a) AND (t2.d > 1)
-) ON (t1.a > 2)
-GROUP BY t2.c;
-ERROR 42000: Non-grouping field 'test.t3.a' is used in SELECT list
-SELECT t3.a
-FROM t1 LEFT JOIN (
-t2
-JOIN t1 AS t3 ON (t2.c = t3.a) AND (t2.d > 1)
 ) ON (t1.a = t2.c)
 GROUP BY t1.a;
 a
 NULL
 NULL
 2
-SELECT t2.c
-FROM t1 LEFT JOIN (
-t2
-LEFT JOIN t1 AS t3 ON (t2.c = t3.a) AND (t2.d > 1)
-) ON (t1.b > 2) and (t1.a = t2.c)
-GROUP BY t2.d;
-ERROR 42000: Non-grouping field 'test.t2.c' is used in SELECT list
-SELECT t3.a
-FROM t1 LEFT JOIN (
-t2
-LEFT JOIN t1 AS t3 ON (t2.c = t3.a) AND (t2.d > 1)
-) ON (t1.a = t2.c)
-GROUP BY t1.a;
-ERROR 42000: Non-grouping field 'test.t3.a' is used in SELECT list
 # Is transformed into
 # "select t1.b AS b from t1 join t2 where t1.a = t2.c
 #  and t1.b = t2.c and t2.d = t2.c group by t1.a"
@@ -2880,6 +2818,196 @@ FROM t1 LEFT JOIN t2 ON (t2.d = t2.c AND t1.b = t2.d)
 WHERE t1.a = t2.c
 GROUP BY t1.a;
 b
+# Non-grouping left table LEFT JOIN field t1.b is used
+# in ON expression
+SELECT t1.a,t2.c
+FROM t1 LEFT JOIN t2 ON t1.a = 12 AND t1.b = t2.d;
+a	c
+1	NULL
+2	NULL
+NULL	NULL
+SELECT t1.a
+FROM t1 LEFT JOIN t2 ON t1.a = 12 AND t1.b = t2.d
+GROUP BY t2.c;
+ERROR 42000: Non-grouping field 'test.t1.a' is used in SELECT list
+SELECT t1.b
+FROM t1 LEFT JOIN t2 ON t1.a = t1.b
+GROUP BY t1.a;
+ERROR 42000: Non-grouping field 'test.t1.b' is used in SELECT list
+SELECT t1.b
+FROM t1 LEFT JOIN t2 ON t1.b = t2.c
+GROUP BY t1.a;
+ERROR 42000: Non-grouping field 'test.t1.b' is used in SELECT list
+SELECT t2.c
+FROM t1 LEFT JOIN t2 ON (t2.c = 2) AND (t1.b = 2)
+GROUP BY t1.a;
+ERROR 42000: Non-grouping field 'test.t2.c' is used in SELECT list
+# ON expression equality predicate can't be used to state
+# that left LEFT JOIN field t1.a is functionally dependent field
+SELECT t1.a
+FROM t1 LEFT JOIN t2 ON t1.a = 12
+GROUP BY t2.c;
+ERROR 42000: Non-grouping field 'test.t1.a' is used in SELECT list
+# No left table field (t2 field) is used in the ON expression
+# of the most inner LEFT JOIN
+SELECT t3.c
+FROM t1
+LEFT JOIN (
+t2 LEFT JOIN t2 AS t3 ON (t3.c = 2)
+) ON (t1.a = 2)
+GROUP BY t1.a;
+ERROR 42000: Non-grouping field 'test.t3.c' is used in SELECT list
+SELECT t1.a,t2.d,t3.c
+FROM t1 LEFT JOIN (
+t2 LEFT JOIN t2 AS t3 ON (t3.c = 2) AND
+(t2.c = t2.d)
+) ON (t1.a = t2.d)
+GROUP BY t1.a;
+ERROR 42000: Non-grouping field 'test.t3.c' is used in SELECT list
+SELECT t2.d,t3.c
+FROM t1 LEFT JOIN (
+t2 LEFT JOIN t2 AS t3 ON (t3.c = 2) AND
+(t2.c = t2.d)
+) ON 1 > 0
+GROUP BY t2.d;
+ERROR 42000: Non-grouping field 'test.t3.c' is used in SELECT list
+SELECT t2.d,t3.c
+FROM t1 LEFT JOIN (
+t2 LEFT JOIN t2 AS t3 ON (t3.c = 2)
+) ON 1 > 0
+GROUP BY t2.d;
+ERROR 42000: Non-grouping field 'test.t3.c' is used in SELECT list
+SELECT t4.a
+FROM t1 LEFT JOIN (
+t2 LEFT JOIN (
+t2 AS t3
+LEFT JOIN t1 AS t4 ON (t4.a = 1)
+) ON (t2.c = 1)
+) ON (t1.a = 1)
+GROUP BY t1.a;
+ERROR 42000: Non-grouping field 'test.t4.a' is used in SELECT list
+SELECT t3.c
+FROM t1 LEFT JOIN (
+t2 LEFT JOIN (
+t2 AS t3
+LEFT JOIN t1 AS t4 ON (t4.a = 1)
+) ON (t3.c = 1)
+) ON (t1.a = 1)
+GROUP BY t1.a;
+ERROR 42000: Non-grouping field 'test.t3.c' is used in SELECT list
+SELECT t4.a
+FROM t1 LEFT JOIN (
+t2 LEFT JOIN (
+t2 AS t3
+LEFT JOIN t1 AS t4 ON (t4.a = 1)
+) ON (t3.c = 1)
+) ON (t1.a = 1)
+GROUP BY t3.c;
+ERROR 42000: Non-grouping field 'test.t4.a' is used in SELECT list
+SELECT t4.a
+FROM t1 LEFT JOIN (
+t2 LEFT JOIN (
+t2 AS t3
+LEFT JOIN t1 AS t4 ON (t4.a = 1)
+) ON (t2.c = t2.d)
+) ON (t1.a = t2.d)
+GROUP BY t3.c;
+ERROR 42000: Non-grouping field 'test.t4.a' is used in SELECT list
+SELECT t3.c
+FROM t1 LEFT JOIN (
+t2 JOIN
+t2 AS t3 ON (t3.c = 2)
+) ON (t1.a = 1)
+GROUP BY "";
+ERROR 42000: Non-grouping field 'test.t3.c' is used in SELECT list
+SELECT t3.c
+FROM t1 LEFT JOIN (
+t2 JOIN
+t2 AS t3 ON (t3.c = 2) AND (t2.c = t2.c)
+) ON (t1.a = 1)
+GROUP BY "";
+ERROR 42000: Non-grouping field 'test.t3.c' is used in SELECT list
+SELECT t3.c
+FROM t1 LEFT JOIN (
+t2 JOIN
+t2 AS t3 ON (t3.c = 2) AND (t2.c = t2.c)
+) ON 1 > 0
+GROUP BY "";
+ERROR 42000: Non-grouping field 'test.t3.c' is used in SELECT list
+SELECT t4.c
+FROM t1 LEFT JOIN (
+t2 LEFT JOIN (
+t1 AS t3
+LEFT JOIN t2 AS t4 ON t4.c = 2
+) ON (t2.d = t3.a) AND (t2.d > 1)
+) ON t2.d = t1.a
+GROUP BY t1.a;
+ERROR 42000: Non-grouping field 'test.t4.c' is used in SELECT list
+SELECT t4.c
+FROM t1 LEFT JOIN (
+t2 LEFT JOIN (
+t1 AS t3
+LEFT JOIN t2 AS t4 ON (t4.c = 2) AND
+(t3.a > 1)
+) ON t2.d = t3.a AND t2.c > 1
+) ON t2.d = t1.a
+GROUP BY t1.a;
+ERROR 42000: Non-grouping field 'test.t4.c' is used in SELECT list
+SELECT t4.c
+FROM t1 LEFT JOIN (
+t2 LEFT JOIN (
+t1 AS t3
+LEFT JOIN t2 AS t4 ON (t4.c = 2) AND
+(t3.a > 1)
+) ON (t2.d = t3.a)
+) ON (t2.d = t1.a) AND (t1.b > 2)
+GROUP BY t1.a;
+ERROR 42000: Non-grouping field 'test.t4.c' is used in SELECT list
+SELECT t4.c
+FROM t1 LEFT JOIN (
+t2 LEFT JOIN (
+t1 AS t3
+LEFT JOIN t2 AS t4 ON (t4.c = t3.a) AND
+(t3.a > 1)
+) ON t2.d = t3.a
+) ON t2.d = t1.a AND t1.b > 2
+GROUP BY t1.a;
+ERROR 42000: Non-grouping field 'test.t4.c' is used in SELECT list
+SELECT t3.a
+FROM t1 LEFT JOIN (
+t2 LEFT JOIN
+t1 AS t3 ON (t3.a = 1) AND (t2.d > 1)
+) ON (t2.d = t1.a) AND (t1.b > 1)
+GROUP BY t1.a;
+ERROR 42000: Non-grouping field 'test.t3.a' is used in SELECT list
+SELECT t3.a
+FROM t1 LEFT JOIN (
+t2 LEFT JOIN
+t1 AS t3 ON (t2.d = t3.a) AND (t2.d > 1)
+) ON (t2.d = t1.a) AND (t1.b > 1)
+GROUP BY t1.a;
+ERROR 42000: Non-grouping field 'test.t3.a' is used in SELECT list
+SELECT t3.a
+FROM t1 LEFT JOIN (
+t2
+JOIN t1 AS t3 ON (t2.c = t3.a) AND (t2.d > 1)
+) ON (t1.a > 2)
+GROUP BY t2.c;
+ERROR 42000: Non-grouping field 'test.t3.a' is used in SELECT list
+SELECT t2.c
+FROM t1 LEFT JOIN (
+t2
+LEFT JOIN t1 AS t3 ON (t2.c = t3.a) AND (t2.d > 1)
+) ON (t1.b > 2) and (t1.a = t2.c)
+GROUP BY t2.d;
+ERROR 42000: Non-grouping field 'test.t2.c' is used in SELECT list
+SELECT t3.a
+FROM t1 LEFT JOIN (
+t2
+LEFT JOIN t1 AS t3 ON (t2.c = t3.a) AND (t2.d > 1)
+) ON (t1.a = t2.c)
+GROUP BY t1.a;
+ERROR 42000: Non-grouping field 'test.t3.a' is used in SELECT list
 #
 # Usage of non-deterministic functions
 #
@@ -2937,6 +3065,28 @@ FROM t1 LEFT JOIN t2 ON t1.a = t2.c
 WHERE t1.a = rand()
 GROUP BY t1.a;
 a	c
+SELECT t1.a
+FROM t1 LEFT JOIN t2 ON (t1.a = t2.c) AND (SELECT t1.b)
+GROUP BY t1.a;
+a
+NULL
+1
+2
+SELECT t2.c
+FROM t1 LEFT JOIN t2 ON (t1.a = t2.c) AND (SELECT t1.b)
+GROUP BY t1.a;
+ERROR 42000: Non-grouping field 'test.t2.c' is used in SELECT list
+SELECT t2.c
+FROM t1 LEFT JOIN t2 ON (t1.a = t2.c) AND t1.a
+GROUP BY t1.a;
+c
+NULL
+NULL
+2
+SELECT t2.c
+FROM t1 LEFT JOIN t2 ON (t1.a = t2.c) AND (SELECT t1.a)
+GROUP BY t1.a;
+ERROR 42000: Non-grouping field 'test.t2.c' is used in SELECT list
 DROP TABLE t3;
 #
 #  Mergeable derived table or view on the right side
@@ -3085,5 +3235,23 @@ LEFT OUTER JOIN t2 ON t1.a = t2.c
 LEFT OUTER JOIN t1 AS t3 ON t1.a = t3.b
 GROUP BY t3.b;
 ERROR 42000: Non-grouping field 'test.t2.c' is used in SELECT list
+# Aggregated functions used if no GROUP BY is used
+CREATE VIEW v1 AS (SELECT * FROM t1);
+SELECT MAX(v1.a)
+FROM v1;
+MAX(v1.a)
+2
+SELECT MAX(v1.a),SUM(v1.a)
+FROM v1;
+MAX(v1.a)	SUM(v1.a)
+2	3
+# different error messages, should be the same
+SELECT MAX(t1.a),t1.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 MAX(v1.a),v1.b
+FROM v1;
+ERROR 42000: Non-grouping field 'test.t1.b' is used in SELECT list
+DROP VIEW v1;
 DROP TABLE t1,t2;
 SET SQL_MODE=' ';
diff --git a/mysql-test/main/func_dep.test b/mysql-test/main/func_dep.test
index 417e192bf16..864f3dcc1ad 100644
--- a/mysql-test/main/func_dep.test
+++ b/mysql-test/main/func_dep.test
@@ -208,6 +208,46 @@ SELECT ORD(t3.y)=ORD(t3.z),t3.y,t3.z
 FROM t3
 GROUP BY t3.y,t3.z;
 
+SELECT LENGTH(LTRIM(t3.y)) = LENGTH(LTRIM(t3.z)),t3.y,t3.z
+FROM t3;
+--error ER_NON_GROUPING_FIELD_USED
+SELECT LENGTH(LTRIM(t3.y)) = LENGTH(LTRIM(t3.z)),t3.y,t3.z
+FROM t3
+GROUP BY t3.y,t3.z;
+
+SELECT LENGTH(RTRIM(t3.y)) = LENGTH(RTRIM(t3.z)),t3.y,t3.z
+FROM t3;
+--error ER_NON_GROUPING_FIELD_USED
+SELECT LENGTH(RTRIM(t3.y)) = LENGTH(RTRIM(t3.z)),t3.y,t3.z
+FROM t3
+GROUP BY t3.y,t3.z;
+
+SELECT RTRIM(LTRIM(t3.y)) = RTRIM(LTRIM(t3.z)),t3.y,t3.z
+FROM t3;
+--error ER_NON_GROUPING_FIELD_USED
+SELECT RTRIM(LTRIM(t3.y)) = RTRIM(LTRIM(t3.z)),t3.y,t3.z
+FROM t3
+GROUP BY t3.y,t3.z;
+
+SELECT QUOTE(TRIM(t3.y)) = QUOTE(TRIM(t3.z)),t3.y,t3.z
+FROM t3;
+--error ER_NON_GROUPING_FIELD_USED
+SELECT QUOTE(TRIM(t3.y)) = QUOTE(TRIM(t3.z)),t3.y,t3.z
+FROM t3
+GROUP BY t3.y,t3.z;
+
+SELECT RTRIM(TRIM(t3.y)) = RTRIM(TRIM(t3.z)),t3.y,t3.z
+FROM t3;
+SELECT RTRIM(TRIM(t3.y)) = RTRIM(TRIM(t3.z)),t3.y,t3.z
+FROM t3
+GROUP BY t3.y,t3.z;
+
+SELECT LCASE(TRIM(t3.y)) = LCASE(TRIM(t3.z)),t3.y,t3.z
+FROM t3;
+SELECT LCASE(TRIM(t3.y)) = LCASE(TRIM(t3.z)),t3.y,t3.z
+FROM t3
+GROUP BY t3.y,t3.z;
+
 EXPLAIN EXTENDED SELECT RAND()
 FROM t3
 GROUP BY t3.x;
@@ -216,7 +256,6 @@ EXPLAIN EXTENDED SELECT t3.x + RAND()
 FROM t3
 GROUP BY t3.x;
 
---error ER_NON_GROUPING_FIELD_USED
 EXPLAIN EXTENDED SELECT LENGTH(t3.y + RAND())
 FROM t3
 GROUP BY t3.y;
@@ -251,11 +290,10 @@ GROUP BY t3.a,t3.b,t3.c;
 
 DROP TABLE t3;
 
---echo # Forbid mixing INT and BIT
+--echo # Mixing INT and BIT
 CREATE TABLE t3(a INT, b BIT(8));
 INSERT INTO t3 VALUES(2, '11111111');
 
---error ER_NON_GROUPING_FIELD_USED
 SELECT a + b
 FROM t3
 GROUP BY a,b;
@@ -266,7 +304,7 @@ GROUP BY a,b;
 
 DROP TABLE t3;
 
---echo # Forbid using FLOAT
+--echo # Using FLOAT
 CREATE TABLE t3(a INT, b FLOAT);
 INSERT INTO t3 VALUES(2, 2.22);
 
@@ -275,7 +313,6 @@ SELECT a + b
 FROM t3
 GROUP BY a,b;
 
---error ER_NON_GROUPING_FIELD_USED
 SELECT a,LENGTH(b)
 FROM t3
 GROUP BY a,b;
@@ -384,6 +421,30 @@ FROM t1
 WHERE t1.b = 1.01 AND t1.a = t1.b
 GROUP BY t1.a;
 
+--error ER_NON_GROUPING_FIELD_USED
+SELECT t1.a,t1.b,t1.c
+FROM t1
+WHERE (t1.c,t1.c) = (1,1)
+GROUP BY t1.a,t1.b;
+
+--error ER_NON_GROUPING_FIELD_USED
+SELECT t1.a,t1.b,t1.c
+FROM t1
+WHERE (t1.a,t1.c) = (1,1)
+GROUP BY t1.a,t1.b;
+
+--error ER_NON_GROUPING_FIELD_USED
+SELECT t1.a,t1.b,t1.c
+FROM t1
+WHERE (t1.a,t1.b,t1.c) = (1,2,1)
+GROUP BY t1.a,t1.b;
+
+--error ER_NON_GROUPING_FIELD_USED
+SELECT t1.a,t1.c
+FROM t1
+WHERE (t1.a,t1.b,t1.c) = (1,2,1)
+GROUP BY t1.a;
+
 --echo # Non-deterministic function in WHERE
 
 CREATE TABLE t3 (x INT, y VARCHAR(3), z VARCHAR(5));
@@ -809,11 +870,17 @@ FROM t3
 WHERE t3.a = TRIM(t3.b)
 GROUP BY t3.b;
 
+--error ER_NON_GROUPING_FIELD_USED
 SELECT t3.c
 FROM t3
 WHERE t3.c = LTRIM(t3.b)
 GROUP BY t3.b;
 
+SELECT t3.c
+FROM t3
+WHERE t3.c = RTRIM(t3.b)
+GROUP BY t3.b;
+
 SELECT t3.a
 FROM t3
 WHERE t3.a = SOUNDEX(t3.b)
@@ -940,6 +1007,49 @@ FROM t3
 GROUP BY LENGTH(t3.y)
 ORDER BY LENGTH(t3.y);
 
+--echo #
+--echo #
+--echo # HAVING clause checking
+--echo #
+--echo #
+
+SELECT 1
+FROM t1
+GROUP BY t1.a
+HAVING t1.a > 1;
+
+--error 1054
+SELECT t1.a
+FROM t1
+WHERE t1.a = t1.b
+GROUP BY t1.a
+HAVING t1.b > 1;
+
+--error 1054
+SELECT t1.a
+FROM t1
+WHERE t1.a = t1.b
+GROUP BY t1.a
+HAVING t1.a + t1.b > 1;
+
+--error 1054
+SELECT t1.a
+FROM t1
+WHERE t1.a + 1 = t1.b
+GROUP BY t1.a
+HAVING t1.a + t1.b > 1;
+
+SELECT t3.y
+FROM t3
+GROUP BY t3.y
+HAVING TRIM(t3.y) > 1;
+
+--error 1054
+SELECT t1.a
+FROM t1
+GROUP BY t1.a
+HAVING t1.b > 1;
+
 DROP TABLE t3;
 
 --echo #
@@ -2150,6 +2260,199 @@ SELECT t2.c
 FROM t1 LEFT JOIN t2 ON (t2.c = 2) AND (t1.a = 2)
 GROUP BY t1.a;
 
+SELECT t3.c
+FROM t1 LEFT JOIN (
+                   t2 LEFT JOIN t2 AS t3 ON (t3.c = 2) AND (t2.c = 1)
+                  ) ON (t1.a = t2.d)
+GROUP BY t2.c;
+
+SELECT t3.c
+FROM t1 LEFT JOIN (
+                   t2 LEFT JOIN t2 AS t3 ON (t3.c = 2) AND (t2.c = 1)
+                  ) ON (t1.a = t2.c)
+GROUP BY t1.a;
+
+SELECT t3.c
+FROM t1 LEFT JOIN (
+                   t2 LEFT JOIN t2 AS t3 ON (t3.c = 2) AND (t2.c > 1)
+                  ) ON (t1.a = t2.c)
+GROUP BY t1.a;
+
+SELECT t1.a,t2.d,t3.c,t3.d
+FROM t1 LEFT JOIN (
+                   t2 LEFT JOIN t2 AS t3 ON (t3.c = 2) AND
+                                            (t2.d = 1) AND
+                                            (t3.d = t2.d)
+                  ) ON (t1.a = t2.d)
+GROUP BY t1.a;
+
+SELECT t3.c
+FROM t1 JOIN (
+              t2 LEFT JOIN t2 AS t3 ON (t3.c = 2)
+             ) ON (t1.a = 1)
+GROUP BY "";
+
+SELECT t4.a
+FROM t1 LEFT JOIN (
+                   t2 LEFT JOIN (
+                                 t2 AS t3
+                                 LEFT JOIN t1 AS t4 ON (t4.a = 1)
+                                ) ON (t2.c = t4.b)
+                  ) ON (t1.a = t2.d)
+GROUP BY t2.c;
+
+SELECT t4.a
+FROM t1 LEFT JOIN (
+                   t2 LEFT JOIN (
+                                 t2 AS t3
+                                 LEFT JOIN t1 AS t4 ON (t4.a = 1) AND
+                                                       (t3.c = t3.c)
+                                ) ON (t2.c = t3.c)
+                  ) ON (t1.a = t2.c)
+GROUP BY t1.a;
+
+SELECT t3.c
+FROM t1 LEFT JOIN (
+                   t2
+                   JOIN t2 AS t3 ON (t3.c = 2)
+                  ) ON (t1.a = 1)
+GROUP BY t1.a;
+
+--echo # Is transformed into:
+--echo # "select t3.c AS c from t1 join t2 left join t2 t3 on
+--echo #  (t3.c = 2) where t1.a = 1 group by t2.c"
+SELECT t3.c
+FROM t1 JOIN (
+              t2 LEFT JOIN t2 AS t3 ON (t3.c = 2)
+             ) ON (t1.a = 1)
+GROUP BY t2.c;
+
+SELECT t3.c
+FROM t1 LEFT JOIN (
+                   t2 JOIN
+                   t2 AS t3 ON (t3.c = 2) AND (t2.c = t2.c)
+                  ) ON (t1.a = 1)
+GROUP BY t1.a;
+SELECT t4.c
+FROM t1 LEFT JOIN (
+                   t2 LEFT JOIN (
+                                 t1 AS t3
+                                 LEFT JOIN t2 AS t4 ON (t4.c = t3.a) AND
+                                                       (t3.a > 1)
+                                ) ON (t2.d = t3.a)
+                  ) ON 1 > 0
+GROUP BY t2.d;
+
+SELECT t4.c
+FROM t1 LEFT JOIN (
+                   t2 LEFT JOIN (
+                                 t1 AS t3
+                                 LEFT JOIN t2 AS t4 ON (t4.c = t3.a) AND
+                                                       (t3.a > 1)
+                                ) ON 1 > 0
+                  ) ON 1 > 0
+GROUP BY t3.a;
+
+SELECT t4.c
+FROM t1 LEFT JOIN (
+                   t2 LEFT JOIN (
+                                 t1 AS t3
+                                 LEFT JOIN t2 AS t4 ON (t4.c = t3.a) AND
+                                                       (t3.a > 1)
+                                ) ON 1 > 0
+                  ) ON (t1.a = t2.d)
+GROUP BY t3.a;
+
+SELECT t4.c
+FROM t1 LEFT JOIN (
+                   t2 LEFT JOIN (
+                                 t1 AS t3
+                                 LEFT JOIN t2 AS t4 ON (t4.c = t3.a) AND
+                                                       (t3.a > 1)
+                                ) ON 1 > 0
+                  ) ON (t1.a = 1)
+GROUP BY t3.a;
+
+SELECT t4.c
+FROM t1 LEFT JOIN (
+                   t2 LEFT JOIN (
+                                 t1 AS t3
+                                 LEFT JOIN t2 AS t4 ON (t4.c = 2) AND
+                                                       (t3.a > 1)
+                                ) ON t2.d = t3.a
+                  ) ON (t2.d = t1.a) AND (t2.c > 2)
+GROUP BY t1.a;
+
+SELECT t3.a
+FROM t1 LEFT JOIN (
+                   t2 LEFT JOIN
+                   t1 AS t3 ON (t2.d = t3.a) AND (t2.d > 1)
+                  ) ON (t2.d = t1.a) AND (t2.c > 1)
+GROUP BY t1.a;
+
+SELECT t4.c
+FROM t1 LEFT JOIN (
+                   t2 LEFT JOIN (
+                                 t1 AS t3
+                                 LEFT JOIN t2 AS t4 ON (t4.c = 2) AND
+                                                       (t3.a > 0)
+                                ) ON (t2.d = t3.a) AND (t2.d > 1)
+                  ) ON t2.d = t1.a
+GROUP BY t1.a;
+
+SELECT t4.c
+FROM t1 LEFT JOIN (
+                   t2 LEFT JOIN (
+                                 t1 AS t3
+                                 LEFT JOIN t2 AS t4 ON (t4.c = t3.a) AND
+                                                       (t3.a > 1)
+                                ) ON 1 > 0
+                  ) ON (t1.a = 1)
+GROUP BY t3.a;
+
+SELECT t4.c
+FROM t1 LEFT JOIN (
+                   t2 LEFT JOIN (
+                                 t1 AS t3
+                                 LEFT JOIN t2 AS t4 ON (t4.c = t3.a) AND
+                                                       (t3.a > 1)
+                                ) ON (t2.c = 2)
+                  ) ON (t1.a = 1)
+GROUP BY t3.a;
+
+SELECT t4.c
+FROM t1 LEFT JOIN (
+                   t2 LEFT JOIN (
+                                 t1 AS t3
+                                 LEFT JOIN t2 AS t4 ON (t4.c = t3.a) AND
+                                                       (t3.a > 1)
+                                ) ON (t2.c = 2)
+                  ) ON 1 > 0
+GROUP BY t3.a;
+
+SELECT t3.a
+FROM t1 LEFT JOIN (
+                   t2
+                   JOIN t1 AS t3 ON (t2.c = t3.a) AND (t2.d > 1)
+                  ) ON (t1.a = t2.c)
+GROUP BY t1.a;
+
+--echo # Is transformed into
+--echo # "select t1.b AS b from t1 join t2 where t1.a = t2.c
+--echo #  and t1.b = t2.c and t2.d = t2.c group by t1.a"
+SELECT t1.b
+FROM t1 LEFT JOIN t2 ON (t2.d = t2.c)
+WHERE t1.a = t2.c AND t1.b = t2.c
+GROUP BY t1.a;
+
+--echo # Is transformed into
+--echo # "select t1.b AS b from t1 join t2 where t1.a = t2.c
+--echo #  and t2.d = t2.c and t1.b = t2.d group by t1.a"
+SELECT t1.b
+FROM t1 LEFT JOIN t2 ON (t2.d = t2.c AND t1.b = t2.d)
+WHERE t1.a = t2.c
+GROUP BY t1.a;
+
 --echo # Non-grouping left table LEFT JOIN field t1.b is used
 --echo # in ON expression
 SELECT t1.a,t2.c
@@ -2169,12 +2472,6 @@ SELECT t1.b
 FROM t1 LEFT JOIN t2 ON t1.b = t2.c
 GROUP BY t1.a;
 
-SELECT t3.c
-FROM t1 LEFT JOIN (
-                   t2 LEFT JOIN t2 AS t3 ON (t3.c = 2) AND (t2.c = 1)
-                  ) ON (t1.a = t2.d)
-GROUP BY t2.c;
-
 --error ER_NON_GROUPING_FIELD_USED
 SELECT t2.c
 FROM t1 LEFT JOIN t2 ON (t2.c = 2) AND (t1.b = 2)
@@ -2197,26 +2494,6 @@ LEFT JOIN (
           ) ON (t1.a = 2)
 GROUP BY t1.a;
 
-SELECT t3.c
-FROM t1 LEFT JOIN (
-                   t2 LEFT JOIN t2 AS t3 ON (t3.c = 2) AND (t2.c = 1)
-                  ) ON (t1.a = t2.c)
-GROUP BY t1.a;
-
-SELECT t3.c
-FROM t1 LEFT JOIN (
-                   t2 LEFT JOIN t2 AS t3 ON (t3.c = 2) AND (t2.c > 1)
-                  ) ON (t1.a = t2.c)
-GROUP BY t1.a;
-
-SELECT t1.a,t2.d,t3.c,t3.d
-FROM t1 LEFT JOIN (
-                   t2 LEFT JOIN t2 AS t3 ON (t3.c = 2) AND
-                                            (t2.d = 1) AND
-                                            (t3.d = t2.d)
-                  ) ON (t1.a = t2.d)
-GROUP BY t1.a;
-
 --error ER_NON_GROUPING_FIELD_USED
 SELECT t1.a,t2.d,t3.c
 FROM t1 LEFT JOIN (
@@ -2240,21 +2517,6 @@ FROM t1 LEFT JOIN (
                   ) ON 1 > 0
 GROUP BY t2.d;
 
-SELECT t3.c
-FROM t1 JOIN (
-              t2 LEFT JOIN t2 AS t3 ON (t3.c = 2)
-             ) ON (t1.a = 1)
-GROUP BY "";
-
---echo # Is transformed into:
---echo # "select t3.c AS c from t1 join t2 left join t2 t3 on
---echo #  (t3.c = 2) where t1.a = 1 group by t2.c"
-SELECT t3.c
-FROM t1 JOIN (
-              t2 LEFT JOIN t2 AS t3 ON (t3.c = 2)
-             ) ON (t1.a = 1)
-GROUP BY t2.c;
-
 --error ER_NON_GROUPING_FIELD_USED
 SELECT t4.a
 FROM t1 LEFT JOIN (
@@ -2295,32 +2557,6 @@ FROM t1 LEFT JOIN (
                   ) ON (t1.a = t2.d)
 GROUP BY t3.c;
 
-SELECT t4.a
-FROM t1 LEFT JOIN (
-                   t2 LEFT JOIN (
-                                 t2 AS t3
-                                 LEFT JOIN t1 AS t4 ON (t4.a = 1)
-                                ) ON (t2.c = t4.b)
-                  ) ON (t1.a = t2.d)
-GROUP BY t2.c;
-
-SELECT t4.a
-FROM t1 LEFT JOIN (
-                   t2 LEFT JOIN (
-                                 t2 AS t3
-                                 LEFT JOIN t1 AS t4 ON (t4.a = 1) AND
-                                                       (t3.c = t3.c)
-                                ) ON (t2.c = t3.c)
-                  ) ON (t1.a = t2.c)
-GROUP BY t1.a;
-
-SELECT t3.c
-FROM t1 LEFT JOIN (
-                   t2
-                   JOIN t2 AS t3 ON (t3.c = 2)
-                  ) ON (t1.a = 1)
-GROUP BY t1.a;
-
 --error ER_NON_GROUPING_FIELD_USED
 SELECT t3.c
 FROM t1 LEFT JOIN (
@@ -2329,13 +2565,6 @@ FROM t1 LEFT JOIN (
                   ) ON (t1.a = 1)
 GROUP BY "";
 
-SELECT t3.c
-FROM t1 LEFT JOIN (
-                   t2 JOIN
-                   t2 AS t3 ON (t3.c = 2) AND (t2.c = t2.c)
-                  ) ON (t1.a = 1)
-GROUP BY t1.a;
-
 --error ER_NON_GROUPING_FIELD_USED
 SELECT t3.c
 FROM t1 LEFT JOIN (
@@ -2352,16 +2581,6 @@ FROM t1 LEFT JOIN (
                   ) ON 1 > 0
 GROUP BY "";
 
-SELECT t4.c
-FROM t1 LEFT JOIN (
-                   t2 LEFT JOIN (
-                                 t1 AS t3
-                                 LEFT JOIN t2 AS t4 ON (t4.c = 2) AND
-                                                       (t3.a > 0)
-                                ) ON (t2.d = t3.a) AND (t2.d > 1)
-                  ) ON t2.d = t1.a
-GROUP BY t1.a;
-
 --error ER_NON_GROUPING_FIELD_USED
 SELECT t4.c
 FROM t1 LEFT JOIN (
@@ -2405,63 +2624,6 @@ FROM t1 LEFT JOIN (
                   ) ON t2.d = t1.a AND t1.b > 2
 GROUP BY t1.a;
 
-SELECT t4.c
-FROM t1 LEFT JOIN (
-                   t2 LEFT JOIN (
-                                 t1 AS t3
-                                 LEFT JOIN t2 AS t4 ON (t4.c = t3.a) AND
-                                                       (t3.a > 1)
-                                ) ON (t2.d = t3.a)
-                  ) ON 1 > 0
-GROUP BY t2.d;
-
-SELECT t4.c
-FROM t1 LEFT JOIN (
-                   t2 LEFT JOIN (
-                                 t1 AS t3
-                                 LEFT JOIN t2 AS t4 ON (t4.c = t3.a) AND
-                                                       (t3.a > 1)
-                                ) ON 1 > 0
-                  ) ON 1 > 0
-GROUP BY t3.a;
-
-SELECT t4.c
-FROM t1 LEFT JOIN (
-                   t2 LEFT JOIN (
-                                 t1 AS t3
-                                 LEFT JOIN t2 AS t4 ON (t4.c = t3.a) AND
-                                                       (t3.a > 1)
-                                ) ON 1 > 0
-                  ) ON (t1.a = t2.d)
-GROUP BY t3.a;
-
-SELECT t4.c
-FROM t1 LEFT JOIN (
-                   t2 LEFT JOIN (
-                                 t1 AS t3
-                                 LEFT JOIN t2 AS t4 ON (t4.c = t3.a) AND
-                                                       (t3.a > 1)
-                                ) ON 1 > 0
-                  ) ON (t1.a = 1)
-GROUP BY t3.a;
-
-SELECT t4.c
-FROM t1 LEFT JOIN (
-                   t2 LEFT JOIN (
-                                 t1 AS t3
-                                 LEFT JOIN t2 AS t4 ON (t4.c = 2) AND
-                                                       (t3.a > 1)
-                                ) ON t2.d = t3.a
-                  ) ON (t2.d = t1.a) AND (t2.c > 2)
-GROUP BY t1.a;
-
-SELECT t3.a
-FROM t1 LEFT JOIN (
-                   t2 LEFT JOIN
-                   t1 AS t3 ON (t2.d = t3.a) AND (t2.d > 1)
-                  ) ON (t2.d = t1.a) AND (t2.c > 1)
-GROUP BY t1.a;
-
 --error ER_NON_GROUPING_FIELD_USED
 SELECT t3.a
 FROM t1 LEFT JOIN (
@@ -2478,36 +2640,6 @@ FROM t1 LEFT JOIN (
                   ) ON (t2.d = t1.a) AND (t1.b > 1)
 GROUP BY t1.a;
 
-SELECT t4.c
-FROM t1 LEFT JOIN (
-                   t2 LEFT JOIN (
-                                 t1 AS t3
-                                 LEFT JOIN t2 AS t4 ON (t4.c = t3.a) AND
-                                                       (t3.a > 1)
-                                ) ON 1 > 0
-                  ) ON (t1.a = 1)
-GROUP BY t3.a;
-
-SELECT t4.c
-FROM t1 LEFT JOIN (
-                   t2 LEFT JOIN (
-                                 t1 AS t3
-                                 LEFT JOIN t2 AS t4 ON (t4.c = t3.a) AND
-                                                       (t3.a > 1)
-                                ) ON (t2.c = 2)
-                  ) ON (t1.a = 1)
-GROUP BY t3.a;
-
-SELECT t4.c
-FROM t1 LEFT JOIN (
-                   t2 LEFT JOIN (
-                                 t1 AS t3
-                                 LEFT JOIN t2 AS t4 ON (t4.c = t3.a) AND
-                                                       (t3.a > 1)
-                                ) ON (t2.c = 2)
-                  ) ON 1 > 0
-GROUP BY t3.a;
-
 --error ER_NON_GROUPING_FIELD_USED
 SELECT t3.a
 FROM t1 LEFT JOIN (
@@ -2516,13 +2648,6 @@ FROM t1 LEFT JOIN (
                   ) ON (t1.a > 2)
 GROUP BY t2.c;
 
-SELECT t3.a
-FROM t1 LEFT JOIN (
-                   t2
-                   JOIN t1 AS t3 ON (t2.c = t3.a) AND (t2.d > 1)
-                  ) ON (t1.a = t2.c)
-GROUP BY t1.a;
-
 --error ER_NON_GROUPING_FIELD_USED
 SELECT t2.c
 FROM t1 LEFT JOIN (
@@ -2539,22 +2664,6 @@ FROM t1 LEFT JOIN (
                   ) ON (t1.a = t2.c)
 GROUP BY t1.a;
 
---echo # Is transformed into
---echo # "select t1.b AS b from t1 join t2 where t1.a = t2.c
---echo #  and t1.b = t2.c and t2.d = t2.c group by t1.a"
-SELECT t1.b
-FROM t1 LEFT JOIN t2 ON (t2.d = t2.c)
-WHERE t1.a = t2.c AND t1.b = t2.c
-GROUP BY t1.a;
-
---echo # Is transformed into
---echo # "select t1.b AS b from t1 join t2 where t1.a = t2.c
---echo #  and t2.d = t2.c and t1.b = t2.d group by t1.a"
-SELECT t1.b
-FROM t1 LEFT JOIN t2 ON (t2.d = t2.c AND t1.b = t2.d)
-WHERE t1.a = t2.c
-GROUP BY t1.a;
-
 --echo #
 --echo # Usage of non-deterministic functions
 --echo #
@@ -2616,6 +2725,24 @@ FROM t1 LEFT JOIN t2 ON t1.a = t2.c
 WHERE t1.a = rand()
 GROUP BY t1.a;
 
+SELECT t1.a
+FROM t1 LEFT JOIN t2 ON (t1.a = t2.c) AND (SELECT t1.b)
+GROUP BY t1.a;
+
+--error ER_NON_GROUPING_FIELD_USED
+SELECT t2.c
+FROM t1 LEFT JOIN t2 ON (t1.a = t2.c) AND (SELECT t1.b)
+GROUP BY t1.a;
+
+SELECT t2.c
+FROM t1 LEFT JOIN t2 ON (t1.a = t2.c) AND t1.a
+GROUP BY t1.a;
+
+--error ER_NON_GROUPING_FIELD_USED
+SELECT t2.c
+FROM t1 LEFT JOIN t2 ON (t1.a = t2.c) AND (SELECT t1.a)
+GROUP BY t1.a;
+
 DROP TABLE t3;
 
 --echo #
@@ -2738,6 +2865,27 @@ LEFT OUTER JOIN t2 ON t1.a = t2.c
   LEFT OUTER JOIN t1 AS t3 ON t1.a = t3.b
 GROUP BY t3.b;
 
+--echo # Aggregated functions used if no GROUP BY is used
+
+CREATE VIEW v1 AS (SELECT * FROM t1);
+
+SELECT MAX(v1.a)
+FROM v1;
+
+SELECT MAX(v1.a),SUM(v1.a)
+FROM v1;
+
+--echo # different error messages, should be the same
+--error ER_MIX_OF_GROUP_FUNC_AND_FIELDS
+SELECT MAX(t1.a),t1.a
+FROM t1;
+
+--error ER_NON_GROUPING_FIELD_USED
+SELECT MAX(v1.a),v1.b
+FROM v1;
+
+DROP VIEW v1;
+
 DROP TABLE t1,t2;
 
 SET SQL_MODE=' ';
diff --git a/sql/item.cc b/sql/item.cc
index 521e96b6d9b..4000549097d 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -10623,7 +10623,5 @@ bool Item::is_deterministic_arg()
          (collation.collation->state & MY_CS_NOPAD))))
       return false;
   }
-  if (field_type() == MYSQL_TYPE_FLOAT)
-    return false;
   return true;
 }
diff --git a/sql/item.h b/sql/item.h
index a3ba67d99cf..44955b80fac 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -2389,12 +2389,6 @@ public:
     Checks if this item consists in the left part of arg IN subquery predicate
   */
   bool pushable_equality_checker_for_subquery(uchar *arg);
-  bool is_number()
-  {
-    return (cmp_type() == INT_RESULT ||
-            cmp_type() == DECIMAL_RESULT ||
-            cmp_type() == REAL_RESULT);
-  }
   bool is_deterministic_arg();
 };
 
diff --git a/sql/item_func.h b/sql/item_func.h
index a3caa08aac2..b91c386b2c8 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -1027,22 +1027,6 @@ class Item_num_op :public Item_func_numhybrid
       set_handler(type_handler_long_or_longlong());
   }
   bool need_parentheses_in_default() { return true; }
-  bool is_deterministic_func()
-  {
-    Item *args0= args[0];
-    for (uint i= 0; i < arg_count; i++)
-    {
-      if (args[i]->field_type() == MYSQL_TYPE_FLOAT ||
-          args[i]->field_type() == MYSQL_TYPE_GEOMETRY ||
-          (args0->field_type() != MYSQL_TYPE_BIT &&
-           i != 0 &&
-           args[i]->field_type() == MYSQL_TYPE_BIT) ||
-          (args[i]->cmp_type() != args0->cmp_type() &&
-           (!args0->is_number() || !args[i]->is_number())))
-        return false;
-    }
-    return true;
-  }
 };
 
 
diff --git a/sql/item_strfunc.h b/sql/item_strfunc.h
index 3c5bebbad66..7bc7723eec0 100644
--- a/sql/item_strfunc.h
+++ b/sql/item_strfunc.h
@@ -605,6 +605,8 @@ public:
   const char *mode_name() const { return "leading"; }
   Item *get_copy(THD *thd)
   { return get_item_copy<Item_func_ltrim>(thd, this); }
+  bool is_deterministic_func()
+  { return Item_args::has_deterministic_args(); }
 };
 
 
diff --git a/sql/sql_func_dep.cc b/sql/sql_func_dep.cc
index eec1e9f0489..709e599707f 100644
--- a/sql/sql_func_dep.cc
+++ b/sql/sql_func_dep.cc
@@ -74,6 +74,7 @@
         2. Contain non IS or FD fields from the left LEFT JOIN tables.
         3. Contain no IS or FD fields from the left LEFT JOIN tables if the
            considered LEFT JOIN is not the most outer LEFT JOIN.
+        4. Contain subquery.
 
     3. From Virtual column definition.
 
@@ -664,10 +665,11 @@ bool check_on_expr_and_get_equalities_info(FD_select_info *sl_info,
     return false;
   }
   if ((!sl_info->top_level &&                                            // 2
-         !(on_expr->used_tables() & (~sl_info->cur_level_tabs))) ||
-          ((on_expr->used_tables() & (~sl_info->cur_level_tabs)) &&        // 3
-            on_expr->walk(&Item::check_reject_fd_extraction_processor, 0,
-                        &sl_info->cur_level_tabs)))
+       !(on_expr->used_tables() & (~sl_info->cur_level_tabs))) ||
+      ((on_expr->used_tables() & (~sl_info->cur_level_tabs)) &&        // 3
+        on_expr->walk(&Item::check_reject_fd_extraction_processor, 0,
+                      &sl_info->cur_level_tabs)) ||
+       on_expr->with_subquery())
   {
     /*
       Check that this ON expression doesn't contain
@@ -1075,11 +1077,12 @@ bool are_select_fields_fd(st_select_lex *sl, List<Item> *gb_items,
 
 bool st_select_lex::check_func_dep()
 {
+  DBUG_ENTER("st_select_lex::check_func_dep");
   /* Stop if no tables are used or fake SELECT is processed. */
   if (leaf_tables.is_empty() ||
       select_number == UINT_MAX ||
       select_number == INT_MAX)
-    return false;
+    DBUG_RETURN(0);
 
   bool need_check= (group_list.elements > 0) ||
                     (master_unit()->outer_select() &&
@@ -1097,7 +1100,8 @@ bool st_select_lex::check_func_dep()
   }
   set_update_table_fields(this); /* UPDATE query processing. */
 
-  if (group_list.elements == 0 && !having)
+  if (group_list.elements == 0 &&
+      !having && !agg_func_used())
   {
     /*
       This SELECT has no GROUP BY clause and HAVING.
@@ -1111,31 +1115,29 @@ bool st_select_lex::check_func_dep()
       bitmap_set_all(&tbl->table->tmp_set);
     }
     if (!need_check)
-      return false;
+      DBUG_RETURN(0);
   }
 
   List<Item> gb_items;
   /* Collect fields from the GROUP BY of this SELECT. */
   if (collect_gb_items(this, gb_items))
-    return true;
+    DBUG_RETURN(1);
 
   if (olap != UNSPECIFIED_OLAP_TYPE)
   {
     table_map map= 0;
     /* If ROLLUP is used don't expand FD fields set. */
     if (!are_select_fields_fd(this, &gb_items, map))
-      return true;
-    return false;
+      DBUG_RETURN(1);
+    DBUG_RETURN(0);
   }
 
   List<Item_equal_fd_info> eq_info;
   FD_select_info *sl_info=
     new (join->thd->mem_root) FD_select_info(this, &eq_info, "WHERE clause");
-  if (expand_fdfs_with_top_join_tables_fields(sl_info))
-    return true;
-
-  if (!are_select_fields_fd(this, &gb_items, sl_info->forbid_fd_expansion))
-    return true;
+  if (expand_fdfs_with_top_join_tables_fields(sl_info) ||
+      !are_select_fields_fd(this, &gb_items, sl_info->forbid_fd_expansion))
+    DBUG_RETURN(1);
 
-  return false;
+  DBUG_RETURN(0);
 }