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

Equality propagation for ORDER BY items do not work with expressions

    Details

    • Type: Bug
    • Status: In Review (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.2, 10.3, 10.4, 10.5
    • Fix Version/s: 10.5
    • Component/s: Optimizer
    • Labels:
      None

      Description

      Test data

      create table ten(a int);
      insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
       
      create table one_k(a int);
      insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
       
      create table t1(a int, b int);
      insert into t1 select A.a + B.a* 10, A.a + B.a* 10 from ten A, ten B;
      create table t2(a int, b int, key(a));
      insert into t2 select A.a + B.a* 10, A.a+B.a*10 from ten A, ten B;
      create table t3(a int, b int);
      insert into t3 select A.a + B.a* 10 + C.a * 100, A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C
      

      MariaDB [test]>  explain select * from t1,t2 where t1.a > 95  and t1.a=t2.a order by t2.a;
      +------+-------------+-------+------+---------------+------+---------+-----------+------+-----------------------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref       | rows | Extra                       |
      +------+-------------+-------+------+---------------+------+---------+-----------+------+-----------------------------+
      |    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL      | 100  | Using where; Using filesort |
      |    1 | SIMPLE      | t2    | ref  | a             | a    | 5       | test.t1.a | 1    |                             |
      +------+-------------+-------+------+---------------+------+---------+-----------+------+-----------------------------+
      2 rows in set (0.008 sec)
       
      MariaDB [test]>  explain select * from t1,t2 where t1.a > 95  and t1.a=t2.a order by t2.a+1;
      +------+-------------+-------+------+---------------+------+---------+-----------+------+----------------------------------------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref       | rows | Extra                                        |
      +------+-------------+-------+------+---------------+------+---------+-----------+------+----------------------------------------------+
      |    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL      | 100  | Using where; Using temporary; Using filesort |
      |    1 | SIMPLE      | t2    | ref  | a             | a    | 5       | test.t1.a | 1    |                                              |
      +------+-------------+-------+------+---------------+------+---------+-----------+------+----------------------------------------------+
      2 rows in set (0.004 sec)
      

      So for expressions we don't propagate equal fields and so we are not able to use the optimization where we can sort by the first table

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                igor Igor Babaev
                Reporter:
                varun Varun Gupta
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated: