opensearch-project / opensearch-project/sql

[BUG] simple joins on keywords don't seem to work

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

Nobody has claimed this yet.

bug SQL
Dominant language
Java
Stars
176
Forks
229
Avg merge
2d 21h
Merged PRs (30d)
43

Description

What is the bug?
Joins on keyword fields do not seem to work.

From the Query Workbench:

select
   q.query_id
from 
   .ubi_log_queries q
join .ubi_log_events e
    on q.query_id = e.query_id

Yields this output:
: Bad Request, this query is not runnable.

Both indices have data and the following mapping:

        "query_id" : {
          "type" : "keyword"
        },

The OpenSearch logs have:

[2024-03-12T13:43:44,456][INFO ][o.o.s.l.p.RestSqlAction  ] [opensearch] [a77b58fa-bf13-4e8d-b9e7-c517f3793a96] Incoming request /_plugins/_sql
[2024-03-12T13:43:44,459][WARN ][stderr                   ] [opensearch] line 1:49 mismatched input 'join' expecting {<EOF>, ';'}
[2024-03-12T13:43:44,459][INFO ][o.o.s.l.p.RestSqlAction  ] [opensearch] [a77b58fa-bf13-4e8d-b9e7-c517f3793a96] Request SQLQueryRequest(jsonContent={"query":"select    q.query_id from     .ubi_log_queries q join .ubi_log_events e     on q.query_id = e.query_id"}, query=select    q.query_id from     .ubi_log_queries q join .ubi_log_events e     on q.query_id = e.query_id, path=/_plugins/_sql, format=jdbc, params={}, sanitize=true, cursor=Optional.empty) is not supported and falling back to old SQL engine
[2024-03-12T13:43:44,470][WARN ][o.o.s.l.u.QueryDataAnonymizer] [opensearch] Caught an exception when anonymizing sensitive data.
[2024-03-12T13:43:44,470][INFO ][o.o.s.l.p.RestSqlAction  ] [opensearch] Request Query: Failed to anonymize data.
[2024-03-12T13:43:44,470][ERROR][o.o.s.l.p.RestSqlAction  ] [opensearch] a77b58fa-bf13-4e8d-b9e7-c517f3793a96 Client side error during query execution
com.alibaba.druid.sql.parser.ParserException: Illegal SQL expression : select    q.query_id from     .ubi_log_queries q join .ubi_log_events e     on q.query_id = e.query_id
        at org.opensearch.sql.legacy.utils.Util.toSqlExpr(Util.java:279) ~[legacy-2.12.0.0.jar:?]
        at org.opensearch.sql.legacy.query.OpenSearchActionFactory.create(OpenSearchActionFactory.java:89) ~[legacy-2.12.0.0.jar:?]
        at org.opensearch.sql.legacy.plugin.SearchDao.explain(SearchDao.java:48) ~[legacy-2.12.0.0.jar:?]
        at org.opensearch.sql.legacy.plugin.RestSqlAction.explainRequest(RestSqlAction.java:243) [legacy-2.12.0.0.jar:?]
        at org.opensearch.sql.legacy.plugin.RestSqlAction.lambda$prepareRequest$1(RestSqlAction.java:172) [legacy-2.12.0.0.jar:?]
        at org.opensearch.sql.legacy.plugin.RestSQLQueryAction$1.onFailure(RestSQLQueryAction.java:130) [legacy-2.12.0.0.jar:?]
        at org.opensearch.sql.sql.SQLService.execute(SQLService.java:43) [sql-2.12.0.0.jar:?]
        at org.opensearch.sql.legacy.plugin.RestSQLQueryAction.lambda$prepareRequest$3(RestSQLQueryAction.java:107) [legacy-2.12.0.0.jar:?]
        at org.opensearch.rest.BaseRestHandler.handleRequest(BaseRestHandler.java:128) [opensearch-2.12.0.jar:2.12.0]
        at org.opensearch.rest.RestController.dispatchRequest(RestController.java:334) [opensearch-2.12.0.jar:2.12.0]
        at org.opensearch.rest.RestController.tryAllHandlers(RestController.java:425) [opensearch-2.12.0.jar:2.12.0]
        at org.opensearch.rest.RestController.dispatchRequest(RestController.java:263) [opensearch-2.12.0.jar:2.12.0]
        at org.opensearch.http.AbstractHttpServerTransport.dispatchRequest(AbstractHttpServerTransport.java:387) [opensearch-2.12.0.jar:2.12.0]
        at org.opensearch.http.AbstractHttpServerTransport.handleIncomingRequest(AbstractHttpServerTransport.java:468) [opensearch-2.12.0.jar:2.12.0]
        at org.opensearch.http.AbstractHttpServerTransport.incomingRequest(AbstractHttpServerTransport.java:370) [opensearch-2.12.0.jar:2.12.0]
        at org.opensearch.http.netty4.Netty4HttpRequestHandler.channelRead0(Netty4HttpRequestHandler.java:56) [transport-netty4-client-2.12.0.jar:2.12.0]
        at org.opensearch.http.netty4.Netty4HttpRequestHandler.channelRead0(Netty4HttpRequestHandler.java:42) [transport-netty4-client-2.12.0.jar:2.12.0]
        at io.netty.channel.SimpleChannelInboundHandler.channelRead(SimpleChannelInboundHandler.java:99) [netty-transport-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:444) [netty-transport-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420) [netty-transport-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412) [netty-transport-4.1.106.Final.jar:4.1.106.Final]
        at org.opensearch.http.netty4.Netty4HttpPipeliningHandler.channelRead(Netty4HttpPipeliningHandler.java:72) [transport-netty4-client-2.12.0.jar:2.12.0]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:442) [netty-transport-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420) [netty-transport-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412) [netty-transport-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:103) [netty-codec-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:444) [netty-transport-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420) [netty-transport-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412) [netty-transport-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:103) [netty-codec-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.handler.codec.MessageToMessageCodec.channelRead(MessageToMessageCodec.java:111) [netty-codec-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:442) [netty-transport-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420) [netty-transport-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412) [netty-transport-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:103) [netty-codec-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:444) [netty-transport-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420) [netty-transport-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412) [netty-transport-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:103) [netty-codec-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:444) [netty-transport-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420) [netty-transport-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412) [netty-transport-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:346) [netty-codec-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:318) [netty-codec-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:444) [netty-transport-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420) [netty-transport-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412) [netty-transport-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.handler.timeout.IdleStateHandler.channelRead(IdleStateHandler.java:286) [netty-handler-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:442) [netty-transport-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420) [netty-transport-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412) [netty-transport-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:103) [netty-codec-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:444) [netty-transport-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420) [netty-transport-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412) [netty-transport-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1410) [netty-transport-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:440) [netty-transport-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420) [netty-transport-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:919) [netty-transport-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.channel.nio.AbstractNioByteChannel$NioByteUnsafe.read(AbstractNioByteChannel.java:166) [netty-transport-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.channel.nio.NioEventLoop.processSelectedKey(NioEventLoop.java:788) [netty-transport-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.channel.nio.NioEventLoop.processSelectedKeysPlain(NioEventLoop.java:689) [netty-transport-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.channel.nio.NioEventLoop.processSelectedKeys(NioEventLoop.java:652) [netty-transport-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:562) [netty-transport-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.util.concurrent.SingleThreadEventExecutor$4.run(SingleThreadEventExecutor.java:997) [netty-common-4.1.106.Final.jar:4.1.106.Final]
        at io.netty.util.internal.ThreadExecutorMap$2.run(ThreadExecutorMap.java:74) [netty-common-4.1.106.Final.jar:4.1.106.Final]
        at java.base/java.lang.Thread.run(Thread.java:1583) [?:?]
[2024-03-12T13:47:17,192][INFO ][o.o.j.s.JobSweeper       ] [opensearch] Running full sweep

What is the expected behavior?
Rows of data should have been returned instead of an error.

What is your host/environment?

  • all versions are OS and plugins are 2.12

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

Start by reproducing the Query Workbench SQL join against the two keyword-mapped indices. Trace the request through RestSqlAction, SQLService, and the legacy parser path at Util.toSqlExpr and OpenSearchActionFactory. Done means the shown join is accepted and returns rows instead of the parser error; no specific test file is named in the issue.

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.