Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

[BUG] SQL Plugin Unexpected Failures using Functions #3247

Open
imbarazz opened this issue Jan 15, 2025 · 0 comments
Open

[BUG] SQL Plugin Unexpected Failures using Functions #3247

imbarazz opened this issue Jan 15, 2025 · 0 comments
Labels
bug Something isn't working untriaged

Comments

@imbarazz
Copy link

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"}
@imbarazz imbarazz added bug Something isn't working untriaged labels Jan 15, 2025
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug Something isn't working untriaged
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@imbarazz