Different execution plans and joins using synonym or table

Edit:

We found the leading cause. If you join tables with the same name but in different schemas, you can get this result. Same with joining two tables using the same alias (whyever this works at all).

---

We have a very strange issue using Oracle 12.1.0.2.

This is the query:

SELECT * FROM OWNER1.TABLE1 JOIN OWNER2.TABLE2 USING (COLUMN1, COLUMN2, COLUMN3, COLUMN4) WHERE COLUMN1=10 AND COLUMN2='string' AND COLUMN3='01.04.2018' ORDER BY COLUMN3;

This results in a cartesian product of the two tables, returning a wrong dataset and thousands of rows.

This is the same query, but this time using the public synonym OWNER1TABLE1 for the table OWNER1.TABLE1:

SELECT * FROM OWNER1TABLE1 JOIN OWNER2.TABLE2 USING (COLUMN1, COLUMN2, COLUMN3, COLUMN4) WHERE COLUMN1=10 AND COLUMN2='string' AND COLUMN3='01.04.2018' ORDER BY COLUMN3;

This results in the correct data being returned (only 16 rows).

Oracle is also using two quite different execution plans:

Using the table name:

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                           
-------------------------------------------------------------
Plan hash value: 3737086414
-------------------------------------------------------------
| Id  | Operation                           | Name          |
-------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |
|   1 |  MERGE JOIN CARTESIAN               |               |
|   2 |   PARTITION RANGE SINGLE            |               |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID| OWNER2.TABLE2 |
|   4 |     INDEX RANGE SCAN                | O2.IDX        |
|   5 |   BUFFER SORT                       |               |
|   6 |    PARTITION RANGE ALL              |               |
|   7 |     TABLE ACCESS FULL               | OWNER1.TABLE1 |
-------------------------------------------------------------

Using the public synonym:

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                           
---------------------------------------------------------------------
Plan hash value: 1141852644
 
---------------------------------------------------------------------
| Id  | Operation                                   | Name          |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |               |
|   1 |  HASH JOIN                                  |               |
|   2 |   PARTITION RANGE SINGLE                    |               |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| OWNER1.TABLE1 |
|   4 |     INDEX RANGE SCAN                        | OWNER1.PKY$T1 |
|   5 |   PARTITION RANGE SINGLE                    |               |
|   6 |    TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| OWNER2.TABLE2 |
|   7 |     INDEX RANGE SCAN                        | O2.IDX        |
---------------------------------------------------------------------

The synonym is defined like this:

CREATE public synonym OWNER1TABLE1 FOR OWNER1.TABLE1;

And maybe the strangest thing is if we define an alias (e.g. T1) for OWNER1.TABLE1 in the query like

SELECT * FROM OWNER1.TABLE1 T1 JOIN OWNER2.TABLE2 USING (COLUMN1, COLUMN2, COLUMN3, COLUMN4) WHERE COLUMN1=10 AND COLUMN2='string' AND COLUMN3='01.04.2018' ORDER BY COLUMN3;

…it returns the correct data, too.

Does anyone know about a bug or something? Can’t really be expected behavior, can it?