How to Resolve MySQL Error 1235: 'LIMIT & IN/ALL/ANY/SOME subquery' Not Supported?

Error: "This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'"

When executing a PHP query that utilizes a subquery with a LIMIT clause and an IN operator, you may encounter an error like this:

Error during SQL execution: SELECT VID, thumb FROM video WHERE VID IN ( SELECT VID FROM video WHERE title LIKE "%funny%" ORDER BY viewtime DESC LIMIT 5) ORDER BY RAND() LIMIT 1

MySQL Error: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

MySQL Errno: 1235

This error occurs because certain versions of MySQL do not support subqueries with a LIMIT clause used in conjunction with IN, ALL, ANY, or SOME operators.

Solution

To resolve this issue, consider using a JOIN instead of a subquery:

SELECT v.VID, v.thumb
FROM video AS v
INNER JOIN
     (SELECT VID
     FROM video
     WHERE title LIKE "%'.$Channel['name'].'%"
     ORDER BY viewtime DESC
     LIMIT 5) as v2
  ON v.VID = v2.VID
ORDER BY RAND()
LIMIT 1

This modified query uses an INNER JOIN to combine the results of two SELECT statements. The first SELECT statement fetches the VID of videos whose titles contain the specified channel name, ordering them by view time in descending order and limiting the results to the first 5. The second SELECT statement then uses this VID list to filter the main result set, fetching specific columns from the video table.

The above is the detailed content of How to Resolve MySQL Error 1235: 'LIMIT & IN/ALL/ANY/SOME subquery' Not Supported?. For more information, please follow other related articles on the PHP Chinese website!