search

opensearch-project / sql

Query your data using familiar SQL or intuitive Piped Processing Language (PPL)
https://opensearch.org/docs/latest/search-plugins/sql/index/
Apache License 2.0
116 stars 134 forks source link

[BUG] SQL fails on executing some queries - OutOfBoundsException #278

Open Yury-Fridlyand opened 2 years ago

Yury-Fridlyand commented 2 years ago

Describe the bug Tableau Desktop lists all columns in a query when it reads all data from a table, instead of calling SELECT * .... For example, kibana service table has 91 columns, so query gets big amount of columns listed.

{
  "error": {
    "reason": "There was internal problem at backend",
    "details": "begin 1, end 0, length 1",
    "type": "StringIndexOutOfBoundsException"
  },
  "status": 503
}

To Reproduce The reason of failure is a bit unclear. This query fails (23 columns, 721 chars):

SELECT `.kibana`.`application_usage_daily`,
  `.kibana`.`application_usage_totals`,
  `.kibana`.`application_usage_transactional`,
  `.kibana`.`config`,
  `.kibana`.`dashboard`,
  `.kibana`.`dql-telemetry`,
  `.kibana`.`index-pattern`,
  `.kibana`.`migrationVersion`,
  `.kibana`.`namespace`,
  `.kibana`.`namespaces`,
  `.kibana`.`originId`,
  `.kibana`.`query`,
  `.kibana`.`references`,
  `.kibana`.`sample-data-telemetry`,
  `.kibana`.`search-telemetry`,
  `.kibana`.`search`,
  `.kibana`.`timelion-sheet`,
  `.kibana`.`tsvb-validation-telemetry`,
  `.kibana`.`type`,
  `.kibana`.`ui-metric`,
  `.kibana`.`updated_at`,
  `.kibana`.`url`,
  `.kibana`.`visualization`
FROM `.kibana`
LIMIT 10000;

This is ok: (12 columns, 403 chars)

SELECT `.kibana`.`application_usage_daily`,
  `.kibana`.`application_usage_totals`,
  `.kibana`.`application_usage_transactional`,
  `.kibana`.`config`,
  `.kibana`.`dashboard`,
  `.kibana`.`dql-telemetry`,
  `.kibana`.`index-pattern`,
  `.kibana`.`migrationVersion`,
  `.kibana`.`namespace`,
  `.kibana`.`namespaces`,
  `.kibana`.`originId`,
  `.kibana`.`query`
FROM `.kibana`
LIMIT 10000;

But if you will add one more column from the table it would fail. This one is also ok:

SELECT 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50

This is ok (1 col, 1055 chars):

SELECT COUNT(*) as very_long_alias_blah_blah_blah________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________
FROM `.kibana`
LIMIT 10000;

This one fails with the message I listed above, but actually it has a syntax error:

SELECT * as A
FROM `.kibana`
LIMIT 10000;

Expected behavior SQL plugin shouldn't fail on parsing/processing such queries if they don't have syntax errors.

Yury-Fridlyand commented 2 years ago

Part of the server log taken on failure:

