sql: add support for decimal arithmetic

[petermattis]

There are several Go decimal arithmetic libraries to choose from:

• https://github.com/oguzbilgic/fpd
• http://godoc.org/github.com/shopspring/decimal
• http://godoc.org/speter.net/go/exp/math/dec/inf
  • This one is used by kubernetes. Is that the best stamp of approval?

These libraries all have essentially the same implementation which is to use math/big.Int for the integer value and keep a separate scale int parameter. The values are thus represented as unscaled * 10**(-scale).

[tildeleb]

It's worth reading this:
golang/go#12127
In particular I found the discussion of big.Decimal vs decnumber.Decimal128 illuminating. For financial applications there seems to be a good case to be made for decnumber.Decimal128.

[rin01]

also, golang/go#12332

https://github.com/rin01/decnum
https://godoc.org/github.com/rin01/decnum

It is a 128bits floating point decimal type.
It's very new, 2 weeks old, and the API may change a litlle bit, but I think it is perfectly usable.
To be confident in its usability, see all the test files in the package.

[petermattis]

Thanks for the pointers.

The SQL decimal/numeric type has user-specified precision. Postgres provides a very large range of allowed precisions (up to 131072 digits before the decimal point; up to 16383 digits after the decimal point). MySQL is more limiting (up to 65 total decimal digits with up to 30 after the decimal point).

FYI, choosing a decimal number library to use is the easiest part of this change. The harder part is plumbing through support for the decimal type everywhere (table schemas, expression evaluation, indexing, etc).

[nvanbenschoten]

To get a better feel for the relative usages of the different libraries, I searched for the three mentioned above in the top 1000 open source Go repos, only to find that the only use of any of them is in Kubernetes. Spring's article on their library gave a good enough argument against fpd.Decimal, so I narrowed it down to the second and third options. Both represent arbitrary-precision fixed-point decimals the same way (big.Int value and int32 exp), but I found http://godoc.org/github.com/shopspring/decimal 's API easier to work with, so for now I'm going to use Spring's library unless any good arguments against it arise.

[petermattis]

Sounds like a good place to start. I imagine the usage of the decimal library should be fairly isolated and we'll be able to revisit this decision if it proves problematic.

[JackKrupansky]

The SQL standard is relatively silent on what distinction there is between NUMERIC and DECIMAL. AFAICT, the only distinction is that NUMERIC assures that the precision is fixed, while DECIMAL only guarantees that at least the precision is maintained - more decimal digits are possible. In both cases the scale (digits to right of the decimal point are fixed.)

Here's the literal text from the 2003 standard (unofficial copy):
"21) NUMERIC specifies the data type exact numeric, with the decimal precision and scale specified by the and .
22) DECIMAL specifies the data type exact numeric, with the decimal scale specified by the and the implementation-defined decimal precision equal to or greater than the value of the specified ."

In any case, it would be useful to be specific as to what rules DECIMAL follows in CockroachDB, and whether NUMERIC is merely an exact synonym or not. They appear to be exact synonyms in Postgres. They appear to be exact synonyms in CockroachDB as well, but we should be explicit.

Postgres and standard SQL (and now CockroachDB) also support the DEC abbreviation for DECIMAL.

[petermattis]

NUMERIC is an exact synonym for DECIMAL in CockroachDB. Making any subtle difference between the two seems like a recipe for confusion.

[JackKrupansky]

Is this issue now fully addressed? I mean, the pull request was merged, so what's left?

Besides the token scanner issue of recognizing large numbers so that they don't parse as FLOAT/INT.

Is the encoding settled? I know there has been discussion related to DECIMAL vs. FLOAT encoding, but is it all settled now?

Is the switch from shopspring to inf.Dec fully settled and accepted (merge has been done)?

Also, this issue is currently listed in the 1.0 section of the Roadmap even though it sounds like it will be (mostly) in for Beta.

[nvanbenschoten]

This seems to have been addressed. Adding math/big.Dec to the stdlib is still up in the air, so we can track that, along with its initial third-party implementation, in #6009.