Fix SQLite WHERE predicate pushdown for strings with special characters - #104217

StorageSQLite::read used LiteralEscapingStyle::Regular, which escapes single quotes as \'. SQLite does not recognise backslash escapes; its only valid string escape is ''. A pushed-down predicate like WHERE col = 'it\'s' causes SQLite to parse 'it\' as a closed string and s' as a stray token — a SQL syntax error or injection vector.

Switching to LiteralEscapingStyle::PostgreSQL would fix single quotes but still emit \n, \r, \t as backslash sequences (which writeAnyEscapedString applies unconditionally). SQLite does not interpret those, so predicates on control-character strings would silently return no rows.

This PR adds a dedicated LiteralEscapingStyle::SQLite backed by writeQuotedStringSQLite: only ' → ''; all other bytes (including \, newline, tab) are embedded literally. NUL bytes cannot be embedded — SQLite's tokenizer loop in sqlite3GetToken terminates on c==0 even inside a string literal, returning TK_ILLEGAL — so a predicate whose string literal (possibly nested in an IN tuple, array or map) contains a NUL byte is not pushed down at all: ClickHouse evaluates it locally, and with external_table_strict_query = 1 the query is rejected instead of silently returning wrong rows.

This is a follow-up to PR #74144 which fixed the DDL/PRAGMA and INSERT paths for SQLite but left the SELECT pushdown path using the wrong escaping style.

During review the same class of bug was fixed on the PostgreSQL pushdown path as well: strings nested inside Array / Tuple / Map literals (e.g. the elements of a pushed-down IN list) now stay in the selected dialect all the way down instead of falling back to the regular ClickHouse escaping, and PostgreSQL string literals that contain backslashes or control characters are emitted as escape string constants (E'...'), so the server reads back exactly the original bytes regardless of standard_conforming_strings (a real tab used to be sent as the two characters \t). Predicates whose string literals contain a NUL byte are not pushed down to PostgreSQL either, since a PostgreSQL string value cannot contain NUL.

The same row-value restriction is applied on the normal WHERE pushdown path: a multi-column tuple is written as the row value (a, b), which SQLite and MySQL accept only next to a comparison or IN, so a predicate such as WHERE (id, val) IS NOT NULL is no longer pushed down to them (ClickHouse evaluates it, and with external_table_strict_query = 1 the query is rejected) instead of being sent as SQL the external database cannot parse (SQLite reports row value misused). For PostgreSQL, whose row constructors are ordinary value expressions, it is still pushed down. A tuple used as the whole condition is ClickHouse's list-of-predicates form and keeps being pushed down as a conjunction, WHERE ("a" > 0) AND ("column" > 10), for every dialect - no external database accepts a row value as a condition.

The user-provided (SELECT ...) table argument of sqlite / postgresql / mysql, which is re-serialized from the parsed AST and sent to the external database as is, no longer leaks ClickHouse-only syntax into that SQL: Array / Map literals and tuples with fewer than two elements (which could only be written back as tuple(...)) now throw BAD_ARGUMENTS instead of producing SQL the external database cannot parse, an explicit tuple(a, b) call is re-serialized as the parenthesized row value (a, b) - for SQLite and MySQL only in positions where those databases accept a row value (an operand of a comparison or IN); in any other position, such as the SELECT list, both the tuple(...) call and the equivalent tuple literal throw BAD_ARGUMENTS, because the parenthesized form is a syntax error there (SQLite reports row value misused). PostgreSQL row constructors are ordinary value expressions, valid in any expression position (SELECT (a, b), WHERE (a, b) IS NOT NULL), so for PostgreSQL such tuples are sent through as row values everywhere instead of being rejected - everywhere except a boolean position, since no database accepts a record as a condition. A tuple in a boolean position - the WHERE / HAVING of the passed query, or an operand of AND / OR / NOT - is ClickHouse's list-of-predicates form, and is lowered to a conjunction for every dialect: (SELECT ... WHERE (a > 0, b > 10)) reaches the external database as WHERE (a > 0) AND (b > 10), the same rewrite the normal pushdown path applies; PREWHERE, which is ClickHouse-only syntax no external database can parse, is lowered into WHERE on that path as well (merging with an existing WHERE via AND), and the lowered filter gets the same boolean-position normalization. the equivalent tuple literal of constants is not a list of predicates the external database could evaluate and throws BAD_ARGUMENTS there instead. array / map calls on that path are rejected for all three databases. The internal _CAST(literal, 'Type') wrapper 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 is unwrapped back to the literal, instead of leaking the ClickHouse-internal _CAST function into the SQL sent to the external database. A single-row multi-column IN set keeps its outer parentheses for both carriers - the fast-path literal (a, b) IN ((1, 'x')) and the explicit call (a, b) IN (tuple(1, 'x')) - so it reaches the external database as IN ((1, 'x')) instead of collapsing to the scalar list IN (1, 'x'). This normalization applies to MySQL as well: it shares the same re-serialization path, and although its Regular literal escaping style is correct for MySQL string literals (MySQL interprets backslash escapes like ClickHouse), the tuple(...) / array(...) / map(...) forms and Array / Map / single-element-tuple literals are not MySQL syntax either.

The JDBC/ODBC (StorageXDBC) pushdown path is intentionally out of scope: 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 it needs a bridge protocol extension. That path keeps the historical Regular escaping, and the limitation is now documented at the call site.

Changelog category (leave one):

• Bug Fix (user-visible misbehavior in an official stable release)

Changelog entry (a user-readable short description of the changes that goes into CHANGELOG.md):

Fixed incorrect SQL literal escaping in StorageSQLite and sqlite() table function when pushing WHERE predicates to SQLite: single quotes and control characters (\n, \r, \t, \) were escaped with backslashes, which SQLite does not interpret, causing syntax errors or wrong query results. Also fixed the escaping of string literals pushed down to PostgreSQL: strings nested inside IN lists kept ClickHouse escaping, and control characters were sent as backslash sequences that PostgreSQL reads back as different bytes.

Documentation entry for user-facing changes

• Documentation is written (mandatory for new features)

Version info

• Backported to: 26.7.7.60