Why adding extra one layer of SELECT resolve Mysql

Question

In one of my previous question, I have asked solution for resolving
mysql 1235 error:

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

Following will throw 1235 :

 DELETE
    FROM job_detail_history
    where id not in (select id from job_detail_history order by start_time desc limit 2);

For that i got the solution which is given by @Zaynul Abadin Tuhin as follows and it works for me too. He just added one single select layer over my subquery. and as per him it is suggested by some mysql experts.
Resolution For above problem:

 DELETE
    FROM job_detail_history
    where id not in (select * from
        (select id from job_detail_history order by start_time desc limit 2) as t1 );

---

I try to do analysis of DB table and i found that when i use
Problem :: this will not work with delete as explained above.

select id from job_detail_history order by start_time desc limit 2;

it return me something like this :

last null was for new row as workbench suggest:

And when i add one extra layer of select :
adding extra layer of subquery : And this will work with my delete.

(select id from (select id from job_detail_history order by start_time desc limit 2)  as t1);

it returns something like this :

So, what i want to understand
How subquery with one extra layer of resolve 1235 error?

can anyone eleborate it in detail.

Answer 1

There is an important difference between a subquery and a derived table.

A derived table replaces a table (it's used where you can also use a "normal" tablename), specifically in from <tablename> or join <tablename>, and it requries an alias (or the "tablename", as it is used as any other table). You cannot write where not in (<tablename>); that is not a derived table, it is a subquery.

In general, this problem (and the solution to use another layer) happens for delete:

You cannot delete from a table and select from the same table in a subquery.

But a derived table using this table is not forbidden. MySQL simply can't handle (or doesn't want to handle) this kind of dependency as to how it works internally (and according to it's rules).

For LIMIT, there is a similar subquery-specific restriction,

MySQL does not support LIMIT in subqueries for certain subquery operators

ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

As to the reason why it makes a difference for MySQL: a derived table stands on its own and cannot depend on the outer query. It can be used internally like a normal table. (E.g., MySQL could simply create this table in the first step of the execution plan and it's there for all further steps.) A subquery on the other hand can depend on the outer table (making it a dependent subquery).

Specifically,

 where id not in (select id from job_detail_history);

is the same as

 where not exists (select id from job_detail_history sub where sub.id = outer.id);

while you cannot do this for limit:

 where id not in (select id from job_detail_history limit 2);

is not the same as

 where not exists (select id from job_detail_history sub 
                   where sub.id = outer.id limit 2);

MySQL can simply not handle this, as it is used to doing this transformation. It will probably allow it sooner or later though. To make it work for the delete, you will still need to use a subquery though.