std.in operator does not support expression on left-hand-side

[kgutwin]

What happened?

I'm trying to make a generic "boolean" conversion function, and this is where I am starting:

let coalesce_bool = func
  val <scalar || null>
  -> case [
    val == null => null,
    val == 0 => false,
    val == 1 => true,
    (val | in ["no", "n", "N", "false"]) => false,
    (val | in ["yes", "y", "Y", "true"]) => true,
  ]

This is working well, for example in the following:

from [{foo = 1}, {foo = null}]
derive { foo_b = coalesce_bool foo }

However, feeding a numeric value other than 0 or 1 into the function is causing DuckDB to raise a Conversion Error ("Could not convert string 'no' to INT32). This is because DuckDB gets to the corresponding line in the CASE statement

    WHEN foo IN ('no', 'n', 'N', 'false') THEN false

and tries to cast the strings in the array to numbers.

The solution for me seemed simple -- to cast val to VARCHAR to do the comparison by strings:

let coalesce_bool = func
  val <scalar || null>
  -> case [
    val == null => null,
    val == 0 => false,
    val == 1 => true,
    (val | as VARCHAR | in ["no", "n", "N", "false"]) => false,
    (val | as VARCHAR | in ["yes", "y", "Y", "true"]) => true,
  ]

but this causes a PRQL compiler panic:

The application panicked (crashed).
Message:  args to `std.array_in` must be a column ref and an array
Location: prqlc/prqlc/src/sql/gen_expr.rs:176

  ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ BACKTRACE ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
                                ⋮ 13 frames hidden ⋮                              
  14: prqlc::sql::gen_expr::process_array_in::h96ab7dc44c311876
      at /Users/kgutwin/github/prql/prqlc/prqlc/src/sql/gen_expr.rs:176
  15: prqlc::sql::gen_expr::translate_expr::h392231bd9a27822a
      at /Users/kgutwin/github/prql/prqlc/prqlc/src/sql/gen_expr.rs:106
[ ... snip ... ]

I looked at prqlc/prqlc/src/sql/gen_expr.rs and it's not obvious to me why there's a panic there, especially as the other process_*() functions seem to handle left-hand-side expressions easily. Would it be possible to remove this panic in order to support more complex expressions for the std.in function? Or is there an underlying reason why the IN operator needs to have a column reference only on the left-hand side?

PRQL input

from [{ a = 1 }]
derive { b = ( a+1 | in [2, 3] ) }

SQL output

n/a (compiler crashes)

Expected SQL output

WITH table_0 AS (
  SELECT
    1 AS a
)
SELECT
  a,
  a + 1 IN (2, 3) AS b
FROM
  table_0

MVCE confirmation

• Minimal example
• New issue

Anything else?

No response

[max-sixty]

Good example and it's encouraging to see you pushing functions like this!

There are definitely a few things up — it obv shouldn't panic there.

Also putting an s-string within the pipeline panics :(

let coalesce_bool = func
  val <scalar || null>
  -> case [
    (s"{val}" | in ["no", "n", "N", "false"]) => false,
  ]

from [{foo = 1}, {foo = null}]
derive { foo_b = coalesce_bool foo }

---

For completeness — if I put this in the playground, duckdb doesn't raise an error — does it on your end? Is the error message because of the type of some data that's not there?

let coalesce_bool = func
  val <scalar || null>
  -> case [
    val == null => null,
    val == 0 => false,
    val == 1 => true,
    (val | in ["no", "n", "N", "false"]) => false,
    (val | in ["yes", "y", "Y", "true"]) => true,
  ]

from [{foo = 1}, {foo = null}]
derive { foo_b = coalesce_bool foo }

---

For a workaround — does an s-string in another transform suffice for the moment?

from [{ a = 1 }]
derive a_ = (s"{a} as VARCHAR")
derive { b = ( a_ | in [2, 3] ) }

[kgutwin]

I have to run now, but quickly -- I can provide some more details later on how I've managed to trigger the underlying situation, but unfortunately an s-string in another transform isn't a great solution because of the way this function is used in other parts of our PRQL.

I'm hoping that the PR that I threw together is a simple solution; it does fix the s-string panic as well.