Introduction
Every new ClickHouse® release brings performance improvements, new features, and occasionally behavioral changes that improve SQL standards compliance. One such change in ClickHouse® 26.3 affects how the NOT operator is parsed and evaluated.
The NOT operator precedence now matches the SQL standard, meaning that an expression such as:
NOT x IS NULL
is now interpreted as:
NOT (x IS NULL)
This change makes ClickHouse® more consistent with standard SQL and aligns its behavior with databases such as PostgreSQL, MySQL, and BigQuery.
However, if your existing queries relied on the previous parsing behavior, upgrading to 26.3 may silently change query results without producing any errors or warnings. These kinds of behavioral changes are among the easiest to overlook during an upgrade.
In this article, we'll examine what changed, why it matters, how it affects existing queries, and how to safely migrate your SQL.
Why Was This Change Introduced?
Historically, ClickHouse® evaluated certain expressions involving the NOT operator differently from the SQL standard.
The parser in ClickHouse® 26.3 now follows standard SQL operator precedence, providing:
- Better SQL standards compliance
- More predictable query behavior
- Improved readability
- Better compatibility with other relational databases
- Easier migration of SQL workloads
Following the SQL standard reduces ambiguity and makes queries behave consistently across different database systems.
Understanding Operator Precedence
Operator precedence determines the order in which SQL expressions are evaluated when parentheses are omitted.
For example, in mathematics:
2 + 3 × 4 = 14
because multiplication has higher precedence than addition.
SQL follows similar rules.
According to the SQL standard:
IS NULL
has higher precedence than
NOT
Therefore:
NOT amount IS NULL
should always be interpreted as:
NOT (amount IS NULL)
which is logically equivalent to:
amount IS NOT NULL
What Changed in ClickHouse® 26.3?
Prior to version 26.3, ClickHouse® interpreted the same expression differently.
Before 26.3
NOT amount IS NULL
was parsed as
(NOT amount) IS NULL
Starting with 26.3
The parser now evaluates it as:
NOT (amount IS NULL)
which follows the SQL standard.
Although the syntax hasn't changed, the evaluation order has.
This means identical SQL can now produce different results after upgrading.
Example
Let's use a simple table.
CREATE TABLE default.orders
(
order_id UInt32,
customer String,
amount Nullable(Float64),
order_date Date
)
ENGINE = MergeTree()
ORDER BY order_id;
Insert some data.
INSERT INTO default.orders VALUES
(1, 'Alice', 1200.00, '2024-01-01'),
(2, 'Bob', NULL, '2024-01-02'),
(3, 'Charlie', 890.00, '2024-01-03'),
(4, 'Diana', NULL, '2024-01-04');
Now run:
SELECT
order_id,
customer,
amount
FROM default.orders
WHERE NOT amount IS NULL;
Before 26.3
The parser interpreted this as:
(NOT amount) IS NULL
which could lead to confusing or unexpected behavior.
After 26.3
The expression becomes:
NOT (amount IS NULL)
which correctly returns rows where amount is not NULL.
Result:
| order_id | customer | amount |
|---|---|---|
| 1 | Alice | 1200.00 |
| 3 | Charlie | 890.00 |
This matches the behavior of standard SQL databases.
Why This Matters
The dangerous part of this change is that it does not generate an error.
Your queries:
- still execute successfully,
- still return data,
- but the returned results may be different.
This makes the issue particularly difficult to detect during upgrades unless query results are validated.
Other SQL Compatibility Improvements in 26.3
The NOT precedence update is one of several SQL compatibility improvements introduced in ClickHouse® 26.3.
Parenthesis-free date and time functions
Functions can now be written using standard SQL syntax.
SELECT
NOW,
CURRENT_DATE,
CURRENT_TIMESTAMP;
instead of
SELECT
now(),
currentDate(),
now();
Support for SOME
SOME is now accepted as an alias for ANY.
SELECT *
FROM t
WHERE x = SOME
(
SELECT y
FROM u
);
Parenthesized JOIN expressions
Complex joins can now be grouped explicitly.
SELECT *
FROM
(
t1
CROSS JOIN t2
)
JOIN t3
ON t2.id = t3.id;
This improves compatibility with standard SQL syntax.
Finding Potentially Affected Queries
Because queries continue running normally, you should proactively search for them before upgrading.
Search the query log for NOT ... IS NULL.
SELECT
query,
event_time,
user
FROM system.query_log
WHERE type = 'QueryFinish'
AND query ILIKE '%NOT%IS NULL%'
AND event_time >= now() - INTERVAL 30 DAY
ORDER BY event_time DESC
LIMIT 50;
Also search for:
SELECT query
FROM system.query_log
WHERE type = 'QueryFinish'
AND query ILIKE '%NOT%IS NOT NULL%'
ORDER BY event_time DESC
LIMIT 50;
Review every matching query to ensure it behaves as intended after upgrading.
How to Fix Existing Queries
The safest solution is to eliminate ambiguity entirely.
Instead of writing:
WHERE NOT amount IS NULL
write
WHERE amount IS NOT NULL
or
WHERE NOT (amount IS NULL)
Both versions are explicit and work consistently across ClickHouse® versions and other SQL databases.
More examples:
Ambiguous:
WHERE NOT status IS NULL
WHERE NOT country IS NULL
Preferred:
WHERE status IS NOT NULL
WHERE country IS NOT NULL
or
WHERE NOT (status IS NULL)
WHERE NOT (country IS NULL)
Using IS NOT NULL is generally the cleanest and most readable option.
Version Compatibility
| ClickHouse® Version | Interpretation | Behavior |
|---|---|---|
| Before 26.3 | (NOT x) IS NULL |
Non-standard |
| 26.3 and later | NOT (x IS NULL) |
SQL standard compliant |
Upgrade Checklist
Before upgrading production systems to ClickHouse® 26.3 or later:
| Step | Recommendation |
|---|---|
| 1 | Search system.query_log for NOT ... IS NULL patterns |
| 2 | Review application SQL code |
| 3 | Check Materialized View definitions |
| 4 | Replace NOT x IS NULL with x IS NOT NULL
|
| 5 | Add parentheses to complex NOT expressions |
| 6 | Validate query results on a staging cluster |
| 7 | Verify Materialized Views produce expected data |
| 8 | Review asynchronous insert configuration if your application depends on synchronous acknowledgements |
Best Practices
To avoid similar issues in future upgrades:
- Prefer
IS NOT NULLinstead ofNOT x IS NULL. - Always use parentheses around complex boolean expressions.
- Never rely on implicit operator precedence.
- Test query results—not just query execution—during upgrades.
- Validate Materialized Views after upgrading.
- Read release notes carefully for parser and SQL compatibility changes.
Final Thoughts
The NOT operator precedence change in ClickHouse® 26.3 is a relatively small parser improvement, but it has significant practical implications.
By aligning with the SQL standard, ClickHouse® becomes more predictable and easier to use alongside other relational databases. However, because this is a silent behavioral change, existing queries may return different results after upgrading without any warnings.
Fortunately, the fix is simple: replace ambiguous expressions like NOT x IS NULL with the clearer and more portable x IS NOT NULL, or use explicit parentheses when necessary.
A few minutes spent reviewing existing SQL before upgrading can prevent subtle bugs and ensure a smooth migration to ClickHouse® 26.3.
Top comments (0)