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

Full outer join in derived table with coalesce (iif) [CORE4083] #4411

Closed
firebird-automations opened this issue Apr 17, 2013 · 24 comments
Closed

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Nikolay Teselko (nikolayv81)

Is related to CORE1246
Is related to QA525
Relate to CORE4873

Votes: 1

select
A_SOME_FIELD,
B_SOME_FIELD,
C_SOME_FIELD,
COALESCE_FIELD
from
(
select
A.SOME_FIELD as A_SOME_FIELD,
B.SOME_FIELD as B_SOME_FIELD,
C.SOME_FIELD as C_SOME_FIELD,
coalesce(A.SOME_FIELD, B.SOME_FIELD, c.SOME_FIELD) as COALESCE_FIELD
from
(select null as SOME_FIELD from RDB$DATABASE) A
full join
(select 1 as SOME_FIELD from RDB$DATABASE) B on B.SOME_FIELD = A.SOME_FIELD
full join
(select null as SOME_FIELD from RDB$DATABASE) C on C.SOME_FIELD = B.SOME_FIELD
) x

expected result (ожидаемый результат):
( checked in oracle )
null null null null
null 1 null 1
null null null null

got result (полученный результат ):

null null null null
null 1 null null <---
null null null null

Problem with more then 2 full join and subrequest
in firebird 2.1.3 request works NOT fine

ru: http://www.sql.ru/forum/actualthread.aspx?tid=1017752

Commits: 929de3d e8d26b0 821dbb4 6d7b918 FirebirdSQL/fbt-repository@5f26ded FirebirdSQL/fbt-repository@df8a79c FirebirdSQL/fbt-repository@f079a00 FirebirdSQL/fbt-repository@0a8691c FirebirdSQL/fbt-repository@dc88efb

@firebird-automations
Copy link
Collaborator Author

Modified by: Nikolay Teselko (nikolayv81)

description: select
A_SOME_FIELD,
B_SOME_FIELD,
C_SOME_FIELD,
COALESCE_FIELD
from
(
select
A.SOME_FIELD as A_SOME_FIELD,
B.SOME_FIELD as B_SOME_FIELD,
C.SOME_FIELD as C_SOME_FIELD,
coalesce(A.SOME_FIELD, B.SOME_FIELD, c.SOME_FIELD) as COALESCE_FIELD
from
(select null as SOME_FIELD from RDB$DATABASE) A
full join
(select 1 as SOME_FIELD from RDB$DATABASE) B on B.SOME_FIELD = A.SOME_FIELD
full join
(select null as SOME_FIELD from RDB$DATABASE) C on C.SOME_FIELD = B.SOME_FIELD
) x

expected result (ожидаемый результат):

null null null null
null 1 null null
null null null null

got result (полученный результат ):

null null null null
null 1 null null <---
null null null null

Problem with more then 2 full join and subrequest
in firebird 2.1.3 request works fine

ru: http://www.sql.ru/forum/actualthread.aspx?tid=1017752

=>

select
A_SOME_FIELD,
B_SOME_FIELD,
C_SOME_FIELD,
COALESCE_FIELD
from
(
select
A.SOME_FIELD as A_SOME_FIELD,
B.SOME_FIELD as B_SOME_FIELD,
C.SOME_FIELD as C_SOME_FIELD,
coalesce(A.SOME_FIELD, B.SOME_FIELD, c.SOME_FIELD) as COALESCE_FIELD
from
(select null as SOME_FIELD from RDB$DATABASE) A
full join
(select 1 as SOME_FIELD from RDB$DATABASE) B on B.SOME_FIELD = A.SOME_FIELD
full join
(select null as SOME_FIELD from RDB$DATABASE) C on C.SOME_FIELD = B.SOME_FIELD
) x

expected result (ожидаемый результат):

null null null null
null 1 null 1
null null null null

got result (полученный результат ):

null null null null
null 1 null null <---
null null null null

Problem with more then 2 full join and subrequest
in firebird 2.1.3 request works fine

ru: http://www.sql.ru/forum/actualthread.aspx?tid=1017752

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Tested with all FB 2.1.x versions and the result was:

null 1 null 1
null 1 null 1
null 1 null 1

@firebird-automations
Copy link
Collaborator Author

Commented by: Nikolay Teselko (nikolayv81)

changed: expected result (ожидаемый результат)

@firebird-automations
Copy link
Collaborator Author

Modified by: Nikolay Teselko (nikolayv81)

