[Bug]: ScriptUtils.splitSqlScript works incorrectly with statements containing IFs and LOOPs

[inponomarev]

Module

Core

Testcontainers version

1.19.1

Using the latest Testcontainers version?

Yes

Host OS

not relevant

Host Arch

not relevant

Docker version

not relevant

What happened?

Current version of ScriptUtils.splitSqlScript tries to take into account BEGIN...END blocks. However, it treats any END as an end of the block, even if it's END IF or END LOOP, so that the following one-statement script

BEGIN
     rec_loop: LOOP
         FETCH blah;
         IF something_wrong THEN LEAVE rec_loop; END IF;
         do_something_else;
     END LOOP;
END;

Is incorrectly split into following "statements":

BEGIN
    rec_loop: LOOP
        FETCH blah;
        IF something_wrong THEN LEAVE rec_loop; END IF
-----------------------------------------------------------------
       do_something_else
-----------------------------------------------------------------
   END LOOP
-----------------------------------------------------------------
END

This is also broken for the case when a special statement separator is used (like GO in MSSQL tradition, / in Oracle tradition or @ in DB2).

The following statement is not split over @ sign as when it comes to the separator, the algorithm counted more ENDs than BEGINs and and since the balance is not zero, it is not splitting the code:

BEGIN
    rec_loop: LOOP
        FETCH blah;
        IF something_wrong THEN LEAVE rec_loop; END IF;
        do_something_else;
    END LOOP;
END;
@
CALL something();
@

Relevant log output

No response

Additional Information

No response

[eddumelendez]

Hi @inponomarev, please consider reading our Contributing guideline and the form before raising a PR and then and issue.

Thanks for taking the time to fill out this bug report! Before submitting a bug, please make sure there is no existing issue for the one you encountered and it has been discussed with the team via discussions or Slack.

Closing this as duplicate of #7646