search

cockroachdb / cockroach

CockroachDB — the cloud native, distributed SQL database designed for high availability, effortless scale, and control over data placement.
https://www.cockroachlabs.com
Other
30.04k stars 3.79k forks source link

sql: decimals with more than 2000 digits aren't supported #35191

Closed dacrypt closed 5 years ago

dacrypt commented 5 years ago

Describe the problem

Error trying to store a decimal (github.com/shopspring/decimal) value into a decimal column type.

To Reproduce


        column_name       |  data_type  | is_nullable | column_default | generation_expression |               indices                | is_hidden
+-------------------------+-------------+-------------+----------------+-----------------------+--------------------------------------+-----------+
  id                      | UUID        |    false    | NULL           |                       | {"primary","idx_credits_deleted_at"} |   false
  created_at              | TIMESTAMPTZ |    true     | NULL           |                       | {}                                   |   false
  updated_at              | TIMESTAMPTZ |    true     | NULL           |                       | {}                                   |   false
  deleted_at              | TIMESTAMPTZ |    true     | NULL           |                       | {"idx_credits_deleted_at"}           |   false
  credit_name             | STRING      |    true     | NULL           |                       | {}                                   |   false
  creditor_id_foreign_key | UUID        |    false    | NULL           |                       | {}                                   |   false
  debtor_id_foreign_key   | UUID        |    false    | NULL           |                       | {}                                   |   false
  principal               | DECIMAL     |    true     | NULL           |                       | {}                                   |   false
  interest                | DECIMAL     |    true     | NULL           |                       | {}                                   |   false
  accrued                 | DECIMAL     |    true     | NULL           |                       | {}                                   |   false
  time                    | INT         |    true     | NULL           |                       | {}                                   |   false
  rate                    | DECIMAL     |    true     | NULL           |                       | {}                                   |   false
  date                    | STRING      |    true     | NULL           |                       | {}                                   |   false
  note                    | STRING      |    true     | NULL           |                       | {}                                   |   false
  status                  | INT         |    true     | NULL           |                       | {}                                   |   false
(15 rows)

Time: 19.907488ms

