Query Works in 5.6 doesn’t work in 5.7

user10647930

Hi,

Some of the queries work in 5.6 and but not on 5.7.

Basically the only difference between the two is the parenthesis in the second query around the first select with the LIMIT.

Without parenthesis they work in 5.6 but if i use on 5.7 i am getting error:

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

any idea how to get this fixed.

User9141645-Oracle

This is a documented change in the syntax. See https://dev.mysql.com/doc/refman/5.7/en/union.html

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

(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)UNION(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

Note

Previous versions of MySQL may permit such statements without parentheses. In MySQL 5.7, the requirement for parentheses is enforced.

Gbenga Ajakaye

It would help if you post your code.

User9141645-Oracle

This is a documented change in the syntax. See https://dev.mysql.com/doc/refman/5.7/en/union.html

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

(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)UNION(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

Note

Previous versions of MySQL may permit such statements without parentheses. In MySQL 5.7, the requirement for parentheses is enforced.