Неоднозначность в левых соединениях (только оракул?)

Мой начальник обнаружил ошибку в созданном мной запросе, и я не понимаю причины этой ошибки, хотя результаты запроса доказывают его правоту. Вот запрос (упрощенная версия) до исправления:

select PTNO,PTNM,CATCD
from PARTS 
left join CATEGORIES on (CATEGORIES.CATCD=PARTS.CATCD);

а вот после исправления:

select PTNO,PTNM,PARTS.CATCD
from PARTS 
left join CATEGORIES on (CATEGORIES.CATCD=PARTS.CATCD);

Ошибка заключалась в том, что для столбца CATCD отображались нулевые значения, т. е. результаты запроса включали результаты из таблицы CATEGORIES вместо PARTS. Вот что я не понимаю: если в исходном запросе была неоднозначность, почему Oracle не выдал ошибку? Насколько я понял, в случае левых объединений "основная" таблица в запросе (PARTS) имеет приоритет по неоднозначности. Я ошибаюсь или просто не правильно думаю об этой проблеме?

Обновлять:

Вот пересмотренный пример, в котором ошибка двусмысленности не выдается:

CREATE TABLE PARTS (PTNO NUMBER, CATCD NUMBER, SECCD NUMBER);

CREATE TABLE CATEGORIES(CATCD NUMBER);

CREATE TABLE SECTIONS(SECCD NUMBER, CATCD NUMBER);


select PTNO,CATCD 
from PARTS 
left join CATEGORIES on (CATEGORIES.CATCD=PARTS.CATCD) 
left join SECTIONS on (SECTIONS.SECCD=PARTS.SECCD) ;

Кто-нибудь знает?


sql oracle join
person Ovesh    schedule 12.09.2008    source источник

Ответы (12)


arrow_upward
1
arrow_downward

Боюсь, я не могу сказать вам, почему вы не получаете исключение, но я могу постулировать, почему он выбрал версию столбца CATEGORIES вместо версии PARTS.

Насколько я понял, в случае левых объединений "основная" таблица в запросе (PARTS) имеет приоритет по неоднозначности

Неясно, подразумеваете ли вы под «основной» просто левую таблицу в левом соединении или «управляющую» таблицу, как вы видите запрос концептуально... Но в любом случае то, что вы видите как «основную» таблицу в запрос, как вы написали, не обязательно будет «основной» таблицей при фактическом выполнении этого запроса.

Я предполагаю, что Oracle просто использует столбец из первой таблицы, с которой он сталкивается при выполнении запроса. А поскольку для большинства отдельных операций в SQL не требуется, чтобы одна таблица попадала перед другой, СУБД во время синтаксического анализа решит, какая из них наиболее эффективна для сканирования в первую очередь. Попробуйте получить план выполнения запроса. Я подозреваю, что это может показать, что сначала он попадает в КАТЕГОРИИ, а затем в ЧАСТИ.

person Chris Ammerman    schedule 12.09.2008

arrow_upward
6
arrow_downward

Вот запрос (упрощенная версия)

Я думаю, упростив запрос, вы устранили настоящую причину ошибки :-)

Какую версию оракула используете? Oracle 10g ( 10.2.0.1.0 ) дает:

create table parts (ptno number , ptnm number , catcd number);  
create table CATEGORIES (catcd number);

select PTNO,PTNM,CATCD from PARTS  
left join CATEGORIES on (CATEGORIES.CATCD=PARTS.CATCD);

Я получаю ORA-00918: столбец определен неоднозначно

person Pat    schedule 12.09.2008

arrow_upward
2
arrow_downward

Интересно в SQL-сервере, который выдает ошибку (как и должно быть)

select id
from sysobjects s
left join syscolumns c on s.id = c.id

Сервер: сообщение 209, уровень 16, состояние 1, строка 1. Неоднозначное имя столбца «id».

select id
from sysobjects 
left join syscolumns  on sysobjects.id = syscolumns.id

Сервер: сообщение 209, уровень 16, состояние 1, строка 1. Неоднозначное имя столбца «id».

person SQLMenace    schedule 12.09.2008

arrow_upward
2
arrow_downward

По моему опыту, если вы создадите такой запрос, результат данных будет тянуть CATCD с правой стороны соединения, а не с левой, когда есть такое перекрытие полей.

