Workflow [PR], commit [85f1375]

Summary: ✅

Local test results

Ran the full SQLLogic test suite locally against a debug build.

Self-test

success	All statements from self-test are successful

Statements test

5887/6109 passed (96.4%)

222 failures breakdown:

• 216× Sorting key contains nullable columns — newer sqlglot (28.10.1) wraps SQLite types in Nullable(), which MergeTree rejects for PK columns
• 4× syntax errors (unsupported SQL constructs)
• 1× lightweight delete related
• 1× expected error mismatch

Complete test

3,399,890/3,880,035 passed (87.6%)

480,145 failures breakdown:

• 323,688× ClickHouse threw exceptions where SQLite succeeded (expected dialect differences)
• 115,091× result hash mismatches (different computation semantics, e.g. integer overflow, floating point)
• 41,074× different value counts
• 216× nullable key issue (same as statements test)
• 70× overflow (inf/-inf)
• 6× other

These results are consistent with the historical results before the test was disabled. The failures are expected ClickHouse-vs-SQLite behavioral differences, not test runner bugs.

Failure classification (complete test: 480,145 / 3,880,035 failures)

1. Cannot convert NULL to a non-nullable type — 260,732 (54.3%)

ClickHouse is stricter about NULL handling than SQLite. When a WHERE clause contains NULL comparisons, ClickHouse refuses to convert NULL to a non-nullable type.

SELECT DISTINCT 87 * col0 AS col0 FROM tab1 WHERE NULL <= (-CAST(NULL, 'REAL'))
-- Code: 70. Cannot convert NULL to a non-nullable type

2. Result hash mismatches — 115,091 (24.0%)

ClickHouse and SQLite compute slightly different results (e.g. integer overflow behavior, floating point precision, correlated subquery semantics).

SELECT (SELECT count(*) FROM t1 AS x WHERE x.b<t1.b)
  FROM t1 WHERE (a>b-2 AND a<b+2) OR c>d ORDER BY 1
-- canonic and actual results have different hashes

3. Aggregate-in-WHERE / semantic differences — 59,702 (12.4%)

Various ClickHouse-specific semantic rejections that SQLite accepts, e.g. using aggregate aliases in WHERE, different scoping rules.

SELECT COUNT(*) AS col0 FROM tab2 AS cor0 WHERE col0 NOT IN (col1 + 7 * col2)
-- Code: 184. Aggregate function COUNT(*) AS col0 is found in WHERE in query

4. Different value counts — 41,074 (8.6%)

UNION ALL / EXCEPT / INTERSECT produce different row counts due to semantic differences.

SELECT d9 FROM t9 WHERE (c9=110 OR 646=d9)
  UNION ALL SELECT a4 FROM t4 WHERE ...
  EXCEPT SELECT d6 FROM t6 WHERE ...
-- canonic values count 9, actual 11

5. Syntax errors — 3,232 (0.7%)

SQL constructs valid in SQLite but not in ClickHouse, e.g. parenthesized table references in FROM.

SELECT * FROM ( tab1 AS cor0 CROSS JOIN tab2 AS cor1 ) WHERE NOT NULL IS NULL
-- Code: 62. Syntax error at position 17

6. Nullable PK columns — 216 (0.04%)

Newer sqlglot (28.10.1) transpiles SQLite INTEGER to Nullable(Int32), which MergeTree rejects for primary key columns.

CREATE TABLE t1 (a1 Nullable(Int32) PRIMARY KEY, ...) ENGINE = MergeTree() ORDER BY a1
-- Code: 44. Sorting key contains nullable columns

7. Integer overflow — 70 (0.01%)

ClickHouse integer arithmetic produces inf/-inf where SQLite wraps around.

SELECT 25 / MIN(-81) FROM tab0 cor0 WHERE (NULL) = NULL
-- Got overflowed result 'inf' for I type

8. Decimal overflow — 19

SELECT DISTINCT 8 * COUNT(col2) * -CAST(+-AVG(DISTINCT +87) AS INTEGER) * -+COUNT(*) FROM tab1 ...
-- Code: 70. Unexpected inf or nan to integer conversion

9. Other minor categories — 9

• 4× illegal argument types for aggregate functions (avg on String)
• 3× table/function does not exist (totalDistinct)
• 1× lightweight delete not supported (UPDATE ... SET)
• 1× expected error mismatch (count(DISTINCT *) succeeds in ClickHouse)

SQLLogic Test Results (Updated with new settings)

Settings applied:

• cast_keep_nullable = 1
• prefer_column_name_to_alias = 1
• allow_nullable_key = 1
• enable_block_number_column = 1, enable_block_offset_column = 1 (table settings)
• Worker pool capped at 16

Results:

• Statements test: 100% pass rate (all 213 tests passed)
• Complete test: 99.27% pass rate (15,130 / 2,058,307 failures)

Failure Classification (15,130 total failures):

Key examples:

1. NOT precedence (74.7%): SELECT * FROM tab0 WHERE NOT (58) IS NOT NULL

• Standard SQL: NOT (58 IS NOT NULL) = FALSE → 0 rows
• ClickHouse: (NOT 58) IS NOT NULL = TRUE → all rows

2. IN non-constant RHS (18.2%): SELECT * FROM tab0 WHERE (97 - col0 * col3) IN (+col1)

