Skip to content

-OrNull does not work with and -Merge and -State #10267

@FreGalle

Description

@FreGalle

Describe the bug
Using the -OrNull aggregate function combinator together with the -Merge and -State combinators produces different results compared to when using it without the -Merge and -State combinators.

How to reproduce

create table x (g Char, v1 Int64, v2 Int64) Engine Memory;
insert into x values ('t', 1, 1), ('t', 2, 2), ('u', 1, 1);

7f14c1d206b1 :) select * from x;

SELECT *
FROM x

┌─g─┬─v1─┬─v2─┐
│ t │  1 │  1 │
│ t │  2 │  2 │
│ u │  1 │  1 │
└───┴────┴────┘
7f14c1d206b1 :) select g, argMaxOrNullIf(v2, v1, v2 > 1) from x group by g;

┌─g─┬─argMaxOrNullIf(v2, v1, greater(v2, 1))─┐
│ t │                                      2 │
│ u │                                   ᴺᵁᴸᴸ │
└───┴────────────────────────────────────────┘

7f14c1d206b1 :) select g, argMaxOrNullIfMerge(aggregated) from (select g, argMaxOrNullIfState(v2, v1, v1 > 1) as aggregated from x group by g) group by g;

┌─g─┬─argMaxOrNullIfMerge(aggregated)─┐
│ t │                            ᴺᵁᴸᴸ │
│ u │                            ᴺᵁᴸᴸ │
└───┴─────────────────────────────────┘
7f14c1d206b1 :) select g, argMaxOrNull(v2, v1) from x group by g;

┌─g─┬─argMaxOrNull(v2, v1)─┐
│ t │                    2 │
│ u │                    1 │
└───┴──────────────────────┘

7f14c1d206b1 :) select g, argMaxOrNullMerge(aggregated) from (select g, argMaxOrNullState(v2, v1) as aggregated from x group by g) group by g;

┌─g─┬─argMaxOrNullMerge(aggregated)─┐
│ t │                          ᴺᵁᴸᴸ │
│ u │                          ᴺᵁᴸᴸ │
└───┴───────────────────────────────┘

Expected behavior
The same aggregation result.

Additional context
Clickhouse version 20.3.5.21

Metadata

Metadata

Assignees

Labels

bugConfirmed user-visible misbehaviour in official release

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions