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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.041 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.