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

INSERT .. VALUES allows undocumented optional ORDER BY and LIMIT clauses but ignores them

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.4.0, 10.4, 10.5
    • Fix Version/s: 10.4, 10.5
    • Component/s: Parser
    • Labels:
      None

      Description

      10.5 f544a712c8

      MariaDB [test]> create or replace table t1 (a int);
      Query OK, 0 rows affected (0.188 sec)
       
      MariaDB [test]> insert into t1 values (1),(2),(3) order by 1 desc limit 1;
      Query OK, 3 rows affected (0.021 sec)
      Records: 3  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> select * from t1;
      +------+
      | a    |
      +------+
      |    1 |
      |    2 |
      |    3 |
      +------+
      3 rows in set (0.001 sec)
      

      I would expect it either to return an error if syntax is not supported; or return a warning if the clauses are intentionally accepted but ignored; or apply them in a meaningful way and be documented.

      MariaDB versions before 10.4, as well as all MySQL versions, throw a syntax error upon it.

      PostgreSQL (9.6) accepts it and produces a more reasonable result:

      postgres=# create table t1 (a int);
      CREATE TABLE
      postgres=# insert into t1 values (1),(2),(3) order by 1 desc limit 1;
      INSERT 0 1
      postgres=# select * from t1;
       a 
      ---
       3
      (1 row)
      

      The change came to 10.4 with this commit:

      commit de745ecf29721795710910a19bd0ea3389da804c
      Author: Oleksandr Byelkin
      Date:   Tue May 22 19:08:39 2018 +0200
       
          MDEV-11953: support of brackets in UNION/EXCEPT/INTERSECT operations
      

      I don't know if it was intentional or not.

      I couldn't find the syntax in the standard 2011 draft, but maybe I didn't search well enough. I don't have access to newer versions.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              rucha174 Rucha Deodhar
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration