A bug inserting a value

[Podoproas]

When I'm trying to insert some values - there is an error.

On Managed Service for ClickHouse on Yandex Cloud I have a DB called "testing_new_structure"
CREATE DATABASE testing_new_structure;

I've made a table using this request:
CREATE TABLE IF NOT EXISTS testing_new_structure.devices (timestamp datetime(), name String, device_id UInt8) ENGINE = MergeTree PARTITION BY toYYYYMM(timestamp) ORDER BY (timestamp);

I'm trying to execute:
INSERT INTO testing_new_structure.devices VALUES ('2021-10-12', 'aaaaa', 4);
and getting such a response:
message: Code: 62, e.displayText() = DB::Exception: Cannot parse expression of type String here: 4) (version 21.8.8.29 (official build))

I've figured out that the error is caused because of 'aaaaa'
If I change it on any other string that contains more or less then 5 characters (error caused only with ASCII characters) it will succeed.

If we try to procede:
INSERT INTO testing_new_structure.devices VALUES ('2021-10-12','aaaaa', 4);
(without space character before string starts) we will SUCCEED!

SO:
INSERT INTO testing_new_structure.devices VALUES ('2021-10-12', 'a', 4); - success
INSERT INTO testing_new_structure.devices VALUES ('2021-10-12', 'aa', 4); - success
INSERT INTO testing_new_structure.devices VALUES ('2021-10-12', 'aaa', 4); - success
INSERT INTO testing_new_structure.devices VALUES ('2021-10-12', 'aaaa', 4); - success
INSERT INTO testing_new_structure.devices VALUES ('2021-10-12', 'aaaaa', 4); - error
INSERT INTO testing_new_structure.devices VALUES ('2021-10-12','aaaaa', 4); - success
INSERT INTO testing_new_structure.devices VALUES ('2021-10-12', 'aaaaaa', 4); - success

I do not consider that table is not supposed to store values at exactly 5 ASCII characters. Something wrong with parsing the query.

Does it reproduce on recent release?
I tried to reproduce it on localhost with the latest release:

DROP DATABASE testing_new_structure

Query id: 1211e237-086a-4df2-a92f-5f897afc97ba

Ok.

0 rows in set. Elapsed: 0.005 sec.

CREATE DATABASE testing_new_structure

CREATE DATABASE testing_new_structure

Query id: 4b60bfee-3765-45a3-8ea1-c4851e75b3b8

Ok.

0 rows in set. Elapsed: 0.013 sec.

CREATE TABLE IF NOT EXISTS testing_new_structure.devices (timestamp datetime(),  name String,  device_id UInt8) ENGINE = MergeTree PARTITION BY toYYYYMM(timestamp) ORDER BY (timestamp);

CREATE TABLE IF NOT EXISTS testing_new_structure.devices
(
    `timestamp` datetime,
    `name` String,
    `device_id` UInt8
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(timestamp)
ORDER BY timestamp

Query id: 09ce7365-50d5-47b1-85a5-6265b722b4e8

Ok.

0 rows in set. Elapsed: 0.016 sec.

INSERT INTO testing_new_structure.devices VALUES ('2021-10-12', 'aaaaa', 4)

INSERT INTO testing_new_structure.devices VALUES

Query id: 0cf2e2f8-aa39-4b4c-a8f5-6b991019786b

Exception on client:
Code: 62. DB::Exception: Cannot parse expression of type String here: 4): While executing ValuesBlockInputFormat: data for INSERT was parsed from query. (SYNTAX_ERROR), Stack trace (when copying this message, always include the lines below):

0. DB::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int, bool) @ 0x944bdda in /usr/bin/clickhouse
1. DB::ValuesBlockInputFormat::parseExpression(DB::IColumn&, unsigned long) @ 0x11b0f3ea in /usr/bin/clickhouse
2. DB::ValuesBlockInputFormat::readRow(std::__1::vector<COW<DB::IColumn>::mutable_ptr<DB::IColumn>, std::__1::allocator<COW<DB::IColumn>::mutable_ptr<DB::IColumn> > >&, unsigned long) @ 0x11b0c53b in /usr/bin/clickhouse
3. DB::ValuesBlockInputFormat::generate() @ 0x11b0b87c in /usr/bin/clickhouse
4. DB::ISource::tryGenerate() @ 0x119a2d55 in /usr/bin/clickhouse
5. DB::ISource::work() @ 0x119a2a5a in /usr/bin/clickhouse
6. ? @ 0x119dd93d in /usr/bin/clickhouse
7. DB::PipelineExecutor::executeStepImpl(unsigned long, unsigned long, std::__1::atomic<bool>*) @ 0x119da111 in /usr/bin/clickhouse
8. DB::PipelineExecutor::executeImpl(unsigned long) @ 0x119d816f in /usr/bin/clickhouse
9. DB::PipelineExecutor::execute(unsigned long) @ 0x119d7f39 in /usr/bin/clickhouse
10. ? @ 0x119e4ebf in /usr/bin/clickhouse
11. ThreadPoolImpl<std::__1::thread>::worker(std::__1::__list_iterator<std::__1::thread, void*>) @ 0x948ce7f in /usr/bin/clickhouse
12. ? @ 0x9490763 in /usr/bin/clickhouse
13. start_thread @ 0x76db in /lib/x86_64-linux-gnu/libpthread-2.27.so
14. /build/glibc-OTsEL5/glibc-2.27/misc/../sysdeps/unix/sysv/linux/x86_64/clone.S:97: clone @ 0x12188f in /usr/lib/debug/lib/x86_64-linux-gnu/libc-2.27.so


Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 21.10.2 revision 54449.

INSERT INTO testing_new_structure.devices VALUES ('2021-10-12', 'a', 4)

INSERT INTO testing_new_structure.devices VALUES

Query id: 83ae6c0f-3144-4bd1-84ae-ac8ae1e8fb25

Ok.

1 rows in set. Elapsed: 0.016 sec.

It reproduced.

Expected behavior

Inserted row

Error message and/or stacktrace

message: Code: 62, e.displayText() = DB::Exception: Cannot parse expression of type String here: 4) (version 21.8.8.29 (official build))

[den-crane]

timestamp datetime(),
'2021-10-12'

But datetime <> date, should be '2021-10-12 00:00:00'

INSERT INTO devices VALUES ('2021-10-12 00:00:00', 'aaaaa', 4);

INSERT INTO devices VALUES

Query id: 9d819672-dbc1-48be-aede-9b02ab9298b2

Ok.

1 rows in set. Elapsed: 0.001 sec.

CREATE TABLE IF NOT EXISTS devices
(
    `timestamp` date,
    `name` String,
    `device_id` UInt8
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(timestamp)
ORDER BY timestamp


INSERT INTO devices VALUES ('2021-10-12', 'aaaaa', 4);

INSERT INTO devices VALUES

Query id: 5f351b9f-eadb-4a7a-b7f5-c627155531bd

Ok.

1 rows in set. Elapsed: 0.001 sec.

[tavplubix]

Looks like "optimistic path" in readDateTimeTextImpl is too optimistic.

Workaround: set date_time_input_format='best_effort' (or specify datetime correctly)