Результаты непредсказуемого выбора SQL Server (ошибка dbms?)

Ниже приведен простой пример, который возвращает странные результаты, непредсказуемые, и мы не можем объяснить это в нашей команде. Делаем ли мы что-то неправильно или это ошибка SQL Server?

После некоторого исследования мы уменьшили область поиска до предложения union в подзапросе , который выбирает одну запись из таблицы «men»

Он работает так, как ожидалось, в SQL Server 2000 (возвращает 12 строк), но в 2008 и 2012 годах он возвращает только одну строку.

create table dual (dummy int)

insert into dual values (0)

create table men (
man_id int,
wife_id int )

-- there are 12 men, 6 married 
insert into men values (1, 1)
insert into men values (2, 2)
insert into men values (3, null)
insert into men values (4, null)
insert into men values (5, null)
insert into men values (6, 3)
insert into men values (7, 5)
insert into men values (8, 7)
insert into men values (9, null)
insert into men values (10, null)
insert into men values (11, null)
insert into men values (12, 9)

Это возвращает только одну строку: 1 1 2

select 
man_id,
wife_id,
(select count( * ) from 
    (select dummy from dual
     union select men.wife_id  ) family_members
) as family_size
from men
--where wife_id = 2 -- uncomment me and try again

Раскомментировать последнюю строку, и она дает: 2 2 2

Существует много нечетного поведения:

  • После серии капель, создает, усекает и вставляет в таблицу «men» иногда работает (возвращает 12 строк)
  • Когда вы меняете «union select men.wife_id» на «union all select men.wife_id» или «union select isnull (men.wife_id, null)» (!!!), он возвращает 12 строк (как и ожидалось).
  • Странное поведение, похоже, не связано с типом данных столбца «wife_id». Мы наблюдали его в системе разработки с гораздо большими наборами данных.
  • "где wife_id> 0" возвращает 6 строк
  • мы также наблюдаем странное поведение взглядов с такими утверждениями. SELECT * возвращает подмножество строк, SELECT TOP 1000 возвращает все
37 голосов | спросил Ryszard Bocian 20 PMpMon, 20 Apr 2015 14:59:35 +030059Monday 2015, 14:59:35

2 ответа


35
  

Мы делаем что-то неправильно или это ошибка SQL Server?

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

Ошибка требует трех ингредиентов:

  1. Вложенные циклы с внешней ссылкой (применяются)
  2. Внутренняя ложная индексная катушка, которая ищет внешнюю ссылку
  3. Оператор конкатенации внутренней стороны

Например, запрос в вопросе создает такой план, как:

Аннотированный план

Существует много способов удалить один из этих элементов, поэтому ошибка больше не воспроизводится.

Например, можно создать индексы или статистику, которые означают, что оптимизатор предпочитает не использовать Lazy Index Spool. Или можно использовать подсказки, чтобы заставить хэш или объединение объединения вместо использования Concatenation. Можно также переписать запрос, чтобы выразить ту же семантику, но в результате получается другая форма плана, в которой отсутствуют один или несколько требуемых элементов.

Подробнее

Lazy Index Spool лениво кэширует внутренние строковые результирующие строки, в рабочей таблице, индексированной значениями внешней ссылки (коррелированный параметр). Если Lazy Index Spool запрашивает внешнюю ссылку, которую он видел раньше, он извлекает строку кэшированного результата из своей рабочей таблицы («перемотка назад»). Если катушка задаются для внешнего опорного значения он не видел раньше, он запускает его поддерево с текущей внешней опорной величиной и кэширует результат (а «перепривязывает»). Предикат поиска в Lazy Index Spool указывает ключ (ы) для его рабочей таблицы.

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

CParamBounds: FNeedToReload

Когда существует вышеописанное поддерево, в частности, где используется Concatenation, что-то идет не так (возможно, проблема ByVal /ByRef /Copy) с привязками, так что CParamBounds:FNeedToReload всегда возвращает false, независимо от того, независимо от того, изменилась ли внешняя ссылка или нет.

