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

    XMLWordPrintable

    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

            People

            Assignee:
            psergei Sergei Petrunia
            Reporter:
            psergei Sergei Petrunia
            Votes:
            1 Vote for this issue
            Watchers:
            6 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: