Rel8 produces invalid SQL: ERROR: argument of CASE/WHEN must not return a set

[ilyakooo0]

It is my understanding that the goal of rel8 is to produce valid SQL in a type-safe manner. Meaning, if the Haskell code compiles without errors and does not use any constructs explicitly marked as "unsafe" in the library, then the produces SQL should always be valid (assuming the declared tables in code match the actual database schema).

I think I found a case where the produces SQL produces an error in Postgres before it is even checked against the schema.

I wrote this script which reproduces the issue:

#!/usr/bin/env stack
{- stack script
   --resolver lts-20.3
   --package rel8
-}

{-# LANGUAGE DeriveAnyClass     #-}
{-# LANGUAGE DerivingStrategies #-}

import           Data.Int     (Int64)
import           GHC.Generics (Generic)
import qualified Rel8

main = putStrLn $ Rel8.showQuery q

q =
  Rel8.many (pure (Rel8.nothingTable :: Rel8.MaybeTable Rel8.Expr (Foo Rel8.Expr)))
    >>= Rel8.catListTable

newtype Foo f = Foo {x :: Rel8.Column f Int64}
  deriving stock Generic
  deriving anyclass Rel8.Rel8able

The script produces the following schema-independent SQL query:

SELECT
CAST("unnest0_4" AS bool) as "isJust",
CAST(CASE WHEN ("unnest0_4") IS NOT NULL THEN "unnest1_4" ELSE NULL END AS int8) as "Just/x"
FROM (SELECT
      UNNEST(CASE WHEN ("rebind0_2") IS NULL THEN CAST(ARRAY[] AS bool[]) ELSE "result0_1" END) as "unnest0_4",
      CASE WHEN (UNNEST(CASE WHEN ("rebind0_2") IS NULL THEN CAST(ARRAY[] AS bool[]) ELSE "result0_1" END)) IS NOT NULL THEN UNNEST(CASE WHEN ("rebind0_2") IS NULL THEN CAST(ARRAY[] AS int8[]) ELSE "result1_1" END) ELSE NULL END as "unnest1_4",
      *
      FROM (SELECT *
            FROM
            (SELECT
             0) as "T1"
            LEFT OUTER JOIN
            (SELECT
             TRUE as "rebind0_2",
             *
             FROM (SELECT
                   ARRAY_AGG("inner0_1") as "result0_1",
                   ARRAY_AGG("inner1_1") as "result1_1"
                   FROM (SELECT
                         CAST(NULL AS bool) as "inner0_1",
                         CASE WHEN (CAST(NULL AS bool)) IS NOT NULL THEN CAST(NULL AS int8) ELSE NULL END as "inner1_1",
                         *
                         FROM (SELECT
                               0) as "T1") as "T1"
                   GROUP BY COALESCE(0)) as "T1") as "T2"
            ON
            TRUE) as "T1") as "T1"

When I try to execute the query I get the following error:

Query 1 ERROR: ERROR:  argument of CASE/WHEN must not return a set
LINE 6:       CASE WHEN (UNNEST(CASE WHEN ("rebind0_2") IS NULL THEN...

I think this indicates that there is a bug somewhere in rel8.

[shane-circuithub]

Thanks so much for reporting this @ilyakooo0, you're right that it is an error. I've added a fix in #240 for what it's worth. I'm sorry it took so long to get to this.