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

Having doesn't working on nested field. #188

Closed
penghuo opened this issue Sep 19, 2019 · 0 comments
Closed

Having doesn't working on nested field. #188

penghuo opened this issue Sep 19, 2019 · 0 comments
Assignees
Labels
bug Something isn't working

Comments

@penghuo
Copy link
Contributor

penghuo commented Sep 19, 2019

SQL

    SELECT e.name, MAX(p.started_year) as max
    FROM employee_nested_it AS e, e.projects AS p
    WHERE p.name LIKE '%security%'
    GROUP BY e.name
    HAVING max > 0

Test Data

https://github.com/opendistro-for-elasticsearch/sql/blob/master/src/test/resources/employee_nested.json

Expected Result

      "buckets" : [
        {
          "key" : "Bob Smith",
          "doc_count" : 1,
          "projects.started_year@NESTED" : {
            "doc_count" : 3,
            "max" : {
              "value" : 2015.0
            }
          }
        },
        {
          "key" : "Jane Smith",
          "doc_count" : 1,
          "projects.started_year@NESTED" : {
            "doc_count" : 2,
            "max" : {
              "value" : 2015.0
            }
          }
        }
      ]

Actual Result

{
  "error": {
    "root_cause": [
      {
        "type": "illegal_argument_exception",
        "reason": "No aggregation found for path [max]"
      }
    ],
    "type": "search_phase_execution_exception",
    "reason": "all shards failed",
    "phase": "query",
    "grouped": true,
    "failed_shards": [
      {
        "shard": 0,
        "index": "employee_nested_it",
        "node": "_2_opYNCR0a53f0tlIlm4Q",
        "reason": {
          "type": "illegal_argument_exception",
          "reason": "No aggregation found for path [max]"
        }
      }
    ],
    "caused_by": {
      "type": "illegal_argument_exception",
      "reason": "No aggregation found for path [max]",
      "caused_by": {
        "type": "illegal_argument_exception",
        "reason": "No aggregation found for path [max]"
      }
    }
  },
  "status": 400
}
@penghuo penghuo added the bug Something isn't working label Sep 19, 2019
@penghuo penghuo self-assigned this Sep 19, 2019
@penghuo penghuo closed this as completed Sep 25, 2019
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Labels
bug Something isn't working
Projects
None yet
Development

No branches or pull requests

1 participant