How to use `Fun.in` with list `Var` query parameter?

[slwu89]

Hi, I'd like to use a Var query parameter as a list for the q arg of Fun.in. I tried the following code but got the error I will paste below the MWE code block. What am I doing wrong? I am on package version [cf6cc811] FunSQL v0.15.0.

using FunSQL, DuckDB
using FunSQL:
    FunSQL, Agg, Append, As, Asc, Bind, CrossJoin, Define, Desc, Fun, From,
    Get, Group, Highlight, Iterate, Join, LeftJoin, Limit, Lit, Order,
    Partition, Select, Sort, Var, Where, With, WithExternal, render

con = DBInterface.connect(DuckDB.DB, "./test.db")
DuckDB.query(con, """
    CREATE TABLE tab (
        name TEXT,
        val NUMERIC
    );

    INSERT INTO tab VALUES
    ('a', 5),
    ('b', 6),
    ('c', 5),
    ('a' ,3);
""")

con_funsql = DBInterface.connect(FunSQL.DB{DuckDB.DB}, "./test.db")

q = From(:tab) |> Where(Fun.in(Get.name, Var.NAMES))
DBInterface.execute(con_funsql, q, NAMES=["a", "b"]) 

DBInterface.close(con)
DBInterface.close(con_funsql)

Error:

julia> DBInterface.execute(con_funsql, q, NAMES=["a", "b"]) 
ERROR: Execute of query "SELECT
  "tab_1"."name",
  "tab_1"."val"
FROM "main"."tab" AS "tab_1"
WHERE ("tab_1"."name" IN ($1))" failed: Conversion Error: Type VARCHAR with value 'a' can't be cast to the destination type LIST

LINE 5: WHERE ("tab_1"."name" IN ($1))
               ^
Stacktrace:
 [1] execute(stmt::DuckDB.Stmt, params::Vector{Any})
   @ DuckDB ~/.julia/packages/DuckDB/SPkZM/src/result.jl:722
 [2] execute
   @ ~/.julia/packages/DuckDB/SPkZM/src/result.jl:872 [inlined]
 [3] execute
   @ ~/.julia/packages/FunSQL/wZQuX/src/connections.jl:133 [inlined]
 [4] execute(conn::FunSQL.SQLConnection{DuckDB.DB}, sql::FunSQL.SQLNode, params::@NamedTuple{NAMES::Vector{String}})
   @ FunSQL ~/.julia/packages/FunSQL/wZQuX/src/connections.jl:122
 [5] #execute#314
   @ ~/.julia/packages/FunSQL/wZQuX/src/connections.jl:113 [inlined]
 [6] top-level scope
   @ ~/Desktop/misc/dbfun.jl:24

[xitology]

Fun.in does not recognize that the second argument is a list, so it generates IN expression assuming a scalar argument, which triggers type error. However, you can generate correct SQL with Fun." IN "(Get.name, Var.NAMES).

[slwu89]

Thanks @xitology. Is that expected behavior?

[xitology]

Thanks @xitology. Is that expected behavior?

I'd say so. Until FunSQL starts tracking argument types, there is little we could do to improve it.

[slwu89]

Thanks. Is it possible somehow to splat a Var parameter into the varargs method of it (Fun.in(x, y₁, y₂, …))? I tried naively just doing Fun.in(Get.name, Var.NAMES...) which didn't work.

[slwu89]

I will close this, I do not think there is anything actionable.