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

Implement tables specified by table value constructors.

Details

    • 10.3.3-1

    Description

      SQL Standards starting from SQL-1999 uses the construct <table value constructor> as
      one of the alternatives for <simple table>:

      <simple table> ::=
      		 
          <query specification>
      		 
        | <table value constructor>
      		 
        | <explicit table>

      Currently MariaDB uses <table value constructor> only in insert statements:

      INSERT INTO t VALUES (1,'xx'), (5,'yyy'), (1,'zzz');

      With <table value constructor> it will be possible to use such CTE specification:

      WITH t (a,c) AS  (SELECT * FROM VALUES (1,'xx'), (5,'yyy'), (1,'zzz')) ...

      Now instead of this we have to use something like this:

      WITH t (a,c) AS (SELECT 1,  'xx' UNION ALL SELECT 5,  'yyy' UNION ALL 1, 'zzz') ...

      Processing of the latter will take much more memory.

      Besides with the possibility of using table value constructors in CTE specifications some optimization transformations for IN predicates will be possible.

      Attachments

        Issue Links

          blocks

          Task - A task that needs to be done. MDEV-12176 Transform [NOT] IN predicate with long list of values INTO [NOT] IN subquery.

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            igor Igor Babaev (Inactive) created issue -
            igor Igor Babaev (Inactive) made changes -
            Field Original Value New Value
            Component/s Optimizer [ 10200 ]
            Fix Version/s 10.3.0 [ 22127 ]
            Assignee Igor Babaev [ igor ]
            serg Sergei Golubchik made changes -
            Description SQL Standards starting from SQL-1999 uses the construct <table value constructor> as
            one of the alternatives for <simple table>:
            {noformat}
            <simple table> ::=
                <query specification>
              | <table value constructor>
              | <explicit table>
            {noformat}
            Currently MariaDB uses <table value constructor> only in insert statements:
            {noformat}
            INSERT INTO t VALUES (1,'xx'), (5,'yyy'), (1,'zzz');
            {noformat}
            With <table value constructor> it will be possible to use such CTE specification:
            {noformat}
            WITH t (a,c) AS (VALUES (1,'xx'), (5,'yyy'), (1,'zzz')) ...
            {noformat}
            Now instead of this we have to use something like this:
            {noformat}
            WITH t (a,c) AS (SELECT 1, 'xx' UNION ALL SELECT 5, 'yyy' UNION ALL 1, 'zzz') ...
            {noformat}
            Processing of the latter will take much more memory.

            Besides with the possibility of using table value constructors in CTE specifications some optimization transformations for IN predicates will be possible.
            		SQL Standards starting from SQL-1999 uses the construct <table value constructor> as
            one of the alternatives for <simple table>:
            {noformat}
            <simple table> ::=
                <query specification>
              | <table value constructor>
              | <explicit table>
            {noformat}
            Currently MariaDB uses <table value constructor> only in insert statements:
            {code:sql}
            INSERT INTO t VALUES (1,'xx'), (5,'yyy'), (1,'zzz');
            {code}
            With <table value constructor> it will be possible to use such CTE specification:
            {code:sql}
            WITH t (a,c) AS (VALUES (1,'xx'), (5,'yyy'), (1,'zzz')) ...
            {code}
            Now instead of this we have to use something like this:
            {code:sql}
            WITH t (a,c) AS (SELECT 1, 'xx' UNION ALL SELECT 5, 'yyy' UNION ALL 1, 'zzz') ...
            {code}
            Processing of the latter will take much more memory.

            Besides with the possibility of using table value constructors in CTE specifications some optimization transformations for IN predicates will be possible.
            serg Sergei Golubchik made changes -
            Labels gsoc17
            igor Igor Babaev (Inactive) made changes -
            igor Igor Babaev (Inactive) made changes -
            Description SQL Standards starting from SQL-1999 uses the construct <table value constructor> as
            one of the alternatives for <simple table>:
            {noformat}
            <simple table> ::=
                <query specification>
              | <table value constructor>
              | <explicit table>
            {noformat}
            Currently MariaDB uses <table value constructor> only in insert statements:
            {code:sql}
            INSERT INTO t VALUES (1,'xx'), (5,'yyy'), (1,'zzz');
            {code}
            With <table value constructor> it will be possible to use such CTE specification:
            {code:sql}
            WITH t (a,c) AS (VALUES (1,'xx'), (5,'yyy'), (1,'zzz')) ...
            {code}
            Now instead of this we have to use something like this:
            {code:sql}
            WITH t (a,c) AS (SELECT 1, 'xx' UNION ALL SELECT 5, 'yyy' UNION ALL 1, 'zzz') ...
            {code}
            Processing of the latter will take much more memory.

            Besides with the possibility of using table value constructors in CTE specifications some optimization transformations for IN predicates will be possible.
            		SQL Standards starting from SQL-1999 uses the construct <table value constructor> as
            one of the alternatives for <simple table>:
            {noformat}
            <simple table> ::=
                <query specification>
              | <table value constructor>
              | <explicit table>
            {noformat}
            Currently MariaDB uses <table value constructor> only in insert statements:
            {code:sql}
            INSERT INTO t VALUES (1,'xx'), (5,'yyy'), (1,'zzz');
            {code}
            With <table value constructor> it will be possible to use such CTE specification:
            {code:sql}
            WITH t (a,c) AS (SELECT * FROM VALUES (1,'xx'), (5,'yyy'), (1,'zzz')) ...
            {code}
            Now instead of this we have to use something like this:
            {code:sql}
            WITH t (a,c) AS (SELECT 1, 'xx' UNION ALL SELECT 5, 'yyy' UNION ALL 1, 'zzz') ...
            {code}
            Processing of the latter will take much more memory.

            Besides with the possibility of using table value constructors in CTE specifications some optimization transformations for IN predicates will be possible.
            serg Sergei Golubchik made changes -
            Fix Version/s 10.3 [ 22126 ]
            Fix Version/s 10.3.0 [ 22127 ]
            serg Sergei Golubchik made changes -
            Sprint 10.3.3-1 [ 200 ]
            serg Sergei Golubchik made changes -
            Rank Ranked higher
            marko Marko Mäkelä made changes -
            Fix Version/s 10.3.3 [ 22644 ]
            Fix Version/s 10.3 [ 22126 ]
            Resolution Fixed [ 1 ]
            Status Open [ 1 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 79827 ] MariaDB v4 [ 133143 ]

            People

              igor Igor Babaev (Inactive)
              igor Igor Babaev (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              6 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.