Hi. I have a reproduction of strange behavior of select statement.
Reproduction:
--drop table test_a1
--drop table test_a2
create table test_a1
(
id1 number(19),
value1 number(1)
)
/
create table test_a2
(
id2 number(19),
value2 number(1) not null
)
/
insert into test_a1 values (1, 1)
/
commit
/
select *
from
test_a1
left join test_a2 on test_a1.id1 = test_a2.id2 and test_a2.value2 = 100
where 1=1
and test_a1.value1 = 1
and (test_a1.value1 = 0 or test_a2.value2 = 100)
/
According to conditions it should not return anything. But result is:
ID1 VALUE1 ID2 VALUE2
---------- ---------- ---------- ----------
1 1
The plan is:
Plan hash value: 2122087141
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 52 | 5 (0)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 1 | 52 | 5 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| TEST_A1 | 1 | 26 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| TEST_A2 | 1 | 26 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("TEST_A1"."ID1"="TEST_A2"."ID2"(+))
2 - filter("TEST_A1"."VALUE1"=1)
3 - filter("TEST_A2"."VALUE2"(+)=100)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Where it considers last predicate as left join (+). But it should be inner join because it is in 'where' clause!
So the question is why is it happening or maybe I was wrong somewhere? Maybe there is some logical explanation?
19c gives the behaviour you expect:
select * from v$version;
BANNER BANNER_FULL BANNER_LEGACY CON_ID
Oracle Database 19c EE High Perf Release 19.0.0.0.0 - Production Oracle Database 19c EE High Perf Release 19.0.0.0.0 - Production
Version 19.3.0.0.0 Oracle Database 19c EE High Perf Release 19.0.0.0.0 - Production 0
create table test_a1
(
id1 number(19),
value1 number(1)
)
/
create table test_a2
(
id2 number(19),
value2 number(1) not null
)
/
insert into test_a1 values (1, 1)
/
commit
/
select *
from
test_a1
left join test_a2 on test_a1.id1 = test_a2.id2 and test_a2.value2 = 100
where 1=1
and test_a1.value1 = 1
and (test_a1.value1 = 0 or test_a2.value2 = 100)
/
no rows selected
I can't track down which bug is causing this. But it also gives the expected result in 11.2. So using the /*+ optimizer_features_enable('11.2.0.4') */ hint may be a workout until you can patch/upgrade:
select * from v$version;
BANNER
Oracle Database 11g EE High Perf Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
create table test_a1
(
id1 number(19),
value1 number(1)
)
/
create table test_a2
(
id2 number(19),
value2 number(1) not null
)
/
insert into test_a1 values (1, 1)
/
commit
/
select *
from
test_a1
left join test_a2 on test_a1.id1 = test_a2.id2 and test_a2.value2 = 100
where 1=1
and test_a1.value1 = 1
and (test_a1.value1 = 0 or test_a2.value2 = 100)
/
no rows selected