[MDEV-22595] INSERT .. VALUES allows undocumented optional ORDER BY and LIMIT clauses but ignores them Created: 2020-05-16  Updated: 2024-02-07

Status: Open
Project: MariaDB Server
Component/s: Parser
Affects Version/s: 10.4.0, 10.4, 10.5
Fix Version/s: 10.4, 10.5

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Rucha Deodhar
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Problem/Incident
is caused by MDEV-11953 support of brackets (parentheses) in ... Closed

 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.
Generated at Thu Feb 08 09:15:58 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.