Если существует одно и то же поддерево, но используется союз Merge Union или Hash Union, это существенное свойство задается правильно на каждой итерации, а Lazy Index Spool перематывает или переустанавливает каждый раз, когда это необходимо. Между прочим, Distinct Sort и Stream Aggregate безупречны. Мое подозрение заключается в том, что Merge and Hash Union делает копию предыдущего значения, тогда как Concatenation использует ссылку. К сожалению, невозможно проверить это без доступа к исходному коду SQL Server.

Конечным результатом является то, что Lazy Index Spool в форме проблемного плана всегда думает, что он уже видел текущую внешнюю ссылку, перематывает ее путем поиска в своей рабочей таблице, обычно ничего не находит, поэтому для этой внешней ссылки не возвращается строка. Выполняя выполнение в отладчике, катушка только когда-либо выполняет свой метод RewindHelper и никогда не выполняет его метод ReloadHelper (reload = rebind в этом контексте). Это видно в плане выполнения, так как у операторов под катушкой есть «Number of Executions = 1».

RewindHelper

Исключением, конечно, является первая внешняя ссылка, заданная Lazy Index Spool.Это всегда выполняет поддерево и кэширует строку результата в рабочей таблице. Все последующие итерации приводят к перемотке, которая будет производить только строку (одиночную кешированную строку), когда текущая итерация имеет одно и то же значение для внешней ссылки в первый раз.

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

Demo

Таблица и примеры данных:

CREATE TABLE #T1 
(
    pk integer IDENTITY NOT NULL,
    c1 integer NOT NULL,

    CONSTRAINT PK_T1
    PRIMARY KEY CLUSTERED (pk)
);
GO
INSERT #T1 (c1)
VALUES
    (1), (2), (3), (4), (5), (6),
    (1), (2), (3), (4), (5), (6),
    (1), (2), (3), (4), (5), (6);

Следующий (тривиальный) запрос производит правильный подсчет по два для каждой строки (всего 18) с помощью Объединения:

SELECT T1.c1, C.c1
FROM #T1 AS T1
CROSS APPLY 
(
    SELECT COUNT_BIG(*) AS c1
    FROM
    (
        SELECT T1.c1
        UNION
        SELECT NULL
    ) AS U
) AS C;

План союза слияния

Если теперь добавить подсказку запроса для принудительного конкатенации:

SELECT T1.c1, C.c1
FROM #T1 AS T1
CROSS APPLY 
(
    SELECT COUNT_BIG(*) AS c1
    FROM
    (
        SELECT T1.c1
        UNION
        SELECT NULL
    ) AS U
) AS C
OPTION (CONCAT UNION);

План выполнения имеет проблемную форму:

План конкатенации

И результат теперь неверен, всего три строки:

Результат трех строк

Хотя это поведение не гарантируется, первая строка из сканирования кластерного индекса имеет значение c1, равное 1. Есть два других строки с этим значением, поэтому в общей сложности производятся три строки.

Теперь обрезаем таблицу данных и загружаем ее с большим количеством дубликатов первой строки:

TRUNCATE TABLE #T1;

INSERT #T1 (c1)
VALUES
    (1), (2), (3), (4), (5), (6),
    (1), (2), (3), (4), (5), (6),
    (1), (1), (1), (1), (1), (1);

Теперь план Concatenation:

8-рядный план конкатенации

И, как указано, создаются 8 строк, все с c1 = 1, конечно:

8 результат строки

Я заметил, что вы открыли Подключить элемент для этой ошибки , но на самом деле это не является местом для сообщения о проблемах, которые оказывают производственное воздействие. Если это так, вы действительно должны обратиться в службу поддержки Microsoft.

ответил Paul White 21 AMpTue, 21 Apr 2015 06:13:02 +030013Tuesday 2015, 06:13:02
-3

Почему вы используете подзапрос без инструкции from? Я думаю, что это может привести к разнице в серверах 2005 и 2008 годов. Может быть, вы можете пойти с явным соединением?

select 
m1.man_id,
m1.wife_id,
(select count( * ) from 
    (select dummy from dual
     union
     select m2.wife_id
     from men m2
     where m2.man_id = m1.man_id) family_members
) as family_size
from men m1
ответил 20 PMpMon, 20 Apr 2015 16:37:48 +030037Monday 2015, 16:37:48

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132