Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-15771

Optimizer removes order by when it's sub-part of group by (test_if_subpart) too early

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Not a Bug
    • 10.2.13
    • 10.2.12
    • Optimizer
    • None
    • MariaDB fork for Columnstore engine.

    Description

      Optimizer removes order by when it's sub-part of group by (test_if_subpart) too early, before it decides how to execute group by. Order by can only be removed if group by naturally delivers sorted results. it's not the case, for example, if group by is pushed down to the engine.
      Here is the example query that triggers the bug:
      select a,b,sum(c) from opportunities group by a,b order by a desc;

      Attachments

        Issue Links

          Activity

            alice Alice Sherepa added a comment -

            Hi Roman, do you mean bug - results in a wrong order? Please add an example.

            create or replace table t (a  int, b int, c int);
            insert into t values (2,2,4), (2,1,5), (2,1,4),(2,2,6),(8,3,4),(1,6,1),(2,3,4),(5,3,2);
            select a,b,sum(c) from t group by a,b order by a desc;
            explain extended  select a,b,sum(c) from t group by a,b order by a desc;
             
            MariaDB [test]> select a,b,sum(c) from t group by a,b order by a desc;
            +------+------+--------+
            | a    | b    | sum(c) |
            +------+------+--------+
            |    8 |    3 | 4      |
            |    5 |    3 | 2      |
            |    2 |    1 | 9      |
            |    2 |    2 | 10     |
            |    2 |    3 | 4      |
            |    1 |    6 | 1      |
            +------+------+--------+
            6 rows in set (0.000 sec)
             
            MariaDB [test]> explain extended  select a,b,sum(c) from t group by a,b order by a desc;
            +------+-------------+-------+------+---------------+------+---------+------+------+----------+---------------------------------+
            | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
            +------+-------------+-------+------+---------------+------+---------+------+------+----------+---------------------------------+
            |    1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL |    8 |   100.00 | Using temporary; Using filesort |
            +------+-------------+-------+------+---------------+------+---------+------+------+----------+---------------------------------+
            1 row in set, 1 warning (0.000 sec)
             
            Note (Code 1003): select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,sum(`test`.`t`.`c`) AS `sum(c)` from `test`.`t` group by `test`.`t`.`a` desc,`test`.`t`.`b` order by `test`.`t`.`a` desc
            

            alice Alice Sherepa added a comment - Hi Roman, do you mean bug - results in a wrong order? Please add an example. create or replace table t (a int, b int, c int); insert into t values (2,2,4), (2,1,5), (2,1,4),(2,2,6),(8,3,4),(1,6,1),(2,3,4),(5,3,2); select a,b,sum(c) from t group by a,b order by a desc; explain extended select a,b,sum(c) from t group by a,b order by a desc;   MariaDB [test]> select a,b,sum(c) from t group by a,b order by a desc; +------+------+--------+ | a | b | sum(c) | +------+------+--------+ | 8 | 3 | 4 | | 5 | 3 | 2 | | 2 | 1 | 9 | | 2 | 2 | 10 | | 2 | 3 | 4 | | 1 | 6 | 1 | +------+------+--------+ 6 rows in set (0.000 sec)   MariaDB [test]> explain extended select a,b,sum(c) from t group by a,b order by a desc; +------+-------------+-------+------+---------------+------+---------+------+------+----------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+----------+---------------------------------+ | 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 8 | 100.00 | Using temporary; Using filesort | +------+-------------+-------+------+---------------+------+---------+------+------+----------+---------------------------------+ 1 row in set, 1 warning (0.000 sec)   Note (Code 1003): select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,sum(`test`.`t`.`c`) AS `sum(c)` from `test`.`t` group by `test`.`t`.`a` desc,`test`.`t`.`b` order by `test`.`t`.`a` desc
            drrtuy Roman added a comment -

            The problem caused by incorrect sorting, over char fields, that happens inside the columnstore engine.

            drrtuy Roman added a comment - The problem caused by incorrect sorting, over char fields, that happens inside the columnstore engine.

            People

              Unassigned Unassigned
              drrtuy Roman
              Votes:
              0 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.