• Exception: "Function in is supported only if second argument is constant or table expression"

3. Aggregate over empty set (3.2%): SELECT AVG(-78) FROM tab0 WHERE col1 * 48 * -65 IS NULL

• Different NULL/0 representation for aggregates over empty result sets

SQLLogic Test Results (latest run)

Settings enabled: default_table_engine=MergeTree, union_default_mode=DISTINCT, group_by_use_nulls=1, join_use_nulls=1, allow_create_index_without_type=1, create_index_ignore_unique=1, cast_keep_nullable=1, prefer_column_name_to_alias=1, aggregate_functions_null_for_empty=1

Table settings: allow_nullable_key=1, enable_block_number_column=1, enable_block_offset_column=1

Statements test: 100% pass rate (213/213)

Complete test: 99.34% pass rate (13,665 / 2,058,307 failures)

All categories are known ClickHouse-vs-SQLite behavioral differences, not bugs.

Updated SQLLogic Test Results (after IN (col) fix)

Changes in this update

New commit: 320e7c9 — Wrap bare column reference in tuple for IN operator

When the parser strips parentheses from IN (col) (single element), it produces IN col with a bare ASTIdentifier. After resolution this becomes a ColumnNode, which is not handled by CollectSets. The fix wraps it in tuple() so the existing tuple → has() rewrite path handles it. This fixes queries like SELECT * FROM t WHERE x IN (col).

Results

Statements test: 100% pass rate (213/213)

Complete test: 99.46% pass rate (11,095 / 2,058,307 failures)

Improvement: 13,665 → 11,095 failures (2,570 fixed by the IN (col) fix).

Failure classification

NOT precedence details

Verified against PostgreSQL, MySQL, DuckDB, SQLite — all agree with the SQL standard: NOT has lower precedence than IS NULL, IN, BETWEEN, LIKE. ClickHouse's Pratt parser also assigns NOT lower precedence (priority 5) than IS NULL (6), and NOT col0 IS NULL parses correctly. But NOT (col0) IS NULL breaks: the parser consumes (col0) as a complete subexpression, applies NOT to it, then IS NOT NULL binds to the result.

Settings used

Server: default_table_engine=MergeTree, union_default_mode=DISTINCT, group_by_use_nulls=1, join_use_nulls=1, allow_create_index_without_type=1, create_index_ignore_unique=1, cast_keep_nullable=1, prefer_column_name_to_alias=1, aggregate_functions_null_for_empty=1

Table: allow_nullable_key=1, enable_block_number_column=1, enable_block_offset_column=1

SQLLogic Test: Full Test Suite Results (5.9M tests)

Statement Failures (8)

All 8 are unsupported SQLite-specific features:

• DROP INDEX without ON, REINDEX, CREATE TRIGGER/DROP TRIGGER, UPDATE without WHERE, UNIQUE constraint, INSERT OR REPLACE
• count(DISTINCT *) — ClickHouse actually supports this, but the test expected it to fail (1 "did not fail as expected")

Query Failures by Root Cause (201,699)

Detailed Exception Breakdown (60,186 queries that threw exceptions)

Settings used: cast_keep_nullable=1, group_by_use_nulls=1, join_use_nulls=1, prefer_column_name_to_alias=1, aggregate_functions_null_for_empty=1, union_default_mode=DISTINCT.

sqlglot is unneeded:

  ┌──────────────────────────────┬──────────────┬─────────────────┬────────┐
  │            Metric            │ With sqlglot │ Without sqlglot │  Diff  │
  ├──────────────────────────────┼──────────────┼─────────────────┼────────┤
  │ Hash mismatch                │ 88,570       │ 88,596          │ +26    │
  ├──────────────────────────────┼──────────────┼─────────────────┼────────┤
  │ Row count mismatch           │ 23,218       │ 25,617          │ +2,399 │
  ├──────────────────────────────┼──────────────┼─────────────────┼────────┤
  │ Code 349 (NULL→non-Nullable) │ 3,668        │ 3,564           │ -104   │
  ├──────────────────────────────┼──────────────┼─────────────────┼────────┤
  │ Code 352 (block mismatch)    │ 2,724        │ 2,756           │ +32    │
  ├──────────────────────────────┼──────────────┼─────────────────┼────────┤
  │ All others                   │ 53,365       │ 53,362          │ -3     │
  ├──────────────────────────────┼──────────────┼─────────────────┼────────┤
  │ Total failures               │ 171,545      │ 173,895         │ +2,350 │
  ├──────────────────────────────┼──────────────┼─────────────────┼────────┤
  │ Pass rate                    │ 97.112%      │ 97.072%         │ -0.04% │
  └──────────────────────────────┴──────────────┴─────────────────┴────────┘

  The +2,399 row-count increase comes from queries containing CAST(NULL AS INTEGER) inside NOT IN clauses. With sqlglot, it was rewriting the entire SQL through ast.sql("clickhouse"), which subtly changed
  query semantics beyond just the CAST wrapping. Without it, the raw SQL goes straight to ClickHouse — cast_keep_nullable handles the NULL type correctly, but the slight differences in how the full query is
  rendered account for the small shift. These are all pre-existing ClickHouse-vs-SQLite semantic gaps, not regressions.