Bug #72512 | Non-aggregated query with set function in ORDER BY should be rejected | ||
---|---|---|---|
Submitted: | 2 May 2014 8:57 | Modified: | 4 Jun 2014 16:45 |
Reporter: | Roy Lyseng | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 5.5, 5.6 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[2 May 2014 8:57]
Roy Lyseng
[2 May 2014 9:32]
MySQL Verification Team
Hello Roy, Thank you for the bug report and test case. Verified as described. Thanks, Umesh
[2 May 2014 9:32]
MySQL Verification Team
// MySQL 5.5.37 mysql> use test Database changed mysql> CREATE TABLE t1(a INTEGER); Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO t1 VALUES (1), (2); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> mysql> SELECT a FROM t1 ORDER BY COUNT(*); +------+ | a | +------+ | 1 | +------+ 1 row in set (0.02 sec) // Oracle 11g - rejects SQL> CREATE TABLE t1(a INTEGER); Table created. SQL> INSERT INTO t1 VALUES (1); 1 row created. SQL> INSERT INTO t1 VALUES (2); 1 row created. SQL> SELECT * FROM t1; A ---------- 1 2 SQL> SELECT a FROM t1 ORDER BY COUNT(*); SELECT a FROM t1 ORDER BY COUNT(*) * ERROR at line 1: ORA-00937: not a single-group group function SQL> SELECT version FROM V$INSTANCE; VERSION ----------------- 11.2.0.2.0 SQL>
[2 May 2014 9:33]
MySQL Verification Team
// PostgreSQL - rejects psql (9.3.4) WARNING: Console code page (437) differs from Windows code page (1252) 8-bit characters might not work correctly. See psql reference page "Notes for Windows users" for details. Type "help" for help. test=# CREATE TABLE t1(a INTEGER); CREATE TABLE test=# INSERT INTO t1 VALUES (1); INSERT 0 1 test=# INSERT INTO t1 VALUES (2); INSERT 0 1 test=# select * from t1; a --- 1 2 (2 rows) test=# SELECT a FROM t1 ORDER BY COUNT(*); ERROR: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT a FROM t1 ORDER BY COUNT(*); ^ test=#
[2 May 2014 9:36]
MySQL Verification Team
// 5.6.19 - affected mysql> select version(); +-------------------------------------------+ | version() | +-------------------------------------------+ | 5.6.19-enterprise-commercial-advanced-log | +-------------------------------------------+ 1 row in set (0.00 sec) mysql> CREATE TABLE t1(a INTEGER); Query OK, 0 rows affected (0.03 sec) mysql> INSERT INTO t1 VALUES (1), (2); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> mysql> SELECT a FROM t1 ORDER BY COUNT(*); +------+ | a | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> mysql> DROP TABLE t1; Query OK, 0 rows affected (0.01 sec)
[4 Jun 2014 16:45]
Paul DuBois
Noted in 5.7.5 changelog. A nonaggregated query with an ORDER BY applied to it cannot contain aggregate functions, but was not rejected as it should be. Now such queries are rejected with an ER_AGGREGATE_ORDER_NON_AGG_QUERY error. Example: SELECT a FROM t1 ORDER BY COUNT(*);