Clickhouse: Normal type and LowCardinality can not be combined with UNION ALL

[alex-zaitsev]

(you don't have to strictly follow this form)

ClickHouse fails when String and LowCardinality(String) columns are connected with UNION ALL. Steps to reproduce:

create temporary table t1(a String);

create temporary table t2(a LowCardinality(String));

select a from t1 union all select a from t2;

Received exception from server (version 19.1.6):
Code: 386. DB::Exception: Received from localhost:9000, ::1. DB::Exception: There is no supertype for types String, LowCardinality(String) because some of them are String/FixedString and some of them are not.

[nezed]

Having the same issue on 19.3.3

Currently you can use CAST(a, 'String') as workaround

SELECT a FROM t1 UNION ALL SELECT CAST(a, 'String') FROM t2;

[alex-zaitsev]

Sure cast works. One way or another.

It is also interesting that behaviour of arrays is not consistent here. I.e. element of Array(LowCardinality(String)) is String. array of LowCardinality(String) is array of String and so on.

Common supertype for String and LowCardinality(String) should help.

    cast('a', 'LowCardinality(String)') AS a, 
    toTypeName(a), 
    [a] AS array_a, 
    toTypeName([a]), 
    cast([a], 'Array(LowCardinality(String))') AS array_a_casted, 
    toTypeName(array_a_casted), 
    toTypeName(array_a_casted[1])
FORMAT Vertical

Row 1:
──────
a:                                                                                                              a
toTypeName(cast('a', 'LowCardinality(String)')):                                                                LowCardinality(String)
array_a:                                                                                                        ['a']
toTypeName(array(cast('a', 'LowCardinality(String)'))):                                                         Array(String)
array_a_casted:                                                                                                 ['a']
toTypeName(cast(array(cast('a', 'LowCardinality(String)')), 'Array(LowCardinality(String))')):                  Array(LowCardinality(String))
toTypeName(arrayElement(cast(array(cast('a', 'LowCardinality(String)')), 'Array(LowCardinality(String))'), 1)): String