-- InnoDB
|
drop table if exists test_having_innodb;
|
create table if not exists test_having_innodb (
|
someString varchar(1000),
|
someInt int,
|
FK int
|
) ENGINE=innodb DEFAULT CHARSET=utf8;
|
|
insert into test_having_innodb values
|
('bla', 1, 17),
|
('xyz', 2, 17);
|
|
select * from test_having_innodb;
|
|
drop table if exists dim_having_innodb;
|
create table if not exists dim_having_innodb (
|
PK int,
|
someString varchar(1000)
|
) ENGINE=innodb DEFAULT CHARSET=utf8;
|
|
insert into dim_having_innodb values
|
(17, 'test');
|
|
select * from dim_having_innodb;
|
|
drop view if exists test_having_innodb_view;
|
create view test_having_innodb_view as
|
select
|
someString as someString,
|
someInt as someInt,
|
NVL(FK, 17) as FK
|
from test_having_innodb;
|
|
select * from test_having_innodb_view;
|
|
drop view if exists dim_having_innodb_view;
|
create view dim_having_innodb_view as
|
select * from
|
dim_having_innodb;
|
|
select `dim`.`someString` as `c0`
|
from `dim_having_innodb_view` as `dim`,
|
`test_having_innodb_view` as `fact`
|
where `fact`.`FK` = `dim`.`PK`
|
group by `dim`.`someString`
|
having NOT((sum(`fact`.`someInt`) is null))
|
order by ISNULL(`dim`.`someString`) ASC,
|
`dim`.`someString` ASC;
|
-- NO ERROR!
|
|
|
|
|
|
|
|
-- COLUMNSTORE
|
drop table if exists test_having_columnstore;
|
create table if not exists test_having_columnstore (
|
someString varchar(1000),
|
someInt int,
|
FK int
|
) ENGINE=Columnstore DEFAULT CHARSET=utf8;
|
|
insert into test_having_columnstore values
|
('bla', 1, 17),
|
('xyz', 2, 17);
|
|
select * from test_having_columnstore;
|
|
drop table if exists dim_having_columnstore;
|
create table if not exists dim_having_columnstore (
|
PK int,
|
someString varchar(1000)
|
) ENGINE=Columnstore DEFAULT CHARSET=utf8;
|
|
insert into dim_having_columnstore values
|
(17, 'test');
|
|
select * from dim_having_columnstore;
|
|
drop view if exists test_having_columnstore_view;
|
create view test_having_columnstore_view as
|
select
|
someString as someString,
|
someInt as someInt,
|
FK as FK
|
from test_having_columnstore;
|
|
select * from test_having_columnstore_view;
|
|
drop view if exists dim_having_columnstore_view;
|
create view dim_having_columnstore_view as
|
select * from
|
dim_having_columnstore;
|
|
select `dim`.`someString` as `c0`
|
from `dim_having_columnstore_view` as `dim`, -- if the TABLE dim_having_columnstore is used here, the error does not occur!
|
`test_having_columnstore_view` as `fact`
|
where `fact`.`FK` = `dim`.`PK`
|
group by `dim`.`someString`
|
having NOT((sum(`fact`.`someInt`) is null))
|
order by ISNULL(`dim`.`someString`) ASC,
|
`dim`.`someString` ASC;
|
-- ERROR !!!
|
-- Error Code: 1815. Internal error: IDB-2021: 'unknown db.unknown table.unknown field' 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. 0.063 sec
|
|