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.

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

          The problem seems to be the call to ISNULL in the select. It seems that there is a bug in MariaDB when using this function. Changing it to IFNULL or COALESCE works as expected. Also for not nullable columns executing this, is a major performance issue in the selects that mondrian generates as well. It would be a good option to be able to disable this somehow, but unfortunatly quick research shows that this might require writing a custom sql dialect. On the other hand there is no valid reason why MariaDB should not be able to execute selects with ISNULL.

          aeae81 andreas eschbacher added a comment - The problem seems to be the call to ISNULL in the select. It seems that there is a bug in MariaDB when using this function. Changing it to IFNULL or COALESCE works as expected. Also for not nullable columns executing this, is a major performance issue in the selects that mondrian generates as well. It would be a good option to be able to disable this somehow, but unfortunatly quick research shows that this might require writing a custom sql dialect. On the other hand there is no valid reason why MariaDB should not be able to execute selects with ISNULL.

          verified in develop 09 April 2024

          kirill.perov@mariadb.com Kirill Perov (Inactive) added a comment - verified in develop 09 April 2024

          People

            denis0x0D Denis Khalikov (Inactive)
            aeae81 andreas eschbacher
            Roman Roman
            Kirill Perov Kirill Perov (Inactive)
            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.