Submitted by: skopalik (skopalik)

Assigned to: @samofatov

SFID: 496784#⁠
Submitted By: skopalik

When optimizer find indexies for Left Join,
work like Inner Join.
There is sql example:

CREATE DATABASE "localhost:d:\ibdata\test.gdb"
USER "sysdba" PASSWORD "masterkey";

Create table Operace (
idProducts integer NOT NULL,
idNextDev Smallint,
Primary Key (idProducts)
);

Create table Devices (
id Smallint NOT NULL,
Primary Key (id)
);

Create table Products (
id integer NOT NULL,
Primary Key (id)
);

Alter table Operace add foreign key (idProducts)
references Products (id) on update no action on
delete no action ;

insert into devices(id) values(0);
insert into devices(id) values(1);
insert into products values(0);
insert into products values(1);
insert into operace values(0,1);
insert into operace values(1,1);

/* This query is correct */
select *
from OPERACE O
join PRODUCTS P on P.ID=O.IDPRODUCTS
left join DEVICES D on D.ID=O.IDNEXTDEV+0 and D.ID=0;

/* This query failed */
select *
from OPERACE O
join PRODUCTS P on P.ID=O.IDPRODUCTS
left join DEVICES D on D.ID=O.IDNEXTDEV and D.ID=0;