Cherry pick #104217 to 26.3: Fix SQLite WHERE predicate pushdown for strings with special characters - #117674

This PR's branch was created on top of the interserver fix branch
`fix_unauthenticated_table_existence_request`, so the unrelated
`TablesStatusRequest` authentication check in `TCPHandler.cpp` (and its
`04036_interserver_tables_status_auth` test) leaked into a PR whose stated
scope is only the SQLite literal escaping fix.

That change is also incorrect: interserver authentication is performed
per-query via the cluster-secret hash computed over the query data, which is
verified in `receiveQuery` where `is_interserver_authenticated` is set. A
`TablesStatusRequest` is a standalone packet that arrives before any query, so
`is_interserver_authenticated` is always `false` at that point. The check
therefore rejected every legitimate interserver `TablesStatusRequest`, breaking
distributed queries (`ALL_CONNECTION_TRIES_FAILED` /
`ATTEMPT_TO_READ_AFTER_EOF`) across ~135 integration tests.

The interserver fix is tracked separately in PR #99854; revert it here so this
PR contains only the SQLite escaping change.

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

…literals

Two correctness gaps in the SQLite `WHERE`-predicate pushdown escaping were
flagged in review:

1. NUL bytes. `writeQuotedStringSQLite` previously emitted the two-char sequence
   `\0` for an embedded NUL. SQLite cannot represent NUL in a string literal at
   all (`sqlite3GetToken` terminates the literal on `c==0`), so this produced a
   literal that can never match the intended value — a silent wrong result.
   Now such predicates are not pushed down: `isCompatible` rejects string
   literals containing NUL (including nested in `IN` tuples / arrays / maps) when
   the escaping style is `SQLite`, so ClickHouse evaluates them instead (or, with
   `external_table_strict_query=1`, the query fails explicitly). As a
   defense-in-depth backstop, `FieldVisitorToStringSQLite` throws if a NUL
   literal is ever formatted, and `writeQuotedStringSQLite` no longer emits the
   mismatching `\0`.

2. `IN` / `notIn` tuple literals. Scalar `IN` lists are kept as a single
   `ASTLiteral(Tuple)`, which previously hit the template fallback in
   `FieldVisitorToStringSQLite` and was formatted by `FieldVisitorToString` with
   regular backslash escaping — reintroducing the very syntax errors / wrong
   results this change fixes (e.g. `val IN ('it''s', 'a\tb')`).
   `FieldVisitorToStringSQLite` now handles `Array`/`Tuple`/`Map` explicitly and
   recurses into itself, so every nested string element is escaped for SQLite.

`04141_sqlite_where_pushdown_escaping` is extended with `IN` / `NOT IN`
special-character cases and NUL-byte cases (both default and strict pushdown).

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

…escaping-pushdown

# Conflicts:
#	src/Storages/StorageSQLite.cpp

The pushed-down WHERE path already reserializes literals with
LiteralEscapingStyle::SQLite, but the (SELECT ...) table argument of
the sqlite table function and of ENGINE = SQLite still went through
tryGetExternalDatabaseQuery with LiteralEscapingStyle::Regular, so a
literal like 'it''s' reached SQLite as 'it\'s' — a syntax error or a
wrong match, the same bug this PR fixes for predicates.

Addresses the review comment from 2026-07-02 on
#104217

…kSize

The recursive Array/Tuple/Map specializations of FieldVisitorToStringSQLite
lacked the checkStackSize guard that FieldVisitorToString has, so a deeply
nested literal could exhaust the stack instead of throwing TOO_DEEP_RECURSION.

Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>

The helper recursively walks Tuple/Array/Map `Field` containers without the
`checkStackSize()` guard that `FieldVisitorToString` and the SQLite literal
visitor use, so a deeply nested literal in a SQLite-pushed predicate could
exhaust the thread stack here before reaching the guarded formatting.

Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>

# Conflicts:
#	src/Storages/transformQueryForExternalDatabase.cpp

…uments

`tryGetExternalDatabaseQuery` formats a user-provided `(SELECT ...)` table
argument straight from the parsed AST, so it bypassed the single-row tuple-set
normalization that `transformQueryForExternalDatabase` applies to `IN`. As a
result `sqlite('db', (SELECT ... WHERE (a, b) IN ((1, 'x'))))` re-serialized as
`... IN (1, 'x')` — the row value collapsed into a scalar list — and SQLite
rejected it with `SQL logic error` (`IN(...) element has 1 term - expected 2`).

