Clickhouse sql logic

Question

I see unexpected clickhouse select behavior and can't explain why this happening. Here are table schema and table values

CREATE DATABASE gaia;

CREATE TABLE gaia.test
(
    Col String,
    Col_array Array(String),
    Col_array2 Array(String)
)
ENGINE = MergeTree
ORDER BY Col

INSERT INTO gaia.test Values ( 1,[1,2,3],[2,8] );

Here is the select that return unexpected result

SELECT
    (
        SELECT max(max_array_length)
        FROM
        (
            SELECT max(length(Col_array)) AS max_array_length
            FROM gaia.test
            UNION ALL
            SELECT max(length(Col_array2)) AS max_array_length
            FROM gaia.test
        )
    ) AS max_array_length,
    Col,
    Col_array,
    Col_array2,
    arrayResize([1], toUInt32(max_array_length))
FROM gaia.test

┌─max_array_length─┬─Col─┬─Col_array─────┬─Col_array2─┬─arrayResize([1], toUInt32(max_array_length))─┐
│                3 │ 1   │ ['1','2','3'] │ ['2','8']  │ [1]                                          │
└──────────────────┴─────┴───────────────┴────────────┴──────────────────────────────────────────────┘

You can see that max_array_length is calculated column comes as parameter for arrayResize function that determine size of array. max_array_length = 3 so arrayResize([1],toUInt32(max_array_length)) should return {1,0,0} but this returns only {1}. If I change arrayResize([1],toUInt32(max_array_length)) to arrayResize([1],3) i get correct result {1,0,0}

Why this happening ?

Answer 1

it seems it's a bug in arrayResize function, if a Nullable type is passed

I would use ifNull as a workaround:

SELECT
     (
         SELECT max(max_array_length)
         FROM
         (
             SELECT max(length(Col_array)) AS max_array_length
             FROM gaia.test
             UNION ALL
             SELECT max(length(Col_array2)) AS max_array_length
             FROM gaia.test
         )
     ) AS max_array_length,
     Col,
     Col_array,
     Col_array2,
     arrayResize([1], ifNull(max_array_length, 0))
 FROM gaia.test
┌─max_array_length─┬─Col─┬─Col_array─────┬─Col_array2─┬─arrayResize([1], ifNull(max_array_length, 0))─┐
│                3 │ 1   │ ['1','2','3'] │ ['2','8']  │ [1,0,0]                                       │
└──────────────────┴─────┴───────────────┴────────────┴───────────────────────────────────────────────┘

ifNull removes Nullable trait from the type

SELECT
    toNullable(3) AS val,
    toTypeName(val),
    toTypeName(ifNull(val, 0))

┌─val─┬─toTypeName(val)─┬─toTypeName(ifNull(val, 0))─┐
│   3 │ Nullable(UInt8) │ UInt8                      │
└─────┴─────────────────┴────────────────────────────┘

---

BTW, this

         SELECT max(max_array_length)
         FROM
         (
             SELECT max(length(Col_array)) AS max_array_length
             FROM gaia.test
             UNION ALL
             SELECT max(length(Col_array2)) AS max_array_length
             FROM gaia.test
         )

can be reduced to

SELECT max(greatest(length(Col_array), length(Col_array2)))
FROM gaia.test

---

Earlier versions of Clickhouse produced the correct results because subqueries produce NotNullable type before. https://fiddle.clickhouse.com/b7bce245-cbb6-4395-9c79-cef2ba063307