haskell-beam / haskell-beam/beam

beam-sqlite: Bug in code generation for "EXISTS" subquery

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

Description

Hi,

first of all I want to say that this library is awesome!

Nonetheless do I think that there is a small bug in the query generation. I am currently trying to remove some redundant entries in some "run" table. E.g. I want to delete every run for which a run with similar fields and a lower id already exists. For that I use an exists clause.

```haskell
removeRedundantRuns = runBeamSqlite $ runDelete $ delete (myDb ^. runs) $ \r -> exists_ $ do
t <- all_ (vdiffDb ^. runs)
guard_ $ (r ^. verifierName) ==. (t ^. verifierName)
guard_ $ (r ^. runId) >. (t ^. runId)
guard_ $ (r ^. resultVerdict) ==. (t ^. resultVerdict)
guard_ $ (r ^. program) ==. (t ^. program)
return (t ^. runId)
```
beam now creates this query (my formatting)
```sql
DELETE FROM "runs" WHERE EXISTS (
SELECT "t0"."run_id" AS "res0"
FROM "runs" AS "t0"
WHERE "verifier_name"="t0"."verifier_name"
AND "run_id" > "t0"."run_id"
AND "result" = "t0"."result"
AND "code_hash" = "t0"."code_hash"
);
```
The problem with this query is that `"run_id"` does not refer to the outer table but to `t0`, which makes the comparison `"run_id" > "t0"."run_id"` trivially false. I believe that a correct query should use an alias to refer to the outside.

EDIT: The matter is complicated by the fact that only very recent versions of sqlite allow an alias in a delete statement. I couldn't find any sign of a syntax change in the changelog of sqlite but it's observable when switching from `direct-sqlite-2.3.23` (syntax error when using an alias) to `direct-sqlite-2.3.24` (statement executed as expected). So I am not even sure what the "correct" behavior should be.

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.