ClickHouse DB::Exception: Invalid WITH FILL expression

The "DB::Exception: Invalid WITH FILL expression" error in ClickHouse occurs when the parameters provided to a WITH FILL clause in an ORDER BY expression are invalid or logically inconsistent. The error code is INVALID_WITH_FILL_EXPRESSION. The WITH FILL modifier is used to fill gaps in ordered result sets (such as missing dates in a time series), and ClickHouse validates the FROM, TO, and STEP values before execution.

Impact

When this error is triggered, the query fails entirely and no results are returned. This can affect:

• Time series dashboards that rely on gap-filling to display continuous data
• Reporting queries that need uniform intervals in their output
• ETL pipelines that generate filled sequences for downstream processing

Common Causes

1. STEP value is zero or negative -- The STEP parameter must be a positive value that moves the fill in the direction from FROM to TO. A zero step would create an infinite loop.
2. FROM is greater than TO with a positive STEP -- If FROM exceeds TO and STEP is positive, ClickHouse cannot generate any fill values, which is a logical contradiction.
3. Mismatched types -- The FROM, TO, and STEP values must be compatible with the column type. For example, using an integer STEP with a DateTime column without proper interval syntax.
4. Missing STEP for non-numeric types -- DateTime and Date columns require an explicit STEP using INTERVAL syntax, since ClickHouse cannot infer a default step for time types.
5. WITH FILL on unsupported column types -- Attempting to use WITH FILL on String or other non-orderable types that do not support arithmetic progression.
6. Conflicting WITH FILL on multiple ORDER BY columns -- Using WITH FILL on multiple columns with incompatible ranges or steps.

Troubleshooting and Resolution Steps

1. Verify that FROM, TO, and STEP are logically consistent:

   -- Correct: FROM < TO with positive STEP
   SELECT date, count
   FROM metrics
   ORDER BY date WITH FILL
       FROM '2024-01-01'
       TO '2024-01-31'
       STEP INTERVAL 1 DAY;
   

2. Ensure STEP is positive and non-zero:

   -- Wrong: zero step
   ORDER BY value WITH FILL FROM 0 TO 100 STEP 0;
   
   -- Correct
   ORDER BY value WITH FILL FROM 0 TO 100 STEP 10;
   

3. Use INTERVAL syntax for DateTime and Date columns:

   -- Wrong: integer step for DateTime
   ORDER BY timestamp WITH FILL FROM '2024-01-01' TO '2024-02-01' STEP 86400;
   
   -- Correct: use INTERVAL
   ORDER BY timestamp WITH FILL
       FROM toDateTime('2024-01-01 00:00:00')
       TO toDateTime('2024-02-01 00:00:00')
       STEP INTERVAL 1 HOUR;
   

4. Match the types of FROM and TO with the column type:

   -- Wrong: string values for numeric column
   ORDER BY id WITH FILL FROM '1' TO '100' STEP 1;
   
   -- Correct: numeric values for numeric column
   ORDER BY id WITH FILL FROM 1 TO 100 STEP 1;
   

5. Handle multiple WITH FILL columns carefully: When filling on multiple columns, each WITH FILL operates independently. Ensure each one has valid parameters:

   SELECT date, hour, value
   FROM data
   ORDER BY
       date WITH FILL FROM '2024-01-01' TO '2024-01-07' STEP INTERVAL 1 DAY,
       hour WITH FILL FROM 0 TO 23 STEP 1;
   

6. Simplify and test incrementally: If you are unsure which part of a complex WITH FILL expression is invalid, start with a minimal version and add parameters one at a time:

   -- Start simple
   ORDER BY date WITH FILL STEP INTERVAL 1 DAY;
   
   -- Then add boundaries
   ORDER BY date WITH FILL FROM '2024-01-01' TO '2024-12-31' STEP INTERVAL 1 DAY;
   

Best Practices

• Always specify explicit FROM, TO, and STEP values for clarity, even when ClickHouse can infer defaults for numeric types.
• Use toDate() or toDateTime() casts on FROM and TO values to avoid type mismatches with Date or DateTime columns.
• When filling time series gaps, pair WITH FILL with appropriate default values using IFNULL or coalesce for the metric columns, since filled rows will have zero or null values.
• Test WITH FILL queries on small datasets first to confirm the fill behavior matches expectations before running on large tables.
• Document the expected ranges in your query comments, as WITH FILL can generate large result sets if the range is wide and the step is small.

Frequently Asked Questions

Q: Can I use WITH FILL without specifying FROM and TO?
A: Yes, for numeric columns. ClickHouse will use the minimum and maximum values found in the result set as implicit boundaries. However, for DateTime columns you typically need to provide explicit boundaries and a STEP.

Q: Does WITH FILL insert rows into the table?
A: No. WITH FILL only affects the query result set. It generates additional rows in the output to fill gaps but does not modify any table data.

Q: What happens to non-filled columns in generated rows?
A: Columns not part of the ORDER BY WITH FILL expression will have their default values (typically 0 for numbers, empty string for strings, or the type's default) in the generated fill rows. Use INTERPOLATE to specify how to compute values for those columns.

Q: Can I use WITH FILL with INTERPOLATE?
A: Yes. The INTERPOLATE clause lets you define how non-key columns should be computed for filled rows. For example: ORDER BY date WITH FILL STEP INTERVAL 1 DAY INTERPOLATE (value AS value) will carry forward the last known value.