Sum of a 'bigint` field gets coerced into a 'PersistRational'

[erikd]

trafficstars

As requested by @parsonsmatt in https://github.com/bitemyapp/esqueleto/issues/82#issuecomment-584725299 .

The relevant bits of the schema are:

  Tx
    hash                ByteString          sqltype=hash32type
    block               BlockId             -- This type is the primary key for the 'block' table.
    outSum              Word64              sqltype=lovelace
    fee                 Word64              sqltype=lovelace
    size                Word64              sqltype=uinteger
    UniqueTx            hash

  Block
    hash                ByteString          sqltype=hash32type
    epochNo             Word64 Maybe        sqltype=uinteger
    slotNo              Word64 Maybe        sqltype=uinteger
    blockNo             Word64 Maybe        sqltype=uinteger
    previous            BlockId Maybe
    merkelRoot          ByteString Maybe    sqltype=hash32type
    slotLeader          SlotLeaderId
    size                Word64              sqltype=uinteger
    time                UTCTime             sqltype=timestamp
    txCount             Word64              sqltype=uinteger
    UniqueBlock         hash

  Epoch
    outSum              Word64              sqltype=outsum
    txCount             Word64              sqltype=uinteger
    no                  Word64              sqltype=uinteger
    startTime           UTCTime             sqltype=timestamp
    endTime             UTCTime             sqltype=timestamp
    UniqueEpoch         no
    deriving Show

where I am using a number of PostgreSQL domain types:

CREATE DOMAIN lovelace AS bigint CHECK (VALUE >= 0 AND VALUE <= 45000000000000000);
CREATE DOMAIN outsum AS bigint CHECK (VALUE >= 0);
CREATE DOMAIN txindex AS smallint CHECK (VALUE >= 0 AND VALUE < 1024);
CREATE DOMAIN uinteger AS integer CHECK (VALUE >= 0);
CREATE DOMAIN hash32type AS bytea CHECK (octet_length (VALUE) = 32);

The query that geneates the data to insert as a row of the epoch table is:

type ValMay a = Value (Maybe a)
queryEpochEntry :: MonadIO m => Word64 -> ReaderT SqlBackend m (Either ExplorerNodeError Epoch)
queryEpochEntry epochNum = do
    res <- select . from $ \ (tx `InnerJoin` blk) -> do
              on (tx ^. TxBlock ==. blk ^. BlockId)
              where_ (blk ^. BlockEpochNo ==. just (val epochNum))
              pure $ (sum_ (tx ^. TxOutSum), count (tx ^. TxOutSum), min_ (blk ^. BlockTime), max_ (blk ^. BlockTime))
    case listToMaybe res of
      Nothing -> pure $ Left (ENEEpochLookup epochNum)
      Just x -> pure $ convert x
  where
    convert :: (ValMay Rational, Value Word64, ValMay UTCTime, ValMay UTCTime) -> Either ExplorerNodeError Epoch
    convert tuple =
      case tuple of
        (Value (Just outSum), Value txCount, Value (Just start), Value (Just end)) ->
            Right $ Epoch (fromIntegral $ numerator outSum) txCount epochNum start end
        _other -> Left $ ENEEpochLookup epochNum

The first odd thing is that sum_ over a field that is Word64 on the Haskell side and lovelace (bigint within a specified range) on the SQL size results in a Rational as above.

The second oddity is that the Epoch struct returned from the above query can be inserted (using Persistent rather than Esqueleto directly btw) fine, but when I switched to repsert it threw an exception because the count field (Word64) is coerced into a PersistRational. I worked around that by switching from repsert to replace.

[parsonsmatt]

So, part of the issue here is that sum is naturally polymorphic in Postgresql. You can sum_ :: SqlExpr (Value Int) -> SqlExpr (Value Rational). But the problem is that Haskell and Postgres aren't really talking to each other about their types.

One quick fix is to make PersistField Int work fine for a PersistRational, which I am probably going to implement - after all, it already parses a PeristDouble for compatibility with oracle lmao.

But this does not solve the underlying problem of lack of communication between postgres and Haskell here. sum_, to be safe in Postgres, needs to cast the output type to correspond with the actual type.

 sum_     :: (PersistField a, PersistField b) => SqlExpr (Value a) -> SqlExpr (Value (Maybe b))
-sum_     = unsafeSqlFunction "SUM"
+sum_     = castAsSqlType @(Maybe b) . unsafeSqlFunction "SUM"

castAsSqlType - womp womp - requires us to know how to write a cast expression for the backend and type in question, beyond just knowing what type we have. We've got PersistField, but not PersistFieldSql, which is what gives us our type.