Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
6.3.1
-
None
-
None
-
Server version: 10.5.5-MariaDB MariaDB Server
Columnstore: 1.5.4-1
-
2023-11, 2023-12, 2024-1
Description
ORDER BY clause if not included in the GROUP BY clause fails on Columnstore.
Repro -
DROP TABLE IF EXISTS t1, t2;
CREATE TABLE t1 (
spID int(10) unsigned,
userID int(10) unsigned,
score smallint(5) unsigned,
date date
) ENGINE=Columnstore;
INSERT INTO t1 VALUES (1,1,1,'','0000-00-00');
CREATE TABLE t2 (
userID int(10) unsigned NOT NULL,
niName char(15)
) ENGINE=Columnstore;
INSERT INTO t2 VALUES (1,'name');
SELECT t1.spID, t2.userid, MIN(t1.score)
FROM t1, t2 WHERE t1.userID=t2.userID GROUP BY t2.userid, t1.spID ORDER BY t1.date;
ERROR 1815 (HY000): Internal error: IDB-2021: 'test.t1.date' is not in GROUP BY clause. All non-aggregate columns in the SELECT and ORDER BY clause must be included in the GROUP BY clause.
On Innodb
------------
SELECT t1.spID, t2.userid, MIN(t1.score)
-> FROM t1, t2 WHERE t1.userID=t2.userID GROUP BY t2.userid, t1.spID ORDER BY t1.date;
Empty set (0.001 sec)