Oracle Select返回它不应该的行

嗨。我复制了select语句的奇怪行为。

复制:

--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)
/

根据条件，它不应该归还任何东西。但是结果是:

       ID1     VALUE1        ID2     VALUE2
---------- ---------- ---------- ----------
         1          1                      

计划是:

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)

其中它认为最后一个谓词为左联接 ()。但它应该是inner join，因为它在 'where' 子句中!
所以问题是为什么会发生这种情况，或者我在某个地方错了？也许有一些合乎逻辑的解释？

19c给出了您期望的行为:

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

我无法找到是哪个错误导致了这种情况。但它也给出了11.2的预期结果。因此，使用/* optimizer_features_enable('11.2.0.4 ') */ hint可能是一个锻炼，直到您可以修补/升级:

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