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

Group By Oder by * SQL ERROR #402

Closed
EveyWu opened this issue Mar 30, 2020 · 4 comments
Closed

Group By Oder by * SQL ERROR #402

EveyWu opened this issue Mar 30, 2020 · 4 comments
Labels
bug Something isn't working JDBC/ODBC formatting This issue is related to JDBC or ODBC driver client SQL

Comments

@EveyWu
Copy link

EveyWu commented Mar 30, 2020

SQL By bin/elasticsearch-sql-cli is correct

Demo SQL of ReadMe.txt

	String aggregate_sql = "SELECT COUNT(*), SUM(age), MIN(age) as m, MAX(age), AVG(age)" +
			"  FROM bank" +
			"  GROUP BY gender" +
			"  HAVING m >= 20" +
			"  ORDER BY SUM(age), m DESC";

ERROR

{
  "error" : {
    "reason" : "Error occurred in Elasticsearch engine: all shards failed",
    "details" : "Shard[0]: [bank/ngwB8uhERG-nAzDgIV7suA] QueryShardException[No mapping found for [SUM(age)] in order to sort on]\n\nFor more details, please send request for Json format to see the raw response from elasticsearch engine.",
    "type" : "SearchPhaseExecutionException"
  },
  "status" : 400
}

ES ERROR LOG

java.lang.RuntimeException: Failed to execute phase [query], all shards failed; shardFailures {[yo7D69ncSpm5FCsX6xyjUQ][bank][0]: RemoteTransportException[[YIWENWU-MB1][127.0.0.1:9300][indices:data/read/search[phase/query]]]; nested: QueryShardException[No mapping found for [SUM(age)] in order to sort on]; }
	at com.amazon.opendistroforelasticsearch.sql.query.planner.core.BindingTupleQueryPlanner.execute(BindingTupleQueryPlanner.java:55) ~[opendistro_sql-1.6.0.0.jar:1.6.0.0]
	at com.amazon.opendistroforelasticsearch.sql.executor.adapter.QueryPlanRequestBuilder.execute(QueryPlanRequestBuilder.java:37) ~[opendistro_sql-1.6.0.0.jar:1.6.0.0]
	at com.amazon.opendistroforelasticsearch.sql.executor.QueryActionElasticExecutor.executeQueryPlanQueryAction(QueryActionElasticExecutor.java:65) ~[opendistro_sql-1.6.0.0.jar:1.6.0.0]
	at com.amazon.opendistroforelasticsearch.sql.executor.QueryActionElasticExecutor.executeAnyAction(QueryActionElasticExecutor.java:109) ~[opendistro_sql-1.6.0.0.jar:1.6.0.0]
	at com.amazon.opendistroforelasticsearch.sql.executor.format.PrettyFormatRestExecutor.execute(PrettyFormatRestExecutor.java:70) [opendistro_sql-1.6.0.0.jar:1.6.0.0]
	at com.amazon.opendistroforelasticsearch.sql.executor.format.PrettyFormatRestExecutor.execute(PrettyFormatRestExecutor.java:47) [opendistro_sql-1.6.0.0.jar:1.6.0.0]
	at com.amazon.opendistroforelasticsearch.sql.executor.AsyncRestExecutor.doExecuteWithTimeMeasured(AsyncRestExecutor.java:161) [opendistro_sql-1.6.0.0.jar:1.6.0.0]
	at com.amazon.opendistroforelasticsearch.sql.executor.AsyncRestExecutor.lambda$async$1(AsyncRestExecutor.java:121) [opendistro_sql-1.6.0.0.jar:1.6.0.0]
	at com.amazon.opendistroforelasticsearch.sql.utils.LogUtils.lambda$withCurrentContext$0(LogUtils.java:72) [opendistro_sql-1.6.0.0.jar:1.6.0.0]
	at org.elasticsearch.common.util.concurrent.ThreadContext$ContextPreservingRunnable.run(ThreadContext.java:633) [elasticsearch-7.6.1.jar:7.6.1]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) [?:?]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) [?:?]
	at java.lang.Thread.run(Thread.java:830) [?:?]
