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

Condition pushdown into derived table doesn't work if select list uses SP

Details

    Description

      A simplified testcase without any customer data:

      create function myfunc(a int) returns int DETERMINISTIC return (a+1);
      		 
       
      		 
      create table t1 (
      		 
        pk int primary key,
      		 
        a int,
      		 
        b int,
      		 
        key(a)
      		 
      );
      		 
      insert into t1 select seq,seq,seq from seq_1_to_1000;


      create view v1 as 
      select
      		 
        t1.a as col1,
      		 
        myfunc(t1.b) as col2
      		 
      from
      		 
        t1;
      		 
       
      		 
      create view v2 as 
      select
      		 
        t1.a as col1,
      		 
        myfunc(t1.b) as col2
      		 
      from
      		 
        t1;


      create view v3 as 
      select col2, col1 from v1
      		 
      union all
      		 
      select col2, col1 from v2;

      I get this both on 10.5.11 and 10.2.14:

      explain select * from v3 where col1=123;
      		 
      +------+-------------+------------+------+---------------+------+---------+-------+------+-------------+
      		 
      | id   | select_type | table      | type | possible_keys | key  | key_len | ref   | rows | Extra       |
      		 
      +------+-------------+------------+------+---------------+------+---------+-------+------+-------------+
      		 
      |    1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL  | 2    | Using where |
      		 
      |    2 | DERIVED     | t1         | ref  | a             | a    | 5       | const | 1    |             |
      		 
      |    3 | UNION       | t1         | ref  | a             | a    | 5       | const | 1    |             |
      		 
      +------+-------------+------------+------+---------------+------+---------+-------+------+-------------+

      add col2=321 into the WHERE clause and on 10.5.11 I get this:

      explain select * from v3 where col1=123 and col2=321;
      		 
      +------+-------------+------------+------+---------------+------+---------+------+------+-------------+
      		 
      | id   | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra       |
      		 
      +------+-------------+------------+------+---------------+------+---------+------+------+-------------+
      		 
      |    1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL | 2000 | Using where |
      		 
      |    2 | DERIVED     | t1         | ALL  | NULL          | NULL | NULL    | NULL | 1000 |             |
      		 
      |    3 | UNION       | t1         | ALL  | NULL          | NULL | NULL    | NULL | 1000 |             |
      		 
      +------+-------------+------------+------+---------------+------+---------+------+------+-------------+

      while on 10.2.14 table t1 is still accessed through ref access.

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          psergei Sergei Petrunia created issue -
          psergei Sergei Petrunia made changes -
          Field Original Value New Value
          psergei Sergei Petrunia made changes -
          Status Open [ 1 ] In Progress [ 3 ]
          julien.fritsch Julien Fritsch made changes -
          Priority Major [ 3 ] Critical [ 2 ]
          psergei Sergei Petrunia made changes -
          Assignee Sergei Petrunia [ psergey ] Igor Babaev [ igor ]
          Status In Progress [ 3 ] In Review [ 10002 ]
          ccalender Chris Calender (Inactive) made changes -
          Description
          A simplified testcase without any customer data:

          {code:sql}
          create function myfunc(a int) returns int DETERMINISTIC return (a+1);

          create table t1 (
            pk int primary key,
            a int,
            b int,
            key(a)
          );
          insert into t1 select seq,seq,seq from seq_1_to_1000;
          {code}

          {code:sql}
          create view v1 as
          select
            t1.a as col1,
            myfunc(t1.b) as col2
          from
            t1;

          create view v2 as
          select
            t1.a as col1,
            myfunc(t1.b) as col2
          from
            t1;
          {code}

          {code:sql}
          create view v3 as
          select col2, col1 from v1
          union all
          select col2, col1 from v2;
          {code}

          I get this both on 10.5.11 and 10.2.14:

          {code:sql}
          explain select * from v3 where col1=123;
          +------+-------------+------------+------+---------------+------+---------+-------+------+-------------+
          | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
          +------+-------------+------------+------+---------------+------+---------+-------+------+-------------+
          | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
          | 2 | DERIVED | t1 | ref | a | a | 5 | const | 1 | |
          | 3 | UNION | t1 | ref | a | a | 5 | const | 1 | |
          +------+-------------+------------+------+---------------+------+---------+-------+------+-------------+
          {code}

          add col2=321 into the WHERE clause and on 10.5.11 I get this:
          {code:sql}
          explain select * from v3 where col1=123 and col2=321;
          +------+-------------+------------+------+---------------+------+---------+------+------+-------------+
          | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
          +------+-------------+------------+------+---------------+------+---------+------+------+-------------+
          | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2000 | Using where |
          | 2 | DERIVED | t1 | ALL | NULL | NULL | NULL | NULL | 1000 | |
          | 3 | UNION | t1 | ALL | NULL | NULL | NULL | NULL | 1000 | |
          +------+-------------+------------+------+---------------+------+---------+------+------+-------------+
          {code}

          while on 10.2.14 table t1 is still accessed through ref access.

          		A simplified testcase without any customer data:

          {code:sql}
          create function myfunc(a int) returns int DETERMINISTIC return (a+1);

          create table t1 (
            pk int primary key,
            a int,
            b int,
            key(a)
          );
          insert into t1 select seq,seq,seq from seq_1_to_1000;
          {code}

          {code:sql}
          create view v1 as
          select
            t1.a as col1,
            myfunc(t1.b) as col2
          from
            t1;

          create view v2 as
          select
            t1.a as col1,
            myfunc(t1.b) as col2
          from
            t1;
          {code}

          {code:sql}
          create view v3 as
          select col2, col1 from v1
          union all
          select col2, col1 from v2;
          {code}

          I get this both on 10.5.11 and 10.2.14:

          {code:sql}
          explain select * from v3 where col1=123;
          +------+-------------+------------+------+---------------+------+---------+-------+------+-------------+
          | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
          +------+-------------+------------+------+---------------+------+---------+-------+------+-------------+
          | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
          | 2 | DERIVED | t1 | ref | a | a | 5 | const | 1 | |
          | 3 | UNION | t1 | ref | a | a | 5 | const | 1 | |
          +------+-------------+------------+------+---------------+------+---------+-------+------+-------------+
          {code}

          add col2=321 into the WHERE clause and on 10.5.11 I get this:
          {code:sql}
          explain select * from v3 where col1=123 and col2=321;
          +------+-------------+------------+------+---------------+------+---------+------+------+-------------+
          | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
          +------+-------------+------------+------+---------------+------+---------+------+------+-------------+
          | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2000 | Using where |
          | 2 | DERIVED | t1 | ALL | NULL | NULL | NULL | NULL | 1000 | |
          | 3 | UNION | t1 | ALL | NULL | NULL | NULL | NULL | 1000 | |
          +------+-------------+------------+------+---------------+------+---------+------+------+-------------+
          {code}

          while on 10.2.14 table t1 is still accessed through ref access.

          allen.lee@mariadb.com Allen Lee (Inactive) made changes -
          allen.lee@mariadb.com Allen Lee (Inactive) made changes -
          igor Igor Babaev (Inactive) made changes -
          Status In Review [ 10002 ] Stalled [ 10000 ]
          allen.lee@mariadb.com Allen Lee (Inactive) made changes -
          igor Igor Babaev (Inactive) made changes -
          Assignee Igor Babaev [ igor ] Sergei Petrunia [ psergey ]
          julien.fritsch Julien Fritsch made changes -
          julien.fritsch Julien Fritsch made changes -
          psergei Sergei Petrunia made changes -
          Fix Version/s 10.2.40 [ 26027 ]
          Fix Version/s 10.3.31 [ 26028 ]
          Fix Version/s 10.4.21 [ 26030 ]
          Fix Version/s 10.5.12 [ 26025 ]
          Fix Version/s 10.6.3 [ 25904 ]
          Fix Version/s 10.5 [ 23123 ]
          Resolution Fixed [ 1 ]
          Status Stalled [ 10000 ] Closed [ 6 ]
          ralf.gebhardt Ralf Gebhardt made changes -
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 122858 ] MariaDB v4 [ 159421 ]
          mariadb-jira-automation Jira Automation (IT) made changes -
          Zendesk Related Tickets 179395

          People

            psergei Sergei Petrunia
            psergei Sergei Petrunia
            Votes:
            1 Vote for this issue
            Watchers:
            6 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.