Bug #70220 Grouping with a view may report 'Invalid use of group function'
Submitted: 3 Sep 2013 6:14 Modified: 26 Oct 2015 15:51
Reporter: Roy Lyseng Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.1,5.5, 5.6, 5.7.2-m12 OS:Any
Assigned to: CPU Architecture:Any
Tags: aggregation, derived table, Scalar subquery, VIEW

[3 Sep 2013 6:14] Roy Lyseng 
Description:
We have three similar queries, one is fetching data from a table, the second is fetching from a derived table and the third is fetching from a view. The two first queries behave correctly, but the third gives an error:

  ERROR 1111 (HY000): Invalid use of group function

How to repeat:
CREATE TABLE test (a INT);

INSERT INTO test VALUES (1);

SELECT (SELECT GROUP_CONCAT(COUNT(q.a)) FROM test) AS i 
FROM test AS q;

SELECT (SELECT GROUP_CONCAT(COUNT(q.i)) FROM test) AS i 
FROM (SELECT a AS i FROM test) AS q;

CREATE VIEW q AS SELECT a AS i FROM test;

SELECT (SELECT GROUP_CONCAT(COUNT(q.i)) FROM test) AS i 
FROM q;

DROP VIEW q;
DROP TABLE test;
[4 Sep 2013 7:46] MySQL Verification Team 
Hello Roy,

Thank you for your bug report and the test case.
Verified as described.

Thanks,
Umesh
[4 Sep 2013 7:47] MySQL Verification Team 
How to repeat:

use test;
CREATE TABLE test (a INT);

INSERT INTO test VALUES (1);

SELECT (SELECT GROUP_CONCAT(COUNT(q.a)) FROM test) AS i 
FROM test AS q;

SELECT (SELECT GROUP_CONCAT(COUNT(q.i)) FROM test) AS i 
FROM (SELECT a AS i FROM test) AS q;

CREATE VIEW q AS SELECT a AS i FROM test;

SELECT (SELECT GROUP_CONCAT(COUNT(q.i)) FROM test) AS i 
FROM q;

DROP VIEW q;
DROP TABLE test;

// 5.5.33

mysql> use test;
Database changed
mysql> CREATE TABLE test (a INT);
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO test VALUES (1);

Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SELECT (SELECT GROUP_CONCAT(COUNT(q.a)) FROM test) AS i
    -> FROM test AS q;
+------+
| i    |
+------+
| 1    |
+------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT (SELECT GROUP_CONCAT(COUNT(q.i)) FROM test) AS i
    -> FROM (SELECT a AS i FROM test) AS q;
+------+
| i    |
+------+
| 1    |
+------+
1 row in set (0.00 sec)

mysql>
mysql> CREATE VIEW q AS SELECT a AS i FROM test;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> SELECT (SELECT GROUP_CONCAT(COUNT(q.i)) FROM test) AS i
    -> FROM q;
ERROR 1111 (HY000): Invalid use of group function
mysql>
mysql> DROP VIEW q;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE test;
Query OK, 0 rows affected (0.00 sec)

// 5.6.15

mysql> use test;
Database changed
mysql> CREATE TABLE test (a INT);
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> INSERT INTO test VALUES (1);

Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SELECT (SELECT GROUP_CONCAT(COUNT(q.a)) FROM test) AS i
    -> FROM test AS q;
+------+
| i    |
+------+
| 1    |
+------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT (SELECT GROUP_CONCAT(COUNT(q.i)) FROM test) AS i
    -> FROM (SELECT a AS i FROM test) AS q;
+------+
| i    |
+------+
| 1    |
+------+
1 row in set (0.00 sec)

mysql>
mysql> CREATE VIEW q AS SELECT a AS i FROM test;

Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> SELECT (SELECT GROUP_CONCAT(COUNT(q.i)) FROM test) AS i
    -> FROM q;
ERROR 1111 (HY000): Invalid use of group function
mysql>
mysql> DROP VIEW q;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE test;
Query OK, 0 rows affected (0.01 sec)

// 5.7.2-m12

mysql> use test;
Database changed
mysql> CREATE TABLE test (a INT);

INSERT INTO test VALUES (1);
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> INSERT INTO test VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SELECT (SELECT GROUP_CONCAT(COUNT(q.a)) FROM test) AS i
    -> FROM test AS q;
+------+
| i    |
+------+
| 1    |
+------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT (SELECT GROUP_CONCAT(COUNT(q.i)) FROM test) AS i
    -> FROM (SELECT a AS i FROM test) AS q;
+------+
| i    |
+------+
| 1    |
+------+
1 row in set (0.00 sec)

mysql>
mysql> CREATE VIEW q AS SELECT a AS i FROM test;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> SELECT (SELECT GROUP_CONCAT(COUNT(q.i)) FROM test) AS i
    -> FROM q;
ERROR 1111 (HY000): Invalid use of group function
mysql>
mysql> DROP VIEW q;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE test;
Query OK, 0 rows affected (0.01 sec)

// 5.1.71

mysql> use test;
Database changed
mysql> CREATE TABLE test (a INT);
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO test VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SELECT (SELECT GROUP_CONCAT(COUNT(q.a)) FROM test) AS i
    -> FROM test AS q;

+------+
| i    |
+------+
| 1    |
+------+
1 row in set (0.02 sec)

mysql>
mysql> SELECT (SELECT GROUP_CONCAT(COUNT(q.i)) FROM test) AS i
    -> FROM (SELECT a AS i FROM test) AS q;
+------+
| i    |
+------+
| 1    |
+------+
1 row in set (0.00 sec)

mysql>
mysql> CREATE VIEW q AS SELECT a AS i FROM test;

SELECT (SELECT GROUP_CONCAT(COUNT(q.i)) FROM test) AS i
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> SELECT (SELECT GROUP_CONCAT(COUNT(q.i)) FROM test) AS i
    -> FROM q;
ERROR 1111 (HY000): Invalid use of group function
mysql>
mysql> DROP VIEW q;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE test;
Query OK, 0 rows affected (0.00 sec)
[26 Oct 2015 15:51] Paul DuBois 
Noted in 5.8.0 changelog.

Grouping with a view could produce an ER_INVALID_GROUP_FUNC_USE error
("Invalid use of group function") when selecting from the base table
did not.