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: | |
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
[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.