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;
If an
order byand/oroffset/fetchis 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)
Now, the following ignores the outer order by and offset/fetch as if it is not there:
Returns: 3, 4, 5, 6, 7
Should return: 5, 4
In other words, it should work the same as:
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
or
While having the clauses only outside the parenthesized query expression does work: