Snowflake scripting problems

[pachu]

Which version and edition of Flyway are you using?

docker 9.8.3-alpine

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)

yes, newest available docker image, when testing was started

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

command-line

Which database are you using? (Type & version)

Snowflake 6.40.1

Which operating system are you using?

alpine (docker distribution)

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)

There are already introduced improvements to Snowflake scripting from version 9.7.0 and most scenarios are already working well, but still during our tests we've found below cases, which directly in Snowflake worksheets works ok.

1. If block.

BEGIN
    IF (NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS AS c WHERE c.TABLE_SCHEMA = 'TEST_FLYWAY_UPGRADE' AND c.TABLE_NAME = 'T_FLYWAY_EXAMPLE_MERGE' AND c.COLUMN_NAME = 'NEW_COLUMN')) THEN
        ALTER TABLE IF EXISTS TEST_FLYWAY_UPGRADE.ST_FLYWAY_EXAMPLE_MERGE ADD COLUMN NEW_COLUMN BOOLEAN NOT NULL DEFAULT FALSE;
    END IF;
END;

UI: Works
Flyway:
my-flyway | -----------------------------------
my-flyway | SQL State : 42000
my-flyway | Error Code : 1003
my-flyway | Message : SQL compilation error:
my-flyway | syntax error line 2 at position 7 unexpected '('.
my-flyway | syntax error line 2 at position 51 unexpected '.'.
my-flyway | Location : /app/scripts/R__end_if_case.sql (/app/scripts/R__end_if_case.sql)
my-flyway | Line : 3
my-flyway | Statement : BEGIN
my-flyway | IF (NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS AS c WHERE c.TABLE_SCHEMA = 'TEST_FLYWAY_UPGRADE' AND c.TABLE_NAME = 'T_FLYWAY_EXAMPLE_MERGE' AND c.COLUMN_NAME = 'NEW_COLUMN')) THEN
my-flyway | ALTER TABLE IF EXISTS TEST_FLYWAY_UPGRADE.ST_FLYWAY_EXAMPLE_MERGE ADD COLUMN IS_SQLSERVER_CDC BOOLEAN NOT NULL DEFAULT FALSE
my-flyway |
my-flyway | Caused by: net.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error:
my-flyway | syntax error line 2 at position 7 unexpected '('.
my-flyway | syntax error line 2 at position 51 unexpected '.'.

Another case:

DECLARE
  MY_EXCEPTION EXCEPTION (-20002, 'RAISED MY_EXCEPTION.');
BEGIN
  LET COUNTER := 0;
  LET SHOULD_RAISE_EXCEPTION := TRUE;
  IF (SHOULD_RAISE_EXCEPTION) THEN
    RAISE MY_EXCEPTION;
  END IF;
  COUNTER := COUNTER + 1;
  RETURN COUNTER;
END;

UI: properly raised exception,
Flyway:
my-flyway | Caused by: net.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error:
my-flyway | syntax error line 8 at position 8 unexpected ''.

2. FOR loop

CREATE OR REPLACE PROCEDURE TEST_FOR_LOOP(ITERATION_LIMIT INTEGER)
RETURNS INTEGER
LANGUAGE SQL
AS
DECLARE 
	COUNTER INTEGER DEFAULT 0;
BEGIN  
    FOR I IN 1 TO ITERATION_LIMIT DO
            COUNTER := COUNTER + 1;
    END FOR;
    RETURN ITERATION_LIMIT;
END;

UI: works
Flyway:
my-flyway | syntax error line 10 at position 11 unexpected ''.
It looks like issue with every block closure/sentence like END %;

3. EXCEPTION BLOCK with dynamic code

BEGIN
    CREATE OR REPLACE PROCEDURE TEST_FLYWAY_EXCEPTIONS()
    RETURNS VARCHAR
    LANGUAGE SQL
    EXECUTE AS CALLER
    AS
    DECLARE 
        sql STRING;
        rtn STRING;
    BEGIN
        SELECT "Hello world" INTO :rtn;
        RETURN (:rtn);
   EXCEPTION
        WHEN OTHER THEN  
            LET exsqlcode := sqlcode;
        BEGIN
            SELECT CONCAT('DECLARE user_exception EXCEPTION (-3424324, ''RAISE MY__FLYWAY_TEST_EXCEPTION.''); \n'
                        , 'BEGIN \n'
                        , '\t RAISE user_exception; \n'
                        , 'END; \n') INTO :sql;
            EXECUTE IMMEDIATE :sql;  
        END;	
    END;  
