PIVOT subquery parameter doesn't work #9548
Description
What happens?
I see a strange error when I try to use a parameter in a PIVOT subquery clause, ie
rows, err = db.Query("PIVOT (select * from users where 1=1 and user in (?)) t ON year using sum(clicks) group by name", "u1")
I get panic: Binder Error: Could not find parameter with identifier 1
As I understand the error is thrown by the DuckDB:
I used the Go client with the version:
To Reproduce
This Go code reproduces the bug:
package main
import (
"database/sql"
"fmt"
_ "github.com/marcboeker/go-duckdb"
)
func main() {
db, err := sql.Open("duckdb", "")
if err != nil {
fmt.Println(err)
return
}
stmt, err := db.Prepare("CREATE TABLE users (name varchar, clicks integer, year integer)")
if err != nil {
fmt.Println(err)
return
}
defer stmt.Close()
_, err = stmt.Exec()
if err != nil {
fmt.Println(err)
return
}
stmt, err = db.Prepare(`INSERT INTO users (name, clicks, year) VALUES ('u1', 1, 1), ('u1', 2, 2)`)
if err != nil {
fmt.Println(err)
return
}
defer stmt.Close()
_, err = stmt.Exec()
if err != nil {
panic(err)
}
rows, err := db.Query("select * from users where 1=1 and user in (?)", "u1")
if err != nil {
panic(err)
}
defer rows.Close()
rows, err = db.Query("PIVOT (select * from users where 1=1 and user in ('u1')) t ON year using sum(clicks) group by name limit ?", 10)
if err != nil {
panic(err)
}
defer rows.Close()
rows, err = db.Query("PIVOT (select * from users where 1=1 and user in ('u1')) t ON year using sum(clicks) group by name ")
if err != nil {
panic(err)
}
defer rows.Close()
rows, err = db.Query("PIVOT (select * from users where 1=1 and user in (?)) t ON year using sum(clicks) group by name", "u1")
if err != nil {
panic(err)
}
defer rows.Close()
}OS:
macOS
DuckDB Version:
0.9.1
DuckDB Client:
Go
Full Name:
Egor Ryashin
Affiliation:
Rilldata
Have you tried this on the latest main branch?
I have tested with a release build (and could not test with a main build)
Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?
- Yes, I have