INSERT INTO "installments" ("id","created_at","updated_at","deleted_at","number","date","payment","interest","repayment","total_interest","total_repayment","total_payment","balance","status","credit_id_foreign_key") VALUES ('26a61f30-f837-4d4d-bfe3-47d01c98edfd','2019-02-25 16:37:51','2019-02-25 16:37:51',NULL,'111','','21027.02470781200793489996801706','3568.02004964269289189755451524473369073022359363817997078380617538514887082979178809631119684495230812654343196753740867707877324870846073054591696694188641036571179645599309203755859842944441626128170622750742507496792160610495214033036530394713604072725218451484831406441216288533461434273152723293348541541970220283355643666507596976730943846495306187952360937575289819508659509138403219662592935314583730033990959924414753085912077439471141800070208739827362051326248175723111074729631798870173446740524861209635066792170613420733989445075663140115718495357750301668567568199730990563223627815194361703908942691298763068544129947137854458706373765847573110286352112468103117530244989776427098094336350727035172774573066693355428113729846317601500299033754363378460633029502226769483310093017020224939044930604809115306655762359888205068792408154783844926801284652160468848949298999482669435885735032031800691299281691876596166257841165422407300487910800451943288044022479641528755779173589153369571395909724947332835719647511292298280505717844458443907312999447196342429065034844482765426157027410870380338988477886958507574845803876459874881971553664168193998570182393900697923342012026626279512878994305460969029098823730502095010536624206351789831613613674564951142715192450269299689950530300657714010806401857158677454703804280322550801701998465268353278943210651224770354856561926631791374475221730573568465342314153304976758918079442908814958333229162632600016611400830161776275562568111418792989612637914730659837354617176525940823994703757951011379701332498563439926501799842817723950379206455592844419113538785032711931156207451961943829516328274510320310073639709749241985203588263266339744489053882242320532100180102917446603391567414753597695795218111804072586269261643941890913165998140847450737052629569262613016754166192818574377142246660271579068957316809508260912553253935109331592950266779407780461337890320308722855556125532909456238070666422302381403762208148688414077315532735125651456','17459.00465816931504300241350181526630926977640636182002921619382461485112917020821190368880315504769187345656803246259132292122675129153926945408303305811358963428820354400690796244140157055558373871829377249257492503207839389504785966963469605286395927274781548515168593558783711466538565726847276706651458458029779716644356333492403023269056153504693812047639062424710180491340490861596780337407064685416269966009040075585246914087922560528858199929791260172637948673751824276888925270368201129826553259475138790364933207829386579266010554924336859884281504642249698331432431800269009436776372184805638296091057308701236931455870052862145541293626234152426889713647887531896882469755010223572901905663649272964827225426933306644571886270153682398499700966245636621539366970497773230516689906982979775060955069395190884693344237640111794931207591845216155073198715347839531151050701000517330564114264967968199308700718308123403833742158834577592699512089199548056711955977520358471244220826410846630428604090275052667164280352488707701719494282155541556092687000552803657570934965155517234573842972589129619661011522113041492425154196123540125118028446335831806001429817606099302076657987973373720487121005694539030970901176269497904989463375793648210168386386325435048857284807549730700310049469699342285989193598142841322545296195719677449198298001534731646721056789348775229645143438073368208625524778269426431534657685846695023241081920557091185041666770837367399983388599169838223724437431888581207010387362085269340162645382823474059176005296242048988620298667501436560073498200157182276049620793544407155580886461214967288068843792548038056170483671725489679689926360290250758014796411736733660255510946117757679467899819897082553396608432585246402304204781888195927413730738356058109086834001859152549262947370430737386983245833807181425622857753339728420931042683190491739087446746064890668407049733220592219538662109679691277144443874467090543761929333577697618596237791851311585922684467264874348544','1506639.81885563310930119224717345701293079475260135100098738883758330964485854224848380575338897078842456066687555635488841374257310250029217321050443125510333737640036433731287985663123100004397450651895833161243839385864926050397931849071886879781599834865148831643942227126976246642670499383593102358255441803909768854606887065884822914959931776080938313640470163314261949164846064855279538135617146480810559649880828299151007497947172687174496021001382808875923356944424379875745390621581945695681901665494244695032909275965897623527575389303620747044645092709672655441419257137423971650176676188227421220916121380728839731596929263888658749387503959766482804974886195924973801529464593510361752175231156357025850412102031063024274528923400468662573575773028309818651959595281947030513491134911641335634330780283880235663275197348131054216995276702172764692373243662401416481144763662233539409147747181040821460325561157313218667433139733221750216880899003785037526802095199157727097539359218588093232866399828811677637802956355707735444706452149370442814766974247561124289350198739962784927373110976919330349245852016210444905730669501446967073793039046353056367296576800526057779258110508952308400311023476195367829484865656135845440701177895676588198581067320831675908569237954763783949406292999897108440616066077983609935963564687166908588554063284262993852391303175881370263308980744128511477848731192989631455974682467661842618842429470609708562536756346824466163355890158605646580069774431039963381229052826492495814902163462510145019494383518268329174617903026541349971140926816645251074128380077808563924917743121513900414115580627802406586402240301301746122895908645065220683429149481260667839873254028271089043876817319709373835750209216188824208410225135549810859514546811785866189677799498573774750708712103825550931042380471768587974097769002691323820157052027648408838919117522781286818405011144355458959708638075195447034826019593738973881303679204669141696850096951940031869315532735125651456','827359.92371149977147270420272020298706920524739864899901261116241669035514145775151619424661102921157543933312444364511158625742689749970782678949556874489666262359963566268712014336876899995602549348104166838756160614135073949602068150928113120218400165134851168356057772873023753357329500616406897641744558196090231145393112934115177085040068223919061686359529836685738050835153935144720461864382853519189440350119171700848992502052827312825503978998617191124076643055575620124254609378418054304318098334505755304967090724034102376472424610696379252955354907290327344558580742862576028349823323811772578779083878619271160268403070736111341250612496040233517195025113804075026198470535406489638247824768843642974149587897968936975725471076599531337426424226971690181348040404718052969486508865088358664365669219716119764336724802651868945783004723297827235307626756337598583518855236337766460590852252818959178539674438842686781332566860266778249783119100996214962473197904800842272902460640781411906767133600171188322362197043644292264555293547850629557185233025752438875710649801260037215072626889023080669650754147983789555094269330498553032926206960953646943632703423199473942220741889491047691599688976523804632170515134343864154559298822104323411801418932679168324091430762045236216050593707000102891559383933922016390064036435312833091411445936715737006147608696824118629736691019255871488522151268807010368544025317532338157381157570529390291437463243653175533836644109841394353419930225568960036618770947173507504185097836537489854980505616481731670825382096973458650028859073183354748925871619922191436075082256878486099585884419372197593413597759698698253877104091354934779316570850518739332160126745971728910956123182680290626164249790783811175791589774864450189140485453188214133810322200501426225249291287896174449068957619528231412025902230997308676179842947972351591161080882477218713181594988855644541040291361924804552965173980406261026118696320795330858303149903048059968130684467264874348544','2333999.74256713288077389644989366','172640.07628850022852729579727979701293079475260135100098738883758330964485854224848380575338897078842456066687555635488841374257310250029217321050443125510333737640036433731287985663123100004397450651895833161243839385864926050397931849071886879781599834865148831643942227126976246642670499383593102358255441803909768854606887065884822914959931776080938313640470163314261949164846064855279538135617146480810559649880828299151007497947172687174496021001382808875923356944424379875745390621581945695681901665494244695032909275965897623527575389303620747044645092709672655441419257137423971650176676188227421220916121380728839731596929263888658749387503959766482804974886195924973801529464593510361752175231156357025850412102031063024274528923400468662573575773028309818651959595281947030513491134911641335634330780283880235663275197348131054216995276702172764692373243662401416481144763662233539409147747181040821460325561157313218667433139733221750216880899003785037526802095199157727097539359218588093232866399828811677637802956355707735444706452149370442814766974247561124289350198739962784927373110976919330349245852016210444905730669501446967073793039046353056367296576800526057779258110508952308400311023476195367829484865656135845440701177895676588198581067320831675908569237954763783949406292999897108440616066077983609935963564687166908588554063284262993852391303175881370263308980744128511477848731192989631455974682467661842618842429470609708562536756346824466163355890158605646580069774431039963381229052826492495814902163462510145019494383518268329174617903026541349971140926816645251074128380077808563924917743121513900414115580627802406586402240301301746122895908645065220683429149481260667839873254028271089043876817319709373835750209216188824208410225135549810859514546811785866189677799498573774750708712103825550931042380471768587974097769002691323820157052027648408838919117522781286818405011144355458959708638075195447034826019593738973881303679204669141696850096951940031869315532735125651456','0','83dd3bbb-d640-40f3-acd7-6b7880b1c7a4');

