Skip to content
New issue

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

Sign up for GitHub

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

Already on GitHub? Sign in to your account

Jump to bottom

SQL: CAST doesn't work in ORDER BY #34557

Closed
imotov opened this issue Oct 17, 2018 · 2 comments
Closed

SQL: CAST doesn't work in ORDER BY #34557

imotov opened this issue Oct 17, 2018 · 2 comments
Labels
:Analytics/SQL SQL querying >bug v6.6.0

Comments

@imotov
Copy link
Contributor

imotov commented Oct 17, 2018

On the surface it might look similar to #33361, but it is causing an exception in painless rather then in planner, so I think it's a separate issue. To reproduce:

DELETE test

PUT test/doc/1
{
  "foo": "42"
}

POST _xpack/sql?format=text/plain
{
  "query": """
      SELECT foo, CAST(foo.keyword AS DOUBLE) test_str FROM "test" ORDER BY foo.keyword
      """
}

POST _xpack/sql?format=text/plain
{
  "query": """
      SELECT foo, CAST(foo.keyword AS DOUBLE) test_str FROM "test" ORDER BY test_str
      """
}

The first SELECT statement works, but the second causes the exception:

{
  "error": {
    "root_cause": [
      {
        "type": "script_exception",
        "reason": "runtime error",
        "script_stack": [
          "doc[params.v0].value",
          "              ^---- HERE"
        ],
        "script": "doc[params.v0].value",
        "lang": "painless"
      }
    ],
    "type": "search_phase_execution_exception",
    "reason": "all shards failed",
    "phase": "query",
    "grouped": true,
    "failed_shards": [
      {
        "shard": 0,
        "index": "test",
        "node": "o8qIS4EtReu3fksMhbWqdQ",
        "reason": {
          "type": "script_exception",
          "reason": "runtime error",
          "script_stack": [
            "doc[params.v0].value",
            "              ^---- HERE"
          ],
          "script": "doc[params.v0].value",
          "lang": "painless",
          "caused_by": {
            "type": "class_cast_exception",
            "reason": "class java.lang.String cannot be cast to class java.lang.Double (java.lang.String and java.lang.Double are in module java.base of loader 'bootstrap')"
          }
        }
      }
    ]
  },
  "status": 400
}

The full stacktrace:

