Skip to content
New issue

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

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

Already on GitHub? # to your account

[BUG] ORDER BY meta fields, such as _id, regression in ODFE 1.13 and removed in OpenSearch 1.0 #432

Closed
dblock opened this issue Feb 15, 2022 · 4 comments
Assignees
Labels
bug Something isn't working v2.7.0

Comments

@dblock
Copy link
Member

dblock commented Feb 15, 2022

Support for meta fields is open as a feature request in opendistro-for-elasticsearch/sql#1038 and #44, but it's actually a regression in ODFE 1.13 - ODFE 1.11.0 had at least some support for it, e.g. ORDER BY _id.

Works in OpenDistro 1.11

GET /

{
    "name": "odfe-node1",
    "cluster_name": "odfe-cluster",
    "cluster_uuid": "8AxjWcmJTGuqUMbVKVakDg",
    "version": {
        "number": "7.9.1",
        "build_flavor": "oss",
        "build_type": "tar",
        "build_hash": "083627f112ba94dffc1232e8b42b73492789ef91",
        "build_date": "2020-09-01T21:22:21.964974Z",
        "build_snapshot": false,
        "lucene_version": "8.6.2",
        "minimum_wire_compatibility_version": "6.8.0",
        "minimum_index_compatibility_version": "6.0.0-beta1"
    },
    "tagline": "You Know, for Search"
}
POST /mydata/_doc/1

{ "x": "y" }
{
    "_index": "mydata",
    "_type": "_doc",
    "_id": "1",
    "_version": 1,
    "result": "created",
    "_shards": {
        "total": 2,
        "successful": 2,
        "failed": 0
    },
    "_seq_no": 0,
    "_primary_term": 1
}
POST /_opendistro/_sql

{ 
    "query": 
    "SELECT * FROM mydata ORDER BY _id" 
}
{
    "schema": [
        {
            "name": "x",
            "type": "text"
        }
    ],
    "total": 1,
    "datarows": [
        [
            "y"
        ]
    ],
    "size": 1,
    "status": 200
}

Note that SELECT _id doesn't return the metadata field.

{
    "schema": [],
    "total": 1,
    "datarows": [
        []
    ],
    "size": 1,
    "status": 200
}

Explain works.

POST /_opendistro/_sql/_explain

{ 
    "query": 
    "SELECT * FROM x ORDER BY _id" 
}
{
    "from": 0,
    "size": 200,
    "sort": [
        {
            "_id": {
                "order": "asc"
            }
        }
    ]
}

Regression in 1.13 and still Broken in OpenSearch 1.2.4

GET /

{
    "name": "opensearch-124-node1",
    "cluster_name": "opensearch-cluster",
    "cluster_uuid": "JwjURv7wQYSPAmUZ_9JuOQ",
    "version": {
        "distribution": "opensearch",
        "number": "1.2.4",
        "build_type": "tar",
        "build_hash": "e505b10357c03ae8d26d675172402f2f2144ef0f",
        "build_date": "2022-01-14T03:38:06.881862Z",
        "build_snapshot": false,
        "lucene_version": "8.10.1",
        "minimum_wire_compatibility_version": "6.8.0",
        "minimum_index_compatibility_version": "6.0.0-beta1"
    },
    "tagline": "The OpenSearch Project: https://opensearch.org/"
}
POST /_plugins/_sql

