opensearch-project / opensearch-project/sql
[BUG] Fix automatic cast string to temporal on function call
Open
@Yury-Fridlyand is already working on this.
Since Dec 14, 2022.
bug
error-experience
SQL
- Dominant language
- Java
- Stars
- 176
- Forks
- 229
- Avg merge
- 2d 21h
- Merged PRs (30d)
- 43
Description
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
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Assessment
This issue has not been assessed yet.