[BUG] Fix automatic cast string to temporal on function call

[Yury-Fridlyand]

What is the bug?

Prereq: a function which accepts a DATE and DATETIME as an argument:

private SerializableFunction<?, ?>[] get_date_add_signatures() {
	return new SerializableFunction[]{
		impl(nullMissingHandling(DateTimeFunction::exprAddDateInterval), DATETIME, DATE, INTERVAL),
		impl(nullMissingHandling(DateTimeFunction::exprAddDateInterval), DATETIME, DATETIME, INTERVAL),
		impl(nullMissingHandling(DateTimeFunction::exprAddDateInterval), DATETIME, TIMESTAMP, INTERVAL),
		impl(nullMissingHandling(DateTimeFunction::exprAddDateInterval), DATETIME, TIME, INTERVAL),
	};
}

The query:

select date_add('2020-09-16 17:30:00', interval 1 day);
TransportError(500, 'SemanticCheckException', {'error': {'type': 'SemanticCheckException', 'reason': 'Invalid Query', 'details': 'date:2020-09-16 17:30:00 in unsupported format, please use yyyy-MM-dd'}, 'status': 400})

PPL:

source=date | eval f = date_add('2020-09-16 17:30:00', interval 1 day) | fields f;
{'reason': 'Invalid Query', 'details': 'date:2020-09-16 17:30:00 in unsupported format, please use yyyy-MM-dd', 'type': 'SemanticCheckException'}

Exception stack

org.opensearch.sql.exception.SemanticCheckException: date:2020-09-16 17:30:00 in unsupported format, please use yyyy-MM-dd
        at org.opensearch.sql.data.model.ExprDateValue.<init>(ExprDateValue.java:38) ~[core-2.4.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.expression.operator.convert.TypeCastOperator.lambda$castToDate$12c7dc48$1(TypeCastOperator.java:158) ~[core-2.4.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.expression.function.FunctionDSL.lambda$nullMissingHandling$878069c8$1(FunctionDSL.java:234) ~[core-2.4.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.expression.function.FunctionDSL$2.valueOf(FunctionDSL.java:117) ~[core-2.4.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.expression.function.FunctionDSL$3.valueOf(FunctionDSL.java:159) ~[core-2.4.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.expression.NamedExpression.valueOf(NamedExpression.java:46) ~[core-2.4.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.planner.physical.ProjectOperator.next(ProjectOperator.java:59) ~[core-2.4.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.planner.physical.ProjectOperator.next(ProjectOperator.java:28) ~[core-2.4.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.opensearch.executor.OpenSearchExecutionEngine.lambda$execute$0(OpenSearchExecutionEngine.java:40) [opensearch-2.4.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.opensearch.client.OpenSearchNodeClient.schedule(OpenSearchNodeClient.java:157) [opensearch-2.4.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.opensearch.executor.OpenSearchExecutionEngine.execute(OpenSearchExecutionEngine.java:33) [opensearch-2.4.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.sql.SQLService.execute(SQLService.java:66) [sql-2.4.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.legacy.plugin.RestSQLQueryAction.lambda$prepareRequest$1(RestSQLQueryAction.java:123) [legacy-2.4.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.legacy.plugin.RestSqlAction.lambda$prepareRequest$1(RestSqlAction.java:162) [legacy-2.4.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.opensearch.executor.Scheduler.lambda$withCurrentContext$0(Scheduler.java:30) [opensearch-2.4.0.0-SNAPSHOT.jar:?]
        at org.opensearch.common.util.concurrent.ThreadContext$ContextPreservingRunnable.run(ThreadContext.java:747) [opensearch-2.4.0-SNAPSHOT.jar:2.4.0-SNAPSHOT]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) [?:?]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) [?:?]
        at java.lang.Thread.run(Thread.java:829) [?:?]

Why it fails?

FunctionResolver grabs first signature (DATE, INTERVAL) -> DATETIME and checks whether input could be casted to the chosen argument type according to the cast map. Yes, it could. Important: no cast attempt done yet. Then Resolver inserts a cast method to convert the type and tries to execute resulting function chain. It fails.

date_add(castToDate('2020-09-16 17:30:00'), ...)

What is the expected behavior?

The argument type is undefined at the moment when Resolver picks a signature to use. It should interact with automatic cast mechanism better to try all possible casts. The resolved function chain should be

date_add(castToDateTime('2020-09-16 17:30:00'), ...)

Alternative solution:

Raise a user-friendly error when an ambiguous cast possible. For example:

Please, specify data type to use in the function call, e.g. `date_add(datetime(<your input>), ... )`

What is your host/environment?

2.x @ 929ebfec

Do you have any additional context?

Rework required on changes done in #171