0

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.

Anyone knows about a bug or something? Can't really be expected behaviour, can it?

  • Please put the edit in an answer - as it seems to be a solution, or at least an explanation of the issue. – ypercubeᵀᴹ Sep 18 '18 at 16:15
  • @ypercubeᵀᴹ well it's no answer at all, it only clarifies the cause. I'd like to know why Oracle behaves this way. – Lenniey Sep 19 '18 at 7:13
  • OK. I guess it's ok to stay in the question then. – ypercubeᵀᴹ Sep 19 '18 at 8:05
0

Bug 18856999 - Wrong results (wrong cursor) with schema table masking public synonym (Doc ID 18856999.8)

There is a one-off patch for this with the same number for 12.1.0.2. The fix is in some later PSUs and starting with 12.2.0.1.

As usual, the above requires an active support subscription.

  • Thank you, but it shouldn't be that. A cursor may select from the wrong table if a table in the current schema shares the same name as a public synonym that points to a table in some other schema. Rediscovery Notes If a table in the current schema shares the same name as a public synonym pointing at a table in some other schema and a cursor executed in the current schema selects from the public synonym instead of the local table, it could be this bug. The synonym isn't pointing to another schema. – Lenniey Sep 19 '18 at 7:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.