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
            GeoffMontee Geoff Montee (Inactive) created issue -
            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.
            serg Sergei Golubchik made changes -
            Field Original Value New Value
            Fix Version/s 10.2 [ 14601 ]
            psergei Sergei Petrunia made changes -
            psergei Sergei Petrunia made changes -
            Fix Version/s 10.2 [ 14601 ]
            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 made changes -
            serg Sergei Golubchik added a comment -

            Bytesmiths, see MDEV-9864

            serg Sergei Golubchik added a comment - Bytesmiths , see MDEV-9864
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            monty Michael Widenius made changes -
            alvinr Alvin Richards (Inactive) made changes -
            Labels Compatibility
            monty Michael Widenius made changes -
            monty Michael Widenius made changes -
            alvinr Alvin Richards (Inactive) made changes -
            Epic Link MDEV-10872 [ 58182 ]
            serg Sergei Golubchik made changes -
            Assignee Igor Babaev [ igor ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.2.2 [ 22013 ]
            Fix Version/s 10.2 [ 14601 ]
            Resolution Fixed [ 1 ]
            Status Open [ 1 ] Closed [ 6 ]
            alvinr Alvin Richards (Inactive) made changes -
            Labels Compatibility Compatibility NRE-307517
            alvinr Alvin Richards (Inactive) made changes -
            Labels Compatibility NRE-307517 Approved Compatibility NRE-307517
            alvinr Alvin Richards (Inactive) made changes -
            NRE Projects NRE-307517
            alvinr Alvin Richards (Inactive) made changes -
            Labels Approved Compatibility NRE-307517 Approved Compatibility
            alvinr Alvin Richards (Inactive) made changes -
            NRE Approved Yes [ 10304 ]
            alvinr Alvin Richards (Inactive) made changes -
            Labels Approved Compatibility Compatibility
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 69898 ] MariaDB v4 [ 132632 ]

            People

              igor Igor Babaev (Inactive)
              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.