Differences between the sql.DB.Prepare and sql.DB.Exec methods in parsing a map[string]interface{} argument

Observed

Hello everyone! When I use db.Prepare and then stmt.Exec from database/sql package, I receive the error: clickhouse [AppendRow]: converting map[string]interface {} to Map(String, String) is unsupported. Try using map[string]string. However, when I use db.Exec with the same query and arguments, everything works fine.

Expected behaviour

Both cases should work?

Code example

Go code:

package main

import (
    "context"
    "database/sql"
    "fmt"
    "time"

    "github.com/ClickHouse/clickhouse-go/v2"
)

func main() {
    if err := start(context.Background()); err != nil {
        fmt.Println(err)
    }
}

func start(_ context.Context) error {
    var (
        cookies = map[string]interface{}{"cookie6": "value6", "cookie7": "value7"}
        query   = "INSERT INTO cookies(datetime, cookies) VALUES (?, ?)"
        db      = openDB()
    )

    if err := usePrepare(db, cookies, query); err != nil {
        fmt.Println("usePrepare error:", err)
    }

    if err := useExec(db, cookies, query); err != nil {
        return err
    }

    return nil
}

// this does not work
func usePrepare(db *sql.DB, cookies map[string]interface{}, query string) error {
    tx, err := db.Begin()
    if err != nil {
        return err
    }

    stmt, err := tx.Prepare(query)
    if err != nil {
        return err
    }
    _, err = stmt.Exec(time.Now(), cookies)
    if err != nil {
        tx.Rollback()
        return err
    }

    if err := tx.Commit(); err != nil {
        return err
    }

    return stmt.Close()
}

// this works
func useExec(db *sql.DB, cookies map[string]interface{}, query string) error {
    tx, err := db.Begin()
    if err != nil {
        return err
    }
    _, err = tx.Exec(query, time.Now(), cookies)
    if err != nil {
        tx.Rollback()
        return err
    }
    return tx.Commit()
}

func openDB() *sql.DB {
    sql := clickhouse.OpenDB(&clickhouse.Options{
        Addr: []string{"localhost:9000"},
        Auth: clickhouse.Auth{
            Database: "wpartner",
        },
        ClientInfo: clickhouse.ClientInfo{
            Products: []struct {
                Name    string
                Version string
            }{
                {Name: "an-example-go-client", Version: "0.1"},
            },
        },

        Debugf: func(format string, v ...interface{}) {
            fmt.Printf(format, v)
        },
    })

    if err := sql.Ping(); err != nil {
        if exception, ok := err.(*clickhouse.Exception); ok {
            fmt.Printf("Exception [%d] %s \n%s\n", exception.Code, exception.Message, exception.StackTrace)
        }
        panic(err)
    }

    return sql
}

Clickhouse schema:

CREATE TABLE IF NOT EXISTS cookies(
  datetime DateTime,
  cookies Map(String, String),
) Engine = MergeTree
ORDER BY
  (datetime) TTL datetime + INTERVAL 3 DAY;

Details

Environment

[ ] clickhouse-go version: v2.25.0
[ ] Interface: database/sql
[ ] Go version: 1.22.4
[ ] Operating system: MacOS
[ ] ClickHouse version: 24.4.2
[ ] Is it a ClickHouse Cloud? no
[ ] CREATE TABLE statements for tables involved: yes

@spanwp the different comes from different way of data serialization.

conn.Exec will serialize data to a stringified format client-side using bind logic: https://github.com/ClickHouse/clickhouse-go/blob/14f8a65e86283f1f11ebaeb1b7e8901512ea1db2/examples/std/bind.go#L28 Prepared statement (it's basically a batch insert) will normalize data to a ClickHouse native format and send it natively to ClickHouse.

It's basically a misalignment of how the various data types are handled. Bind logic is flexible in data handling, but it has a cost of reflection. Native data format serialization is explicit on input data type, because ClickHosue expects strict type.

I will close this issue, but if you have any follow-up questions feel free to ask.

ClickHouse / clickhouse-go