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

Equality propagation for ORDER BY items do not work with expressions

    XMLWordPrintable

Details

    • Bug
    • Status: Stalled (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL), 11.1(EOL), 11.2(EOL)
    • 10.6, 10.11
    • Optimizer
    • 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

              psergei Sergei Petrunia
              varun Varun Gupta (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.