Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

unable to decrease block depth below 0 #2772

Closed
matteoquintiliani opened this issue Apr 15, 2020 · 1 comment
Closed

unable to decrease block depth below 0 #2772

matteoquintiliani opened this issue Apr 15, 2020 · 1 comment

Comments

@matteoquintiliani
Copy link

matteoquintiliani commented Apr 15, 2020

Which version and edition of Flyway are you using?

Flyway Community Edition 6.3.3 by Redgate

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

Command-line

Which database are you using (type & version)?

MariaDB / MySQL

Which operating system are you using?

Debian 10.2

What did you do?

(Please include the content causing the issue, any relevant configuration settings, the SQL statement that failed (if relevant) and the command you ran.)

The following functions have been working for a long time.

Part of this migration was created through mysqldump.

DELIMITER ;;
CREATE DEFINER=`user`@`localhost` FUNCTION `fn_datetime_from_vax`(in_str VARCHAR(50)) RETURNS varchar(50) CHARSET latin1
    DETERMINISTIC
BEGIN
    DECLARE l_out       VARCHAR(50);
    SET in_str = TRIM(in_str);
    IF (INSTR(in_str, '.') > 0) THEN
        SET l_out = LEFT(in_str, INSTR(in_str, '.') -1);
    ELSE
        SET l_out = LEFT(in_str, 19);
    END IF;

    RETURN IF(fn_isdate(l_out), NULL, l_out);
END ;;
DELIMITER ;
DELIMITER ;;
CREATE DEFINER=`user`@`localhost` FUNCTION `fn_delta`(
in_lat1 float,
in_lon1 float,
in_lat2 float,
in_lon2 float) RETURNS float
    DETERMINISTIC
BEGIN
DECLARE l_g1            FLOAT;
DECLARE l_g2            FLOAT;
DECLARE l_gr            FLOAT;
DECLARE l_a             FLOAT;
DECLARE l_b             FLOAT;
DECLARE l_rlonep        FLOAT;
DECLARE l_gamma2        FLOAT;
DECLARE l_amenb2        FLOAT;
DECLARE l_apiub2        FLOAT;
DECLARE l_tgaamenbb2    FLOAT;
DECLARE l_tgaapiubb2    FLOAT;
DECLARE l_aamenbb2      FLOAT;
DECLARE l_aapiubb2      FLOAT;
DECLARE l_tgc2s         FLOAT;
DECLARE l_cs            FLOAT;
DECLARE l_tgc2p         FLOAT;
DECLARE l_cp            FLOAT;
DECLARE l_azim          FLOAT;
DECLARE l_delta         FLOAT;
DECLARE l_rlonst        FLOAT;

SET l_g1=6.28318531;
SET l_g2=360;
SET l_gr=l_g1/l_g2;
SET l_b=(90-in_lat1)*l_gr;
SET l_rlonep=in_lon1*l_gr;
SET l_a=(90-in_lat2)*l_gr;
SET l_rlonst=in_lon2*l_gr;
SET l_gamma2=(l_rlonst-l_rlonep)/2;
SET l_amenb2=(l_a-l_b)/2;
SET l_apiub2=(l_a+l_b)/2;
IF (ABS(l_gamma2)<(0.000001*l_gr)) THEN
    SET l_delta=(ABS(in_lat1-in_lat2)*l_gr);
ELSE
    IF (in_lat1=in_lat2) THEN
        SET l_tgaamenbb2=(1/Tan(l_gamma2))*Sin(l_amenb2)/Sin(l_apiub2);
        SET l_tgaapiubb2=(1/Tan(l_gamma2))*Cos(l_amenb2)/Cos(l_apiub2);
        SET l_aamenbb2=Atan(l_tgaamenbb2);
        SET l_aapiubb2=Atan(l_tgaapiubb2);
        SET l_tgc2s=Tan(l_apiub2)*Cos(l_aapiubb2)/Cos(l_aamenbb2);
        SET l_cs=2*Atan(l_tgc2s);
        SET l_delta=l_cs;
    ELSE
        SET l_tgaamenbb2=(1/Tan(l_gamma2))*Sin(l_amenb2)/Sin(l_apiub2);
        SET l_tgaapiubb2=(1/Tan(l_gamma2))*Cos(l_amenb2)/Cos(l_apiub2);
        SET l_aamenbb2=Atan(l_tgaamenbb2);
        SET l_aapiubb2=Atan(l_tgaapiubb2);
        SET l_tgc2p=Tan(l_amenb2)*Sin(l_aapiubb2)/Sin(l_aamenbb2);
        SET l_cp=2*Atan(l_tgc2p);
        SET l_delta=l_cp;
    END IF;