description: select
A_SOME_FIELD,
B_SOME_FIELD,
C_SOME_FIELD,
COALESCE_FIELD
from
(
select
A.SOME_FIELD as A_SOME_FIELD,
B.SOME_FIELD as B_SOME_FIELD,
C.SOME_FIELD as C_SOME_FIELD,
coalesce(A.SOME_FIELD, B.SOME_FIELD, c.SOME_FIELD) as COALESCE_FIELD
from
(select null as SOME_FIELD from RDB$DATABASE) A
full join
(select 1 as SOME_FIELD from RDB$DATABASE) B on B.SOME_FIELD = A.SOME_FIELD
full join
(select null as SOME_FIELD from RDB$DATABASE) C on C.SOME_FIELD = B.SOME_FIELD
) x

expected result (ожидаемый результат):

null null null null
null 1 null 1
null null null null

got result (полученный результат ):

null null null null
null 1 null null <---
null null null null

Problem with more then 2 full join and subrequest
in firebird 2.1.3 request works fine

ru: http://www.sql.ru/forum/actualthread.aspx?tid=1017752

=>

select
A_SOME_FIELD,
B_SOME_FIELD,
C_SOME_FIELD,
COALESCE_FIELD
from
(
select
A.SOME_FIELD as A_SOME_FIELD,
B.SOME_FIELD as B_SOME_FIELD,
C.SOME_FIELD as C_SOME_FIELD,
coalesce(A.SOME_FIELD, B.SOME_FIELD, c.SOME_FIELD) as COALESCE_FIELD
from
(select null as SOME_FIELD from RDB$DATABASE) A
full join
(select 1 as SOME_FIELD from RDB$DATABASE) B on B.SOME_FIELD = A.SOME_FIELD
full join
(select null as SOME_FIELD from RDB$DATABASE) C on C.SOME_FIELD = B.SOME_FIELD
) x

expected result (ожидаемый результат):

null 1 null null
null 1 null 1
null 1 null null

got result (полученный результат ):

null null null null
null 1 null null <---
null null null null

Problem with more then 2 full join and subrequest
in firebird 2.1.3 request works fine

ru: http://www.sql.ru/forum/actualthread.aspx?tid=1017752

@firebird-automations
Copy link
Collaborator Author

Modified by: Nikolay Teselko (nikolayv81)

description: select
A_SOME_FIELD,
B_SOME_FIELD,
C_SOME_FIELD,
COALESCE_FIELD
from
(
select
A.SOME_FIELD as A_SOME_FIELD,
B.SOME_FIELD as B_SOME_FIELD,
C.SOME_FIELD as C_SOME_FIELD,
coalesce(A.SOME_FIELD, B.SOME_FIELD, c.SOME_FIELD) as COALESCE_FIELD
from
(select null as SOME_FIELD from RDB$DATABASE) A
full join
(select 1 as SOME_FIELD from RDB$DATABASE) B on B.SOME_FIELD = A.SOME_FIELD
full join
(select null as SOME_FIELD from RDB$DATABASE) C on C.SOME_FIELD = B.SOME_FIELD
) x

expected result (ожидаемый результат):

null 1 null null
null 1 null 1
null 1 null null

got result (полученный результат ):

null null null null
null 1 null null <---
null null null null

Problem with more then 2 full join and subrequest
in firebird 2.1.3 request works fine

ru: http://www.sql.ru/forum/actualthread.aspx?tid=1017752

=>

select
A_SOME_FIELD,
B_SOME_FIELD,
C_SOME_FIELD,
COALESCE_FIELD
from
(
select
A.SOME_FIELD as A_SOME_FIELD,
B.SOME_FIELD as B_SOME_FIELD,
C.SOME_FIELD as C_SOME_FIELD,
coalesce(A.SOME_FIELD, B.SOME_FIELD, c.SOME_FIELD) as COALESCE_FIELD
from
(select null as SOME_FIELD from RDB$DATABASE) A
full join
(select 1 as SOME_FIELD from RDB$DATABASE) B on B.SOME_FIELD = A.SOME_FIELD
full join
(select null as SOME_FIELD from RDB$DATABASE) C on C.SOME_FIELD = B.SOME_FIELD
) x

expected result (ожидаемый результат):

null 1 null 1
null 1 null 1
null 1 null 1

got result (полученный результат ):

null null null null
null 1 null null <---
null null null null

Problem with more then 2 full join and subrequest
in firebird 2.1.3 request works fine

ru: http://www.sql.ru/forum/actualthread.aspx?tid=1017752

@firebird-automations
Copy link
Collaborator Author

