Repository navigation
Could not parse complex COALESCE/CAST #1607
Description
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.
Activity
[-]Could not parse complex COALSECE / CAST[/-][+]Could not parse complex CAST[/+]manticore-projects commentedon Aug 2, 2022
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.
[-]Could not parse complex CAST[/-][+]Could not parse complex COALESCE/CAST[/+]d2a-raudenaerde commentedon Aug 2, 2022
Ah (sorry for the editting)
I'll check the PR.
manticore-projects commentedon Aug 2, 2022
What I can do: I can update the PR to reflect the latest release and then you can give it a try.
d2a-raudenaerde commentedon Aug 2, 2022
That would be nice :)
manticore-projects commentedon Aug 2, 2022
Tomorrow first thing in the morning, I am running late here. cheers.
d2a-raudenaerde commentedon Aug 2, 2022
No hurry! :)
d2a-raudenaerde commentedon Aug 2, 2022
Btw: it is not a keyword according to the docs, but rather a function:
CURRENT_TIMESTAMP(precision)
d2a-raudenaerde commentedon Aug 2, 2022
So, maybe instead of building support, we need to remove the
K_TIME_KEY_EXPRso the parser just parses a function?d2a-raudenaerde commentedon Aug 2, 2022
I removed it and the parse rules involving it, and now it parses fine :)
If something like the
TimeKeyExpressionis needed somewhere, I think theFunctionparse rules could use some IF statement matching the functions name and return aTimeFunction(as that would be a more correct name)?d2a-raudenaerde commentedon Aug 2, 2022
Mmm. It fails the
CURRENT TIMEtest 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)d2a-raudenaerde commentedon Aug 2, 2022
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?)
d2a-raudenaerde commentedon Aug 2, 2022
I think it would be rather straight-forward to implement these rules, and allow the tokens to be both with underscore, and without.
manticore-projects commentedon Aug 2, 2022
CURRENT_TIMESTAMPis 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.
manticore-projects commentedon Aug 2, 2022
With and without underscore is supported already.
d2a-raudenaerde commentedon Aug 2, 2022
Ah ok, I will be patient and wait for you PR :)
manticore-projects commentedon Aug 2, 2022
The PR #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.
manticore-projects commentedon Nov 12, 2022
Still relevant with JSQLParser 4.6 Snapshot.
fix: White-list CURRENT_DATE and CURRENT_TIMESTAMP tokens
d2a-raudenaerde commentedon Nov 14, 2022
Cool, thanks!