bug: with clickhouse tcp mode, insert a timestamp type value with ns the ck client will hang

[TCeason]

Search before asking

• I had searched in the issues and found no similar issues.

Version

854fc4d

What's Wrong?

with clickhouse tcp mode, insert a timestamp type value with ns the ck client will hang

How to Reproduce?

run databend-query

./target/debug/databend-query --admin-api-address=127.0.0.1:1234 --clickhouse-handler-host=0.0.0.0

use clickhouse-client connect the server:

ClickHouse client version 22.6.3.35 (official build).
Connecting to 192.168.191.66:9000 as user u1.
Connected to databend server version 2021.5.0 revision 54405.

ClickHouse client version is older than ClickHouse server. It may lack support for new features.

databend :) select version();

SELECT version()

Query id: 7c5c0128-b05b-4402-9827-26d03d07668d

Connecting to 192.168.191.66:9000 as user u1.
Connected to databend server version 2021.5.0 revision 54405.

ClickHouse client version is older than ClickHouse server. It may lack support for new features.

┌─version()───────────────────────────────────────────────────────────────────────────────────────┐
│ DatabendQuery v0.7.148-nightly-854fc4d-simd(rust-1.64.0-nightly-2022-08-02T06:44:12.549983597Z) │
└─────────────────────────────────────────────────────────────────────────────────────────────────┘

1 row in set. Elapsed: 0.054 sec. 

databend :) create table t(id int, n datetime);

CREATE TABLE t
(
    `id` int,
    `n` datetime
)

Query id: 9d7424b5-1aa9-49d2-8a61-67e3597c5d85

Ok.

0 rows in set. Elapsed: 0.046 sec. 

databend :) insert into t values(1, '2022-02-02 00:01:02');

INSERT INTO t FORMAT Values

Query id: b4887ff3-24b4-460d-a8d3-39db7022dad9

Ok.

1 row in set. Elapsed: 0.055 sec. 

databend :) insert into t values(1, '2022-02-02 00:01:02.123'); -- this sql will be hang.

INSERT INTO t FORMAT Values

Query id: 764006d6-70dc-4330-811b-593e6c66ffe0

• Note: with mysql and http and clickhouse http is ok.

So I think the hang is the ck client hang with some reason.

Because the datetime type in clickhouse just support second precision

Are you willing to submit PR?

• Yes I am willing to submit a PR!

[TCeason]

I try to insert with select , it can be successed.

databend :) insert into t select 10, to_datetime('2022-02-01 00:00:01.123');

INSERT INTO t SELECT
    10,
    to_datetime('2022-02-01 00:00:01.123')

Query id: 611c7996-bc17-4e8c-bf87-a9f377055d33

Ok.

0 rows in set. Elapsed: 0.038 sec. 

databend :) select * from t where id=10;

SELECT *
FROM t
WHERE id = 10

Query id: df020b78-a810-47ce-94b8-351cadb52362

┌─id─┬───────────────────a─┐
│ 10 │ 2022-02-01 00:00:01 │
└────┴─────────────────────┘
┌─id─┬───────────────────a─┐
│ 10 │ 2022-02-01 00:00:01 │
└────┴─────────────────────┘

2 rows in set. Elapsed: 0.055 sec. 

[ClSlaid]

Reproduced on current main branch feee0dc

SQL I executed:

-- for databend
select version();
show tables; -- I've created table t with create table t(id int, n datetime);
insert into t values(1, '2022-02-02 00:01:02.123'); -- client hang here
-- if you wait long enough the client will raise a SOCKET_TIMEOUT error

-- for clickhouse
select version();
show tables; -- nothing
create table t(id int, n datetime) ENGINE=Memory; -- create successful
insert into t values(1, '2022-02-02 00:01:02.123'); -- clickhouse complain about exception on client

TCP stream tracing by Wireshark:

• Databend:
  blue segments are from the server, reds are from the client
  

• ClickHouse:
  blue segments are from the server, reds are from the client
  

From the picture, there is one more segment from the server at last, in the ClickHouse-ClickHouse scene. After sending byte string "\2 id \5 Int32 \8 DateTime", the ClickHouse server sent "\5" and "\4", while the Databend ClickHouse handler sent "\4" only.

The version of DataBend:

┌─version()──────────────────────────────────────────────────────────────────────────────────────┐
│ DatabendQuery v0.6.66-nightly-feee0dc-simd(rust-1.64.0-nightly-2022-08-04T07:38:27.807046692Z) │
└────────────────────────────────────────────────────────────────────────────────────────────────┘

The version of ClickHouse server:

┌─version()──┐
│ 21.12.3.32 │
└────────────┘
docker pulled from https://hub.docker.com/r/yandex/clickhouse-server

The version of ClickHouse client:

ClickHouse client version 22.7.1.2484 (official build).

[ClSlaid]

The real problem seems larger than this issue described. When executing malformed insert into SQLs on ClickHouse client like:

insert into t values (1, '2020');  -- insert string
insert into t values (1); -- insert int only
-- and even incomplete SQLs
-- unclosed bracket
insert into t values (1
-- missing the corresponding comma
insert into t values (1, '2020);

will hang the ClickHouse client. Since these hangings all come with nothing (equal or above info level) logged, I suppose they obey the same cause.

[sundy-li]

insert into t values(1, '2022-02-02 00:01:02.123'); -- clickhouse complain about exception on client

DateTime/Timestamp is mapping to ClickHouse 's DateTime.

I think it's because we forgot to write the precision/tz into clickhouse's client.

[sundy-li]

ClickHouse client version 21.12.1.1.

This version could throw errors.

databend :) insert into t values(1, '2022-02-02 00:01:02.123');

INSERT INTO t FORMAT Values

Query id: 17c66985-b662-4bb8-b9f2-477a442049de

Exception on client:
Code: 6. DB::Exception: Cannot parse string '2022-02-02 00:01:02.123' as DateTime: syntax error at position 19 (parsed just '2022-02-02 00:01:02'): while executing 'FUNCTION if(isNull(_dummy_0) : 3, defaultValueOfTypeName('DateTime') :: 2, _CAST(_dummy_0, 'DateTime') :: 4) -> if(isNull(_dummy_0), defaultValueOfTypeName('DateTime'), _CAST(_dummy_0, 'DateTime')) DateTime : 1': While executing ValuesBlockInputFormat: data for INSERT was parsed from query. (CANNOT_PARSE_TEXT)