Clickhouse: Implement '||' operator for string concatenation

[hatarist]

Is it possible to process an expression like 'foo' + 'bar' as concat('foo', 'bar')?
I think it'd be really neat and convenient that way!

It's not possible at the moment:

:) SELECT 'foo' + 'bar';

Received exception from server:
Illegal type String of first argument of function plus

[alexey-milovidov]

It is not obvious that we should implement it.
Some DBMS use operator || for that case.

I have checked queries

SELECT 'a' + 'b';
SELECT 'a' || 'b';

for various DBMS with http://sqlfiddle.com

MySQL:
Returns 0 for both queries. Probably depend on sqlmode.

PostgreSQL:
Works with ||.
Gives meaningful error message for +:
ERROR: operator is not unique: unknown + unknown Hint: Could not choose a best candidate operator. You might need to add explicit type casts. Position: 12
Also not works if you specify types: SELECT CAST('a' AS TEXT) + CAST('b' AS TEXT);

SQLite:
Works for ||.
Returns 0 for +.

BTW, concat has support for variadic number of arguments.

[hatarist]

Thanks, didn't know that!
Would it be hard to implement the || operator support?

[alexey-milovidov]

It is not hard to implement operator || (almost trivial).
I have some doubts... Probably it is worth to do.
Drawback is that it could be easily confused with logical or.
I will do, if I see, that some BI tool uses this operator.

[Tgarifullin]

|| as concatenation operator was defined in ANSI SQL2003 standard.
So maybe it will be better to be closer to ANSI SQL as possible.

[alexey-milovidov]

Thanks! You have convinced me that it is worth to implement.

[alexey-milovidov]

Added to master: https://github.com/yandex/ClickHouse/commit/75a058fbab86e8cd67df84173862318521b17669