MySQL Bugs: #83661: ER_WRONG_USAGE for UNION and LIMIT changes behaviour in sql

Bug #83661	ER_WRONG_USAGE for UNION and LIMIT changes behaviour in sql_lex.cc
Submitted:	3 Nov 2016 0:30	Modified:	8 Nov 2016 14:31
Reporter:	Ceri Williams	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	5.7, 5.7.16	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	lexical, sql_lex.cc, UNION

Description:
The documentation states that parentheses should be used for UNION queries using LIMIT on individual SELECT queries:

"To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT"

In previous versions, sql_lex.cc did not throw ER_WRONG_USAGE and there seems to be no instance of 'my_error(ER_WRONG_USAGE, MYF(0), "UNION", "LIMIT");' in 5.6.

In 5.7, a query that ran successfully in 5.6 will now throw an error and fail during lexical analysis.

mysql> select id from test1 limit 1 union select id from test2 limit 1;
ERROR 1221 (HY000): Incorrect usage of UNION and LIMIT

How to repeat:
mysql> create table test1 (id integer unsigned not null primary key);                                                                                          
Query OK, 0 rows affected (0.26 sec)

mysql> create table test2 (id integer unsigned not null primary key);                                                                                          
Query OK, 0 rows affected (0.35 sec)

mysql> create table test3 (id integer unsigned not null primary key);                                                                                          
Query OK, 0 rows affected (0.23 sec)

mysql> insert into test1 set id=1;
Query OK, 1 row affected (0.06 sec)

mysql> insert into test2 set id=2;                                                                                                                             
Query OK, 1 row affected (0.07 sec)

mysql> insert into test3 set id=3;                                                                                                                             
Query OK, 1 row affected (0.06 sec)

mysql> (select id from test1 limit 1)
    -> union (select id from test2 limit 1)
    -> union (select id from test3 limit 1)
    -> ;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

mysql> select id from test1 limit 1 union select id from test2 limit 1 union select id from test3 limit 1;                                                                                                                                                                                                                     
ERROR 1221 (HY000): Incorrect usage of UNION and LIMIT

Using gdb - break sql/sql_lex.cc:687

The SELECT without parentheses will hit this and error.

Thread 28 "mysqld" hit Breakpoint 3, LEX::new_union_query (this=0x7fa83c002c28, curr_select=0x7fa83c005570, distinct=true) at mysql-5.7.15/sql/sql_lex.cc:687
687         my_error(ER_WRONG_USAGE, MYF(0), "UNION", "LIMIT");

Suggested fix:
Update the documentation to make clear the change of behaviour and the introduction of a new error

Hello Ceri Williams,

Thank you for the report and test case.

Thanks,
Umesh

The behavior change occurs in 5.7.4.
http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-4.html says:

The parser silently accepted duplicate ORDER BY clauses and/or LIMIT clauses before ORDER BY clauses in subqueries. These caused failures during query execution. Fixing this problem results in some changes in parser behavior. The parser no longer accepts:

* A LIMIT clause before an ORDER BY clause

* A LIMIT clause in a parentheses-less SELECT statement before a UNION keyword

* An INTO clause after a PROCEDURE ANALYSE() clause

(Bug #17426017, Bug #17703542, Bug #17727401)

May I suggest adding to this page:
http://dev.mysql.com/doc/refman/5.7/en/upgrading-from-previous-series.html

The main docs for 5.5 and 5.6 mention this requirement of parentheses as well: (http://dev.mysql.com/doc/refman/5.5/en/union.html): "To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT". 

Except those versions allow the SQL without parentheses.

So, for example:

mysql [localhost] {msandbox} (test) > select id from test1 limit 1 union select id from test2 limit 1 union select id from test3 limit 1;  
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > select id from test1 limit 1 union select id from test2 limit 1 union select id from test3;  
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) > select id from test1 limit 0 union select id from test2 limit 1 union select id from test3;  
+----+
| id |
+----+
|  2 |
|  3 |
+----+

Removing the last LIMIT allows the other LIMIT conditions to work, so behaviour changed. This should be in GA documentation, specifically those looking to upgrade would expect to see mention of it in the upgrade docs.

Posted by developer:
 
Behavior change in requirement for parentheses mentioned here:

http://dev.mysql.com/doc/refman/5.7/en/union.html
http://dev.mysql.com/doc/refman/5.7/en/upgrading-from-previous-series.html