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.

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?

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

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.

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 ?

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/

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