[PERF] JSQLParser Version : parse timeout when calling many nested functions #1983

young0098 · 2024-03-31T02:01:35Z

Always check against the Latest SNAPSHOT of JSQLParser and the Syntax Diagram

Failing SQL Feature:

parse timeout，just a common sql , haved set timeout argument to 3 minutes.
Example: WITH ROLLUP can't be parsed

SQL Example:

Simplified Query Example, focusing on the failing feature

INSERT INTO
C01_INDIV_TELBK_CUST_INFO_H_T2 (PARTY_ID, PARTY_SIGN_STAT_CD, SIGN_TM, CLOSE_TM)
SELECT
A1.PARTY_ID,
A1.PARTY_SIGN_STAT_CD,
CAST(
(
CASE
WHEN A1.SIGN_TM IS NULL
OR A1.SIGN_TM = '' THEN CAST(
CAST(
CAST('ATkkIVQJZm' AS DATE FORMAT 'YYYYMMDD') AS DATE
) || ' 00:00:00' AS TIMESTAMP
)
WHEN CHARACTERS (TRIM(A1.SIGN_TM)) <> 19
OR SUBSTR (TRIM(A1.SIGN_TM), 1, 1) < '0'
OR SUBSTR (TRIM(A1.SIGN_TM), 1, 1) > '9'
OR SUBSTR (TRIM(A1.SIGN_TM), 2, 1) < '0'
OR SUBSTR (TRIM(A1.SIGN_TM), 2, 1) > '9'
OR SUBSTR (TRIM(A1.SIGN_TM), 3, 1) < '0'
OR SUBSTR (TRIM(A1.SIGN_TM), 3, 1) > '9'
OR SUBSTR (TRIM(A1.SIGN_TM), 4, 1) < '0'
OR SUBSTR (TRIM(A1.SIGN_TM), 4, 1) > '9'
OR SUBSTR (TRIM(A1.SIGN_TM), 6, 1) < '0'
OR SUBSTR (TRIM(A1.SIGN_TM), 6, 1) > '1'
OR SUBSTR (TRIM(A1.SIGN_TM), 7, 1) < '0'
OR SUBSTR (TRIM(A1.SIGN_TM), 7, 1) > '9'
OR SUBSTR (TRIM(A1.SIGN_TM), 9, 1) < '0'
OR SUBSTR (TRIM(A1.SIGN_TM), 9, 1) > '3'
OR SUBSTR (TRIM(A1.SIGN_TM), 10, 1) < '0'
OR SUBSTR (TRIM(A1.SIGN_TM), 10, 1) > '9'
OR SUBSTR (TRIM(A1.SIGN_TM), 1, 4) = '0000'
OR SUBSTR (TRIM(A1.SIGN_TM), 6, 2) = '00'
OR SUBSTR (TRIM(A1.SIGN_TM), 9, 2) = '00'
OR SUBSTR (TRIM(A1.SIGN_TM), 1, 1) = '0' THEN CAST(
CAST(
CAST('cDXtwdFyky' AS DATE FORMAT 'YYYYMMDD') AS DATE
) || ' 00:00:00' AS TIMESTAMP
)
ELSE (
CASE
WHEN (
CAST(SUBSTR (TRIM(A1.SIGN_TM), 9, 2) AS INTEGER) < 29
AND SUBSTR (TRIM(A1.SIGN_TM), 6, 2) = '02'
)
OR (
CAST(SUBSTR (TRIM(A1.SIGN_TM), 9, 2) AS INTEGER) < 31
AND SUBSTR (TRIM(A1.SIGN_TM), 6, 2) <> '02'
AND SUBSTR (TRIM(A1.SIGN_TM), 6, 2) <= 12
)
OR (
CAST(SUBSTR (TRIM(A1.SIGN_TM), 9, 2) AS INTEGER) = 31
AND SUBSTR (TRIM(A1.SIGN_TM), 6, 2) IN ('01', '03', '05', '07', '08', '10', '12')
) THEN CAST(A1.SIGN_TM AS TIMESTAMP)
WHEN SUBSTR (TRIM(A1.SIGN_TM), 6, 2) || SUBSTR (TRIM(A1.SIGN_TM), 9, 2) = '0229'
AND (
CAST(SUBSTR (TRIM(A1.SIGN_TM), 1, 4) AS INTEGER) MOD 400 = 0
OR (
CAST(SUBSTR (TRIM(A1.SIGN_TM), 1, 4) AS INTEGER) MOD 4 = 0
AND CAST(SUBSTR (TRIM(A1.SIGN_TM), 1, 4) AS INTEGER) MOD 100 <> 0
)
) THEN CAST(A1.SIGN_TM AS TIMESTAMP)
ELSE CAST(
CAST(
CAST('cDXtwdFyky' AS DATE FORMAT 'YYYYMMDD') AS DATE
) || ' 00:00:00' AS TIMESTAMP
)
END
)
END
) AS DATE FORMAT 'YYYYMMDD'
),
CAST(
(
CASE
WHEN A1.CLOSE_TM IS NULL
OR A1.CLOSE_TM = '' THEN CAST(
CAST(
CAST('ATkkIVQJZm' AS DATE FORMAT 'YYYYMMDD') AS DATE
) || ' 00:00:00' AS TIMESTAMP
)
WHEN CHARACTERS (TRIM(A1.CLOSE_TM)) <> 19
OR SUBSTR (TRIM(A1.CLOSE_TM), 1, 1) < '0'
OR SUBSTR (TRIM(A1.CLOSE_TM), 1, 1) > '9'
OR SUBSTR (TRIM(A1.CLOSE_TM), 2, 1) < '0'
OR SUBSTR (TRIM(A1.CLOSE_TM), 2, 1) > '9'
OR SUBSTR (TRIM(A1.CLOSE_TM), 3, 1) < '0'
OR SUBSTR (TRIM(A1.CLOSE_TM), 3, 1) > '9'
OR SUBSTR (TRIM(A1.CLOSE_TM), 4, 1) < '0'
OR SUBSTR (TRIM(A1.CLOSE_TM), 4, 1) > '9'
OR SUBSTR (TRIM(A1.CLOSE_TM), 6, 1) < '0'
OR SUBSTR (TRIM(A1.CLOSE_TM), 6, 1) > '1'
OR SUBSTR (TRIM(A1.CLOSE_TM), 7, 1) < '0'
OR SUBSTR (TRIM(A1.CLOSE_TM), 7, 1) > '9'
OR SUBSTR (TRIM(A1.CLOSE_TM), 9, 1) < '0'
OR SUBSTR (TRIM(A1.CLOSE_TM), 9, 1) > '3'
OR SUBSTR (TRIM(A1.CLOSE_TM), 10, 1) < '0'
OR SUBSTR (TRIM(A1.CLOSE_TM), 10, 1) > '9'
OR SUBSTR (TRIM(A1.CLOSE_TM), 1, 4) = '0000'
OR SUBSTR (TRIM(A1.CLOSE_TM), 6, 2) = '00'
OR SUBSTR (TRIM(A1.CLOSE_TM), 9, 2) = '00'
OR SUBSTR (TRIM(A1.CLOSE_TM), 1, 1) = '0' THEN CAST(
CAST(
CAST('cDXtwdFyky' AS DATE FORMAT 'YYYYMMDD') AS DATE
) || ' 00:00:00' AS TIMESTAMP
)
ELSE (
CASE
WHEN (
CAST(SUBSTR (TRIM(A1.CLOSE_TM), 9, 2) AS INTEGER) < 29
AND SUBSTR (TRIM(A1.CLOSE_TM), 6, 2) = '02'
)
OR (
CAST(SUBSTR (TRIM(A1.CLOSE_TM), 9, 2) AS INTEGER) < 31
AND SUBSTR (TRIM(A1.CLOSE_TM), 6, 2) <> '02'
AND SUBSTR (TRIM(A1.CLOSE_TM), 6, 2) <= 12
)
OR (
CAST(SUBSTR (TRIM(A1.CLOSE_TM), 9, 2) AS INTEGER) = 31
AND SUBSTR (TRIM(A1.CLOSE_TM), 6, 2) IN ('01', '03', '05', '07', '08', '10', '12')
) THEN CAST(A1.CLOSE_TM AS TIMESTAMP)
WHEN SUBSTR (TRIM(A1.CLOSE_TM), 6, 2) || SUBSTR (TRIM(A1.CLOSE_TM), 9, 2) = '0229'
AND (
CAST(SUBSTR (TRIM(A1.CLOSE_TM), 1, 4) AS INTEGER) MOD 400 = 0
OR (
CAST(SUBSTR (TRIM(A1.CLOSE_TM), 1, 4) AS INTEGER) MOD 4 = 0
AND CAST(SUBSTR (TRIM(A1.CLOSE_TM), 1, 4) AS INTEGER) MOD 100 <> 0
)
) THEN CAST(A1.CLOSE_TM AS TIMESTAMP)
ELSE CAST(
CAST(
CAST('cDXtwdFyky' AS DATE FORMAT 'YYYYMMDD') AS DATE
) || ' 00:00:00' AS TIMESTAMP
)
END
)
END
) AS DATE FORMAT 'YYYYMMDD'
)
FROM
T01_PTY_SIGN_H_T1 A1
WHERE
A1.PARTY_SIGN_TYPE_CD = 'CD_021'
AND A1.ST_DT <= CAST('LDBCGtCIyo' AS DATE FORMAT 'YYYYMMDD')
AND A1.END_DT > CAST('LDBCGtCIyo' AS DATE FORMAT 'YYYYMMDD')
GROUP BY
1,
2,
3,
4
```

Software Information:

JSqlParser 4.8
Database Teradata

Tips:

Please write in English and avoid Screenshots (as we can't copy and paste content from it).
Try your example online with the latest JSQLParser and share the link in the error report.
Do provide Links or References to the specific Grammar and Syntax you are trying to use.

The text was updated successfully, but these errors were encountered:

manticore-projects · 2024-03-31T02:12:03Z

The query fails after 52 seconds:

CAST( expression AS DATE FORMAT char_literal) is not supported.

manticore-projects · 2024-03-31T02:13:46Z

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
                .withSquareBracketQuotation(false)
                .withAllowComplexParsing(true)
                .withTimeOut(60000));
    }

manticore-projects · 2024-03-31T02:19:48Z

Seems to be Google BigQuery.
Lucky you as I am going to implement this special FORMAT clause.

manticore-projects · 2024-03-31T02:54:55Z

I have implemented Google BigQuery CAST with FORMAT clause and in the result the query does not fail anymore after 1 minute.

Instead it literally runs forever, likely because of the way how you call FUNCTION (which depends on expensive semantic LOOKAHEAD amplifying when nested deeply).

I can only encourage you to write more reasonable SQL. e.g. instead of calling SUBSTR (TRIM(A1.CLOSE_TM), x) a million times, you could just Split() your string into characters and then access this array.

Unfortunately I don't have the time to dig deeper into this particular statement right now. You can try to remove logical blocks until it parses normally in order to isolate the particular logical block where it starts hanging. Once we get this analysis, we can certainly do something about in the Grammar.

Good luck.

young0098 · 2024-03-31T05:34:56Z

Thanks. This is old code, I have to paser it. I change cast expression manually, delete format keywords. The timeout exception is remain. If I want to pasrse this query successfully, How many timeout I should set？
INSERT INTO
C01_INDIV_TELBK_CUST_INFO_H_T2 (PARTY_ID, PARTY_SIGN_STAT_CD, SIGN_TM, CLOSE_TM)
SELECT
A1.PARTY_ID,
A1.PARTY_SIGN_STAT_CD,
CAST(
(
CASE
WHEN A1.SIGN_TM IS NULL
OR A1.SIGN_TM = '' THEN CAST(
CAST(CAST('UsGgJQoxuH' AS DATE) AS DATE) || ' 00:00:00' AS TIMESTAMP
)
WHEN CHARACTERS (TRIM(A1.SIGN_TM)) <> 19
OR SUBSTR (TRIM(A1.SIGN_TM), 1, 1) < '0'
OR SUBSTR (TRIM(A1.SIGN_TM), 1, 1) > '9'
OR SUBSTR (TRIM(A1.SIGN_TM), 2, 1) < '0'
OR SUBSTR (TRIM(A1.SIGN_TM), 2, 1) > '9'
OR SUBSTR (TRIM(A1.SIGN_TM), 3, 1) < '0'
OR SUBSTR (TRIM(A1.SIGN_TM), 3, 1) > '9'
OR SUBSTR (TRIM(A1.SIGN_TM), 4, 1) < '0'
OR SUBSTR (TRIM(A1.SIGN_TM), 4, 1) > '9'
OR SUBSTR (TRIM(A1.SIGN_TM), 6, 1) < '0'
OR SUBSTR (TRIM(A1.SIGN_TM), 6, 1) > '1'
OR SUBSTR (TRIM(A1.SIGN_TM), 7, 1) < '0'
OR SUBSTR (TRIM(A1.SIGN_TM), 7, 1) > '9'
OR SUBSTR (TRIM(A1.SIGN_TM), 9, 1) < '0'
OR SUBSTR (TRIM(A1.SIGN_TM), 9, 1) > '3'
OR SUBSTR (TRIM(A1.SIGN_TM), 10, 1) < '0'
OR SUBSTR (TRIM(A1.SIGN_TM), 10, 1) > '9'
OR SUBSTR (TRIM(A1.SIGN_TM), 1, 4) = '0000'
OR SUBSTR (TRIM(A1.SIGN_TM), 6, 2) = '00'
OR SUBSTR (TRIM(A1.SIGN_TM), 9, 2) = '00'
OR SUBSTR (TRIM(A1.SIGN_TM), 1, 1) = '0' THEN CAST(
CAST(CAST('airHppDlix' AS DATE) AS DATE) || ' 00:00:00' AS TIMESTAMP
)
ELSE (
CASE
WHEN (
CAST(SUBSTR (TRIM(A1.SIGN_TM), 9, 2) AS INTEGER) < 29
AND SUBSTR (TRIM(A1.SIGN_TM), 6, 2) = '02'
)
OR (
CAST(SUBSTR (TRIM(A1.SIGN_TM), 9, 2) AS INTEGER) < 31
AND SUBSTR (TRIM(A1.SIGN_TM), 6, 2) <> '02'
AND SUBSTR (TRIM(A1.SIGN_TM), 6, 2) <= 12
)
OR (
CAST(SUBSTR (TRIM(A1.SIGN_TM), 9, 2) AS INTEGER) = 31
AND SUBSTR (TRIM(A1.SIGN_TM), 6, 2) IN ('01', '03', '05', '07', '08', '10', '12')
) THEN CAST(A1.SIGN_TM AS TIMESTAMP)
WHEN SUBSTR (TRIM(A1.SIGN_TM), 6, 2) || SUBSTR (TRIM(A1.SIGN_TM), 9, 2) = '0229'
AND (
CAST(SUBSTR (TRIM(A1.SIGN_TM), 1, 4) AS INTEGER) MOD 400 = 0
OR (
CAST(SUBSTR (TRIM(A1.SIGN_TM), 1, 4) AS INTEGER) MOD 4 = 0
AND CAST(SUBSTR (TRIM(A1.SIGN_TM), 1, 4) AS INTEGER) MOD 100 <> 0
)
) THEN CAST(A1.SIGN_TM AS TIMESTAMP)
ELSE CAST(
CAST(CAST('airHppDlix' AS DATE) AS DATE) || ' 00:00:00' AS TIMESTAMP
)
END
)
END
) AS DATE
),
CAST(
(
CASE
WHEN A1.CLOSE_TM IS NULL
OR A1.CLOSE_TM = '' THEN CAST(
CAST(CAST('UsGgJQoxuH' AS DATE) AS DATE) || ' 00:00:00' AS TIMESTAMP
)
WHEN CHARACTERS (TRIM(A1.CLOSE_TM)) <> 19
OR SUBSTR (TRIM(A1.CLOSE_TM), 1, 1) < '0'
OR SUBSTR (TRIM(A1.CLOSE_TM), 1, 1) > '9'
OR SUBSTR (TRIM(A1.CLOSE_TM), 2, 1) < '0'
OR SUBSTR (TRIM(A1.CLOSE_TM), 2, 1) > '9'
OR SUBSTR (TRIM(A1.CLOSE_TM), 3, 1) < '0'
OR SUBSTR (TRIM(A1.CLOSE_TM), 3, 1) > '9'
OR SUBSTR (TRIM(A1.CLOSE_TM), 4, 1) < '0'
OR SUBSTR (TRIM(A1.CLOSE_TM), 4, 1) > '9'
OR SUBSTR (TRIM(A1.CLOSE_TM), 6, 1) < '0'
OR SUBSTR (TRIM(A1.CLOSE_TM), 6, 1) > '1'
OR SUBSTR (TRIM(A1.CLOSE_TM), 7, 1) < '0'
OR SUBSTR (TRIM(A1.CLOSE_TM), 7, 1) > '9'
OR SUBSTR (TRIM(A1.CLOSE_TM), 9, 1) < '0'
OR SUBSTR (TRIM(A1.CLOSE_TM), 9, 1) > '3'
OR SUBSTR (TRIM(A1.CLOSE_TM), 10, 1) < '0'
OR SUBSTR (TRIM(A1.CLOSE_TM), 10, 1) > '9'
OR SUBSTR (TRIM(A1.CLOSE_TM), 1, 4) = '0000'
OR SUBSTR (TRIM(A1.CLOSE_TM), 6, 2) = '00'
OR SUBSTR (TRIM(A1.CLOSE_TM), 9, 2) = '00'
OR SUBSTR (TRIM(A1.CLOSE_TM), 1, 1) = '0' THEN CAST(
CAST(CAST('airHppDlix' AS DATE) AS DATE) || ' 00:00:00' AS TIMESTAMP
)
ELSE (
CASE
WHEN (
CAST(SUBSTR (TRIM(A1.CLOSE_TM), 9, 2) AS INTEGER) < 29
AND SUBSTR (TRIM(A1.CLOSE_TM), 6, 2) = '02'
)
OR (
CAST(SUBSTR (TRIM(A1.CLOSE_TM), 9, 2) AS INTEGER) < 31
AND SUBSTR (TRIM(A1.CLOSE_TM), 6, 2) <> '02'
AND SUBSTR (TRIM(A1.CLOSE_TM), 6, 2) <= 12
)
OR (
CAST(SUBSTR (TRIM(A1.CLOSE_TM), 9, 2) AS INTEGER) = 31
AND SUBSTR (TRIM(A1.CLOSE_TM), 6, 2) IN ('01', '03', '05', '07', '08', '10', '12')
) THEN CAST(A1.CLOSE_TM AS TIMESTAMP)
WHEN SUBSTR (TRIM(A1.CLOSE_TM), 6, 2) || SUBSTR (TRIM(A1.CLOSE_TM), 9, 2) = '0229'
AND (
CAST(SUBSTR (TRIM(A1.CLOSE_TM), 1, 4) AS INTEGER) MOD 400 = 0
OR (
CAST(SUBSTR (TRIM(A1.CLOSE_TM), 1, 4) AS INTEGER) MOD 4 = 0
AND CAST(SUBSTR (TRIM(A1.CLOSE_TM), 1, 4) AS INTEGER) MOD 100 <> 0
)
) THEN CAST(A1.CLOSE_TM AS TIMESTAMP)
ELSE CAST(
CAST(CAST('airHppDlix' AS DATE) AS DATE) || ' 00:00:00' AS TIMESTAMP
)
END
)
END
) AS DATE
)
FROM
T01_PTY_SIGN_H_T1 A1
WHERE
A1.PARTY_SIGN_TYPE_CD = 'CD_021'
AND A1.ST_DT <= CAST('oXKthfFlJH' AS DATE)
AND A1.END_DT > CAST('oXKthfFlJH' AS DATE)
GROUP BY
1,
2,
3,
4

manticore-projects · 2024-03-31T05:37:31Z

I really don't know, you can only try out by yourself.

manticore-projects mentioned this issue Mar 31, 2024

[Feedback] JSQLParser Version : 4.5, CCJSqlParserUtil.parseStatement(parser) method execution blocks Vert.x event loop #1981

Closed

manticore-projects changed the title ~~[BUG] JSQLParser Version : RDBMS : parse timeout, a simply sql, even set timeout to 3 minutes.~~ [BUG] JSQLParser Version : parse timeout when calling many nested functions Mar 31, 2024

manticore-projects self-assigned this Mar 31, 2024

manticore-projects added the improvement label Mar 31, 2024

manticore-projects changed the title ~~[BUG] JSQLParser Version : parse timeout when calling many nested functions~~ [PERF] JSQLParser Version : parse timeout when calling many nested functions Mar 31, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

[PERF] JSQLParser Version : parse timeout when calling many nested functions #1983

[PERF] JSQLParser Version : parse timeout when calling many nested functions #1983

young0098 commented Mar 31, 2024

manticore-projects commented Mar 31, 2024

manticore-projects commented Mar 31, 2024

manticore-projects commented Mar 31, 2024 •

edited

manticore-projects commented Mar 31, 2024

young0098 commented Mar 31, 2024

manticore-projects commented Mar 31, 2024

[PERF] JSQLParser Version : parse timeout when calling many nested functions #1983

[PERF] JSQLParser Version : parse timeout when calling many nested functions #1983

Comments

young0098 commented Mar 31, 2024

Failing SQL Feature:

SQL Example:

Software Information:

Tips:

manticore-projects commented Mar 31, 2024

manticore-projects commented Mar 31, 2024

manticore-projects commented Mar 31, 2024 • edited

manticore-projects commented Mar 31, 2024

young0098 commented Mar 31, 2024

manticore-projects commented Mar 31, 2024

manticore-projects commented Mar 31, 2024 •

edited