Я нашел то, что, по моему мнению, является ошибкой в Oracle, но мне интересно, есть ли что-то задокументированное, что я пропустил.
скрипки: Oracle: http://sqlfiddle.com/#!4/43c19/2 SQL Server: http://sqlfiddle.com/#!3/ddc49/1 MySql: http://sqlfiddle.com/#!2/43c195/1
В основном у меня есть основная таблица, в которой я оставил присоединение к вторичной таблице. Затем я оставил соединение на виду. Если я укажу в соединении с представлением, что только хочу присоединиться, когда столбец во вторичной таблице не равен нулю, я получаю неожиданные результаты. Это лучше всего объяснить, показывая запрос:
SELECT
1,
MainTable.*
FROM
MainTable
LEFT JOIN SecondaryTable ON MainTable.KeyColumn = SecondaryTable.KeyColumn
LEFT JOIN ViewWithoutSecondary ON ((SecondaryTable.KeyColumn IS NOT NULL) AND SecondaryTable.KeyColumn = ViewWithoutSecondary.KeyColumn)
UNION ALL
SELECT
2,
MainTable.*
FROM
MainTable
LEFT JOIN SecondaryTable ON MainTable.KeyColumn = SecondaryTable.KeyColumn
LEFT JOIN ViewWithSecondary ON ((SecondaryTable.KeyColumn IS NOT NULL) AND SecondaryTable.KeyColumn = ViewWithSecondary.KeyColumn)
См. ниже сценарии создания, чтобы проверить его самостоятельно. В SQL Server и MySql я получаю те же результаты, однако Oracle отличается. В схеме есть три таблицы и два вида. Представления определяются следующим образом:
CREATE VIEW ViewWithoutSecondary
AS
SELECT
TertiaryTable.KeyColumn,
TertiaryValue + 1 ViewValue
FROM
TertiaryTable
CREATE VIEW ViewWithSecondary
AS
SELECT
SecondaryTable.KeyColumn,
TertiaryValue + 1 ViewValue
FROM
SecondaryTable
LEFT JOIN TertiaryTable ON SecondaryTable.KeyColumn = TertiaryTable.KeyColumn;
В Oracle я обнаружил, что если представление содержит ссылку на SecondaryTable, тогда я получаю только строки из MainTable, которые имеют совпадение в Secondary table. Мне кажется, что Oracle каким-то образом вставляет код представления, так что одна из строк опускается.
Я думаю, что если MainTable имеет три строки, то выполнение двух левых объединений должно всегда возвращать три строки по крайней мере плюс любые результаты из соединения. Однако в приведенном примере это не так.
Я знаю, что SecondaryTable.KeyValue IS NOT NULL избыточно, поскольку вторая половина предложения не будет истинна, если значение равно null, но я пытаюсь переработать запрос, чтобы помочь оптимизатору разработать лучший план.
Полное создание script для запуска примера:
CREATE TABLE MainTable
(
KeyColumn varchar(32),
ValueColumn varchar(32)
);
INSERT INTO MainTable VALUES ('123', 'abc');
INSERT INTO MainTable VALUES ('456', 'def');
INSERT INTO MainTable VALUES ('789', 'ghi');
CREATE TABLE SecondaryTable
(
KeyColumn varchar(32),
SecondaryValue integer
);
INSERT INTO SecondaryTable VALUES ('123', 1);
INSERT INTO SecondaryTable VALUES ('456', 2);
CREATE TABLE TertiaryTable
(
KeyColumn varchar(32),
TertiaryValue integer
);
INSERT INTO TertiaryTable VALUES ('123', 1);
CREATE VIEW ViewWithoutSecondary
AS
SELECT
TertiaryTable.KeyColumn,
TertiaryValue + 1 ViewValue
FROM
TertiaryTable;
CREATE VIEW ViewWithSecondary
AS
SELECT
SecondaryTable.KeyColumn,
TertiaryValue + 1 ViewValue
FROM
SecondaryTable
LEFT JOIN TertiaryTable ON SecondaryTable.KeyColumn = TertiaryTable.KeyColumn;
