haskell-beam / haskell-beam/beam

Incorrect query generated when using `nub_` in subquery

Open
#531 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
Haskell
Stars
635
Forks
193
PR merge metrics
No merged PRs in 30d

Description

### Summary

The following query

```haskell
missingRunCurrencies = do
runPk <- runsWithNoCurrencies
baseQuote <- runBaseQuote runPk
pure (runPk, baseQuote)
where
runsWithNoCurrencies = do
run <- all_ $ runs liquidityDb
guard_ $ not_ $ exists_ $
filter_
(\rc -> RC.rcRun rc `references_` run)
(all_ $ run_currencys liquidityDb)
pure (pk run)
runBaseQuote runId = nub_ $ do
book <- all_ $ books liquidityDb
guard_ $ Book.bookRun book ==. runId
pure (Book.bookBase book, Book.bookQuote book)
```

produces this SQL

```sql
SELECT
"t0"."id" AS "res0",
"t1"."res0" AS "res1",
"t1"."res1" AS "res2"
FROM
"runs" AS "t0" CROSS
JOIN (
SELECT
DISTINCT "t0"."base" AS "res0",
"t0"."quote" AS "res1"
FROM
"books" AS "t0"
WHERE
("t0"."run__id") = ("t0"."id")
) AS "t1"
WHERE
NOT(
EXISTS (
SELECT
"sub_t0"."run__id" AS "res0",
"sub_t0"."currency__symbol" AS "res1"
FROM
"run_currencys" AS "sub_t0"
WHERE
("sub_t0"."run__id") = ("t0"."id")
)
)

```
which is incorrect because `t0` from the subquery (alias for `books`) clashes with the `t0` alias for `runs`. Consequently, `book.id` (instead of `run.id`) is compared to `book.run__id`.

If the `nub_` is removed from `runBaseQuote` then a correct query is generated (in which the subquery's `t0` is named `sub_t0`).

### Schema

```haskell
data RunT f
= Run
{ runId :: C f (SqlSerial Word32)
, runTimeStart :: C f UTCTime
, runTimeEnd :: C f UTCTime
}

data BookT f
= Book
{ bookId :: C f (SqlSerial Word32)
, bookRun :: PrimaryKey RunT f
, bookTime :: C f UTCTime
, bookVenue :: C f Text
, bookBase :: C f Text
, bookQuote :: C f Text
}

data RunCurrencyT f
= RunCurrency
{ rcRun :: PrimaryKey RunT f
, rcCurrency :: C f Text
} deriving Generic
```

### Versions

beam-core: 0.9.0.0
beam-postgres: 0.5.0.0

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.