T-SQL Почему деление на ноль не приводит к ошибке в предложении order by

Я нашел несколько утверждений в одной процедуре, которые я сначала счел неверными, но после их проверки я убедился, что они работают нормально. Чего я не могу понять, так это пути.

У меня есть простая таблица с записью столбцов id, pts и ptsOf:

DECLARE @DataSource TABLE
(
     RecordID TINYINT
    ,Pts   INT
    ,PtsOf INT
)

INSERT INTO @DataSource
VALUES (1,5,5)
      ,(1,7,8)
      ,(1,3,5)
      ,(2,5,0)

и мне нужно рассчитать общий балл для каждой записи, используя следующую формулу:

SUM(pts)/SUM(ptsOf) * 100

Итак, приведенный выше оператор сгенерирует следующую ошибку, потому что для последней записи у меня будет 5/0:

Сообщение 8134, уровень 16, состояние 1, строка 21 Разделить на ноль обнаружена ошибка.

Но оператор, который я нашел, проверяет деление на ноль только в предложении select и не делает этого в предложении order by:

 SELECT  RecordID
         ,CAST(CAST(SUM(Pts) AS decimal) / CASE SUM(PtsOf) WHEN 0 THEN NULL ELSE SUM(PtsOf) END * 100 AS decimal(18, 0))
 FROM @DataSource
 GROUP BY RecordID
 ORDER BY RecordID,  CAST(CAST(SUM(Pts) AS decimal) / SUM(PtsOf) * 100 AS decimal(18, 0)) ASC

Почему вычисление в разделе order by не приводит к ошибке?

Ниже приведен полный пример:

SET NOCOUNT ON
GO

    DECLARE @DataSource TABLE
    (
         RecordID TINYINT
        ,Pts   INT
        ,PtsOf INT
    )

    INSERT INTO @DataSource
    VALUES (1,5,5)
          ,(1,7,8)
          ,(1,3,5)
          ,(2,5,0)

     SELECT  RecordID
             ,CAST(CAST(SUM(Pts) AS decimal) / CASE SUM(PtsOf) WHEN 0 THEN NULL ELSE SUM(PtsOf) END * 100 AS decimal(18, 0))
     FROM @DataSource
     GROUP BY RecordID
     ORDER BY RecordID,  CAST(CAST(SUM(Pts) AS decimal) / SUM(PtsOf) * 100 AS decimal(18, 0)) ASC

SET NOCOUNT OFF
GO

sql-server-2012 sql-order-by divide-by-zero
person gotqn    schedule 29.03.2013    source источник
comment
Выражения в вычисляемых скалярах не обязательно вычисляются ровно один раз. Иногда они оцениваются несколько раз. В других случаях вообще нет, если результат на самом деле не нужен. см. статью, чтобы узнать больше об этом   -  person Martin Smith    schedule 29.03.2013
comment
@MartinSmith Большое спасибо за статью. Теперь я убежден, что скаляр вычисления в моем случае не выполняется, потому что добавление столбца pts/ptsOf после агрегатного выражения вызвало ошибку.   -  person gotqn    schedule 30.03.2013
comment
И если вы попробуете ORDER BY RecordID, (CRYPT_GEN_RANDOM(1) *0) + CAST(CAST(SUM(Pts) AS decimal) / SUM(PtsOf) * 100 AS decimal(18, 0)), вы увидите, что оценка больше не откладывается и возникает ошибка, как в примере 4 в этой статье.   -  person Martin Smith    schedule 30.03.2013

Ответы (1)


arrow_upward
3
arrow_downward

Я считаю, что второе предложение в ORDER BY просто игнорируется в данном конкретном случае. Ведь если сделать так:

DECLARE @t TABLE(i INT PRIMARY KEY, x UNIQUEIDENTIFIER);

INSERT @t VALUES(1,NEWID()),(2,NEWID()),(3,NEWID()),(4,NEWID());

SELECT i, x FROM @t ORDER BY i, x;

x не учитывается в ORDER BY, а почему должно быть? Первая сущность в предложении ORDER BY уже диктует порядок, и второе предложение не может его изменить. Поскольку вы группируете по RecordID, SQL Server достаточно умен, чтобы понять, что первый элемент в ORDER BY уникален, поэтому ему не нужно учитывать второй. Я не могу это доказать, и я могу заставить его потерпеть неудачу, когда второй элемент на самом деле намного понятнее для SQL Server, используя константу, например:

ORDER BY RecordID, CONVERT(1/0);

Но когда вывод столбца не так легко известен SQL Server, и он в любом случае не может сделать ничего полезного с выводом, он поступает правильно (ИМХО) и отбрасывает выражение, не оценивая его полностью и вызывая ошибку времени выполнения. Вы также можете вернуть ошибку, если вы сначала не заказываете столбец, который гарантированно будет уникальным:

ORDER BY CAST(CAST(SUM(Pts) AS decimal) / SUM(PtsOf) * 100 AS decimal(18, 0));
person Aaron Bertrand    schedule 29.03.2013
comment
Спасибо за ответ. Действительно, если вы добавляете столбец pts/ptsOf после функции агрегации, генерируется ошибка. Так вот, в моем примере просто игнорируется за ненадобностью. - person gotqn; 30.03.2013