1

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 :
enter image description here

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 :
enter image description here

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

can anyone eleborate it in detail.

yash
  • 2,101
  • 2
  • 23
  • 32
  • because mysql doesn't allow this. By using join you can do it – Ankit Agrawal Aug 17 '18 at 07:41
  • @AnkitAgrawal, but it is not mentioned anywhere that adding one extra layer of select will resolve your issue. – yash Aug 17 '18 at 07:42
  • It isn't clear to me which of these queries is actually working. You might want to take a few moments and highlight that. – Tim Biegeleisen Aug 17 '18 at 07:52
  • @TimBiegeleisen , i updated question. hope this will be easy to you understand. – yash Aug 17 '18 at 08:02
  • My now deleted answer was not far off, I think. The workaround obviously has something to do with materializing the `LIMIT` subquery, and then running `WHERE IN` against that. As to exactly _why_ this works, we'd have to delve into execution plans and things like that, to see what MySQL is doing at each step. – Tim Biegeleisen Aug 17 '18 at 08:03
  • Subselect has to return only 1 result. So limit to 1. – Markus Zeller Aug 17 '18 at 08:45

1 Answers1

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.

Solarflare
  • 10,721
  • 2
  • 18
  • 35
  • Good catch. Thanks for pointing mysql documentation, which will clarify about the problem. – yash Aug 17 '18 at 08:48