The Alias Trap
Generally an alias in a query is there to make it easier to understand, either for the developer or the database. However the Trickster can reuse aliases within a query to make things more confusing.
desc scott.emp
Name Null? Type
---------- -------- --------------------
1 EMPNO NOT NULL NUMBER(4)
2 ENAME VARCHAR2(10)
3 JOB VARCHAR2(9)
4 MGR NUMBER(4)
5 HIREDATE DATE
6 SAL NUMBER(7,2)
7 COMM NUMBER(7,2)
8 DEPTNO NUMBER(2)
desc scott.salgrade
Name Null? Type
--------- -------- --------------------
1 GRADE NUMBER
2 LOSAL NUMBER
3 HISAL NUMBER
desc scott.dept
Name Null? Type
--------- -------- --------------------
1 DEPTNO NOT NULL NUMBER(2)
2 DNAME VARCHAR2(14)
3 LOC VARCHAR2(13)
The trickster can try queries such as
SELECT e.ename, e.grade
FROM scott.emp e
JOIN scott.salgrade e ON e.sal BETWEEN e.losal AND e.hisal;
and
SELECT x.ename, x.dname
from scott.emp x join scott.dept x using (deptno);
As long as any prefixed column names involved are unique to a table, the database can work out what to do.
If you find this in a 'live' query, it is normally one with at least half a dozen tables where an extra join has been added without noticing that the alias is already in use. And you'll discover it when a column is added to one of those tables causing the database to throw up its hands in surrender. Sometimes you will find it in a dynamically constructed query, when it will fail seemingly at random.
Once discovered, it isn't a mentally difficult bug to resolve. But first you have to get past the mental roadblock of "But all the columns already have an alias pointing to the table they come from".
No comments:
Post a Comment