sum has the wrong type

[tomjaguarpaw]

sum :: Aggregator (C.Column a) (C.Column a) is just wrong. PGInt4 does not sum to PGInt4, for example. This is likely to break for users very frequently. #65 is even more important, then, than I realised.

[csasarak]

As some further documentation that I think is related: sum of the result of several countStars breaks because the generated case statement sometimes returns a bigint and sometimes it returns an integer. See the case statement inside the sum in this SQL snippet:

SELECT
"result0_6" as "result1_7"
FROM (SELECT
      *
      FROM (SELECT
            SUM(CASE WHEN ("result2_0_5") IS NULL THEN CAST(0 AS bigint) ELSE "result2_0_5" END) as "result0_6"
            FROM (SELECT
                  *
                  FROM (SELECT *
                        FROM
                        (SELECT
                         0,
                         *
                         FROM (SELECT
                               0) as "T1") as "T1"
                        LEFT OUTER JOIN
                        (SELECT
                         "result0_4" as "result2_0_5",
                         *
                         FROM (SELECT
                               *
                               FROM (SELECT
                                     COUNT(CAST(0 AS integer)) as "result0_4"

Which gives this error: *** Exception: Incompatible {errSQLType = "numeric", errSQLTableOid = Nothing, errSQLField = "result1_7", errHaskellType = "Int64", errMessage = "types incompatible"}

A possible workaround is to run unsafeCast "bigint" on the sum-ed row.

[tomjaguarpaw]

That's strange. Can you send me some sample code that generates this?

[csasarak]

Here is a minimal/simpler example, note that it succeeds on an empty table but fails on one with any rows in it:

{-# LANGUAGE TemplateHaskell,FlexibleInstances,MultiParamTypeClasses,Arrows #-}
module Main where

import Opaleye
import Control.Arrow
import Criterion.Main
import Data.Int
import Data.Profunctor.Product.TH
import Data.Profunctor.Product
import qualified Database.PostgreSQL.Simple as PGS

newtype TestTable' a = TestTable' {
  numberCol :: a
  }

type TestTableColR = TestTable'
  (Column PGInt4)

type TestTableColW = TestTableColR

$(makeAdaptorAndInstance  "pTestTable" ''TestTable')

tableTest :: Table TestTableColW TestTableColR
tableTest = Table "bench" (pTestTable TestTable' {
                           numberCol = required "number_col"
                           })
numbers :: Query TestTableColR
numbers = queryTable tableTest

sumOfCounts :: Query (Column SqlInt8)
sumOfCounts =  (aggregate countStar numbers
                            `unionAll` aggregate countStar numbers)

main :: IO ()
main = do con <- PGS.connect connInfo
          runQuery con sumOfCounts :: IO [Int64]
          return ()

The generated SQL is:

SELECT
"result0_3" as "result1_4"
FROM (SELECT
      *
      FROM (SELECT
            SUM("result0_2") as "result0_3"
            FROM (SELECT
                  *
                  FROM (SELECT
                        COUNT(0) as "result0_2"
                        FROM (SELECT
                              *
                              FROM (SELECT
                                    "number_col" as "number_col0_1"
                                    FROM "bench" as "T1") as "T1") as "T1"
                        GROUP BY COALESCE(0)) as "T1") as "T1"
            GROUP BY COALESCE(0)) as "T1") as "T1"

Which now that I look closer doesn't have the case statement from the above, but results in the same runtime error.

I edited to add another counting, since normally summing a count of a single query wouldn't make any sense.

[tomjaguarpaw]

Hmm, do you need me to do something about this, or are you happy with your workaround for now?

[csasarak]

I apologize for the delay. Casting to bigint was able to fix it. Iirc postgres was giving back numerics, which is the thing that got me because I expected some kind of integer in the haskell code