Emulate DECIMAL data type on SQLite

[AnyhowStep]

trafficstars

Use case:

For testing with SQLite, https://github.com/AnyhowStep/tsql/pull/27

Also nice to have DECIMAL data type on SQLite for regular applications.

Can probably emulate with TEXT and user defined functions (sqlite3_create_function()).

[AnyhowStep]

https://www.sqlite.org/datatype3.html

A column with NUMERIC affinity may contain values using all five storage classes. When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if the text is a well-formed integer or real literal, respectively. If the TEXT value is a well-formed integer literal that is too large to fit in a 64-bit signed integer, it is converted to REAL. For conversions between TEXT and REAL storage classes, only the first 15 significant decimal digits of the number are preserved. If the TEXT value is not a well-formed integer or real literal, then the value is stored as TEXT. For the purposes of this paragraph, hexadecimal integer literals are not considered well-formed and are stored as TEXT. (This is done for historical compatibility with versions of SQLite prior to version 3.8.6 2014-08-15 where hexadecimal integer literals were first introduced into SQLite.) No attempt is made to convert NULL or BLOB values.

---

Given the above, for SQLite, it's better to use TEXT columns for tables and add user-defined functions to implement DECIMAL data type functions that do not lose precision.

Using NUMERIC columns will cause TEXT values to be converted to INTEGER or REAL and lose accuracy...

The alternative is to use string values like 'qwerty-non-number-123456.7890'. With the non-number prefix, SQLite will not convert to INTEGER or REAL. This is ugly as hell, and I am against this workaround, though.

So, I'll probably have to document that if the emulated-DECIMAL feature is enabled, columns using NUMERIC should be changed to TEXT, to preserve accuracy

[AnyhowStep]

It seems like JS doesn't have a decent fixed-point library that handles both precision and scale.

There is https://github.com/MikeMcl/decimal.js/ but it only does precision and has a different model from the SQL standard.

I really don't want to have to write my own...

[AnyhowStep]

If you write something like 1.0/3.0 on PostgreSQL, you get this,

0.33333333333333333333

20 decimal places.

If at least one of the numbers has more than 20 decimal places, the result has the same scale as the highest of the two operands.

[AnyhowStep]

PostgreSQL,

SELECT CAST(1.999 AS DECIMAL(10, 2))
> 2.00
SELECT CAST(1.991 AS DECIMAL(10, 2))
> 1.99

https://www.postgresql.org/docs/9.0/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL

If the scale of a value to be stored is greater than the declared scale of the column, the system will round the value to the specified number of fractional digits. Then, if the number of digits to the left of the decimal point exceeds the declared precision minus the declared scale, an error is raised.

[AnyhowStep]

PostgreSQL,

• -5.0/9.0 is -0.55555555555555555556
• 5.0/6.0 is 0.55555555555555555556

[AnyhowStep]

PostgreSQL,

SELECT CAST(5.0 AS DECIMAL(2,1))/CAST(9.0 AS DECIMAL(2,1))
> 0.55555555555555555556

[AnyhowStep]

PostgreSQL,

SELECT 1.1 * 1.23
> 1.353

The scale is sum of the scale of operands

---

For addition, ~~division~~, subtraction, the scale is the MAX(scale of operands)

[Edit] Division is fucked. Uses weird algorithm that sort-of uses significant-figures-but-not-really

[AnyhowStep]

MySQL sort of follows PostgreSQL for the most part, ignoring division and implicit minimum scale

[AnyhowStep]

May be helpful to consult this for the API of the emulation

https://github.com/tc39/proposal-decimal

---

For division, https://github.com/tc39/proposal-decimal/issues/13

[AnyhowStep]

For better division results, maybe this?

https://math.stackexchange.com/questions/1339411/how-to-estimate-the-number-of-decimal-places-required-for-a-division

This tells us how many digits we need for the division, in advance

[AnyhowStep]

https://www.reddit.com/r/SQL/comments/fd8zij/when_have_you_used_numericdecimal_and_how_often/