sql
sql copied to clipboard
opensearch-project
[BUG] simple joins on keywords don't seem to work
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
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 I'm happy to work this issue if you want to assign it to me. It will benefit our OpenSearch UBI work.
