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

[chkl]

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.

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)

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.