MySQL - This Version of MySQL Doesn't Yet Support 'Limit & In/All/Any/Some Subquery

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

Instead of using IN, you can use JOIN

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

MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Try this:

  SELECT * FROM 
    tb_emp_daily_status d 
    where 
    d.shiftdate>='2017-06-07' and 
    shiftdate<='2017-06-13' and 
    emp_id in (SELECT * FROM (SELECT id FROM tb_employee e WHERE e.user_id = '18145' LIMIT 20)   
          as t)
    order by d.id asc

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

UPDATE id_card
INNER JOIN (
  SELECT code 
  FROM id_card 
  WHERE version LIKE '%922846%'
  ORDER BY version DESC
  LIMIT 1
) as t2 using (code)
SET division_category = '678' 
WHERE id_card.code = t2.code

MySQL doesn't yet support LIMIT & IN/ALL/ANY/SOME

You can do this by

`SELECT *, 
IF(users_posts.uid IN (select * from (SELECT puid FROM post_ups WHERE  post_ups.uid = UID LIMIT 400) tmp_tbl ) AND users_posts.uid  <> UID ,10,0)
    FROM users_posts`

Or Instead of LEFT JOIN Use INNER JOIN.

SELECT *,IF(ups.puid = t1.uid,10,0)
FROM users_posts t1 INNER JOIN (SELECT puid FROM post_ups WHERE  post_ups.uid = 135 ORDER BY `created_date` DESC LIMIT 400 ) AS ups ON ups.puid = t1.uid  AND t1.uid  <> 135

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

As Mihai mentions in his comment: try wrapping the sub-select in another sub-select. Looks dirty but is as close to your original query as you're going to get. Hence:

SELECT MAX(issue) AS issue_one 
FROM issue_list
WHERE issue NOT IN 
(SELECT issue from 
 (SELECT issue FROM issue_list ORDER BY id ASC LIMIT 46) x
);

This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' - Multiple WHEREs and LIMITS

You can utilize Derived Table here. Get the uid and old_uuid as per your conditions in the subquery (Derived Table); and then JOIN it to the main table matching either of them to uuid:

SELECT 
  t1.*
FROM 
  table1 AS t1 
JOIN 
(
  SELECT uid, old_uuid 
  FROM table1
  WHERE updated_at LIKE '2019-07-12 08%'
    AND player_id IS NOT NULL
  LIMIT 1
) AS dt ON dt.uid = t1.uuid 
           OR dt.old_uuid = t1.uuid 

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

SELECT s.*,v.*
FROM softwares s LEFT JOIN versions v ON s.SoftId = v.SoftId
WHERE DateAdded IS NULL
    OR DateAdded = (
        SELECT MAX(DateAdded)
        FROM versions v2
        WHERE v2.SoftId = s.SoftId
    )

https://www.db-fiddle.com/f/bNYJL5eobFYP3TRukA1Wi7/0

Solution to MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Since the subquery returns only 1 row with 1 column there is no need for IN.

You can use =:

 SELECT m.* 
 FROM `my_table` m
 WHERE m.`id` = (
   SELECT o.`id`
   FROM (
     SELECT DISTINCT i.`id`, i.`label`, i.`client`, i.`place`
     FROM `my_table` i
     ORDER BY i.`label`, -i.`client` DESC, -i.`place` DESC) o
   WHERE m.`label` = o.`label` LIMIT 1
 );

But as it is written, your query uses LIMIT without ORDER BY (you do use ORDER BY in the inner subquery where it is useless).

Do you mean to do something like this:

SELECT m.* 
FROM `my_table` m
WHERE m.`id` = (
  SELECT o.`id`
  FROM (
    SELECT DISTINCT i.`id`, i.`label`, i.`client`, i.`place`
    FROM `my_table` i
  ) o
  WHERE m.`label` = o.`label` 
  ORDER BY o.`label`, -o.`client` DESC, -o.`place` DESC
  LIMIT 1
);

Also ordering by the negative value of a column descending is equivalent to ordering just ascending, so the ORDER BY clause can be simplified to:

ORDER BY o.`label`, o.`client`, o.`place`

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

A query like

select * from table where id in (select id from othertable)

will basically be interpreted as

select * from table where exists 
   (select id from othertable where table.id = othertable.id)

That is what you expect for that query. You especially expect the IN query to use the index on othertable.id. In the manual, it is described as

Some optimizations that MySQL itself makes are: [...]

• MySQL rewrites IN, ALL, ANY, and SOME subqueries in an attempt to take advantage of the possibility that the select-list columns in the subquery are indexed.

These are, not by pure coincidence, exactly the four operators mentioned in the error message if you add a limit:

select * from table where id in 
   (select id from othertable order by id limit 10)

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

Rewriting that query in a similar way is not directly possible anymore, as it is something different than

select * from table where exists 
   (select id from othertable where table.id = othertable.id 
    order by id limit 10)

To execute the IN with a limit, MySQL could retrieve the 10 first rows of othertable, store that resultset as a derived subtable and check if the id is in there. And you can of course do exactly that:

select * from table where id in 
   (select id from 
      (select id from othertable order by id limit 10) subtable)

This will, in analogy to the first example, be interpreted as

select * from table where exists 
   (select * from 
      (select id from othertable order by id limit 10) subtable
    where table.id = subtable.id)

So it is a mixture of how the optimizer works (it will rewrite the query), how limit works (it stops execution after finding rows, not skipping them), what is expected (the use of indexes) and ultimately if the developers decided to allow a specific syntax or not.

You could argue that MySQL could always fall back to execute the query as a derived table if it encounters IN with a limit subquery - but so could you by explicitely using a derived subtable. You could also argue that you can think of ways to implement that or implement that differently - and you are right, there are. That's why there is a "yet" in the error message. So feel free to implement them or at least describe them e.g. in a feature request as thoroughly as possible and under consideration of how all the other parts of MySQL work. But make sure they are actually faster than just using a subtable.

---

Related Topics