SQL Server requires mixed mode when BACKUP is used as a column name

[rchurchley]

Which version and edition of Flyway are you using?

Flyway Community Edition 7.7.1 by Redgate

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

Command line

Which database are you using? (Type & version)

Microsoft SQL Server 15.0 (2019) on Docker

Which operating system are you using?

Docker container based on RHEL.

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)

Run the following script through flyway, with -mixed="false":

CREATE OR ALTER PROCEDURE [dbo].[FOO] AS SELECT 1 AS [BACKUP]
GO

CREATE OR ALTER PROCEDURE [dbo].[BAR] AS SELECT 1 AS [NOT_BACKUP]
GO

What did you expect to see?

The script should successfully be run, with both procedures generated.

If this is not possible, the limitation should be documented at https://flywaydb.org/documentation/knownparserlimitations.

What did you see instead?

ERROR: Detected both transactional and non-transactional statements within the same migration (even though mixed is false). Offending statement found at line 4: CREATE OR ALTER PROCEDURE [dbo].[BAR] AS SELECT 1 AS [NOT_BACKUP]

The parser appears to catch the column name BACKUP in a filter intended for the non-transactional statements BACKUP DATABASE or BACKUP LOG. The parser does not make the same mistake when BACKUP is a proper substring of the column name, so the second create procedure is correctly interpreted as a transactional statement.

[Rixium]

Hey @rchurchley ,

Thanks for finding and reporting this issue,
I've managed to reproduce the bug, and it does seem to be a limitation with our parser, at the moment,
which we'll aim to fix, hopefully in the next release,

Thanks!