EXEC_JOB_EXECUTION_ERR when using CASE statements in Stored Procedures

[SamPriyadarshi]

When creating Stored Procedures containing CASE statements like below, Liquibase throws liquibase.exception.DatabaseException and fails with EXEC_JOB_EXECUTION_ERR

Sample Stored Procedure

Note: All the referenced tables, udfs are already in place.

--liquibase formatted sql
--changeset gcp:1 runOnChange: true

CREATE OR REPLACE PROCEDURE `project.dataset.sp`(load_dt DATE)
BEGIN

  BEGIN TRANSACTION;

 --Full Refresh Table truncate
  TRUNCATE TABLE `project.dataset.sp`;

  --Insert into structured table
  INSERT INTO `project.dataset.sp` (
  SELECT
    `project.dataset.sp.udf_std_str_to_str`(epvid_evt_id) as event_id,
    `project.dataset.sp.udf_std_str_to_str`(epvid_vend_sbsy) as vendor_subsidiary,
    CASE WHEN epvid_vers_id LIKE '%-%' THEN NULL ELSE `project.dataset.sp.udf_std_str_to_str`(epvid_vers_id) END as version_id,
    `project.dataset.sp.udf_std_str_to_str`(epvid_itm_nbr) as item_nbr,
    `project.dataset.sp.udf_std_str_to_date`(epvid_trn_dte,'%Y-%m-%d') as transaction_date,
    `project.dataset.sp.udf_std_str_to_str`(epvid_rgn) as region_nbr,
    `project.dataset.sp.udf_std_str_to_str`(epvid_cur_itm_nbr) as current_item_nbr,
    epvid_act_itsl_dlr as actual_sale_dlr,
    epvid_act_itsl_unt as actual_sale_units,
    epvid_act_itmd_dlr as actual_markdown_dlr,
    epvid_act_itgm_dlr as actual_gross_margin_dlr,
    `project.dataset.sp.udf_std_str_to_str`(epvid_crt_opr_id) as create_operator_id,
    `project.dataset.sp.udf_std_str_to_date`(epvid_crt_dte,'%Y-%m-%d') as create_date,
    `project.dataset.sp.udf_std_str_to_datetime`(epvid_lst_mnt_tsmp,'%Y-%m-%d %H:%M:%E*S') as last_maintenance_timestamp,
    `project.dataset.sp.udf_std_str_to_str`(epvid_lst_opr_id) as last_op_id,
    `project.dataset.sp.udf_std_str_to_str`(epvid_lst_trn_cde) as last_trnsctn_code,
    `project.dataset.sp.udf_std_str_to_str`(epvid_grp_id) as grp_id,
    epvid_out_stk_ind as out_of_stock_ind,
    epvid_nbr_str_out as number_of_stores_out,
    `project.dataset.sp.udf_std_str_to_str`(epvid_pvt_itlb_ind) as pvt_lbl_ind,
    file_ingested_date,
    "dag_struct_load_evt_item_daily" as dag_id,
    current_datetime as created_datetime,
    current_datetime as updated_datetime
  FROM `otherproject.dataset.sp.event_item_daily`
  WHERE file_ingested_date = load_dt);

  COMMIT TRANSACTION;

  --Roll back transaction
  EXCEPTION WHEN ERROR THEN
  ROLLBACK TRANSACTION;

  RAISE USING MESSAGE = FORMAT("Error Message - %s, Error Statement - %s, Error Stack - %s", @@error.message, @@error.statement_text, @@error.formatted_stack_trace);

END;

Tested by removing CASE statement with below and after that the creation of stored procedure succeeds.
if(epvid_vers_id like '%-%', null, `project.dataset.sp.udf_std_str_to_str`(epvid_vers_id)) as version_id

Version Details

JDBC Simba Driver Version: 1.3.3.1004
Liquibase BigQuery Version: 4.17.0

Output

Running Changeset: sp.sql::1::google
SEVERE [liquibase.changelog] ChangeSet sp.sql::1::google encountered an exception.
SEVERE [liquibase.integration] Migration failed for changeset sp.sql::1::gcp:
     Reason: liquibase.exception.DatabaseException: [Simba][BigQueryJDBCDriver](100032) Error executing query job. Message: EXEC_JOB_EXECUTION_ERR [Failed SQL: (100032) 

Steps to Reproduce

1. Create any Stored Procedure with CASE statement in it.
2. Run liquibase update

Expected Behavior

The stored procedure would've have been created.

Actual Behavior

Liquibase is throwing exception mentioned above.

Community Note

• Please vote on this issue by adding a 👍 reaction
  to the original issue to help the community and maintainers prioritize this request
• Please do not leave "+1" or "me too" comments, they generate extra noise for issue followers and do
  not help prioritize the request
• If you are interested in working on this issue or have submitted a pull request, please leave a comment.

[thomascjohnson]

I am seeing a similar issue with a procedure definition. The procedure definition executes on its own in BigQuery but fails when run using Liquibase. I can't share it because it contains sensitive information but I'm wondering how this is happening.

The error message in my case is this:

Caused by: liquibase.exception.DatabaseException: [Simba][BigQueryJDBCDriver](100032) Error executing query job. Message: Syntax error: Expected ";" but got end of script at [64:11]

This corresponds to an IF block ending in END IF; where the semicolon is the character at 64:11 – so it is a strange error message!

[bermi]

I think this might have to do with how liquibase splits statements. I ran into this issue with a raw sql query, and adding splitStatements:false to the --changeset fixed it

--changeset procedure-with-cases splitStatements:false

liquibase should not be automatically splitting statements within a stored procedure