Support FETCH n first/next rows expression for limit

[Insolita]

Postgres since 13, and oracle since 12 ver support expression

OFFSET start { ROW | ROWS } FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES }

MariaDb also supports since 10.6 https://mariadb.com/kb/en/select-offset-fetch/

MS SQL and MySql supports FETCH { FIRST | NEXT } [ count ] { ROW | ROWS }

Right now there is no way to use it with QueryBuilder.

Propose for add ability to add fetch() construction or allow custom Expression for limit() without "LIMIT" prefix if it starts with "FETCH" at this place https://github.com/yiisoft/yii2/blob/master/framework/db/QueryBuilder.php#L1446

[Insolita]

@bizley Will you accept PR for this? If I add an additional check at this place https://github.com/yiisoft/yii2/blob/master/framework/db/QueryBuilder.php#L1446 for FETCH clause (Or may be OFFSET also, if any db require strict order)

[bizley]

Sure, PR will be fantastic. This change may be better placed in the specific DB versions of that class. Let's see the proposal, the tests, and opinion from people that are way better with these db engines than I am ;)

[Insolita]

Yes, I will check more details in the engine's documentation and hope that someone else tells own opinion. Practically this kind of queries useful for cursor-based pagination, infinite pagination, and top stat

Should I check and restrict for concrete db versions, or keep it for the user side?

[bizley]

We should basically keep it in the ranges supported officially by Yii if possible.