When using rownum in having clause without group by or in subquery, the result may be wrong

create table t1 (a int primary key, b int);

insert into t1 values (1, 1), (2, 1), (3, 1), (4, 2), (5, 2), (6, 2);

select * from (select * from t1 order by a, b) dt having rownum() = 3;

MariaDB root@127.0.0.1:test> select * from (select * from t1 order by a, b) dt having rownum() = 3;

+---+---+

| a | b |

+---+---+

+---+---+

0 rows in set

create table t1 (a int primary key, b int);

insert into t1 values (1, 1), (2, 1), (3, 1), (4, 2), (5, 2), (6, 2);

select * from t1 where a in (select rownum() from t1);

MariaDB root@127.0.0.1:test> select * from t1 where a in (select rownum() from t1);

+---+---+

| a | b |

+---+---+

| 1 | 1 |

+---+---+

1 row in set

create table t1 (a int primary key, b int);

insert into t1 values (1, 1), (2, 1), (3, 1), (4, 2), (5, 2), (6, 2);

set optimizer_switch='materialization=off';

select * from t1 where a + 1 in (select rownum() from t1);

MariaDB root@127.0.0.1:test>

                          -> set optimizer_switch='materialization=off';

                          -> select * from t1 where a + 1 in (select rownum() from t1);

Query OK, 0 rows affected

Time: 0.001s

+---+---+

| a | b |

+---+---+

+---+---+

0 rows in set

Time: 0.005s