DuckDB lag in @mutate produces error

[rgreminger]

Below is another case I found where the query unexpectedly throws an error. A quick workaround (for anyone having the same issue) is to do two separate @Mutate statements (i.e., first one creates var_lag, second one produces diff = var - var_lag).

using DataFrames
using TidierData
import TidierDB as DB

df = DataFrame(id = [string('A' + i ÷ 26, 'A' + i % 26) for i in 0:9], 
                          value0 = [i % 2 == 0 ? "aa" : "bb" for i in 1:10], 
                          value1 = repeat(1:5, 2)) 

db = DB.connect(DB.duckdb())
DB.copy_to(db, df, "dt");

dt = DB.dt(db, "dt")

df = @chain dt begin 
    DB.@mutate(
        diff_val = value1 - lag(value1),
        _by = id, _order = value0 
    )
    @aside DB.@show_query _
    DB.@collect()
end

WITH cte_1 AS (
SELECT  id, value0, value1, value1 - 'lag(value1) OVER (PARTITION BY id 
        ORDER BY value0 ASC )' AS diff_val
        FROM dt)  
SELECT *
        FROM cte_1
ERROR: Execute of query "WITH cte_1 AS (SELECT  id, value0, value1, value1 - 'lag(value1) OVER (PARTITION BY id  ORDER BY value0 ASC )' AS diff_val FROM dt)  SELECT * FROM cte_1" failed: Conversion Error: Could not convert string 'lag(value1) OVER (PARTITION BY id  ORDER BY value0 ASC )' to INT64

LINE 1: WITH cte_1 AS (SELECT  id, value0, value1, value1 - 'lag(value1) OVER (PARTITION BY id  ORDER BY value0 ASC...

[drizk1]

Thanks for finding some more issues ! I'll take a look at these 2 and sort something out

[drizk1]

This was straightforward to fix.

I'll have it up soon.