Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
6.4.2, 23.02.2
-
None
-
None
-
Mariadb 10.6.9
-
2024-1
Description
Here is the example,
views with innodb are working well
views with columnstore failed
-- 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 |
|