expected ... arguments, got ... when using db.Prepare()

[AndreKR]

Observed

The program below creates a table and tries to insert two rows into it. The first row is inserted using db.Exec() the second one using stmt, err := db.Prepare() + stmt.Exec().

The insert using db.Exec() works, the insert using stmt.Exec() returns an error: expected 3 arguments, got 2

Expected behaviour

I'd expect both inserts to work identically.

Code example

package main

import (
	"database/sql"
	"log"

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

func main() {
	dsn := "https://clickhouse:443/test?secure=true&username=user&password=pass&debug=true"

	db, err := sql.Open("clickhouse", dsn)
	if err != nil {
		log.Fatalf("failed to connect: %v", err)
	}

	_, err = db.Exec(`DROP TABLE example`)
	if err != nil {
		log.Fatalf("failed to drop table: %v", err)
	}

	_, err = db.Exec(`CREATE TABLE example (
		  a String,
		  b Nullable(String),
		  c Nullable(String)
		) ENGINE = MergeTree PRIMARY KEY a
	`)
	if err != nil {
		log.Fatalf("failed to create table: %v", err)
	}


	// This works
	_, err = db.Exec(`INSERT INTO example(a, c) VALUES('foo','bar')`)
	if err != nil {
		log.Fatalf("exec failed: %w", err)
	}

	// This does not
	stmt, err := db.Prepare(`INSERT INTO example(a, c) VALUES(?,?)`)
	if err != nil {
		log.Fatalf("prepare failed: %w", err)
	}
	_, err = stmt.Exec([]interface{} {"foo", "bar"}...)
	if err != nil {
		log.Fatalf("execution failed: %w", err)
	}
}

Error log

[clickhouse-std][conn=0][clickhouse:443] [batch][exec] append error: clickhouse [Append]:  clickhouse: expected 3 arguments, got 2
2025/01/31 03:54:10 execution failed: %!w(*proto.BlockError=&{Append 0xc000030250 })
exit status 1

Details

Environment

• clickhouse-go version: v2.30.1
• Interface: database/sql compatible driver
• Go version: go version go1.23.5 windows/amd64
• Operating system: Windows 10 Pro
• ClickHouse version: 24.12.3.47
• Is it a ClickHouse Cloud? No.

[SpencerTorres]

Hello! Thanks for submitting an issue.

For Prepare you don't need to provide the ?, just providing the column names is enough. The values are then provided per column in Exec. See the examples/std folder in this repo for more examples. You may also find that the native API is simpler to use than the stdlib sql interface (examples/clickhouse_api).

These lines have a good example (src):

batch, err := tx.PrepareContext(ctx, "INSERT INTO go_json_example (product)")
if err != nil {
	return err
}


insertProductString := "{\"id\":1234,\"name\":\"Book\",\"tags\":[\"library\",\"fiction\"]," +
	"\"pricing\":{\"price\":750,\"currency\":\"usd\"},\"metadata\":{\"page_count\":852,\"region\":\"us\"}," +
	"\"created_at\":\"2024-12-19T11:20:04.146Z\"}"


if _, err = batch.ExecContext(ctx, insertProductString); err != nil {
	return err
}

These are the Context variations of those functions, but it should behave the same. Notice how the INSERT only provides the columns, and then the ExecContext provides the value.

Let me know if you're still having trouble, otherwise feel free to close this issue. Thanks!

[AndreKR]

I'm not sure if I did it correctly because the example only has one column while I have multiple.

I adjusted my code like this:

	stmt, err := db.Prepare(`INSERT INTO example(a, c)`)
	if err != nil {
		log.Fatalf("prepare failed: %w", err)
	}
	_, err = stmt.Exec("foo", "bar")
	if err != nil {
		log.Fatalf("execution failed: %w", err)
	}

To make it even closer to the example I also tried using a context and a transaction:

	ctx := context.Background()
	
	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		log.Fatalf("begin failed: %w", err)
	}
	
	batch, err := tx.PrepareContext(ctx, `INSERT INTO example(a, c)`)
	if err != nil {
		log.Fatalf("prepare failed: %w", err)
	}
	
	_, err = batch.ExecContext(ctx, "foo", "bar")
	if err != nil {
		log.Fatalf("execution failed: %w", err)
	}
	
	err = tx.Commit()
	if err != nil {
		log.Fatalf("commit failed: %w", err)
	}

