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

Merge mergeable derived tables used at the top level of UPDATE statements

    XMLWordPrintable

    Details

      Description

      Currently in MariaDB derived tables used at the top level of UPDATE (and DELETE) statements are always materialized. As a result the base tables underlying a mergeable derived table cannot be updated while the mergeable view specified with the same SELECT expression as the derived table can be updated.

      MariaDB [test]> update (select * from t1 where a < 3) as t set a=14;
      ERROR 1288 (HY000): The target table t of the UPDATE is not updatable
       
      MariaDB [test]> update (select a from t1 where a < 3) as t, t2 set t.a=10 where t.a=t2.b;
      ERROR 1288 (HY000): The target table t of the UPDATE is not updatable
       
      MariaDB [test]> create view v1 as (select a from t1 where a < 3);
       
      MariaDB [test]> explain update v1 set a=14; 
      +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
      +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
      |    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 4    | Using where |
      +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
       
      MariaDB [test]> explain update v1 as t, t2 set t.a=10 where t.a=t2.b;
      +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
      +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
      |    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 4    | Using where |
      |    1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL | 5    | Using where |
      +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
      
      

      The goal of this task is to change the current code to allow updating mergeable derived tables. When this task is finished we'll have:

      MariaDB [test]> explain update (select * from t1 where a < 3) as t set a=14;
      +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
      +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
      |    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 4    | Using where |
      +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
       
      MariaDB [test]> explain update (select a from t1 where a < 3) as t, t2 set t.a=10 where t.a=t2.b;
      +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
      +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
      |    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 4    | Using where |
      |    1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL | 5    | Using where |
      +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              igor Igor Babaev
              Reporter:
              igor Igor Babaev
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration