[MDEV-18511] CTE support for UPDATE and DELETE statements - Jira

Daniel Black created issue - 2019-02-08 00:56

Sergei Golubchik made changes - 2019-03-05 18:34

Field	Original Value	New Value
Labels	upstream-fixed	gsoc19 upstream-fixed

Sergei Golubchik made changes - 2019-03-05 18:34

Assignee

Igor Babaev [ igor ]

Sergei Golubchik made changes - 2019-03-09 16:48

Component/s

Optimizer [ 10200 ]

Vicențiu Ciorbaru made changes - 2020-02-04 14:36

Labels

gsoc19 upstream-fixed

gsoc19 gsoc20 upstream-fixed

Alice Sherepa made changes - 2020-04-29 10:39

Link

This issue is duplicated by ~~MDEV-22405~~ [ ~~MDEV-22405~~ ]

Sergei Petrunia made changes - 2020-07-22 08:53

Fix Version/s

10.6 [ 24028 ]

Sergei Petrunia made changes - 2020-07-22 08:55

Description

{code:sql}
     CREATE TABLE tree (
       `Node` VARCHAR(3),
       `ParentNode` VARCHAR(3),
       `EmployeeID` INTEGER,
       `Depth` INTEGER,
       `Lineage` VARCHAR(16)
     );

     INSERT INTO tree
       (`Node`, `ParentNode`, `EmployeeID`, `Depth`, `Lineage`)
     VALUES
       ('100', NULL, '1001', 0, '/'),
       ('101', '100', '1002', NULL, NULL),
       ('102', '101', '1003', NULL, NULL),
       ('103', '102', '1004', NULL, NULL),
       ('104', '102', '1005', NULL, NULL),
       ('105', '102', '1006', NULL, NULL);

     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 tree t JOIN prev p ON t.ParentNode = p.Node
     )
     SELECT * FROM prev;
     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
     )
     UPDATE tree t, prev p SET t.Depth=p.Depth, t.Lineage=p.Lineage WHERE t.Node=p.Node;

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UPDATE tree t, prev p SET t.Depth=p.Depth, t.Lineage=p.Lineage WHERE t.Node=p.No' at line 7
{code}

[db<>fiddle here|https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=a28ed3cabd68bac75941eba5973fd50a]*

[supported in MySQL-8.0|https://dev.mysql.com/doc/refman/8.0/en/with.html] and [MSSQL|(https://stackoverflow.com/questions/54583713/mysql-while-exists-from-a-snippet-throws-error/54584556#54584556]

Sergei Petrunia made changes - 2020-07-28 15:22

Summary

Recursive CTE support for UPDATE (and DELETE) statements

CTE support for UPDATE (and DELETE) statements

Sergei Petrunia made changes - 2020-07-28 17:21

Status

Open [ 1 ]

Confirmed [ 10101 ]

Igor Babaev (Inactive) made changes - 2020-08-21 16:13

Summary

CTE support for UPDATE (and DELETE) statements

CTE support for UPDATE and DELETE statements

Igor Babaev (Inactive) made changes - 2020-08-24 05:47

Link

This issue is blocked by MDEV-23552 [ MDEV-23552 ]

Sergei Petrunia made changes - 2020-09-09 12:48

Link

This issue includes ~~MDEV-21012~~ [ ~~MDEV-21012~~ ]

Sergei Petrunia made changes - 2020-09-09 12:54

Link

This issue includes ~~MDEV-22415~~ [ ~~MDEV-22415~~ ]

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

Rank

Ranked higher

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

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Ralf Gebhardt made changes - 2021-03-25 09:47

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

Alice Sherepa made changes - 2021-08-24 15:30

Link

This issue relates to ~~MDEV-26470~~ [ ~~MDEV-26470~~ ]

Alice Sherepa made changes - 2021-08-24 15:31

Link

This issue relates to ~~MDEV-26469~~ [ ~~MDEV-26469~~ ]

Ralf Gebhardt made changes - 2021-10-12 21:09

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

Alice Sherepa made changes - 2021-11-19 11:23

Link

This issue relates to ~~MDEV-26470~~ [ ~~MDEV-26470~~ ]

Alice Sherepa made changes - 2021-11-19 11:24

Link

This issue relates to ~~MDEV-26469~~ [ ~~MDEV-26469~~ ]

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

Workflow

MariaDB v3 [ 92362 ]

MariaDB v4 [ 131832 ]

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 ]

