Default values are ignored in AppendStruct

[leonidboykov]

Issue description

Altering table to add a new column causes an error

[AppendStruct]: missing destination name "<column_name>" in <struct_name>"

even if I've specified a default value for the column.

Perhaps checking that column has a default value here might solve the issue.

https://github.com/ClickHouse/clickhouse-go/blob/ee79713daf7a10ba3b363797f74b0909be463b49/struct_map.go#L69-L74

Configuration

OS: unrelated

Interface: E.g. native

Driver version: v2.0.12

Go version: unrelated

ClickHouse Server version: 20.10.6.27

[gingerwizard]

Thanks @leonidboykov i'll need to dig into this as I'm not certain we get the default value back over the native protocol (@genzgd do you know?) - the initial INSERT gets the columns back and this is in the information which is matched against the struct.

You might want to consider using JSON support - see https://github.com/ClickHouse/clickhouse-go/pull/590

[gingerwizard]

@leonidboykov so this information isn't available or returned by the native protocol by default. We'll therefore need to issue a precursor query to DESCRIBE the table, prior to any INSERT. This has some merit - allowing columns to be committed on insert if they have default values would be a nice feature - I'm just a little hesitant about the additional call.

[leonidboykov]

@gingerwizard what about MustSend method which disables checks? Or some "unsafe" flags for driver settings?

[dovefi]

Is there any new progress

[dovefi]

i got the same problem here. i have upgrade clickhouse table first

ALTER TABLE [db_name.]table ADD COLUMN col_name DEFAULT emptyArrayString();

and the code have not add this column to struct yet. when i AppendStruct(), it return error

clickhouse [AppendStruct]: missing destination name \"xxxxx\" in *xxxxx\

[dovefi]

i got the same problem here. i have upgrade clickhouse table first

ALTER TABLE [db_name.]table ADD COLUMN col_name DEFAULT emptyArrayString();

and the code have not add this column to struct yet. when i AppendStruct(), it return error

clickhouse [AppendStruct]: missing destination name \"xxxxx\" in *xxxxx\

actually, we can't upgrade both table and production code together

[khanakia]

I am having the same issue. I have a column with default value but clickhouse seems to be ignoring this

CREATE TABLE `students` (
  `task_id` String,
  `id` Int64,
  `name` String DEFAULT ''
) ENGINE = MergeTree

type Student struct {
  TaskID string `ch:"task_id"`
  ID     int64  `ch:"id"`
}

batch, err := conn.PrepareBatch(context.Background(), "INSERT INTO students")
if err != nil {
  return err
}

for i := 0; i < 2; i++ {
  err := batch.AppendStruct(&Student{
	  ID:     1,
	  TaskID: "task1",
  })
  if err != nil {
	  fmt.Println(err)
	  return err
  }
}

generated sql

INSERT into students values ('task1', 22)

Error:

clickhouse [AppendStruct]: missing destination name "name" in *Student*

if I add the Name to struct then it generates a query like this and works fine

INSERT into students ("task_id", "id", "name") values ('task1', 22, "jane")

[jkaflik]

@khanakia you can work around this behaviour by specifying columns in INSERT clause

[khanakia]

Yes, that's true but there are hundreds of columns I have.

I thought Clickhouse would handle it automatically.

[jkaflik]

ClickHouse native format does not support default values when inserting. This is the format clickhouse-go transforms Go types into. There is an issue in ClickHouse repository: https://github.com/ClickHouse/ClickHouse/issues/58662

This is sort of enhancement that can be work around by the library, as mentioned by @gingerwizard :

We'll therefore need to issue a precursor query to DESCRIBE the table, prior to any INSERT. This has some merit - allowing columns to be committed on insert if they have default values would be a nice feature - I'm just a little hesitant about the additional call.

We are not prioritizing it. Perhaps you want to contribute with your proposal.