Query Works in 5.6 doesn’t work in 5.7

user10647930 · Mar 16, 2017 11:56AM

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 · Mar 16, 2017 1:25PM

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 · Mar 16, 2017 12:58PM

It would help if you post your code.

User9141645-Oracle · Mar 16, 2017 1:25PM

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.

Forum Stats