{ 
    "query": 
    "SELECT * FROM mydata ORDER BY _id" 
}
{
  "error": {
    "reason": "Invalid SQL query",
    "details": "can't resolve Symbol(namespace=FIELD_NAME, name=id) in type env",
    "type": "SemanticCheckException"
  },
  "status": 400
}
opensearch-124-node1   | [2022-02-15T19:46:29,496][INFO ][o.o.s.l.p.RestSqlAction  ] [opensearch-124-node1] [c94e8a95-448f-4248-a972-773a9f1b1da1] Incoming request /_plugins/_sql: ( SELECT * FROM table ORDER BY identifier )
opensearch-124-node1   | [2022-02-15T19:46:30,416][ERROR][o.o.s.l.p.RestSqlAction  ] [opensearch-124-node1] c94e8a95-448f-4248-a972-773a9f1b1da1 Client side error during query execution
opensearch-124-node1   | org.opensearch.sql.exception.SemanticCheckException: can't resolve Symbol(namespace=FIELD_NAME, name=id) in type env
opensearch-124-node1   | 	at org.opensearch.sql.analysis.TypeEnvironment.resolve(TypeEnvironment.java:75) ~[core-1.2.4.0.jar:?]
opensearch-124-node1   | 	at org.opensearch.sql.analysis.ExpressionAnalyzer.visitIdentifier(ExpressionAnalyzer.java:282) ~[core-1.2.4.0.jar:?]
opensearch-124-node1   | 	at org.opensearch.sql.analysis.ExpressionAnalyzer.visitQualifiedName(ExpressionAnalyzer.java:263) ~[core-1.2.4.0.jar:?]
opensearch-124-node1   | 	at org.opensearch.sql.analysis.ExpressionAnalyzer.visitQualifiedName(ExpressionAnalyzer.java:84) ~[core-1.2.4.0.jar:?]
opensearch-124-node1   | 	at org.opensearch.sql.ast.expression.QualifiedName.accept(QualifiedName.java:131) ~[core-1.2.4.0.jar:?]
opensearch-124-node1   | 	at org.opensearch.sql.analysis.ExpressionAnalyzer.analyze(ExpressionAnalyzer.java:103) ~[core-1.2.4.0.jar:?]
opensearch-124-node1   | 	at org.opensearch.sql.analysis.Analyzer.lambda$visitSort$8(Analyzer.java:340) ~[core-1.2.4.0.jar:?]
opensearch-124-node1   | 	at java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:195) ~[?:?]
opensearch-124-node1   | 	at java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1625) ~[?:?]
opensearch-124-node1   | 	at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:484) ~[?:?]
opensearch-124-node1   | 	at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:474) ~[?:?]
opensearch-124-node1   | 	at java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:913) ~[?:?]
opensearch-124-node1   | 	at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234) ~[?:?]
opensearch-124-node1   | 	at java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:578) ~[?:?]
opensearch-124-node1   | 	at org.opensearch.sql.analysis.Analyzer.visitSort(Analyzer.java:343) ~[core-1.2.4.0.jar:?]
opensearch-124-node1   | 	at org.opensearch.sql.analysis.Analyzer.visitSort(Analyzer.java:98) ~[core-1.2.4.0.jar:?]
opensearch-124-node1   | 	at org.opensearch.sql.ast.tree.Sort.accept(Sort.java:70) ~[core-1.2.4.0.jar:?]
opensearch-124-node1   | 	at org.opensearch.sql.analysis.Analyzer.visitProject(Analyzer.java:272) ~[core-1.2.4.0.jar:?]
opensearch-124-node1   | 	at org.opensearch.sql.analysis.Analyzer.visitProject(Analyzer.java:98) ~[core-1.2.4.0.jar:?]
opensearch-124-node1   | 	at org.opensearch.sql.ast.tree.Project.accept(Project.java:91) ~[core-1.2.4.0.jar:?]
opensearch-124-node1   | 	at org.opensearch.sql.analysis.Analyzer.analyze(Analyzer.java:121) ~[core-1.2.4.0.jar:?]
opensearch-124-node1   | 	at org.opensearch.sql.sql.SQLService.analyze(SQLService.java:120) ~[sql-1.2.4.0.jar:?]
opensearch-124-node1   | 	at org.opensearch.sql.legacy.plugin.RestSQLQueryAction.prepareRequest(RestSQLQueryAction.java:126) ~[legacy-1.2.4.0.jar:?]
opensearch-124-node1   | 	at org.opensearch.sql.legacy.plugin.RestSqlAction.prepareRequest(RestSqlAction.java:172) [legacy-1.2.4.0.jar:?]
opensearch-124-node1   | 	at org.opensearch.rest.BaseRestHandler.handleRequest(BaseRestHandler.java:105) [opensearch-1.2.4.jar:1.2.4]
opensearch-124-node1   | 	at org.opensearch.security.filter.SecurityRestFilter$1.handleRequest(SecurityRestFilter.java:126) [opensearch-security-1.2.4.0.jar:1.2.4.0]
opensearch-124-node1   | 	at org.opensearch.rest.RestController.dispatchRequest(RestController.java:306) [opensearch-1.2.4.jar:1.2.4]
opensearch-124-node1   | 	at org.opensearch.rest.RestController.tryAllHandlers(RestController.java:392) [opensearch-1.2.4.jar:1.2.4]
opensearch-124-node1   | 	at org.opensearch.rest.RestController.dispatchRequest(RestController.java:235) [opensearch-1.2.4.jar:1.2.4]
opensearch-124-node1   | 	at org.opensearch.security.ssl.http.netty.ValidatingDispatcher.dispatchRequest(ValidatingDispatcher.java:63) [opensearch-security-1.2.4.0.jar:1.2.4.0]
opensearch-124-node1   | 	at org.opensearch.http.AbstractHttpServerTransport.dispatchRequest(AbstractHttpServerTransport.java:361) [opensearch-1.2.4.jar:1.2.4]
opensearch-124-node1   | 	at org.opensearch.http.AbstractHttpServerTransport.handleIncomingRequest(AbstractHttpServerTransport.java:440) [opensearch-1.2.4.jar:1.2.4]
opensearch-124-node1   | 	at org.opensearch.http.AbstractHttpServerTransport.incomingRequest(AbstractHttpServerTransport.java:351) [opensearch-1.2.4.jar:1.2.4]
opensearch-124-node1   | 	at org.opensearch.http.netty4.Netty4HttpRequestHandler.channelRead0(Netty4HttpRequestHandler.java:55) [transport-netty4-client-1.2.4.jar:1.2.4]
opensearch-124-node1   | 	at org.opensearch.http.netty4.Netty4HttpRequestHandler.channelRead0(Netty4HttpRequestHandler.java:41) [transport-netty4-client-1.2.4.jar:1.2.4]
opensearch-124-node1   | 	at io.netty.channel.SimpleChannelInboundHandler.channelRead(SimpleChannelInboundHandler.java:99) [netty-transport-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379) [netty-transport-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365) [netty-transport-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357) [netty-transport-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at org.opensearch.http.netty4.Netty4HttpPipeliningHandler.channelRead(Netty4HttpPipeliningHandler.java:71) [transport-netty4-client-1.2.4.jar:1.2.4]
opensearch-124-node1   | 	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379) [netty-transport-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365) [netty-transport-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357) [netty-transport-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:103) [netty-codec-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379) [netty-transport-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365) [netty-transport-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357) [netty-transport-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:103) [netty-codec-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379) [netty-transport-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365) [netty-transport-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357) [netty-transport-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:103) [netty-codec-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379) [netty-transport-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365) [netty-transport-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357) [netty-transport-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:324) [netty-codec-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:296) [netty-codec-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379) [netty-transport-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365) [netty-transport-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357) [netty-transport-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.handler.timeout.IdleStateHandler.channelRead(IdleStateHandler.java:286) [netty-handler-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379) [netty-transport-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365) [netty-transport-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357) [netty-transport-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:103) [netty-codec-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379) [netty-transport-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365) [netty-transport-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357) [netty-transport-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.handler.ssl.SslHandler.unwrap(SslHandler.java:1371) [netty-handler-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.handler.ssl.SslHandler.decodeJdkCompatible(SslHandler.java:1234) [netty-handler-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.handler.ssl.SslHandler.decode(SslHandler.java:1283) [netty-handler-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.handler.codec.ByteToMessageDecoder.decodeRemovalReentryProtection(ByteToMessageDecoder.java:507) [netty-codec-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.handler.codec.ByteToMessageDecoder.callDecode(ByteToMessageDecoder.java:446) [netty-codec-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:276) [netty-codec-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379) [netty-transport-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365) [netty-transport-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357) [netty-transport-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1410) [netty-transport-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379) [netty-transport-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365) [netty-transport-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:919) [netty-transport-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.channel.nio.AbstractNioByteChannel$NioByteUnsafe.read(AbstractNioByteChannel.java:166) [netty-transport-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.channel.nio.NioEventLoop.processSelectedKey(NioEventLoop.java:722) [netty-transport-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.channel.nio.NioEventLoop.processSelectedKeysPlain(NioEventLoop.java:623) [netty-transport-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.channel.nio.NioEventLoop.processSelectedKeys(NioEventLoop.java:586) [netty-transport-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:496) [netty-transport-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.util.concurrent.SingleThreadEventExecutor$4.run(SingleThreadEventExecutor.java:986) [netty-common-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at io.netty.util.internal.ThreadExecutorMap$2.run(ThreadExecutorMap.java:74) [netty-common-4.1.72.Final.jar:4.1.72.Final]
opensearch-124-node1   | 	at java.lang.Thread.run(Thread.java:832) [?:?]

@dblock dblock added bug Something isn't working Beta untriaged and removed Beta labels Feb 15, 2022
@dblock dblock changed the title [BUG] ORDER BY meta fields, such as _id, is broken [BUG] ORDER BY meta fields, such as _id, broken since ODFE 1.13 Feb 15, 2022
@penghuo
Copy link
Collaborator

penghuo commented Feb 16, 2022

Why throw SemanticCheck in ODFE 1.13

  • SQL engine v2 execute SemanticCheck with index mapping (TypeEnvironment). "_id" is meta field which is not defined in index mapping, so SQL engine v2 throw SemanticCheck.

Why ODFE 1.11 works

  • ODFE 1.13, SQL Engine V2 is enabled by default.
  • ODFE 1.11, SQL Engine V2 is disabled by default. So all the request by default executed in V1 engine which support _id field.

Migration

@dblock
Copy link
Member Author

dblock commented Feb 16, 2022

@penghuo For OpenSearch, the doc is confusing. Was this setting removed from OpenSearch 1.0 or a more recent version?

In #70 we have removed this flag from OpenSearch 1.x, so the workaround will only work in OpenDistro. See https://github.com/opensearch-project/sql/blob/main/release-notes/opensearch-sql.release-notes-1.0.0.0.md for more changes.

@dblock dblock changed the title [BUG] ORDER BY meta fields, such as _id, broken since ODFE 1.13 [BUG] ORDER BY meta fields, such as _id, regression in ODFE 1.13 and removed in OpenSearch 1.0 Feb 16, 2022
@penghuo penghuo removed the untriaged label Feb 17, 2022
@Yury-Fridlyand Yury-Fridlyand self-assigned this Dec 14, 2022
@dai-chen
Copy link
Collaborator

Another issue related: #339

@acarbonetto
Copy link
Collaborator

@dai-chen this issue can be closed as part of #1456

# for free to join this conversation on GitHub. Already have an account? # to comment
Labels
bug Something isn't working v2.7.0
Projects
No open projects
Status: Done
Development

No branches or pull requests

5 participants