Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3 4   вперед  Ctrl      все
 вы думаете что 1+(-1) = 0? а вот и нет  [new]
bigker
Member

Откуда:
Сообщений: 2
не могу найти на форуме. но проблема старая и я был уверен что давно решенная
Oracle 11.2.0.3.0 64 bit

ситуация следующая. есть средних размеров таблица. начиная с определенного момента запрос
вида
select   t.num,  sum(t.num)over() nsum from mytable t where ....

стал выдавать следующие данные:

num nsum
1 -17,3563 -2,3E-37
2 17,3563 -2,3E-37
что в общем приводит к неприятным последствиям

с чем связан этот баг в целом понятно - особенности хранения вещественных чисел. вопрос в другом, как с этим бороться? получается что ни в одном запросе нельзя использовать суммирование без округления? результат не гарантирован. или архитектурно прописывать всегда точность хранения....
28 июн 17, 10:02    [20595743]     Ответить | Цитировать Сообщить модератору
 Re: вы думаете что 1+(-1) = 0? а вот и нет  [new]
Elic
Member

Откуда:
Сообщений: 29991
bigker
с чем связан этот баг в целом понятно
Заблуждаешься.
RTFM set numwidth, DUMP, TO_CHAR
28 июн 17, 10:09    [20595769]     Ответить | Цитировать Сообщить модератору
 Re: вы думаете что 1+(-1) = 0? а вот и нет  [new]
MaximaXXL
Member

Откуда: Киев
Сообщений: 652
bigker,

Странно, у меня вот так например:
with t as (select -17.3563 num from dual union all
select 17.3563 from dual )

select   t.num,  sum(t.num)over() nsum from t

NUM	     NSUM
-17.3563	0
17.3563  	0

2 rows selected.
28 июн 17, 10:46    [20595900]     Ответить | Цитировать Сообщить модератору
 Re: вы думаете что 1+(-1) = 0? а вот и нет  [new]
Elic
Member

Откуда:
Сообщений: 29991
MaximaXXL
Странно, у меня вот так например:
А надо так:
-17.3563-2.3e-37
28 июн 17, 10:55    [20595931]     Ответить | Цитировать Сообщить модератору
 Re: вы думаете что 1+(-1) = 0? а вот и нет  [new]
IgorSm
Member

Откуда:
Сообщений: 85
Как раз на тему "всегда ли определять Number?"
https://www.sql.ru/forum/1264000-1/vsegda-li-opredelyat-number
28 июн 17, 11:09    [20595984]     Ответить | Цитировать Сообщить модератору
 Re: вы думаете что 1+(-1) = 0? а вот и нет  [new]
bigker
Member

Откуда:
Сообщений: 2
Ну вобщем я понимаю так - то что я не вижу знака в 37 разряде после запятой - не значит что его там нет. и нужно было озаботиться округлением (trunc) самому. либо при записи данных, либо при суммировании. Все вокруг сплошной обман =)
28 июн 17, 11:49    [20596186]     Ответить | Цитировать Сообщить модератору
 Re: вы думаете что 1+(-1) = 0? а вот и нет  [new]
молчать и слушать, молчать и слу
Guest
bigker
Ну вобщем я понимаю так - то что я не вижу знака в 37 разряде после запятой - не значит что его там нет. и нужно было озаботиться округлением

Озаботиться нужно заданием необходимых масштаба и точности для поля с типом данных NUMBER, а не вбиванием костылей в виде округления и усечение имеющихся данных.
И, да (поскольку структура таблицы не показана) - избегать применения машинных числовых типов (binary_float & binary_double) если требуется фиксированная точность вычислений.
28 июн 17, 12:00    [20596235]     Ответить | Цитировать Сообщить модератору
 Re: вы думаете что 1+(-1) = 0? а вот и нет  [new]
Elic
Member

Откуда:
Сообщений: 29991
bigker
Ну вобщем я понимаю так - то что я не вижу знака в 37 разряде после запятой - не значит что его там нет.
Да.
bigker
округлением (trunc)
Ты уж определись.
28 июн 17, 12:01    [20596236]     Ответить | Цитировать Сообщить модератору
 Re: вы думаете что 1+(-1) = 0? а вот и нет  [new]
