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

Columnstore throws error when using GROUP BY on DB-Views where field name is identical in two different tables

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • None
    • 23.10.1
    • ExeMgr
    • CS 5.6.8 MariaDB 10.5.18 RHEL 8.5
    • 2023-11

    Description

      – COLUMNSTORE throws error:
      – =========================
      create table t1
      (
      id int,
      someStr varchar(100)
      ) ENGINE=Columnstore;

      create table t2
      (
      id int,
      fk_t1 int,
      someStr varchar(100)
      ) ENGINE=Columnstore;

      insert into t1 values (1, 'bla');
      insert into t2 values (1, 1, 'xyz');

      create view view_on_t1 as select id, someStr from t1;
      create view view_on_t2 as select id, fk_t1, someStr from t2;

      – ERROR !!!
      select max(view_on_t1.id), view_on_t1.someStr, view_on_t2.someStr
      from view_on_t1
      left outer join view_on_t2
      on view_on_t1.id = view_on_t2.fk_t1
      group by view_on_t1.someStr, view_on_t2.someStr;
      – ==> All non-aggregate columns in the SELECT and ORDER BY clause must be included in the GROUP BY clause. 0.063 sec

      – working on tables directly it works however:
      select max(t1.id), t1.someStr, t2.someStr
      from t1
      left outer join t2
      on t1.id = t2.fk_t1
      group by t1.someStr, t2.someStr;

      – INNODB
      – ======
      create table t1_innodb
      (
      id int,
      someStr varchar(100)
      ) ENGINE=InnoDB;

      create table t2_innodb
      (
      id int,
      fk_t1 int,
      someStr varchar(100)
      ) ENGINE=InnoDB;

      insert into t1_innodb values (1, 'bla');
      insert into t2_innodb values (1, 1, 'xyz');

      create view view_on_t1_innodb as select id, someStr from t1_innodb;
      create view view_on_t2_innodb as select id, fk_t1, someStr from t2_innodb;

      – works:
      select max(view_on_t1_innodb.id), view_on_t1_innodb.someStr, view_on_t2_innodb.someStr
      from view_on_t1_innodb
      left outer join view_on_t2_innodb
      on view_on_t1_innodb.id = view_on_t2_innodb.fk_t1
      group by view_on_t1_innodb.someStr, view_on_t2_innodb.someStr;

      Attachments

        Activity

          People

            allen.herrera Allen Herrera
            aeae81 andreas eschbacher
            Votes:
            0 Vote for this issue
            Watchers:
            6 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.