But in both cases I still get the same error:

append error: clickhouse [Append]:  clickhouse: expected 3 arguments, got 2

[SpencerTorres]

I cannot reproduce this error, I'm running the tests from the examples folder. I modified it to match your schema and insert into (a, c) and it is working fine. I also tried running your original code, and it works successfully with the removal of VALUES(?,?)

Is this the actual code you're running or is this an anonymized version if it? Are you able to run the module's tests locally to confirm everything is functioning as expected? There must be another factor here causing this code to fail.

Let me know if there are any more details at all. Thanks!

[SpencerTorres]

EDIT: deleted your comment since it had sensitive credentials. The code portion was helpful though so thank you

Okay using your example I was able to reproduce the error, perhaps it's something with how the sql driver is created? I have my own ClickHouse Cloud instance to try so I'll use that instead.

Definitely delete the credentials you provided there 👀

I'm stepping through the debugger now to see what's going on. My first guess is that it's expecting the full list of columns from the table instead of just the ones specified in the INSERT

[SpencerTorres]

Okay I found the cause to all our problems:

Why

The SQL batch (specifically for HTTP connections using std sql interface) will try to extract your column names (a, c) via regex in this function.

Because there is no space between your table name and columns example(a, c), it is unable to find the column names. To identify the correct column types, it will run DESC TABLE example to fetch ALL column names (a, b, c). Since it was unable to extract your specific column names, it falls back on the full column list from DESC TABLE as seen here.

While I was using the same std sql interface, I didn't catch this issue in my code since my tests were using the Native TCP protocol instead of HTTP. This issue will occur on any ClickHouse deployment type, not just cloud or open source.

Immediate solutions

Either will work:

• Add a space between example and (a, c)
• Use the Native TCP protocol instead of HTTP (change your port number to 9440 or 9000, and URL format to clickhouse:// or tcp://)

Further actions

• We could mark this as a bug and fix the regex, but regexes can be a pain to fight with so I'm not too sure I want to do that. Some table syntax in ClickHouse have parenthesis, and ultimately there will be SQL statements that a regex isn't going to catch.
• We could improve the error reporting, perhaps a verbose debug level should print a warning saying that the statement is failing to parse columns. I can't imagine a compiled program would want to simply fall back on DESC TABLE, that's quite unpredictable.
• We could add an override in opts driver.PrepareBatchOptions, where the user can provide exact column names to be used. This in pair with the error message would solve the issue of fighting with the regex for complicated syntax.

Perhaps we should do all of the above?

We can keep this open for further discussion. I appreciate your patience in troubleshooting this issue. Let me know if you have any more issues or thoughts on this topic. Thanks!

[AndreKR]

Thanks for the analysis.

I was observing this behavior in Telegraf, I will submit an issue and a PR there to insert the space here.

We could improve the error reporting, perhaps a verbose debug level should print a warning saying that the statement is failing to parse columns. I can't imagine a compiled program would want to simply fall back on DESC TABLE, that's quite unpredictable.

I was about to suggest that the Prepare() could simply fail (return an error) if the parsing fails, but I'm assuming the fallback is there to support INSERT INTO my_table VALUES ..., which is somewhat common in dump files. I guess fixing the regex does make sense.

We could add an override in opts driver.PrepareBatchOptions, where the user can provide exact column names to be used.

Can this be used with database/sql? What would that look like?

The native API of clickhouse-go isn't an option for me because I'm using it through Telegraf which uses database/sql because it supports many different database drivers.

The native protocol (port 9000) isn't an option for me either because my ClickHouse is behind a reverse proxy/TLS offloader that only supports HTTP.

[SpencerTorres]

Can [an override in opts driver.PrepareBatchOptions] be used with database/sql?

It looks like it just uses an empty struct for the options right now: https://github.com/ClickHouse/clickhouse-go/blob/3e6eea33444efc84bb07e1e14f95f773dd12092d/clickhouse_std.go#L357

But we could technically pass these options through the context. It could be added to these options, or perhaps in a different context key.

What would that look like?

This entire test file shows context for std sql interface queries: https://github.com/ClickHouse/clickhouse-go/blob/3e6eea33444efc84bb07e1e14f95f773dd12092d/examples/std/context.go#L67-L72

Seems reasonable to me to add an override here