left join with coalesce where clause return different results in 2.5.5 and 3.0
RC2
----------------------------------------------------------------------------------
Key: CORE-5150
URL: http://tracker.firebirdsql.org/browse/CORE-5150
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 3.0 RC2
Environment: windows 10 64bit; fiebird 3.0 RC2 34bit / 64bit
Reporter: Ugur Soyan
create table master(id integer not null primary key, x integer);
create table trn(id integer not null primary key, master_id integer);
insert into master(id,x) values(1,0);
insert into master(id,x) values(2,null);
insert into master(id,x) values(3,1);
insert into trn(id,master_id) values(1,1);
insert into trn(id,master_id) values(2,1);
insert into trn(id,master_id) values(3,2);
insert into trn(id,master_id) values(4,2);
insert into trn(id,master_id) values(5,3);
insert into trn(id,master_id) values(6,3);
insert into trn(id,master_id) values(7,3);
------------
Select t.id trn_id, t.master_id trn_master_id, m.id master_id, m.x
from trn t
left outer join master m on m.id = t.master_id
where coalesce( m.x ,0 ) = 0
------------
--- Firebird 2.5 Result ---
TRN_ID | TRN_MASTER_ID | MASTER_ID | X
------------------------------------------
1 | 1 | 1 | 0
2 | 1 | 1 | 0
3 | 2 | 2 |(null)
4 | 2 | 2 |(null)
------------------------------------------
--- Firebird 3 RC Result ---
TRN_ID | TRN_MASTER_ID | MASTER_ID | X
------------------------------------------
1 | 1 | 1 | 0
2 | 1 | 1 | 0
3 | 2 | 2 |(null)
4 | 2 | 2 |(null)
5 | 3 |(null) |(null)
6 | 3 |(null) |(null)
7 | 3 |(null) |(null)
------------------------------------------
--
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
------------------------------------------------------------------------------
Transform Data into Opportunity.
Accelerate data analysis in your applications with
Intel Data Analytics Acceleration Library.
Click to learn more.
http://pubads.g.doubleclick.net/gampad/clk?id=278785111&iu=/4140
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