Julien Fritsch made changes - 2022-04-14 17:34

Description

{code:sql}
     CREATE TABLE tree (
       `Node` VARCHAR(3),
       `ParentNode` VARCHAR(3),
       `EmployeeID` INTEGER,
       `Depth` INTEGER,
       `Lineage` VARCHAR(16)
     );

     INSERT INTO tree
       (`Node`, `ParentNode`, `EmployeeID`, `Depth`, `Lineage`)
     VALUES
       ('100', NULL, '1001', 0, '/'),
       ('101', '100', '1002', NULL, NULL),
       ('102', '101', '1003', NULL, NULL),
       ('103', '102', '1004', NULL, NULL),
       ('104', '102', '1005', NULL, NULL),
       ('105', '102', '1006', NULL, NULL);

     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 tree t JOIN prev p ON t.ParentNode = p.Node
     )
     SELECT * FROM prev;
     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
     )
     UPDATE tree t, prev p SET t.Depth=p.Depth, t.Lineage=p.Lineage WHERE t.Node=p.Node;

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UPDATE tree t, prev p SET t.Depth=p.Depth, t.Lineage=p.Lineage WHERE t.Node=p.No' at line 7
{code}

[db<>fiddle here|https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=a28ed3cabd68bac75941eba5973fd50a]*

[supported in MySQL-8.0|https://dev.mysql.com/doc/refman/8.0/en/with.html] and [MSSQL|(https://stackoverflow.com/questions/54583713/mysql-while-exists-from-a-snippet-throws-error/54584556#54584556]

{code:sql}
     CREATE TABLE tree (
       `Node` VARCHAR(3),
       `ParentNode` VARCHAR(3),
       `EmployeeID` INTEGER,
       `Depth` INTEGER,
       `Lineage` VARCHAR(16)
     );

     INSERT INTO tree
       (`Node`, `ParentNode`, `EmployeeID`, `Depth`, `Lineage`)
     VALUES
       ('100', NULL, '1001', 0, '/'),
       ('101', '100', '1002', NULL, NULL),
       ('102', '101', '1003', NULL, NULL),
       ('103', '102', '1004', NULL, NULL),
       ('104', '102', '1005', NULL, NULL),
       ('105', '102', '1006', NULL, NULL);

     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 tree t JOIN prev p ON t.ParentNode = p.Node
     )
     SELECT * FROM prev;
     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
     )
     UPDATE tree t, prev p SET t.Depth=p.Depth, t.Lineage=p.Lineage WHERE t.Node=p.Node;

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UPDATE tree t, prev p SET t.Depth=p.Depth, t.Lineage=p.Lineage WHERE t.Node=p.No' at line 7
{code}

[db<>fiddle here|https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=a28ed3cabd68bac75941eba5973fd50a]\*

[supported in MySQL\-8.0|https://dev.mysql.com/doc/refman/8.0/en/with.html] and [MSSQL|(https://stackoverflow.com/questions/54583713/mysql-while-exists-from-a-snippet-throws-error/54584556#54584556]

Julien Fritsch made changes - 2022-04-14 18:32

Description

{code:sql}
     CREATE TABLE tree (
       `Node` VARCHAR(3),
       `ParentNode` VARCHAR(3),
       `EmployeeID` INTEGER,
       `Depth` INTEGER,
       `Lineage` VARCHAR(16)
     );

     INSERT INTO tree
       (`Node`, `ParentNode`, `EmployeeID`, `Depth`, `Lineage`)
     VALUES
       ('100', NULL, '1001', 0, '/'),
       ('101', '100', '1002', NULL, NULL),
       ('102', '101', '1003', NULL, NULL),
       ('103', '102', '1004', NULL, NULL),
       ('104', '102', '1005', NULL, NULL),
       ('105', '102', '1006', NULL, NULL);

     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 tree t JOIN prev p ON t.ParentNode = p.Node
     )
     SELECT * FROM prev;
     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
     )
     UPDATE tree t, prev p SET t.Depth=p.Depth, t.Lineage=p.Lineage WHERE t.Node=p.Node;

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UPDATE tree t, prev p SET t.Depth=p.Depth, t.Lineage=p.Lineage WHERE t.Node=p.No' at line 7
{code}

