SQL: COUNT() makes a function resolution in WHERE clause to fail

[imotov]

A single COUNT(*) in the select portion prevents the field attribute resolution in WHERE clause

DELETE test
PUT test/_doc/1
{
  "int": 10
}

POST _sql?format=txt
{
  "query": "SELECT COUNT(*) FROM test WHERE ABS(int) > 0 GROUP BY int"
}

The query above fails wtth the following error:

{
  "error": {
    "root_cause": [
      {
        "type": "verification_exception",
        "reason": "Found 1 problem(s)\nline 1:55: Unknown column [int], did you mean [int]?"
      }
    ],
    "type": "verification_exception",
    "reason": "Found 1 problem(s)\nline 1:55: Unknown column [int], did you mean [int]?"
  },
  "status": 400
}

However if we add something besides COUNT(*) to the select clause, it works:

POST _sql?format=txt
{
  "query": "SELECT COUNT(*), MAX(int) FROM test WHERE ABS(int) > 0 GROUP BY int"
}

Removing the function for the WHERE clause also works:

POST _sql?format=txt
{
  "query": "SELECT COUNT(*) FROM test WHERE int > 0 GROUP BY int"
}

This issue might be related to #41413 but the use case seems to be quite different since it requires calling a function in WHERE and doesn't use a literal.

[elasticmachine]

Pinging @elastic/es-search

[codebird]

@imotov this is fixed with my proposed solution.

curl -XPOST -H 'Content-Type: application/json' -u elastic-admin:elastic-password "http://localhost:9200/_sql?format=txt" -d'
{
  "query": "SELECT COUNT(*) FROM test WHERE ABS(int) > 0 GROUP BY int"
}'
   COUNT(*)    
---------------
2              

but the issue #41413, is not solved, there is still something I am missing!

[imotov]

@codebird is there a PR with your proposed solution?

[codebird]

nope didn't do a PR, because it doesn't solve #41413, I just wanted some input on the approach.

https://github.com/elastic/elasticsearch/compare/master...codebird:issue%2341413?expand=1

Here's the code if you want to check it.

[imotov]

@codebird thanks! I think it would greatly simplify the feedback process if you could sign CLA and open a PR for this fix (assuming that you want it to be eventually merged into elasticsearch).

[codebird]

Oh I have already signed the CLA. I'll make a pull request and reference this issue and 41413 as they are closely related

[matriv]

master : 45b8580
7.x : 5b32d11
7.6 : 967f702