Commented by: Nikolay Teselko (nikolayv81)

checked in oracle (expected result)

in 2.1 not worked fine

@firebird-automations
Copy link
Collaborator Author

Modified by: Nikolay Teselko (nikolayv81)

description: select
A_SOME_FIELD,
B_SOME_FIELD,
C_SOME_FIELD,
COALESCE_FIELD
from
(
select
A.SOME_FIELD as A_SOME_FIELD,
B.SOME_FIELD as B_SOME_FIELD,
C.SOME_FIELD as C_SOME_FIELD,
coalesce(A.SOME_FIELD, B.SOME_FIELD, c.SOME_FIELD) as COALESCE_FIELD
from
(select null as SOME_FIELD from RDB$DATABASE) A
full join
(select 1 as SOME_FIELD from RDB$DATABASE) B on B.SOME_FIELD = A.SOME_FIELD
full join
(select null as SOME_FIELD from RDB$DATABASE) C on C.SOME_FIELD = B.SOME_FIELD
) x

expected result (ожидаемый результат):

null 1 null 1
null 1 null 1
null 1 null 1

got result (полученный результат ):

null null null null
null 1 null null <---
null null null null

Problem with more then 2 full join and subrequest
in firebird 2.1.3 request works fine

ru: http://www.sql.ru/forum/actualthread.aspx?tid=1017752

=>

select
A_SOME_FIELD,
B_SOME_FIELD,
C_SOME_FIELD,
COALESCE_FIELD
from
(
select
A.SOME_FIELD as A_SOME_FIELD,
B.SOME_FIELD as B_SOME_FIELD,
C.SOME_FIELD as C_SOME_FIELD,
coalesce(A.SOME_FIELD, B.SOME_FIELD, c.SOME_FIELD) as COALESCE_FIELD
from
(select null as SOME_FIELD from RDB$DATABASE) A
full join
(select 1 as SOME_FIELD from RDB$DATABASE) B on B.SOME_FIELD = A.SOME_FIELD
full join
(select null as SOME_FIELD from RDB$DATABASE) C on C.SOME_FIELD = B.SOME_FIELD
) x

expected result (ожидаемый результат):
( checked in oracle )
null null null null
null 1 null 1
null null null null

got result (полученный результат ):

null null null null
null 1 null null <---
null null null null

Problem with more then 2 full join and subrequest
in firebird 2.1.3 request works NOT fine

ru: http://www.sql.ru/forum/actualthread.aspx?tid=1017752

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Good test case, it also shows a bug in FB3 (it returns one row filled with nulls)

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Version: 2.1.5 Update 1 [ 10522 ]

Version: 2.5.2 [ 10450 ]

Version: 2.1.5 [ 10420 ]

Version: 2.0.7 [ 10390 ]

Version: 2.5.1 [ 10333 ]

Version: 2.1.4 [ 10361 ]

Version: 2.5.0 [ 10221 ]

Version: 2.0.6 [ 10303 ]

Version: 3.0 Initial [ 10301 ]

Version: 2.1.3 [ 10302 ]

Version: 2.1.2 [ 10270 ]

Version: 2.0.5 [ 10222 ]

Version: 2.1.1 [ 10223 ]

Version: 2.1.0 [ 10041 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Nikolay Teselko (nikolayv81)

in our version of 2.1 (LI-V6.3.1.17910 Firebird 2.1) incorrect work full join

select
*
from
(select 1 as field1 from rdb$database) a
full join
(select 2 as field2 from rdb$database) b on b.field2=a.field1

got result:
1 2
1 2
expected result
null 2
1 null

i think it's different errors, i can't install last version of 2.1

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => In Progress [ 3 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: In Progress [ 3 ] => Open [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Link: This issue is related to CORE1246 [ CORE1246 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

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

summary: full outer join in subrequest with coalesce ( iif ) => Full outer join in derived table with coalesce (iif)

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

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

resolution: Fixed [ 1 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @sim1984

Whether it is impossible to port it in a branch 2.5.x?

@firebird-automations
Copy link
Collaborator Author

Commented by: Nikolay Teselko (nikolayv81)

Problem only in dsql/pass1.cpp.pass1_derived_table?
Why it not solved in 2.5.3 ( code of func is similar to 3.0 not fixed version)???

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

Fix backported to 2.5.3.

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Fix Version: 2.5.3 [ 10461 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA525 [ QA525 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pmakowski

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Closed [ 6 ] => Closed [ 6 ]

QA Status: Done successfully

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue relate to CORE4873 [ CORE4873 ]

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