MySQL doesn't like hex floats?

[jteresco]

https://forum.travelmapping.net/index.php?topic=6238.0

The floating point format introduced in #623 didn't play nice with the version of MySQL on noreaster. All mileages were being read in as 0.

[yakra]

An unlikely series of very specific events got us here.

• Using sprintf up till now, I was only able to control the number of digits after the decimal point, not the overall number of sig figs.
• This means minor diffs between Python & C++ flavored SQL files.
• Some tables have sums of data from unordered_maps. These can get stored & thus summed in non-deterministic order, meaning cumulative rounding errors can lead to slightly different data between runs.
• The need to account for these small differences while diffing 2 SQL files led me to create compare_sql.
  ...Which as it turns out is kind of a plate of hot garbage.
  The idea is to round decimal values to however many decimal places for each table before making the comparison.
• Here's where the fun really begins.
  I went ahead and blindly used compare_sql, putting too much faith in it while forgetting what it does under the hood.
• When compare_sql gets to the routes table, it "rounds" alb.a002's mileage to 17 decimal places -- even though the new file only had 16 sig figs to begin with. Meanwhile, the old file had 17 decimal places.
• Detecting a mismatch, compare_sql prints the "rounded" strings -- not the original strings as they exist in the .sql file.
• Forgetting about that last bit, I said "What the devil? Why am I getting 17 sig figs?", and thought some more tweaking to siteupdate was needed.
• This led me to try setting the floatfield flag, without an understanding of what it actually did. Bad idea. This is actually the same as setting scientific | fixed. This is where the hex strings came from.
• compare_sql dutifully reads in that hex string, converts to double, uses sprintf to "round" to 17 decimal places, compares it to the string from the old file, and sees no difference. It reports that everything's fine when in reality it's anything but.