[MDEV-10120] Wrong result of UNION .. ORDER BY GROUP_CONCAT() Created: 2016-05-25  Updated: 2020-07-09  Resolved: 2020-07-09

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Subquery, Optimizer
Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.2.33, 10.3.24, 10.4.14, 10.5.5, 10.6.0

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Varun Gupta (Inactive)
Resolution: Fixed Votes: 0
Labels: upstream-fixed

Issue Links:
Relates
relates to MDEV-8909 union parser cleanup Closed

 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);



 Comments   
Comment by Alexander Barkov [ 2016-05-25 ]

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 UNION

Related 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

Comment by Varun Gupta (Inactive) [ 2020-07-01 ] 

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.

Comment by Varun Gupta (Inactive) [ 2020-07-02 ]

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.

Comment by Oleksandr Byelkin [ 2020-07-02 ]

So it is clear that GROUP BY is local to the last SELECT in the union.

Comment by Varun Gupta (Inactive) [ 2020-07-06 ]

Patch
http://lists.askmonty.org/pipermail/commits/2020-July/014278.html

Comment by Oleksandr Byelkin [ 2020-07-09 ]

OK to push

Generated at Thu Feb 08 07:39:49 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.