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

Table elimination does not work across derived tables

    XMLWordPrintable

Details

    Description

      A dataset (not necessarily minimal):

      create table t1 (a int);
      insert into t1 select seq from seq_1_to_10;
       
      create table t11 (
        a int not null,
        b int,
        key(a)
      );
      insert into t11 select A.seq, A.seq+B.seq 
      from 
        seq_1_to_100 A,
        seq_1_to_1000 B;
      

      create table t12 (
        pk int primary key,
        col1 int
      );
      insert into t12 select seq, seq from seq_1_to_100000;
      

      A non-mergeable view where table t12 can be eliminated:

      create view v2b as 
      select 
        t11.a as a,
        count(*) as b
      from
        t11 left join t12 on t12.pk=t11.b
      group by
        t11.a;
      

      EXPLAIN shows it it is indeed eliminated:

      explain
      select t1.*
      from 
        t1 left join v2b on v2b.a=t1.a;
      

      +------+-----------------+------------+------+---------------+------+---------+---------+------+-------------+
      | id   | select_type     | table      | type | possible_keys | key  | key_len | ref     | rows | Extra       |
      +------+-----------------+------------+------+---------------+------+---------+---------+------+-------------+
      |    1 | PRIMARY         | t1         | ALL  | NULL          | NULL | NULL    | NULL    | 10   |             |
      |    1 | PRIMARY         | <derived2> | ref  | key0          | key0 | 5       | j5.t1.a | 2    | Using where |
      |    2 | LATERAL DERIVED | t11        | ref  | a             | a    | 4       | j5.t1.a | 1    |             |
      +------+-----------------+------------+------+---------------+------+---------+---------+------+-------------+
      

      Now, let's add a column from t12 into the select list:

      create view v2c as 
      select 
        t11.a as a,
        max(t12.col1) as b
      from
        t11 left join t12 on t12.pk=t11.b
      group by
        t11.a;
      

      and run a query that doesn't use it:

      explain
      select t1.* 
      from 
        t1 left join v2c on v2c.a=t1.a;
      

      EXPLAIN shows t12 was not eliminated:

      +------+-----------------+------------+--------+---------------+---------+---------+----------+------+-------------+
      | id   | select_type     | table      | type   | possible_keys | key     | key_len | ref      | rows | Extra       |
      +------+-----------------+------------+--------+---------------+---------+---------+----------+------+-------------+
      |    1 | PRIMARY         | t1         | ALL    | NULL          | NULL    | NULL    | NULL     | 10   |             |
      |    1 | PRIMARY         | <derived2> | ref    | key0          | key0    | 5       | j5.t1.a  | 2    | Using where |
      |    2 | LATERAL DERIVED | t11        | ref    | a             | a       | 4       | j5.t1.a  | 1    |             |
      |    2 | LATERAL DERIVED | t12        | eq_ref | PRIMARY       | PRIMARY | 4       | j5.t11.b | 1    | Using where |
      +------+-----------------+------------+--------+---------------+---------+---------+----------+------+-------------+
      

      Attachments

        Issue Links

          Activity

            People

              oleg.smirnov Oleg Smirnov
              psergei Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              7 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.