Wrong result of join when joined fields are created via row_number() function
-----------------------------------------------------------------------------
Key: CORE-4261
URL: http://tracker.firebirdsql.org/browse/CORE-4261
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 3.0 Alpha 1
Reporter: Pavel Zotov
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> s1.id s1_id, 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 s1.id s1_id, 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)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
November Webinars for C, C++, Fortran Developers
Accelerate application performance with scalable programming models. Explore
techniques for threading, error checking, porting, and tuning. Get the most
from the latest Intel processors and coprocessors. See abstracts and register
http://pubads.g.doubleclick.net/gampad/clk?id=60136231&iu=/4140/ostg.clktrk
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
