[MDEV-23552] Merge mergeable derived tables used at the top level of UPDATE statements - Jira

Details

Type: Task
Status: Stalled (View Workflow)
Priority: Major
Resolution: Unresolved
Fix Version/s: 12.0
Component/s: Data Manipulation - Update
Labels:
- tech_debt

Description

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

Open

Activity

Ascending order - Click to sort in descending order

Igor Babaev created issue - 2020-08-24 05:47

Igor Babaev made changes - 2020-08-24 05:47

Field	Original Value	New Value
Link		This issue blocks MDEV-18511 [ MDEV-18511 ]

Sergei Golubchik made changes - 2020-09-09 17:20

Rank

Ranked higher

Sergei Golubchik made changes - 2020-09-09 18:27

Status

Open [ 1 ]

In Progress [ 3 ]

Sergei Petrunia made changes - 2021-04-27 16:18

Fix Version/s		10.7 [ 24805 ]
Fix Version/s	10.6 [ 24028 ]

Ralf Gebhardt made changes - 2021-10-08 15:44

Fix Version/s		10.8 [ 26121 ]
Fix Version/s	10.7 [ 24805 ]

Sergei Golubchik made changes - 2021-12-06 21:22

Workflow

MariaDB v3 [ 112841 ]

MariaDB v4 [ 131848 ]

Sergei Golubchik made changes - 2022-02-01 11:33

Fix Version/s		10.9 [ 26905 ]
Fix Version/s	10.8 [ 26121 ]

Sergei Golubchik made changes - 2022-04-06 10:48

Fix Version/s		10.10 [ 27530 ]
Fix Version/s	10.9 [ 26905 ]

Ralf Gebhardt made changes - 2022-06-23 08:23

Fix Version/s		10.11 [ 27614 ]
Fix Version/s	10.10 [ 27530 ]

AirFocus made changes - 2022-08-09 16:10

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.
{noformat}
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 |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+

{noformat}
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:
{noformat}
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 |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
{noformat}

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.

{noformat}
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 |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+

{noformat}

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:

{noformat}
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 |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
{noformat}

Julien Fritsch made changes - 2022-08-11 09:36

Priority

Major [ 3 ]

Critical [ 2 ]

Julien Fritsch made changes - 2023-05-04 09:58

Fix Version/s		11.2 [ 28603 ]
Fix Version/s	10.11 [ 27614 ]

Julien Fritsch made changes - 2023-05-04 10:03

Priority

Critical [ 2 ]

Major [ 3 ]

Julien Fritsch made changes - 2023-05-04 16:41

Priority

Major [ 3 ]

Critical [ 2 ]

Ralf Gebhardt made changes - 2023-07-25 19:48

Fix Version/s		11.3 [ 28565 ]
Fix Version/s	11.2 [ 28603 ]

Sergei Golubchik made changes - 2023-08-16 20:37

Priority

Critical [ 2 ]

Major [ 3 ]

Julien Fritsch made changes - 2023-08-17 16:48

Priority

Major [ 3 ]

Critical [ 2 ]

Sergei Golubchik made changes - 2023-09-17 18:02

Fix Version/s		11.4 [ 29301 ]
Fix Version/s	11.3 [ 28565 ]

Sergei Golubchik made changes - 2023-10-30 12:59

Fix Version/s		11.5 [ 29506 ]
Fix Version/s	11.4 [ 29301 ]

Julien Fritsch made changes - 2023-11-30 16:29

Issue Type

Task [ 3 ]

New Feature [ 2 ]

Julien Fritsch made changes - 2023-12-07 10:26

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Sergei Golubchik made changes - 2024-01-30 16:24

Priority

Critical [ 2 ]

Major [ 3 ]

Ralf Gebhardt made changes - 2024-01-30 16:56

Fix Version/s		11.6 [ 29515 ]
Fix Version/s	11.5 [ 29506 ]

Ralf Gebhardt made changes - 2024-06-21 09:21

Fix Version/s		11.7 [ 29815 ]
Fix Version/s	11.6 [ 29515 ]

Julien Fritsch made changes - 2024-07-02 16:39

Issue Type

New Feature [ 2 ]

Task [ 3 ]

Ralf Gebhardt made changes - 2024-07-09 08:46

Labels

tech_debt

Sergei Golubchik made changes - 2024-09-24 13:54

Fix Version/s		11.8 [ 29921 ]
Fix Version/s	11.7 [ 29815 ]

Sergei Golubchik made changes - 2024-11-22 08:16

Fix Version/s

11.8 [ 29921 ]

Sergei Golubchik made changes - 2024-11-22 08:16

Fix Version/s

11.9 [ 29945 ]

People

Assignee:: Igor Babaev

Reporter:: Igor Babaev

Votes:: 1 Vote for this issue

Watchers:: 8 Start watching this issue

Dates

Created:: 2020-08-24 05:47

Updated:: 2024-11-22 08:16

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration