Impact of Using Same Alias Twice in Huge SQL Statement [message #613133] |
Wed, 30 April 2014 07:55 |
|
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 #613155 is a reply to message #613151] |
Wed, 30 April 2014 09:25 |
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
[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 #613165 is a reply to message #613158] |
Wed, 30 April 2014 09:57 |
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 |
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
[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 #613174 is a reply to message #613171] |
Wed, 30 April 2014 10:31 |
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 #613199 is a reply to message #613176] |
Wed, 30 April 2014 12:32 |
|
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...
|
|
|