Submitted by: @pavel-zotov

Votes: 1

The following query displays wrong result in LI-T3.0.0.30695:

SQL> with data as(
CON> select 1 id from rdb$database union all
CON> select 2 id from rdb$database union all
CON> select 3 id from rdb$database union all
CON> select 5 id from rdb$database
CON> )
CON> ,seq as(
CON> select row_number()over(order by id) rn, id from data
CON> )
CON> select
CON> http://s1.id s1_id, http://s2.id s2_id
CON> ,s1.rn s1_rn, s2.rn s2_rn
CON> ,iif(s1.rn=s2.rn-1,'yes',iif(s1.rn<>s2.rn-1, 'no!', 'hm!..')) result
CON> from seq s1
CON> left join seq s2 on s1.rn=s2.rn-1;

Output:

   S1\_ID        S2\_ID                 S1\_RN                 S2\_RN RESULT

============ ============ ===================== ===================== ======
1 2 1 2 yes
2 3 2 3 yes
3 5 3 4 yes
5 5 4 0 hm!..

Compare with MS SQL 2005:

with data as(
select 1 id
union all
select 2
union all
select 3
union all
select 5
)
,seq as(
select row_number()over(order by id) rn, id from data
)
select http://s1.id s1_id, http://s2.id s2_id, s1.rn s1_rn, s2.rn s2_rn
,case when s1.rn=s2.rn-1 then 'yes' when s1.rn<>s2.rn-1 then 'no!' else 'hm!..' end result
from seq s1
left join seq s2 on s1.rn=s2.rn-1

Result:
s1_id s2_id s1_rn s2_rn result
1 2 1 2 yes
2 3 2 3 yes
3 5 3 4 yes
5 NULL 4 NULL hm!..

(the same in Oracle 11.2g)

Commits: d9c0e95 FirebirdSQL/fbt-repository@202d888