Word64 does not roundtrip through a database correctly

[NorfairKing]

Bug Reports

The Word64 instance for PersistField does not roundtrip through databases correctly.
It uses fromIntegral for converting between Word64 and Int64.

While this does technically roundtrip, the database values will be silently wrong because negative values will be saved instead of positive ones.

Example:

This does technically roundtrip:

ghci> import Data.Word
ghci> import Data.Int
ghci> 2^64
18446744073709551616
ghci> fromIntegral (18446744073709551615  :: Word64) :: Int64
-1
ghci> fromIntegral (-1 :: Int64) :: Word64
18446744073709551615

BUT the database will contain -1, which does not sort in the same way.

Solutions

You could have another PersistValue constructor for Word64, but I'm not sure if all the "supported" databases support such a type.
As far as I can tell, sqlite supports larger integers by storing them as reals but I don't know about other databases:

sqlite> create table t1 (i INTEGER);
sqlite> insert into t1 values(18446744073709551615);
sqlite> insert into t1 values(0);
sqlite> insert into t1 values(1);
sqlite> select * from t1 order by i;
0
1
1.84467440737096e+19
sqlite> select typeof(i) from t1;
real
integer
integer

[ncaq]

MySQL have BIGINT UNSIGNED (unsigned 64bit integer)
PostgreSQL have numeric (user-specified precision, exact)

[parsonsmatt]

Related issue: #1095 And a potential fix #1096

I agree this is really unfortunate. I think we'd need to remove the instance for Word64 for a release (or two) and provide LegacyWord64 and ProperWord64 newtypes to make this a migration that wouldn't cause data integrity issues for folks relying on the existing behavior.