opensearch-project / opensearch-project/sql

[BUG] SQL Plugin Unexpected Failures using Functions

Open
#3,247 2 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

bug error-experience SQL v3.0.0
Dominant language
Java
Stars
176
Forks
229
Avg merge
2d 21h
Merged PRs (30d)
43

Description

I ran into the following 2 issues running OS 2.15 on AWS, as well as OS 2.17 locally.

Problem 1

When specifying format=json, functions do not work. Removing format=json fixes issue.

Request:

POST /_plugins/_sql?format=json
{
    "query": "SELECT * FROM my_index WHERE DATE < current_date()"
}

Response:

{
  "error": {
    "reason": "There was internal problem at backend",
    "details": "Failed to explain query action",
    "type": "IllegalStateException"
  },
  "status": 500
}

Trace:

[2025-01-15T17:06:35,510][INFO ][o.o.s.l.p.RestSqlAction  ] [] Request Query: ( SELECT * FROM table WHERE identifier < current_date() )
[2025-01-15T17:06:35,513][ERROR][o.o.s.l.p.RestSqlAction  ] [] 045d63ed-c0c7-4274-8d6b-61810b9bf233 Server side error during query execution
java.lang.IllegalStateException: Failed to explain query action
        at org.opensearch.sql.legacy.executor.ElasticDefaultRestExecutor.<init>(ElasticDefaultRestExecutor.java:49) ~[legacy-2.17.1.0.jar:?]
        at org.opensearch.sql.legacy.executor.ActionRequestRestExecutorFactory.createExecutor(ActionRequestRestExecutorFactory.java:29) ~[legacy-2.17.1.0.jar:?]
        at org.opensearch.sql.legacy.plugin.RestSqlAction.executeSqlRequest(RestSqlAction.java:268) [legacy-2.17.1.0.jar:?]
        at org.opensearch.sql.legacy.plugin.RestSqlAction.lambda$prepareRequest$1(RestSqlAction.java:173) [legacy-2.17.1.0.jar:?]
        at org.opensearch.sql.legacy.plugin.RestSQLQueryAction.lambda$prepareRequest$0(RestSQLQueryAction.java:89) [legacy-2.17.1.0.jar:?]
        at org.opensearch.rest.BaseRestHandler.handleRequest(BaseRestHandler.java:127) [opensearch-2.17.1.jar:2.17.1]
        at org.opensearch.rest.RestController.dispatchRequest(RestController.java:389) [opensearch-2.17.1.jar:2.17.1]
        at org.opensearch.rest.RestController.tryAllHandlers(RestController.java:480) [opensearch-2.17.1.jar:2.17.1]
        at org.opensearch.rest.RestController.dispatchRequest(RestController.java:295) [opensearch-2.17.1.jar:2.17.1]
        at org.opensearch.http.AbstractHttpServerTransport.dispatchRequest(AbstractHttpServerTransport.java:397) [opensearch-2.17.1.jar:2.17.1]
        at org.opensearch.http.AbstractHttpServerTransport.handleIncomingRequest(AbstractHttpServerTransport.java:505) [opensearch-2.17.1.jar:2.17.1]
        at org.opensearch.http.AbstractHttpServerTransport.incomingRequest(AbstractHttpServerTransport.java:380) [opensearch-2.17.1.jar:2.17.1]
        at org.opensearch.http.netty4.Netty4HttpRequestHandler.channelRead0(Netty4HttpRequestHandler.java:56) [transport-netty4-client-2.17.1.jar:2.17.1]
        at org.opensearch.http.netty4.Netty4HttpRequestHandler.channelRead0(Netty4HttpRequestHandler.java:42) [transport-netty4-client-2.17.1.jar:2.17.1]
        at io.netty.channel.SimpleChannelInboundHandler.channelRead(SimpleChannelInboundHandler.java:99) [netty-transport-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:444) [netty-transport-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420) [netty-transport-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412) [netty-transport-4.1.112.Final.jar:4.1.112.Final]
        at org.opensearch.http.netty4.Netty4HttpPipeliningHandler.channelRead(Netty4HttpPipeliningHandler.java:72) [transport-netty4-client-2.17.1.jar:2.17.1]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:442) [netty-transport-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420) [netty-transport-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412) [netty-transport-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:103) [netty-codec-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:444) [netty-transport-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420) [netty-transport-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412) [netty-transport-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:103) [netty-codec-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.handler.codec.MessageToMessageCodec.channelRead(MessageToMessageCodec.java:111) [netty-codec-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:442) [netty-transport-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420) [netty-transport-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412) [netty-transport-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:103) [netty-codec-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:444) [netty-transport-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420) [netty-transport-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412) [netty-transport-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:103) [netty-codec-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:444) [netty-transport-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420) [netty-transport-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412) [netty-transport-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:346) [netty-codec-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:318) [netty-codec-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:444) [netty-transport-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420) [netty-transport-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412) [netty-transport-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.handler.timeout.IdleStateHandler.channelRead(IdleStateHandler.java:289) [netty-handler-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:442) [netty-transport-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420) [netty-transport-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412) [netty-transport-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:103) [netty-codec-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:444) [netty-transport-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420) [netty-transport-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412) [netty-transport-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1407) [netty-transport-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:440) [netty-transport-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420) [netty-transport-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:918) [netty-transport-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.channel.nio.AbstractNioByteChannel$NioByteUnsafe.read(AbstractNioByteChannel.java:166) [netty-transport-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.channel.nio.NioEventLoop.processSelectedKey(NioEventLoop.java:788) [netty-transport-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.channel.nio.NioEventLoop.processSelectedKeysPlain(NioEventLoop.java:689) [netty-transport-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.channel.nio.NioEventLoop.processSelectedKeys(NioEventLoop.java:652) [netty-transport-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:562) [netty-transport-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.util.concurrent.SingleThreadEventExecutor$4.run(SingleThreadEventExecutor.java:994) [netty-common-4.1.112.Final.jar:4.1.112.Final]
        at io.netty.util.internal.ThreadExecutorMap$2.run(ThreadExecutorMap.java:74) [netty-common-4.1.112.Final.jar:4.1.112.Final]
        at java.base/java.lang.Thread.run(Thread.java:1583) [?:?]
Caused by: org.opensearch.sql.legacy.exception.SqlParseException: The following query method is not supported: current_date
        at org.opensearch.sql.legacy.query.maker.Maker.make(Maker.java:187) ~[legacy-2.17.1.0.jar:?]
        at org.opensearch.sql.legacy.query.maker.Maker.make(Maker.java:118) ~[legacy-2.17.1.0.jar:?]
        at org.opensearch.sql.legacy.query.maker.QueryMaker.explanWhere(QueryMaker.java:46) ~[legacy-2.17.1.0.jar:?]
        at org.opensearch.sql.legacy.query.maker.QueryMaker.explain(QueryMaker.java:33) ~[legacy-2.17.1.0.jar:?]
        at org.opensearch.sql.legacy.query.DefaultQueryAction.setWhere(DefaultQueryAction.java:205) ~[legacy-2.17.1.0.jar:?]
        at org.opensearch.sql.legacy.query.DefaultQueryAction.buildRequest(DefaultQueryAction.java:81) ~[legacy-2.17.1.0.jar:?]
        at org.opensearch.sql.legacy.query.DefaultQueryAction.explain(DefaultQueryAction.java:72) ~[legacy-2.17.1.0.jar:?]
        at org.opensearch.sql.legacy.query.DefaultQueryAction.explain(DefaultQueryAction.java:53) ~[legacy-2.17.1.0.jar:?]
        at org.opensearch.sql.legacy.executor.ElasticDefaultRestExecutor.<init>(ElasticDefaultRestExecutor.java:47) ~[legacy-2.17.1.0.jar:?]
        ... 63 more
Problem 2

Adding filter in JSON request breaks function use. Removing the filter fixes the issue. Note how I'm not using format=json here.

Request:

POST /_plugins/_sql
{
    "query": "SELECT * FROM my_index WHERE DATE < current_date()",
    "filter": {
        "term": {
            "TENANTID": "tenant1"
        }
    }
}

Response:

{
    "error": {
        "reason": "There was internal problem at backend",
        "details": "The following query method is not supported: current_date",
        "type": "SqlParseException"
    },
    "status": 500
}

Trace:

[2025-01-15T17:01:29,280][INFO ][o.o.s.l.p.RestSqlAction  ] [] Request Query: ( SELECT * FROM table WHERE identifier < current_date() )
[2025-01-15T17:01:29,290][WARN ][o.o.s.l.e.f.PrettyFormatRestExecutor] [] Error happened in pretty formatter
org.opensearch.sql.legacy.exception.SqlParseException: The following query method is not supported: current_date
        at org.opensearch.sql.legacy.query.maker.Maker.make(Maker.java:187) ~[legacy-2.17.1.0.jar:?]
        at org.opensearch.sql.legacy.query.maker.Maker.make(Maker.java:118) ~[legacy-2.17.1.0.jar:?]
        at org.opensearch.sql.legacy.query.maker.QueryMaker.explanWhere(QueryMaker.java:46) ~[legacy-2.17.1.0.jar:?]
        at org.opensearch.sql.legacy.query.maker.QueryMaker.explain(QueryMaker.java:33) ~[legacy-2.17.1.0.jar:?]
        at org.opensearch.sql.legacy.query.DefaultQueryAction.setWhere(DefaultQueryAction.java:205) ~[legacy-2.17.1.0.jar:?]
        at org.opensearch.sql.legacy.query.DefaultQueryAction.buildRequest(DefaultQueryAction.java:81) ~[legacy-2.17.1.0.jar:?]
        at org.opensearch.sql.legacy.query.DefaultQueryAction.explain(DefaultQueryAction.java:72) ~[legacy-2.17.1.0.jar:?]
        at org.opensearch.sql.legacy.executor.format.PrettyFormatRestExecutor.buildProtocolForDefaultQuery(PrettyFormatRestExecutor.java:93) ~[legacy-2.17.1.0.jar:?]
        at org.opensearch.sql.legacy.executor.format.PrettyFormatRestExecutor.execute(PrettyFormatRestExecutor.java:65) [legacy-2.17.1.0.jar:?]
        at org.opensearch.sql.legacy.executor.format.PrettyFormatRestExecutor.execute(PrettyFormatRestExecutor.java:41) [legacy-2.17.1.0.jar:?]
        at org.opensearch.sql.legacy.executor.AsyncRestExecutor.doExecuteWithTimeMeasured(AsyncRestExecutor.java:154) [legacy-2.17.1.0.jar:?]
        at org.opensearch.sql.legacy.executor.AsyncRestExecutor.lambda$async$1(AsyncRestExecutor.java:110) [legacy-2.17.1.0.jar:?]
        at org.opensearch.sql.common.utils.QueryContext.lambda$withCurrentContext$0(QueryContext.java:61) [common-2.17.1.0.jar:?]
        at org.opensearch.common.util.concurrent.ThreadContext$ContextPreservingRunnable.run(ThreadContext.java:946) [opensearch-2.17.1.jar:2.17.1]
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144) [?:?]
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642) [?:?]
        at java.base/java.lang.Thread.run(Thread.java:1583) [?:?]
Setup

Index

PUT /my_index
{
    "mappings": {
        "properties": {
            "TITLE": {
                "type": "text"
            },
            "TENANTID": {
                "type": "keyword"
            },
            "DATE": {
                "type": "date"
            }
        }
    }
}

Documents

POST /_bulk
{"index": {"_index": "my_index", "_id": "1"}}
{ "TITLE": "title1", "TENANTID": "tenant1", "DATE" : "2025-08-14T15:45:55.476Z"}
{"index": {"_index": "my_index", "_id": "2"}}
{ "TITLE": "title2", "TENANTID": "tenant2", "DATE" : "2024-08-14T15:45:55.476Z"}
{"index": {"_index": "my_index", "_id": "3"}}
{ "TITLE": "title3", "TENANTID": "tenant3", "DATE" : "2023-08-14T15:45:55.476Z"}

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Reproduce both requests against the SQL plugin, then trace the format and filter paths from RestSqlAction into QueryMaker and Maker, where the report shows current_date is rejected. Compare the failing function-handling paths with requests that omit format=json or filter. Done means functions in both reported request forms no longer fail with an internal 500.

Written by the indexing model from the issue text.

Assessment

Tech stack
java, sql
Domain
backend, databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.