Home » SQL & PL/SQL » SQL & PL/SQL » Invalid table alias with left join behaves differently on different versions (Ver : 10.2.0.5, 11.2.0.2, 11.2.0.3, 12.1.0.1 OS : Windows, Linux)
icon5.gif  Invalid table alias with left join behaves differently on different versions [message #638171] Fri, 05 June 2015 01:43 Go to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
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
Re: Invalid table alias with left join behaves differently on different versions [message #638175 is a reply to message #638171] Fri, 05 June 2015 02:28 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
Definitely a bug - it only works with a left join if you're referencing the outer-joined table. So this fails as well:
SELECT a.a.v_col_a
FROM tbla A
LEFT JOIN tblb b
ON a.pk_col_a = b.fk_a;
Re: Invalid table alias with left join behaves differently on different versions [message #638176 is a reply to message #638175] Fri, 05 June 2015 02:30 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
That's on 11.2.0.3.0 and 10.2.0.2.0
Re: Invalid table alias with left join behaves differently on different versions [message #638178 is a reply to message #638176] Fri, 05 June 2015 04:10 Go to previous message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Thanks for verifying it.
Previous Topic: How to get recursive result
Next Topic: Date range from table
Goto Forum:
  


Current Time: Wed Apr 24 15:18:09 CDT 2024