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

COUNT DISTINCT for 0 matching documents return NULL #50013

Closed
astefan opened this issue Dec 10, 2019 · 2 comments · Fixed by #50037
Closed

COUNT DISTINCT for 0 matching documents return NULL #50013

astefan opened this issue Dec 10, 2019 · 2 comments · Fixed by #50037
Assignees
@astefan
Labels
:Analytics/SQL SQL querying >bug

Comments

@astefan
Copy link
Contributor

astefan commented Dec 10, 2019

When #44745 has been introduced as a way of aggregations (SUM, MAX, AVG etc) dealing with a lack of documents matching a query and, implicitly, dealing with NULLs the case of COUNT(DISTINCT) (that uses a cardinality aggregation behind the scene) has been approached as well.

But, this introduced the issue of returning a NULL for COUNT(DISTINCT) where there are no matching documents. For example SELECT COUNT(DISTINCT languages) FROM test_emp WHERE languages > 100 returns NULL, whereas it should return 0 since there are no documents matching the condition languages > 100.
@astefan astefan self-assigned this Dec 10, 2019
@elasticmachine
Copy link
Collaborator

Pinging @elastic/es-search (:Search/SQL)

@astefan astefan mentioned this issue Dec 10, 2019
Merged
@astefan
Copy link
Contributor Author

astefan commented Dec 12, 2019

master (8.0.0): cb94731
7.x (7.6.0): e9e2e5f
7.5 (7.5.1): fd06783
7.4 (7.4.3): 22a6b6c

@jpountz jpountz changed the title SQL: COUNT DISTINCT for 0 matching documents return NULL COUNT DISTINCT for 0 matching documents return NULL Dec 18, 2019
This was referenced Feb 3, 2020
Closed
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@astefan astefan

Labels
:Analytics/SQL SQL querying >bug
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

SQL: COUNT DISTINCT returns 0 instead of NULL for no matching docs astefan/elasticsearch
2 participants
@astefan @elasticmachine