Forum Stats

  • 3,825,276 Users
  • 2,260,494 Discussions
  • 7,896,475 Comments

Discussions

Query Works in 5.6 doesn’t work in 5.7

user10647930
user10647930 Member Posts: 7 Blue Ribbon
edited Mar 17, 2017 10:48AM in MySQL Community Space

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.

Sheeri

Best Answer

  • User9141645-Oracle
    User9141645-Oracle Member Posts: 1
    edited Mar 16, 2017 1:25PM Answer ✓

    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.

    Sheeri

Answers