SQL:2023 support matrix for ClickHouse

[UnamedRus]

Based on http://peter.eisentraut.org/blog/2023/04/04/sql-2023-is-finished-here-is-whats-new review (kudos for him)

ClickHouse 23.3

UNIQUE null treatment (F292) (Not supported)

Unique constants does not exist in ClickHouse

ORDER BY in grouped table (F868) (Not supported)

SELECT
    product.product_id,
    sum(product_part.num)
FROM product
INNER JOIN product_part ON product.product_id = product_part.product_id
GROUP BY product.product_id
ORDER BY product.product_code ASC

Received exception:
Code: 215. DB::Exception: Column `product_code` is not under aggregate function and not in GROUP BY. Have columns: ['sum(num)','product_id']: While processing product_code ASC. (NOT_AN_AGGREGATE)

GREATEST and LEAST (T054) (Supported)

SELECT GREATEST(1, 2, 3)

┌─greatest(1, 2, 3)─┐
│                 3 │
└───────────────────┘

String padding functions (T055) (Supported)

SELECT lpad(CAST('12345.67', 'varchar'), 12, '*') AS res

Query id: 1ee3a790-8edc-4ce5-9db4-92f5b26b40b3

┌─res──────────┐
│ ****12345.67 │
└──────────────┘

Multi-character TRIM functions (T056) (Partial)

Supported, but with old and "heavy" syntax

SELECT TRIM(LEADING '*' FROM '****12345.67') as res;

┌─res──────┐
│ 12345.67 │
└──────────┘

SELECT ltrim('*','****12345.67') as res;

Expected one of:

Need alias for ltrim function with 2 arguments

Optional string types maximum length (T081) (Supported)

CREATE TABLE t1
(
    `a` VARCHAR,
    `b` VARCHAR
)
ENGINE = Memory

Enhanced cycle mark values (T133) (Not supported)

Recursive CTE's are not supported

ANY_VALUE (T626) (Supported)

SELECT any_value(number)
FROM numbers(10)

Received exception:
Code: 46. DB::Exception: Unknown function any_value:

(any function exist in ClickHouse)
#52147
#52140

Non-decimal integer literals (T661) (Partial)

Working:

SELECT 0xFFFF,  0b11001111;

Non-working:

SELECT 0o755

Received exception:
Code: 47. DB::Exception: Missing columns: '0o755'

Underscores in numeric literals (T662) (Partial)

Working:

SELECT 1_000_000;

Non working:

SELECT
    1000000,
    `0x_FFFF_FFFF`

Code: 47. DB::Exception: Missing columns: '0x_FFFF_FFFF'

JSON

JSON data type (T801) (Supported)

But it's experimental

Enhanced JSON data type (T802) (Partial)

Allow to add constrain on only uniq keys
JSON('...text...' WITH UNIQUE KEYS).

(BTW, it's default behavior for ClickHouse)

SELECT CAST('{"foo": {"bar": [100, 200, 300]}, "foo": "e" }', 'JSON') AS a

0 rows in set. Elapsed: 0.007 sec.

Received exception:
Code: 15. DB::Exception: Subcolumn 'foo' already exists: While processing CAST('{"foo": {"bar": [100, 200, 300]}, "foo": "e" }', 'JSON') AS a.

String-based JSON (T803) (Not supported)

Need an alias data type

Hex integer literals in SQL/JSON path language (T840) (Not supported)

???

SQL/JSON simplified accessor (T860–T864) (Partial)

Supported only for real columns from table, and not from CAST's

SELECT k.foo.bar[2]
FROM json

┌─arrayElement(k.foo.bar, 2)─┐
│                        200 │
└────────────────────────────┘

SQL/JSON item methods (T865–T878) (Not supported)

    T865: SQL/JSON item method: bigint()
    T866: SQL/JSON item method: boolean()
    T867: SQL/JSON item method: date()
    T868: SQL/JSON item method: decimal()
    T869: SQL/JSON item method: decimal() with precision and scale
    T870: SQL/JSON item method: integer()
    T871: SQL/JSON item method: number()
    T872: SQL/JSON item method: string()
    T873: SQL/JSON item method: time()
    T874: SQL/JSON item method: time_tz()
    T875: SQL/JSON item method: time precision
    T876: SQL/JSON item method: timestamp()
    T877: SQL/JSON item method: timestamp_tz()
    T878: SQL/JSON item method: timestamp precision

JSON comparison (T879–T882) (Not supported)

SELECT
    CAST('{"foo": {"bar": [100, 200, 300]}, "aaa": "e" }', 'JSON') AS a,
    CAST('{"foo": "result"}', 'JSON') AS b,
    a = b

┌─a───────────────────────────────────┬─b────────────────┬─equals(a, b)─┐
│ {"aaa":"e","foo.bar":[100,200,300]} │ {"foo":"result"} │            1 │
└─────────────────────────────────────┴──────────────────┴──────────────┘

Property Graph Queries (SQL/PGQ) (Not Supported)

CREATE PROPERTY GRAPH financial_transactions
    VERTEX TABLES (person, company, account)
    EDGE TABLES (ownerof, transaction);

SELECT owner_name,
       SUM(amount) AS total_transacted
FROM financial_transactions GRAPH_TABLE (
  MATCH (p:person WHERE p.name = 'Alice')
        -[:ownerof]-> (:account)
        -[t:transaction]- (:account)
        <-[:ownerof]- (owner:person|company)
  COLUMNS (owner.name AS owner_name, t_amount AS amount)
) AS ft
GROUP BY owner_name;

Not supported

---

Should be easy to implement / fix:

• Multi-character TRIM functions (T056) (Partial)
• Non-decimal integer literals (T661) (Partial)
• Underscores in numeric literals (T662) (Partial)