Snowflake parser: nested BEGIN...EXCEPTION...END; sub-block inside CREATE TASK still truncates statement (unexpected '<EOF>')

Environment

• Flyway versions tested: 12.11.0, 13.2.0 (issue reproduces identically on both)
• Database: Snowflake
• Driver: bundled Snowflake JDBC driver shipped with each respective Flyway version
• Migration type: Repeatable migration (R__*.sql)

Summary

When a Snowflake Scripting CREATE TASK ... AS BEGIN ... END; body contains a nested plain BEGIN...EXCEPTION...END; sub-block (used for exception handling), Flyway's SQL statement parser truncates the statement before reaching the final closing END; of the outer block, and drops any statement(s) that follow it in the same migration file. The truncated SQL is sent to Snowflake, which fails with SQL compilation error: syntax error ... unexpected '<EOF>'.

This is a related but distinct case from #4179 / fixed by #4194 (which addressed nested IF/FOR/CASE blocks). That fix does not appear to extend to nested plain BEGIN...END (exception-handling) sub-blocks — this reproduces on 12.11.0 and 13.2.0, both well after the #4194 fix shipped in 12.0.0.

Minimal reproduction

CREATE OR REPLACE TASK my_schema.my_task
WAREHOUSE = 'MY_WH'
SCHEDULE = '5 MINUTE'
AS
BEGIN
    LET flag BOOLEAN := FALSE;
    BEGIN
        SELECT TRUE INTO :flag
        FROM my_schema.some_control_table
        WHERE some_condition = TRUE
        LIMIT 1;
    EXCEPTION
        WHEN OTHER THEN
            LET flag := FALSE;
    END;

    IF (:flag) THEN
        CALL SYSTEM$SET_RETURN_VALUE('a');
    ELSE
        IF ((SELECT COUNT(*) FROM my_schema.some_table) = 0) THEN
            CALL SYSTEM$SET_RETURN_VALUE('a');
        ELSE
            CALL SYSTEM$SET_RETURN_VALUE('b');
        END IF;
    END IF;
END;

SELECT SYSTEM$TASK_DEPENDENTS_ENABLE('my_schema.my_task');

Expected behavior

Flyway sends the full, intact CREATE OR REPLACE TASK ... statement (through its final END;) to Snowflake, followed by the trailing SELECT SYSTEM$TASK_DEPENDENTS_ENABLE(...) as a second statement.

Actual behavior

Flyway truncates the statement before the final END; and drops the trailing SELECT SYSTEM$TASK_DEPENDENTS_ENABLE(...) statement entirely. Snowflake receives an incomplete script and throws:

SQL State : 42000 Error Code : 1003 Message : SQL compilation error: syntax error line 34 at position 10 unexpected ''.

The captured Statement in Flyway's error output confirms the truncation point — it ends immediately after the second END IF (missing its semicolon), with the outer block's closing END; and the subsequent SELECT statement both absent from what was actually sent to Snowflake.

Suspected cause

Consistent with the mechanism described in #4179 (block-depth mis-tracking), but for a different construct: the parser's depth counter appears to correctly track the nested IF/END IF pairs (per the #4194 fix) but loses track of depth after passing through the nested plain BEGIN...EXCEPTION...END; sub-block, causing it to close the outer block one END; too early.

Workaround (confirmed working)

Wrapping the entire CREATE TASK statement (and any trailing statements that must execute atomically with it) inside EXECUTE IMMEDIATE $$ ... $$ avoids the issue, since Flyway then treats the whole payload as a single string literal and does not attempt to parse its internal structure:

EXECUTE IMMEDIATE $$
BEGIN
    CREATE OR REPLACE TASK my_schema.my_task
    ...
    AS
    BEGIN
        ...
    END;

    SELECT SYSTEM$TASK_DEPENDENTS_ENABLE('my_schema.my_task');
END;
$$;

Related

#4179 / #4194 — fixed nested IF/FOR/CASE tracking in Flyway 12.0.0; this report is for the still-reproducing nested BEGIN...EXCEPTION...END case.