Почему этот запрос возвращает только один результат?
Это странный тестовый пример из набора регрессионных тестов Postgres.
Он начинается с таблицы с именем tenk, в которой 10 000 строк. В таблице есть столбец с именем unique1, оба из которых содержат значения от 0 до 9999, причем каждая строка в таблице имеет уникальное значение для этого столбца для каждого столбца. (т. е. в одной строке есть 369 для unique1 и 6521 для unique2, и ни одна другая строка не имеет таких значений, хотя есть строка, в которой 369 для unique2, и строка, в которой 6521 для unique1.)
Тестовый запрос:
select
(select max((select i.unique2 from tenk1 i where i.unique1 = o.unique1)))
from tenk1 o;
Это возвращает одну строку со значением 9999. И это кажется странным. Вот как я анализирую запрос в своей голове:
Select [subquery] from tenk1 o
Subquery selects max(i.unique2) from tenki, where i.unique1 = o.unique1
This is basically a really weird way to write a self-join.
For each row in o, select the max i.unique2 value that corresponds to it.
There are 10,000 rows in o, each will have exactly 1 match in i.
Return 10,000 rows in the result set.
Где я ошибаюсь?
Обновлено: поведение становится еще более странным, если вы немного измените код.
SELECT кажется эквивалентом соединения между таблицами i и o, но если вы перепишете его как одну:
select
(select max((select i.unique2 from tenk1 i join tenk1 o on i.unique1 = o.unique1)))
выдает ошибку:
ОШИБКА: более одной строки, возвращенной подзапросом, используемым в качестве выражения.
Но если вместо этого вы удалите max из исходного запроса,
select
(select (select i.unique2 from tenk1 i where i.unique1 = o.unique1))
from tenk1 o;
как и ожидалось, вы получите набор результатов из 10 000 элементов. Похоже, у базы данных нет проблем с подзапросом, возвращающим здесь более одной строки! Удалите таблицу o из этого необъяснимо действительного запроса,
select
(select (select i.unique2 from tenk1 i))
и возвращается ошибка «более одной строки»!
Это официальное поведение PostgreSQL, которое набор тестов требует от базы данных. Правило, изложенное в сообщении об ошибке, гласит, что нельзя использовать подзапрос в качестве выражения, возвращающего более одной строки. Что такого в этом странном не совсем присоединении к таблице во внешней области, которое обходит это правило?
«Кажется, у базы данных нет проблем с подзапросом, возвращающим здесь более одной строки!» — было бы, но этот подзапрос всегда возвращает одну строку (…where i.unique1 = …). Однако он выполняется 10 000 раз, по одному разу для каждой строки в o.
@Bergi: Я слишком быстро удалил свой комментарий. То, что вы написали, в большинстве случаев не имеет смысла. Если SQL оценивается снаружи внутрь, как будет работать SELECT TOP 10 * FROM (SELECT Last, First, WeeklyPay FROM (SELECT Last, First, HourlyWage * HoursWorked AS WeeklyPay FROM PayRoll ORDER BY Last, First) AS Wages) As PayoutForWeek) As SalarySummary WHERE WeekPay > 400, поскольку очевидно, что самое внутреннее должно быть выполнено, прежде чем можно будет запустить следующее самое внутреннее?
Ответы 2
select (select max(i.unique2) from tenk1 i where i.unique1 = o.unique1)
from tenk1 o
сделал бы то, что вы сказали, но запрос этого не делает. Это делает внутренний
select i.unique2 from tenk1 i where i.unique1 = o.unique1
(что, как вы говорите, является странным и даже бессмысленным самосоединением, но это тестовый пример). В результате получается одна строка «внешнего» tenk1 на каждую строку. Затем он использует max, чтобы получить единственное наибольшее значение unique2, полученное в результате запроса (которое также является самым большим значением в таблице).
Кажется, это еще более странно. Смотрите мое редактирование. Это «странное, даже бессмысленное самообъединение», похоже, является крайним случаем, нарушающим ранее установленные правила. Есть идеи, что здесь происходит?
Это тест на регрессию.
Согласно aggregrate.sql :
-- Тестирование обработки подссылок в агрегатах внешнего уровня.
-- Согласно отчету об ошибке от Дэниела Грейса.*
Если отчет об ошибке агрегировать по подвыборкам не удается:
Следующий бессмысленный запрос приводит к сбою PostgreSQL с ошибкой: plan не должен ссылаться на переменную подплана. (Это было вырезано из «полезный» запрос, вызвавший ту же ошибку). Впервые столкнулся на 8.3.4, воспроизведено на 8.3.7
Патч — это Агрегатный патч:
Исправлена обработка подзапросов, появляющихся в аргументах внешнего уровня. агрегатная функция. По определению, такой суб-SELECT не может ссылаться ни на один переменные уровней запроса между собой и семантическим уровнем агрегата (в противном случае агрегат был бы присвоен этому более низкому уровню). Таким образом, правильная и наиболее эффективная реализация — рассматривать подзапрос как будучи подвыборкой этого внешнего уровня запроса, а не уровня агрегата синтаксически появляется. Невыполнение этого также чертовски сбивает с толку наше логика передачи параметров, как показано в отчете об ошибке Дэниела Грейса.
К счастью, мы уже копировали выражение Aggref целиком, вплоть до уровень внешнего запроса, поэтому все, что нужно, это задержать SS_process_sublinks обработка суб-SELECT до тех пор, пока управление не вернется на внешний уровень.
Это было нарушено с тех пор, как мы ввели обработку данных в соответствии со спецификациями. внешние агрегаты в 7.4; так что пропатчите все обратно.
С патчемагрегатов.sql (r1.13.4.1 -> r1.13.4.2):
+ -- Test handling of sublinks within outer-level aggregates.
+ -- Per bug report from Daniel Grace.
+ select
+ (select max((select i.unique2 from tenk1 i where i.unique1 = o.unique1)))
+ from tenk1 o;
+
Спасибо, понимание того, что происходит в исходном выпуске, действительно проясняет ситуацию.
@KenWhite В целом подзапросы работают не так — они выполняются для каждой строки внешнего запроса, а оценка происходит снаружи. Только когда Postgres видит, что подзапрос не связан с внешней строкой, он может оптимизировать его, выполнив только один раз (и «до» внешнего запроса).