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.

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

SQL: MIN/MAX/AVG/SUM/PERCENTILE/PERCENTILE RANK handling on NULL values #34896

Closed
astefan opened this issue Oct 26, 2018 · 2 comments
Closed

Comments

@astefan
Copy link
Contributor

astefan commented Oct 26, 2018

sql> select max(languages) max, min(languages) min, sum(languages) sum, percentile(languages, 80) percent, percentile_rank(languages, 3) percent_rank, avg(languages) avg from test_emp group by languages;
      max      |      min      |      sum      |    percent    | percent_rank  |      avg
---------------+---------------+---------------+---------------+---------------+---------------
-Infinity      |Infinity       |0.0            |NaN            |NaN            |NaN
1.0            |1.0            |15.0           |1.0            |100.0          |1.0
2.0            |2.0            |38.0           |2.0            |100.0          |2.0
3.0            |3.0            |51.0           |3.0            |100.0          |3.0
4.0            |4.0            |72.0           |4.0            |0.0            |4.0
5.0            |5.0            |105.0          |5.0            |0.0            |5.0

The first line is for null values in the languages field. Shouldn't MIN and MAX of null values return a null? Same question for SUM. Not so sure about PERCENTILE and PERCENTILE_RANK though... ES returns null for these two. If MIN and MAX would return null probably the same should be done for AVG.
Opening this one up for discussion.

@astefan astefan added the :Analytics/SQL SQL querying label Oct 26, 2018
@elasticmachine
Copy link
Collaborator

Pinging @elastic/es-search-aggs

@costin
Copy link
Member

costin commented Oct 26, 2018

The issue here is that as the bucket doesn't contain any entries, the aggs return default values (since Aggs use primitives which cannot be nullified).

costin added a commit to costin/elasticsearch that referenced this issue Oct 26, 2018
When dealing with a null group, make sure the associated aggs return
null

Fix elastic#34896
costin added a commit that referenced this issue Oct 26, 2018
When dealing with a null group, the associated metric aggs need to
return null as well

Fix #34896
costin added a commit that referenced this issue Oct 26, 2018
When dealing with a null group, the associated metric aggs need to
return null as well

Fix #34896

(cherry picked from commit 55d48e4)
costin added a commit that referenced this issue Oct 28, 2018
When dealing with a null group, the associated metric aggs need to
return null as well

Fix #34896

(cherry picked from commit 55d48e4)
kcm pushed a commit that referenced this issue Oct 30, 2018
When dealing with a null group, the associated metric aggs need to
return null as well

Fix #34896
@colings86 colings86 added v6.5.0 and removed v6.5.1 labels Nov 2, 2018
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

4 participants