Проблема со ссылками на столбец во встроенном представлении Oracle

Question

У меня три стола в качестве мастера, ребенка и внука. Упрощенно что-то вроде этого:

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

Моя проблема заключалась в том, что я не указал, какая из меток time_stamp указана в предложении where. Удивительно, но я не получил никакой ошибки, но вместо этого база данных решила использовать столбец table1.time_stamp! Мой первый вопрос: есть ли причина, по которой я не получаю "ORA-00918: столбец двусмысленно определен"? Мне потребовалось некоторое время, чтобы найти проблему, но когда она была найдена, ее легко исправить, указав в представлении встроенного представления, какие столбцы меня интересуют в этом случае tbl3.time_stamp. При тестировании я обнаружил, что если я включу два из time_stamp в список столбцов, то я, как и ожидалось, получу ORA-00918.

Пожалуйста, помогите мне, я что-то здесь упускаю или есть какая-то проблема (ошибка или функция) со встроенным представлением в Oracle 11?

Answer 1

Интересно, что если мы попытаемся получить имена столбцов в запросе, используя процедуру 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.

Answer 2

Следует отметить, что вы выбрали * в своем запросе, и я предполагаю, что оракул автоматически называет столбцы time_stamp как time_stamp, time_stamp_1, time_stamp_2.

Теперь в предложении where у вас есть time_stamp=, и это работает, так как связывает time_stamp с table1.time_stamp.

если вы выполните sql ниже, вы сможете увидеть выше имена столбцов в выводе

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')