Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5
Description
DROP TABLE IF EXISTS t1; |
CREATE TABLE t1 (a INT) ENGINE=MyISAM; |
INSERT t1 VALUES (1),(2),(3); |
SELECT 1 AS a UNION SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY GROUP_CONCAT(a ORDER BY 1); |
returns this result:
+------+
|
| a |
|
+------+
|
| 1 |
|
+------+
|
This is wrong. The right part of the union alone:
SELECT * FROM t1 GROUP BY a WITH ROLLUP; |
returns
+------+
|
| a |
|
+------+
|
| 1 |
|
| 2 |
|
| 3 |
|
| NULL |
|
+------+
|
The result of a UNION can't return less rows than UNION parts alone.
The same problem is repeatable with other aggregate functions:
SELECT 1 AS a UNION SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY AVG(a); |
SELECT 1 AS a UNION SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY SUM(a); |
Attachments
Issue Links
- relates to
-
MDEV-8909 union parser cleanup
-
- Closed
-
Activity
MariaDB [test]> (SELECT 1 AS a) UNION (SELECT a FROM t1 GROUP BY a) ORDER BY GROUP_CONCAT(a);
|
+------+
|
| a |
|
+------+
|
| 1 |
|
+------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [test]> (SELECT 1 AS a) UNION (SELECT a FROM t1 GROUP BY a ORDER BY GROUP_CONCAT(a));
|
+------+
|
| a |
|
+------+
|
| 1 |
|
| 2 |
|
| 3 |
|
+------+
|
3 rows in set (0.00 sec)
|
|
Adding brackets at different places gives the varying results, maybe this is what is happening. The query is getting executed as the first one.
From the knowledge base https://mariadb.com/kb/en/union/,
it says UNION queries cannot be used with aggregate functions.
So this query should return an error.
Also mysql docs say
UNION queries with an aggregate function in an ORDER BY clause are rejected with an ER_AGGREGATE_ORDER_FOR_UNION error.
So it is clear that GROUP BY is local to the last SELECT in the union.
This problem is not repeatable in MySQL-5.7, which returns an error:
ERROR 3028 (HY000): Expression #1 of ORDER BY contains aggregate function and applies to a UNIONRelated MySQL bugs:
Bug#18503515: UNION with set function in ORDER BY should be rejected
Bug#18694751 72512: Non-aggregated query with set function in ORDER BY should be rejected