[MDEV-8308] Implement SQL standard Common Table Expression (CTE) support Created: 2015-06-12  Updated: 2018-08-31  Resolved: 2016-10-08

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Subquery, Parser
Fix Version/s: 10.2.2

Type: Task Priority: Major
Reporter: Geoff Montee (Inactive) Assignee: Igor Babaev
Resolution: Fixed Votes: 18
Labels: Compatibility

Issue Links:
Duplicate
duplicates MDEV-9956 suport hierachy query (connect by) Closed
duplicates MDEV-10116 Equivalent of DB2 WITH statement Closed
PartOf
is part of MDEV-10137 Providing compatibility to other data... Open
Sub-Tasks:
Key
 Summary
 Type
 Status
 Assignee
MDEV-9864 Implement recursive common table expr... Technical task Closed Igor Babaev  
MDEV-8789 Implement non-recursive common table ... Technical task Closed Igor Babaev  
Epic Link: Oracle Compatibility

 Description   

It would be great if MariaDB supported SQL standard Common Table Expressions (CTEs).

WITH moved_rows AS (
 
    DELETE FROM products
 
    WHERE
 
        "date" >= '2010-10-01' AND
 
        "date" < '2010-11-01'
 
    RETURNING *
 
)
 
INSERT INTO products_log
 
SELECT * FROM moved_rows;



 Comments   
Comment by Colin Charles [ 2015-06-13 ]

I spoke to D. Richard Hipp (creator of SQLite), and he too would like to see CTE support. We kind of have some of the parts to make this work – virtual columns, DELETE ... RETURNING.

Comment by Jan Steinman [ 2016-02-29 ]

Please make it recursive, like the standard!

Without WITH RECURSIVE, I don't think CTEs buy much.

Currently, there seem to be a half-dozen hacks for imposing full graphs onto MySQL, all incompatible, most requiring distorting the data model to support arbitrary-depth hierarchy. OQGRAPH looked nice, but apparently has little or no support. (I have been unable to get OQGRAPH to compile on MacOS, and no one replies to my help requests.)

My #1 priority is useful, built-in, arbitrary-depth hierarchy queries.

Comment by Sergei Golubchik [ 2016-04-02 ]

Bytesmiths, see MDEV-9864

Generated at Thu Feb 08 07:26:10 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.