[MDEV-10122] MariaDB does not support group functions in some contexts where MySQL does Created: 2016-05-25  Updated: 2017-02-14  Resolved: 2017-02-14

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.2
Fix Version/s: 10.2.4

Type: Bug Priority: Critical
Reporter: Alexander Barkov Assignee: Vicențiu Ciorbaru
Resolution: Fixed Votes: 0
Labels: 10.2-rc

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

 Description   

I create a table like this:

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a INT);
 
INSERT INTO t1 VALUES (10),(20),(30);

These queries normally return results:

(SELECT a FROM t1 GROUP BY a ORDER BY GROUP_CONCAT(a));
 
(SELECT a FROM t1 GROUP BY a ORDER BY GROUP_CONCAT(a)) LIMIT 1;
 
(SELECT a FROM t1 GROUP BY a ORDER BY a,GROUP_CONCAT(a));
 
(SELECT a FROM t1 GROUP BY a ORDER BY a,GROUP_CONCAT(a)) LIMIT 1;

These queries return the "Invalid use of group function" error:

(SELECT a FROM t1 GROUP BY a ORDER BY GROUP_CONCAT(a)) ORDER BY a;
 
(SELECT a FROM t1 GROUP BY a ORDER BY GROUP_CONCAT(a)) ORDER BY a LIMIT 1;
 
(SELECT a FROM t1 GROUP BY a ORDER BY a,GROUP_CONCAT(a)) ORDER BY a;
 
(SELECT a FROM t1 GROUP BY a ORDER BY a,GROUP_CONCAT(a)) ORDER BY a LIMIT 1;
 
SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY GROUP_CONCAT(a));
 
SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY GROUP_CONCAT(a)) LIMIT 1;
 
SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY GROUP_CONCAT(a)) ORDER BY a;
 
SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY GROUP_CONCAT(a)) ORDER BY a LIMIT 1;
 
SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY a,GROUP_CONCAT(a));
 
SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY a,GROUP_CONCAT(a)) LIMIT 1;
 
SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY a,GROUP_CONCAT(a)) ORDER BY a;
 
SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY a,GROUP_CONCAT(a)) ORDER BY a LIMIT 1;

In MySQL-5.7 all above queries work without errors.

The same problem is repeatable with MAX instead of GROUP_CONCAT:
These queries work in both MariaDB and MySQL:

(SELECT a FROM t1 GROUP BY a ORDER BY MAX(a));
 
(SELECT a FROM t1 GROUP BY a ORDER BY MAX(a)) LIMIT 1;
 
(SELECT a FROM t1 GROUP BY a ORDER BY a,MAX(a));
 
(SELECT a FROM t1 GROUP BY a ORDER BY a,MAX(a)) LIMIT 1;

These queries fail in MariaDB, but work in MySQL:

(SELECT a FROM t1 GROUP BY a ORDER BY MAX(a)) ORDER BY a;
 
(SELECT a FROM t1 GROUP BY a ORDER BY MAX(a)) ORDER BY a LIMIT 1;
 
(SELECT a FROM t1 GROUP BY a ORDER BY a,MAX(a)) ORDER BY a;
 
(SELECT a FROM t1 GROUP BY a ORDER BY a,MAX(a)) ORDER BY a LIMIT 1;
 
SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY MAX(a));
 
SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY MAX(a)) LIMIT 1;
 
SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY MAX(a)) ORDER BY a;
 
SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY MAX(a)) ORDER BY a LIMIT 1;
 
SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY a,MAX(a));
 
SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY a,MAX(a)) LIMIT 1;
 
SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY a,MAX(a)) ORDER BY a;
 
SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY a,MAX(a)) ORDER BY a LIMIT 1;



 Comments   
Comment by Elena Stepanova [ 2016-05-27 ]

It worked in 5.1 and 5.2, but stopped working in 5.3.

Comment by Oleksandr Byelkin [ 2017-01-24 ]

I think it is connected to nonsens of having ORDER BY in subquery without LIMIT clause.

Comment by Alexander Barkov [ 2017-01-24 ]

Right, some ORDER BY are redundant. Perhaps, instead of returning an error, the optimizer could remove the redundant ORDER BY.

Comment by Sergei Petrunia [ 2017-02-09 ]

Ok to push.

Comment by Vicențiu Ciorbaru [ 2017-02-14 ]

Fixed with https://github.com/mariadb/server/commit/f675eab7dc7f1eb4f4b61bfdd548d9e8052678a4

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