`SqliteError: near "(": syntax error` when using `notExists`

It seems like queries with notExists clauses add a pair of parentheses too many:

db
    .select({ foobar: sql<number>`1` })
    .from(content)
    .where(notExists(db.select({ foobar: sql<number>`1` }).from(content)))
    .toSQL()
// Output:
{
  sql: 'select 1 from "Content" where not exists ((select 1 from "Content"))',
  params: []
}

db
    .select({ foobar: sql<number>`1` })
    .from(content)
    .where(notExists(db.select({ foobar: sql<number>`1` }).from(content)))
    .toSQL()
// Output:
{
  sql: 'select 1 from "Content" where not exists ((select 1 from "Content"))',
  params: []
}

Am I doing something wrong or does someone know a workaround?

4 Replies

Angelelz•13mo ago

There is an open issue for this. You can keep track there:

Angelelz•13mo ago

https://github.com/drizzle-team/drizzle-orm/issues/1404

GitHub

[BUG]: SQLite WHERE EXISTS too many parentheses · Issue #1404 · dri...

What version of drizzle-orm are you using? 0.28.6 What version of drizzle-kit are you using? 0.19.13 Describe the Bug The following drizzle query using EXISTs in WHERE generates incompatible SQL fo...

Angelelz•13mo ago

As for a workaround, the where method accepts sql So you could do:

db
    .select({ foobar: sql<number>`1` })
    .from(content)
    .where(sql`not exists (select 1 from ${content})`)
    .toSQL()

db
    .select({ foobar: sql<number>`1` })
    .from(content)
    .where(sql`not exists (select 1 from ${content})`)
    .toSQL()

SeptatrixOP•13mo ago

Thanks, in the meantime I also found https://github.com/drizzle-team/drizzle-orm/issues/1235 and came across the same workaround.

GitHub

[BUG]: SqliteError: near "(": syntax error on query where exists ...

What version of drizzle-orm are you using? 0.28.6 What version of drizzle-kit are you using? No response Describe the Bug Final SQL has a pair redundant () inside where exists const items = await c...

Gaming

Programming

`SqliteError: near "(": syntax error` when using `notExists`