[elasticsearch] [2018-10-17T10:10:51,441][DEBUG][o.e.a.s.TransportSearchAction] [node-0] [test][0], node[o8qIS4EtReu3fksMhbWqdQ], [P], s[STARTED], a[id=8PktJQKERCyTljlxsvoWug]: Failed to execute [SearchRequest{searchType=QUERY_THEN_FETCH, indices=[test], indicesOptions=IndicesOptions[ignore_unavailable=false, allow_no_indices=true, expand_wildcards_open=true, expand_wildcards_closed=false, allow_aliases_to_multiple_indices=true, forbid_closed_indices=true, ignore_aliases=false], types=[], routing='null', preference='null', requestCache=null, scroll=Scroll{keepAlive=1.5m}, maxConcurrentShardRequests=0, batchedReduceSize=512, preFilterShardSize=128, allowPartialSearchResults=false, source={"size":1000,"timeout":"45s","_source":{"includes":["foo"],"excludes":[]},"docvalue_fields":[{"field":"foo.keyword","format":"use_field_mapping"}],"sort":[{"_script":{"script":{"source":"doc[params.v0].value","lang":"painless","params":{"v0":"foo.keyword"}},"type":"number","order":"asc"}}]}}]
[elasticsearch] org.elasticsearch.transport.RemoteTransportException: [node-0][127.0.0.1:9300][indices:data/read/search[phase/query]]
[elasticsearch] Caused by: org.elasticsearch.search.query.QueryPhaseExecutionException: Query Failed [Failed to execute main query]
[elasticsearch]         at org.elasticsearch.search.query.QueryPhase.execute(QueryPhase.java:302) ~[elasticsearch-7.0.0-alpha1-SNAPSHOT.jar:7.0.0-alpha1-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.search.query.QueryPhase.execute(QueryPhase.java:108) ~[elasticsearch-7.0.0-alpha1-SNAPSHOT.jar:7.0.0-alpha1-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.search.SearchService.loadOrExecuteQueryPhase(SearchService.java:329) ~[elasticsearch-7.0.0-alpha1-SNAPSHOT.jar:7.0.0-alpha1-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.search.SearchService.executeQueryPhase(SearchService.java:376) ~[elasticsearch-7.0.0-alpha1-SNAPSHOT.jar:7.0.0-alpha1-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.search.SearchService.access$100(SearchService.java:120) ~[elasticsearch-7.0.0-alpha1-SNAPSHOT.jar:7.0.0-alpha1-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.search.SearchService$2.onResponse(SearchService.java:338) [elasticsearch-7.0.0-alpha1-SNAPSHOT.jar:7.0.0-alpha1-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.search.SearchService$2.onResponse(SearchService.java:334) [elasticsearch-7.0.0-alpha1-SNAPSHOT.jar:7.0.0-alpha1-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.search.SearchService$4.doRun(SearchService.java:1073) [elasticsearch-7.0.0-alpha1-SNAPSHOT.jar:7.0.0-alpha1-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.common.util.concurrent.ThreadContext$ContextPreservingAbstractRunnable.doRun(ThreadContext.java:723) [elasticsearch-7.0.0-alpha1-SNAPSHOT.jar:7.0.0-alpha1-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:37) [elasticsearch-7.0.0-alpha1-SNAPSHOT.jar:7.0.0-alpha1-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.common.util.concurrent.TimedRunnable.doRun(TimedRunnable.java:41) [elasticsearch-7.0.0-alpha1-SNAPSHOT.jar:7.0.0-alpha1-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:37) [elasticsearch-7.0.0-alpha1-SNAPSHOT.jar:7.0.0-alpha1-SNAPSHOT]
[elasticsearch]         at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) [?:?]
[elasticsearch]         at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) [?:?]
[elasticsearch]         at java.lang.Thread.run(Thread.java:834) [?:?]
[elasticsearch] Caused by: org.elasticsearch.script.ScriptException: runtime error
[elasticsearch]         at org.elasticsearch.painless.PainlessScript.convertToScriptException(PainlessScript.java:94) ~[?:?]
[elasticsearch]         at org.elasticsearch.painless.PainlessScript$Script.execute(doc[params.v0].value:15) ~[?:?]
[elasticsearch]         at org.elasticsearch.search.sort.ScriptSortBuilder$2$1.doubleValue(ScriptSortBuilder.java:380) ~[elasticsearch-7.0.0-alpha1-SNAPSHOT.jar:7.0.0-alpha1-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.index.fielddata.FieldData$14.advanceExact(FieldData.java:572) ~[elasticsearch-7.0.0-alpha1-SNAPSHOT.jar:7.0.0-alpha1-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.index.fielddata.NumericDoubleValues$1.advanceExact(NumericDoubleValues.java:44) ~[elasticsearch-7.0.0-alpha1-SNAPSHOT.jar:7.0.0-alpha1-SNAPSHOT]
[elasticsearch]         at org.apache.lucene.search.FieldComparator$DoubleComparator.getValueForDoc(FieldComparator.java:181) ~[lucene-core-8.0.0-snapshot-7d0a7782fa.jar:8.0.0-snapshot-7d0a7782fa 7d0a7782fa7e99250bccfb4d3e995485c3f0ca19 - jimczi - 2018-09-18 14:02:34]
[elasticsearch]         at org.apache.lucene.search.FieldComparator$DoubleComparator.copy(FieldComparator.java:200) ~[lucene-core-8.0.0-snapshot-7d0a7782fa.jar:8.0.0-snapshot-7d0a7782fa 7d0a7782fa7e99250bccfb4d3e995485c3f0ca19 - jimczi - 2018-09-18 14:02:34]
[elasticsearch]         at org.apache.lucene.search.TopFieldCollector$SimpleFieldCollector$1.collect(TopFieldCollector.java:140) ~[lucene-core-8.0.0-snapshot-7d0a7782fa.jar:8.0.0-snapshot-7d0a7782fa 7d0a7782fa7e99250bccfb4d3e995485c3f0ca19 - jimczi - 2018-09-18 14:02:34]
[elasticsearch]         at org.apache.lucene.search.MatchAllDocsQuery$1$1.score(MatchAllDocsQuery.java:62) ~[lucene-core-8.0.0-snapshot-7d0a7782fa.jar:8.0.0-snapshot-7d0a7782fa 7d0a7782fa7e99250bccfb4d3e995485c3f0ca19 - jimczi - 2018-09-18 14:02:34]
[elasticsearch]         at org.apache.lucene.search.BulkScorer.score(BulkScorer.java:39) ~[lucene-core-8.0.0-snapshot-7d0a7782fa.jar:8.0.0-snapshot-7d0a7782fa 7d0a7782fa7e99250bccfb4d3e995485c3f0ca19 - jimczi - 2018-09-18 14:02:34]
[elasticsearch]         at org.apache.lucene.search.IndexSearcher.search(IndexSearcher.java:652) ~[lucene-core-8.0.0-snapshot-7d0a7782fa.jar:8.0.0-snapshot-7d0a7782fa 7d0a7782fa7e99250bccfb4d3e995485c3f0ca19 - jimczi - 2018-09-18 14:02:34]
[elasticsearch]         at org.elasticsearch.search.internal.ContextIndexSearcher.search(ContextIndexSearcher.java:177) ~[elasticsearch-7.0.0-alpha1-SNAPSHOT.jar:7.0.0-alpha1-SNAPSHOT]
[elasticsearch]         at org.apache.lucene.search.IndexSearcher.search(IndexSearcher.java:443) ~[lucene-core-8.0.0-snapshot-7d0a7782fa.jar:8.0.0-snapshot-7d0a7782fa 7d0a7782fa7e99250bccfb4d3e995485c3f0ca19 - jimczi - 2018-09-18 14:02:34]
[elasticsearch]         at org.elasticsearch.search.query.QueryPhase.execute(QueryPhase.java:270) ~[elasticsearch-7.0.0-alpha1-SNAPSHOT.jar:7.0.0-alpha1-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.search.query.QueryPhase.execute(QueryPhase.java:108) ~[elasticsearch-7.0.0-alpha1-SNAPSHOT.jar:7.0.0-alpha1-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.search.SearchService.loadOrExecuteQueryPhase(SearchService.java:329) ~[elasticsearch-7.0.0-alpha1-SNAPSHOT.jar:7.0.0-alpha1-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.search.SearchService.executeQueryPhase(SearchService.java:376) ~[elasticsearch-7.0.0-alpha1-SNAPSHOT.jar:7.0.0-alpha1-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.search.SearchService.access$100(SearchService.java:120) ~[elasticsearch-7.0.0-alpha1-SNAPSHOT.jar:7.0.0-alpha1-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.search.SearchService$2.onResponse(SearchService.java:338) ~[elasticsearch-7.0.0-alpha1-SNAPSHOT.jar:7.0.0-alpha1-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.search.SearchService$2.onResponse(SearchService.java:334) [elasticsearch-7.0.0-alpha1-SNAPSHOT.jar:7.0.0-alpha1-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.search.SearchService$4.doRun(SearchService.java:1073) ~[elasticsearch-7.0.0-alpha1-SNAPSHOT.jar:7.0.0-alpha1-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.common.util.concurrent.ThreadContext$ContextPreservingAbstractRunnable.doRun(ThreadContext.java:723) ~[elasticsearch-7.0.0-alpha1-SNAPSHOT.jar:7.0.0-alpha1-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:37) ~[elasticsearch-7.0.0-alpha1-SNAPSHOT.jar:7.0.0-alpha1-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.common.util.concurrent.TimedRunnable.doRun(TimedRunnable.java:41) ~[elasticsearch-7.0.0-alpha1-SNAPSHOT.jar:7.0.0-alpha1-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:37) ~[elasticsearch-7.0.0-alpha1-SNAPSHOT.jar:7.0.0-alpha1-SNAPSHOT]
[elasticsearch]         at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) ~[?:?]
[elasticsearch]         at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) ~[?:?]
[elasticsearch]         at java.lang.Thread.run(Thread.java:834) ~[?:?]
[elasticsearch] Caused by: java.lang.ClassCastException: class java.lang.String cannot be cast to class java.lang.Double (java.lang.String and java.lang.Double are in module java.base of loader 'bootstrap')
[elasticsearch]         at org.elasticsearch.painless.Def.DefTodoubleImplicit(Def.java:705) ~[?:?]
[elasticsearch]         at org.elasticsearch.painless.PainlessScript$Script.execute(doc[params.v0].value:15) ~[?:?]
[elasticsearch]         at org.elasticsearch.search.sort.ScriptSortBuilder$2$1.doubleValue(ScriptSortBuilder.java:380) ~[elasticsearch-7.0.0-alpha1-SNAPSHOT.jar:7.0.0-alpha1-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.index.fielddata.FieldData$14.advanceExact(FieldData.java:572) ~[elasticsearch-7.0.0-alpha1-SNAPSHOT.jar:7.0.0-alpha1-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.index.fielddata.NumericDoubleValues$1.advanceExact(NumericDoubleValues.java:44) ~[elasticsearch-7.0.0-alpha1-SNAPSHOT.jar:7.0.0-alpha1-SNAPSHOT]
[elasticsearch]         at org.apache.lucene.search.FieldComparator$DoubleComparator.getValueForDoc(FieldComparator.java:181) ~[lucene-core-8.0.0-snapshot-7d0a7782fa.jar:8.0.0-snapshot-7d0a7782fa 7d0a7782fa7e99250bccfb4d3e995485c3f0ca19 - jimczi - 2018-09-18 14:02:34]
[elasticsearch]         at org.apache.lucene.search.FieldComparator$DoubleComparator.copy(FieldComparator.java:200) ~[lucene-core-8.0.0-snapshot-7d0a7782fa.jar:8.0.0-snapshot-7d0a7782fa 7d0a7782fa7e99250bccfb4d3e995485c3f0ca19 - jimczi - 2018-09-18 14:02:34]
[elasticsearch]         at org.apache.lucene.search.TopFieldCollector$SimpleFieldCollector$1.collect(TopFieldCollector.java:140) ~[lucene-core-8.0.0-snapshot-7d0a7782fa.jar:8.0.0-snapshot-7d0a7782fa 7d0a7782fa7e99250bccfb4d3e995485c3f0ca19 - jimczi - 2018-09-18 14:02:34]
[elasticsearch]         at org.apache.lucene.search.MatchAllDocsQuery$1$1.score(MatchAllDocsQuery.java:62) ~[lucene-core-8.0.0-snapshot-7d0a7782fa.jar:8.0.0-snapshot-7d0a7782fa 7d0a7782fa7e99250bccfb4d3e995485c3f0ca19 - jimczi - 2018-09-18 14:02:34]
[elasticsearch]         at org.apache.lucene.search.BulkScorer.score(BulkScorer.java:39) ~[lucene-core-8.0.0-snapshot-7d0a7782fa.jar:8.0.0-snapshot-7d0a7782fa 7d0a7782fa7e99250bccfb4d3e995485c3f0ca19 - jimczi - 2018-09-18 14:02:34]
[elasticsearch]         at org.apache.lucene.search.IndexSearcher.search(IndexSearcher.java:652) ~[lucene-core-8.0.0-snapshot-7d0a7782fa.jar:8.0.0-snapshot-7d0a7782fa 7d0a7782fa7e99250bccfb4d3e995485c3f0ca19 - jimczi - 2018-09-18 14:02:34]
[elasticsearch]         at org.elasticsearch.search.internal.ContextIndexSearcher.search(ContextIndexSearcher.java:177) ~[elasticsearch-7.0.0-alpha1-SNAPSHOT.jar:7.0.0-alpha1-SNAPSHOT]
[elasticsearch]         at org.apache.lucene.search.IndexSearcher.search(IndexSearcher.java:443) ~[lucene-core-8.0.0-snapshot-7d0a7782fa.jar:8.0.0-snapshot-7d0a7782fa 7d0a7782fa7e99250bccfb4d3e995485c3f0ca19 - jimczi - 2018-09-18 14:02:34]
[elasticsearch]         at org.elasticsearch.search.query.QueryPhase.execute(QueryPhase.java:270) ~[elasticsearch-7.0.0-alpha1-SNAPSHOT.jar:7.0.0-alpha1-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.search.query.QueryPhase.execute(QueryPhase.java:108) ~[elasticsearch-7.0.0-alpha1-SNAPSHOT.jar:7.0.0-alpha1-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.search.SearchService.loadOrExecuteQueryPhase(SearchService.java:329) ~[elasticsearch-7.0.0-alpha1-SNAPSHOT.jar:7.0.0-alpha1-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.search.SearchService.executeQueryPhase(SearchService.java:376) ~[elasticsearch-7.0.0-alpha1-SNAPSHOT.jar:7.0.0-alpha1-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.search.SearchService.access$100(SearchService.java:120) ~[elasticsearch-7.0.0-alpha1-SNAPSHOT.jar:7.0.0-alpha1-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.search.SearchService$2.onResponse(SearchService.java:338) ~[elasticsearch-7.0.0-alpha1-SNAPSHOT.jar:7.0.0-alpha1-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.search.SearchService$2.onResponse(SearchService.java:334) ~[elasticsearch-7.0.0-alpha1-SNAPSHOT.jar:7.0.0-alpha1-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.search.SearchService$4.doRun(SearchService.java:1073) ~[elasticsearch-7.0.0-alpha1-SNAPSHOT.jar:7.0.0-alpha1-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.common.util.concurrent.ThreadContext$ContextPreservingAbstractRunnable.doRun(ThreadContext.java:723) ~[elasticsearch-7.0.0-alpha1-SNAPSHOT.jar:7.0.0-alpha1-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:37) ~[elasticsearch-7.0.0-alpha1-SNAPSHOT.jar:7.0.0-alpha1-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.common.util.concurrent.TimedRunnable.doRun(TimedRunnable.java:41) ~[elasticsearch-7.0.0-alpha1-SNAPSHOT.jar:7.0.0-alpha1-SNAPSHOT]
[elasticsearch]         at org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:37) ~[elasticsearch-7.0.0-alpha1-SNAPSHOT.jar:7.0.0-alpha1-SNAPSHOT]
[elasticsearch]         at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) ~[?:?]
[elasticsearch]         at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) ~[?:?]
[elasticsearch]         at java.lang.Thread.run(Thread.java:834) ~[?:?]
@imotov imotov added >bug :Analytics/SQL SQL querying labels Oct 17, 2018
@elasticmachine
Copy link
Collaborator

Pinging @elastic/es-search-aggs

@imotov imotov mentioned this issue Nov 12, 2018
Merged
@matriv
Copy link
Contributor

matriv commented Dec 19, 2018

Fixed by #36640

@matriv matriv closed this as completed Dec 19, 2018
@pcsanwald pcsanwald mentioned this issue Jan 17, 2019
Merged
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
:Analytics/SQL SQL querying >bug v6.6.0
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
6 participants
@colings86 @imotov @astefan @matriv @elasticmachine @jimczi