PrepareBatch fail when a table name such as `db`.`t`

[twowind]

Observed

When the insert statement contains a database name or a table name wrapped in ``, the table name is not recognized correctly.

Code example

package code

_, err := clickhouse.PrepareBatch(ctx, "INSERT INTO `db`.`t`")

Error log

clickhouse [execute]:: 404 code: Code: 60. DB::Exception: Table `clickhouse-go-issues-1b62bb68-9ee-1718446267685`.db does not exist. (UNKNOWN_TABLE)

Details

Environment

• [ ] clickhouse-go version: 2.25.0
• [ ] Interface: ClickHouse API / database/sql compatible driver
• [ ] Go version: go1.21.11
• [ ] Operating system: wsl2/ubuntu
• [ ] ClickHouse version: latest
• [ ] Is it a ClickHouse Cloud? NO
• [ ] CREATE TABLE statements for tables involved: CREATE TABLE IF NOT EXISTS `db`.`t` (`test1` String) Engine = Memory

[ramzes642]

Added tests, my fix also helps with your issue

[twowind]

Added tests, my fix also helps with your issue

Thank you for the quick fix! It works fine with the ClickHouse API interface, but it still doesn't work with database/sql.

conn := clickhouse.OpenDB(options);
_, err := conn.Prepare(tc.input); // err != nil

[jkaflik]

@twowind

Could you verify on a latest version? Here is a code snippet that I wrote to reproduce your issue. It uses a helper function to initiate database/sql connection:

package issues

import (
    "fmt"
    "testing"

    clickhouse_tests "github.com/ClickHouse/clickhouse-go/v2/tests"
    "github.com/stretchr/testify/require"
)

func Test1329(t *testing.T) {
    testEnv, err := clickhouse_tests.GetTestEnvironment("issues")
    require.NoError(t, err)
    conn, err := clickhouse_tests.TestDatabaseSQLClientWithDefaultSettings(testEnv)
    require.NoError(t, err)

    _, err = conn.Exec(`CREATE TABLE test_1329 (Col String) Engine Memory()`)
    require.NoError(t, err)
    t.Cleanup(func() {
        _, _ = conn.Exec("DROP TABLE test_1329")
    })

    scope, err := conn.Begin()

    batch, err := scope.Prepare(fmt.Sprintf("INSERT INTO `%s`.`test_1329`", testEnv.Database))
    require.NoError(t, err)
    _, err = batch.Exec(
        "str",
    )
    require.NoError(t, err)
    require.NoError(t, scope.Commit())
}

[twowind]

@jkaflik

I'm sorry, my previous description might have been a bit unclear. The latest library has fixed the Prepare for non-http protocols, but there are still issues with http protocol.

Below is my test code:

env, err := clickhouse_tests.GetTestEnvironment("issues")
require.NoError(t, err)
options := &clickhouse.Options{
    Addr: []string{fmt.Sprintf("%s:%d", env.Host, env.HttpPort)},
    Auth: clickhouse.Auth{
        Database: env.Database,
        Username: env.Username,
        Password: env.Password,
    },
    Protocol: clickhouse.HTTP,
}
conn := clickhouse.OpenDB(options)
require.NoError(t, conn.Ping())

d := "CREATE DATABASE db ENGINE = Memory"
_, err = conn.Exec(d)
require.NoError(t, err)

ddl := "CREATE TABLE IF NOT EXISTS `db`.`t` (`test1` String) Engine = Memory"
_, err = conn.Exec(ddl)
require.NoError(t, err)

_, err = conn.Prepare("INSERT INTO `db`.`t`")
require.NoError(t, err)