/*-
 * #%L
 * JSQLParser library
 * %%
 * Copyright (C) 2004 - 2019 JSQLParser
 * %%
 * Dual licensed under GNU LGPL 2.1 or Apache License 2.0
 * #L%
 */
package net.sf.jsqlparser.statement.select;

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import org.junit.jupiter.api.Disabled;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.Timeout;

import java.util.logging.Logger;

import static net.sf.jsqlparser.test.TestUtils.assertSqlCanBeParsedAndDeparsed;
import org.junit.jupiter.api.Assertions;
import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.fail;
import org.junit.jupiter.api.function.Executable;

/**
 *
 * @author tw
 */
public class NestedBracketsPerformanceTest {

    private static final Logger LOG =
            Logger.getLogger(NestedBracketsPerformanceTest.class.getName());

    @Test
    @Timeout(2000)
    public void testIssue766() throws JSQLParserException {
        assertSqlCanBeParsedAndDeparsed(
                "SELECT concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat('1','2'),'3'),'4'),'5'),'6'),'7'),'8'),'9'),'10'),'11'),'12'),'13'),'14'),'15'),'16'),'17'),'18'),'19'),'20'),'21'),col1 FROM tbl t1",
                true, parser -> parser.withTimeOut(60000));
    }

    @Test
    @Timeout(2000)
    public void testIssue766_2() throws JSQLParserException {
        assertSqlCanBeParsedAndDeparsed(
                "SELECT concat(concat(concat('1', '2'), '3'), '4'), col1 FROM tbl t1", true,
                parser -> parser.withTimeOut(60000));
    }

    @Test
    @Timeout(2000)
    public void testIssue235() throws JSQLParserException {
        assertSqlCanBeParsedAndDeparsed(
                "SELECT CASE WHEN ( CASE WHEN ( CASE WHEN ( CASE WHEN ( 1 ) THEN 0 END ) THEN 0 END ) THEN 0 END ) THEN 0 END FROM a",
                true, parser -> parser.withTimeOut(60000));
    }

    @Test
    @Timeout(2000)
    public void testNestedCaseWhenWithoutBracketsIssue1162() throws JSQLParserException {
        assertSqlCanBeParsedAndDeparsed("CREATE VIEW VIEW_NAME1 AS\n" + "SELECT CASE\n"
                + "WHEN WDGFLD.PORTTYPE = 1 THEN 'INPUT PORT'\n" + "ELSE CASE\n"
                + "WHEN WDGFLD.PORTTYPE = 1 THEN 'INPUT PORT'\n" + "ELSE CASE\n"
                + "WHEN WDGFLD.PORTTYPE = 1 THEN 'INPUT PORT'\n" + "ELSE CASE\n"
                + "WHEN WDGFLD.PORTTYPE = 1 THEN 'INPUT PORT'\n" + "ELSE CASE\n"
                + "WHEN WDGFLD.PORTTYPE = 1 THEN 'INPUT PORT'\n" + "ELSE CASE\n"
                + "WHEN WDGFLD.PORTTYPE = 1 THEN 'INPUT PORT'\n" + "ELSE CASE\n"
                + "WHEN WDGFLD.PORTTYPE = 1 THEN 'INPUT PORT'\n" + "ELSE CASE\n"
                + "WHEN WDGFLD.PORTTYPE = 1 THEN 'INPUT PORT'\n" + "ELSE CASE\n"
                + "WHEN WDGFLD.PORTTYPE = 1 THEN 'INPUT PORT'\n" + "ELSE CASE\n"
                + "WHEN WDGFLD.PORTTYPE = 1 THEN 'INPUT PORT'\n" + "ELSE CASE\n"
                + "WHEN WDGFLD.PORTTYPE = 1 THEN 'INPUT PORT'\n" + "ELSE CASE\n"
                + "WHEN WDGFLD.PORTTYPE = 1 THEN 'INPUT PORT'\n" + "ELSE CASE\n"
                + "WHEN WDGFLD.PORTTYPE = 1 THEN 'INPUT PORT'\n"
                + "ELSE CASE WHEN WDGFLD.PORTTYPE = 1 THEN 'INPUT PORT' ELSE '0' END END END END END END END END END END END END END END COLUMNALIAS\n"
                + "FROM TABLE1", true, parser -> parser.withTimeOut(60000));
    }

