[MCOL-5476] GROUP BY on duplicate expressions using functions throws error "IDB-2001: ... is not in GROUP BY clause." on Columnstore Created: 2023-04-14  Updated: 2023-12-21  Resolved: 2023-12-07

Status: Closed
Project: MariaDB ColumnStore
Component/s: MariaDB Server
Affects Version/s: 23.10
Fix Version/s: 23.10.1

Type: Bug Priority: Critical
Reporter: andreas eschbacher Assignee: Denis Khalikov
Resolution: Fixed Votes: 1
Labels: triage
Environment:

MariaDB 10.5.18, Columnstore 5.6.8; RHEL 8.5

Attachments: File EBI-656_bug_exposal_for_MariaDB_support.sql    
Sprint: 2023-11
Assigned for Review: Roman Roman
Assigned for Testing: Kirill Perov Kirill Perov

 Description   

The attached query throws the mentioned error when using Columnstore, but not when using InnoDB.
Furthermore, using Columnstore, omitting the function calls also works.

While this is for sure an unconventional query, this is generated by a third-party BI software, where we cannot influence how generation of the SQL statements is done.

 Comments   
Comment by andreas eschbacher [ 2023-04-14 ]

I cloned https://jira.mariadb.org/browse/MCOL-5468, which is why the Assignee is set. I have no possibility to UNassign the ticket it seems.

Comment by Allen Herrera [ 2023-06-12 ]

Group by the alias name, not the function

The following works

select distinct
 
DAYOFWEEK(someDatetime) as C1,
 
DAYOFWEEK(someDatetime) as C2,
 
SUM(someInt)
 
from A_CS
 
group by 
C1,
 
C2;

Comment by Kirill Perov [ 2023-12-06 ]

fixed in latest develop (06.12.2023)

testing finished ok

Generated at Thu Feb 08 02:58:10 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.