Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
None
-
CS 5.6.8 MariaDB 10.5.18 RHEL 8.5
-
2023-11
Description
– COLUMNSTORE throws error:
– =========================
create table t1
(
id int,
someStr varchar(100)
) ENGINE=Columnstore;
create table t2
(
id int,
fk_t1 int,
someStr varchar(100)
) ENGINE=Columnstore;
insert into t1 values (1, 'bla');
insert into t2 values (1, 1, 'xyz');
create view view_on_t1 as select id, someStr from t1;
create view view_on_t2 as select id, fk_t1, someStr from t2;
– ERROR !!!
select max(view_on_t1.id), view_on_t1.someStr, view_on_t2.someStr
from view_on_t1
left outer join view_on_t2
on view_on_t1.id = view_on_t2.fk_t1
group by view_on_t1.someStr, view_on_t2.someStr;
– ==> All non-aggregate columns in the SELECT and ORDER BY clause must be included in the GROUP BY clause. 0.063 sec
– working on tables directly it works however:
select max(t1.id), t1.someStr, t2.someStr
from t1
left outer join t2
on t1.id = t2.fk_t1
group by t1.someStr, t2.someStr;
– INNODB
– ======
create table t1_innodb
(
id int,
someStr varchar(100)
) ENGINE=InnoDB;
create table t2_innodb
(
id int,
fk_t1 int,
someStr varchar(100)
) ENGINE=InnoDB;
insert into t1_innodb values (1, 'bla');
insert into t2_innodb values (1, 1, 'xyz');
create view view_on_t1_innodb as select id, someStr from t1_innodb;
create view view_on_t2_innodb as select id, fk_t1, someStr from t2_innodb;
– works:
select max(view_on_t1_innodb.id), view_on_t1_innodb.someStr, view_on_t2_innodb.someStr
from view_on_t1_innodb
left outer join view_on_t2_innodb
on view_on_t1_innodb.id = view_on_t2_innodb.fk_t1
group by view_on_t1_innodb.someStr, view_on_t2_innodb.someStr;