Issue Details (XML | Word | Printable)

Key: CORE-4083
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Critical Critical
Assignee: Adriano dos Santos Fernandes
Reporter: Nikolay Teselko
Votes: 1
Watchers: 3
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Full outer join in derived table with coalesce (iif)

Created: 17/Apr/13 09:43 AM   Updated: 13/Jul/15 09:37 AM
Component/s: Engine
Affects Version/s: 2.1.0, 2.1.1, 2.0.5, 2.1.2, 2.1.3, 3.0 Initial, 2.0.6, 2.5.0, 2.1.4, 2.5.1, 2.0.7, 2.1.5, 2.5.2, 2.1.5 Update 1, 2.5.2 Update 1
Fix Version/s: 3.0 Alpha 1, 2.5.3

Environment:
LI-V6.3.2.26540 Firebird 2.5 ( тестовый x64 debian wheezy, fb c firebirsql.org, Intel i5)
LI-V6.3.2.26508 Firebird 2.5 ( основной рабочий x64 debian squeeze, fb из wheezy, Intel xeon)
LI-V6.3.2.26539 Firebird 2.5 ( тестовый x64 debian wheezy, fb c firebirsql.org, Intel i5)
Issue Links:
Relate

QA Status: Done successfully


 Description  « Hide
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

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 17/Apr/13 09:56 AM
Tested with all FB 2.1.x versions and the result was:

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

Nikolay Teselko added a comment - 17/Apr/13 10:07 AM
changed: expected result (ожидаемый результат)

Nikolay Teselko added a comment - 17/Apr/13 10:22 AM
checked in oracle (expected result)

in 2.1 not worked fine

Dmitry Yemanov added a comment - 17/Apr/13 10:53 AM
Good test case, it also shows a bug in FB3 (it returns one row filled with nulls)

Nikolay Teselko added a comment - 17/Apr/13 11:54 AM
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

Simonov Denis added a comment - 06/May/13 05:02 AM
Whether it is impossible to port it in a branch 2.5.x?

Nikolay Teselko added a comment - 07/May/13 09:59 AM - edited
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)???

Adriano dos Santos Fernandes added a comment - 09/May/13 01:19 AM
Fix backported to 2.5.3.