Clickhouse: argMaxOrNullState/Merge doesn't work with NULL values

[Anshelen]

Consider the following queries:

CREATE TABLE test (ts DATETIME, id VARCHAR, ver Int8, str Nullable(VARCHAR), num Nullable(DOUBLE)) ENGINE=MergeTree PARTITION BY toStartOfDay(ts) ORDER BY id;

INSERT INTO test VALUES (now(), '1', 1, null, null);

SELECT ts, id, argMax(str, ver), argMax(num, ver) FROM test GROUP BY ts,id;

| 2020-08-11 08:29:51 | 1 | NULL | NULL |
|---------------------|---|------|------|

CREATE MATERIALIZED VIEW test_mv ENGINE=AggregatingMergeTree PARTITION BY toStartOfDay(ts) ORDER BY id POPULATE AS SELECT ts,id, argMaxOrNullState(str, ver) str, argMaxOrNullState(num, ver) num FROM test GROUP BY ts, id;

SELECT ts, id, argMaxOrNullMerge(str), argMaxOrNullMerge(num) FROM test_mv GROUP BY ts, id;

| 2020-08-11 08:29:51 | 1 | | 0 |
|---------------------|---|------|------|

I expect the second output to be equal to the first one.
Some other functions are working as expected (checked anyOrNullState, maxOrNullState, sumOrNullState).

ClickHouse version: 20.5.3.27

[filimonov]

W/o OrNull it works :)

CREATE MATERIALIZED VIEW test_mv2 ENGINE=AggregatingMergeTree PARTITION BY toStartOfDay(ts) ORDER BY id POPULATE AS SELECT ts,id, argMaxState(str, ver) str, argMaxState(num, ver) num FROM test GROUP BY ts, id;

SELECT ts, id, argMaxMerge(str), argMaxMerge(num) FROM test_mv2 GROUP BY ts, id;


┌──────────────────ts─┬─id─┬─argMaxMerge(str)─┬─argMaxMerge(num)─┐
│ 2020-08-11 14:21:10 │ 1  │ ᴺᵁᴸᴸ             │             ᴺᵁᴸᴸ │
└─────────────────────┴────┴──────────────────┴──────────────────┘

1 rows in set. Elapsed: 0.002 sec. 

The issue with OrNull is visible w/o involving states:

SELECT 
    ts,
    id,
    argMaxOrNull(str, ver),
    argMaxOrNull(num, ver)
FROM test
GROUP BY 
    ts,
    id

┌──────────────────ts─┬─id─┬─argMaxOrNull(str, ver)─┬─argMaxOrNull(num, ver)─┐
│ 2020-08-11 14:21:10 │ 1  │                        │                      0 │
└─────────────────────┴────┴────────────────────────┴────────────────────────┘

[filimonov]

@hczhcz can you check that?

[Anshelen]

@filimonov i repeated your requests:

CREATE TABLE test (ts DATETIME, id VARCHAR, ver Int8, str Nullable(VARCHAR), num Nullable(DOUBLE)) ENGINE=MergeTree PARTITION BY toStartOfDay(ts) ORDER BY id;
INSERT INTO test VALUES (now(), '1', 1, null, null);

CREATE MATERIALIZED VIEW test_mv2 ENGINE=AggregatingMergeTree PARTITION BY toStartOfDay(ts) ORDER BY id POPULATE AS SELECT ts,id, argMaxState(str, ver) str, argMaxState(num, ver) num FROM test GROUP BY ts, id;

SELECT ts, id, argMaxMerge(str), argMaxMerge(num) FROM test_mv2 GROUP BY ts, id;

But anyway I get empty string and zero number

[filimonov]

Hm, it looks like the behavior has changed just in the last releases (most probably https://github.com/ClickHouse/ClickHouse/pull/12376 ) Check 20.5.4

[Anshelen]

Finally, in 20.5.4.40 problem was fixed

[filimonov]

Actually it seems argMaxOrNull still misbehaves, but workaround looks straight forward.