4 ms·

MySQL often doesn't support limit in subqueries, hence the roundtrip. > ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME

MySQL often doesn't support limit in subqueries, hence the roundtrip. > ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' See https://dev.mysql.com/doc/refman/5.7/en/subquery-restrictions.html https://dev.mysql.com/doc/refman/5.7/en/subquery-restriction...

samwillis 4y ago
Ah ok, confusingly the OP (and your blog post they link to) both shows example SQL with a subquery using limit and imply it’s from a book on MySQL optimisations.
- aarondf 4y ago
  Yeah that's a bit confusing. The deferred join in the book does indeed use an inner join but my package (and PlanetScale's) both use a "deconstructed subquery." I made that choice for mine because I didn't want to pollute the `select` part of the developer's query to exclude the joined data. If we were writing the whole query from scratch we could, but not in the scenario where the package is actually used, unfortunately.
- EGreg 4y ago
  It’s because it is using JOIN which isn’t one of the problematic operations
gavinray 4y ago
You can use LIMIT in correlated subqueries in MySQL +8 using LATERAL https://dev.mysql.com/doc/refman/8.0/en/lateral-derived-tables.html https://dev.mysql.com/doc/refman/8.0/en/lateral-derived-tabl... Prior to this you can emulate it with ROW NUMBER Window function in subquery, and this is what LATERAL gets turned into in many DB engines I believe https://stackoverflow.com/questions/73127487/aws-athena-v2-prestodb-reference-left-hand-row-in-right-hand-subquery https://stackoverflow.com/questions/73127487/aws-athena-v2-p...
simonw 4y ago
Might be possible to use a CTE instead, something like this: with page_numbers as ( select id from docs order by id limit 25 offset 50 ) select * from docs where id in ( select id from page_numbers )
- aarondf 4y ago
  Mmm yeah, that's clever! I like it. I think for my package (the Laravel one) I'll have to hold off on that because CTEs only work with MySQL 8.0 and up. Also the Laravel query builder doesn't have methods to add CTEs, unfortunately. I'm gonna noodle on this though, there's something nice about it.
- aledalgrande 4y ago
  I don't know if it's the same for MySQL, but in Postgres a CTE would possibly force the planner to make less efficient choices [1]. Would be interesting if someone who knows more than me could clarify on this [1] https://hakibenita.com/be-careful-with-cte-in-postgre-sql https://hakibenita.com/be-careful-with-cte-in-postgre-sql
  - Lukas_Skywalker 4y ago
    This changed in version 12. See the disclaimer: This article is intended for PostgreSQL versions 11 and prior. Starting at version 12, PostgreSQL changed the way it treats CTE to prevent the issues described in this article.
    - aledalgrande 4y ago
      Ohhh nice catch!

samwillis 4y ago

Ah ok, confusingly the OP (and your blog post they link to) both shows example SQL with a subquery using limit and imply it’s from a book on MySQL optimisations.

aarondf 4y ago
Yeah that's a bit confusing. The deferred join in the book does indeed use an inner join but my package (and PlanetScale's) both use a "deconstructed subquery." I made that choice for mine because I didn't want to pollute the `select` part of the developer's query to exclude the joined data. If we were writing the whole query from scratch we could, but not in the scenario where the package is actually used, unfortunately.
EGreg 4y ago
It’s because it is using JOIN which isn’t one of the problematic operations

gavinray 4y ago

You can use LIMIT in correlated subqueries in MySQL +8 using LATERAL https://dev.mysql.com/doc/refman/8.0/en/lateral-derived-tables.html https://dev.mysql.com/doc/refman/8.0/en/lateral-derived-tabl... Prior to this you can emulate it with ROW NUMBER Window function in subquery, and this is what LATERAL gets turned into in many DB engines I believe https://stackoverflow.com/questions/73127487/aws-athena-v2-prestodb-reference-left-hand-row-in-right-hand-subquery https://stackoverflow.com/questions/73127487/aws-athena-v2-p...

simonw 4y ago

Might be possible to use a CTE instead, something like this: with page_numbers as ( select id from docs order by id limit 25 offset 50 ) select * from docs where id in ( select id from page_numbers )

aarondf 4y ago
Mmm yeah, that's clever! I like it. I think for my package (the Laravel one) I'll have to hold off on that because CTEs only work with MySQL 8.0 and up. Also the Laravel query builder doesn't have methods to add CTEs, unfortunately. I'm gonna noodle on this though, there's something nice about it.
aledalgrande 4y ago
I don't know if it's the same for MySQL, but in Postgres a CTE would possibly force the planner to make less efficient choices [1]. Would be interesting if someone who knows more than me could clarify on this [1] https://hakibenita.com/be-careful-with-cte-in-postgre-sql https://hakibenita.com/be-careful-with-cte-in-postgre-sql
- Lukas_Skywalker 4y ago
  This changed in version 12. See the disclaimer: This article is intended for PostgreSQL versions 11 and prior. Starting at version 12, PostgreSQL changed the way it treats CTE to prevent the issues described in this article.
  - aledalgrande 4y ago
    Ohhh nice catch!