在Oracle内联视图中引用列的问题(Issue with referencing column in Oracle inline view)

我有三张桌子作为主人，孩子和孙子。简化这样的事情：

CREATE TABLE TABLE1 ( ID NUMBER(10) NOT NULL, TIME_STAMP DATE NOT NULL, COL1 VARCHAR2(64 CHAR) NOT NULL ) CREATE TABLE TABLE2 ( ID NUMBER(10) NOT NULL, TIME_STAMP DATE NOT NULL, TABLE1_ID NUMBER(10) NOT NULL, COL2 VARCHAR2(64 CHAR) NOT NULL, ) ALTER TABLE TABLE2 ADD ( CONSTRAINT TABLE2_FK FOREIGN KEY (TABLE1_ID) REFERENCES TABLE1 (ID)) / CREATE TABLE TABLE3 ( ID NUMBER(10) NOT NULL, TIME_STAMP DATE NOT NULL, TABLE2_ID NUMBER(10) NOT NULL, COL3 VARCHAR2(255 CHAR) NOT NULL ) ALTER TABLE TABLE3 ADD ( CONSTRAINT TABLE3_FK FOREIGN KEY (TABLE2_ID) REFERENCES TABLE2 (ID)) /

在一个更大的查询中，我将这三个表连接成一个内联视图，如下所示：

SELECT * FROM ( SELECT * FROM table3 tbl3 JOIN table2 tbl2 ON tbl3.table2_id = tbl2.id JOIN table1 tbl1 ON tbl2.table1_id = tbl1.id WHERE tbl2.col2 = 'SOME_CODE' AND tbl1.col1 = 'SOME_CODE' AND tbl3.time_stamp > TO_DATE('20130901','YYYYMMDD') ) WHERE time_stamp < :query_date

我的问题是我没有在where子句中指定time_stamp中的哪个引用。令人惊讶的是，我没有得到任何错误，而是数据库决定使用列table1.time_stamp！我的第一个问题是，为什么我没有得到'ORA-00918：列模糊定义'？花了我一些时间来找到问题，但是当找到位置然后通过在内联视图的选择中指定我在这种情况下感兴趣的列tbl3.time_stamp很容易纠正。在进行测试时，我确实知道如果我在列列表中包含两个time_stamp，那么我将按预期获得ORA-00918。

请帮帮我，我在这里遗漏了什么或者是否存在Oracle 11中内联视图的问题（错误或功能）？

I have three tables as a master, child and grandchild. Simplified something like this:

CREATE TABLE TABLE1 ( ID NUMBER(10) NOT NULL, TIME_STAMP DATE NOT NULL, COL1 VARCHAR2(64 CHAR) NOT NULL ) CREATE TABLE TABLE2 ( ID NUMBER(10) NOT NULL, TIME_STAMP DATE NOT NULL, TABLE1_ID NUMBER(10) NOT NULL, COL2 VARCHAR2(64 CHAR) NOT NULL, ) ALTER TABLE TABLE2 ADD ( CONSTRAINT TABLE2_FK FOREIGN KEY (TABLE1_ID) REFERENCES TABLE1 (ID)) / CREATE TABLE TABLE3 ( ID NUMBER(10) NOT NULL, TIME_STAMP DATE NOT NULL, TABLE2_ID NUMBER(10) NOT NULL, COL3 VARCHAR2(255 CHAR) NOT NULL ) ALTER TABLE TABLE3 ADD ( CONSTRAINT TABLE3_FK FOREIGN KEY (TABLE2_ID) REFERENCES TABLE2 (ID)) /

In a larger query I have these three tables join in an inline view like this:

SELECT * FROM ( SELECT * FROM table3 tbl3 JOIN table2 tbl2 ON tbl3.table2_id = tbl2.id JOIN table1 tbl1 ON tbl2.table1_id = tbl1.id WHERE tbl2.col2 = 'SOME_CODE' AND tbl1.col1 = 'SOME_CODE' AND tbl3.time_stamp > TO_DATE('20130901','YYYYMMDD') ) WHERE time_stamp < :query_date

My problem was that I did not specify which of the time_stamp the reference in the where clause. Surprisingly enough I didn’t get any error but instead the database decided to use column table1.time_stamp! My first question is there any reason why I don’t get ‘ORA-00918: column ambiguously defined’? Took me some time to found the problem but when located then easy corrected by specifying in the select of the inline view what columns I’m interested in in this case tbl3.time_stamp. When testing I did fount that if I included two of the time_stamp in the columns list then I as expected will get the ORA-00918.

