paginate()/_create_count_query() fails with "Parameter count mismatch" on a repeated named parameter

[cofin]

Summary

select_with_total() / _create_count_query() raises Parameter count mismatch for any paginated query that references the same named parameter more than once (e.g. WHERE a = :p OR b = :p). The base statement compiles and executes fine — the single named value is correctly expanded to every placeholder position — but the count-query rebuild used by pagination rejects it.

This is not CTE-specific (a plain WHERE … OR … reproduces it) and is distinct from the already-fixed #301 (CTE structure in the count wrapper) and #379 (multiple distinct search fields). The trigger here is a single, repeated same-named parameter.

Environment

• sqlspec==0.48.2
• Python 3.12
• Reproduces adapter-independently (sqlite below; originally hit on asyncpg)

MCVE

Minimal — no CTE, no JOIN:

from sqlspec import SQL
from sqlspec.adapters.sqlite import SqliteConfig

q = "SELECT id FROM t WHERE a = :wid OR b = :wid"   # same named param, twice

# 1) Direct compile: the repeated named param expands correctly
print(SQL(q, wid="W").compile())        # -> ('SELECT id FROM t WHERE a = ? OR b = ?', ('W', 'W'))  ✅

# 2) The same statement through pagination's count-query builder -> raises
cfg = SqliteConfig(pool_config={"database": ":memory:"})
with cfg.provide_session() as drv:
    drv._create_count_query(SQL(q, wid="W")).compile()
    # sqlspec.exceptions.SQLSpecError:
    #   Parameter count mismatch: 1 parameters provided but 2 placeholders detected.

select_with_total() / paginate() call _create_count_query() internally, so any paginate()
over a query with a repeated named parameter fails. Confirmed failing variants (all identical error):

Query	_create_count_query
SELECT id FROM t WHERE a = :wid OR b = :wid (plain)	❌
SELECT x.id FROM t x JOIN u ON x.id=u.id WHERE x.a = :wid OR u.b = :wid (join)	❌
WITH s AS (SELECT id FROM t WHERE owner = :wid) SELECT x.id FROM t x JOIN s ON x.id=s.id WHERE x.owner = :wid (cte)	❌
SELECT id FROM t WHERE a = :wid (single occurrence)	✅

count_with_window=True does not help — _add_count_over_column() rebuilds from the expression
the same way and fails identically.

Expected

The count query should compile/execute like the base statement: one provided value for :wid
expands to every placeholder position (('W', 'W')).

Actual

sqlspec.exceptions.SQLSpecError: Parameter count mismatch: 1 parameters provided but 2 placeholders detected.

Root cause (analysis)

Both branches of _create_count_query() (sqlspec/driver/_common.py) — the subquery-wrap branch
(used when GROUP BY / JOIN / CTE is present) and the direct-count branch (plain queries) — end by
reconstructing the count statement from a sqlglot expression while re-binding parameters from the
name-keyed original_sql.named_parameters dict:

filtered_named_params = {k: v for k, v in original_sql.named_parameters.items()
                         if k not in pagination_params}
return SQL(count_expr, *original_sql.positional_parameters,
           statement_config=original_sql.statement_config, **filtered_named_params)

For a repeated :wid, named_parameters holds a single entry {"wid": "W"}, but count_expr
contains two :wid placeholder nodes. When SQL() is built from an expression (rather than a
string), parameter-alignment validation counts the raw placeholder nodes (2) and does not expand
the single repeated named value the way the string-input path does (step 1 above) — so it reports a
1-vs-2 mismatch. The two construction paths disagree on how a repeated named parameter maps to
placeholders.

A fix likely needs the expression-input alignment to expand a single named value across all
identically-named placeholders (matching string-input behavior), or _create_count_query() to
materialize per-placeholder bindings before re-wrapping.

Workaround

Ensure the paginated statement references each named parameter at most once, or bind positionally so
the single value lines up with the placeholder count.

Related

• select_with_total() generates invalid SQL for queries with CTEs (WITH clause) #301 select_with_total() invalid SQL for CTE queries (structure — fixed)
• SearchFilter on multi-field named SQL can fail with asyncpg parameter mismatch #379 SearchFilter on multi-field named SQL parameter mismatch (distinct fields — fixed)
• paginate() / _create_count_query() fails on UNION ALL queries #373 paginate() / _create_count_query() on UNION ALL