Attention! - The problem remains also if I use the same alias name!!! - that means - I have to use in every case a different alias!!

I stumbled across this behavior several months ago when I was doing some data profiling. Suddenly I realized I needed to double-check my results! The behavior is easily demonstrated:

SQL> select count(*) from user_tables where table_name in ( select table_name from user_views );

    COUNT(*)
------------
          58

SQL> select count(*) from user_tables --where table_name in ( select table_name from user_views );

    COUNT(*)
------------
          58

Clearly there's no TABLE_NAME column in USER_VIEWS, but the first query runs!

I was looking for anomalies between a reference table and the transaction table, and I never found any. I kept plugging away -- until at one point I realized I was comparing a "reference table" that didn't even have the column in question:

SQL> select count(*) from user_tables where table_name not in ( select table_name from user_views );

    COUNT(*)
------------
           0

USER_VIEWS contains the names of every table?? No... not quite!

Looking into the 10053 Trace could explain the things even better.

demo@ORA12C> @10053

Session altered.

demo@ORA12C> explain plan for
  2  SELECT     a.tb_id
  3  FROM       suh_tb_test a
  4  WHERE      a.tb_id NOT IN  (SELECT         tb_id   FROM    suh_tb_test_1);

Explained.

demo@ORA12C> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Final query after transformations:******* UNPARSED QUERY IS *******

SELECT "A"."TB_ID" "TB_ID"
FROM "DEMO"."SUH_TB_TEST" "A"
WHERE NOT EXISTS
  (SELECT 0
  FROM "DEMO"."SUH_TB_TEST_1" "SUH_TB_TEST_1"
  WHERE LNNVL("A"."TB_ID"<>"A"."TB_ID")
  )