Search code examples
clickhouse

materialized view's query has different results when executed seprerately

I want to Sum the duration that a user spent on a page, the materialized view query is like this :

CREATE MATERIALIZED VIEW duration
(
    `visits` Int64 CODEC(DoubleDelta,LZ4),
    `pageUrl` LowCardinality(String),
    `duration` Int8 CODEC(DoubleDelta,LZ4),
    `createdAtDay` Date
)
ENGINE = SummingMergeTree(visits)
ORDER BY ( createdAtDay, pageUrl)
SETTINGS index_granularity = 8192 
POPULATE AS SELECT
    count(visitId) AS visits,
   pageUrl ,
    sum(e.value) AS duration,
    createdAtDay
FROM Record r
left join Events e on r.visitId = e.visitId and e.eventType = 6
GROUP BY ( createdAtDay, pageUrl);

the thing is when I execute the query seperately, I get this results for example (for a specific page) enter image description here

but when I query the view for the same page I get this :

enter image description here

ps :

  1. I don't have <0 values in the Events table
  2. eventype = 6 means that the type of the event is a heartbeat
Solution

  • duration Int8 CODEC(DoubleDelta,LZ4),

    Int8 overflow.

    select toInt8(200+200);
┌─toInt8(plus(200, 200))─┐
│                   -112 │
└────────────────────────┘

    You have to use duration Int64 CODEC(T64,LZ4) instead.