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.
![[community-db] Support OceanBase MySQL mode](https://avatars.githubusercontent.com/u/27404407?v=4)
777 Jan 6, 2023
4.6k Dec 30, 2022
2 May 3, 2022
1.1k Dec 22, 2022
1.1k Dec 28, 2022
12.3k Jan 1, 2023
363 Dec 30, 2022
11.4k Jan 5, 2023
1k Dec 14, 2022
4.1k Dec 30, 2022
3.6k Jan 2, 2023
43 Oct 19, 2022
159 Dec 13, 2022
5 Dec 13, 2021
145 Dec 7, 2022
3 Sep 2, 2021
1 Jan 13, 2022