Nā hiʻohiʻona pono ʻole o nā ʻano maoli, a i ʻole e makaʻala me REAL

Ma hope o ka hoʻolaha ʻana nāʻatikala e pili ana i nā hiʻohiʻona o ka paʻi ʻana ma PostgreSQL, ʻo ka ʻōlelo mua loa e pili ana i nā pilikia o ka hana ʻana me nā helu maoli. Ua hoʻoholo wau e nānā wikiwiki i ke code o nā nīnau SQL i loaʻa iaʻu e ʻike i ka nui o kā lākou hoʻohana ʻana i ke ʻano REAL. ʻIke ʻia ua hoʻohana pinepine ʻia ia, a ʻaʻole maopopo mau nā mea hoʻomohala i nā pōʻino ma hope. A ʻo kēia ʻoiai he nui nā ʻatikala maikaʻi ma ka Pūnaewele a ma Habré e pili ana i nā hiʻohiʻona o ka mālama ʻana i nā helu maoli i ka hoʻomanaʻo kamepiula a me ka hana pū me lākou. No laila, ma kēia ʻatikala e hoʻāʻo wau e hoʻopili i ia mau hiʻohiʻona i PostgreSQL, a e hoʻāʻo e nānā wikiwiki i nā pilikia e pili ana iā lākou, i maʻalahi i nā mea hoʻomohala nīnau SQL ke pale aku iā lākou.

Ua ʻōlelo maʻalahi ka palapala PostgreSQL: "ʻO ka mālama ʻana i ia mau hewa a me kā lākou hoʻolaha ʻana i ka wā o ka helu ʻana ke kumuhana o kahi lālā holoʻokoʻa o ka makemakika a me ka ʻepekema kamepiula, ʻaʻole i uhi ʻia ma ʻaneʻi" (ʻoiai e kuhikuhi ana i ka mea heluhelu i ka maʻamau IEEE 754). He aha ke ʻano o nā hewa i manaʻo ʻia ma ʻaneʻi? E kūkākūkā kākou ma ke ʻano, a e ʻike koke ʻia ke kumu aʻu i lawe hou ai i ka peni.

E lawe kākou i kekahi noi maʻalahi:

********* ЗАПРОС *********
SELECT 0.1::REAL;
**************************
float4
--------
    0.1
(1 строка)

ʻO ka hopena, ʻaʻole mākou e ʻike i kahi mea kūikawā - e loaʻa iā mākou ka 0.1 i manaʻo ʻia. Akā i kēia manawa e hoʻohālikelike mākou iā 0.1:

********* ЗАПРОС *********
SELECT 0.1::REAL = 0.1;
**************************
?column?
----------
f
(1 строка)

ʻAʻole like! He mau hana mana! Akā ʻoi aku, ʻoi aku. E ʻōlelo mai kekahi, ʻike wau he hana ʻino ka REAL me nā hakina, no laila e hoʻokomo wau i nā helu holoʻokoʻa ma laila, a e maikaʻi nā mea a pau me lākou. ʻAe, e hoʻolei kāua i ka helu 123 iā REAL:

********* ЗАПРОС *********
SELECT 123456789::REAL::INT;
**************************
   int4   
-----------
123456792
(1 строка)

A ua lilo i 3 hou aku! ʻO ia, ua poina loa ka waihona i ka helu ʻana! A i ʻole ke kuhihewa nei mākou i kekahi mea? E noʻonoʻo kākou.

ʻO ka mua, e hoʻomanaʻo kākou i ka mea. E like me kāu e ʻike ai, hiki ke hoʻonui ʻia kekahi helu decimal i nā mana o ka ʻumi. No laila, ua like ka helu 123.456 me 1*102 + 2*101 + 3*100 + 4*10-1 + 5*10-2 + ​​​​6*10-3. Akā ke hana nei ke kamepiula me nā helu ma ke ʻano binary, no laila pono lākou e hōʻike ʻia ma ke ʻano o ka hoʻonui ʻana i nā mana o ʻelua. No laila, ua hōʻike ʻia ka helu 5.625 ma ka binary me 101.101 a ua like ia me 1*22 + 0*21 + 1*20 + 1*2-1 + 0*2-2 + 1*2-3. A inā hāʻawi mau nā mana maikaʻi o ʻelua i nā helu decimal holoʻokoʻa (1, 2, 4, 8, 16, a me nā mea ʻē aʻe), a laila me nā mana maikaʻi ʻole, ʻoi aku ka paʻakikī o nā mea āpau (0.5, 0.25, 0.125, 0,0625, etc.). ʻO ia ka pilikia ʻAʻole hiki ke hōʻike ʻia kēlā me kēia decimal ma ke ʻano he hakina binary palena. No laila, ʻike ʻia kā mākou 0.1 kaulana ma ke ʻano o ka hakina binary ma ke ʻano he 0.0(0011). No laila, ʻokoʻa ka waiwai hope o kēia helu ma ka hoʻomanaʻo kamepiula ma muli o ka hohonu.

