[MDEV-37220] Allow UPDATE/DELETE to read from a CTE - Jira

XML

Word

Printable

Release Note Summary:

Hide
UPDATE and DELETE statements now also allow the inclusion of a Common Table Expression (CTE) prior to the UPDATE or DELETE statement
* UPDATE/DELETE can now read from a CTE
* This is an extension from the SQL standard which is also available in other DBMS
* These CTEs are read only like other derived tables, so
cannot have their columns updated in updates set clause, nor have rows
removed in the delete statement.

Show
UPDATE and DELETE statements now also allow the inclusion of a Common Table Expression (CTE) prior to the UPDATE or DELETE statement * UPDATE/DELETE can now read from a CTE * This is an extension from the SQL standard which is also available in other DBMS * These CTEs are read only like other derived tables, so cannot have their columns updated in updates set clause, nor have rows removed in the delete statement.

This is a part of MDEV-18511.

The most common use case in it is when the UPDATE/DELETE reads from a CTE but doesn't change it.
This should be relatively easy to implement.

is part of

MDEV-18511 CTE support for UPDATE and DELETE statements

relates to

MDEV-38258 No error thrown when CTE columns updated in updates set clause

MDEV-38259 Assertion `table_share->tmp_table || m_lock_type == 1' failed

MDEV-38272 Sig11 in LEX::resolve_references_to_cte at sql/sql_cte.cc

MDEV-38295 Recursive CTE usage leaks memory when not used in a top level select

(1 has action item)

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