config update: re-split script[] elements that contain multiple statements (gap beyond #245)

[beckavac]

Context

#245 (closed in v0.28.0) added auto-normalization script: str → array for SQL transformations via normalize_blocks_codes_script calling split_statements(). That closed the string-vs-array runtime crash.

There is a remaining gap on the same code path: when the input script is already a list but a single element contains multiple ;-separated statements, the normalizer short-circuits and never re-splits per-element.

Failure mode

Runtime crashes at job execution with:

Error "odbc_prepare(): SQL error: Actual statement count 2 did not match the desired statement count 1.,
SQL state 0A000 in SQLPrepare" while executing query "CREATE OR REPLACE TABLE ... ; alter session unset week_of_year_policy, week_start;"

ODBC SQLPrepare expects exactly 1 statement per script[i] element. Two statements glued by ; in one element pass the array-shape validator (it's still a list of strings), so the write lands as 200 OK and the version increments — crash happens only at job run time.

Root cause

src/keboola_agent_cli/sync/code_extraction.py line 173 in normalize_blocks_codes_script:

script = code.get("script")
if not isinstance(script, str):
    continue  # ← already a list → passed through unchanged, even if elements are malformed

The split_statements() state machine that #245 already wired up handles this case correctly — it just isn't invoked on per-element list contents.

Repro

Take any Snowflake transformation, put two ;-separated statements into one script[i] element via Storage API PUT (or have a third-party tool / older UI / hand-edited config produce that shape — both happen in practice). Storage API accepts it silently and the version increments. Run the job → the runtime error above.

A natural seed for this shape: legacy code where a CREATE OR REPLACE TABLE …; ALTER SESSION SET week_of_year_policy=1; pair was authored as one block, then ingested by something that wraps the whole block as a single array element.

Proposal

In the loop over codes, after the existing string-case branch, also pass each list element through split_statements() (SQL transformations only — the existing is_sql gate). If the element produces more than 1 statement, replace it in place with the multi-element split. Surface via the existing normalizations envelope with a new action label, e.g. sql_resplit, plus before_length=1 / after_length=N so the silent fix stays observable.

This keeps the helper's contract — "every list element is exactly one statement after kbagent writes it" — and reuses the same split_statements() logic that already respects '…' / \"…\" / \$\$…\$\$ / -- / # / // / /* … */.

Where this was hit

SK→CZ migration on 2026-05-11 of project 1507 transformation 745661351 (Data reports). Three elements contained CREATE OR REPLACE TABLE … ; alter session set/unset week_of_year_policy, week_start; glued in one element each:

• C0 "CZ Presidents" el[23] (out.proxy_zombici)
• C1 "SK Presidents" el[26] (tmp.sk_visits_cpc_ag_prep)
• C1 "SK Presidents" el[29] (out.sk_proxy_start)

These survived v513 → v515 because the array was already a list and only specific elements got rewritten — the offending elements were not touched by the migration text replacements but were re-pushed verbatim. Manual fix: split each element in two and push via kbagent config update --configuration-file ….

Related

• update_sql_transformation: validate / preserve script field as array (string causes runtime parse error) #245 — string → array normalization on write (shipped v0.28.0)
• sync push collapses transformation script[] array → runtime fails #119 — sync push collapsing script[] on the read/write boundary

[beckavac]

Update — same bug, broader manifestation than originally described

Filed this issue with CREATE TABLE … ; ALTER SESSION … as the trigger. Hit two more failures on the same config (1507 / 745661351) a few hours later that confirm the bug is not specific to ALTER SESSION — it's any second statement in a single script[] element.

Second failure (v515 → v516)

After splitting the 3 ; ALTER SESSION elements (v515), the next scheduler run crashed on a different code block with the same ODBC error:

Actual statement count 2 did not match the desired statement count 1, SQL state 0A000 in SQLPrepare
in "Users Consent"

Offending element: parameters.blocks[0].codes[14].script[0] — contained two statements:

CREATE OR REPLACE TABLE "out.users_consent_with_visit" AS
SELECT … ORDER BY 1,2;

SELECT
CONVERT_TIMEZONE('Europe/Prague', CURRENT_TIMESTAMP())::DATE - 1 AS "date"
, … FROM "users_2" u JOIN … ORDER BY 1,2;

/* CREATE OR REPLACE TABLe "out.user_consent_dw" AS … ;*/

Two real statements (CREATE + bare SELECT) plus a /* … */-commented third block. A keyword-anchored splitter that looks for CREATE|INSERT|UPDATE|DELETE|MERGE|ALTER|… at line starts misses this case — the second statement starts with select (lowercase, not in any reasonable statement-keyword list). The trailing comment block has to stay attached to the previous real statement, not become a standalone element.

Implication for the fix

The proposal in the issue still stands: pass each list element through split_statements(). But the detector that decides whether to invoke the re-split must not rely on statement-keyword anchoring — it has to use real ;-as-separator logic, the same state machine #245 already wired up:

• Walk the element character by character.
• Track open '…' / "…" / $$…$$ strings and -- / /* … */ comments — skip ; inside them.
• Every other ; is a candidate statement terminator.
• Group resulting parts: pure-whitespace / pure-comment chunks attach to the previous real statement (so a trailing /* … */ stays where the author put it).
• If the resulting group count > 1, re-split the element in place and emit a sql_resplit entry in normalizations.

Effectively this is split_statements() already, just applied per-element instead of only when the whole field is a string.

Why both manifestations slipped through #245

#245 normalizes when script is a str. Both cases here had script already as list — the malformed element was just one entry in the list. The string path runs the splitter; the list path does not. Same root cause, different surface symptoms.

Affected configs in this migration (today)

• 745661351 "Data reports" (analytick-t-m / 1507):
  • v515 hotfix: split 3× ; ALTER SESSION (C0 el[23], C1 el[26], C1 el[29])
  • v516 hotfix: split 1× ; SELECT (C14 el[0])
• 01k2hm1z3ffthv5mbbwjbvqa28 "Restricted cash" (slevomat / 226) — unrelated JOIN-ambiguity bug, mentioned only to confirm this manifestation is not project-specific.

Both ; ALTER and ; SELECT crashes had the same Storage API → runtime mismatch: PUT lands as 200 OK with version increment, crash only visible at scheduler-time. Detection on the write side via re-split would have prevented both.