Clickhouse: Can't escape strings with \', \" or \\ in Array

[goranc]

When I try to insert record which have escaped char for single quote, double quote or backslash in some Array field, I got an error from database.

[ludv1x]

What input FORMAT do you use ?

Could you also provide error message and an example reproducing the problem?

[goranc]

I'm using VALUES format, and I can insert single quote with escape like \' but string is written in database with that escape, not like single quote (so it is selected back with backslash and single quote).

Just to answer after deeper investigation:

This is intentional by design !

When I select data with different Format (eg. JSONEachRow) the strings are without escape chars.
This is intentional to provide correct formatting for CSV, TabSeparated and other structures.

You can close this issue 👍

[ludv1x]

It actually writes to DB single character. To prove this I print length(str) in addition to str:

:) drop table if exists test.quoting;

DROP TABLE IF EXISTS test.quoting

Ok.

0 rows in set. Elapsed: 0.000 sec. 

:) CREATE TABLE test.quoting (str String) ENGINE = Memory;

CREATE TABLE test.quoting
(
    str String
) ENGINE = Memory

Ok.

0 rows in set. Elapsed: 0.007 sec. 

:) insert into test.quoting VALUES ('\'') ('\"') ('\\') ('"') ('0');

INSERT INTO test.quoting VALUES

Ok.

5 rows in set. Elapsed: 0.001 sec. 

:) select str, length(str) from test.quoting;

SELECT 
    str, 
    length(str)
FROM test.quoting 

┌─str─┬─length(str)─┐
│ \'  │           1 │
│ "   │           1 │
│ \\  │           1 │
│ "   │           1 │
│ 0   │           1 │
└─────┴─────────────┘

5 rows in set. Elapsed: 0.001 sec. 

:) select str, length(str) from test.quoting FORMAT TabSeparatedRaw;

SELECT 
    str, 
    length(str)
FROM test.quoting 
FORMAT TabSeparatedRaw

'   1
"   1
\   1
"   1
0   1

5 rows in set. Elapsed: 0.001 sec. 

:)

Many output formats add escape symbols to the result.
Default formats TabSeparated and Pretty do that.

To avoid escaping you can use one of "raw" formats, for example TabSeparatedRaw as in my example.