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