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

query validation incorrect for complex order by

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 1.0.11, 1.1.0
    • 1.4.3
    • ExeMgr
    • None

    Description

      The following query fails to parse though should work and does with innodb. Replacing the expression in the order by with the column alias is a workaround (2nd query). It would appear the validation is not correctly detecting the expression as an aggregate.

      CREATE TABLE testcolumnstore(
      id BIGINT,
      integerfield INT,
      integerfield2 INT
      )
      ENGINE=Columnstore;
       
       
      insert into testcolumnstore values(1,null,1);
      insert into testcolumnstore values(1,null,1);
      insert into testcolumnstore values(2,null,1);
       
       
      select id,
      SUM(integerfield) sum1,
      SUM(integerfield2) sum2,
      ifnull(SUM(integerfield),0)+ifnull(SUM(integerfield2),0) sum3
      from testcolumnstore
      group by id
      order by ifnull(SUM(integerfield),0)+ifnull(SUM(integerfield2),0);
       
      ERROR 1815 (HY000): Internal error: IDB-2021: 'test.testcolumnstore.integerfield' 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.
       
       
      select id,
      SUM(integerfield) sum1,
      SUM(integerfield2) sum2,
      ifnull(SUM(integerfield),0)+ifnull(SUM(integerfield2),0) sum3
      from testcolumnstore
      group by id
      order by sum3;
       
      +------+------+------+------+
      | id   | sum1 | sum2 | sum3 |
      +------+------+------+------+
      |    2 | NULL | 1    | 1    |
      |    1 | NULL | 2    | 2    |
      +------+------+------+------+
      2 rows in set (0.01 sec)
      

      Attachments

        Activity

          People

            Unassigned Unassigned
            dthompson David Thompson (Inactive)
            Votes:
            2 Vote for this issue
            Watchers:
            3 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.