Таким образом, поскольку в этом соединении будут все записи из PARTS и только некоторые из CATEGORIES, вы будете иметь NULL в поле CATCD каждый раз, когда в правой части нет данных.

Явно определяя столбец как из PARTS (т.е. слева), вы получите ненулевое значение, предполагая, что поле содержит данные в PARTS.

Помните, что с LEFT JOIN вам гарантированы только данные в полях из левой таблицы, справа вполне могут быть пустые столбцы.

person Laith    schedule 12.09.2008
comment
Я понимаю ваш ответ. Но почему я не получил ошибку двусмысленности в первую очередь? - person Ovesh; 12.09.2008
comment
Я не уверен, почему. это была единственная странность в вашем вопросе. - person Laith; 19.09.2008

arrow_upward
2
arrow_downward

Это может быть ошибка в оптимизаторе Oracle. Я могу воспроизвести такое же поведение в запросе с 3 таблицами. Интуитивно кажется, что это должно привести к ошибке. Если я перепишу его одним из следующих способов, он выдаст ошибку:

(1) Использование внешнего соединения в старом стиле

select ptno, catcd
from parts, categories, sections
where categories.catcd (+) = parts.catcd
  and sections.seccd (+) = parts.seccd

(2) Явная изоляция двух соединений

select ptno, catcd
from (
  select ptno, seccd, catcd
  from parts
  left join categories on (categories.CATCD=parts.CATCD) 
)
left join sections on (sections.SECCD=parts.SECCD)

Я использовал DBMS_XPLAN, чтобы получить подробную информацию о выполнении запроса, который показал кое-что интересное. План в основном состоит в том, чтобы внешнее соединение PARTS и CATEGORIES, проецирование этого набора результатов, а затем внешнее соединение его с SECTIONS. Интересно то, что в проекции первого внешнего соединения включаются только PTNO и SECCD, а НЕ включаются CATCD ни из одной из первых двух таблиц. Поэтому конечным результатом будет получение CATCD из третьей таблицы.

Но я не знаю, причина это или следствие.

person Dave Costa    schedule 15.09.2008

arrow_upward
1
arrow_downward

Я использую Oracle 9.2.0.8.0. и это дает ошибку «ORA-00918: столбец определен неоднозначно».

person user2752    schedule 12.09.2008

arrow_upward
1
arrow_downward

Это известная ошибка в некоторых версиях Oracle при использовании соединений в стиле ANSI. Правильным поведением было бы получение ошибки ORA-00918.

В любом случае всегда лучше указывать имена ваших таблиц; таким образом, ваши запросы не прерываются, когда вы добавляете новый столбец с именем, которое также используется в другой таблице.

person Myto    schedule 18.09.2008

arrow_upward
0
arrow_downward

Как правило, рекомендуется в любом случае указывать конкретные и полные имена всех столбцов, поскольку это экономит оптимизатору немного работы. Конечно в SQL Server.

Из того, что я могу почерпнуть из документов Oracle, похоже, выберите имя столбца дважды в списке выбора или один раз в списке выбора, а затем еще раз в другом месте, например, в предложении order by.

Возможно, вы обнаружили «недокументированную функцию» :)

person hollystyles    schedule 12.09.2008

arrow_upward
0
arrow_downward

Как и HollyStyles, я не могу найти в документации Oracle ничего, что могло бы объяснить то, что вы видите.

PostgreSQL, DB2, MySQL и MSSQL отказываются выполнять первый запрос, поскольку он неоднозначен.

person Troels Arvin    schedule 12.09.2008

arrow_upward
0
arrow_downward

@Pat: я получаю ту же ошибку здесь для вашего запроса. Мой запрос немного сложнее, чем то, что я изначально опубликовал. Сейчас я работаю над воспроизводимым простым примером.

person Ovesh    schedule 12.09.2008

arrow_upward
0
arrow_downward

Более важный вопрос, который вы должны себе задать: почему у меня есть код категории в таблице деталей, которого нет в таблице категорий?

person Mike McAllister    schedule 17.09.2008

arrow_upward
0
arrow_downward

Это ошибка в Oracle 9i. Если вы соедините более двух таблиц, используя нотацию ANSI, он не обнаружит неоднозначности в именах столбцов и может вернуть неверный столбец, если псевдоним не используется.

Как уже было сказано, в 10g это исправлено, поэтому если псевдоним не используется, будет возвращена ошибка.

person steve godfrey    schedule 11.08.2011