EXEC_JOB_EXECUTION_ERR when using CASE statements in Stored Procedures

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

Create any Stored Procedure with CASE statement in it.
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.

liquibase / liquibase-bigquery