SQL Returns wrong result due to Join elimination using composite foreign key (Doc ID 2589341.1)

Last updated on APRIL 17, 2023

Applies to:

Symptoms

• Wrong results  from a query  joining two tables with composite foreign key
• Following is example testcase:  
  create table test_a (a1 varchar2(1), a2 number, a3 number)
  /
  create table test_b (a1 varchar2(1), a2 number, a3 number)

  /
  alter table test_a add constraint unq_test_a unique (a1,a2,a3)
  /
  alter table test_b add constraint fk_test_b foreign key (a1,a2,a3) references test_a (a1,a2,a3)
  /
  insert into test_a values (1, 2, 3)
  /
  insert into test_b values (null, 2, 3) --- null value for one of the columns
  /
  commit
  /
  /no record return/ <<<<<<<<<<<<<<<<<<<<<<<<<<<<<
  select 1 rec from test_b b
  where not EXISTs (select 1 from test_a a where a.a1 = b.a1 and a.a2 = b.a2 and a.a3 = b.a3)
  /
  alter table test_b drop constraint fk_test_b
  /
  /returns record after drop foreign key/ <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
  select 1 rec from test_b b
  where not EXISTs (select 1 from test_a a where a.a1 = b.a1 and a.a2 = b.a2 and a.a3 = b.a3)
  /