pq: could not parse "3568.02004964269289189755451524473369073022359363817997078380617538514887082979178809631119684495230812654343196753740867707877324870846073054591696694188641036571179645599309203755859842944441626128170622750742507496792160610495214033036530394713604072725218451484831406441216288533461434273152723293348541541970220283355643666507596976730943846495306187952360937575289819508659509138403219662592935314583730033990959924414753085912077439471141800070208739827362051326248175723111074729631798870173446740524861209635066792170613420733989445075663140115718495357750301668567568199730990563223627815194361703908942691298763068544129947137854458706373765847573110286352112468103117530244989776427098094336350727035172774573066693355428113729846317601500299033754363378460633029502226769483310093017020224939044930604809115306655762359888205068792408154783844926801284652160468848949298999482669435885735032031800691299281691876596166257841165422407300487910800451943288044022479641528755779173589153369571395909724947332835719647511292298280505717844458443907312999447196342429065034844482765426157027410870380338988477886958507574845803876459874881971553664168193998570182393900697923342012026626279512878994305460969029098823730502095010536624206351789831613613674564951142715192450269299689950530300657714010806401857158677454703804280322550801701998465268353278943210651224770354856561926631791374475221730573568465342314153304976758918079442908814958333229162632600016611400830161776275562568111418792989612637914730659837354617176525940823994703757951011379701332498563439926501799842817723950379206455592844419113538785032711931156207451961943829516328274510320310073639709749241985203588263266339744489053882242320532100180102917446603391567414753597695795218111804072586269261643941890913165998140847450737052629569262613016754166192818574377142246660271579068957316809508260912553253935109331592950266779407780461337890320308722855556125532909456238070666422302381403762208148688414077315532735125651456" as type decimal```

**Environment:**
Build Tag:    v2.1.3
Build Time:   2018/12/17 19:15:31
Distribution: CCL
Platform:     linux amd64 (x86_64-unknown-linux-gnu)
Go Version:   go1.10.3
C Compiler:   gcc 6.3.0
Build SHA-1:  0c87b11cb99ba5c677c95ded55dcba385928474e
Build Type:   release
jordanlewis commented 5 years ago

@mjibson could you PTAL? It looks like the input decimals are much too long, but presumably we should be able to handle unlimited-size decimals anyway.

dacrypt commented 5 years ago

Hi, thanks for replying. I'm aware of the length of the decimals. The decimals are the result of a financial mathematic operation. Keeping the whole decimals is desirable in this case. I'm just storing the value as it comes.

I couldn't find a limit of decimals in the docs so I guess this must be a bug or something.

maddyblue commented 5 years ago

That number is 2010 digits long. We only support 2000 digits. This is indeed a difference between us and postgres. It's more a known limitation than a bug, though. We are unlikely to change this in the upcoming release. I've wanted to make our decimal precision match postgres for a while, so maybe in the next release we will be able to address this.

For now the best we can do is either you truncate to 2k digits or use a string and do your math in your application.

jordanlewis commented 5 years ago

cc @rafiss

rafiss commented 5 years ago

40379 should address this issue, and it will be included in the 19.2 release