JSqlParser
JSqlParser copied to clipboard
JSQLParser
[PERF] JSQLParser Version : parse timeout when calling many nested functions
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 ROLLUPcan'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 query fails after 52 seconds:
CAST( expression AS DATE FORMAT char_literal) is not supported.
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));
}
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.
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