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

created at 07-27-2021 views: 1

error

Encountered an error when executing the sql statement:

[Err] 1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Original sql statement:

SELECT id,friend_id,sender_id,obj_key,comment_id,content,ctime from msg 
where  
id in (SELECT MAX(id) as id FROM msg WHERE user_id = 312 and status !=3  GROUP BY obj_key,comment_id ORDER BY id DESC LIMIT 10)

The result of the query is: LIMIT cannot be used for sub-queries in MySQL

Solution

For example, such statements cannot be executed correctly.

select * from table where id in (select id from table limit 12);

The first method: as long as you add another layer. like:

select * from table where id in (select t.id from (select * from table limit 12) as t)

The second method:

select * from (select id from table limit 12) as t;

The third method: use inner join .... on ....

select t.id from table as t 
inner join
(select id from table limit 12) as t2
on t.id=t2.id

It is recommended to use inner join, because usually db engine will parse where as join

created at:07-27-2021
edited at: 07-27-2021: