Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-5236

MariaDB Columnstore: using views leads to an error when an Analyzer report with a measure and a year is built.

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 6.4.2, 23.02.2
    • 23.10.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
      
      

      Attachments

        Activity

          People

            denis0x0D Denis Khalikov
            aeae81 andreas eschbacher
            Roman Roman
            Kirill Perov Kirill Perov
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.