Блог

Главная - Вопросы по программированию - СЛУЧАЙ, КОГДА разрывается с условием, которого никогда не бывает

СЛУЧАЙ, КОГДА разрывается с условием, которого никогда не бывает

#sql #sql-server #tsql

Вопрос:

У меня есть сценарий, который недавно сломался у меня.

В нем у меня есть оператор CASE, который обрабатывает столбец кода. Когда идентификатором продукта является «ABC», третий символ-это число. В других случаях это не так.

 SELECT
    CASE
        WHEN risk.PRODUCTIDENTIFIER = 'ABC' AND DATEADD(y, CAST(SUBSTRING(risk.BenefitLabel, 3, 1) AS tinyint), risk.BenefitStartDate) >= DATEADD(m, 1, CAST(CAST('202109'/100 AS Char(4))   '-'   SUBSTRING('202109', 5, 6)   '-01' AS DATE)) THEN 1
        WHEN risk.ConvertedPolicyNo IS NOT NULL
                AND DATEADD(y, 1, conversions.BenefitStartDate) >= DATEADD(m, 1, CAST(CAST('202109'/100 AS Char(4))   '-'   SUBSTRING('202109', 5, 6)   '-01' AS DATE)) THEN 1 
        END AS ADB_FLAG
FROM 
    risk
    LEFT JOIN conversions 
        ON risk.ConvertedPolicyNo = conversions.PolicyNumber 
        AND risk.ConvertedPolicyInsuredNo = conversions.PolicyInsuredNo
WHERE 
    risk.Productidentifier NOT IN ('ABC', 'XYZ')

Странно то, что вышеприведенный запрос завершается с ошибкой, выдающей следующее сообщение об ошибке:

Не удалось выполнить преобразование при преобразовании значения varchar ‘F’ в тип данных tinyint.

Но если я удалю любое условие из инструкции CASE, оно будет работать просто отлично. Это особенно странно для меня, так как первое условие невозможно из-за ограничений в предложении «ГДЕ».

Насколько я понимаю, SQL должен оценивать предложение WHERE перед предложением SELECT. Я что-то неправильно понял?

Комментарии:

1. К вашему сведению case , это выражение , а не утверждение .

2. Просто, у вас есть строка в вашей таблице, где SUBSTRING(risk.BenefitLabel, 3, 1) содержится буква «F».

3. Строки, по которым SQL Server решает, что ему необходимо оценить выражение case, являются чем — то неизвестным нам, простым смертным. Итак, во-первых, предложение where не защищает вас от попыток оценить строку, которая даже не будет возвращена. Во-вторых, изменение любого аспекта запроса может изменить строки, которые он оценивает.

4. Зависит от данных, которые вы храните, но таким же простым решением может быть добавление AND ISNUMERIC(SUBSTRING(risk.BenefitLabel, 3, 1)) = 1 в эту ветвь вашего дела. Имейте в виду, что ISNUMERIC это не идеально, но часто бывает достаточно хорошо.

5. @DanielV — еще один возможный обходной путь: Заменить: ПРИВЕДЕНИЕ(ПОДСТРОКА(риск. Метка выгоды, 3, 1) КАК tinyint) с: ОБЪЕДИНЯЕТСЯ(TRY_CONVERT(int, ПОДСТРОКА(риск. Маркировка преимуществ, 3, 1)), 0)

Ответ №1:

SQL Server может свободно оценивать предикаты в предложении WHERE намного позже, чем ваши прогнозы в SELECT. Если вам абсолютно необходимо отфильтровать данные, чтобы удалить ошибочные строки, вы можете использовать такой хак, как CTE с предложением ORDER BY и TOP N. Просто сделайте ВЕРХНЮЮ N достаточно большой, чтобы убедиться, что вы получите все свои строки. Когда я прибегаю к этому (обычно для случаев использования JSON), я обычно использую максимальное значение 32-разрядного целого числа со знаком или ТОП 2147483647 в сочетании с произвольным предложением ORDER BY.

Альтернативой является размещение соответствующих строк во временную таблицу или переменную таблицы, а затем запрос из нее.

Невозможно заставить стандартную инструкцию SQL вычислять предикат заранее перед выражением CASE.

Метки: , ,