Skip to content

ClickHouse dialect should not require interval qualifier #2390

Open

Description

@dinmukhamedm

When the require_interval_qualifier dialect option was added (#1398), the main concern was PostgreSQL. Modern clickhouse supports all of:

unquoted intervals

SELECT INTERVAL 1 DAY;

   ┌─toIntervalDay(1)─┐
1. │                1 │
   └──────────────────┘

quoted intervals

SELECT INTERVAL '1 d';

   ┌─toIntervalDay(1)─┐
1. │                1 │
   └──────────────────┘

Mixed intervals

SELECT INTERVAL '1 2:3:4' DAY TO SECOND;

   ┌─(toIntervalDay(1), toIntervalHour(2), toIntervalMinute(3), toIntervalSecond(4))─┐
1. │ (1,2,3,4)                                                                       │
   └─────────────────────────────────────────────────────────────────────────────────┘

The fix should be as simple as flipping one boolean here

Activity

  1. dinmukhamedm commented on Jun 28, 2026

    @dinmukhamedm
    ContributorAuthor

    Ok, this is slightly more complex, because ClickHouse is very permissive. Using the terminology from the linked PR, clickhouse allows both

    SELECT INTERVAL '1 day'; -- PostgreSQL like syntax

    and

    SELECT INTERVAL 2 DAY; -- MySQL-like syntax

    The latter approach to parsing is more flexible, allowing the expression between INTERVAL and DAY qualifier to be anything ranging from a single number to an arithmetic expression INTERVAL 1 + 1 DAY to a column reference like INTERVAL user_id DAY.

    The former is strict, and even if a dialect allows unquoted intervals (by the way postgres itself doesn't, while sqlparser sort of assumes it does?), it will only allow a single numeric expression to be the value.

    Now the original PR assumed that these two are coupled, i.e. it assumed that if a dialect allows intervals in string literals, then it does not allow complex expressions in interval values. That's not the case for ClickHouse. It looks like their parser acts "MySQL-like" for unquoted intervals, and "PostgreSQL-like" for quoted ones.

    Suggestion: we should split these two controls into 2 separate dialect-level flags, e.g. require_interval_qualifier (probably should have been called something like supports_string_literal_intervals) and supports_complex_interval_expression.

    I am happy to submit a pull request myself, but it might take a little more time, as I don't want the change to be breaking, and I am not very well familiar with other dialects.


    Edit: Now that I've played a little with DuckDB playground, it looks like it might even be 4 separate flags, which are coupled, but not as tightly as the current implementation assumes. I'll probably experiment more and keep extending the table below as I go.

    Feature Requires qualifier (INTERVAL '1' INvalid) Can parse string intervals (INTERVAL '1 day') Supports arithmetic or col references in intervals (INTERVAL 1+1 DAY or INTERVAL <column> DAY FROM <table>) Supports complex intervals, (INTERVAL '1:2' HOUR TO MINUTE)
    Postgres No Yes No Yes [1]
    DuckDB Yes Yes No No [2]
    ClickHouse Yes Yes Yes Yes [3]
    MySQL Yes No Yes No
    BigQuery Yes No Yes Yes
    Databricks Yes Yes No Yes
    Hive Yes No Yes [4] Yes [5]
    Oracle [6] Yes No No Yes
    Redshift No Yes No Yes
    Snowflake [6] Yes No No Yes
    Spark SQL [7] Yes Yes No No

    [1] Together with requires qualifier == false, this means interval '1:2' is also valid in postgres
    [2] I think at parser level we should allow this, because the error message goes: HOUR TO MINUTE not supported, which means probably their parser can identify the syntax, but they just decided not to support it
    [3] Support of complex intervals introduced in ClickHouse fairly recently, from version 26.3 or 26.4
    [4] Yes, but I had to wrap the expression in parentheses: INTERVAL (1 + 1) DAY or INTERVAL (<column>) DAY FROM <table>
    [5] I couldn't get HOUR TO MINUTE to work, but INTERVAL '1-2' YEAR TO MONTH worked.
    [6] The interval VALUE itself must be quoted, i.e. INTERVAL '1' DAY
    [7] Tested in PySpark in Google Colab. Not sure if that's the best source

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions