Clickhouse: Single backslash cannot be inserted into String field

[atk91]

Consider this clickhouse-client usage scenario:

:) create table t (a String) ENGINE = Memory;

CREATE TABLE t
(
    a String
) ENGINE = Memory

Ok.

0 rows in set. Elapsed: 0.004 sec.

:) insert into t values ('abc\xyz')

INSERT INTO t VALUES

Ok.

1 rows in set. Elapsed: 0.001 sec.

:) select * from t;

SELECT *
FROM t

┌─a─────┐
│ abc\0 │
└───────┘

1 rows in set. Elapsed: 0.001 sec.

:) insert into t values ('abc\\xyz');

INSERT INTO t VALUES

Ok.

1 rows in set. Elapsed: 0.001 sec.

:) select * from t;

SELECT *
FROM t

┌─a─────┐
│ abc\0 │
└───────┘
┌─a────────┐
│ abc\\xyz │
└──────────┘

2 rows in set. Elapsed: 0.001 sec.

It seems there is no option to insert single backslash into String field.

Notice that when that single backslash is not escaped, the select output is quite strange. Not sure whether this is a bug or feature.

[hatarist]

It inserts a single backslash when you do abc\\xyz - it unescapes the string.
It just displays an escaped representation of the data. If you change the format to - for instance - CSV, it will show the raw data, with a single backslash:

:) SELECT 'hello\\world' FORMAT CSV;

"hello\world"

To confirm the right behaviour, here's an example when a \x0a (which is a line break) is displayed as \n instead, since it's more convenient:

:) SELECT '\x0a' AS x0a;

┌─x0a─┐
│ \n  │
└─────┘

[artpaul]

@hatarist Changing of output format doesn't help. If I execute this sequence
echo "create table t (a String) ENGINE = Memory" | POST 'http://localhost:8123
echo "insert into t values ('abc\xyz')" | POST 'http://localhost:8123/'
echo "select * from t FORMAT CSV" | POST 'http://localhost:8123/'
I'v got just "abc".
But if I do echo 'abc\xyz' I'v got abc\xyz

[hatarist]

Yeah, because \xyz is an (invalid) escape sequence, see String literals.

Notice that when that single backslash is not escaped, the select output is quite strange. Not sure whether this is a bug or feature.
I'v got just "abc".

Regarding that: ClickHouse reads "\xyz" as a "binary representation of the character with a hex code YZ in the ASCII character table". YZ isn't a hex code at all, so ClickHouse converts it to a null byte (\0) instead.

In your case, you should escape the backslashes as you insert it.
But!
Please note that, in your example, your shell and/or the echo program are probably unescaping the string themselves, so you should escape it for the echo too - it should be received by ClickHouse with the escaped backslashes - \\).

$ echo "SELECT 'abc\\\xyz' FORMAT CSV" | clickhouse-client
"abc\xyz"

In this case, clickhouse-client receives this query -SELECT 'abc\\xyz' FORMAT CSV - and handles the data as you intend it to - with just a single backslash.
Feel free to run an INSERT query instead, and/or use libwww-perl's POST command instead of clickhouse-client, the behaviour will be the same.