[db<>fiddle here|https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=a28ed3cabd68bac75941eba5973fd50a]\*

[supported in MySQL\-8.0|https://dev.mysql.com/doc/refman/8.0/en/with.html] and [MSSQL|(https://stackoverflow.com/questions/54583713/mysql-while-exists-from-a-snippet-throws-error/54584556#54584556]

{code:sql}
     CREATE TABLE tree (
       `Node` VARCHAR(3),
       `ParentNode` VARCHAR(3),
       `EmployeeID` INTEGER,
       `Depth` INTEGER,
       `Lineage` VARCHAR(16)
     );

     INSERT INTO tree
       (`Node`, `ParentNode`, `EmployeeID`, `Depth`, `Lineage`)
     VALUES
       ('100', NULL, '1001', 0, '/'),
       ('101', '100', '1002', NULL, NULL),
       ('102', '101', '1003', NULL, NULL),
       ('103', '102', '1004', NULL, NULL),
       ('104', '102', '1005', NULL, NULL),
       ('105', '102', '1006', NULL, NULL);

     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 tree t JOIN prev p ON t.ParentNode = p.Node
     )
     SELECT * FROM prev;
     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
     )
     UPDATE tree t, prev p SET t.Depth=p.Depth, t.Lineage=p.Lineage WHERE t.Node=p.Node;

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UPDATE tree t, prev p SET t.Depth=p.Depth, t.Lineage=p.Lineage WHERE t.Node=p.No' at line 7
{code}

[db<>fiddle here|https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=a28ed3cabd68bac75941eba5973fd50a]*

[supported in MySQL-8.0|https://dev.mysql.com/doc/refman/8.0/en/with.html] and [MSSQL|(https://stackoverflow.com/questions/54583713/mysql-while-exists-from-a-snippet-throws-error/54584556#54584556]

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

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

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

Description

{code:sql}
     CREATE TABLE tree (
       `Node` VARCHAR(3),
       `ParentNode` VARCHAR(3),
       `EmployeeID` INTEGER,
       `Depth` INTEGER,
       `Lineage` VARCHAR(16)
     );

     INSERT INTO tree
       (`Node`, `ParentNode`, `EmployeeID`, `Depth`, `Lineage`)
     VALUES
       ('100', NULL, '1001', 0, '/'),
       ('101', '100', '1002', NULL, NULL),
       ('102', '101', '1003', NULL, NULL),
       ('103', '102', '1004', NULL, NULL),
       ('104', '102', '1005', NULL, NULL),
       ('105', '102', '1006', NULL, NULL);

     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 tree t JOIN prev p ON t.ParentNode = p.Node
     )
     SELECT * FROM prev;
     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
     )
     UPDATE tree t, prev p SET t.Depth=p.Depth, t.Lineage=p.Lineage WHERE t.Node=p.Node;

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UPDATE tree t, prev p SET t.Depth=p.Depth, t.Lineage=p.Lineage WHERE t.Node=p.No' at line 7
{code}

[db<>fiddle here|https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=a28ed3cabd68bac75941eba5973fd50a]*

[supported in MySQL-8.0|https://dev.mysql.com/doc/refman/8.0/en/with.html] and [MSSQL|(https://stackoverflow.com/questions/54583713/mysql-while-exists-from-a-snippet-throws-error/54584556#54584556]

{code:sql}
     CREATE TABLE tree (
       `Node` VARCHAR(3),
       `ParentNode` VARCHAR(3),
       `EmployeeID` INTEGER,
       `Depth` INTEGER,
       `Lineage` VARCHAR(16)
     );

     INSERT INTO tree
       (`Node`, `ParentNode`, `EmployeeID`, `Depth`, `Lineage`)
     VALUES
       ('100', NULL, '1001', 0, '/'),
       ('101', '100', '1002', NULL, NULL),
       ('102', '101', '1003', NULL, NULL),
       ('103', '102', '1004', NULL, NULL),
       ('104', '102', '1005', NULL, NULL),
       ('105', '102', '1006', NULL, NULL);

     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 tree t JOIN prev p ON t.ParentNode = p.Node
     )
     SELECT * FROM prev;
     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
     )
     UPDATE tree t, prev p SET t.Depth=p.Depth, t.Lineage=p.Lineage WHERE t.Node=p.Node;

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UPDATE tree t, prev p SET t.Depth=p.Depth, t.Lineage=p.Lineage WHERE t.Node=p.No' at line 7
{code}