    @Test
    @Timeout(2000)
    public void testNestedCaseWhenWithBracketsIssue1162() throws JSQLParserException {
        assertSqlCanBeParsedAndDeparsed("CREATE VIEW VIEW_NAME1 AS\n" + "SELECT CASE\n"
                + "WHEN WDGFLD.PORTTYPE = 1 THEN 'INPUT PORT'\n" + "ELSE (CASE\n"
                + "WHEN WDGFLD.PORTTYPE = 1 THEN 'INPUT PORT'\n" + "ELSE (CASE\n"
                + "WHEN WDGFLD.PORTTYPE = 1 THEN 'INPUT PORT'\n" + "ELSE (CASE\n"
                + "WHEN WDGFLD.PORTTYPE = 1 THEN 'INPUT PORT'\n" + "ELSE (CASE\n"
                + "WHEN WDGFLD.PORTTYPE = 1 THEN 'INPUT PORT'\n" + "ELSE (CASE\n"
                + "WHEN WDGFLD.PORTTYPE = 1 THEN 'INPUT PORT'\n" + "ELSE (CASE\n"
                + "WHEN WDGFLD.PORTTYPE = 1 THEN 'INPUT PORT'\n" + "ELSE (CASE\n"
                + "WHEN WDGFLD.PORTTYPE = 1 THEN 'INPUT PORT'\n" + "ELSE (CASE\n"
                + "WHEN WDGFLD.PORTTYPE = 1 THEN 'INPUT PORT'\n" + "ELSE (CASE\n"
                + "WHEN WDGFLD.PORTTYPE = 1 THEN 'INPUT PORT'\n" + "ELSE (CASE\n"
                + "WHEN WDGFLD.PORTTYPE = 1 THEN 'INPUT PORT'\n" + "ELSE (CASE\n"
                + "WHEN WDGFLD.PORTTYPE = 1 THEN 'INPUT PORT'\n" + "ELSE (CASE\n"
                + "WHEN WDGFLD.PORTTYPE = 1 THEN 'INPUT PORT'\n"
                + "ELSE (CASE WHEN WDGFLD.PORTTYPE = 1 THEN 'INPUT PORT' ELSE '0' END) END) END) END) END) END) END) END) END) END) END) END) END) END COLUMNALIAS\n"
                + "FROM TABLE1", true, parser -> parser.withTimeOut(60000));
    }

    @Test
    @Timeout(10000)
    public void testIssue496() throws JSQLParserException {
        Assertions.assertThrowsExactly(JSQLParserException.class, new Executable() {
            @Override
            public void execute() throws Throwable {
                assertSqlCanBeParsedAndDeparsed(
                        "select isNull(charLen(TEST_ID,0)+ isNull(charLen(TEST_DVC,0)+ isNull(charLen(TEST_NO,0)+ isNull(charLen(ATEST_ID,0)+ isNull(charLen(TESTNO,0)+ isNull(charLen(TEST_CTNT,0)+ isNull(charLen(TEST_MESG_CTNT,0)+ isNull(charLen(TEST_DTM,0)+ isNull(charLen(TEST_DTT,0)+ isNull(charLen(TEST_ADTT,0)+ isNull(charLen(TEST_TCD,0)+ isNull(charLen(TEST_PD,0)+ isNull(charLen(TEST_VAL,0)+ isNull(charLen(TEST_YN,0)+ isNull(charLen(TEST_DTACM,0)+ isNull(charLen(TEST_MST,0) from test_info_m",
                        true, parser -> parser.withTimeOut(6000));
            }
        });

    }

    @Test
    @Timeout(2000)
    public void testIssue856() throws JSQLParserException {
        String sql = "SELECT "
                + buildRecursiveBracketExpression(
                        "if(month(today()) = 3, sum(\"Table5\".\"Month 002\"), $1)", "0", 3)
                + " FROM mytbl";
        assertSqlCanBeParsedAndDeparsed(sql, true, parser -> parser.withTimeOut(60000));
    }

    @Test
    @Timeout(2000)
    public void testRecursiveBracketExpressionIssue1019() {
        assertEquals("IF(1=1, 1, 2)", buildRecursiveBracketExpression("IF(1=1, $1, 2)", "1", 0));
        assertEquals("IF(1=1, IF(1=1, 1, 2), 2)",
                buildRecursiveBracketExpression("IF(1=1, $1, 2)", "1", 1));
        assertEquals("IF(1=1, IF(1=1, IF(1=1, 1, 2), 2), 2)",
                buildRecursiveBracketExpression("IF(1=1, $1, 2)", "1", 2));
    }

