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] simple joins on keywords don't seem to work #2550

Open
RasonJ opened this issue Mar 12, 2024 · 3 comments · May be fixed by #2948
Open

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

RasonJ opened this issue Mar 12, 2024 · 3 comments · May be fixed by #2948
Labels
bug Something isn't working

Comments

@RasonJ
Copy link

RasonJ commented Mar 12, 2024

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
@RasonJ RasonJ added bug Something isn't working untriaged labels Mar 12, 2024
@RasonJ RasonJ mentioned this issue Mar 12, 2024
Closed
@penghuo
Copy link
Collaborator

penghuo commented Mar 28, 2024

this is a bug when handing string with ., https://github.com/opensearch-project/sql/blob/main/legacy/src/main/java/org/opensearch/sql/legacy/utils/StringUtils.java#L93

to migrate the issue, is it possible to remove . from index name

@penghuo penghuo removed the untriaged label Mar 28, 2024
jzonthemtn added a commit to jzonthemtn/sql that referenced this issue Jun 3, 2024
@jzonthemtn
opensearch-project#2550 Not removing leading periods when quoting/unq…
f544d67 
…uoting.

Signed-off-by: jzonthemtn <[email protected]>
@jzonthemtn
Copy link

@penghuo I'm happy to work this issue if you want to assign it to me. It will benefit our OpenSearch UBI work.

@epugh
Copy link

epugh commented Aug 14, 2024

I suspect that you don't need it assigned to you to submit a PR ;-)

jzonthemtn added a commit to jzonthemtn/sql that referenced this issue Aug 28, 2024
@jzonthemtn
opensearch-project#2550 Allowing joins when index names start with a …
efff79e 
…period.

Signed-off-by: Jeff <[email protected]>
@jzonthemtn jzonthemtn linked a pull request Aug 28, 2024 that will close this issue
Open
7 tasks
@Swiddis Swiddis mentioned this issue Dec 23, 2024
Open
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
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

#2550 Allowing joins when index names start with a period jzonthemtn/sql
4 participants
@epugh @penghuo @jzonthemtn @RasonJ