Clickhouse: Default values don’t work when using JSONEachRow format w/o specifying column names

[Har01d]

The code:

DROP TABLE IF EXISTS test_table;

CREATE TABLE test_table (
  a String,
  b Date,
  c DEFAULT sipHash64(a),
  d MATERIALIZED sipHash64(a)
) ENGINE = MergeTree(b, a, 8192);

INSERT INTO test_table (a, b) values ('1', '2016-01-01');

INSERT INTO test_table (a, b) FORMAT JSONEachRow {"a":"2","b":"2016-01-02"}

INSERT INTO test_table values ('3', '2016-01-03'); -- throws an error

INSERT INTO test_table FORMAT JSONEachRow {"a":"4","b":"2016-01-04"} -- doesn’t throw an error

SELECT *, d FROM test_table ORDER BY a;

The result:

┌─a─┬──────────b─┬────────────────────c─┬────────────────────d─┐
│ 1 │ 2016-01-01 │  5003827105613308882 │  5003827105613308882 │
│ 2 │ 2016-01-02 │ 11449545338359147399 │ 11449545338359147399 │
│ 4 │ 2016-01-04 │                    0 │  3672830208859661989 │
└───┴────────────┴──────────────────────┴──────────────────────┘

The expected result:

┌─a─┬──────────b─┬────────────────────c─┬────────────────────d─┐
│ 1 │ 2016-01-01 │  5003827105613308882 │  5003827105613308882 │
│ 2 │ 2016-01-02 │ 11449545338359147399 │ 11449545338359147399 │
│ 4 │ 2016-01-04 │  3672830208859661989 │  3672830208859661989 │
└───┴────────────┴──────────────────────┴──────────────────────┘

[alexey-milovidov]

It is known (documented) issue.
https://clickhouse.yandex/reference_en.html#JSONEachRow

N.B. when using JSONEachRow format, complex default values are not supported, so when omitting a column its value will be zeros or empty string depending on its type.

The reason is that complex default values need expression evaluation, but data formats currently are independent of expressions interpreters.

But we already have many requests to implement this functionality, so we have to do it.

[filimonov]

+1 to fix

[kolsys]

+1 to fix

[myy2829]

+1 to fix

[4ertus2]

Works in master. It needs insert_sample_with_metadata to be set.

DROP TABLE IF EXISTS test_table;

CREATE TABLE test_table (
  a String,
  b Date,
  c DEFAULT sipHash64(a),
  d MATERIALIZED sipHash64(a)
) ENGINE = MergeTree(b, a, 8192);

SET insert_sample_with_metadata=1;
INSERT INTO test_table FORMAT JSONEachRow {"a":"4","b":"2016-01-04"}
SELECT a, c, d, sipHash64(a) FROM test_table ORDER BY a;

4       5406800756778728304     5406800756778728304     5406800756778728304

P.S. There's a mistake in sample. 3672830208859661989 == sipHash64('3'), not sipHash64('4').