    // maxDepth = 10 collides with the Parser Timeout = 6 seconds
    // @todo: implement methods to set the Parser Timeout explicitly and on demand
    @Test
    @Timeout(2000)
    public void testRecursiveBracketExpressionIssue1019_2() throws JSQLParserException {
        doIncreaseOfParseTimeTesting("IF(1=1, $1, 2)", "1", 10);
    }

    @Test
    @Timeout(2000)
    public void testIssue1013() throws JSQLParserException {
        assertSqlCanBeParsedAndDeparsed("SELECT ((((((((((((((((tblA)))))))))))))))) FROM mytable",
                true, parser -> parser.withTimeOut(60000));
    }

    @Test
    @Timeout(2000)
    public void testIssue1013_2() throws JSQLParserException {
        assertSqlCanBeParsedAndDeparsed("SELECT * FROM ((((((((((((((((tblA))))))))))))))))", true,
                parser -> parser.withTimeOut(60000));
    }

    @Test
    @Timeout(2000)
    public void testIssue1013_3() throws JSQLParserException {
        assertSqlCanBeParsedAndDeparsed("SELECT * FROM (((tblA)))", true,
                parser -> parser.withTimeOut(60000));
    }

    @Test
    @Timeout(2000)
    public void testIssue1013_4() throws JSQLParserException {
        StringBuilder s = new StringBuilder("tblA");
        for (int i = 1; i < 100; i++) {
            s = new StringBuilder("(" + s + ")");
        }
        String sql = "SELECT * FROM " + s;
        LOG.info("testing " + sql);
        assertSqlCanBeParsedAndDeparsed(sql, true, parser -> parser.withTimeOut(60000));
    }

    /**
     * Try to avoid or border huge parsing time increments by adding more bracket constructs.
     *
     * @throws JSQLParserException
     */
    @Test
    @Timeout(2000)
    public void testIncreaseOfParseTime() throws JSQLParserException {
        doIncreaseOfParseTimeTesting("concat($1,'B')", "'A'", 50);
    }

    private void doIncreaseOfParseTimeTesting(String template, String finalExpression, int maxDepth)
            throws JSQLParserException {
        long oldDurationTime = 2000;
        int countProblematic = 0;
        for (int i = 0; i < maxDepth; i++) {
            String sql = "SELECT " + buildRecursiveBracketExpression(template, finalExpression, i)
                    + " FROM mytbl";
            long startTime = System.currentTimeMillis();
            assertSqlCanBeParsedAndDeparsed(sql, true, parser -> parser.withTimeOut(12000));
            long durationTime = System.currentTimeMillis() - startTime;

            if (i > 0) {
                System.out.println("old duration " + oldDurationTime + " new duration time "
                        + durationTime + " for " + sql);
            }
            if (oldDurationTime * 10 < durationTime) {
                countProblematic++;
            }
            if (countProblematic > 5) {
                fail("too large increment of parsing time");
            }

            oldDurationTime = Math.max(durationTime, 1);
        }
    }

    @Test
    @Timeout(2000)
    public void testRecursiveBracketExpression() {
        assertEquals("concat('A','B')",
                buildRecursiveBracketExpression("concat($1,'B')", "'A'", 0));
        assertEquals("concat(concat('A','B'),'B')",
                buildRecursiveBracketExpression("concat($1,'B')", "'A'", 1));
        assertEquals("concat(concat(concat('A','B'),'B'),'B')",
                buildRecursiveBracketExpression("concat($1,'B')", "'A'", 2));
    }

    private String buildRecursiveBracketExpression(String template, String finalExpression,
            int depth) {
        if (depth == 0) {
            return template.replace("$1", finalExpression);
        }
        return template.replace("$1",
                buildRecursiveBracketExpression(template, finalExpression, depth - 1));
    }

    @Test
    @Timeout(2000)
    public void testIssue1103() throws JSQLParserException {
        assertSqlCanBeParsedAndDeparsed(
                "SELECT\n" + "ROUND(ROUND(ROUND(ROUND(ROUND(ROUND(ROUND(ROUND(\n"
                        + "ROUND(ROUND(ROUND(ROUND(ROUND(ROUND(ROUND(ROUND(\n"
                        + "ROUND(ROUND(ROUND(ROUND(ROUND(ROUND(ROUND(ROUND(\n"
                        + "ROUND(ROUND(ROUND(ROUND(ROUND(ROUND(ROUND(ROUND(0\n"
                        + ",0),0),0),0),0),0),0),0)\n" + ",0),0),0),0),0),0),0),0)\n"
                        + ",0),0),0),0),0),0),0),0)\n" + ",0),0),0),0),0),0),0),0)",
                true, parser -> parser.withTimeOut(60000));
    }

