Clickhouse: Last view value replaced by zero in function calls

[vivienm]

Describe the bug

I have a simple table with two columns date (used for ordering and partitioning) and hits. I have created a materialized view to compute total hits per day using an AggregatingMergeTree, and a plain view to easily query / display the data from the AggregatingMergeTree (see below). The rows in the view look correct.

However, sum(hits) on the view is lower than the expected result, as if the last row was ignored. Its value does not appear in groupArray(hits), whereas an unexpected 0 is present.

How to reproduce

• Which ClickHouse server version to use

Tested on 19.3.4 (Debian) and 18.16.1 (Docker).

• Which interface to use, if matters

TCP (native client).

• Non-default settings, if any

TCP server running on port 10000.

• CREATE TABLE statements for all tables involved

CREATE TABLE test_base
(
    date Date,
    hits UInt32
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(date)
ORDER BY date;

CREATE MATERIALIZED VIEW test_aggr
(
    date Date,
    hits_state AggregateFunction(sum, UInt32)
)
ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(date)
ORDER BY date AS
SELECT
    date,
    sumState(hits) AS hits_state
FROM test_base
GROUP BY date;

CREATE VIEW test_view
(
    date Date,
    hits UInt32
) AS
SELECT
    date,
    sumMerge(hits_state) AS hits
FROM test_aggr
GROUP BY date;

• Sample data for all these tables, use clickhouse-obfuscator if necessary

INSERT INTO test_base FORMAT JSONEachRow
{"date":"2018-09-26","hits":1}
{"date":"2018-09-26","hits":2}
{"date":"2018-09-27","hits":4}
{"date":"2018-09-28","hits":8};

The query

SELECT *
FROM test_view;

returns as expected

┌───────date─┬─hits─┐
│ 2018-09-26 │    3 │
│ 2018-09-27 │    4 │
│ 2018-09-28 │    8 │
└────────────┴──────┘

• Queries to run that lead to unexpected result

SELECT
    count(),
    sum(hits),
    groupArray(hits)
FROM test_view;

Returns:

┌─count()─┬─sum(hits)─┬─groupArray(hits)─┐
│       3 │         7 │ [3,0,4]          │
└─────────┴───────────┴──────────────────┘

Expected behavior

I would have expected:

┌─count()─┬─sum(hits)─┬─groupArray(hits)─┐
│       3 │        15 │ [3,4,8]          │
└─────────┴───────────┴──────────────────┘

Why is the last value (8) missing? Where does the 0 come from in groupArray(hits)?

[den-crane]

Seems UInt64 treated as UInt32

CREATE VIEW test_view AS
SELECT date, sumMerge(hits_state) AS hits FROM test_aggr GROUP BY date;

DESCRIBE TABLE test_view

┌─name─┬─type───┬─default_type─┬─default_expression─┬─comment_expression─┬─codec_expression─┐
│ date │ Date   │              │                    │                    │                  │
│ hits │ UInt64 │              │                    │                    │                  │
└──────┴────────┴──────────────┴────────────────────┴────────────────────┴──────────────────┘

SELECT count(), sum(hits), groupArray(hits) FROM test_view;

┌─count()─┬─sum(hits)─┬─groupArray(hits)─┐
│       3 │        15 │ [3,4,8]          │
└─────────┴───────────┴──────────────────┘

---

CREATE VIEW test_view
(    date Date,    hits UInt32
) AS
SELECT
    date,
    cast(sumMerge(hits_state) as UInt32) AS hits
FROM test_aggr
GROUP BY date;

SELECT count(), sum(hits), groupArray(hits) FROM test_view;

┌─count()─┬─sum(hits)─┬─groupArray(hits)─┐
│       3 │        15 │ [3,4,8]          │
└─────────┴───────────┴──────────────────┘

[den-crane]

ClickHouse server version 19.3.5

create view testv(a UInt32) as select number a from numbers(10);
select groupArray(a) from testv;
┌─groupArray(a)─────────┐
│ [0,0,1,0,2,0,3,0,4,0] │
└───────────────────────┘

[alexey-milovidov]

Original issue was fixed.
And now we have error message instead:

milovidov-Pro-P30 :) create view testv(a UInt32) as select number a from numbers(10);

CREATE VIEW testv
(
    `a` UInt32
) AS
SELECT number AS a
FROM numbers(10)

Ok.

0 rows in set. Elapsed: 0.016 sec. 

milovidov-Pro-P30 :) select groupArray(a) from testv;

SELECT groupArray(a)
FROM testv

→ Progress: 0.00 rows, 0.00 B (0.00 rows/s., 0.00 B/s.) Received exception from server (version 20.2.1):
Code: 368. DB::Exception: Received from localhost:9000. DB::Exception: Bad cast from type DB::ColumnVector<unsigned long> to DB::ColumnVector<unsigned int>.

[alexey-milovidov]

We need to add type conversions for Views.

[alexey-milovidov]

100% fixed.