[MDEV-10037] UNION with LIMIT ROWS EXAMINED does not require parentheses Created: 2016-05-06  Updated: 2016-05-21  Resolved: 2016-05-21

Status: Closed
Project: MariaDB Server
Component/s: Parser
Affects Version/s: 10.1, 10.2
Fix Version/s: 10.2.1

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Blocks
blocks MDEV-8909 union parser cleanup Closed

 Description   

10.1 disallowed use of non-parenthesized UNION parts that have ORDER or LIMIT clauses, according to the SQL Standard. That was a merge from MySQL-5.6.

This script:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10),(10);
SELECT * FROM t1 LIMIT 1
UNION
SELECT * FROM t1 LIMIT 1;

returns an error starting from 10.1:

ERROR 1221 (HY000): Incorrect usage of UNION and LIMIT

However, if I use LIMIT in combination with ROWS EXAMINED:

SELECT * FROM t1 LIMIT ROWS EXAMINED 1
UNION
SELECT * FROM t1 LIMIT ROWS EXAMINED 1;

it returns a result instead of an error:

+------+
| a    |
+------+
|   10 |
+------+
1 row in set, 1 warning (0.00 sec)

It should be fixed to require parentheses as well, like in case of a "normal" LIMIT clause.

Most likely, LIMIT ROWS EXAMINED was just forgotten during the merge.

The documentation at:
https://mariadb.com/kb/en/mariadb/union/
says:

Individual selects can contain their own ORDER BY and LIMIT clauses. In that case, the individual queries need to be wrapped between parenthesis.


Generated at Thu Feb 08 07:39:11 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.