Named query parameters are sensitive to position when specifying `kwargs`

[slwu89]

Hi, I noticed that when using Var, and specifying the query parameter values as keyword arguments in DBInterface.execute, it is sensitive to the order that they are given in (i.e. they must match the order that they appear in the query). This seems strange to me given that we are specifying their "names" anyway, is there a way to fix this? On [cf6cc811] FunSQL v0.15.0.

using DataFrames
using 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, ":memory:")
catalog = FunSQL.reflect(con)

DBInterface.execute(con, """
    CREATE TABLE tab (
        name TEXT,
        val NUMERIC
    );
""")

DBInterface.execute(con, """
    INSERT INTO tab VALUES
    ('a', 5),
    ('b', 6),
    ('c', 5),
    ('a', 3),
    ('b', 4),
    ('c', 2);
""")

q = From(:tab) |> Where(Fun.or(Get.name .== Var.NAME, Get.val .== Var.VAL)) |> Select(:name, :val)

# works
DataFrame(DBInterface.execute(con, render(catalog, q); NAME="a", VAL=5))

# this is unexpected
DataFrame(DBInterface.execute(con, render(catalog, q); VAL=5, NAME="a"))

DBInterface.close!(con)

[xitology]

To make sure that parameters are passed in the correct order expected by the rendered SQL, you need to use FunSQL.pack:

sql = render(catalog, q)
params = pack(sql, (VAL=5, NAME="a"))
DBInterface.execute(con, sql, params)

The FunSQL.pack() function converts a tuple of named parameters into a vector of positional parameters suitable for use with DBInterface.execute(). If you are using a FunSQL.DB connection wrapper, rendering and parameter packing is handled automatically, and you can pass named parameters directly, like so:

db = FunSQL.DB(con; catalog)
DBInterface.execute(
    db,
    From(:tab) |> Where(Fun.or(Get.name .== Var.NAME, Get.val .== Var.VAL)),
    VAL=5,
    NAME="a")

[slwu89]

Thanks @xitology! I wonder if this could be worked into somewhere in the docs other than the API page, that's why I missed this function, I read "Usage Guide" and "Examples" which seemed comprehensive and more engaging than browsing function docstrings.

Yeah, in my case I need to be able to read/write to the connection, and due to this issue with DuckDB I discovered (duckdb/duckdb#17453), I can't have 2 connections (one for R/W one for R), so I just have a single connection made with DBInterface.connect, and something like the below to run FunSQL queries

function execute_query(con, catalog, query; kwargs...)
    sql = FunSQL.render(catalog, query)
    pars = FunSQL.pack(sql, kwargs)
    DataFrame(DBInterface.execute(con, sql, pars))
end

[clarkevans]

@slwu89 PR #87 has minimal code needed to reflect ATTACH of a read only database (see duckdb#17453).