End delimiter logic broken by BEGIN DIALOG/BEGIN CONVERSATION

[Dl1MA]

Search first

• I searched and no similar issues were found

Description

Using Liquibase version 4.14.0 and lower change script work perfect;
After install newer version (4.15.0 and later) update script raise exception;

In my opinion problem in a parsing sql file - same as described in #3356
In Microsoft SQL, in addition to the keyword BEGIN TRANSACTION, there are other constructs that start with the BEGIN keyword and do not need the END keyword. Supported construction marked in the list below:

• BEGIN DIALOG CONVERSATION ...;
• BEGIN CONVERSATION TIMER ...;
• BEGIN DISTRIBUTED TRANSACTION ;
  • BEGIN DISTRIBUTED TRAN - only not to forget this variant;
• BEGIN TRANSACTION
  • BEGIN TRAN - same as BEGIN TRANSACTION

Changes #1589 not fixed the above described variants witch start with BEGIN keyword and haven't END.

Code example:

CREATE PROCEDURE servis.A
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @handle UNIQUEIDENTIFIER;

    BEGIN DIALOG @handle
        FROM SERVICE [//aa/BB/Service]
        TO SERVICE '//aa/BB/Service'
        ON CONTRACT [//aa/BB/Contract]
        WITH ENCRYPTION = OFF;

    SEND ON CONVERSATION @handle MESSAGE TYPE [//aa/BB/Type];

END;
GO
CREATE PROCEDURE servis.B
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @queue TABLE (message_type_name VARCHAR(256));

    WAITFOR (
            RECEIVE message_type_name FROM servis.que_bindata
            INTO @queue
            );

    DELETE
    FROM servis.data_tmp
    WHERE start_date< DATEADD(DAY, - 1, GETDATE());
END;
GO

Steps To Reproduce

script.sql

CREATE PROCEDURE servis.A
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @handle UNIQUEIDENTIFIER;

    BEGIN DIALOG @handle
        FROM SERVICE [//aa/BB/Service]
        TO SERVICE '//aa/BB/Service'
        ON CONTRACT [//aa/BB/Contract]
        WITH ENCRYPTION = OFF;

    SEND ON CONVERSATION @handle MESSAGE TYPE [//aa/BB/Type];

END;
GO
CREATE PROCEDURE servis.B
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @queue TABLE (message_type_name VARCHAR(256));

    WAITFOR (
            RECEIVE message_type_name FROM servis.que_bindata
            INTO @queue
            );

    DELETE
    FROM servis.data_tmp
    WHERE start_date< DATEADD(DAY, - 1, GETDATE());
END;
GO

script.xml

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
            http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.3.xsd">

  <changeSet id="script.0.0.0.sql" author="tester">
    <sqlFile endDelimiter="\nGO" encoding="cp1250" path="script.0.0.0.sql" splitStatements="true"/>
  </changeSet>

</databaseChangeLog>

Output

Create complete.
####################################################
##   _     _             _ _                      ##
##  | |   (_)           (_) |                     ##
##  | |    _  __ _ _   _ _| |__   __ _ ___  ___   ##
##  | |   | |/ _` | | | | | '_ \ / _` / __|/ _ \  ##
##  | |___| | (_| | |_| | | |_) | (_| \__ \  __/  ##
##  \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___|  ##
##              | |                               ##
##              |_|                               ##
##                                                ##
##  Get documentation at docs.liquibase.com       ##
##  Get certified courses at learn.liquibase.com  ##
##  Free schema change activity reports at        ##
##      https://hub.liquibase.com                 ##
##                                                ##
####################################################
Starting Liquibase at 12:00:34 (version 4.15.0 #4001 built at 2022-08-05 16:17+0000)
Liquibase Version: 4.15.0
Liquibase Community 4.15.0 by Liquibase
Running Changeset: always.set_param::always.set_param::tester
Running Changeset: script.xml::script.sql::tester

Unexpected error running Liquibase: Migration failed for changeset script.xml::script.sql::tester:
     Reason: liquibase.exception.DatabaseException: Incorrect syntax near 'GO'. [Failed SQL: (102) CREATE PROCEDURE servis.A
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @handle UNIQUEIDENTIFIER;

    BEGIN DIALOG @handle
        FROM SERVICE [//aa/BB/Service]
        TO SERVICE '//aa/BB/Service'
        ON CONTRACT [//aa/BB/Contract]
        WITH ENCRYPTION = OFF;

    SEND ON CONVERSATION @handle MESSAGE TYPE [//aa/BB/Type];

END;
GO
CREATE PROCEDURE servis.B
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @queue TABLE (message_type_name VARCHAR(256));

    WAITFOR (
            RECEIVE message_type_name FROM servis.que_bindata
            INTO @queue
            );

    DELETE
    FROM servis.data_tmp
    WHERE start_date< DATEADD(DAY, - 1, GETDATE());
END;]

Logs saved to z:\...\db\install.log

Expected/Desired Behavior

SQL must be separated to two simple procedures A and B

Liquibase Version

4.15.0

Database Vendor & Version

Microsoft SQL Server 15.00.2080

Liquibase Integration

CLI

Liquibase Extensions

No response

OS and/or Infrastructure Type/Provider

Windows 10

Additional Context

No response

Are you willing to submit a PR?

• I'm willing to submit a PR (Thank you!)

[jccampanero]

@nvoxland Can I work on this and the related issue #3356?

I don't know if a generic solution is possible, but I think that at least the provided uses cases for Sql Server could be solved.

[kevin-atx]

@jccampanero - yes, please feel free to work on this issue and the related issue #3356. As @nvoxland is out on PTO for the next two weeks, please mention @filipelautert if you have questions or need guidance.

[jccampanero]

Sorry for the late reply @kevin-atx Kevin.

Thank you very much, I will try working on both issues. I will ask for help to @filipelautert if necessary.

I think it is clear in my mind: my only concern is trying to find a solution that could be applied not only to SQL Server but for any database system: I think it would be great if every database system could define its own rules (i.e. SQL Server has these kind of constructs, Oracle has others, etc). But I am not sure if I could find a proper solution without parsing the actual SQL grammars, just with text processing.

In any case, I'll give it a try.

Thank you very much again.

[kevin-atx]

@tati-qalified - this issue looks similar to GH issue #3687 - parsing errors with scripts that contain double slashes - //. Could you test this at the same time as that ticket and let me know if this is fixed or still an issue?

[tati-qalified]

@kevin-atx this is still an issue with Liquibase version 4.25.0 - same problem, same error message.