END;

UI: success.
Flyway:
my-flyway | Caused by: net.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error:
my-flyway | syntax error line 25 at position 7 unexpected ''.

4. Merge statement
with DECLARE

CREATE TABLE IF NOT EXISTS TEST_FLYWAY_UPGRADE.T_FLYWAY_EXAMPLE_MERGE_TEST ("ID" number, "ENVIRONMENT" VARCHAR(100), "TEXT" VARCHAR(100));

DECLARE
	environment_name := 'DEV';
BEGIN
	MERGE INTO TEST_FLYWAY_UPGRADE.T_FLYWAY_EXAMPLE_MERGE_TEST AS trg USING (
		WITH SOURCE AS (
			SELECT *
				FROM (VALUES ('1',			:environment_name,	'TEST dadas')
						   , ('2',			:environment_name,	'TEST dasdasdsa')
                           , ('3',			:environment_name,	'TEST dasdsadsadsa')
					) m(ID, ENVIRONMENT, TEXT)
		)
		SELECT src.ID, src.ENVIRONMENT, src.TEXT
				, CASE WHEN src.ID IS NULL THEN 'NOT_MATCHED_BY_SOURCE'
					WHEN trg.ID IS NULL THEN 'NOT_MATCHED_BY_TARGET'
					ELSE 'MATCHED' END AS MATCH
			FROM SOURCE AS src
			FULL JOIN TEST_FLYWAY_UPGRADE.T_FLYWAY_EXAMPLE_MERGE_TEST AS trg
				ON trg.ID = src.ID
	) AS src
		ON trg.ID = src.ID
	
	WHEN MATCHED AND src.MATCH = 'MATCHED' AND (trg.ENVIRONMENT <> src.ENVIRONMENT OR trg.TEXT <> src.TEXT)
	THEN UPDATE SET trg.ENVIRONMENT = src.ENVIRONMENT
					, trg.TEXT = src.TEXT;
END;

UI: success.
Flyway:
my-flyway | Caused by: net.snowflake.client.jdbc.SnowflakeSQLException: Stored procedure execution error: Scoped transaction started in stored procedure is incomplete and it was rolled back

without DECLARE

BEGIN
	MERGE INTO TEST_FLYWAY_UPGRADE.T_FLYWAY_EXAMPLE_MERGE_TEST AS trg USING (
		WITH SOURCE AS (
			SELECT *
				FROM (VALUES ('1',			'DEV',	'TEST dadas')
						   , ('2',			'DEV',	'TEST dasdasdsa')
                           , ('3',			'DEV',	'TEST dasdsadsadsa')
					) m(ID, ENVIRONMENT, TEXT)
		)
		SELECT src.ID, src.ENVIRONMENT, src.TEXT
				, CASE WHEN src.ID IS NULL THEN 'NOT_MATCHED_BY_SOURCE'
					WHEN trg.ID IS NULL THEN 'NOT_MATCHED_BY_TARGET'
					ELSE 'MATCHED' END AS MATCH
			FROM SOURCE AS src
			FULL JOIN TEST_FLYWAY_UPGRADE.T_FLYWAY_EXAMPLE_MERGE_TEST AS trg
				ON trg.ID = src.ID
	) AS src
		ON trg.ID = src.ID
	
	WHEN MATCHED AND src.MATCH = 'MATCHED' AND (trg.ENVIRONMENT <> src.ENVIRONMENT OR trg.TEXT <> src.TEXT)
	THEN UPDATE SET trg.ENVIRONMENT = src.ENVIRONMENT
					, trg.TEXT = src.TEXT;
END;

UI: success.
Flyway:
my-flyway | Caused by: net.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error:
my-flyway | syntax error line 22 at position 26 unexpected ''.

What did you expect to see?

Sucessfully executed migrations.

What did you see instead?

Shown above exceptions.