haskell-beam / haskell-beam/beam
Incorrect query generated when using `nub_` in subquery
- 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.