terrymanu commented on issue #32280:
URL:
https://github.com/apache/shardingsphere/issues/32280#issuecomment-3641307879
## Problem Understanding
- Executing INSERT ... ON CONFLICT ... DO UPDATE ... WHERE ... with
placeholders in PostgreSQL triggers No value specified for parameter (e.g.,
“parameter 3 not set”).
- Placeholders appear across VALUES, SET, and the ON CONFLICT WHERE
clause; some samples also show duplicated or missing setXxx bindings.
## Root Cause
- The PostgreSQL visitor’s visitOptOnConflict only collects setClauseList
and does not traverse the ON CONFLICT target and its WHERE. Placeholders inside
that WHERE are never added to parameterMarkerSegments, so fewer parameters are
passed than the SQL requires, causing
the error.
- If application code skips a binding (e.g., sets parameter 2 twice and
never sets parameter 3), the same exception occurs and should be checked in
parallel.
## Analysis
- Parameter pushdown relies on the parser to collect every placeholder
position; missing placeholders inside ON CONFLICT ... WHERE ... is a parser gap.
- Official syntax reference (placeholders must be fully bound):
https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT
## Conclusion
- Missing traversal in ON CONFLICT leads to uncounted placeholders and
mismatched bindings. Applications should also ensure every placeholder is bound
exactly once.
- Fix suggestion: in
parser/sql/engine/dialect/postgresql/.../PostgreSQLStatementVisitor.java,
extend visitOptOnConflict to traverse the ON CONFLICT target and WHERE, adding
their parameter markers to parameterMarkerSegments; add unit/E2E tests covering
placeholders in
VALUES, SET, and WHERE to validate counts.
- We warmly welcome community contributors to submit a PR with the parser
enhancement and tests; we’ll gladly review and appreciate your help.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
