IN subquery accepted with OFFSET clause

constructs like expr IN (SELECT ... OFFSET n ROWS) are accepted and produce wrong query results.
I think we should refuse such queries, because we refuse very similar IN (SELECT ... LIMIT n).

Testcase:

create table t1(a int);

insert into t1 values (1),(2);

create table t2 (a int);

insert into t2 values (1),(2),(3);

This will return empty set as t2 has fewer than 10 rows:

select t2.a from t2 offset 10 rows;

a

But when we put that into an IN-subquery it will return TRUE which is clearly incorrect:

select t1.a in (select t2.a from t2 offset 10 rows) as SUBQ from t1;

SUBQ

1

1

Note that attempt to use LIMIT causes query error:

select t1.a in (select t2.a from t2 limit 10) as SUBQ from t1

query 'select t1.a in (select t2.a from t2 limit 10) as SUBQ from t1' failed: 

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

Some background

Support for SELECT ... OFFSET n ROWS without a LIMIT came in with MDEV-23908, "Implement SELECT ... OFFSET ... FETCH ...".

• Commit: 299b9353203, by Vicențiu Ciorbaru, dated 2021-03-28.
• First release: the first tagged release containing it is mariadb-10.6.0.

So, it was added much later than subquery or subquery optimizations code.
It looks that patch has missed disabling OFFSET in the WHERE clauses.

How to fix

Disable it in the parser like it's done for IN(SELECT ... LIMIT ).