Please help me out, am I missing something here or is there some issue (bug or feature) with the inline view in Oracle 11?

最满意答案

有趣的是，如果我们尝试使用dbms_sql.describe过程在查询中获取列名，我们可以看到三个相似的名称“time_stamp”和“id”：

SQL> declare 2 c int; 3 clm dbms_sql.DESC_TAB; 4 c_n int; 5 begin 6 c := dbms_sql.open_cursor; 7 dbms_sql.parse(c => c 8 , statement => 'SELECT * FROM ( SELECT * '|| 9 'FROM table3 tbl3 JOIN table2 tbl2 ON tbl3.table2_id = tbl2.id '|| 10 'JOIN table1 tbl1 ON tbl2.table1_id = tbl1.id WHERE tbl2.col2 = ''SOME_CODE'''|| 11 ' AND tbl1.col1 = ''SOME_CODE''' || 12 ' AND tbl3.time_stamp > TO_DATE(''20130901'',''YYYYMMDD'')) where time_stamp < sysdate' 13 , language_flag => dbms_sql.native); 14 dbms_sql.describe_columns(c => c 15 , col_cnt => c_n 16 , desc_t => clm 17 ); 18 for i in 1..c_n loop 19 dbms_output.put_line(clm(i).col_name); 20 end loop; 21 dbms_sql.close_cursor(c); 22 end; 23 / ID TIME_STAMP TABLE2_ID COL3 ID TIME_STAMP TABLE1_ID COL2 ID TIME_STAMP

但是如果你来自JOIN语法加入WHERE子句，你可以得到ORA-00918：

SQL> SELECT * FROM ( 2 SELECT * 3 FROM table3 tbl3 4 , table2 tbl2 5 , table1 tbl1 6 WHERE tbl2.col2 = 'SOME_CODE' 7 AND tbl1.col1 = 'SOME_CODE' 8 AND tbl3.time_stamp > TO_DATE('20130901','YYYYMMDD') 9 AND tbl3.table2_id = tbl2.id 10 AND tbl2.table1_id = tbl1.id 11 ) 12 where time_stamp < sysdate 13 / where time_stamp < sysdate * error in line 12: ORA-00918: column ambiguously defined

所以它似乎只是来自JOIN语法的众多错误之一。

Interesting what if we try to get column names in the query using dbms_sql.describe procedure we can see three similar name "time_stamp" and "id":

SQL> declare 2 c int; 3 clm dbms_sql.DESC_TAB; 4 c_n int; 5 begin 6 c := dbms_sql.open_cursor; 7 dbms_sql.parse(c => c 8 , statement => 'SELECT * FROM ( SELECT * '|| 9 'FROM table3 tbl3 JOIN table2 tbl2 ON tbl3.table2_id = tbl2.id '|| 10 'JOIN table1 tbl1 ON tbl2.table1_id = tbl1.id WHERE tbl2.col2 = ''SOME_CODE'''|| 11 ' AND tbl1.col1 = ''SOME_CODE''' || 12 ' AND tbl3.time_stamp > TO_DATE(''20130901'',''YYYYMMDD'')) where time_stamp < sysdate' 13 , language_flag => dbms_sql.native); 14 dbms_sql.describe_columns(c => c 15 , col_cnt => c_n 16 , desc_t => clm 17 ); 18 for i in 1..c_n loop 19 dbms_output.put_line(clm(i).col_name); 20 end loop; 21 dbms_sql.close_cursor(c); 22 end; 23 / ID TIME_STAMP TABLE2_ID COL3 ID TIME_STAMP TABLE1_ID COL2 ID TIME_STAMP

But if you come from JOIN syntax to joins in WHERE clause you can get ORA-00918:

SQL> SELECT * FROM ( 2 SELECT * 3 FROM table3 tbl3 4 , table2 tbl2 5 , table1 tbl1 6 WHERE tbl2.col2 = 'SOME_CODE' 7 AND tbl1.col1 = 'SOME_CODE' 8 AND tbl3.time_stamp > TO_DATE('20130901','YYYYMMDD') 9 AND tbl3.table2_id = tbl2.id 10 AND tbl2.table1_id = tbl1.id 11 ) 12 where time_stamp < sysdate 13 / where time_stamp < sysdate * error in line 12: ORA-00918: column ambiguously defined

So seems it's just one of the many bugs coming from JOIN syntax.

更多推荐