Elic
Member

Откуда:
Сообщений: 29991
молчать и слушать, молчать и слу
избегать применения машинных числовых типов (binary_float & binary_double)
Ни один из этих типов не может дать наблюдаемой здесь точности (погрешности)
28 июн 17, 12:03    [20596245]     Ответить | Цитировать Сообщить модератору
 Re: вы думаете что 1+(-1) = 0? а вот и нет  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18398
Elic
молчать и слушать, молчать и слу
избегать применения машинных числовых типов (binary_float & binary_double)
Ни один из этих типов не может дать наблюдаемой здесь точности (погрешности)

Даже binary_double легко дает гораздо большую погрешность:
SQL>select to_char((1+power(2,-54)) - cast(cast(1+power(2,-54) as binary_double) as number),'0.99999eeee') err
from dual;

ERR         
-------------
  5.55112E-17 
28 июн 17, 12:52    [20596397]     Ответить | Цитировать Сообщить модератору
 Re: вы думаете что 1+(-1) = 0? а вот и нет  [new]
-2-
Member

Откуда:
Сообщений: 15330
andrey_anonymous
гораздо большую погрешность
предлагаю задуматься над словом "наблюдаемой", сравнить порядок погрешности и исходной величины.
28 июн 17, 14:17    [20596769]     Ответить | Цитировать Сообщить модератору
 Re: вы думаете что 1+(-1) = 0? а вот и нет  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18398
-2-
andrey_anonymous
гораздо большую погрешность
предлагаю задуматься над словом "наблюдаемой", сравнить порядок погрешности и исходной величины.

Раскройте пожалуйста свою мысль.
28 июн 17, 14:21    [20596786]     Ответить | Цитировать Сообщить модератору
 Re: вы думаете что 1+(-1) = 0? а вот и нет  [new]
Elic
Member

Откуда:
Сообщений: 29991
andrey_anonymous
Раскройте пожалуйста свою мысль.
Сперва ты: ты мне возражал или же подтверждал мой тезис своим примером?
28 июн 17, 14:26    [20596805]     Ответить | Цитировать Сообщить модератору
 Re: вы думаете что 1+(-1) = 0? а вот и нет  [new]
-2-
Member

Откуда:
Сообщений: 15330
andrey_anonymous
-2-
пропущено...
предлагаю задуматься над словом "наблюдаемой", сравнить порядок погрешности и исходной величины.

Раскройте пожалуйста свою мысль.
Между 101 и 10-38 требуется точность 40 значащих цифр. binary_double дает до 18 десятичных знаков.
28 июн 17, 14:27    [20596806]     Ответить | Цитировать Сообщить модератору
 Re: вы думаете что 1+(-1) = 0? а вот и нет  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18398
-2-
andrey_anonymous
пропущено...

Раскройте пожалуйста свою мысль.
Между 101 и 10-38 требуется точность 40 значащих цифр. binary_double дает до 18 десятичных знаков.

И что из этого следует?
28 июн 17, 14:34    [20596834]     Ответить | Цитировать Сообщить модератору
 Re: вы думаете что 1+(-1) = 0? а вот и нет  [new]
kernA
Member

Откуда: Санкт-Петербург
Сообщений: 390
bigker
не могу найти на форуме. но проблема старая и я был уверен что давно решенная
Oracle 11.2.0.3.0 64 bit

пропущено...

с чем связан этот баг в целом понятно - особенности хранения вещественных чисел. вопрос в другом, как с этим бороться? получается что ни в одном запросе нельзя использовать суммирование без округления? результат не гарантирован. или архитектурно прописывать всегда точность хранения....


create table t123(a number, b number);

insert into t123 values (1,1);
insert into t123 values (-1/3,2);
insert into t123 values (-1/3,3);
insert into t123 values (-1/3,4);

select b, a, sum(a) over () nsum from t123



b a nsum
1 1 3,4E-39

По логике, после вычисления надо округлять.
Могут не сойтись условия a-b=0, либо минус между множествами.
28 июн 17, 14:50    [20596893]     Ответить | Цитировать Сообщить модератору
 Re: вы думаете что 1+(-1) = 0? а вот и нет  [new]
