[BUG] JSQLParser 5.1 : Oracle : special table function JSON_TABLE

[baliure]

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

Failing SQL Feature:

• CASE WHEN EXISTS can't be parsed

SQL Example:

SELECT id,
RECORD_ID,
MODULE_ID,
PAGE_ID,
TO_CHAR(MODIFY_DATE, 'yyyy-MM-dd') MODIFY_DATE_STR,
CASE WHEN EXISTS (SELECT 0 FROM JSON_TABLE(READ_USER, '$[*]' COLUMNS(value NUMBER(20) PATH '$'))
WHERE value = '-110') THEN
'已读'
ELSE
'未读'
END READ_STATUS

from BMSSETT.VERSION_RECORD_DETAIL

Software Information:

• JSqlParser version 4.6-5.1
• Database (Oracle 19c)

Tips:

net.sf.jsqlparser.JSQLParserException: Time out occurred.
SELECT id, RECORD_ID, MODULE_ID, PAGE_ID, TO_CHAR(MODIFY_DATE, 'yyyy-MM-dd') MODIFY_DATE_STR, CASE WHEN EXISTS (SELECT 0 FROM JSON_TABLE(READ_USER, '$[*]' COLUMNS(value NUMBER(20) PATH '$')) WHERE value = '-110') THEN '已读' ELSE '未读' END READ_STATUS from BMSSETT.VERSION_RECORD_DETAIL
at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatement(CCJSqlParserUtil.java:261)
at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:81)
at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:47)
at cn.dazd.utils.jsqlparser.SqlOptimize.onlyQueryColumn(SqlOptimize.java:240)
at cn.dazd.utils.jsqlparser.SqlOptimize.onlyQueryColumn(SqlOptimize.java:254)
at cn.dazd.utils.jsqlparser.SqlOptimizeTestMain.main(SqlOptimizeTestMain.java:59)
Caused by: java.util.concurrent.TimeoutException
at java.util.concurrent.FutureTask.get(FutureTask.java:205)
at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatement(CCJSqlParserUtil.java:258)
... 5 more

[manticore-projects]

Greetings.

Its not about the Exists but about the special table function JSON_TABLE which is not supported.