Странная магия оптимизатора MSSQL

#sql #sql_server

Есть временная таблица следующего вида:




CREATE TABLE #1 (
    num1 FLOAT
    ,num2 FLOAT
    )
INSERT INTO #1
SELECT 0
    ,0
INSERT INTO #1
SELECT 0
    ,1
INSERT INTO #1
SELECT 1
    ,0
INSERT INTO #1
SELECT 1
    ,1


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

SELECT *
FROM #1 a
WHERE (
        a.num1 > 0
        AND a.num2 = 0
        )
    OR (
        a.num1 > 0
        AND a.num2 > 0
        AND a.num1 / a.num2 >= 2
        )
    OR (
        a.num1 >= 0
        AND a.num2 < 0
        )



  Divide by zero error encountered.


Покурил справку, там написано, что операция деления выполняется раньше всех логических
операций. Ок, теперь стало понятно, почему валится с ошибкой. 
Но потом я убираю знак "=" из последнего условия и все работает: 

SELECT *
FROM #1 a
WHERE (
        a.num1 > 0
        AND a.num2 = 0
        )
    OR (
        a.num1 > 0
        AND a.num2 > 0
        AND a.num1 / a.num2 >= 2
        )
    OR (
        a.num1 > 0
        AND a.num2 < 0
        )


Посмотрел план выполнения запроса:


Судя по плану выполнения запроса, должна была снова возникнуть ошибка деления на
ноль. Но этого не произошло. Проверял на Microsoft SQL Server-е версии 2005 и 2012.
Проблема такая везде присутствует. В Teradata первый запрос отрабатывает без проблем.

Мой вопрос не в том, как переписать запрос, чтобы все заработало, а ПОЧЕМУ оно так
работает. Почему, когда во втором запросе я убираю знак "=" запрос отрабатывает без
проблем?
    

Ответы

Ответ 1

По-видимому дело вот в чём.

Посмотрите план выполнения (XML). Когда вы убираете =, то предикат оптимизируется
в (отредактировано для краткости)


    
        
            
                num1 > 0
            
            
                
                    
                        num2 = 0
                    
                    
                        num1 / num2 >= 2 AND num2 > 0
                    
                    
                        num2 < 0
                    
                
            
        
    



т.е.

a.num1 > 0
AND
(
    a.num2 = 0
    OR
    a.num1 / a.num2 >= 2 AND a.num2 > 0
    OR
    a.num2 < 0
)


Строки данных (0, 0) и (0, 1) отсекаются по условию a.num1 > 0. Строки (1, 0) и (1,
1) ему удовлетворяет, но (1, 0) проходит по условию a.num2 = 0 (до деления не доходит).
Строка (1, 1) не проходит ни по одному условию OR.

Для сравнения, в неизменённом запросе предикат (также отредактировано для краткости),
взятый из оценочного плана запроса, выглядит так:


    
        
            
                num1 > 0 AND num2 = 0
            
            
                num1/num2 >= 2 AND num1 > 0 AND num2 > 0
            
            
                num1 >= 0 AND num2 < 0
            
        
    



Ответ 2

Из плана видно, что значения трактуются как вещественные. Вещественное деление на
ноль приводит к результату NaN (не число).

В то время как челочисленное деление на ноль приводит к ошибке Divide by zero.

Вероятно, сравнение > трактуется как неточное сравнение с нулём, отсюда вещественные
числа. А сравнение >= трактуется как точное сравнение, отсюда челые числа.