Nesting/unnesting many/catListTable crashes?

[tomjaguarpaw]

Consider the following Rel8 program. It produces SQL which crashes.

Query Error: error: could not identify column "f1" in record data type

Is this known/expected?

The ultimate problem is that .f1, .f2, etc. for accessing fields of ROWs don't really work "through" SELECTs.

*Rel8 Data.Int Prelude> putStr $ showQuery $ do { q1 <- many (many (values [1 , 2 :: Expr Int16])); q2 <- catListTable q1; catListTable q2 }
SELECT
CAST("unnest0_9" AS int2) as "anon"
FROM (SELECT
      UNNEST("unnest0_7") as "unnest0_9",
      *
      FROM (SELECT
            (UNNEST(CASE WHEN ("rebind0_5") IS NULL THEN CAST(ARRAY[] AS record[]) ELSE "result0_4" END)).f1 as "unnest0_7",
            *
            FROM (SELECT *
                  FROM
                  (SELECT
                   0) as "T1"
                  LEFT OUTER JOIN
                  (SELECT
                   TRUE as "rebind0_5",
                   *
                   FROM (SELECT
                         *
                         FROM (SELECT
                               ARRAY_AGG("inner0_4") as "result0_4"
                               FROM (SELECT
                                     ROW(CASE WHEN ("rebind0_3") IS NULL THEN CAST(ARRAY[] AS int2[]) ELSE "result0_2" END) as "inner0_4",
                                     *
                                     FROM (SELECT *
                                           FROM
                                           (SELECT
                                            0) as "T1"
                                           LEFT OUTER JOIN
                                           (SELECT
                                            TRUE as "rebind0_3",
                                            *
                                            FROM (SELECT
                                                  *
                                                  FROM (SELECT
                                                        ARRAY_AGG("inner0_2") as "result0_2"
                                                        FROM (SELECT
                                                              "values0_1" as "inner0_2",
                                                              *
                                                              FROM (SELECT
                                                                    *
                                                                    FROM (SELECT "column1" as "values0_1"
                                                                          FROM
                                                                          (VALUES
                                                                           (CAST(1 AS int2)),
                                                                           (CAST(2 AS int2))) as "V") as "T1") as "T1") as "T1"
                                                        GROUP BY COALESCE(0)) as "T1") as "T1") as "T2"
                                           ON
                                           TRUE) as "T1") as "T1"
                               GROUP BY COALESCE(0)) as "T1") as "T1") as "T2"
                  ON
                  TRUE) as "T1") as "T1") as "T1"

[ocharles]

What version of PostgreSQL are you on? We somewhat know about this, and afaik it works on newer PostgreSQL versions. On older ones, the fix is to use castTable with many. Can you share the Haskell that produced this crashing query?

[tomjaguarpaw]

It fails in every version of Postgres on DB Fiddle. In fact it seems that .f1 syntax for extracting fields of anonymous rows was first supported in v13, yet v13 doesn't support this particular usage (which is a flaw of Postgres I think).

The Haskell is in my post above:

do { q1 <- many (many (values [1 , 2 :: Expr Int16])); q2 <- catListTable q1; catListTable q2 }

[ocharles]

Thanks, I missed that this was in GHCI. Can you try changing some many x to many <$> x? I'll have a play soon myself

[tomjaguarpaw]

Here is a full program that demonstrates the problem (requiring the hasql and tmp-postgres packages). castTable doesn't seem to help, but I'm not sure I'm using it right.

import Rel8
import Data.Int
import Hasql.Statement
import Hasql.Session
import Hasql.Connection
import Database.Postgres.Temp
import Data.Text (Text)

main = Database.Postgres.Temp.with $ \db -> do
  Right conn <- acquire (toConnectionString db)

  flip run conn $ statement () $ select $ do
    q1 <- castTable <$> many (castTable <$> many (values [1 , 2 :: Expr Int16]))
    q2 <- catListTable q1
    catListTable q2

[tomjaguarpaw]

Can you try changing some many x to many <$> x?

That doesn't seem to type check.

[ocharles]

Sorry, I meant many x to many $ castTable <$> x

[tomjaguarpaw]

I made that change, but it still crashes with the same error:

module Main where

import Rel8
import Data.Int
import Hasql.Statement
import Hasql.Session
import Hasql.Connection
import Database.Postgres.Temp

main = Database.Postgres.Temp.with $ \db -> do
  Right conn <- acquire (toConnectionString db)

  flip run conn $ statement () $ select $ do
    q1 <- many (castTable <$> (many (castTable <$> (values [1 , 2 :: Expr Int16]))))
    q2 <- catListTable q1
    catListTable q2

[ilyakooo0]

Is this possibly related to #219?