search

PRQL / prql

PRQL is a modern language for transforming data — a simple, powerful, pipelined SQL replacement
https://prql-lang.org
Apache License 2.0
9.6k stars 208 forks source link

using `in` with an empty array pattern produces invalid SQL #4595

Closed Globidev closed 3 weeks ago

Globidev commented 3 weeks ago

What happened?

in [] value transpiles to value in () which is not valid syntax (at least in postgresql)

PRQL input

prql target:sql.postgres
from table
filter (field | in [])

SQL output

SELECT
  *
FROM
  "table"
WHERE
  field IN ()

Expected SQL output

SELECT
  *
FROM
  "table"
WHERE
  FALSE -- Or maybe some other smart alternative I don't know about

MVCE confirmation

Anything else?

Thanks for this wonderful project ❤️

max-sixty commented 3 weeks ago

Thanks @Globidev . Indeed, seems like we should we working around that postgres issue. (Ideally postgres would allow an empty list!).

This would likely be a fairly small PR into https://github.com/PRQL/prql/blob/2f2c08b168e9f6894ff65e315da44c6a86e6237d/prqlc/prqlc/src/sql/gen_expr.rs#L159-L186 if anyone wants to give it a go.

PrettyWood commented 3 weeks ago

Closed by #4598