PostgreSQL `cust_with_values` fails on ARRAY[...] literals (placeholder swallowing)

[cavebatsofware]

Bug Report: cust_with_values drops placeholders inside [...] groups (e.g. ARRAY[...])

Description

Expr::cust_with_values silently drops bind values whose $N (or ?) placeholders are written inside a [...] group, most notably a PostgreSQL ARRAY[...] literal or a subscript expression. The rendered SQL contains a stray [...] substring instead of the substituted placeholders, the values vector is shorter than the prepared statement expects, and execution fails with a parameter-count mismatch (or, in the worst case, binds against the wrong column when the count happens to line up).

Root cause: the SQL tokenizer in src/token.rs treats [ as a string-quoting delimiter for every backend. Inside Expr::cust_with_values, the tokenizer is used to walk the input and substitute $N / ?. Anything between [ and ] is consumed as a single Token::Quoted and never reaches the placeholder-substitution loop, so the placeholders are emitted verbatim and the corresponding values stay in the input vec instead of being routed into the bind vec at the right offset.

The [col] bracketed-identifier convention only exists in MSSQL / T-SQL, and as an MS Access-compatibility mode in SQLite. MySQL has no [col] syntax (it uses backticks). PostgreSQL not only doesn't use [col] but actively uses [...] for ARRAY literals and subscript syntax (arr[1], arr[1:3]), which is where this bug is most visible.

Steps to Reproduce

let (statement, values) = Query::select()
    .column(Char::Character)
    .from(Char::Table)
    .and_where(Expr::cust_with_values(
        "tags @> ARRAY[$1, $1]",
        ["needle"],
    ))
    .build(PostgresQueryBuilder);

println!("{statement}");
println!("{values:?}");

Expected Behavior

SELECT "character" FROM "character" WHERE tags @> ARRAY[$1, $2]
values: ["needle", "needle"]

Actual Behavior (1.0.0-rc.34, current master)

SELECT "character" FROM "character" WHERE tags @> ARRAY[$1, $1]
values: []

PostgreSQL rejects the prepared statement with a parameter-count mismatch. The same failure shape applies to any cust_with_values input that places $N / ? inside [...], e.g. paradedb / pg_search predicates like:

id @@@ paradedb.boolean(should => ARRAY[
    paradedb.match('body', $1, distance => 1),
    paradedb.match('body_ngram', $1)
])

Reproduces How Often

Always, whenever cust_with_values input contains a placeholder between [ and ].

Versions

Reproduced on sea-query 1.0.0-rc.34 (current master).

The same bug exists in the released 0.32.x line, but that release line lacks the TokenizerBackend machinery added since and needs a different fix shape. A separate issue / PR could cover the 0.32.x situation; this one is scoped to master.

Database: PostgreSQL 18 with paradedb / pg_search. OS: Linux.

Fix shape

A PR against master is on the way. The backend-aware TokenizerBackend::{Mysql, Postgres, Sqlite} plumbing already exists, so the fix is small: is_string_delimiter_start becomes a &self method that returns false for [ when self.backend == TokenizerBackend::Postgres. MySQL and SQLite are left byte-identical to today's behavior. A new tokenizer-level test (ARRAY[$1, $2] survives as separate tokens under Postgres) and an Expr::cust_with_values end-to-end regression test are included.

Additional Information

Workaround in user code: render the SeaQuery to SQL via build_any(...), splice the [...] bearing fragment in manually, and push the values onto the bind vec at the right offsets. It works but is messy and reqires handling raw string query fragments. Fixing this issue would eliminate such workarounds.