Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Округление. Внезапно.  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5489
Сижу на работе трезвый:

WITH t1(p) AS
(
	SELECT 1.11
	UNION ALL
	SELECT 1.11
)
SELECT
	SUM(t1.p) s1,
	2.225 - 2.22 a1,
	2.225 - SUM(t1.p) a2
FROM t1;


s1a1a2
2.220.0050.01


Как так получилось?
5 янв 15, 16:01    [17086798]     Ответить | Цитировать Сообщить модератору
 Re: Округление. Внезапно.  [new]
Glory
Member

Откуда:
Сообщений: 104751
Dmitry V. Liseev
Как так получилось?

Обычная ошибка того, кто считает, что по его правилам сервер должен определять размер и точность констант
WITH t1(p) AS
(
	SELECT 1.110
	UNION ALL
	SELECT 1.110
	)
SELECT
	SUM(t1.p) s1,
	2.225 - 2.22 a1,
	2.225 - SUM(t1.p) a2
FROM t1;
5 янв 15, 16:14    [17086834]     Ответить | Цитировать Сообщить модератору
 Re: Округление. Внезапно.  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4422
Дело в неявном приведении типов
Вот пример
WITH t1(p) AS
(
	SELECT cast(1.11 as money)
	UNION ALL
	SELECT cast(1.11 as money)
)
SELECT
	SUM(t1.p) s1,
	2.225 - 2.22 a1,
	2.225 - SUM(t1.p) a2
	
FROM t1;
5 янв 15, 16:28    [17086858]     Ответить | Цитировать Сообщить модератору
 Re: Округление. Внезапно.  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
WITH t1(p) AS
(
	SELECT CAST(1.11 AS decimal(4,2))
	UNION ALL
	SELECT 1.11
)
SELECT
	SUM(t1.p) s1,
	2.225 - 2.22 a1,
	2.225 - SUM(t1.p) a2
FROM t1;


s1a1a2
2.220.0050.01


WITH t1(p) AS
(
	SELECT CAST(1.11 AS decimal(4,3))
	UNION ALL
	SELECT 1.11
)
SELECT
	SUM(t1.p) s1,
	2.225 - 2.22 a1,
	2.225 - SUM(t1.p) a2
FROM t1;


s1a1a2
2.220.0050.005


WITH t1(p) AS
(
	SELECT CAST(1.11 AS real)
	UNION ALL
	SELECT 1.11
)
SELECT
	SUM(t1.p) s1,
	2.225 - 2.22 a1,
	2.225 - SUM(t1.p) a2
FROM t1;


s1a1a2
2.220000028610230.0050.0049999713897706


WITH t1(p) AS
(
	SELECT CAST(1.11 AS float)
	UNION ALL
	SELECT 1.11
)
SELECT
	SUM(t1.p) s1,
	2.225 - 2.22 a1,
	2.225 - SUM(t1.p) a2
FROM t1;


s1a1a2
2.220.0050.00499999999999989
5 янв 15, 16:39    [17086894]     Ответить | Цитировать Сообщить модератору
 Re: Округление. Внезапно.  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5489
Glory
Dmitry V. Liseev
Как так получилось?

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

Нет, сервер должен определять по документации.

Константа 1.11 имеет тип DECIMAL(3, 2), а 2.225 имеет тип DECIMAL(4, 3), ибо decimal и numeric (Transact-SQL) гласит:
В инструкциях Transact-SQL константа с десятичным разделителем автоматически преобразуется в значение типа данных numeric с минимально необходимой точностью и масштабом. Например, константа 12,345 преобразуется в значение numeric с точностью 5 и масштабом 3


Тип функции SUM (Transact-SQL) должен быть DECIMAL(38, 2).

А после вычитания, как утверждает Точность, масштаб и длина (Transact-SQL), масштаб должен получится max(s1, s2).

То есть, округления тут в принципе получится не может.

Более того, явный каст суммы:
WITH t1(p) AS
(
	SELECT 1.11
	UNION ALL
	SELECT 1.11
	)
SELECT
	SUM(t1.p) s1,
	2.225 - 2.22 a1,
	2.225 - CAST(SUM(t1.p) AS DECIMAL(18, 2)) a2
FROM t1;


Даёт правильный результат:
s1a1a2
2.220.0050.005
5 янв 15, 18:02    [17087084]     Ответить | Цитировать Сообщить модератору
 Re: Округление. Внезапно.  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5489
SQL2008
Дело в неявном приведении типов
Вот пример
И что показывает пример? Как сервер умудрился привести типы, чтобы произошло округление?
5 янв 15, 18:06    [17087100]     Ответить | Цитировать Сообщить модератору
 Re: Округление. Внезапно.  [new]
Диклевич Александр
Member

Откуда:
Сообщений: 611
Dmitry V. Liseev,

Хм, а вот так тоже странно
WITH t1(p) AS
(
	SELECT 1.11
	UNION ALL
	SELECT 1.11
)
SELECT
	SUM(t1.p) s1,
	2.225 - CAST(2.22 AS [numeric](38, 2)) a1,
	2.225 - SUM(t1.p) a2
FROM t1;


s1 a1 a2
2.22 0.01 0.01


