Problem

1. concat-ing any null value makes everything null
2. concat-ing any numbers or booleans makes everything crash

We likely want to store null properties as nulls, but concat messes this up.

Changes

Makes concat null-tolerant (sounds the opposite of what it is). Now the following:

concat(null, 'a', 3, toString(4), toString(NULL))

gets automatically converted to

concat('', 'a', toString(3), toString(4), '')

Also adds the piped concat operator:

properties.$screen_width || 'x' || properties.$screen_height

Actual property access inside a ClickHouse SQL string while inside a concat looks kind of dorky, but should be accurate. We can definitely simplify this further if we need to.

-- hogql
concat(properties.$screen_width, 'x', properties.$screen_height)

-- clickhouse
concat(ifNull(toString(replaceRegexpAll(nullIf(nullIf(JSONExtractRaw(events.properties, '$screen_width', ''), 'null'), '^\"|\"$', '')), ''), 'x', ifNull(toString(replaceRegexpAll(nullIf(nullIf(JSONExtractRaw(events.properties, '$screen_height'), ''), 'null'), '^\"|\"$', '')), ''))

Questions

Do we want this? I'd consider it a big UX improvement. Otherwise I need to put the ifNull-s everywhere myself, as I can never be too sure every property is always set.

We can alternatively make properties always take default values, but that has its own issues and is not concluded yet. This fix seems like a happy middle ground bandage.

Alternatives

Alternatively we can implement a new type of non-ANSI compliant string operator, e.g. f'{NULL}-haha-{properties.bla}', but I'd keep the operators standard and be flexible with the types instead.

How did you test this code?

Added tests