Cannot execute parameterized queries using certain postgres drivers
I run picodata 24.4.0-46-gb1c5a133 on Ubuntu 22.04. I start picodata with:
picodata run --data-dir i1 --listen :3301 --http-listen :8081 --pg-listen :3302
I am using a single instance setup and I have created user and table with the following queries:
CREATE USER andy WITH PASSWORD 'Passw0rd' USING md5;
CREATE TABLE "characters" (
"id" INTEGER NOT NULL,
"name" TEXT NOT NULL,
"year" INTEGER,
PRIMARY KEY ("id")
)
USING MEMTX DISTRIBUTED BY ("id")
OPTION (TIMEOUT = 3.0);
GRANT WRITE ON TABLE "characters" TO andy;
GRANT read ON TABLE "characters" TO andy;
INSERT INTO "characters" values (1, 'Dima', 1997);
I have a sample golang application and I am using lib/pq driver to connect to Picodata and I want to get my new character from DB:
package main
import (
"database/sql"
"log"
_ "github.com/lib/pq"
)
type Character struct {
id int
name string
year int
}
func main() {
connStr := "postgres://ANDY:Passw0rd@localhost:3302?sslmode=disable"
db, err := sql.Open("postgres", connStr)
if err != nil {
log.Fatal(err)
}
id := 1
row := db.QueryRow("SELECT * FROM \"characters\" WHERE \"id\" = $1", id)
var char Character
err = row.Scan(&char.id, &char.name, &char.year)
if err != nil {
log.Fatalf("Error during row scan: %s", err)
}
log.Printf("Character with id %d is %s and is %d years old\n", char.id, char.name, char.year)
}
If I run such an app, I will recieve the following error:
2024/06/06 19:43:33 Error during row scan: pq: picodata error: sbroad: Lua error (IR dispatch): LuaError(ExecutionError("sbroad: LuaError(ExecutionError(\"Type mismatch: can not convert string('1') to integer\"))"))
And I expect the following to be printed:
2024/06/06 19:48:18 Character with id 1 is Dima and is 1997 years old
The problems appears only with the parametrization of the query. If I will swap the query with the following code I will receive my character
row := db.QueryRow("SELECT * FROM \"characters\" WHERE \"id\" = 1")
Edited by Дмитрий Кольцов