Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-8308

Implement SQL standard Common Table Expression (CTE) support

Details

    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;

      Attachments

        Issue Links

          duplicates

          Task - A task that needs to be done. MDEV-9956 suport hierachy query (connect by)

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-10116 Equivalent of DB2 WITH statement

          • Major - Major loss of function.
          • Closed
          is part of

          Epic - Created by Jira Software - do not edit or delete. Issue type for a big user story that needs to be broken down. MDEV-10137 Providing compatibility to other databases

          • Major - Major loss of function.
          • Open

          Activity

            Ascending order - Click to sort in descending order
            colin Colin Charles added a comment -

            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.

            colin Colin Charles added a comment - 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.
            Bytesmiths Jan Steinman added a comment - - edited

            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.

            Bytesmiths Jan Steinman added a comment - - edited 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.
            serg Sergei Golubchik added a comment -

            Bytesmiths, see MDEV-9864

            serg Sergei Golubchik added a comment - Bytesmiths , see MDEV-9864

            People

              igor Igor Babaev
              GeoffMontee Geoff Montee (Inactive)
              Votes:
              18 Vote for this issue
              Watchers:
              15 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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