ʻO ka manawa kēia e hoʻomanaʻo ai i ka mālama ʻana o nā helu maoli i ka hoʻomanaʻo kamepiula. Ma ka ʻōlelo maʻamau, ʻekolu mau ʻāpana nui ka helu maoli - hōʻailona, ​​mantissa a me exponent. Hiki i ka hōʻailona ke hoʻohui a i ʻole ka liʻiliʻi, no laila ua hoʻokaʻawale ʻia kahi ʻāpana no ia. Akā ʻo ka helu o nā bits o ka mantissa a me ka exponent e hoʻoholo ʻia e ke ʻano maoli. No laila, no ke ʻano REAL, ʻo ka lōʻihi o ka mantissa he 23 bits (hoʻokahi bit e like me 1 i hoʻohui pū ʻia i ka hoʻomaka o ka mantissa, a ʻo ka hopena he 24), a ʻo ka exponent he 8 mau ʻāpana. ʻO ka huina he 32 bits, a i ʻole 4 bytes. A no ke ʻano DOUBLE PRECISION, ʻo ka lōʻihi o ka mantissa he 52 bits, a ʻo ka exponent he 11 bits, no ka huina o 64 bits, a i ʻole 8 bytes. ʻAʻole kākoʻo ʻo PostgreSQL i ka pololei kiʻekiʻe no nā helu lana.

E hoʻokomo i kā mākou helu decimal 0.1 i loko o nā ʻano REAL a me DOUBLE PRECISION. Ma muli o ka like o ka hōʻailona a me ka waiwai o ka exponent, e kālele mākou i ka mantissa (Ua haʻalele wau i nā hiʻohiʻona ʻike ʻole o ka mālama ʻana i nā waiwai o ka exponent a me nā waiwai maoli ʻole, no ka mea e hoʻopiʻi lākou i ka ʻike a me ka hoʻokaʻawale ʻana mai ke kumu. o ka pilikia, inā makemake, e ʻike i ke kūlana IEEE 754). He aha kā mākou e loaʻa ai? Ma ka laina kiʻekiʻe e hāʻawi wau i ka "mantissa" no ke ʻano REAL (e noʻonoʻo ana i ka hoʻopuni ʻana o ka bit hope loa e 1 i ka helu kikoʻī kokoke loa, inā ʻaʻole ia he 0.099999 ...), a ma ka laina lalo - no ka ke ʻano DOUBLE PRECISION:

0.000110011001100110011001101
0.00011001100110011001100110011001100110011001100110011001

ʻIke loa kēia mau helu ʻokoʻa ʻelua! No laila, i ka hoʻohālikelike ʻana, e uhi ʻia ka helu mua me nā zeros a, no laila, ʻoi aku ka nui ma mua o ka lua (e noʻonoʻo ana i ka hoʻopuni ʻana - ka mea i kaha ʻia me ka wiwo ʻole). Hōʻike kēia i ka ambiguity mai kā mākou mau hiʻohiʻona. Ma ka lua o ka la'ana, ua ho'olei 'ia ka helu 0.1 i kuhikuhi 'ia i ke 'ano DOUBLE PRECISION, a laila e ho'ohālikelike 'ia me kekahi mau 'ano REAL. Hoʻemi ʻia nā mea ʻelua i ka ʻano like, a loaʻa iā mākou ka mea a mākou e ʻike ai ma luna. E hoʻololi kākou i ka nīnau i hiki i nā mea a pau ke kūpono:

********* ЗАПРОС *********
SELECT 0.1::REAL > 0.1::DOUBLE PRECISION;
**************************
?column?
----------
t
(1 строка)

A ʻoiaʻiʻo, ma ka hana ʻana i ka hōʻemi pālua o ka helu 0.1 i REAL a me DOUBLE PRECISION, loaʻa iā mākou ka pane i ka nane:

********* ЗАПРОС *********
SELECT 0.1::REAL::DOUBLE PRECISION;
**************************

      float8       
-------------------
0.100000001490116
(1 строка)

Hoʻomaopopo pū kēia i ke kolu o ka laʻana ma luna. He maʻalahi ka helu 123 ʻAʻole hiki ke hoʻokomo i ka mantissa i 24 mau ʻāpana (23 maopopo + 1 i kuhikuhi ʻia). ʻO ka huina nui loa i hiki ke komo i loko o nā bits 24, ʻo ia ka 224-1 = 16. No laila, ua hoʻopuni ʻia kā mākou helu 777 i ka 215 kokoke loa.

********* ЗАПРОС *********
SELECT 123456789::DOUBLE PRECISION::INT;
**************************
   int4   
-----------
123456789
(1 строка)

ʻo ia wale nō. ʻIke ʻia ʻaʻohe hana mana. Akā ʻo nā mea a pau i wehewehe ʻia he kumu maikaʻi ia e noʻonoʻo ai i ka nui o kou makemake i ke ʻano REAL. ʻO ka pōmaikaʻi nui paha o kāna hoʻohana ʻana, ʻo ia ka wikiwiki o ka helu ʻana me ka nalowale o ka pololei. Akā, he hiʻohiʻona honua kēia e hōʻoia i ka hoʻohana pinepine ʻana i kēia ʻano? Mai noʻonoʻo.

Source: www.habr.com