kernA
Member

Откуда: Санкт-Петербург
Сообщений: 390
kernA
Могут не сойтись условия a-b=0.


* имелось ввиду значения из одной колонки, сгруппированные по определённому признаку
28 июн 17, 14:53    [20596914]     Ответить | Цитировать Сообщить модератору
 Re: вы думаете что 1+(-1) = 0? а вот и нет  [new]
dbpatch
Member

Откуда:
Сообщений: 1130
andrey_anonymous
-2-
пропущено...
Между 101 и 10-38 требуется точность 40 значащих цифр. binary_double дает до 18 десятичных знаков.

И что из этого следует?


следует то, что некорректно сравнивать граничные number и binary_float, в первый влазит намного больше "информации" - значащих цифр, разница между ними будет всегда

проблема же автора скорее из отряда того, что у него клиенские double/float (а это очень распостраненная практика - преобразовывать number, который base100 разновидность BCD в типы IEEE на клиенте, ибо там других типов нет) пишутся в базу данных без специальной коррекции, в результате он наступает на проблему

http://0.30000000000000004.com/

и тут ему только в trunc()/round(), ну и в dump() для полного просвещения, сама же СУБД тут абсолютно ни при чем.
28 июн 17, 15:01    [20596970]     Ответить | Цитировать Сообщить модератору
 Re: вы думаете что 1+(-1) = 0? а вот и нет  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18398
dbpatch
andrey_anonymous
пропущено...

И что из этого следует?


следует то, что некорректно сравнивать граничные number и binary_float

:)

Фикус немного в другом.
number в oracle имеет не только десятичную мантиссу, но и, что более важно, десятичный порядок.
Числа, представленные в IEEE-754, имеют двоичный порядок.
И тут наступает смешная штука - множества представимых чисел в этих представлениях тупо не совпадают.
И чем больше порядок - тем больше расстояние между представимыми числами, и, следовательно, больше абсолютная погрешность.
В итоге, сохраняя IEEE-754 (а они бывают 32, 64, и 80 бит) во вроде бы более точный number, получаем по факту потерю точности, поскольку в number не всегда возможно представить значение double.
Потому и ввели в 12с binary_double и binary_float, чтобы классические приложения могли хранить данные без преобразования и не "наступать".
И в этой связи мне показался слегка странным данный тут совет избегать этих типов.
Если что - устриц ел, в смысле - приходилось как-то плотно заниматься "самопальным" преобразованиями из IEEE-754 в number и обратно.
28 июн 17, 16:22    [20597357]     Ответить | Цитировать Сообщить модератору
 Re: вы думаете что 1+(-1) = 0? а вот и нет  [new]
dbpatch
Member

Откуда:
Сообщений: 1130
andrey_anonymous
dbpatch
пропущено...


следует то, что некорректно сравнивать граничные number и binary_float

:)

Фикус немного в другом.
number в oracle имеет не только десятичную мантиссу, но и, что более важно, десятичный порядок.
Числа, представленные в IEEE-754, имеют двоичный порядок.

не совсем десятичный, там base100. но с натяжкой ок, можно считать десятичным :)
http://www.orafaq.com/wiki/Number#Internal_storage


andrey_anonymous
И тут наступает смешная штука - множества представимых чисел в этих представлениях тупо не совпадают.
И чем больше порядок - тем больше расстояние между представимыми числами, и, следовательно, больше абсолютная погрешность.
В итоге, сохраняя IEEE-754 (а они бывают 32, 64, и 80 бит) во вроде бы более точный number, получаем по факту потерю точности, поскольку в number не всегда возможно представить значение double.


а не наоборот ???

andrey_anonymous
Потому и ввели в 12с binary_double и binary_float, чтобы классические приложения могли хранить данные без преобразования и не "наступать".

сомнительно, ИМХО это было скорее для вопроса перфоманса.

andrey_anonymous
И в этой связи мне показался слегка странным данный тут совет избегать этих типов.\

Oracle в подавляющем большинстве случаев используется для подсчета и хранения денег, а использование там нативного
double вообще говоря преступление с т.з. бухгалтерий всяких (если у тебя нет библиотеки для "нормализации" копеек после умножений и делений).