Caused by: org.elasticsearch.action.search.SearchPhaseExecutionException: all shards failed
	at org.elasticsearch.action.search.AbstractSearchAsyncAction.onPhaseFailure(AbstractSearchAsyncAction.java:545) ~[elasticsearch-7.6.1.jar:7.6.1]
	at org.elasticsearch.action.search.AbstractSearchAsyncAction.executeNextPhase(AbstractSearchAsyncAction.java:306) ~[elasticsearch-7.6.1.jar:7.6.1]
	at org.elasticsearch.action.search.AbstractSearchAsyncAction.onPhaseDone(AbstractSearchAsyncAction.java:574) ~[elasticsearch-7.6.1.jar:7.6.1]
	at org.elasticsearch.action.search.AbstractSearchAsyncAction.onShardFailure(AbstractSearchAsyncAction.java:386) ~[elasticsearch-7.6.1.jar:7.6.1]
	at org.elasticsearch.action.search.AbstractSearchAsyncAction.access$200(AbstractSearchAsyncAction.java:66) ~[elasticsearch-7.6.1.jar:7.6.1]
	at org.elasticsearch.action.search.AbstractSearchAsyncAction$1.onFailure(AbstractSearchAsyncAction.java:242) ~[elasticsearch-7.6.1.jar:7.6.1]
	at org.elasticsearch.action.search.SearchExecutionStatsCollector.onFailure(SearchExecutionStatsCollector.java:73) ~[elasticsearch-7.6.1.jar:7.6.1]
	at org.elasticsearch.action.ActionListenerResponseHandler.handleException(ActionListenerResponseHandler.java:59) ~[elasticsearch-7.6.1.jar:7.6.1]
	at org.elasticsearch.action.search.SearchTransportService$ConnectionCountingHandler.handleException(SearchTransportService.java:423) ~[elasticsearch-7.6.1.jar:7.6.1]
	at org.elasticsearch.transport.TransportService$ContextRestoreResponseHandler.handleException(TransportService.java:1118) ~[elasticsearch-7.6.1.jar:7.6.1]
	at org.elasticsearch.transport.TransportService$DirectResponseChannel.processException(TransportService.java:1227) ~[elasticsearch-7.6.1.jar:7.6.1]
	at org.elasticsearch.transport.TransportService$DirectResponseChannel.sendResponse(TransportService.java:1201) ~[elasticsearch-7.6.1.jar:7.6.1]
	at org.elasticsearch.transport.TaskTransportChannel.sendResponse(TaskTransportChannel.java:60) ~[elasticsearch-7.6.1.jar:7.6.1]
	at org.elasticsearch.action.support.ChannelActionListener.onFailure(ChannelActionListener.java:56) ~[elasticsearch-7.6.1.jar:7.6.1]
	at org.elasticsearch.action.ActionListener$1.onFailure(ActionListener.java:71) ~[elasticsearch-7.6.1.jar:7.6.1]
	at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:65) ~[elasticsearch-7.6.1.jar:7.6.1]
	at org.elasticsearch.action.ActionRunnable.lambda$supply$0(ActionRunnable.java:58) ~[elasticsearch-7.6.1.jar:7.6.1]
	at org.elasticsearch.action.ActionRunnable$2.doRun(ActionRunnable.java:73) ~[elasticsearch-7.6.1.jar:7.6.1]
	at org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:37) ~[elasticsearch-7.6.1.jar:7.6.1]
	at org.elasticsearch.common.util.concurrent.TimedRunnable.doRun(TimedRunnable.java:44) ~[elasticsearch-7.6.1.jar:7.6.1]
	at org.elasticsearch.common.util.concurrent.ThreadContext$ContextPreservingAbstractRunnable.doRun(ThreadContext.java:692) ~[elasticsearch-7.6.1.jar:7.6.1]
	at org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:37) ~[elasticsearch-7.6.1.jar:7.6.1]
	... 3 more
