Wrong query result on master — settings randomization divergence [2b0dd05393c693c7]

[clickgapai]

Found via ClickGap automated review. Please close or comment if this is incorrect or needs adjustment.

Describe what's wrong

A SELECT returns DIFFERENT results under a result-preserving setting profile vs defaults — a wrong-result bug, not a crash. Found by ClickGap's settings_randomization differential oracle on master and confirmed to still diverge.

Root cause: The result diverges under a result-preserving setting profile vs defaults (cell differs at row 0: '15042171845452101230' vs '9558639962779380867') — it must be identical regardless, so a difference is a correctness bug.

Reproduce — both queries should return the SAME result:

-- 1) defaults:
SELECT normalized_query_hash AS a, normalizedQueryHash(query) AS b, a = b FROM system.processes WHERE query LIKE
'SELECT normalized_query_hash AS a, normalizedQueryHash(query) AS b, a = b FROM system.processes WHERE query LIKE%'
LIMIT 1
FORMAT TSV;

-- 2) with the settings under test (should match #1):
SELECT normalized_query_hash AS a, normalizedQueryHash(query) AS b, a = b FROM system.processes WHERE query LIKE
'SELECT normalized_query_hash AS a, normalizedQueryHash(query) AS b, a = b FROM system.processes WHERE query LIKE%'
LIMIT 1
SETTINGS max_threads=1, max_block_size=1, max_insert_block_size=1, optimize_read_in_order=0, max_bytes_before_external_group_by=1
FORMAT TSV;

Found by ClickGap's continuous oracle campaign (master campaign @ local-build).

Why we believe this is a bug: The settings_randomization differential oracle flagged a result divergence (confirmed).

Affected locations:
See evidence chain.

Impact: Wrong query result (silent — no crash).

Does it reproduce on most recent release?

Yes — confirmed still diverging on current master.

How to reproduce

See root cause description and affected locations above.

Expected behavior

The code should not exhibit the behavior described in the root cause above.

Error message and/or stacktrace

See root cause description above.

Additional context

Analysis details: Confidence HIGH | Severity P1 | Testability: STATELESS_SQL

Found during automated review of PR #0.

---

ClickGapAI · Confidence: HIGH · Severity: P1 · Finding: fuzz_diff_2b0dd05393c693c7

[Gudauu]

The second query includes the SETTINGS ... clause inside the SQL text, so the row in system.processes.query is not the same as in the first query. Since the query computes/compares hashes derived from that query text, the different hash appears expected, rather than a bug as it claims.

[clickgapai]

You're right, and thanks for the precise diagnosis. The reproducer is self-referential: it computes normalizedQueryHash(query) over its own text pulled from system.processes. My oracle injects the settings profile by appending a literal SETTINGS max_threads=1, ... clause to the SQL text of run #2 — so run #2 hashes a genuinely different query string than run #1. The differing hashes (the latest commit vs the latest commit) are the expected result of hashing different text, not a correctness divergence.

The oracle's result-preserving invariant assumes settings injection leaves the observable input unchanged; that assumption is invalid for queries that introspect their own text via system.processes. There's no surviving regime to build a valid test on — the divergence is a methodology artifact, not a bug.

Closing as a false positive. I'll add self-referential system.processes.query reads to the oracle's exclusion list so this class doesn't get filed again.

[antaljanosbenjamin]

Closing as a false positive. I'll add self-referential system.processes.query reads to the oracle's exclusion list so this class doesn't get filed again.

@zlareb1 isn't this a false positive?

[zlareb1]

@antaljanosbenjamin I actually bulk assigned so missed this. Closing this one.