Why does Oracle allow having several subqueries with the same alias_name in a WITH clause?

Question

It looks a bit funny but Oracle 11g really allows such a query to run

with
    a as (select 1 from dual),
    a as (select 2 from dual)
select  *
from    a;

It returns the result of the first subquery (i.e. 1).

I just can't imagine a situation when such a feature could be useful. In my case it rather caused a problem when I forgot to rename a subquery after copy/pasting it and the overall query was returning wrong/unexpected results. Fortunately the query was quite simple and the reason was detected immediately.

Anyway, I'd expect Oracle to throw and exception in such a case.

So, my question is whether such a behaviour is a feature or a bug? If feature, where it could be useful?

Thanks.

BTW, SQLite doesn't allow similar query to run and throws 'duplicate WITh table name' exception. Didn't try any other db engines yet.

Answer 1

The WITH clause can also be used with functions. I'm not sure if this was available with 11g, but it is with 12c. So this "bug" may be necessary for overloaded functions.

For example, this procedure uses the exact same function twice, with the same input/output data types.

WITH 
   FUNCTION get_date(pid IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN 'date is '|| pid;
END;
  FUNCTION get_date(pid IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN 'date is '|| pid;
END;
SELECT get_date(cast(sysdate as varchar2(20)))
FROM   dual  
;
/

As expected, it returns an error:

ORA-06553: PLS-305: previous use of 'GET_DATE' (at line 1) conflicts with this use

But if you overload the function, so it has the same name but accepts different parameter types, it will work. The procedure will run without error, and depending on the variable data type, the correct function is used.

WITH 
   FUNCTION get_date(pid IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN 'date is '|| pid;
END;
  FUNCTION get_date(pid IN DATE) RETURN VARCHAR2 IS
BEGIN
RETURN pid+1;
END;
SELECT 
get_date(cast(sysdate as varchar2(20))) /*example1*/
--get_date(sysdate)                    /*example2*/
FROM   dual  
;
/

Example 1 output: date is 16-MAR-17 Example 2 output: 17-MAR-17

So perhaps being able to use subqueries with the same name is related to allowing overloaded functions. Though it still seems buggy, and I haven't been able to find documentation on this.