Caused by: org.elasticsearch.index.query.QueryShardException: No mapping found for [SUM(age)] in order to sort on
	at org.elasticsearch.search.sort.FieldSortBuilder.build(FieldSortBuilder.java:403) ~[elasticsearch-7.6.1.jar:7.6.1]
	at org.elasticsearch.search.sort.SortBuilder.buildSort(SortBuilder.java:153) ~[elasticsearch-7.6.1.jar:7.6.1]
	at org.elasticsearch.search.SearchService.parseSource(SearchService.java:767) ~[elasticsearch-7.6.1.jar:7.6.1]
	at org.elasticsearch.search.SearchService.createContext(SearchService.java:591) ~[elasticsearch-7.6.1.jar:7.6.1]
	at org.elasticsearch.search.SearchService.createAndPutContext(SearchService.java:550) ~[elasticsearch-7.6.1.jar:7.6.1]
	at org.elasticsearch.search.SearchService.executeQueryPhase(SearchService.java:351) ~[elasticsearch-7.6.1.jar:7.6.1]
	at org.elasticsearch.search.SearchService.lambda$executeQueryPhase$1(SearchService.java:343) ~[elasticsearch-7.6.1.jar:7.6.1]
	at org.elasticsearch.action.ActionListener.lambda$map$2(ActionListener.java:146) ~[elasticsearch-7.6.1.jar:7.6.1]
	at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:63) ~[elasticsearch-7.6.1.jar:7.6.1]
	at org.elasticsearch.action.ActionRunnable.lambda$supply$0(ActionRunnable.java:58) ~[elasticsearch-7.6.1.jar:7.6.1]
	at org.elasticsearch.action.ActionRunnable$2.doRun(ActionRunnable.java:73) ~[elasticsearch-7.6.1.jar:7.6.1]
	at org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:37) ~[elasticsearch-7.6.1.jar:7.6.1]
	at org.elasticsearch.common.util.concurrent.TimedRunnable.doRun(TimedRunnable.java:44) ~[elasticsearch-7.6.1.jar:7.6.1]
	at org.elasticsearch.common.util.concurrent.ThreadContext$ContextPreservingAbstractRunnable.doRun(ThreadContext.java:692) ~[elasticsearch-7.6.1.jar:7.6.1]
	at org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:37) ~[elasticsearch-7.6.1.jar:7.6.1]
	... 3 more
@penghuo penghuo added bug Something isn't working and removed bug Something isn't working labels Mar 30, 2020
@dai-chen dai-chen added the bug Something isn't working label Mar 30, 2020
@dai-chen
Copy link
Member

dai-chen commented Mar 30, 2020 •
edited
Loading

Thanks for reporting! I just checked the explain output and the sort section in DSL generated looks wrong.

POST /_opendistro/_sql/_explain
{
  "query" : """
    SELECT COUNT(*), SUM(age), MIN(age) as m, MAX(age), AVG(age)
    FROM accounts
    GROUP BY gender
    HAVING m >= 20
    ORDER BY SUM(age), m DESC
	"""
}

