[MDEV-17807] Incorrect usage of CUBE/ROLLUP and ORDER BY for a basic query Created: 2018-11-22  Updated: 2023-11-30

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Fix Version/s: None

Type: New Feature Priority: Major
Reporter: Sergei Petrunia Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 1
Labels: None

Issue Links:
Blocks
Relates
relates to MDEV-17802 Check how optimizer behaves for TPC-D... Stalled

 Description   

I am not sure if this a bug or an intended limitation.
But this is not documented and this is certainly an issue. We cannot run a lot of queries in TPC-DS because of this.

create table _t1 (a int, b int, c int);
 
 
 
explain 
select 
  a,b,c
 
from 
  _t1
 
group by 
  a,b,c with rollup
 
order by 
  a,b,c;

Gives this error:

ERROR 1221 (HY000): Incorrect usage of CUBE/ROLLUP and ORDER BY



 Comments   
Comment by Sergei Petrunia [ 2018-11-22 ]

This works with MySQL 8.0.12. If I add a LIMIT clause, it still works.

Comment by Elena Stepanova [ 2018-11-22 ]

It was the documented behavior in MySQL before 8.0(.12).
https://dev.mysql.com/doc/refman/5.7/en/group-by-modifiers.html

When you use ROLLUP, you cannot also use an ORDER BY clause to sort the results. In other words, ROLLUP and ORDER BY are mutually exclusive in MySQL.

Comment by Elena Stepanova [ 2018-11-28 ]

psergey, do you want to have it documented as a limitation, or converted to a feature request?

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