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-issue-importer opened this issue Apr 17, 2013 · 24 comments
Closed

Comments

@firebird-issue-importer

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-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 17, 2013

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-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 17, 2013

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-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 17, 2013

Commented by: Nikolay Teselko (nikolayv81)

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

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 17, 2013

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-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 17, 2013

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-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 17, 2013

Commented by: Nikolay Teselko (nikolayv81)

checked in oracle (expected result)

in 2.1 not worked fine

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 17, 2013

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-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 17, 2013

Commented by: @dyemanov

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

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 17, 2013

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-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 17, 2013

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-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 18, 2013

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 24, 2013

Modified by: @dyemanov

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

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 28, 2013

Modified by: @dyemanov

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

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 6, 2013

Modified by: @asfernandes

Link: This issue is related to CORE1246 [ CORE1246 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 6, 2013

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-issue-importer
Copy link
Author

firebird-issue-importer commented May 6, 2013

Modified by: @asfernandes

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

resolution: Fixed [ 1 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 6, 2013

Commented by: @sim1984

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

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 7, 2013

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-issue-importer
Copy link
Author

firebird-issue-importer commented May 9, 2013

Commented by: @asfernandes

Fix backported to 2.5.3.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 9, 2013

Modified by: @asfernandes

Fix Version: 2.5.3 [ 10461 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Mar 29, 2014

Modified by: @pcisar

Link: This issue is related to QA525 [ QA525 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 10, 2014

Modified by: @pmakowski

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

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jul 12, 2015

Modified by: @pavel-zotov

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

QA Status: Done successfully

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jul 13, 2015

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