    @Test
    @Timeout(2000)
    public void testDeepFunctionParameters() throws JSQLParserException {
        String sqlStr = "SELECT  a.*\n"
                + "        , To_Char( a.eingangsdat, 'MM.YY' ) AS eingmonat\n"
                + "        , ( SELECT Trim( b.atext )\n"
                + "            FROM masseinheiten x\n"
                + "                , a_lmt b\n"
                + "            WHERE x.a_text_id = b.a_text_id\n"
                + "                AND b.sprach_kz = sprache\n"
                + "                AND x.masseinh_id = a.masseinh_id ) AS reklamengesonst_bez\n"
                + "        , ( SELECT Trim( name ) || ' ' || Trim( vorname ) AS eingangerfasser_name\n"
                + "            FROM personal\n"
                + "            WHERE mandanten_id = m_personal\n"
                + "                AND personal_id = eingangerfasser ) AS eingangerfasser_name\n"
                + "        , Nvl( (    SELECT Max( change_date )\n"
                + "                    FROM besch_statusaenderung\n"
                + "                    WHERE beschwerden_id = a.beschwerden_id\n"
                + "                        AND beschstatus_id = 9\n"
                + "                        AND Nvl( inaktiv, 'F' ) != 'T' ), sysdate ) AS abschlussdatum\n"
                + "        , a.sachstand\n"
                + "        , a.bewertung\n"
                + "        , a.massnahmen\n"
                + "        , ( Decode( Nvl( (  SELECT Max( Trunc( change_date ) ) - Trunc( a.adate )\n"
                + "                            FROM besch_statusaenderung\n"
                + "                            WHERE beschwerden_id = a.beschwerden_id\n"
                + "                                AND beschstatus_id = 9\n"
                + "                                AND Nvl( inaktiv, 'F' ) != 'T' ), - 1 )\n"
                + "                    , - 1, Trunc( sysdate ) - Trunc( a.adate ) - (  SELECT Count()\n"
                + "                                                                    FROM firmenkalender\n"
                + "                                                                    WHERE firma_id = firmen_id\n"
                + "                                                                        AND Nvl( b_verkauf, 'F' ) = 'T'\n"
                + "                                                                        AND kal_datum BETWEEN Trunc( a.adate )\n"
                + "                                                                                             AND Trunc( sysdate ) )\n"
                + "                    , Nvl( (    SELECT Max( Trunc( change_date ) ) - Trunc( a.adate )\n"
                + "                                FROM besch_statusaenderung\n"
                + "                                WHERE beschwerden_id = a.beschwerden_id\n"
                + "                                    AND beschstatus_id = 9\n"
                + "                                    AND Nvl( inaktiv, 'F' ) != 'T' ), - 1 )\n"
                + "                             - (    SELECT Count()\n"
                + "                                    FROM firmenkalender\n"
                + "                                    WHERE firma_id = firmen_id\n"
                + "                                        AND Nvl( b_verkauf, 'F' ) = 'T'\n"
                + "                                        AND kal_datum BETWEEN Trunc( a.adate )\n"
                + "                                                             AND (  SELECT Max( Trunc( change_date ) )\n"
                + "                                                                    FROM besch_statusaenderung\n"
                + "                                                                    WHERE beschwerden_id = a.beschwerden_id\n"
                + "                                                                        AND beschstatus_id = 9\n"
                + "                                                                        AND Nvl( inaktiv, 'F' ) != 'T' ) ) ) + 1 ) AS laufzeit\n"
                + "        , Nvl( (    SELECT grenzwert\n"
                + "                    FROM beschfehler\n"
                + "                    WHERE beschfehler_id = a.beschwkat_id ), 0 ) AS grenzwert\n"
                + "        , Nvl( (    SELECT warnwert\n"
                + "                    FROM beschfehler\n"
                + "                    WHERE beschfehler_id = a.beschwkat_id ), 0 ) AS warnwert\n"
                + "        , a.beschstatus_id AS pruef_status\n"
                + "        , ( CASE\n"
                + "                    WHEN ( ( Decode( Nvl( ( SELECT Max( Trunc( change_date ) ) - Trunc( a.adate )\n"
                + "                                            FROM besch_statusaenderung\n"
                + "                                            WHERE beschwerden_id = a.beschwerden_id\n"
                + "                                                AND beschstatus_id = 9\n"
                + "                                                AND Nvl( inaktiv, 'F' ) != 'T' ), - 1 )\n"
                + "                                        , - 1, Trunc( sysdate ) - Trunc( a.adate ) - (  SELECT Count()\n"
                + "                                                                                        FROM firmenkalender\n"
                + "                                                                                        WHERE firma_id = firmen_id\n"
                + "                                                                                            AND Nvl( b_verkauf, 'F' ) = 'T'\n"
                + "                                                                                            AND kal_datum BETWEEN Trunc( a.adate )\n"
                + "                                                                                                                 AND Trunc( sysdate ) )\n"
                + "                                        , Nvl( (    SELECT Max( Trunc( change_date ) ) - Trunc( a.adate )\n"
                + "                                                    FROM besch_statusaenderung\n"
                + "                                                    WHERE beschwerden_id = a.beschwerden_id\n"
                + "                                                        AND beschstatus_id = 9\n"
                + "                                                        AND Nvl( inaktiv, 'F' ) != 'T' ), - 1 )\n"
                + "                                                 - (    SELECT Count()\n"
                + "                                                        FROM firmenkalender\n"
                + "                                                        WHERE firma_id = firmen_id\n"
                + "                                                            AND Nvl( b_verkauf, 'F' ) = 'T'\n"
                + "                                                            AND kal_datum BETWEEN Trunc( a.adate )\n"
                + "                                                                                 AND (  SELECT Max( Trunc( change_date ) )\n"
                + "                                                                                        FROM besch_statusaenderung\n"
                + "                                                                                        WHERE beschwerden_id = a.beschwerden_id\n"
                + "                                                                                            AND beschstatus_id = 9\n"
                + "                                                                                            AND Nvl( inaktiv, 'F' ) != 'T' ) ) ) + 1 ) - Nvl( ( SELECT grenzwert\n"
                + "                                                                                                                                                FROM beschfehler\n"
                + "                                                                                                                                                WHERE beschfehler_id = a.beschwkat_id ), 0 ) ) < 0\n"
                + "                        THEN 0\n"
                + "                    ELSE ( ( Decode( Nvl( ( SELECT Max( Trunc( change_date ) ) - Trunc( a.adate )\n"
                + "                                            FROM besch_statusaenderung\n"
                + "                                            WHERE beschwerden_id = a.beschwerden_id\n"
                + "                                                AND beschstatus_id = 9\n"
                + "                                                AND Nvl( inaktiv, 'F' ) != 'T' ), - 1 )\n"
                + "                                        , - 1, Trunc( sysdate ) - Trunc( a.adate ) - (  SELECT Count()\n"
                + "                                                                                        FROM firmenkalender\n"
                + "                                                                                        WHERE firma_id = firmen_id\n"
                + "                                                                                            AND Nvl( b_verkauf, 'F' ) = 'T'\n"
                + "                                                                                            AND kal_datum BETWEEN Trunc( a.adate )\n"
                + "                                                                                                                 AND Trunc( sysdate ) )\n"
                + "                                        , Nvl( (    SELECT Max( Trunc( change_date ) ) - Trunc( a.adate )\n"
                + "                                                    FROM besch_statusaenderung\n"
                + "                                                    WHERE beschwerden_id = a.beschwerden_id\n"
                + "                                                        AND beschstatus_id = 9\n"
                + "                                                        AND Nvl( inaktiv, 'F' ) != 'T' ), - 1 )\n"
                + "                                                 - (    SELECT Count( * )\n"
                + "                                                        FROM firmenkalender\n"
                + "                                                        WHERE firma_id = firmen_id\n"
                + "                                                            AND Nvl( b_verkauf, 'F' ) = 'T'\n"
                + "                                                            AND kal_datum BETWEEN Trunc( a.adate )\n"
                + "                                                                                 AND (  SELECT Max( Trunc( change_date ) )\n"
                + "                                                                                        FROM besch_statusaenderung\n"
                + "                                                                                        WHERE beschwerden_id = a.beschwerden_id\n"
                + "                                                                                            AND beschstatus_id = 9\n"
                + "                                                                                            AND Nvl( inaktiv, 'F' ) != 'T' ) ) ) + 1 ) - Nvl( ( SELECT grenzwert\n"
                + "                                                                                                                                                FROM beschfehler\n"
                + "                                                                                                                                                WHERE beschfehler_id = a.beschwkat_id ), 0 ) )\n"
                + "                END ) AS grenz_ueber\n"
                + "FROM beschwerden a\n"
                + "WHERE a.mandanten_id = m_beschwerde\n"
                + "    AND a.rec_status <> '9'\n"
                + "    AND EXISTS (    SELECT 1\n"
                + "                    FROM besch_statusaenderung\n"
                + "                    WHERE beschwerden_id = a.beschwerden_id )\n"
                + "    AND Nvl( (  SELECT grenzwert\n"
                + "                FROM beschfehler\n"
                + "                WHERE beschfehler_id = a.beschwkat_id ), 0 ) > 0\n";

        assertSqlCanBeParsedAndDeparsed(sqlStr, true, parser -> parser.withTimeOut(60000));
    }