в IBM вообще есть целая библиотека для "правильного" подсчета денег https://github.com/libdfp/libdfp
даром что ей никто не пользуется (в Java и .NET тоже есть имплементации, тоже непопулярные).

andrey_anonymous
Если что - устриц ел, в смысле - приходилось как-то плотно заниматься "самопальным" преобразованиями из IEEE-754 в number и обратно.

да нет там никаких устриц, задача для начального уровня. я с ней столкнулся практически на второй неделе после того, как вообще начал работать программистом - мне принесли два отчета где цифры Итого не сходились.

странно, что неофиты до сих пор наступают на такие грабли, и никто не решил проблему системно
28 июн 17, 16:39    [20597413]     Ответить | Цитировать Сообщить модератору
 Re: вы думаете что 1+(-1) = 0? а вот и нет  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18398
dbpatch
не совсем десятичный, там base100. но с натяжкой ок, можно считать десятичным :)

Сам когда-то так полагал (и вроде даже писал на этом форуме).
Но там чистый BCD.
Если конвертировать по-байтно - то да, удобно в сторичной считать.
Однако в контексте беседы это вообще не имеет значения.

dbpatch
andrey_anonymous
И тут наступает смешная штука - множества представимых чисел в этих представлениях тупо не совпадают.
И чем больше порядок - тем больше расстояние между представимыми числами, и, следовательно, больше абсолютная погрешность.
В итоге, сохраняя IEEE-754 (а они бывают 32, 64, и 80 бит) во вроде бы более точный number, получаем по факту потерю точности, поскольку в number не всегда возможно представить значение double.

а не наоборот ???

Что конкретно наоборот?
Расстояние между представимыми числами, точность или невозможность представить IEEE-754 в number?

dbpatch
andrey_anonymous
Потому и ввели в 12с binary_double и binary_float, чтобы классические приложения могли хранить данные без преобразования и не "наступать".

сомнительно, ИМХО это было скорее для вопроса перфоманса.

Что-то я сомневаюсь относительно заметного "перформанс гаин" :)

dbpatch
странно, что неофиты до сих пор наступают на такие грабли, и никто не решил проблему системно

Так нет тут "системной проблемы", потому и не решают.
Всего лишь особенности машинных вычислений, которые по идее должны преподаваться на профильных курсах.
28 июн 17, 17:06    [20597509]     Ответить | Цитировать Сообщить модератору
 Re: вы думаете что 1+(-1) = 0? а вот и нет  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18398
Вот более "чистая" иллюстрация несовпадения представимых множеств IEEE-754 и number:
clear
set numwidth 20
with t as (
select 1.000000000000001d bd
     , cast(1.000000000000001d as number) bd2n 
     , 1.000000000000001 n
from dual
connect by level < 10000
)
select min(n) as "Number", sum(n) "Number Calc", min(bd) "IEEE-754 double  ", sum(bd) "IEEE754 double Calc", min(bd2n) "IEEE->Number", sum(bd2n) "IEEE->Number Calc" from t
;

              Number          Number Calc IEEE-754 double   IEEE754 double Calc         IEEE->Number    IEEE->Number Calc
-------------------- -------------------- ----------------- ------------------- -------------------- --------------------
   1.000000000000001 9999.000000000009999 1.000000000000001 9999.000000000002     1.0000000000000011 9999.0000000000109989 

Как видно и примера, само по себе десятичное 1.000000000000001 представимо в number, но не представимо в double, хотя и делает вид, что представимо :)
Ближайшее к 1.000000000000001 представимое в double - это '3FF0000000000005', однако это НЕ 1.000000000000001 и при конвертации double->number получаем ближайшее представимое в number - 1.0000000000000011

Одновременно иллюстрация про точность.
Как видим, суммирование в number дает корректный результат.
Однако суммирование в double идет с потерей точности, поскольку мантиссы в 6.5 байт для представления суммы не хватает.
28 июн 17, 18:07    [20597751]     Ответить | Цитировать Сообщить модератору
 Re: вы думаете что 1+(-1) = 0? а вот и нет  [new]
dbpatch
Member

