decoding number to bigint returns wrong number

[domenkozar]

λ> :set -XOverloadedStrings
λ> 
λ> import Hasql.Connection
λ> import Hasql.Session
λ> import Hasql.Statement
λ> import Hasql.Decoders as HD
λ> import Hasql.Encoders as HE
λ> Right conn <- acquire "postgresql:///whatever"
λ> let s = Statement "SELECT 889988 :: NUMERIC" HE.unit (HD.singleRow (HD.column HD.int8)) True
λ> let session =  statement () s
λ> result <- Hasql.Session.run session conn
λ> result
Right 5777156

[nikita-volkov]

You're using a wrong codec.

> flip Hasql.Session.run conn (statement () (Statement "SELECT 889988 :: NUMERIC" HE.unit (HD.singleRow (HD.column HD.int8)) True))
Right 5777156
> flip Hasql.Session.run conn (statement () (Statement "SELECT 889988 :: NUMERIC" HE.unit (HD.singleRow (HD.column HD.numeric)) True))
Right 889988.0
> flip Hasql.Session.run conn (statement () (Statement "SELECT 889988 :: BIGINT" HE.unit (HD.singleRow (HD.column HD.int8)) True))
Right 889988

[domenkozar]

@nikita-volkov I know it's the wrong codec, but the issue is deeper, because postgresql is not statically typed. It's a slippery slope:

select pg_typeof(SUM(3000 :: BIGINT + 5000 :: BIGINT));
 pg_typeof 
-----------
 numeric
(1 row)

select pg_typeof(SUM(3000 :: INT + 5000 :: INT));
 pg_typeof 
-----------
 bigint
(1 row)

Could numeric -> bigint casting be a hard error?

[ocharles]

This is why rel8 puts sum in a type class: https://github.com/ocharles/rel8/blob/master/Rel8/Internal/Aggregate.hs#L41

[domenkozar]

I'm slowly seeing the benefit of going that far :)

@nikita-volkov does the wire protocol include the type that PostgreSQL is returning?

[nikita-volkov]

If I remember correctly it does include the type's OID. Since I sense I get what you're gonna suggest, I've created a dedicated issue: nikita-volkov/hasql#97

[domenkozar]

I've hit this bug again after months in production :/

[ocharles]

I strongly suggest adding explicit casts to all your queries to guarantee that the result matches what hasql will expect. This is what we do in our query DSL

[nikita-volkov]

Well, as mentioned in the linked thread, I'm open to PRs :) Unfortunately this issue is low on my priority list