– 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;