MySQL and MariaDB Refuse LIMIT Inside an IN Subquery

Finding the customers behind the ten latest orders with WHERE id IN (SELECT customer_id FROM orders ORDER BY created_at DESC LIMIT 10) works on SQLite and PostgreSQL. MySQL and MariaDB refuse it.

Both report that this version doesn’t yet support 'LIMIT & IN/ALL/ANY/SOME subquery'. LIMIT is fine in a subquery used as a table and in one that returns a single value.

What Each Engine Answered

On a table made with

CREATE TABLE t (x INTEGER);
INSERT INTO t VALUES (1), (2);

the example

SELECT x FROM t WHERE x IN (SELECT x FROM t ORDER BY x LIMIT 1);

gave these answers:

What to Write Instead

This form answers the same on every engine:

SELECT x FROM t WHERE x IN (SELECT x FROM (SELECT x FROM t ORDER BY x LIMIT 1) AS s);

Wrap the limited query in a subquery used as a table: IN (SELECT x FROM (SELECT x FROM t ORDER BY x LIMIT 1) AS s).

What BareSQL Portable Reports

Saved as example.sql, the example gets this from baresql lint --all:

$ baresql lint --all example.sql
example.sql:3:54: info: SQLite, MySQL and MariaDB put NULL first in ascending order; PostgreSQL puts it last. [null-order]
    x
    Sort on x IS NULL first: ORDER BY x IS NULL, x puts NULL last on every engine, and ORDER BY x IS NOT NULL, x puts it first. NULLS FIRST and NULLS LAST do not work on MySQL and MariaDB.
example.sql:3:56: error: MySQL and MariaDB refuse LIMIT in a subquery under IN, ANY, ALL or SOME; SQLite and PostgreSQL accept it. [limit-in-subquery]
    LIMIT 1
    Wrap the limited query in a subquery used as a table: IN (SELECT x FROM (SELECT x FROM t ORDER BY x LIMIT 1) AS s).

2 problems: 1 error, 1 note.

The example also runs into Where NULL sorts, which has a post of its own.

baresql rules limit-in-subquery prints the same answers in the terminal, and the BareSQL Portable page lists every difference BareSQL knows.