You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
ClickHouse dialect should not require interval qualifier #2390
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
Activity
dinmukhamedm commentedon Jun 28, 2026
Ok, this is slightly more complex, because ClickHouse is very permissive. Using the terminology from the linked PR, clickhouse allows both
and
The latter approach to parsing is more flexible, allowing the expression between
INTERVALandDAYqualifier to be anything ranging from a single number to an arithmetic expressionINTERVAL 1 + 1 DAYto a column reference likeINTERVAL user_id DAY.The former is strict, and even if a dialect allows unquoted intervals (by the way postgres itself doesn't, while
sqlparsersort 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 likesupports_string_literal_intervals) andsupports_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.
INTERVAL '1'INvalid)INTERVAL '1 day')INTERVAL 1+1 DAYorINTERVAL <column> DAY FROM <table>)INTERVAL '1:2' HOUR TO MINUTE)[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 MINUTEnot 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) DAYorINTERVAL (<column>) DAY FROM <table>[5] I couldn't get HOUR TO MINUTE to work, but
INTERVAL '1-2' YEAR TO MONTHworked.[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