Wrong function results on typed Nullable. Inconsistent with results on Nullable(Nothing) #9253
Description
Describe the bug or unexpected behaviour
Clickhouse version 19.14.7.15
set join_use_nulls = 1
sql
SELECT
k,
b_num,
isNull(b_num),
if(b_num = 0, -1, b_num),
if(isNull(b_num), -1, b_num),
b_str,
isNull(b_str),
empty(b_str),
if(isNull(b_str), 'Others', b_str),
if(empty(b_str), 'Others', b_str)
FROM
(
SELECT
number AS k,
toInt8(number) AS a_num,
toString(number) AS a_str
FROM system.numbers
LIMIT 10
)
LEFT JOIN
(
SELECT
number AS k,
toInt8(number) AS b_num,
toString(number) AS b_str
FROM system.numbers
LIMIT 5, 10
) USING (k)
ORDER BY k ASC
Returned columns
if(equals(b_num, 0), -1, b_num)
and
if(empty(b_str), 'Others', b_str)
are not correct.
Returned result detail
┌─k─┬─b_num─┬─isNull(b_num)─┬─if(equals(b_num, 0), -1, b_num)─┬─if(isNull(b_num), -1, b_num)─┬─b_str─┬─isNull(b_str)─┬─empty(b_str)─┬─if(isNull(b_str), 'Others', b_str)─┬─if(empty(b_str), 'Others', b_str)─┐
│ 0 │ ᴺᵁᴸᴸ │ 1 │ -1 │ -1 │ ᴺᵁᴸᴸ │ 1 │ ᴺᵁᴸᴸ │ Others │ Others │
│ 1 │ ᴺᵁᴸᴸ │ 1 │ -1 │ -1 │ ᴺᵁᴸᴸ │ 1 │ ᴺᵁᴸᴸ │ Others │ Others │
│ 2 │ ᴺᵁᴸᴸ │ 1 │ -1 │ -1 │ ᴺᵁᴸᴸ │ 1 │ ᴺᵁᴸᴸ │ Others │ Others │
│ 3 │ ᴺᵁᴸᴸ │ 1 │ -1 │ -1 │ ᴺᵁᴸᴸ │ 1 │ ᴺᵁᴸᴸ │ Others │ Others │
│ 4 │ ᴺᵁᴸᴸ │ 1 │ -1 │ -1 │ ᴺᵁᴸᴸ │ 1 │ ᴺᵁᴸᴸ │ Others │ Others │
│ 5 │ 5 │ 0 │ 5 │ 5 │ 5 │ 0 │ 0 │ 5 │ 5 │
│ 6 │ 6 │ 0 │ 6 │ 6 │ 6 │ 0 │ 0 │ 6 │ 6 │
│ 7 │ 7 │ 0 │ 7 │ 7 │ 7 │ 0 │ 0 │ 7 │ 7 │
│ 8 │ 8 │ 0 │ 8 │ 8 │ 8 │ 0 │ 0 │ 8 │ 8 │
│ 9 │ 9 │ 0 │ 9 │ 9 │ 9 │ 0 │ 0 │ 9 │ 9 │
└───┴───────┴───────────────┴─────────────────────────────────┴──────────────────────────────┴───────┴───────────────┴──────────────┴────────────────────────────────────┴───────────────────────────────────┘