When SUM() Lies: A ClickHouse WHERE Clause Skews Aggregate Counts

The first bug I encountered during my internship: the "slow call chain" number on the service instance details page didn't match the total count after clicking into it. Investigation revealed that the ClickHouse aggregate function sum() was affected by WHERE conditions, causing a change in statistical scope. The fix used a CASE WHEN statement. Here's the complete troubleshooting process and solution reasoning.

---

1. The Problem Emerges

Last week, my mentor sent me a "minor defect":

On the Service Instance Details → Anomaly Analysis page, the number displayed for "Slow Call Chains" is A. After clicking "View All Error Chains" → "View Slow Calls Only", the total number of call chains displayed is B. A ≠ B, but these two numbers should represent the same thing.

Honestly, I was a bit confused when I first saw this—isn't a number just a count? How could it be different?

---

2. Investigation: Comparing Request Parameters via F12

The first step in debugging any bug is always to see what parameters it's sending.

Opening F12 → Network, I captured the two requests:

When clicking "Anomaly" vs. when clicking "View Slow Calls Only"

I found the problem:

Action	stats API	list API
Click "Anomaly"	Did not send state	state=error
Click "View Slow Calls Only"	Sent state=isSlow	state=isSlow

The same stats API received different parameters in different scenarios, so naturally it returned different numbers.

---

3. Tracing the Code: From Controller to Repository

Following the call chain layer by layer:

xxxxxxxxxxController (receives Param) → xxxxxxxxxxxImpl (Service layer, directly passes through) → xxxxxxxxxRepository → buildTraceWhere() method

The key code is in buildTraceWhere:

// Dynamically concatenate WHERE conditions
if (StringUtils.hasText(param.getState())) {
    sql.append(switch (param.getState()) {
        case "success" -> " AND isError = 0 AND isSlow = 0";
        case "error"   -> " AND isError > 0";
        case "isSlow"  -> " AND isError = 0 AND isSlow > 0";  // ← Problem is here
        default        -> " AND 1 = 2";
    });
}

The logic is clear:

• state is empty (not sent) → if statement doesn't execute → no filtering
• state has a value (sent) → if statement executes → filter by condition

4. Root Cause: The Aggregate Function Was "Led Astray" by WHERE

The core SQL of the stats API:

SELECT
    sum(isError) AS errorCount,
    sum(isSlow)  AS slowCount    -- ← This has a problem
FROM trace_table
WHERE ...

The problem is subtle:

Scenario	WHERE Condition	What sum(isSlow) counts
Click "Anomaly" (state not sent)	No filtering	Slow calls in the full dataset
Click "View Slow Calls Only" (state=isSlow)	isError = 0	Slow calls after excluding errors

The statistical scope of the aggregate function follows the WHERE clause—this is the root cause.

5. Eliminating Solutions: My Deliberation Process

This problem wasn't big, but I thought about the solution choice for a long time.

Option 1: Remove isError = 0 from buildTraceWhere

No good. If removed, the slow call list would show error call data, which doesn't meet product requirements—"a call that is both slow and an error does not belong to the slow call category."

Option 2: Set state to null before calling the Repository

No good. If set to null, buildTraceWhere's switch wouldn't execute, and the list API's filtering would also fail. The "View Errors Only" and "View Slow Calls Only" lists would mix data.

Option 3: Don't change WHERE, change the aggregate function itself

✅ Let the aggregate function define its own statistical rules, independent of WHERE filtering.

6. The Fix: One Line of Code

In the end, only one line of SQL was changed:

-- Before fix
sum(isSlow) AS slowCount

-- After fix
sum(CASE WHEN isSlow > 0 AND isError = 0 THEN 1 ELSE 0 END) AS slowCount

My mentor saw it and said: ClickHouse has a more concise way to write this—sumIf:

-- ClickHouse-specific syntax (alternative writing)
sumIf(isSlow, isError = 0) AS slowCount

sumIf(value, condition) : Only accumulates when the condition is met, more concise than CASE WHEN.

Why does this solution work?

• The aggregate function internally defines the statistical rule (only count pure slow calls), unaffected by WHERE
• The list API's WHERE filtering logic is undisturbed, keeping the three states (success/error/slow) strictly separated

After the fix, tests passed. Clicking "Anomaly" and clicking "View Slow Calls Only" now shows consistent numbers.

7. Review: What I Learned

1. Troubleshooting Methodology (Reusable) F12 capture requests → Compare parameter differences → URL → Controller → Service → Repository (trace layer by layer) → Find WHERE / filter conditions → Compare whether filter conditions are consistent across different scenarios → Where they are inconsistent is the Bug

2. ClickHouse Conditional Aggregate Functions

Function	Purpose	Equivalent Standard SQL
sumIf(value, condition)	Sum when condition is met	sum(CASE WHEN condition THEN value END)
countIf(condition)	Count when condition is met	count(CASE WHEN condition THEN 1 END)
avgIf(value, condition)	Average when condition is met	Similar

ClickHouse-specific optimization, more concise than standard SQL.

3. Awareness of Solution Analysis

This problem made me realize: Before changing one place, first think clearly about which other places will be affected.

I initially wanted to directly change the WHERE condition, but it would affect the list API; I thought about setting it to null, but the two lists would mix data. Finally, I discovered that making the aggregate function self-contained was the correct approach.

This was the first bug I fixed during my internship. It wasn't big, but I went through the complete process from investigation to fix. If you are also using ClickHouse for statistics, remember: Putting conditions inside aggregate functions is more controllable than WHERE filtering