fix: strip RETURNING from translated optimized_sql, not from original_sql plan

tom-dyar · tom-dyar · commit 1ba30c8b8027 · 2026-03-09T15:25:24.000-07:00
_prepare_sql step 5: plan.stripped_sql was built from original_sql (untranslated),
so using it to overwrite optimized_sql discarded the entire normalization pipeline.
optimized_sql still had DEFAULT, $1 params, unquoted schema names etc. → SQLCODE -12.

Fix: when original_sql is provided, call ReturningPlan._strip_clauses() on
optimized_sql directly using plan.returning_clause and plan.on_conflict_clause
(metadata from original_sql, stripping applied to translated form).

Bumps version to 1.5.7.
diff --git a/CHANGELOG.md b/CHANGELOG.md
@@ -7,6 +7,11 @@ and this project adheres to [Semantic Versioning](https://semver.org/spec/v2.0.0
 
 ## [Unreleased]
 
+## [1.5.7] - 2026-03-09
+
+### Fixed
+- **`_prepare_sql` overwrote translated SQL with untranslated original after RETURNING strip**: After the 1.5.6 fix, `_prepare_sql` correctly used `original_sql` for `ReturningPlan.from_sql` RETURNING detection. However, line 699 then unconditionally did `optimized_sql = plan.stripped_sql`. Since `plan` was built from `original_sql`, `plan.stripped_sql` was the *original* (untranslated) SQL with just `RETURNING` removed — still containing `DEFAULT`, `$1`-style params, unquoted schema names, etc. This overwrote the correctly-translated `optimized_sql` from the normalization pipeline, causing IRIS to receive e.g. `INSERT INTO "SESSION" (...) VALUES (..., DEFAULT)` → `SQLCODE -12`. Fixed by applying `ReturningPlan._strip_clauses()` directly to `optimized_sql` when `original_sql` was provided, rather than using `plan.stripped_sql`. The `else` branch (no `original_sql`) continues to use `plan.stripped_sql` as before.
+
 ## [1.5.6] - 2026-03-09
 
 ### Fixed
diff --git a/src/iris_pgwire/__init__.py b/src/iris_pgwire/__init__.py
@@ -6,7 +6,7 @@
 caretdev/sqlalchemy-iris.
 """
 
-__version__ = "1.5.6"
+__version__ = "1.5.7"
 __author__ = "Thomas Dyar <thomas.dyar@intersystems.com>"
 
 # Don't import server/protocol in __init__ to avoid sys.modules conflicts
diff --git a/src/iris_pgwire/iris_executor.py b/src/iris_pgwire/iris_executor.py
@@ -696,7 +696,18 @@ def _prepare_sql(
                 columns=plan.columns,
                 session_id=session_id,
             )
-        optimized_sql = plan.stripped_sql
+        # Strip RETURNING / ON CONFLICT from the *translated* optimized_sql.
+        # When original_sql was provided, plan.stripped_sql is the original (untranslated)
+        # SQL with only RETURNING removed — we must NOT use it to overwrite optimized_sql or
+        # we'd undo the entire normalization pipeline (DEFAULT, $1 params, schema names, etc.
+        # still present → IRIS SQLCODE -12).
+        # Instead, apply the same stripping regex directly to optimized_sql.
+        if original_sql:
+            optimized_sql = ReturningPlan._strip_clauses(
+                optimized_sql, plan.returning_clause, plan.on_conflict_clause
+            )
+        else:
+            optimized_sql = plan.stripped_sql
 
         # 6. Semicolon Stripping
         optimized_sql = optimized_sql.strip().rstrip(";")
