Ambiguity resolved.

Another case where the order of the tables within the FROM clause has an impact.

Two tables, with the same column names (and data types).

> desc t2
   Name      Null?    Type
   --------- -------- ---------------
1  ID                 NUMBER
2  VAL                VARCHAR2(10)

> desc t1
   Name      Null?    Type
   --------- -------- ---------------
1  ID                 NUMBER
2  VAL                VARCHAR2(10)




Put together a SELECT * query. Now, which table has the value 'Blue' and which is 'Red'.
 
> select * from (select * from t2 join t1 on t1.id = t2.id);
QCSJ_C000000000400000 QCSJ_C0000 QCSJ_C000000000400001 QCSJ_C0000
--------------------- ---------- --------------------- ----------
                 1.00 Blue                        1.00 Red

It appears to be the order of the tables in the where clause (so the first two columns are from T2 and the latter from T1). Sensibly, that happens irrespective of the path the optimizer takes, so a change in plans won't suddenly change the results.

Yes, it is another reason NOT to use a SELECT *.
That said, do you notice the automatic column aliasing ?
SQL*Plus has truncated some of the column headers, and the actual column names (and datatypes) are :

QCSJ_C000000000400000           NUMBER
QCSJ_C000000000400002           VARCHAR2(10)
QCSJ_C000000000400001           NUMBER
QCSJ_C000000000400003           VARCHAR2(10)

It only happens with the ANSI join, with the old-style join throwing an error.

select * from (select * from t2 , t1 where t1.id = t2.id)
       *
ERROR at line 1:
ORA-00918: column ambiguously defined