Откуда:
Сообщений: 1130
andrey_anonymous
dbpatch
не совсем десятичный, там base100. но с натяжкой ок, можно считать десятичным :)

Сам когда-то так полагал (и вроде даже писал на этом форуме).
Но там чистый BCD.


ой да ладно. base100 это и разновидность BCD, но если мы говорим про IBM варианты, то .... блин, да нет понятия чистый BCD
https://en.wikipedia.org/wiki/Binary-coded_decimal



andrey_anonymous
dbpatch
а не наоборот ???

Что конкретно наоборот?
Расстояние между представимыми числами, точность или невозможность представить IEEE-754 в number?

всякий IEEE-754 можно представить как number, но не наоборот.
ты говорил что как раз не всякий IEEE-754 можно в number, что не есть научно


andrey_anonymous
dbpatch
странно, что неофиты до сих пор наступают на такие грабли, и никто не решил проблему системно

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


это не техническая системная проблема, а проблема организационная.

практически все актуальные языки программирования имеют реализацию Money/Currency или подобного, от Fixed pointer до BCD вариаций, но этим никто не пользуются, все почему-то считают деньги через double, который без специальных библиотек можно использовать лишь для инженерных/научных расчетов, но никак не для бухгалтерий

есть даже специальный вид консалтинга - приходят и начинают код "линтать", на предмет некорректного использования double (первым делом ищут операторы деления и умножения, находят практически каждый раз в новом коде)
28 июн 17, 18:28    [20597793]     Ответить | Цитировать Сообщить модератору
 Re: вы думаете что 1+(-1) = 0? а вот и нет  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18398
dbpatch
andrey_anonymous
пропущено...
Сам когда-то так полагал (и вроде даже писал на этом форуме).
Но там чистый BCD.

ой да ладно. base100 это и разновидность BCD

Ну покажи вариант BCD, который нельзя назвать "BASE 100" :)

dbpatch
всякий IEEE-754 можно представить как number, но не наоборот.

Неправда ваша.
Пример - выше :)
Что до научности - то независимо от системы счисления множество вещественных чисел нельзя представить ни в одном из машинных представлений уже просто ввиду перечислимости представимых множеств.
Из первого попавшегося можно почитать здесь:
http://www.softelectro.ru/ieee754.html
7.4 Точность представления вещественных чисел в формате IEEE754

А в обсуждаемом случае имеем ошибку преобразования из одного представления в другое просто ввиду несовпадения множеств представимых чисел.
28 июн 17, 18:39    [20597817]     Ответить | Цитировать Сообщить модератору
 Re: вы думаете что 1+(-1) = 0? а вот и нет  [new]
dbpatch
Member

Откуда:
Сообщений: 1130
andrey_anonymous
dbpatch
пропущено...

ой да ладно. base100 это и разновидность BCD

Ну покажи вариант BCD, который нельзя назвать "BASE 100" :)


Я дал ссылку на википедию, все описанные там варианты BCD - не base 100.


andrey_anonymous
dbpatch
всякий IEEE-754 можно представить как number, но не наоборот.

Неправда ваша.
Пример - выше :)

какой такой пример? с power? ну так power для double выдает искаженный результат, но это не означает, что существует
какое-то представимое в double число, которое нельзя воткнуть в number без потери значимых цифр.

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

andrey_anonymous
Что до научности - то независимо от системы счисления множество вещественных чисел нельзя представить ни в одном из машинных представлений уже просто ввиду перечислимости представимых множеств.
Из первого попавшегося можно почитать здесь:
http://www.softelectro.ru/ieee754.html
7.4 Точность представления вещественных чисел в формате IEEE754

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


вот жеж блин :\

где я говорил, что бесконечное множество вещественных чисел можно представить в number?
число 0.3(3), как результат 1/3 нельзя представить ни в double, ни в number, без потери точности, это и ежу понятно,

речь же была про другое, про то, что не всякий double можно в number - я к этому утверждению (ложному) и придрался

говоря проще - я утверждал (вернее предпологаю), что все конечное множество числел double можно поместить в множество number.


вот и прошу опровергнуть оное, примерами непомещаемых чисел
28 июн 17, 18:53    [20597840]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3 4   вперед  Ctrl      все
Все форумы / Oracle Ответить