JSqlParser
JSqlParser copied to clipboard
JSQLParser
Could not parse complex COALESCE/CAST
This bit of (Postgresql valid) SQL fails:
select
t0.*
, cr.code
from
mytable t0
join othertable cr ON cr.code = '1234'
where
COALESCE (cast(to_date(to_char(t0.datum_ontslagen,'YYYYMMDD'),'YYYYMMDD')||' '||t0.tijd_ontslagen as timestamp), current_timestamp(0)) -
cast(to_date(to_char(t0.datum_opname,'YYYYMMDD'),'YYYYMMDD')||' '||t0.tijd_opname as timestamp)
>= (SELECT somefunction()) * interval '1 hours'
net.sf.jsqlparser.JSQLParserException: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "(" "(" at line 8, column 13.
Greetings.
I think its this one: current_timestamp(0) -- current_timestamp needs to be whitelisted as keyword so it can be interpreted as a function name.
There is a 6 month old PR regarding a proper keyword handling. But it has not gotten much interested yet.
What I can do: I can update the PR to reflect the latest release and then you can give it a try.
Tomorrow first thing in the morning, I am running late here. cheers.
Btw: it is not a keyword according to the docs, but rather a function:
So, maybe instead of building support, we need to remove the K_TIME_KEY_EXPR so the parser just parses a function?
I removed it and the parse rules involving it, and now it parses fine :)
If something like the TimeKeyExpression is needed somewhere, I think the Function parse rules could use some IF statement matching the functions name and return a TimeFunction (as that would be a more correct name)?
Mmm. It fails the CURRENT TIME test for db2 (is that even valid SQL?)
That can be fixed by instead of removing the K_TIME_KEY_EXPR, we keep it but only the tokens with a ' ' (so the _ will be handled by the function parsing rules)
Btw: the sql2003standard does have a special case for these datetime functions: (but not the syntax db2 apparently uses, so that should perhapse be a capability setting?)
<datetime value function> ::=
<current date value function>
| <current time value function>
| <current timestamp value function>
| <current local time value function>
| <current local timestamp value function>
<current date value function> ::= CURRENT_DATE
<current time value function> ::= CURRENT_TIME [ <left paren> <time precision> <right paren> ]
<current local time value function> ::= LOCALTIME [ <left paren> <time precision> <right paren> ]
<current timestamp value function> ::= CURRENT_TIMESTAMP [ <left paren> <timestamp precision> <right paren> ]
<current local timestamp value function> ::= LOCALTIMESTAMP [ <left paren> <timestamp precision> <right paren> ]
I think it would be rather straight-forward to implement these rules, and allow the tokens to be both with underscore, and without.
CURRENT_TIMESTAMP is defined as a Token in JSQLParser and this token has not been whitelisted yet for the use of function names.
My PR fixes this re-occurring problem in a generic way and for ALL tokens, not only this one.
I think it would be rather straight-forward to implement these rules, and allow the tokens to be both with underscore, and without.
With and without underscore is supported already.
The PR https://github.com/JSQLParser/JSqlParser/pull/1382 is there already, I just need to resolve conflicts. You can read through the lengthy discussion already for understanding what it does and how it works.