SQL & PL/SQL

I was trying a query in the schema SCOTT and the problems can be replicated in https://livesql.oracle.com with Oracle 19c.

The idea is to obtain all the possible pairs (e1, e2) where e1 and e2 are empno codes, and these conditions are satisfied:

1. e1 <> e2
2. e2 is not manager of e1
3. e1 is not manager of e2

I solved it with this query and the results (a total of 156 rows) obtained are correct:

select e1.empno, e2.empno 
from SCOTT.emp e1 CROSS JOIN SCOTT.emp e2 
where e1.empno != e2.empno 
and (e1.empno, e2.empno) not in (select empno, mgr from SCOTT.emp where mgr is not null) 
and (e2.empno, e1.empno) not in (select empno, mgr from SCOTT.emp where mgr is not null);

Then I tried to do the same query by restructuring the subquery. The subquery returns the mgr and then the empno, and the left-side of the predicate compares the pair (e1.empno, e2.empno). The meaning is the same as in the first query, but the result is completely different! It obtains a total of 169 rows.

select e1.empno, e2.empno 
from SCOTT.emp e1 CROSS JOIN SCOTT.emp e2 
where e1.empno != e2.empno 
and (e1.empno, e2.empno) not in (select empno, mgr from SCOTT.emp where mgr is not null) 
and (e1.empno, e2.empno) not in (select mgr, empno from SCOTT.emp where mgr is not null);

By checking the plans of both queries. The first one checks all the predicates but the second one does not. Is it assuming that both subqueries are the same and skips the second one?

The plan of the first query (correct):

Plan hash value: 1067877273

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24 | 13 (0)| 00:00:01 |
|* 1 | HASH JOIN ANTI | | 1 | 24 | 13 (0)| 00:00:01 |
|* 2 | HASH JOIN ANTI | | 110 | 1760 | 10 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 182 | 1456 | 7 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | PK_EMP | 14 | 56 | 1 (0)| 00:00:01 |
|* 5 | INDEX FAST FULL SCAN| PK_EMP | 13 | 52 | 0 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | EMP | 13 | 104 | 3 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | EMP | 13 | 104 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("E1"."EMPNO"="EMPNO" AND "E2"."EMPNO"="MGR")
2 - access("E2"."EMPNO"="EMPNO" AND "E1"."EMPNO"="MGR")
5 - filter("E1"."EMPNO"<>"E2"."EMPNO")
6 - filter("MGR" IS NOT NULL)
7 - filter("MGR" IS NOT NULL)

Note
-----
- this is an adaptive plan

The plan of the second query (incorrect):

Plan hash value: 3035832168

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 110 | 1760 | 10 (0)| 00:00:01 |
|* 1 | HASH JOIN ANTI | | 110 | 1760 | 10 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 182 | 1456 | 7 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_EMP | 14 | 56 | 1 (0)| 00:00:01 |
|* 4 | INDEX FAST FULL SCAN| PK_EMP | 13 | 52 | 0 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMP | 13 | 104 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("E1"."EMPNO"="EMPNO" AND "E2"."EMPNO"="MGR")
4 - filter("E1"."EMPNO"<>"E2"."EMPNO")
5 - filter("MGR" IS NOT NULL)

I think this is an important bug. What is doing the planner to skip one predicate? In Postgres both queries return correct results (156 rows)!