END IF;
RETURN Abs((l_delta/l_gr)*111.195);
END ;;
DELIMITER ;
DELIMITER ;;
CREATE DEFINER=`user`@`localhost` FUNCTION `fn_ellispsoid_pro`(
in_what          TINYINT,
axlen1           DOUBLE,
axlen2           DOUBLE,
axlen3           DOUBLE,
azimut1          DOUBLE,
azimut2          DOUBLE,
azimut3          DOUBLE,
dip1             DOUBLE,
dip2             DOUBLE,
dip3             DOUBLE
) RETURNS double
    DETERMINISTIC
BEGIN

    DECLARE azimut_pro_xy1  DOUBLE DEFAULT 0;
    DECLARE azimut_pro_xy2  DOUBLE DEFAULT 0;
    DECLARE axlen_pro_xy1   DOUBLE DEFAULT 0;
    DECLARE axlen_pro_xy2   DOUBLE DEFAULT 0;
...
    DECLARE azimut_new1     DOUBLE DEFAULT 0;
...
    DECLARE dip_new1        DOUBLE DEFAULT 0;
...
    DECLARE a11             DOUBLE DEFAULT 0;
...
    DECLARE a44             DOUBLE DEFAULT 0;
    DECLARE alfa11          DOUBLE DEFAULT 0;
...
    DECLARE alfa33          DOUBLE DEFAULT 0;
...
    DECLARE b               DOUBLE DEFAULT 0;
...
        SET axlen1 = IF(axlen1 <= 0.01, 0.01, axlen1);
...
        SET maxlen = IF(axlen1 > maxlen, axlen1, maxlen);
...
        SET minlen = IF(axlen3 < minlen, axlen3, minlen);

        SET max_value = ABS(axlen1 * cos(RADIANS(dip1)));
        SET fi = azimut1;
        SET l_mymax = ABS(axlen2 * COS(RADIANS(dip2)));
        IF (l_mymax > max_value) THEN
            SET max_value = l_mymax;
            SET fi = azimut2;
        END IF;

       SET l_mymax = ABS(axlen3 * COS(RADIANS(dip3)));
        IF (l_mymax > max_value) THEN
            SET max_value = l_mymax;
            SET fi = azimut3;
        END IF;

        SET fi_90 = fi + 90;

        SET azimut_new1 = azimut1 - fi;
...
        SET azimut_new1 = IF(azimut_new1 < 0, azimut_new1 + 360, azimut_new1);
...
        SET dip_new1 = dip1 + 90;
...
        SET z0 = 0;
...
        SET A44 = A11 * POW(x0, 2) + A22 * POW(y0, 2) + A33 * POW(z0, 2) + 2 * (A12 * x0 * y0) + 2 * (A31 * x0 * z0) + 2 * (A23 * y0 * z0) - 1;

        SET n = 0;
        SET l_ciclo = 0;
        REPEAT
            SET n = n + 1;
            SET k1 = minlen + (n-1) * passo;
            SET aa = A33;
            SET bb = 2 * A23 * k1;
            SET cc = A22 * POW(k1, 2) + A44;
            IF POW(bb, 2) -4* aa * cc <= 0 THEN
                SET l_ciclo = 1;
            END IF;
        UNTIL (n = 1002 OR l_ciclo = 1) END REPEAT;

        SET axlen_pro_xy1 = k1;
...
        REPEAT
            SET n = n + 1;
            SET k2 = minlen + (n - 1) * passo;
            SET aa = A33;
            SET bb = 2 * A13 * k2;
            SET cc = A11 * POW(k2, 2) + A44;
            IF POW(bb, 2) - 4 * aa * cc <= 0 THEN
                SET l_ciclo = 1;
            END IF;
        UNTIL (n = 1002 OR l_ciclo = 1) END REPEAT;
...
        SET axlen_pro_xy2 = k2;
...
        CASE in_what
            WHEN 1 THEN RETURN azimut_pro_xy1;
            WHEN 2 THEN RETURN azimut_pro_xy2;
            WHEN 3 THEN RETURN axlen_pro_xy1;
            WHEN 4 THEN RETURN axlen_pro_xy2;
            ELSE RETURN NULL;
        END CASE;
END ;;
DELIMITER ;
What did you expect to see?

I expect to be able to import these features by flyway.

What did you see instead?

ERROR: Unable to parse statement in ./sql-migrations/V0.2.0__initial_import_dump.sql at line 3154 col 1: Flyway parsing bug: unable to decrease block depth below 0

@MikielAgutu
Copy link

MikielAgutu commented Apr 15, 2020

Related to #2758 and #2762. I think it's resolved in d956445 which is coming with Flyway 6.4.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants