[V4.25.0] Regression in SQL execution with endDelimiter and splitStatements

[adamjones0101]

Search first

• I searched and no similar issues were found

Description

In a changeset an sql statement is added. splitStatements= true and endDelimiter = "/"
if there are 2 statements in the sql which have an "/" as the last character then the execution fails with a "missing or invalid option" error, ORA-00922 in Oracle DB

Steps To Reproduce

Include the following in a changeset:

<sql dbms="oracle" endDelimiter="/" splitStatements="true" stripComments="false>
CREATE USER mytestuser1 IDENTIFIED BY "abcde12345" PROFILE DEFAULT ACCOUNT UNLOCK/
GRANT CREATE TABLE TO mytestuser1/

Expected/Desired Behavior

in version 4.9.1 this works correctly but in 4.25.0 it appears that the entire sql is executed as a single statement.

Liquibase Version

4.25.0

Database Vendor & Version

No response

Liquibase Integration

No response

Liquibase Extensions

No response

OS and/or Infrastructure Type/Provider

No response

Additional Context

No response

Are you willing to submit a PR?

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

[tati-qalified]

Hi @adamjones0101, thank you for reporting this issue.
I was able to verify that it is replicable and it was introduced by Liquibase 4.15.0, possibly with #3118.

I'll be forwarding this to the development team so they can evaluate how to move forward.

Thanks,
Tatiana

[MalloD12]

Hi @adamjones0101,

As stated by @tati-qalified this behavior has been updated with #3118, and it is working as designed. For you to make this SQL execute successfully you need to update it in the below way:

<sql dbms="oracle" endDelimiter="/" splitStatements="true" stripComments="false">
CREATE USER mytestuser1 IDENTIFIED BY "abcde123" PROFILE DEFAULT ACCOUNT UNLOCK
/
GRANT CREATE TABLE TO mytestuser1
/
</sql>

There shouldn't be any space either at the beginning or the end of each sentence and / should be in the next line. As specified by @nvoxland, this is to avoid splitting a statement at the wrong spot.

I'll close this issue for now, but if you have any additional feedback or you are keen to propose an enhancement for this you are more than welcome to do it.

Thanks,
Daniel.

[adamjones0101]

I guess that there's no concern that nearly every liquibase changelog on the planet has been broken.
We are going to have to modify hundreds if not thousands of changesets to accommodate this backward incompatible change.

[MalloD12]

I guess that there's no concern that nearly every liquibase changelog on the planet has been broken. We are going to have to modify hundreds if not thousands of changesets to accommodate this backward incompatible change.

Let me double-check with some other team members if there is any other option to have both cases working (using / delimiter at the end of SQL sentence and having an expression that contains a / on it and it's not used as the end- delimiter). In the meantime, any idea or enhancement proposal is more than welcome.

Thanks,
Daniel.

[adamjones0101]

Thank you! I think we have had rare occurrences of expressions that contain a slash, I'd have to find one to see what was done as a workaround. We have 1681 changesets with splitStatements="true"

…

On Tue, Mar 12, 2024 at 6:37 PM Daniel Mallorga ***@***.***> wrote: I guess that there's no concern that nearly every liquibase changelog on the planet has been broken. We are going to have to modify hundreds if not thousands of changesets to accommodate this backward incompatible change. Let me double-check with some other team members if there is any other option to have both cases working (using / delimiter at the end of SQL sentence and having an expression that contains a / on it and it's not used as the end- delimiter). In the meantime, any idea or enhancement proposal is more than welcome. Thanks, Daniel. — Reply to this email directly, view it on GitHub <#5359 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AKQSMRIEA3AN4EJRLOAE763YX57STAVCNFSM6AAAAABARY6RX2VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSOJSGY4TGMJSGU> . You are receiving this because you were mentioned.Message ID: ***@***.***>