Cannot load to ephemeral column from Avro

[ltrk2]

Describe what's wrong

When loading from a file of Avro format into a column of ephemeral type, the values don't seem to get populated.

Does it reproduce on recent release?

It's reproducible in 23.7.4.5.

How to reproduce

https://fiddle.clickhouse.com/2b6ed6af-12d6-4829-b376-ed40e9564043

Expected behavior

Performing the same thing using RowBinary format or inserting directly into the table results in the expected behaviour.

https://fiddle.clickhouse.com/ac7ef343-4a08-4dbc-afc1-b211c54bdda5

[cangyin]

That's exactly expected, as per documented:

Ephemeral column. Columns of this type are not stored in the table and it is not possible to SELECT from them. The only purpose of ephemeral columns is to build default value expressions of other columns from them.

[cangyin]

set input_format_avro_allow_missing_fields=1;

you allowed the missing field id_default to be inserted as default value (0), that's why there are zeros under id_default.

[ltrk2]

@cangyin would you be able to pinpoint what the difference between Avro and RowBinary is in this context and what makes one approach work and the other fail?

[cangyin]

please read the doc about

• RowBinary format
• input_format_avro_allow_missing_fields setting

set input_format_avro_allow_missing_fields=1;

you allowed the missing field id_default to be inserted as default value (0), that's why there are zeros under id_default.

explanation above is clear.

[Avogar]

From documentation about epheeral columns:

An insert without explicitly specified columns will skip columns of this typ

So, just insert into idsephemeral FROM INFILE 'x' format Avro; will insert only ordinary columns and skip ephemeral columns, so you should write insert into (id) idsephemeral FROM INFILE 'x' format Avro; or insert into idsephemeral (id, id_default) FROM INFILE 'x' format Avro;. But it also won't work because of a bug: we use StorageFile to read data from file and it doesn't support ephemeral columns, and when we propogate columns description from the insertion table to the StorageFile, we don't change Ephemeral columns to ordinary, I fixed it.

[ltrk2]

@Avogar thanks for fixing it!