Multi-level order by and offset/fetch ignored on parenthesized query expressions

[mrotteveel]

If an order by and/or offset/fetch is added inside and outside a parenthesized query expression, the clause outside the parentheses are silently ignored. This is syntactically allowed, and as far as I can tell from the SQL standard, should work.

(I'm using snapshot Firebird-5.0.0.1038-0-windows-x64)

create table rowdata (id integer generated always as identity);
insert into rowdata default values;
insert into rowdata default values;
insert into rowdata default values;
insert into rowdata default values;
insert into rowdata default values;
insert into rowdata default values;
insert into rowdata default values;
insert into rowdata default values;
insert into rowdata default values;

Now, the following ignores the outer order by and offset/fetch as if it is not there:

(
  select id
  from rowdata
  order by id
  offset 2 rows fetch next 5 rows only
)
order by id desc
offset 2 rows fetch next 2 rows only;

Returns: 3, 4, 5, 6, 7

Should return: 5, 4

In other words, it should work the same as:

select * from (
  select id
  from rowdata
  order by id
  offset 2 rows fetch next 5 rows only
)
order by id desc
offset 2 rows fetch next 2 rows only;

For example PostgreSQL 15 (dbfiddle) will report "multiple ORDER BY clauses not allowed" (and same for offset and fetch if the others are removed).

Looking at the original discussion on firebird-devel, this potential problem was raised by Dmitry before implementation.

Looking at the SQL standard, I think this should apply another sort and offset/fetch, but otherwise raising an error like PostgreSQL might be acceptable.

In a similar vein, the outer clauses are also ignored if they don't overlap

(
  select id
  from rowdata
  --order by id
  offset 2 rows fetch next 5 rows only
)
order by id desc
-- offset 2 rows fetch next 2 rows only;

or

(
  select id
  from rowdata
  order by id
  -- offset 2 rows fetch next 5 rows only
)
-- order by id desc
offset 2 rows fetch next 2 rows only;

While having the clauses only outside the parenthesized query expression does work:

(
  select id
  from rowdata
  -- order by id
  -- offset 2 rows fetch next 5 rows only
)
order by id desc
offset 2 rows fetch next 2 rows only;