Skip to content
This repository was archived by the owner on Aug 2, 2022. It is now read-only.

ORDER BY meta fields, such as _id, is broken #1159

Open
Open
ORDER BY meta fields, such as _id, is broken#1159
@dblock

Description

@dblock
dblock
opened on Feb 15, 2022

Support for meta fields is open as a feature request in #1038, 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) [?:?]

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions