SQL-Ex blog

Когда 4.4/.44 не равно 10

Давайте начнем с такого примера:

SELECT 4.4/CAST(.44 AS VARCHAR(5));

Не обращая пока внимания на то, что делитель имеет тип VARCHAR, если мы посчитаем в уме или воспользуемся калькулятором, то увидим, что ответ должен быть равен 10:



Однако, если мы посмотрим на результат, который возвращает SQL Server, то увидим странное 11:



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

Старшинство типов данных

Если мы возьмем упрощенную версию этого примера, то увидим, что SQL Server фактически знает, как выполнять математическую операцию и вернет в ответе 10:

SELECT 4.4/.44;

Мы можем использовать функцию SQL_VARIANT_PROPERTY(), чтобы увидеть, какие типы данных предполагает использовать SQL Server в наших вычислениях:

SELECT 

    SQL_VARIANT_PROPERTY(4.4,'BaseType'),

    SQL_VARIANT_PROPERTY(.44,'BaseType'),

    SQL_VARIANT_PROPERTY(4.4/.44,'BaseType');

В этом случае, поскольку типы данных обоих операндов являются numeric, SQL Server не потребовалось напрягаться, делая какие-то радикальные преобразования, чтобы дать нам ожидаемый ответ. Если мы, напротив, дадим ему что-то несколько более сложное:

SELECT 4.4/CAST(.44 AS FLOAT);

Здесь мы также возвращаем результат 10, однако SQL Server должен сделать немного больше работы. Из предыдущего примера нам известно, что 4.4 имеет тип Numeric, а в этом примере мы явно преобразуем 0.44 к типу float. SQL Server не может выполнить операцию деления для операндов двух различных типов, поэтому он обращается к таблице старшинства типов данных, чтобы решить, какое из значений преобразовать к совпадающему типу данных.

В упомянутой (ссылка) выше таблице, тип данных float находится выше в списке, чем тип данных numeric (синоним: decimal). Это заставляет SQL Server преобразовать numeric 4.4 к float, прежде чем выполнить деление.

Пока SQL Server выполняет под капотом лишнюю работу, которую мы явно не запрашивали, это не может нас сильно беспокоить, поскольку мы все же получаем "верный" результат.

"Неправильные" преобразования

Давайте взглянем на несколько более неожиданное:

SELECT CAST(4.4 AS NUMERIC)/CAST(.44 AS FLOAT);

Вы могли бы подумать, что этот запрос также должен вернуть 10, как в предыдущем примере, однако в действительности он возвращает 9.090909:



Хотя мы тут по-прежнему являемся свидетелями неявного преобразования (numeric преобразуется к float, чтобы позволить SQL server выполнить деление), мы также сталкиваемся со случаем принимаемых по умолчанию точности и масштаба типа данных. Если мы снова воспользуемся функцией SQL_VARIANT_PROPERTY(), но выведем не только базовый тип, а также точность и масштаб, то увидим, что когда мы позволяем SQL Server "угадать" масштаб, он корректно выбирает одну позицию после десятичной точки. Тогда как мы используем значение по умолчанию масштаба, связанного с типом numeric, мы получаем 0:

SELECT 

    SQL_VARIANT_PROPERTY(4.4,'BaseType'),

    SQL_VARIANT_PROPERTY(4.4,'Precision'),

    SQL_VARIANT_PROPERTY(4.4,'Scale'); 

SELECT 

    SQL_VARIANT_PROPERTY(CAST(4.4 AS NUMERIC),'BaseType'),

    SQL_VARIANT_PROPERTY(CAST(4.4 AS NUMERIC),'Precision'),

    SQL_VARIANT_PROPERTY(CAST(4.4 AS NUMERIC),'Scale');

Это на деле означает то, что дробная часть числа 4.4 отбрасывается, что приводит к выражению 4 / .44 = 9,09090909090909

Соберем все вместе

Итак, возвращаемся к нашему исходному примеру, который возвращает 11:

SELECT 4.4/CAST(.44 AS VARCHAR(5));

Что именно здесь происходит. Ну, для начала старшинство типов данных заставляет SQL Server преобразовать VARCHAR .44 к numeric. Но numeric с какой точностью и масштабом?

SELECT 

    SQL_VARIANT_PROPERTY(4.4,'BaseType'),

    SQL_VARIANT_PROPERTY(4.4,'Precision'),

    SQL_VARIANT_PROPERTY(4.4,'Scale');

Так как SQL Server определяет, что наш числитель имеет тип NUMERIC(2,1), он преобразует и знаменатель к NUMERIC(2,1). Это означает вместо деления на .44 деление на .4, что приводит к результату 11:

-- Явная версия тех же самых вычислений

SELECT CAST(4.4 AS NUMERIC(2,1))/CAST(.44 AS NUMERIC(2,1));

SQL Server не ошибся

Хотя легче всего обвинить SQL Server в том, что он не знает как выполнять простые математические операции, это мы, разработчики, виноваты. SQL Server это часть программного обеспечения, следующего правилам, когда ситуация, в которую мы его ставим, является неопределенной.

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

В действительности лучшим решением будет изначально точно определять типы данных и всегда использовать явные преобразования типов данных, когда результат вычислений важен.