Похоже что-то не то если максимальная точность одного из слагаемых 38.
Вот

declare @SQL [nvarchar](MAX) = 'WITH t1(p) AS
(
	SELECT 1.11
	UNION ALL
	SELECT 1.11
)
SELECT
	SUM(t1.p) s1,
	2.225 - 2.22 a1,
	2.225 - SUM(t1.p) a2
FROM t1;'

SELECT name, system_type_name
    FROM sys.dm_exec_describe_first_result_set(@SQL, NULL, 0)
    ORDER BY column_ordinal;


name system_type_name
s1 numeric(38,2)
a1 numeric(5,3)
a2 numeric(38,2)


В MSDN говорится что-то на сей счет "Точность и масштаб результата имеют абсолютный максимум, равный 38. Если значение точности превышает 38, то соответствующий масштаб уменьшается, чтобы по возможности предотвратить усечение интегральной части результата."
Может поэтому?

Хотя тоже интересно.
5 янв 15, 18:40    [17087174]     Ответить | Цитировать Сообщить модератору
 Re: Округление. Внезапно.  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5489
Диклевич Александр
В MSDN говорится что-то на сей счет "Точность и масштаб результата имеют абсолютный максимум, равный 38. Если значение точности превышает 38, то соответствующий масштаб уменьшается, чтобы по возможности предотвратить усечение интегральной части результата."
Может поэтому?

Хотя тоже интересно.
Первый раз я заметил это в новогоднюю ночь, когда запрос отлаживал. Подумал, что показалось, и чай слишком крепкий. Ну не может быть такого. Вот и решил с коллегами посоветоваться. Если масштаб непредсказуемо и внезапно уменьшается, то это бага. С другой стороны, на MS SQL крутятся кучи финансовых приложений, чувствительных к подобным округлениям. Такие баги давно бы всплыли.
5 янв 15, 22:22    [17087742]     Ответить | Цитировать Сообщить модератору
 Re: Округление. Внезапно.  [new]
Glory
Member

Откуда:
Сообщений: 104751
Dmitry V. Liseev
Тип функции SUM (Transact-SQL) должен быть DECIMAL(38, 2).

А после вычитания, как утверждает Точность, масштаб и длина (Transact-SQL), масштаб должен получится max(s1, s2).

То есть, округления тут в принципе получится не может.

Конечно не может
select cast(2.225 as numeric(5,3)) - cast(2.22 as numeric(38,2))
e1 - e2
max(s1, s2) + max(p1-s1, p2-s2) + 1 = max(3,2)+max(5-3,38-2)+1 = 2+36+1 = 39
max(s1, s2) = 3
Т.к numeric(39,3) не существует, то что по вашему будет делать сервер ?
6 янв 15, 09:30    [17088502]     Ответить | Цитировать Сообщить модератору
 Re: Округление. Внезапно.  [new]
Диклевич Александр
Member

Откуда:
Сообщений: 611
Glory,

Так как "...значение точности превышает 38, то соответствующий масштаб уменьшается, чтобы по возможности предотвратить усечение интегральной части результата..." и получаем (38, 2).
Так получается?
6 янв 15, 11:51    [17088744]     Ответить | Цитировать Сообщить модератору
 Re: Округление. Внезапно.  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5489
Glory
Dmitry V. Liseev
Тип функции SUM (Transact-SQL) должен быть DECIMAL(38, 2).

А после вычитания, как утверждает Точность, масштаб и длина (Transact-SQL), масштаб должен получится max(s1, s2).

То есть, округления тут в принципе получится не может.

Конечно не может
select cast(2.225 as numeric(5,3)) - cast(2.22 as numeric(38,2))
e1 - e2
max(s1, s2) + max(p1-s1, p2-s2) + 1 = max(3,2)+max(5-3,38-2)+1 = 2+36+1 = 39
max(s1, s2) = 3
Т.к numeric(39,3) не существует, то что по вашему будет делать сервер ?
А что говорит на этот счёт документация? Кто заставлял сервер делать у суммы масштаб 38? Выходит, любые операции с суммой теперь будут приводить к потере точности? Какие ещё операции приводят к непредсказуемому падению точности и округлению?
6 янв 15, 14:39    [17089248]     Ответить | Цитировать Сообщить модератору
 Re: Округление. Внезапно.  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5489
Диклевич Александр
Glory,

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


А почему не (38,3)? Меня бы даже (5,3) в данной задаче устроило.
6 янв 15, 14:42    [17089261]     Ответить | Цитировать Сообщить модератору
 Re: Округление. Внезапно.  [new]
Glory
Member

Откуда:
Сообщений: 104751
Dmitry V. Liseev
А что говорит на этот счёт документация? Кто заставлял сервер делать у суммы масштаб 38? Выходит, любые операции с суммой теперь будут приводить к потере точности? Какие ещё операции приводят к непредсказуемому падению точности и округлению?

Это вы видите, что вы суммируете два раза 1.11.
А оптимизатор не может заранее знать, какое именно число вернет агрегатная функция.
И согласно вашей же ссылке вполне оправданно считает, что "Тип функции SUM (Transact-SQL) должен быть DECIMAL(38, 2).", в котором вполне может оказать и 1E35
6 янв 15, 16:34    [17089669]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить