`DAY TO SECOND` is not supported

[realizm]

Actual Behavior

Caused by: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "DAY" <S_IDENTIFIER>
at line 1, column 71.

Was expecting one of:

"&"
")"
"::"
"<<"
">>"
"^"
"|"

at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:18047)

Expected Behavior

Steps to Reproduce the Problem

String sql = "select EXTRACT(DAY FROM (SYSDATE - to_date('20180101', 'YYYYMMDD' ) ) DAY TO SECOND) from dual;";

JSqlParser jSqlParser = new CCJSqlParserManager();
try {
jSqlParser.parse(new StringReader(sql));
} catch (JSQLParserException e) {
e.printStackTrace();
}

Specifications

• Version:
• Platform:
• Subsystem:

[mvasr7]

How to solve this? Facing the same problem

[manticore-projects]

DAY FROM ... and DAY TO ... are not supported since it is not SQL Standard compliant.
You can either provide or sponsor an implementation for the platform specific syntax.

[wumpz]

@manticore-projects This is not quite right, since SpecialStringFunctionWithNamedParameters with NamedExpressionListExprFirst and the following is able to accept e.g. substring( a from b). This way the extract should be accepted as well.

This is parsed

select EXTRACT(DAY FROM (SYSDATE - to_date('20180101', 'YYYYMMDD' ) ) ) from dual;

So maybe this DAY TO SECONDS can be implemented this way as well.

[manticore-projects]

In my opinion, this particular production NamedExpressionListExprFirst is cursed (since it depends on a semantic LOOKAHEAD) and should be replaced with a proper implementation of the SQL:2016 standard functions. I did convert() and trim() already and will do the others next.

NameExpressionList must die (although I have tamed it already by streamlining it into an extension of ExpressionList).

[wumpz]

@manticore-projects :) You are right, it was a hack, but since you already started discussing a new version, isn't that a bit to much?
The lookahead here is not that bad, since it is drastically filtered by this function name list.

[manticore-projects]

I leave it to you to decide. In the current development stage it extends ExpressionList and so fits in much better.
However, I consider it a kind of generic fallback, while I still would provide the SQL:2016 compliant functions separately.

(It's a shame that SQL standard allowed those syntax, when regular functions with comma separated parameters would have done a fine job.)

[fudianchn]

Verified on current master (406a4d4): the exact statement from this issue parses and round-trips.

select EXTRACT(DAY FROM (SYSDATE - to_date('20180101', 'YYYYMMDD' ) ) DAY TO SECOND)  from dual;

CCJSqlParserUtil.parse(...) succeeds, toString() returns the same statement (whitespace normalized, trailing ; dropped as usual) and the output reparses identically, with the DAY TO SECOND suffix preserved. The EXTRACT(...) part is parsed into ExtractExpression.

For completeness: the plain interval literal SELECT INTERVAL '3 12:30:06.5' DAY TO SECOND FROM DUAL parses and round-trips as well (through the IntervalQualifier from #2456).

This looks fixed to me.