Emily Bowman added a comment - 2020-01-24 05:33 This needs to be called out in the documentation for UPDATE & DELETE. Coming from a MySQL project to MariaDB I expected this to work and banged my head on it for an hour, before a lucky Stack Overflow find led me to this bug. Fortunately I have permission to create & drop temporary tables, but not everyone will.

Rahul Anand added a comment - 2020-03-03 10:26 Hi Igor, I would like to work on this feature as my GSoC '20 project. I have already setup the development environment and trying to get the familiar codebase. To best of my understanding, the first thing would be to extend the parser which is coded in 'server/sql_yacc.yy' and I understand how a basic parser works. So, what should be my next steps? Also, I was not able to find you on zulip and was wondering which form of communication would be most suitable for you regarding this project?

Rahul Anand added a comment - 2020-06-08 17:01 8th July 2020 Report – Updatability of tests in main.view with merging allowed for multi-update. https://www.notion.so/8-June-2020-02635b89b39842bc97121105c05ac7d1 All reports can be seen here: https://www.notion.so/GSOC-Reports-be798f73587845279116e8a4b2d0fcec

Igor Babaev (Inactive) added a comment - 2020-08-22 02:43 To add <with clauses> to UPDATE/DELETE statements in the grammar is an easy task. Yet processing UPDATE/DELETE statements with CTEs at the top level is not easy for the following reasons. In MariaDB references of CTEs in SELECT statements are always considered similar to a references to derived tables (unnamed views). After identification of an non-recursive CTE reference it is processed exactly in the same way as a reference to a derived table. If the specification of a CTE is mergeable then a reference to the CTE is processed as a mergeable derived table with the same specification. If the specification of a CTE isn't mergeable then a reference to the CTE is processed as a non-mergeable derived table with same specification. The current implementation of the UPDATE/DELETE always processes references to derived tables used in the main unit as references to non-mergeable derived tables. As a result it's not possible to update mergeable derived tables. Here are the examples: MariaDB [test]> update (select * from t1) 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 When a multi-table UPDATE statement does not update the used mergeable derived table the table is still materialized. ariaDB [test]> explain update (select a from t1 where a < 3) as t, t2 set t2.b=10 where t.a=t2.b; +------+-------------+------------+------+---------------+------+---------+-----------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+------+---------------+------+---------+-----------+------+-------------+ | 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 5 | Using where | | 1 | PRIMARY | <derived2> | ref | key0 | key0 | 5 | test.t2.b | 2 | | | 2 | DERIVED | t1 | ALL | NULL | NULL | NULL | NULL | 4 | Using where | +------+-------------+------------+------+---------------+------+---------+-----------+------+-------------+ 3 rows in set (0.002 sec) We don't have such problems with mergeable views: 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 | +------+-------------+-------+------+---------------+------+---------+------+------+-------------+   MariaDB [test]> explain update v1 as t, t2 set t2.b=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 | +------+-------------+-------+------+---------------+------+---------+------+------+-------------+ So it's quite possible to merge the mergeable derived tables used at the top level of UPDATE/DELETE statements rather then materialize them.

Thierry Giguere added a comment - 2022-10-17 16:49 I'm not sure to understand but the following works for sure in MariaDb 10.6.10 UPDATE tree t, ( WITH RECURSIVE prev AS ( SELECT * FROM tree WHERE ParentNode IS NULL UNION SELECT t.Node,t.ParentNode,t.EmployeeID,p.Depth + 1 as Depth, CONCAT(p.Lineage, t.ParentNode, '/') FROM prev p JOIN tree t ON t.ParentNode = p.Node ) SELECT * FROM prev ) q SET t.Depth=q.Depth, t.Lineage=q.Lineage WHERE t.Node=q.Node; SELECT * FROM tree; Node ParentNode EmployeeID Depth Lineage ---- ---------- ---------- ----- ------------- 100 <null> 1001 0 / 101 100 1002 1 /100/ 102 101 1003 2 /100/101/ 103 102 1004 3 /100/101/102/ 104 102 1005 3 /100/101/102/ 105 102 1006 3 /100/101/102/ Is it a supported syntax ? What is the difference with the SQL at the center of the discussion ? Only the fact that CTE is "outside" the update statement ?

Valerii Kravchuk added a comment - 2023-04-12 11:13 Is 10.11 as a fix version still correct? Now that we have 10.11.2 released as GA: https://mariadb.com/kb/en/mariadb-10-11-2-release-notes/

Ralf Gebhardt added a comment - 2023-04-13 11:51 valerii no, it is not. psergei and igor , can you estimate how much work is still needed to get this task done? I will change the status to stalled for now and will remove the fixVersion until we have a better idea about the task/open effort. It does not make sense to just add 11.2 as fixVersion for now, as this does not seem to be realistic

PierreCar added a comment - 2024-05-02 15:03 For people this bang their head on the wall in 2024 with this long time issue not resolved, like me, I finally found a solution that is not perfect but works, use a temporary table to save CTE result then update the main table. In the example above : CREATE TEMPORARY TABLE tmp_table WITH RECURSIVE prev AS ( SELECT * FROM tree WHERE ParentNode IS NULL UNION SELECT t.Node,t.ParentNode,t.EmployeeID,p.Depth + 1 as Depth, CONCAT(p.Lineage, t.ParentNode, '/') FROM prev p JOIN tree t ON t.ParentNode = p.Node ) SELECT * FROM prev;   UPDATE tree t, tmp_table p SET t.Depth=p.Depth, t.Lineage=p.Lineage WHERE t.Node=p.Node;

Sergei Petrunia added a comment - 2025-02-12 13:32 - edited Note: the most common use case is when the UPDATE modifies a base table, not the CTE. This is what the examples in this MDEV do. It is also possible to think of UPDATE changing the CTE, which translates into changes to the table used in the CTE. Similar to updatable VIEWs. Example (also at https://dbfiddle.uk/Q1p7ImR2 ) create table t1 (pk int primary key , a int ); insert into t1 values (1,1),(2,2),(3,3),(4,4); with T as ( select pk, a from t1 where pk <3 ) update T set a=a*1000; select * from t1; pk a 1 1000 2 2000 3 3 4 4 SQL Server supports this, PostgreSQL doesn't. I haven't checked what the SQL Standard says about this. I think update-through-CTE should be outside of scope of this MDEV.

Igor Babaev (Inactive) added a comment - 2025-02-13 02:15 - edited A preliminary patch for this task that implemented UPDATE command with updatable CTEs was pushed into 10.5-mdev-18511 in October 2020. The patch contains a lot of test cases demonstrating how the feature worked for 10.5. Actually it was the patch for MDEV-23552 . Unfortunately the code cannot be applied directly to the current 10.4+ because the upper level architecture for UPDATE/DELETE has been changed by MDEV-28883 . However CTE related code was incorporated into 10.5 when the CTE implementation was redesigned. One of the reason why MDEV-28883 was initiated was the fact that I experienced serious difficulties with implementation of MDEV-23552 . Even more difficulties were expected with implementation of a similar task for DELETE commands. serg , I've looked at at the support case 201775 and I don't understand what the ticket has to do with MDEV-18511 . I also don't understand why MDEV-21012 is considered as a sub-task of this task.

Igor Babaev (Inactive) added a comment - 2025-02-13 02:40 - edited MYSQL 8.0 does not allow update updatable CTE/derived tables. MYSQL 8.0 merges mergeable derived tables used in multi-update/multi-delete. MariaDB always materializes such derived tables.