Wrong result in left join

[fhh-coder]

When I execute the following SQL statement, I expect the result of field b to be null, but the result is 0.

select l.*, r.* from 
( select 'a' as a ) l
left join 
( select '1' as a, 1 as b ) r 
on l.a = r.a;

My version is 23.8.2.1

[kitaisreal]

By default, ClickHouse does not make non joined columns for LEFT, RIGHT, FULL JOIN Nullable, so it uses default values for non joined rows.
This behaviour can be controlled with setting https://clickhouse.com/docs/en/operations/settings/settings#join_use_nulls.

select l.*, r.* from
( select 'a' as a ) l
left join
( select '1' as a, 1 as b ) r
on l.a = r.a SETTINGS join_use_nulls = 1;

   ┌─a─┬─r.a──┬────b─┐
1. │ a │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │
   └───┴──────┴──────┘

[fhh-coder]

get it!