Home » SQL & PL/SQL » SQL & PL/SQL » Impact of Using Same Alias Twice in Huge SQL Statement (SQL)
Impact of Using Same Alias Twice in Huge SQL Statement [message #613133] Wed, 30 April 2014 07:55 Go to next message
smedvid
Messages: 5
Registered: April 2014
Junior Member
Something I have never seen before and not sure of impact... Same Alias for a Table/Sub-Query used twice in same SQL SELECT...

Assumed ownership huge SQL INSERT that references perhaps 75+ tables and also includes Sub-Selects. Found same "exact" Alias is used twice within the same SELECT JOINs... Which one takes precedence or does Oracle assign a system reference and it is really working?

LEFT JOIN TEMPTABLE03 TEMPX ON TEMPX.ID = MAIN.ID
... etc...
LEFT JOIN
(
SELECT AA.* , ROW_NUMBER () OVER (PARTITION BY AA.ID ORDER BY AA.TEMPDATE DESC) AS ROW_NUM
FROM
USER1.TEMPABC AA
) TEMPX ON TEMPX.ID = MAIN.ID AND TEMPX.ROW_NUM = 1
...etc...


Re: Impact of Using Same Alias Twice in Huge SQL Statement [message #613136 is a reply to message #613133] Wed, 30 April 2014 08:04 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Would expect something like this, but it depends how it is structured, brackets will let you get away with it. But not get away with the slap I'd send your way Wink


select dummy from dual a, dual a where a.dummy=a.dummy
                                               *
ERROR at line 1:
ORA-00918: column ambiguously defined
Re: Impact of Using Same Alias Twice in Huge SQL Statement [message #613143 is a reply to message #613136] Wed, 30 April 2014 08:29 Go to previous messageGo to next message
smedvid
Messages: 5
Registered: April 2014
Junior Member
Try looking at below example that not only compiled, ran without any error.
This similates code I have a question about...

SELECT A.ID, B.STATE
FROM MAINTABLE A
LEFT JOIN SECONDTABLE B
ON A.ID = B.ID
LEFT JOIN
(SELECT C.* FROM SECONDTABLE C) B
ON A.ID = B.ID

--Again, I did not write this code! and issue will be fixed in future...

[Updated on: Wed, 30 April 2014 08:30]

Report message to a moderator

Re: Impact of Using Same Alias Twice in Huge SQL Statement [message #613151 is a reply to message #613143] Wed, 30 April 2014 09:05 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
That should error out. Can you tell us your exact oracle version and supply create table statements for the tables so we can try and recreate it?
Re: Impact of Using Same Alias Twice in Huge SQL Statement [message #613155 is a reply to message #613151] Wed, 30 April 2014 09:25 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Maybe ANSI syntax is carrying it.

edit: Yup

 select * from dual a left join dual a on a.dummy=a.dummy;

D D
- -
X X

  1  select * from dual a
  2  join dual a on a.dummy=a.dummy
  3* left join dual a on a.dummy=a.dummy
sql> /

D D D
- - -
X X X

Elapsed: 00:00:00.00
sql> ed
Wrote file afiedt.buf

  1  select * from dual a
  2  join dual a on a.dummy=a.dummy
  3* left join dual a on a.dummy=a.dummy||'a'
sql> /

D D D
- - -
X X





I suspect it's going to lead to all sorts of unexpected behaviour though. I can't make up my mind if it is being really clever, or really stupid. I lean to the latter.

Yup. Stupid.

  1  select * from dual a
  2  join dual a on a.dummy=a.dummy
  3* left join (select 'Y' dummy from dual) a on a.dummy=a.dummy
sql> /

D D D
- - -
X X Y


  1  select * from dual a
  2  join (select 'Z' dummy from dual) a on a.dummy=a.dummy
  3* left join (select 'Y' dummy from dual) a on a.dummy=a.dummy
sql> /

D D D
- - -
Z Z Y





That said, Oracle has a good track record (which I applaud) of letting people do really stupid things. An excellent learning mechanism in my experience Smile

[Updated on: Wed, 30 April 2014 09:31]

Report message to a moderator

Re: Impact of Using Same Alias Twice in Huge SQL Statement [message #613158 is a reply to message #613155] Wed, 30 April 2014 09:43 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
just because you can do something, it does not mean you should do it.
You can poke yourself in the eye with a sharp pencil; just as you can use same column alias more than once in a SQL statement.
You are advised to avoid taking either action; then no problem will exist.
Re: Impact of Using Same Alias Twice in Huge SQL Statement [message #613165 is a reply to message #613158] Wed, 30 April 2014 09:57 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
It's a bug:
Connected to:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

SQL> select * from dual a
join dual a on a.dummy=a.dummy
left join dual a on a.dummy=a.dummy;
  2    3  select * from dual a
*
ERROR at line 1:
ORA-00918: column ambiguously defined


SQL>


@Roachcoach - which version are you using?
Re: Impact of Using Same Alias Twice in Huge SQL Statement [message #613166 is a reply to message #613165] Wed, 30 April 2014 09:59 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production



Edit: Still works with optimizer_features_enable='11.2.0.1'


Edit2: Also works in

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Solaris: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production




Edit3: aaaaand 11.2.0.2.0

"breaks" in 11.1.0.7 though Smile

[Updated on: Wed, 30 April 2014 10:06]

Report message to a moderator

Re: Impact of Using Same Alias Twice in Huge SQL Statement [message #613167 is a reply to message #613166] Wed, 30 April 2014 10:05 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I get the error in 10.2.0.2.0, 10.2.0.5.0, 11.1.0.7.0 and 11.2.0.1.0.
It works in 11.2.0.3.0.

I have to assume it's a bug. Has anyone got a 12c instance they can try it on?
Re: Impact of Using Same Alias Twice in Huge SQL Statement [message #613171 is a reply to message #613167] Wed, 30 April 2014 10:16 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
12c here showing it working too

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

[Updated on: Wed, 30 April 2014 10:16]

Report message to a moderator

Re: Impact of Using Same Alias Twice in Huge SQL Statement [message #613174 is a reply to message #613171] Wed, 30 April 2014 10:31 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
It only works if the alias isn't used anywhere other than the join clauses:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

SQL> select * from dual a
join dual a on a.dummy=a.dummy
left join dual a on a.dummy=a.dummy;  2    3

D D D
- - -
X X X

SQL> select * from dual a
join dual a on a.dummy=a.dummy
left join dual a on a.dummy=a.dummy
WHERE a.dummy = 'Y';  2    3    4
WHERE a.dummy = 'Y'
      *
ERROR at line 4:
ORA-00918: column ambiguously defined


SQL> select a.* from dual a
join dual a on a.dummy=a.dummy
left join dual a on a.dummy=a.dummy;  2    3
select a.* from dual a
         *
ERROR at line 1:
ORA-00918: column ambiguously defined


SQL>
Re: Impact of Using Same Alias Twice in Huge SQL Statement [message #613176 is a reply to message #613174] Wed, 30 April 2014 10:40 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Yes, I suspect ANSI implementation bug as it seems to only work in an "ON" clause.

Either way, write code like that....hell mend ya Wink

[Updated on: Wed, 30 April 2014 10:41]

Report message to a moderator

Re: Impact of Using Same Alias Twice in Huge SQL Statement [message #613199 is a reply to message #613176] Wed, 30 April 2014 12:32 Go to previous message
smedvid
Messages: 5
Registered: April 2014
Junior Member
Thanks for confirmation on this Bizzaro error I found...
Guess you never know what you will find when you open stuff from others.

I will certainly remove the duplicate alias...
from the massive and long SQL Insert Statement with 75+ Loins.

Still lots to review and optimize...

Thanks again all...
Previous Topic: HSODBC.EXE, unable to find corresponding oracle session
Next Topic: Oracle Roles -- Users -- Grant Privileges
Goto Forum:
  


Current Time: Fri Mar 29 01:38:59 CDT 2024