join_use_nulls on LowCardinality type does not work

[mcgrawia]

Describe the bug
When I use the set join_use_nulls = 1; setting, LowCardinality columns still show up with the default "" value.

How to reproduce

• Which ClickHouse server version to use
  20.6.5.8

• Queries to run that lead to unexpected result

create table left (
    id integer
) ENGINE = Memory();

create table right (
    id integer,
    batch LowCardinality(String)
) ENGINE = Memory();

insert into left (id) select * from system.numbers limit 10;
insert into right (id, batch) values (0, 'test'), (1, 'test'), (2, 'test');

set join_use_nulls = 1;
select * from left left outer join right on left.id = right.id;

Results:

0	0     test
1	1     test
2	2     test
3	null  ""
4	null  ""
5	null  ""
6	null  ""
7	null  ""
8	null  ""
9	null  ""

Expected behavior

0	0       test 
1	1       test 
2	2       test 
3	null	null 
4	null	null 
5	null	null 
6	null	null 
7	null	null 
8	null	null 
9	null	null 

[den-crane]

WA:

SELECT *
FROM left
LEFT JOIN
(
    SELECT
        id,
        cast(batch, 'LowCardinality(Nullable(String))')
    FROM right
) AS right ON left.id = right.id

┌─id─┬─right.id─┬─cast(batch, 'LowCardinality(Nullable(String))')─┐
│  0 │        0 │ test                                            │
│  1 │        1 │ test                                            │
│  2 │        2 │ test                                            │
│  3 │     ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ                                            │
│  4 │     ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ                                            │
│  5 │     ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ                                            │
│  6 │     ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ                                            │
│  7 │     ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ                                            │
│  8 │     ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ                                            │
│  9 │     ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ                                            │
└────┴──────────┴─────────────────────────────────────────────────┘

[4ertus2]

Some of types are not allowed inside Nullable yet. LC - one of them. There's also tuples and arrays. You may check it with

SELECT toTypeName(toNullable(something))

For LC it returns

SELECT toTypeName(toNullable(toLowCardinality('c')))

┌─toTypeName(toNullable(toLowCardinality('c')))─┐
│ LowCardinality(Nullable(String))              │
└───────────────────────────────────────────────┘

Unfortunately it's not the same as Nullable(LowCardianlity(String)) which is not supported. So JOIN cannot change it's nullability the same way as all other types.

[alexey-milovidov]

@4ertus2 but LowCardinality of Nullable is Ok.

[alexey-milovidov]

Works perfectly on version 20.13-testing.
I will add a test.

[alexey-milovidov]

@KochetovNicolai It does not work.

[alexey-milovidov]

@KochetovNicolai it still does not work.

[vdimir]

LowCardinality of Nullable is Ok.

@alexey-milovidov

Now canBeInsideNullable is false for LowCardinality(String). Enabling it in ColumnLowCardinality.h and DataTypeLowCardinality.h makes this query work, I'll watch on other tests in CI #23220

UPD: this is about Nullable(LowCardinality(T)) but we need LowCardinality(Nullable(T)), trying in #23237