    @Test
    @Disabled
    void testIssue1983() throws JSQLParserException {
        String sqlStr = "INSERT INTO\n" +
                "C01_INDIV_TELBK_CUST_INFO_H_T2 (PARTY_ID, PARTY_SIGN_STAT_CD, SIGN_TM, CLOSE_TM)\n"
                +
                "SELECT\n" +
                "A1.PARTY_ID,\n" +
                "A1.PARTY_SIGN_STAT_CD,\n" +
                "CAST(\n" +
                "(\n" +
                "CASE\n" +
                "WHEN A1.SIGN_TM IS NULL\n" +
                "OR A1.SIGN_TM = '' THEN CAST(\n" +
                "CAST(\n" +
                "CAST('ATkkIVQJZm' AS DATE FORMAT 'YYYYMMDD') AS DATE\n" +
                ") || ' 00:00:00' AS TIMESTAMP\n" +
                ")\n" +
                "WHEN CHARACTERS (TRIM(A1.SIGN_TM)) <> 19\n" +
                "OR SUBSTR (TRIM(A1.SIGN_TM), 1, 1) < '0'\n" +
                "OR SUBSTR (TRIM(A1.SIGN_TM), 1, 1) > '9'\n" +
                "OR SUBSTR (TRIM(A1.SIGN_TM), 2, 1) < '0'\n" +
                "OR SUBSTR (TRIM(A1.SIGN_TM), 2, 1) > '9'\n" +
                "OR SUBSTR (TRIM(A1.SIGN_TM), 3, 1) < '0'\n" +
                "OR SUBSTR (TRIM(A1.SIGN_TM), 3, 1) > '9'\n" +
                "OR SUBSTR (TRIM(A1.SIGN_TM), 4, 1) < '0'\n" +
                "OR SUBSTR (TRIM(A1.SIGN_TM), 4, 1) > '9'\n" +
                "OR SUBSTR (TRIM(A1.SIGN_TM), 6, 1) < '0'\n" +
                "OR SUBSTR (TRIM(A1.SIGN_TM), 6, 1) > '1'\n" +
                "OR SUBSTR (TRIM(A1.SIGN_TM), 7, 1) < '0'\n" +
                "OR SUBSTR (TRIM(A1.SIGN_TM), 7, 1) > '9'\n" +
                "OR SUBSTR (TRIM(A1.SIGN_TM), 9, 1) < '0'\n" +
                "OR SUBSTR (TRIM(A1.SIGN_TM), 9, 1) > '3'\n" +
                "OR SUBSTR (TRIM(A1.SIGN_TM), 10, 1) < '0'\n" +
                "OR SUBSTR (TRIM(A1.SIGN_TM), 10, 1) > '9'\n" +
                "OR SUBSTR (TRIM(A1.SIGN_TM), 1, 4) = '0000'\n" +
                "OR SUBSTR (TRIM(A1.SIGN_TM), 6, 2) = '00'\n" +
                "OR SUBSTR (TRIM(A1.SIGN_TM), 9, 2) = '00'\n" +
                "OR SUBSTR (TRIM(A1.SIGN_TM), 1, 1) = '0' THEN CAST(\n" +
                "CAST(\n" +
                "CAST('cDXtwdFyky' AS DATE FORMAT 'YYYYMMDD') AS DATE\n" +
                ") || ' 00:00:00' AS TIMESTAMP\n" +
                ")\n" +
                "ELSE (\n" +
                "CASE\n" +
                "WHEN (\n" +
                "CAST(SUBSTR (TRIM(A1.SIGN_TM), 9, 2) AS INTEGER) < 29\n" +
                "AND SUBSTR (TRIM(A1.SIGN_TM), 6, 2) = '02'\n" +
                ")\n" +
                "OR (\n" +
                "CAST(SUBSTR (TRIM(A1.SIGN_TM), 9, 2) AS INTEGER) < 31\n" +
                "AND SUBSTR (TRIM(A1.SIGN_TM), 6, 2) <> '02'\n" +
                "AND SUBSTR (TRIM(A1.SIGN_TM), 6, 2) <= 12\n" +
                ")\n" +
                "OR (\n" +
                "CAST(SUBSTR (TRIM(A1.SIGN_TM), 9, 2) AS INTEGER) = 31\n" +
                "AND SUBSTR (TRIM(A1.SIGN_TM), 6, 2) IN ('01', '03', '05', '07', '08', '10', '12')\n"
                +
                ") THEN CAST(A1.SIGN_TM AS TIMESTAMP)\n" +
                "WHEN SUBSTR (TRIM(A1.SIGN_TM), 6, 2) || SUBSTR (TRIM(A1.SIGN_TM), 9, 2) = '0229'\n"
                +
                "AND (\n" +
                "CAST(SUBSTR (TRIM(A1.SIGN_TM), 1, 4) AS INTEGER) MOD 400 = 0\n" +
                "OR (\n" +
                "CAST(SUBSTR (TRIM(A1.SIGN_TM), 1, 4) AS INTEGER) MOD 4 = 0\n" +
                "AND CAST(SUBSTR (TRIM(A1.SIGN_TM), 1, 4) AS INTEGER) MOD 100 <> 0\n" +
                ")\n" +
                ") THEN CAST(A1.SIGN_TM AS TIMESTAMP)\n" +
                "ELSE CAST(\n" +
                "CAST(\n" +
                "CAST('cDXtwdFyky' AS DATE FORMAT 'YYYYMMDD') AS DATE\n" +
                ") || ' 00:00:00' AS TIMESTAMP\n" +
                ")\n" +
                "END\n" +
                ")\n" +
                "END\n" +
                ") AS DATE FORMAT 'YYYYMMDD'\n" +
                "),\n" +
                "CAST(\n" +
                "(\n" +
                "CASE\n" +
                "WHEN A1.CLOSE_TM IS NULL\n" +
                "OR A1.CLOSE_TM = '' THEN CAST(\n" +
                "CAST(\n" +
                "CAST('ATkkIVQJZm' AS DATE FORMAT 'YYYYMMDD') AS DATE\n" +
                ") || ' 00:00:00' AS TIMESTAMP\n" +
                ")\n" +
                "WHEN CHARACTERS (TRIM(A1.CLOSE_TM)) <> 19\n" +
                "OR SUBSTR (TRIM(A1.CLOSE_TM), 1, 1) < '0'\n" +
                "OR SUBSTR (TRIM(A1.CLOSE_TM), 1, 1) > '9'\n" +
                "OR SUBSTR (TRIM(A1.CLOSE_TM), 2, 1) < '0'\n" +
                "OR SUBSTR (TRIM(A1.CLOSE_TM), 2, 1) > '9'\n" +
                "OR SUBSTR (TRIM(A1.CLOSE_TM), 3, 1) < '0'\n" +
                "OR SUBSTR (TRIM(A1.CLOSE_TM), 3, 1) > '9'\n" +
                "OR SUBSTR (TRIM(A1.CLOSE_TM), 4, 1) < '0'\n" +
                "OR SUBSTR (TRIM(A1.CLOSE_TM), 4, 1) > '9'\n" +
                "OR SUBSTR (TRIM(A1.CLOSE_TM), 6, 1) < '0'\n" +
                "OR SUBSTR (TRIM(A1.CLOSE_TM), 6, 1) > '1'\n" +
                "OR SUBSTR (TRIM(A1.CLOSE_TM), 7, 1) < '0'\n" +
                "OR SUBSTR (TRIM(A1.CLOSE_TM), 7, 1) > '9'\n" +
                "OR SUBSTR (TRIM(A1.CLOSE_TM), 9, 1) < '0'\n" +
                "OR SUBSTR (TRIM(A1.CLOSE_TM), 9, 1) > '3'\n" +
                "OR SUBSTR (TRIM(A1.CLOSE_TM), 10, 1) < '0'\n" +
                "OR SUBSTR (TRIM(A1.CLOSE_TM), 10, 1) > '9'\n" +
                "OR SUBSTR (TRIM(A1.CLOSE_TM), 1, 4) = '0000'\n" +
                "OR SUBSTR (TRIM(A1.CLOSE_TM), 6, 2) = '00'\n" +
                "OR SUBSTR (TRIM(A1.CLOSE_TM), 9, 2) = '00'\n" +
                "OR SUBSTR (TRIM(A1.CLOSE_TM), 1, 1) = '0' THEN CAST(\n" +
                "CAST(\n" +
                "CAST('cDXtwdFyky' AS DATE FORMAT 'YYYYMMDD') AS DATE\n" +
                ") || ' 00:00:00' AS TIMESTAMP\n" +
                ")\n" +
                "ELSE (\n" +
                "CASE\n" +
                "WHEN (\n" +
                "CAST(SUBSTR (TRIM(A1.CLOSE_TM), 9, 2) AS INTEGER) < 29\n" +
                "AND SUBSTR (TRIM(A1.CLOSE_TM), 6, 2) = '02'\n" +
                ")\n" +
                "OR (\n" +
                "CAST(SUBSTR (TRIM(A1.CLOSE_TM), 9, 2) AS INTEGER) < 31\n" +
                "AND SUBSTR (TRIM(A1.CLOSE_TM), 6, 2) <> '02'\n" +
                "AND SUBSTR (TRIM(A1.CLOSE_TM), 6, 2) <= 12\n" +
                ")\n" +
                "OR (\n" +
                "CAST(SUBSTR (TRIM(A1.CLOSE_TM), 9, 2) AS INTEGER) = 31\n" +
                "AND SUBSTR (TRIM(A1.CLOSE_TM), 6, 2) IN ('01', '03', '05', '07', '08', '10', '12')\n"
                +
                ") THEN CAST(A1.CLOSE_TM AS TIMESTAMP)\n" +
                "WHEN SUBSTR (TRIM(A1.CLOSE_TM), 6, 2) || SUBSTR (TRIM(A1.CLOSE_TM), 9, 2) = '0229'\n"
                +
                "AND (\n" +
                "CAST(SUBSTR (TRIM(A1.CLOSE_TM), 1, 4) AS INTEGER) MOD 400 = 0\n" +
                "OR (\n" +
                "CAST(SUBSTR (TRIM(A1.CLOSE_TM), 1, 4) AS INTEGER) MOD 4 = 0\n" +
                "AND CAST(SUBSTR (TRIM(A1.CLOSE_TM), 1, 4) AS INTEGER) MOD 100 <> 0\n" +
                ")\n" +
                ") THEN CAST(A1.CLOSE_TM AS TIMESTAMP)\n" +
                "ELSE CAST(\n" +
                "CAST(\n" +
                "CAST('cDXtwdFyky' AS DATE FORMAT 'YYYYMMDD') AS DATE\n" +
                ") || ' 00:00:00' AS TIMESTAMP\n" +
                ")\n" +
                "END\n" +
                ")\n" +
                "END\n" +
                ") AS DATE FORMAT 'YYYYMMDD'\n" +
                ")\n" +
                "FROM\n" +
                "T01_PTY_SIGN_H_T1 A1\n" +
                "WHERE\n" +
                "A1.PARTY_SIGN_TYPE_CD = 'CD_021'\n" +
                "AND A1.ST_DT <= CAST('LDBCGtCIyo' AS DATE FORMAT 'YYYYMMDD')\n" +
                "AND A1.END_DT > CAST('LDBCGtCIyo' AS DATE FORMAT 'YYYYMMDD')\n" +
                "GROUP BY\n" +
                "1,\n" +
                "2,\n" +
                "3,\n" +
                "4";
        CCJSqlParserUtil.parse(sqlStr, parser -> parser
                .withTimeOut(60000));
    }

    @Test
    @Disabled
    // see https://github.com/javacc/javacc/issues/296
    void testIssue2140() throws JSQLParserException {
        String sqlStr = "(((IIF((CASE WHEN 1 = 2 THEN 'a' ELSE 'b') = 'b'), 2, 3)))";

        CCJSqlParserUtil.parseExpression(
                sqlStr, true, parser -> parser
                        .withTimeOut(10000));
    }
}
