Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Wrong result of join when joined fields are created via row_number() function [CORE4261] #4585

Closed
firebird-issue-importer opened this issue Nov 11, 2013 · 4 comments

Comments

@firebird-issue-importer

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

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 12, 2013

Commented by: @pavel-zotov

One more sample:

SQL> select q1
CON> from( select 1 as q1 ,row_number() over() rnk from rdb$database) t1
CON> join( select row_number() over() rnk from rdb$database) t2
CON> on t1.rnk=t2.rnk;

      Q1

============
1

(yes, it`s OK)

But:

SQL> select q1
CON> from( select 1 as q1 ,row_number() over() rnk from rdb$database) t1
CON> join( select row_number() over() rnk from rdb$database) t2
CON> on t1.rnk=t2.rnk
CON> group by q1;

      Q1

============
0

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 12, 2013

Modified by: @dyemanov

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Dec 5, 2013

Modified by: @dyemanov

assignee: Adriano dos Santos Fernandes [ asfernandes ] => Dmitry Yemanov [ dimitr ]

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0 Alpha 2 [ 10560 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 29, 2015

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: Done successfully

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants