Округление. Внезапно. / Microsoft SQL Server / Sql.ru

Добро пожаловать в форум, 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;

s1	a1	a2
2.22	0.005	0.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;

s1	a1	a2
2.22	0.005	0.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;

s1	a1	a2
2.22	0.005	0.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;

s1	a1	a2
2.22000002861023	0.005	0.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;

s1	a1	a2
2.22	0.005	0.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;

Даёт правильный результат:

s1	a1	a2
2.22	0.005	0.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

Диклевич Александр

Первый раз я заметил это в новогоднюю ночь, когда запрос отлаживал. Подумал, что показалось, и чай слишком крепкий. Ну не может быть такого. Вот и решил с коллегами посоветоваться. Если масштаб непредсказуемо и внезапно уменьшается, то это бага. С другой стороны, на 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). То есть, округления тут в принципе получится не может.

А что говорит на этот счёт документация? Кто заставлял сервер делать у суммы масштаб 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

Это вы видите, что вы суммируете два раза 1.11.
А оптимизатор не может заранее знать, какое именно число вернет агрегатная функция.
И согласно вашей же ссылке вполне оправданно считает, что "Тип функции SUM (Transact-SQL) должен быть DECIMAL(38, 2).", в котором вполне может оказать и 1E35

6 янв 15, 16:34 [17089669] Ответить | Цитировать Сообщить модератору

Все форумы / Microsoft SQL Server

Ссылка на сообщение

Ссылка (включая название темы)

Ссылка (URL)