Issue while parsing "END CASE" in BigQuery stored procedure

[kankaiz]

Which version and edition of Flyway are you using?

9.22.0

If this is not the latest version, can you reproduce the issue with the latest one as well? (Many bugs are fixed in newer releases and upgrading will often resolve the issue)

Which client are you using? (Command-line, Java API, Maven plugin, Gradle plugin)

command-line

Which database are you using? (Type & version)

BigQuery

Which operating system are you using?

macOS

What did you do? (Please include the content causing the issue, any relevant configuration settings, the SQL statement(s) that failed (if any), and the command you ran)

It looks like there's an issue in flyway while parsing the CASE and END CASE block in BigQuery migration.

The test procedure I've defined looks like this

CREATE 
OR REPLACE PROCEDURE `${project}.${dataset}.debug_flyway`(testVar STRING)
BEGIN

DECLARE _testVar STRING DEFAULT testVar;

CASE _testVar
    WHEN "test_a" THEN
        SELECT "test_a";
    WHEN "test_b" THEN
        SELECT "test_b";
    ELSE
        SELECT "not found";
END CASE;


END;

However, after running flyway migrate, I've got error message

"exception": "Migration R__debug_flyway.sql failed\n------------------------------------\n
SQL State  : HY000\nError Code : 100032\nMessage    : [Simba][BigQueryJDBCDriver](100032) Error executing query job. Message: Syntax error: Expected \";\" but got end of script at [14:9]\nLocation   : sql/R__debug_flyway.sql (/flyway/sql/R__debug_flyway.sql)\nLine       : 1\n
Statement  : CREATE \nOR REPLACE PROCEDURE `****.st_datamesh.debug_flyway`(testVar STRING)\nBEGIN\n\nDECLARE _testVar STRING DEFAULT testVar;\n\nCASE _testVar\n    WHEN \"test_a\" THEN\n        SELECT \"test_a\";\n    WHEN \"test_b\" THEN\n        SELECT \"test_b\";\n    ELSE\n        SELECT \"not found\";\nEND CASE\n",

As shown in the exception, it seems like flyway has only parsed the procedure to END CASE; not to the end of the PROCEDURE definition.

tested the stored procedure in BQ

BigQuery doc for CASE syntax
https://cloud.google.com/bigquery/docs/reference/standard-sql/procedural-language#case

full logs:

Oct 02, 2023 5:07:53 AM org.flywaydb.core.internal.command.DbMigrate info
INFO: Migrating schema `st_datamesh` with repeatable migration "debug flyway" [non-transactional]
Oct 02, 2023 5:07:54 AM org.flywaydb.core.internal.command.DbMigrate error
SEVERE: Migration of schema `st_datamesh` with repeatable migration "debug flyway" [non-transactional] failed! Please restore backups and roll back database and code!
Oct 02, 2023 5:08:13 AM org.flywaydb.core.internal.jdbc.PlainExecutionTemplate error
SEVERE: Failed to execute operation in non-transactional context. Please restore backups and roll back database and code!
Oct 02, 2023 5:08:15 AM org.flywaydb.commandline.utils.OperationsReportUtils warn
WARNING: Unable to create default report files.
{
  "error": {
    "errorCode": "FAILED_REPEATABLE_MIGRATION",
    "message": "Migration R__debug_flyway.sql failed\n------------------------------------\nSQL State  : HY000\nError Code : 100032\nMessage    : [Simba][BigQueryJDBCDriver](100032) Error executing query job. Message: Syntax error: Expected \";\" but got end of script at [14:9]\nLocation   : sql/R__debug_flyway.sql (/flyway/sql/R__debug_flyway.sql)\nLine       : 1\nStatement  : CREATE \nOR REPLACE PROCEDURE `****.st_datamesh.debug_flyway`(testVar STRING)\nBEGIN\n\nDECLARE _testVar STRING DEFAULT testVar;\n\nCASE _testVar\n    WHEN \"test_a\" THEN\n        SELECT \"test_a\";\n    WHEN \"test_b\" THEN\n        SELECT \"test_b\";\n    ELSE\n        SELECT \"not found\";\nEND CASE\n",
    "stackTrace": null,
    "lineNumber": 1,
    "path": "/flyway/sql/R__debug_flyway.sql"
  },
  "initialSchemaVersion": "000004",
  "targetSchemaVersion": null,
  "schemaName": "st_datamesh",
  "migrations": [
    {
      "category": "Versioned",
      "version": "000005",
      "description": "add sync log table",
      "type": "SQL",
      "filepath": "/flyway/sql/V000005__add_****_table.sql",
      "executionTime": 680
    }
  ],
  "migrationsExecuted": 1,
  "success": false,
  "flywayVersion": "9.22.0",
  "database": "****",
  "warnings": [],
  "timestamp": "2023-10-02T05:06:41.103685",
  "operation": "migrate",
  "exception": "Migration R__debug_flyway.sql failed\n------------------------------------\nSQL State  : HY000\nError Code : 100032\nMessage    : [Simba][BigQueryJDBCDriver](100032) Error executing query job. Message: Syntax error: Expected \";\" but got end of script at [14:9]\nLocation   : sql/R__debug_flyway.sql (/flyway/sql/R__debug_flyway.sql)\nLine       : 1\nStatement  : CREATE \nOR REPLACE PROCEDURE `****.st_datamesh.debug_flyway`(testVar STRING)\nBEGIN\n\nDECLARE _testVar STRING DEFAULT testVar;\n\nCASE _testVar\n    WHEN \"test_a\" THEN\n        SELECT \"test_a\";\n    WHEN \"test_b\" THEN\n        SELECT \"test_b\";\n    ELSE\n        SELECT \"not found\";\nEND CASE\n",
  "licenseFailed": false
}
Oct 02, 2023 5:08:15 AM org.flywaydb.commandline.Main info
INFO: Flyway is performing some final checks. Thank you for your patience.
make: *** [migrate-flyway-st] Error 1

[JasonLuo-Redgate]

Hi @kankaiz , many thanks for finding this. We'll log this issue and get it fixed.

[kankaiz]

thanks @JasonLuo-Redgate
actually I have raised a PR #3753 to fix that.
you can also have a look when you are working on this bug.

[JasonLuo-Redgate]

Yes, @kankaiz , many thanks for the PR. We will take that fix and include it in our next release. 👍

[kankaiz]

great. thanks @JasonLuo-Redgate
am also glad I could contribute to this awesome tool repo.