Could not parse complex COALESCE/CAST

[d2a-raudenaerde]

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.

[manticore-projects]

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.

[d2a-raudenaerde]

Ah (sorry for the editting)
I'll check the PR.

[manticore-projects]

What I can do: I can update the PR to reflect the latest release and then you can give it a try.

[d2a-raudenaerde]

That would be nice :)

[manticore-projects]

Tomorrow first thing in the morning, I am running late here. cheers.

[d2a-raudenaerde]

No hurry! :)

[d2a-raudenaerde]

Btw: it is not a keyword according to the docs, but rather a function:

CURRENT_TIMESTAMP(precision)

[d2a-raudenaerde]

So, maybe instead of building support, we need to remove the K_TIME_KEY_EXPR so the parser just parses a function?

[d2a-raudenaerde]

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)?

[d2a-raudenaerde]

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)

[d2a-raudenaerde]

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> ]

[d2a-raudenaerde]

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]

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.

[manticore-projects]

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.

[d2a-raudenaerde]

Ah ok, I will be patient and wait for you PR :)

[manticore-projects]

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]

Still relevant with JSQLParser 4.6 Snapshot.

[d2a-raudenaerde]

Cool, thanks!