arrayAgg and UnexpectedNull

[mohanzhang]

I'm new to esqueleto (and pretty new to Yesod/Persistent) so I apologize if I'm missing something obvious :) I am getting the following error, and I think I understand why, but I'm not sure how to fix it (from yesod devel's log:

column number 8 is out of range 0..-1
column number 8 is out of range 0..-1
16/Sep/2015:12:05:59 -0700 [Debug#SQL] SELECT "client"."id", "client"."name", "client"."email", "client"."partner_id", "client"."tier", "client"."notes", "client"."created_at", "client"."updated_at", array_agg("consultant"."id")
FROM "client" LEFT OUTER JOIN "plan" ON "plan"."client_id" = "client"."id" LEFT OUTER JOIN "consultant" ON "consultant"."id" = "plan"."consultant_id"
GROUP BY "client"."id"
; []
16/Sep/2015:12:05:59 -0700 [Error#yesod-core] UnexpectedNull {errSQLType = "_int4", errSQLTableOid = Nothing, errSQLField = "", errHaskellType = "Int64", errMessage = ""} @(yesod_2gNoBIsCQyuFa0Zr5VzFu6:Yesod.Core.Class.Yesod ./Yesod/Core/Class/Yesod.hs:628:5)

My query looks like:

runDB $ do
    select $ from $ \(client `LeftOuterJoin` plan `LeftOuterJoin` consultant) -> do
    on (consultant ^. ConsultantId ==. plan ^. PlanConsultantId)
    on (plan ^. PlanClientId ==. client ^. ClientId)
    groupBy (client ^. ClientId)
    return (client, arrayAgg (consultant ^. ConsultantId))

The schema is essentially that Clients have many Consultants through Plans, and I am trying to return an array of ConsultantIds for each Client. Since not all Clients have Plans at any given time, it is the case that the consultant ids array may be {NULL}. I have verified that the generated SQL looks and works like the SQL I would write by hand, so the marshaling back into haskell types is what is suspect.

And as I understand it, this is probably where the error is coming from: Persistent is expecting a list of numbers to marshal, like {1,2,3}, but doesn't know what to do with an element of NULL. The desired behavior is to have it throw away the null, so that {NULL} becomes [], but I'm not sure how to do this. Is it possible?

Thank you for this great library, btw. I have been consistently surprised by how intuitive it has been.

[meteficha]

I think that today is your lucky day, as this bug may have already been fixed! Can you try using persistent-postgresql-2.2.1?

[mohanzhang]

Wow, yes. I've spent longer in the checkout line at a grocery store than I did waiting for this fix. Awesome :)

For anyone else reading this, the only change I had to make to my code above was lifting my arrayAgg projection into Maybe with just, so:

runDB $ do
    select $ from $ \(client `LeftOuterJoin` plan `LeftOuterJoin` consultant) -> do
    on (consultant ^. ConsultantId ==. plan ^. PlanConsultantId)
    on (plan ^. PlanClientId ==. client ^. ClientId)
    groupBy (client ^. ClientId)
    return (client, arrayAgg (just $ consultant ^. ConsultantId)) -- <--- note just

Otherwise you will get PersistMarshalError "int64 Expected Integer, received: PersistNull", but this makes perfect sense, of course.

Cheers @meteficha!