Denny [DBA at Innervate]

try to upgrade to 22.3.12.19 Definitely something wrong with the partition pruning or the index analysis. Your real table is partitioned. And you have a timezone defined probably not to...

can you please share the result of ``` SELECT toStartOfMinute(identity(t)) AS s, min(t), max(t), _part, count() FROM x WHERE s = '2022-09-09 12:00:00' GROUP BY s, _part SELECT toStartOfMinute(t) AS...

Check `sum(n)` as well, ``` SELECT toStartOfMinute(identity(t)) AS s, min(t), max(t), _part, count(), sum(n) FROM x WHERE s = '2022-09-09 12:00:00' GROUP BY s, _part SELECT toStartOfMinute(t) AS s, min(t),...

I am confused. Provide the exact queries with outputs to demonstrate the issue.

I see, `optimize_aggregation_in_order` is the culprit. ``` | => clickhouse-client --optimize_aggregation_in_order=1 < query.sql | awk -F$'\t' '{ if ($2 == "amw1332333235" && $3 == "446612552" && $4 == "0" &&...

repro ```sql drop table if exists x3; CREATE TABLE x3(t DateTime, c1 String) ENGINE = MergeTree ORDER BY (t, c1) as select '2022-09-09 12:00:00', toString(number%2) from numbers(2) union all select...

@larry-cdn77 Please confirm your settings ``` select name,value from system.settings where name = 'optimize_aggregation_in_order'; ``` And check the result with `optimize_aggregation_in_order=0`

merged in https://github.com/ClickHouse/ClickHouse/pull/40219

>ENGINE = MergeTree(created_date, (tags_id, created_at), 8192) This is outdated syntax and this partitioning does not use partition pruning which makes part of queries much slower. I recommend to replace it...

You should use parseDateTime64BestEffort `SELECT 1　FROM dt64test　WHERE dt64column = parseDateTime64BestEffort('2020-01-13 13:37:00',3)`