[db<>fiddle here|https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=a28ed3cabd68bac75941eba5973fd50a]\*

[supported in MySQL\-8.0|https://dev.mysql.com/doc/refman/8.0/en/with.html] and [MSSQL|(https://stackoverflow.com/questions/54583713/mysql-while-exists-from-a-snippet-throws-error/54584556#54584556]

Ralf Gebhardt made changes - 2023-04-13 11:51

Fix Version/s

10.11 [ 27614 ]

Ralf Gebhardt made changes - 2023-04-13 11:52

Assignee

Igor Babaev [ igor ]

Ralf Gebhardt [ ralf.gebhardt@mariadb.com ]

Ralf Gebhardt made changes - 2023-04-13 11:52

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Ralf Gebhardt made changes - 2023-04-13 11:52

Assignee

Ralf Gebhardt [ ralf.gebhardt@mariadb.com ]

Igor Babaev [ igor ]

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

Fix Version/s

11.2 [ 28603 ]

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

Priority

Major [ 3 ]

Critical [ 2 ]

Ralf Gebhardt made changes - 2023-08-03 07:44

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

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

Link

This issue blocks ~~MDEV-22415~~ [ ~~MDEV-22415~~ ]

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

Link

This issue includes ~~MDEV-22415~~ [ ~~MDEV-22415~~ ]

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

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

Ralf Gebhardt made changes - 2023-10-23 14:11

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 ]

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

Priority

Critical [ 2 ]

Major [ 3 ]

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

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 ]

Jira Automation (IT) made changes - 2024-07-04 04:03

Zendesk Related Tickets		201775 202044
Zendesk active tickets		201775

Yuchen Pei made changes - 2024-07-23 00:04

Link

This issue blocks ~~MDEV-22415~~ [ ~~MDEV-22415~~ ]

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.9 [ 29945 ]
Fix Version/s	11.8 [ 29921 ]

Ralf Gebhardt made changes - 2025-01-10 15:14

Fix Version/s

11.9 [ 29945 ]

Michael Widenius made changes - 2025-02-12 12:38

Status

Stalled [ 10000 ]

Open [ 1 ]

Igor Babaev (Inactive) made changes - 2025-02-15 04:30

Link

This issue includes ~~MDEV-21012~~ [ ~~MDEV-21012~~ ]

Igor Babaev (Inactive) made changes - 2025-03-14 00:30

Fix Version/s		12.1 [ 29992 ]
Labels	gsoc19 gsoc20 upstream-fixed

Igor Babaev (Inactive) made changes - 2025-03-14 00:30

Status

Open [ 1 ]

In Progress [ 3 ]

Igor Babaev (Inactive) made changes - 2025-03-14 00:36

Link

This issue relates to MDEV-36293 [ MDEV-36293 ]

Sergei Golubchik made changes - 2025-03-14 07:27

Labels

gsoc19 gsoc20 upstream-fixed

Julien Fritsch made changes - 2025-03-28 14:53

Assignee

Igor Babaev [ igor ]

Sergei Petrunia [ psergey ]

Julien Fritsch made changes - 2025-04-02 17:22

Sprint

Server 12.1 dev sprint [ 793 ]

Julien Fritsch made changes - 2025-04-07 13:36

Fix Version/s		12.2 [ 30146 ]
Fix Version/s	12.1 [ 29992 ]

Julien Fritsch made changes - 2025-04-07 13:36

Sprint

Server 12.1 dev sprint [ 793 ]

Julien Fritsch made changes - 2025-04-07 15:00

Status

In Progress [ 3 ]

Stalled [ 10000 ]

MariaDB Server

CTE support for UPDATE and DELETE statements

Details

Description

Attachments

Issue Links

Sub-Tasks

Activity

People

Dates

Git Integration