Extract the existing wrap logic in `isCompatible` into a shared
`wrapSingleRowTupleSetForINNode`, add a recursive `wrapSingleRowTupleSetsForIN`,
and apply it (on a clone, leaving the user's AST untouched) to the subquery
before formatting it for the external database. Extend
`04493_sqlite_subquery_literal_escaping` with a multi-column single-row `IN`
case (plus a multi-row control, which was already correct).

Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>

…escaping-pushdown

`04341_sqlite_query_passing` exercised the query-backed SQLite source only with
numeric filters, so the `LiteralEscapingStyle::SQLite` escaping on the subquery
path (`StorageSQLite`/`TableFunctionSQLite` `tryGetExternalDatabaseQuery`) was
untested. Add a table with a single-quote and a tab value and filter it through
the `(SELECT ... WHERE s = 'it''s')` subquery form so a regression back to
backslash escaping (which SQLite rejects) is caught.

Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>

…ize` and prove NUL-byte local evaluation

The AST walked by `wrapSingleRowTupleSetsForIN` comes from a user query, so its
depth is unbounded; without `checkStackSize` a deep `(SELECT ...)` table argument
could exhaust the stack instead of throwing `TOO_DEEP_RECURSION`.

The NUL-byte test only asserted `count() = 0` over a fixture with no NUL-containing
row, which also passes if the predicate is wrongly pushed down as the two-character
string `\\0`. Add a SQLite table holding a value with an actual embedded NUL and
assert the non-strict query matches it.

…escaping-pushdown

…escaping-pushdown

…ively

The AI review's remaining major: `FieldVisitorToStringPostgreSQL` only
overrode the top-level `String` case, so `Array` / `Tuple` / `Map`
elements still went through the plain `FieldVisitorToString`. Both
`StoragePostgreSQL` and `tryGetExternalDatabaseQuery` request
`LiteralEscapingStyle::PostgreSQL`, so a pushed-down `val IN ('it''s',
'a\tb')` or a query-backed `(SELECT ...)` table argument kept ClickHouse
backslash escaping for the nested strings, which PostgreSQL interprets
differently or rejects.

Factor the recursive container handling that was added for SQLite into a
shared `FieldVisitorToStringForDialect` base and derive both
`FieldVisitorToStringPostgreSQL` and `FieldVisitorToStringSQLite` from
it, so once a target dialect is selected, nested literals stay in that
dialect all the way down. `Object` is now quoted with the target
dialect's rules too. `checkStackSize` is kept on every container path.

Add `gtest_literal_escaping_style.cpp` covering top-level strings,
strings inside tuples (including the single-element `tuple(...)` form),
nested containers, and the SQLite NUL-byte rejection inside a container.

…escaping-pushdown

…r bugfix validation

The `Bugfix validation (unit tests)` CI job overlays the PR's unit-test files
onto the merge-base sources and builds `unit_tests_dbms` without the fix. The
test referenced `LiteralEscapingStyle::SQLite` directly, an enumerator that
does not exist before this PR, so the before-binary failed to compile and the
check was reported as inconclusive.

Keep every reference to the new enumerator dependent on a template parameter
(`formatSQLite` with a `requires` check), so the test compiles on the
merge-base: the SQLite expectations evaporate there, while the PostgreSQL
nested-literal expectations still fail on the merge-base and reproduce the bug
(verified locally: 5 of 6 cases fail with master sources, all 6 pass with the
fix).

Report: https://s3.amazonaws.com/clickhouse-test-reports/json.html?PR=104217&sha=6f2d55e6abe332d6a91c88f1a089feea9d401a99&name_0=PR&name_1=Bugfix%20validation%20%28unit%20tests%29
PR: #104217

Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>

…escaping-pushdown

… external database pushdown

`isCompatible` only rejected a top-level `Array` literal, so a predicate
like `(id, arr_col) IN ((1, [1, 2]))` was still considered
pushdown-compatible and reserialized with ClickHouse `[...]` syntax,
which external databases (e.g. PostgreSQL) reject. Recurse into `Tuple`
literals and reject `Array` / `Map` at any depth (including a top-level
`Map`, which was not rejected before either), so such predicates are
evaluated by ClickHouse locally instead.

Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>

…escaping-pushdown

…characters

A pushed-down string containing a real tab was emitted as '\''a\tb'\'', which a
standard-conforming PostgreSQL server reads back as the two characters \t, so
such predicates silently compared against the wrong value. Strings that need
backslash escapes are now written as escape string constants (E'\''...'\''), whose
interpretation does not depend on standard_conforming_strings; plain strings
keep the doubled-quote literal form. Predicates whose string literals contain
a NUL byte are no longer pushed down to PostgreSQL (a PostgreSQL string value
cannot contain NUL), matching the SQLite behavior, and
FieldVisitorToStringPostgreSQL fails explicitly if one is ever formatted.

#104217

Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>

…escaping-pushdown

The user-provided `(SELECT ...)` table argument of `sqlite` / `postgresql` is
re-serialized from the parsed AST and sent to the external database as is, so
expressions that only have a ClickHouse-specific text form must not leak into
that SQL:

- `FieldVisitorToStringForDialect` now throws `BAD_ARGUMENTS` for `Array` /
  `Map` literals and for tuples with fewer than two elements (which could only
  be written back as `tuple(...)`) instead of emitting them in ClickHouse
  syntax that PostgreSQL / SQLite cannot parse.
- `wrapSingleRowTupleSetsForIN` is generalized into
  `normalizeSubqueryForExternalDatabase`: for non-`Regular` escaping styles an
  explicit `tuple` call with at least two arguments is marked to be formatted
  in the parenthesized row-value form `(a, b)`, while `tuple` with fewer than
  two arguments and `array` / `map` calls throw `BAD_ARGUMENTS`.

#104217

…escaping-pushdown

…w IN set

`wrapSingleRowTupleSetForINNode` only recognized the parser's fast-path
carrier `ASTLiteral(Tuple)`. When the same single-row multi-column IN set
was written with an explicit function call, `(id, val) IN (tuple(1, 'x'))`,
the RHS stayed an `ASTFunction` and the later `tuple` normalization turned
it into the operator form, collapsing the row to a scalar list:
`IN (1, 'x')` instead of `IN ((1, 'x'))` - on both the pushed-down
predicate path and the raw `(SELECT ...)` table-argument path.

Treat an explicit RHS `tuple(...)` whose first element is not itself a
tuple as the same single-row set and wrap it before formatting. Also clear
the `parenthesized` flag on that carrier: the user's own parentheses in
`IN (tuple(...))` would otherwise duplicate the grouping parens and
produce `IN (((1, 'x')))`.

Add gtest regressions for the explicit carrier (single-row and multi-row)
and a stateless-test case on the raw `(SELECT ...)` path.

#104217

Without the user's own parentheses around the right-hand side there is no
`parenthesized` flag to accidentally supply the grouping, so on the
previous code this variant collapsed to `IN ('foo', 'bar')` on the old
analyzer path as well - pin the fixed output.

#104217

…escaping-pushdown

…escaping-pushdown

…ntexts

External databases accept the row value `(a, b)` only as an operand of a
comparison or `IN` (SQLite reports "row value misused" for `SELECT (1, 2)`),
so `normalizeSubqueryForExternalDatabase` now tracks the position while
walking the subquery: the `tuple` call is switched to the operator form only
in row-value-safe contexts, and both the call and the equivalent `Tuple`
literal throw `BAD_ARGUMENTS` anywhere else instead of being sent as broken
SQL.

Also unwrap the internal `_CAST(literal, 'Type')` call that the analyzer's
`ConstantNode::toAST` puts around a tuple literal used as a plain expression
operand (e.g. `WHERE (id, val) = (2, 'y')`) when it re-serializes the
subquery argument from the query tree - previously that leaked `_CAST` into
the SQL sent to the external database.

…escaping-pushdown

…osition

PostgreSQL row constructors are ordinary value expressions, valid outside
comparisons and `IN` too (`SELECT (a, b)`, `WHERE (a, b) IS NOT NULL`), so the
row-value position restriction in `normalizeSubqueryForExternalDatabase` now
applies only to the SQLite / MySQL dialects, where the row value `(a, b)` is
accepted only next to a comparison or `IN`.

Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>

…escaping-pushdown

…arison

`normalizeSubqueryForExternalDatabase` rewrote `tuple(a, b)` into the row value
`(a, b)` only next to `=`, `!=`, `<`, `<=`, `>`, `>=` and `IN`, so a query-backed
MySQL source such as `(SELECT * FROM t WHERE tuple(a, b) <=> tuple(1, 2))` fell
through to the generic `Disallowed` recursion and threw `BAD_ARGUMENTS`, even
though MySQL accepts row values next to its NULL-safe equality `<=>`. Handle
`isNotDistinctFrom` like the other comparison operators and pin it in the
`QueryTableArgumentForMySQL` gtest.

…escaping-pushdown

…ushdown path too

`normalizeSubqueryForExternalDatabase` tracked where the external database
accepts the row value `(a, b)` (next to a comparison or `IN`; anywhere for
PostgreSQL), but `isCompatible` - the normal outer-`WHERE` pushdown path - did
not. So `WHERE tuple(id, val) IS NOT NULL` was still considered compatible and
pushed down as `("id", "val") IS NOT NULL`, which SQLite rejects with
`row value misused` (and MySQL with "Operand should contain 1 column(s)").

`isCompatible` now carries the same `RowValueContext`: the operands of a
comparison are row-value positions, the left-hand side of `IN` is one and its
right-hand side is the `IN` set whose elements are rows, and everywhere else a
multi-column tuple (either an `ASTFunction("tuple")` or a folded `Tuple`
literal) makes the predicate non-compatible, so it is evaluated by ClickHouse
instead of being sent as broken SQL. A new `BooleanPredicate` context marks the
pushed-down `WHERE` itself and the operands of `AND` / `OR` / `NOT`: no external
database accepts a row value as a condition, not even PostgreSQL, whose row
constructors are otherwise ordinary value expressions. A tuple used as the whole
condition is ClickHouse's list-of-predicates form and is still pushed down as a
conjunction by the existing splitting path.

Also added `checkStackSize` to `isCompatible`, which recurses over a
user-controlled AST.

New `TransformQueryForExternalDatabase.RowValueOutsideComparison` regression,
and the gtest helpers accept a `LiteralEscapingStyle` so the PostgreSQL
behaviour is covered too.

…escaping-pushdown

…rguments

The raw `(SELECT ...)` table-argument walk never tracked boolean
positions, so ClickHouse's list-of-predicates form `WHERE (a > 0, b > 10)`
was treated as a general row value: for PostgreSQL
`rowValueIsValidAnywhere` let it through and the formatted SQL kept the
record form, which PostgreSQL rejects ("argument of WHERE must be type
boolean, not type record").

`normalizeSubqueryForExternalDatabaseImpl` now walks the `WHERE` /
`PREWHERE` / `HAVING` / `QUALIFY` clauses of a select and the operands of
`AND` / `OR` / `NOT` with `RowValueContext::BooleanPredicate`, and lowers
a `tuple` call there to a conjunction - the same way the
predicate-pushdown path rewrites `WHERE (a > 0, b > 10)` to
`WHERE (a > 0) AND (b > 10)` - for every dialect. A single-predicate
`tuple` call is unwrapped to the predicate itself, and the folded `Tuple`
literal carrier of constants stays rejected for every dialect, including
PostgreSQL.

Regression: `TransformQueryForExternalDatabase.QueryTableArgumentBooleanPredicate`.

…lite` query argument

…escaping-pushdown

The `arm_tidy` build failed on the new NUL-byte checks: use `contains`
instead of `find != npos`.

https://s3.amazonaws.com/clickhouse-test-reports/json.html?PR=104217&sha=e467ef390d1e4bc63bf54600b8ac6ff68882dfb6&name_0=PR&name_1=Build%20%28arm_tidy%29
#104217

`normalizeSubqueryForExternalDatabase` left the `PREWHERE` clause of a
parsed `(SELECT ...)` table argument intact, so it was serialized back
as `PREWHERE`, which no external database can parse. Lower it into
`WHERE` (merging with an existing `WHERE` via `AND`) before the walk,
so the lowered filter also gets the boolean-position normalization.

…caping

The bridge protocol only reports the identifier quoting style, not the
literal escaping dialect of the remote database, so plumbing a
dialect-aware escaping style through XDBC needs a bridge protocol
extension and is out of scope here; the call site keeps the historical
behavior and now says so explicitly.

…escaping-pushdown

The UUID range-comparison check walks the user-controlled AST recursively
before `isCompatible`'s own guarded walk, so a deeply nested predicate could
exhaust the worker stack instead of failing with `TOO_DEEP_RECURSION`. Add
the same `checkStackSize()` guard as in the other recursive helpers in this
file (`fieldHasStringWithNulByte`, `fieldContainsArrayOrMap`,
`fieldRequiresClickHouseOnlySyntax`).

Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>

…escaping-pushdown

…ing-pushdown

Fix SQLite WHERE predicate pushdown for strings with special characters