caseWithExpression (case + toInt16OrNull) unexpected behaviour (All CH versions)

[den-crane]

SELECT
    d,
    toInt16OrNull(d),
    caseWithExpression(d, 'a', 3, toInt16OrZero(d)) AS case_zero,
    caseWithExpression(d, 'a', 3, toInt16OrNull(d)) AS case_null,
    if(d = 'a', 3, toInt16OrZero(d)) AS if_zero,
    if(d = 'a', 3, toInt16OrNull(d)) AS if_null
FROM
(
    SELECT arrayJoin(['', '1', 'a']) AS d
)
ORDER BY
    case_zero ASC,
    d ASC

case_null is wrong for 'a'
┌─d─┬─toInt16OrNull(d)─┬─case_zero─┬─case_null─┬─if_zero─┬─if_null─┐
│   │             ᴺᵁᴸᴸ │         0 │      ᴺᵁᴸᴸ │       0 │    ᴺᵁᴸᴸ │
│ 1 │                1 │         1 │         1 │       1 │       1 │
│ a │             ᴺᵁᴸᴸ │         3 │      ᴺᵁᴸᴸ │       3 │       3 │
└───┴──────────────────┴───────────┴───────────┴─────────┴─────────┘

expected
┌─d─┬─toInt16OrNull(d)─┬─case_zero─┬─case_null─┬─if_zero─┬─if_null─┐
│   │             ᴺᵁᴸᴸ │         0 │      ᴺᵁᴸᴸ │       0 │    ᴺᵁᴸᴸ │
│ 1 │                1 │         1 │         1 │       1 │       1 │
│ a │             ᴺᵁᴸᴸ │         3 │         3 │       3 │       3 │
└───┴──────────────────┴───────────┴───────────┴─────────┴─────────┘

[alexey-milovidov]

Known issue: lack of proper support for Nullable in function transform.

[den-crane]

I remember that I saw something similar but I cannot find the existing issue.

[alexey-milovidov]

caseWithExpression is implemented with function transform
See caseWithExpression.cpp, executeImpl.

The function transform is using "default implementation for nulls", that means if one of the arguments is NULL then the result is also NULL.
See transform.cpp, and
virtual bool useDefaultImplementationForNulls() const { return true; } is by default for IFunction.

It cannot be easily extended for proper support of Nullable, because it is implemented only for a few carefully optimized cases (num -> num, num -> string, string -> num, string -> string) but lacks the generic implementation that can work for all possible types, including Nullable.

Let's note that the behaviour of transform function is very similar to Join (see Join.h, Join.cpp), more specifically - ANY LEFT JOIN. We create a dictionary, then search and substitute the values from this dictionary. We can use Join to generalize the implementation of transform function.

(for this step, ask @amosbird (Telegram: @amosbird) or @4ertus2)

The dictionary inside the function transform is build lazily inside the initialize method, under mutex. It will be worth doing to rewrite it with IFunctionOverloadResolver that will initialize it from constant arguments and return IExecutableFunction.

(for this step, ask @KochetovNicolai)

[alexey-milovidov]

Also it's a good idea to use ColumnUnique to implement generic version of function transform.

We can convert source array elements to ColumnUnique to represent them as a dictionary to offsets in the destination array.
Then we will use this dictionary for lookups and copy elements with IColumn::insertFrom.

If the types of source column and source array to transform or destination array and destination default value are different, we can convert array to getLeastSupertype in advance.

We can also support non-constant destination array easily.

[den-crane]

@antonio2368 check this

[alexey-milovidov]

Fixed in 23.5.