|
The following query fails to parse though should work and does with innodb. Replacing the expression in the order by with the column alias is a workaround (2nd query). It would appear the validation is not correctly detecting the expression as an aggregate.
CREATE TABLE testcolumnstore(
|
id BIGINT,
|
integerfield INT,
|
integerfield2 INT
|
)
|
ENGINE=Columnstore;
|
|
|
insert into testcolumnstore values(1,null,1);
|
insert into testcolumnstore values(1,null,1);
|
insert into testcolumnstore values(2,null,1);
|
|
|
select id,
|
SUM(integerfield) sum1,
|
SUM(integerfield2) sum2,
|
ifnull(SUM(integerfield),0)+ifnull(SUM(integerfield2),0) sum3
|
from testcolumnstore
|
group by id
|
order by ifnull(SUM(integerfield),0)+ifnull(SUM(integerfield2),0);
|
|
ERROR 1815 (HY000): Internal error: IDB-2021: 'test.testcolumnstore.integerfield' 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.
|
|
|
select id,
|
SUM(integerfield) sum1,
|
SUM(integerfield2) sum2,
|
ifnull(SUM(integerfield),0)+ifnull(SUM(integerfield2),0) sum3
|
from testcolumnstore
|
group by id
|
order by sum3;
|
|
+------+------+------+------+
|
| id | sum1 | sum2 | sum3 |
|
+------+------+------+------+
|
| 2 | NULL | 1 | 1 |
|
| 1 | NULL | 2 | 2 |
|
+------+------+------+------+
|
2 rows in set (0.01 sec)
|
|