Precision of entered and displayed units

[lentschi]

Re-raising a logical issue:

We've had a similar problem before and I thought it to be solved, but the following logical issue just occurred to me again:

Using the received form for the oranges from our current small.en.seeds (supplier_order_unit: "Piece (12 kg)", group_order_unit: kg):
When receiving an amount of 4 kg, the stored value will be displayed as 3.996 kg. That happens as the units_received are stored in supplier_order_unit (to be directly and safely comparable with units_to_order which is obviously stored in the same unit) and 4 / 12 is 0.3333333... but only 3 decimals are stored. So when trying to convert it back for re-editing/display 0.333 * 12 turns out to be 3.996.

The quick fix would be to simply increase precision (to 6 e.g.), but obviously, if ratios are to high, the problem will still occur.

This is related to, but I believe not quite the same, as #72.

[twothreenine]

I'm a fan of saving numbers as rationals because of problems like this -- only converting it for display. But I guess that would take more effort to change.

[lentschi]

I'm a fan of saving numbers as rationals because of problems like this -- only converting it for display. But I guess that would take more effort to change.

Rational is a ruby data type - we need to persist the data in SQL.

The datatypes we can choose from there are limited:

• DECIMAL
  • Pros: Can be used efficiently for calculations (e.g. SUM) in SQL itself; precise
  • Cons: limited to a certain number of decimal points
• FLOAT/DOUBLE
  • Pros: Can be used efficiently for calculations in SQL; Unlimited number of decimal points
  • Cons: imprecise (used for scientific calculation only)
• VARCHAR/TEXT
  • Pros: Unlimited number of decimal points
  • Cons: Cannot be used efficiently for calculations in SQL
• 2 x DECIMAL (Storing the two numbers of a ruby Rational in a DECIMAL column each)
  • Pros: Unlimited number of decimal points (as they are implicit)
  • Cons: Missing rails adapter to persist Rationals in this way (I oddly couldn't find one, can you?); probably would require touching a lot of legacy code currently using single column DECIMALs; Calculations in SQL itself might be possible, but they are a lot more costly and complicated to design

I think our best choice would be to simple increase the scale and precision of the decimals in use to make all real-life cases work. (The only downsides of using DECIMALS with high precision: Negative impact on db size and performance) Then we need to identify those places where rounding still might occur and flash apt warnings when users try to enter numbers that will cause "weird" rounding behavior.

Maybe you have some insights on this @yksflip ?

[twothreenine]

2 x DECIMAL (Storing the two numbers of a ruby Rational in a DECIMAL column each)

We'd only need a pair of (big enough) integers, but the problem remains.
I also couldn't find any easy solution. Some suggested to use an SQL User Defined Type (UDT) but Mariadb does not support that yet.

Some (probably stupid) ideas:

• Adding a table fractions with nominator and denominator columns and using references (too inefficient?)
• Switching to a different database which supports UDT (too much effort?)

[lentschi]

We'd only need a pair of (big enough) integers, but the problem remains.

No, I think, there'd be instances where at least the dividend could have decimal points (Simply if the user enters them into a field that has to be divided before storing it to the db). But that is no really the issue.

Some suggested to use an SQL User Defined Type (UDT)

Yes, and even if we could use them, UDTs would still be slow in SQL calculations (like SUM() for example).

Adding a table fractions with nominator and denominator columns and using references (too inefficient?)

Yes, I'm afraid that would make it even (slightly) slower than just storing dividend and divisor as two columns directly and solve none of the problems.

Switching to a different database which supports UDT (too much effort?)

Even if that would help (which I believe it doesn't - see above), it would add a technology barrier, which I think everyone would like to avoid. (Right now, foodsoft may be optimized and best tested on MariaDB, but should in theory function on all current popular relational DBs supported by rails.)

So, since there were no other feasible suggestions so far, I'll "fix" this by increasing precision to 11 and scale to 6. That should do for most cases.
I'll leave the issue open, but move it to post-merge, so we can discuss it upstream. (I think, it should be fine, if we add warnings in the right places when rounding numbers - also see #72)