ClickHouse dialect should not require interval qualifier

[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

[dinmukhamedm]

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