[2021-11-09T20:50:01,289][ERROR][o.o.s.l.p.RestSqlAction  ] [opensearch-node1] 4c406b45-c75c-484d-9184-7ecce3819ac9 Server side error during query execution
java.lang.StringIndexOutOfBoundsException: begin 1, end 0, length 1
     at java.lang.String.checkBoundsBeginEnd(String.java:3734) ~[?:?]
     at java.lang.String.substring(String.java:1903) ~[?:?]
     at org.opensearch.sql.legacy.utils.StringUtils.unquoteSingleField(StringUtils.java:104) ~[legacy-1.1.0.0.jar:?]
     at org.opensearch.sql.legacy.utils.StringUtils.unquoteFullColumn(StringUtils.java:121) ~[legacy-1.1.0.0.jar:?]
     at org.opensearch.sql.legacy.utils.StringUtils.unquoteFullColumn(StringUtils.java:128) ~[legacy-1.1.0.0.jar:?]
     at org.opensearch.sql.legacy.rewriter.identifier.UnquoteIdentifierRule.endVisit(UnquoteIdentifierRule.java:71) ~[legacy-1.1.0.0.jar:?]
     at com.alibaba.druid.sql.ast.expr.SQLIdentifierExpr.accept0(SQLIdentifierExpr.java:67) ~[druid-1.0.15.jar:1.0.15]
     at com.alibaba.druid.sql.ast.SQLObjectImpl.accept(SQLObjectImpl.java:40) ~[druid-1.0.15.jar:1.0.15]
     at com.alibaba.druid.sql.ast.SQLObjectImpl.acceptChild(SQLObjectImpl.java:62) ~[druid-1.0.15.jar:1.0.15]
     at com.alibaba.druid.sql.ast.statement.SQLExprTableSource.accept0(SQLExprTableSource.java:52) ~[druid-1.0.15.jar:1.0.15]
     at com.alibaba.druid.sql.ast.SQLObjectImpl.accept(SQLObjectImpl.java:40) ~[druid-1.0.15.jar:1.0.15]
     at com.alibaba.druid.sql.ast.SQLObjectImpl.acceptChild(SQLObjectImpl.java:62) ~[druid-1.0.15.jar:1.0.15]
     at com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock.accept0(MySqlSelectQueryBlock.java:257) ~[druid-1.0.15.jar:1.0.15]
     at com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock.accept0(MySqlSelectQueryBlock.java:246) ~[druid-1.0.15.jar:1.0.15]
     at com.alibaba.druid.sql.ast.SQLObjectImpl.accept(SQLObjectImpl.java:40) ~[druid-1.0.15.jar:1.0.15]
     at com.alibaba.druid.sql.ast.SQLObjectImpl.acceptChild(SQLObjectImpl.java:62) ~[druid-1.0.15.jar:1.0.15]
     at com.alibaba.druid.sql.ast.statement.SQLSelect.accept0(SQLSelect.java:85) ~[druid-1.0.15.jar:1.0.15]
     at com.alibaba.druid.sql.ast.SQLObjectImpl.accept(SQLObjectImpl.java:40) ~[druid-1.0.15.jar:1.0.15]
     at com.alibaba.druid.sql.ast.SQLObjectImpl.acceptChild(SQLObjectImpl.java:62) ~[druid-1.0.15.jar:1.0.15]
     at com.alibaba.druid.sql.ast.expr.SQLQueryExpr.accept0(SQLQueryExpr.java:55) ~[druid-1.0.15.jar:1.0.15]
     at com.alibaba.druid.sql.ast.SQLObjectImpl.accept(SQLObjectImpl.java:40) ~[druid-1.0.15.jar:1.0.15]
     at org.opensearch.sql.legacy.rewriter.identifier.UnquoteIdentifierRule.rewrite(UnquoteIdentifierRule.java:86) ~[legacy-1.1.0.0.jar:?]
     at org.opensearch.sql.legacy.rewriter.RewriteRuleExecutor.executeOn(RewriteRuleExecutor.java:50) ~[legacy-1.1.0.0.jar:?]
     at org.opensearch.sql.legacy.query.OpenSearchActionFactory.create(OpenSearchActionFactory.java:120) ~[legacy-1.1.0.0.jar:?]
     at org.opensearch.sql.legacy.plugin.SearchDao.explain(SearchDao.java:72) ~[legacy-1.1.0.0.jar:?]
     at org.opensearch.sql.legacy.plugin.RestSqlAction.explainRequest(RestSqlAction.java:218) [legacy-1.1.0.0.jar:?]
     at org.opensearch.sql.legacy.plugin.RestSqlAction.prepareRequest(RestSqlAction.java:180) [legacy-1.1.0.0.jar:?]
     at org.opensearch.rest.BaseRestHandler.handleRequest(BaseRestHandler.java:102) [opensearch-1.1.0.jar:1.1.0]
     at org.opensearch.security.filter.SecurityRestFilter$1.handleRequest(SecurityRestFilter.java:126) [opensearch-security-1.1.0.0.jar:1.1.0.0]
     at org.opensearch.rest.RestController.dispatchRequest(RestController.java:271) [opensearch-1.1.0.jar:1.1.0]
     at org.opensearch.rest.RestController.tryAllHandlers(RestController.java:353) [opensearch-1.1.0.jar:1.1.0]
     at org.opensearch.rest.RestController.dispatchRequest(RestController.java:204) [opensearch-1.1.0.jar:1.1.0]
     at org.opensearch.security.ssl.http.netty.ValidatingDispatcher.dispatchRequest(ValidatingDispatcher.java:63) [opensearch-security-1.1.0.0.jar:1.1.0.0]
     at org.opensearch.http.AbstractHttpServerTransport.dispatchRequest(AbstractHttpServerTransport.java:332) [opensearch-1.1.0.jar:1.1.0]
     at org.opensearch.http.AbstractHttpServerTransport.handleIncomingRequest(AbstractHttpServerTransport.java:397) [opensearch-1.1.0.jar:1.1.0]
     at org.opensearch.http.AbstractHttpServerTransport.incomingRequest(AbstractHttpServerTransport.java:322) [opensearch-1.1.0.jar:1.1.0]
     at org.opensearch.http.netty4.Netty4HttpRequestHandler.channelRead0(Netty4HttpRequestHandler.java:55) [transport-netty4-client-1.1.0.jar:1.1.0]
     at org.opensearch.http.netty4.Netty4HttpRequestHandler.channelRead0(Netty4HttpRequestHandler.java:41) [transport-netty4-client-1.1.0.jar:1.1.0]
     at io.netty.channel.SimpleChannelInboundHandler.channelRead(SimpleChannelInboundHandler.java:99) [netty-transport-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379) [netty-transport-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365) [netty-transport-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357) [netty-transport-4.1.59.Final.jar:4.1.59.Final]
     at org.opensearch.http.netty4.Netty4HttpPipeliningHandler.channelRead(Netty4HttpPipeliningHandler.java:71) [transport-netty4-client-1.1.0.jar:1.1.0]
     at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379) [netty-transport-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365) [netty-transport-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357) [netty-transport-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:103) [netty-codec-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379) [netty-transport-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365) [netty-transport-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357) [netty-transport-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:103) [netty-codec-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379) [netty-transport-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365) [netty-transport-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357) [netty-transport-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:103) [netty-codec-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379) [netty-transport-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365) [netty-transport-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357) [netty-transport-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:324) [netty-codec-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:296) [netty-codec-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379) [netty-transport-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365) [netty-transport-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357) [netty-transport-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.handler.timeout.IdleStateHandler.channelRead(IdleStateHandler.java:286) [netty-handler-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379) [netty-transport-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365) [netty-transport-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357) [netty-transport-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:103) [netty-codec-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379) [netty-transport-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365) [netty-transport-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357) [netty-transport-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.handler.ssl.SslHandler.unwrap(SslHandler.java:1533) [netty-handler-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.handler.ssl.SslHandler.decodeJdkCompatible(SslHandler.java:1282) [netty-handler-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.handler.ssl.SslHandler.decode(SslHandler.java:1329) [netty-handler-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.handler.codec.ByteToMessageDecoder.decodeRemovalReentryProtection(ByteToMessageDecoder.java:508) [netty-codec-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.handler.codec.ByteToMessageDecoder.callDecode(ByteToMessageDecoder.java:447) [netty-codec-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:276) [netty-codec-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379) [netty-transport-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365) [netty-transport-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357) [netty-transport-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1410) [netty-transport-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379) [netty-transport-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365) [netty-transport-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:919) [netty-transport-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.channel.nio.AbstractNioByteChannel$NioByteUnsafe.read(AbstractNioByteChannel.java:166) [netty-transport-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.channel.nio.NioEventLoop.processSelectedKey(NioEventLoop.java:719) [netty-transport-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.channel.nio.NioEventLoop.processSelectedKeysPlain(NioEventLoop.java:620) [netty-transport-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.channel.nio.NioEventLoop.processSelectedKeys(NioEventLoop.java:583) [netty-transport-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:493) [netty-transport-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.util.concurrent.SingleThreadEventExecutor$4.run(SingleThreadEventExecutor.java:989) [netty-common-4.1.59.Final.jar:4.1.59.Final]
     at io.netty.util.internal.ThreadExecutorMap$2.run(ThreadExecutorMap.java:74) [netty-common-4.1.59.Final.jar:4.1.59.Final]
     at java.lang.Thread.run(Thread.java:832) [?:?]
Yury-Fridlyand commented 2 years ago

The bug title was updated: I found few simple queries which produce the same error:

SELECT LEFT(`calcs`.`str0`, -2) AS t FROM `calcs`;
SELECT LEFT(`calcs`.`str0`, CAST(`calcs`.`num4` as int)) AS t FROM `calcs`;
SELECT LEFT(`calcs`.`str2`, `calcs`.`int0`) AS t FROM `calcs`;
SELECT RIGHT(`calcs`.`str0`, CAST(`calcs`.`num4` as int)) AS t FROM `calcs`;
SELECT RIGHT(`calcs`.`str2`, `calcs`.`int0`) AS t FROM `calcs`;

str0 and str2 are string columns, num4 contains doubles and int0 has ints

Yury-Fridlyand commented 2 years ago

Might be related to https://github.com/opensearch-project/sql/issues/266

acarbonetto commented 1 year ago

The bug title was updated: I found few simple queries which produce the same error:

According to mysql, anything less than zero should return an empty string. LEFT(x, null) will return null.
This is unrelated to the original problem.