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

Sub-query with GROUP BY and ORDER BY must allow non-aggregates in projection and ORDER BY as MDB does.

    XMLWordPrintable

    Details

    • Type: New Feature
    • Status: Open (View Workflow)
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: 1.5.8
    • Component/s: ExeMgr
    • Labels:
      None

      Description

      As of 1.4 CS doesn't support non-aggregates in both projection and ORDER BY but MDB does. Consider the example:

      MariaDB [test]> create table cs1 (i bigint, i2 bigint)engine=columnstore;
      Query OK, 0 rows affected (0.517 sec)
       
      MariaDB [test]> insert into cs1 values (1,36),(100,5),(26,1),(25,103),(1,41),(2,42);
      Query OK, 6 rows affected (0.446 sec)
      Records: 6  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> select * from ( select count(1), i, i2 from cs1 group by i order by i2 ) a;
      ERROR 1815 (HY000): Internal error: IDB-2021: 'test.cs1.i2' 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.
      MariaDB [test]> drop table cs1;
      Query OK, 0 rows affected (0.370 sec)
       
      MariaDB [test]> create table cs1 (i bigint, i2 bigint);
      Query OK, 0 rows affected (0.018 sec)
       
      MariaDB [test]> insert into cs1 values (1,36),(100,5),(26,1),(25,103),(1,41),(2,42);
      Query OK, 6 rows affected (0.005 sec)
      Records: 6  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> select * from ( select count(1), i, i2 from cs1 group by i order by i2 ) a;
      +----------+------+------+
      | count(1) | i    | i2   |
      +----------+------+------+
      |        1 |   26 |    1 |
      |        1 |  100 |    5 |
      |        2 |    1 |   36 |
      |        1 |    2 |   42 |
      |        1 |   25 |  103 |
      +----------+------+------+
      5 rows in set (0.002 sec)
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              toddstoffel Todd Stoffel
              Reporter:
              drrtuy Roman
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated: