Floating point numbers pose challenges for blockchains and related protocols by breaking determinism.

By @Carson Farmer

Take a look at your favorite blockchain project with smart contract support and you’ll notice right away that the types of data supported are pretty limited. For instance, Solidity (the language used by EVM chains) supports both signed and unsigned integers, booleans, and bytes, as well as special types like addresses and enums. But not floating point values. Similarly, Near supports strings, signed and unsigned integers, and booleans (and various collections of these things). Again, no floating point values.

Because floating point values are — by definition — approximate. Let me say that again: Floating point values are approximate. If you need an exact answer, you should not use floating-point values, in Tableland or in any other product. This is not a Tableland limitation. It is a mathematical limitation inherent in the design of floating-point numbers.

So how can we possibly deal with floating point values in a blockchain scenario where a network of validators abhors non-determinism? The answer is simply that we can’t. Heck, even our underlying database implementation (SQLite) “makes no guarantees about the accuracy of computations on floating point values, as no such guarantees are possible”. So what does that mean for Tableland?

First, let’s start with some context. Recently, our core detectives (ahem, developers) discovered an issue with floating point math when testing our validator network. The investigation was comprehensive and actually pretty exciting, but the final outcome came down to the fact that some folks were running AMD64 CPUs, and some folks had the newer M1 processor (which is ARM based). Technically speaking, we’d expect that floating point rounding or precision to be standardized between CPU architectures, but as it turned out rounding is done differently in M1 than AMD/Intel AMD64 architectures… oops!

That finding was… pretty surprising (and interesting). Our team even found a random article doubting that you can trust floating point numbers in Apple’s M1 processor. In the article, they conclude:

So we have some bad news here; while the article stated that things “should be almost (if not completely) identical”, the reality in our case was quite different!

The good news was that our core development team was able to identify this inconsistency pretty quickly during testing, which demonstrates the power of proof-based systems for detecting non-determinism. We were able to find a mismatch in how two different CPU architectures round floating-point numbers (after some specific precision) by replaying the historic Tableland events — a deterministic test checking a real web3 application with real data and usage.

The short answer is that it means we can’t support the full set of storage classes and datatypes outlined in the SQLite spec, upon which our own Tableland SQL Specification is based. This is a bit of a bummer for SQL enthusiasts, but isn’t entirely surprising, because it turns out floating point math is hard, and lots of people know this, which is probably why they aren’t used for storing currencies or with blockchains very often.

Our core developer team has already patched our SQL Specification to reflect the fact that we no longer support floating point values (REAL values in the old spec), and developers should ensure they aren’t leveraging this data type in their projects. But additionally, the ANY type that our spec once supported also opened up the door to undefined behavior (see below), so this type will be deprecated as well. This means that any previous statements that utilized these types will no longer be valid Tableland statements.

From our initial research, it looks like a very limited set of tables will be affected by this specification change, and since all were on testnets, things are looking pretty good! In several cases, our team was able to identify the projects using these types, and they have or will be notified of the future breaking change to the spec.

We did encounter one interesting issue with our own Rigs tables (which has already been fixed!) that revealed an interesting quirk of SQLite that makes this whole thing even more complicated. The short version of that story is that, even if you specify a field as ANY, SQLite will interpret floating point values stored within that column as REAL, and the floating point math issue sneaks back in. The only way to ensure (safely) that this isn’t the case, is to also exclude ANY from the specification. Oh well, we probably should have been more specific ANYway 😉.

So in summary, when developing applications that require numeric data (which is probably just about all applications out there) it is always good to remember that floating point math is hard, and it is generally a good idea to avoid it when you can.

The changes to the spec will go live in the New Year. In the mean time, you can read up on the new Tableland SQL Specification data types, and learn more about why floating point math is hard. For developers, you should follow the guidelines in the updated spec, and take a look at our updated docs for potential solutions when working with floating point values.

If your application relies on floating point values, 1) are you sure?, 2) if you can, update (and we’ll have some more spec updates coming soon to help with this), and/or 3) reach out to us for help on migrating your data back into spec compliance!