{
  "from" : 0,
  "size" : 0,
  "_source" : {
    "includes" : [
      "COUNT",
      "SUM",
      "MIN",
      "MAX",
      "AVG"
    ],
    "excludes" : [ ]
  },
  "sort" : [
    {
      "SUM(age)" : {
        "order" : "asc"
      }
    }
  ],
  "aggregations" : {
    "gender.keyword" : {
      "terms" : {
        "field" : "gender.keyword",
        "size" : 200,
        "min_doc_count" : 1,
        "shard_min_doc_count" : 0,
        "show_term_doc_count_error" : false,
        "order" : [
          {
            "m" : "desc"
          },
          {
            "_key" : "asc"
          }
        ]
      },
      "aggregations" : {
        "COUNT_0" : {
          "value_count" : {
            "field" : "_index"
          }
        },
        "SUM_1" : {
          "sum" : {
            "field" : "age"
          }
        },
        "m" : {
          "min" : {
            "field" : "age"
          }
        },
        "MAX_3" : {
          "max" : {
            "field" : "age"
          }
        },
        "AVG_4" : {
          "avg" : {
            "field" : "age"
          }
        },
        "bucket_filter" : {
          "bucket_selector" : {
            "buckets_path" : {
              "COUNT_0" : "COUNT_0",
              "MAX_3" : "MAX_3",
              "SUM_1" : "SUM_1",
              "AVG_4" : "AVG_4",
              "m" : "m"
            },
            "script" : {
              "source" : "params.m >= 20",
              "lang" : "painless"
            },
            "gap_policy" : "skip"
          }
        }
      }
    }
  }
}

@dai-chen
Copy link
Member

Using alias for SUM(age) doesn't raise exception but the result is not in order expected. From the DSL in explain, it seems alias s is being ignored? For some reason, I can only see alias m.

POST /_opendistro/_sql/_explain
{
  "query" : """
    SELECT COUNT(*), SUM(age) AS s, MIN(age) as m, MAX(age), AVG(age)
    FROM accounts
    GROUP BY gender
    HAVING m >= 20
    ORDER BY s, m DESC
	"""
}

{
  "from" : 0,
  "size" : 0,
  "_source" : {
    "includes" : [
      "COUNT",
      "SUM",
      "MIN",
      "MAX",
      "AVG"
    ],
    "excludes" : [ ]
  },
  "aggregations" : {
    "gender.keyword" : {
      "terms" : {
        "field" : "gender.keyword",
        "size" : 200,
        "min_doc_count" : 1,
        "shard_min_doc_count" : 0,
        "show_term_doc_count_error" : false,
        "order" : [
          {
            "m" : "desc"
          },
          {
            "_key" : "asc"
          }
        ]
      },
      "aggregations" : {
        "COUNT_0" : {
          "value_count" : {
            "field" : "_index"
          }
        },
        "s" : {
          "sum" : {
            "field" : "age"
          }
        },
        "m" : {
          "min" : {
            "field" : "age"
          }
        },
        "MAX_3" : {
          "max" : {
            "field" : "age"
          }
        },
        "AVG_4" : {
          "avg" : {
            "field" : "age"
          }
        },
        "bucket_filter" : {
          "bucket_selector" : {
            "buckets_path" : {
              "COUNT_0" : "COUNT_0",
              "s" : "s",
              "MAX_3" : "MAX_3",
              "AVG_4" : "AVG_4",
              "m" : "m"
            },
            "script" : {
              "source" : "params.m >= 20",
              "lang" : "painless"
            },
            "gap_policy" : "skip"
          }
        }
      }
    }
  }
}

@EveyWu
Copy link
Author

EveyWu commented Mar 31, 2020 •
edited
Loading

it correct for JSON formatting POST _opendistro/_sql/?format=json，but failed for GET _opendistro/_sql?sql= and JDBC query.

@penghuo penghuo added the JDBC/ODBC formatting This issue is related to JDBC or ODBC driver client label Apr 15, 2020
@dai-chen dai-chen added the SQL label Sep 11, 2020
@dai-chen dai-chen mentioned this issue Oct 16, 2020
Merged
@dai-chen
Copy link
Member

The issue has been fixed in new query engine (which is for now experimental and disabled by default). You can enable it to preview by changing the plugin setting: https://github.com/opendistro-for-elasticsearch/sql/blob/master/docs/user/admin/settings.rst#opendistro-sql-engine-new-enabled. Thanks!

Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Assignees
No one assigned
Labels
bug Something isn't working JDBC/ODBC formatting This issue is related to JDBC or ODBC driver client SQL
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@penghuo @dai-chen @EveyWu