Unable to decrease block depth below 0 error in version 6.4.0

[ChristianFischerJena]

Which version and edition of Flyway are you using?

6.4.0

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)

6.4.0 is the latest

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

Java

Which database are you using (type & version)?

sqlite

Which operating system are you using?

Windows 10

What did you do?

After upgrading to the latest flyway version. It gives me this error message when parsing my migrations: Unable to parse statement in db/migration/V1.40.2__unnamed.sql at line 3 col 1: Flyway parsing bug: unable to decrease block depth below 0
Before I used version 4.2.0.

The migration in question looks like that:

-- comment

INSERT INTO table1 (
  identifier,
  ask_for_men_time_unit,
  ask_for_men,
  ask_for_women_time_unit,
  ask_for_women,
  created_at,
  deleted,
  gender,
  title,
  updated_at,
  valid_for_men_time_unit,
  valid_for_men,
  valid_for_women_time_unit,
  valid_for_women,
  version,
  indication_type)
  SELECT
    r.identifier,
    null,
    null,
    null,
    null,
    r.created_at,
    r.deleted,
    null,
    CASE WHEN r.deleted = 1 then r.name || ' (' || d.name || ')' || r.identifier else r.name || ' (' || d.name || ')' END,
    r.updated_at,
    null,
    null,
    null,
    null,
    r.version,
    1
  FROM table2 r
  JOIN table3 d on d.identifier = r.d_id;

INSERT INTO table4(
  identifier,
  created_at,
  updated_at,
  value,
  version
  )SELECT
    lower(hex( randomblob(4)) || '-' || hex( randomblob(2))
             || '-' || '4' || substr( hex( randomblob(2)), 2) || '-'
             || substr('AB89', 1 + (abs(random()) % 4) , 1)  ||
             substr(hex(randomblob(2)), 2) || '-' || hex(randomblob(6))) as id,
    r.created_at,
    r.updated_at,
    r.information,
    0
    FROM table2 r
    WHERE r.information is not null and not r.deleted;

INSERT INTO table5(
  h_identifier,
  wm_identifier
  )SELECT
    h.identifier as hid,
    w.identifier as wid
    FROM table1 h
    JOIN table2 r on r.identifier = h.identifier
    JOIN table4 w on w.value = r.information
    WHERE r.identifier is not null and r.information is not null
    group by r.identifier;

INSERT INTO table6(
  h_identifier,
  va_identifier)
  SELECT
    identifier,
    d_id
  FROM table2;

INSERT INTO table7(
  identifier,
  created_at,
  updated_at,
  version,
  p_identifier,
  h_identifier,
  d_date,
  system_source,
  take_into_account,
  valid_till_hard,
  archived
  )
  SELECT
    identifier,
    created_at,
    updated_at,
    version,
    p_identifier,
    rg_identifier,
    date('now'),
    1,
    1,
    null,
    0
  FROM table8;

INSERT INTO table9(
  vsi_identifier,
  hi_identifier
  )
  SELECT
      table10.vsi_identifier,
      hi.identifier
    FROM table10
    JOIN table2 r on r.identifier = table10.rg_identifier
    JOIN table1 h on h.identifier = r.identifier
    JOIN table11 vsi on vsi.identifier = table10.vsi_identifier
    JOIN table12 vs on vs.identifier = vsi.vs_identifier
    JOIN table7 hi on hi.h_identifier = h.identifier and hi.p_identifier = vs.p_identifier;


INSERT INTO table7(
  identifier,
  created_at,
  updated_at,
  version,
  p_identifier,
  h_identifier,
  d_date,
  system_source,
  take_into_account,
  valid_till_hard,
  archived
  )SELECT
     lower(hex( randomblob(4)) || '-' || hex( randomblob(2))
             || '-' || '4' || substr( hex( randomblob(2)), 2) || '-'
             || substr('AB89', 1 + (abs(random()) % 4) , 1)  ||
             substr(hex(randomblob(2)), 2) || '-' || hex(randomblob(6))) as id,
    CAST((julianday('now') - 2440587.5)*86400000 AS INTEGER),
    CAST((julianday('now') - 2440587.5)*86400000 AS INTEGER),
    0,
    v.p_identifier as patient,
    r2.identifier as table2,
    v.v_date,
    1,
    1,
    null,
    1
  FROM table13
  JOIN table2 r2 on r2.identifier = table13.rg_identifier
  JOIN table20 v on v.identifier = table13.v_identifier;

INSERT INTO table14(
  v_identifier,
  hi_identifier
  )SELECT
      table13.v_identifier,
      hi.identifier
  FROM table13
  JOIN table2 r on r.identifier = table13.rg_identifier
  JOIN table20 v on v.identifier = table13.v_identifier
  JOIN table1 h on h.identifier = r.identifier
  JOIN table7 hi on hi.h_identifier = h.identifier
      and hi.p_identifier = v.p_identifier
      and hi.d_date = v.v_date
      and hi.archived = 1;

-- comment
DROP TABLE table10;
DROP TABLE table13;

--do later
--DROP TABLE table2;
--DROP TABLE table8; 

(names were replaced)

Hope this helps tracking down this issue.

[MikielAgutu]

Fixed by 9235c04

[ChristianFischerJena]

Thanks a lot!

[junketer]

@MikielAgutu
Hi Mikiel

I have hit this issue using db2 11.1 - more detail here https://stackoverflow.com/questions/70622262/flyway-migration-error-with-db2-11-1-sp-including-pure-xml-ddl

From your understanding on this issue, would the condition that drives the problem in flyway mean that the SQL wouldn't compile or run?
Because my DB2 SP compiles, deploys and executes as I expect, so I wouldn't expect it to have an issue with flyway

It's quite a complex SP, so before I expend a lot of time trying to check all the SQL balances (as the error implies it doesn't balance, but the compilation of it implies it does) I would appreciate some insight into the underlying flyway issue

As I am using Db2 11.1 and community flyway I am limited on the flyway versions I can use - the latest versions 8.x I think don't support 11.1, only 11.5. Moving up db2 versions is not an option

Hope that makes sense
Regards

Dan