EXISTS/NOT EXISTS subqueries produce syntax error

[newmanjeff]

Running an exists or not exists query fails. E.g. adding this integration test to test/ecto/integration/crud_test.exs:

    test "handles exists subquery" do
      account1 = TestRepo.insert!(%Account{name: "Main"})
      user1 = TestRepo.insert!(%User{name: "John"}, [])
      TestRepo.insert!(%AccountUser{user_id: user1.id, account_id: account1.id})

      subquery = from(au in AccountUser, where: au.user_id == parent_as(:user).id, select: 1)

      assert [_] = TestRepo.all(from(a in Account, as: :user, where: exists(subquery)))
    end

Produces an error:

  1) test select handles exists subquery (Ecto.Integration.CrudTest)
     test/ecto/integration/crud_test.exs:237
     ** (Exqlite.Error) near "(": syntax error
     SELECT a0."id", a0."name", a0."email", a0."inserted_at", a0."updated_at" FROM "accounts" AS a0 WHERE (exists((SELECT 1 FROM "account_users" AS sa0 WHERE (sa0."user_id" = a0."id"))))
     code: assert [_] = TestRepo.all(from(a in Account, as: :user, where: exists(subquery)))
     stacktrace:
       (ecto_sql 3.7.2) lib/ecto/adapters/sql.ex:760: Ecto.Adapters.SQL.raise_sql_call_error/1
       (ecto_sql 3.7.2) lib/ecto/adapters/sql.ex:693: Ecto.Adapters.SQL.execute/5
       (ecto 3.7.2) lib/ecto/repo/queryable.ex:219: Ecto.Repo.Queryable.execute/4
       (ecto 3.7.2) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3
       test/ecto/integration/crud_test.exs:244: (test)

The error appears to be that the generated SQL has two parentheses after the EXISTS (exists((), but sqlite only supports a single exists( .

[warmwaffles]

Yea this can be fixed. Thank you for throwing a test together. If you want to attempt to fix it, that's fine. Otherwise, I'll try to get to this soon.

[newmanjeff]

Thanks. I briefly investigated, but the solution wasn't apparent to me.

[warmwaffles]

Thanks. I briefly investigated, but the solution wasn't apparent to me.

Liar 😉 It looks like you figured it out!

[newmanjeff]

Sometimes you just have to scratch the itch 🤣