Hi,
The following syntax:
SELECT any_alias.correct_alias.column FROM.. LEFT JOIN..
works on 10g and 11g, however fails on 12c as expected.
Setup
DROP TABLE tbla PURGE;
DROP TABLE tblb PURGE;
CREATE TABLE tbla
( pk_col_a INT PRIMARY KEY,
v_col_a VARCHAR2(50)
);
CREATE TABLE tblb
( pk_col_b INT PRIMARY KEY,
fk_a INT,
v_col_b VARCHAR2(50)
);
INSERT INTO tbla SELECT 1, '1A' FROM dual;
INSERT INTO tbla SELECT 2, '1A' FROM dual;
INSERT INTO tblb SELECT 1, 1, '1B1A' FROM dual;
INSERT INTO tblb SELECT 2, 1, '2B1A' FROM dual;
Test case 1 : 11g
SQL> SELECT banner FROM v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL>
SQL> SELECT b.b.v_col_b
2 FROM tbla A
3 LEFT JOIN tblb b
4 ON a.pk_col_a = b.fk_a;
V_COL_B
--------------------------------------------------
1B1A
2B1A
SQL> SELECT xyz.b.v_col_b
2 FROM tbla A
3 LEFT JOIN tblb b
4 ON a.pk_col_a = b.fk_a;
V_COL_B
--------------------------------------------------
1B1A
2B1A
Test case 2 : 12c
SQL> SELECT banner FROM v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE 12.1.0.1.0 Production
TNS for 64-bit Windows: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production
SQL> SELECT b.b.v_col_b
2 FROM tbla A
3 LEFT JOIN tblb b
4 ON a.pk_col_a = b.fk_a;
SELECT b.b.v_col_b
*
ERROR at line 1:
ORA-00904: "B"."B"."V_COL_B": invalid identifier
I see the same behaviour on 10.2.0.5 and 11.2.0.3.
If I change the outer join with inner join, it would fail with ORA-00904.
Possibly a bug?
Regards,
Lalit