Details
-
Task
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
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
- blocks
-
MDEV-18511 CTE support for UPDATE and DELETE statements
-
- Stalled
-
- relates to
-
-
- Open
-