Possible issue with JSON fields preventing altering tables. #38886
Description
JSON field added via alteration does not infer types - throws LOGICAL_ERROR. This prevents records to be written after the alteration.
I would expect that I can still insert data into the pre-alteration and post-alteration JSON fields regardless of their schema.
Clickhouse version
SELECT version()
Query id: 061064c4-5cb3-4833-8acb-8ea79709ca93
┌─version()──┐
│ 22.7.1.163 │
└────────────┘
1 row in set. Elapsed: 0.001 sec.
How to reproduce
Create the db:
create database json_tests;
Enable experimental object type:
SET allow_experimental_object_type = 1;
Create the first table:
create table if not exists json_tests.testA
(
ts DateTime64(0, 'UTC'),
id UUID,
data JSON
)
engine = ReplacingMergeTree()
partition by toYYYYMM(ts)
order by (ts, id)
ttl toDateTime(ts) + interval 5 day;
Insert some data:
insert into json_tests.testA format JSONEachRow {"ts": 1657036800, "id": "a2bd6255-6474-42c9-8137-8f83af49754b", "data": {"a": "1", "b": "2", "c": [1,2], "d": {"a": 1}}};
So far so good, now alter:
alter table json_tests.testA
add column metrics JSON
after data;
Insert some data:
insert into json_tests.test format JSONEachRow {"ts": 1657036800, "id": "a2bd6255-6474-42c9-8137-8f83af49754b", "data": {"a": "1", "b": "2", "c": [1,2], "d": {"a": 1}}, "metrics": {"x": 1, "y": {"test": "this"}}};
You will see this error:
Received exception from server (version 22.7.1):
Code: 49. DB::Exception: Received from localhost:9000. DB::Exception: Least common type for object can be deduced only from tuples, but Object('json') given. (LOGICAL_ERROR)
Now, create another table but this time add all the fields upfront, without altering:
create table if not exists json_tests.testB
(
ts DateTime64(0, 'UTC'),
id UUID,
data JSON,
metrics JSON
)
engine = ReplacingMergeTree()
partition by toYYYYMM(ts)
order by (ts, id)
ttl toDateTime(ts) + interval 5 day;
Insert some data:
insert into json_tests.testB format JSONEachRow {"ts": 1657036800, "id": "a2bd6255-6474-42c9-8137-8f83af49754b", "data": {"a": "1", "b": "2", "c": [1,2], "d": {"a": 1}}, "metrics": {"x": 1, "y": {"test": "this"}}};
This is as expected:
Ok.
1 row in set. Elapsed: 0.006 sec.
I would assume that both methods should yield a success and not a LOGICAL_ERROR.