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: GROUPing BY YEAR function applied on a CASTed AS DATE field results in NULL values #49386

Closed
astefan opened this issue Nov 20, 2019 · 3 comments · Fixed by #49559
Closed

SQL: GROUPing BY YEAR function applied on a CASTed AS DATE field results in NULL values #49386

astefan opened this issue Nov 20, 2019 · 3 comments · Fixed by #49559
Assignees
@matriv
Labels
:Analytics/SQL SQL querying >bug

Comments

@astefan
Copy link
Contributor

astefan commented Nov 20, 2019

SELECT YEAR(CAST(birth_date AS DATE)) FROM test_emp GROUP BY 1 results in

YEAR(CAST(birth_date AS DATE))
------------------------------------
null
@elasticmachine
Copy link
Collaborator

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

@matriv matriv self-assigned this Nov 22, 2019
matriv added a commit to matriv/elasticsearch that referenced this issue Nov 25, 2019
@matriv
SQL: Fix issue with GROUP BY YEAR()
41e2798 
Grouping By YEAR() is translated to a histogram aggregation, but
previously if there was a scalar function invloved (e.g.:
`YEAR(date + INTERVAL 2 YEARS)`), there was no proper script created
and the histogram was applied on a field with name: `date + INTERVAL 2 YEARS`
which doesn't make sense, and resulted in null result.

Check the underlying field of YEAR() and if it's a function call
`asScript()` to properly get the painless script on which the histogram
is applied.

Fixes: elastic#49386
@matriv matriv mentioned this issue Nov 25, 2019
Merged
matriv added a commit that referenced this issue Nov 26, 2019
@matriv
SQL: Fix issue with GROUP BY YEAR() (#49559)
93c37ab 
Grouping By YEAR() is translated to a histogram aggregation, but
previously if there was a scalar function invloved (e.g.:
`YEAR(date + INTERVAL 2 YEARS)`), there was no proper script created
and the histogram was applied on a field with name: `date + INTERVAL 2 YEARS`
which doesn't make sense, and resulted in null result.

Check the underlying field of YEAR() and if it's a function call
`asScript()` to properly get the painless script on which the histogram
is applied.

Fixes: #49386
matriv added a commit that referenced this issue Nov 26, 2019
@matriv
SQL: Fix issue with GROUP BY YEAR() (#49559)
b0cb7bf 
Grouping By YEAR() is translated to a histogram aggregation, but
previously if there was a scalar function invloved (e.g.:
`YEAR(date + INTERVAL 2 YEARS)`), there was no proper script created
and the histogram was applied on a field with name: `date + INTERVAL 2 YEARS`
which doesn't make sense, and resulted in null result.

Check the underlying field of YEAR() and if it's a function call
`asScript()` to properly get the painless script on which the histogram
is applied.

Fixes: #49386
(cherry picked from commit 93c37ab)
@matriv
Copy link
Contributor

matriv commented Nov 26, 2019

master : 93c37ab
7.x : b0cb7bf
7.5 : e56a463

matriv added a commit that referenced this issue Nov 26, 2019
@matriv
SQL: Fix issue with GROUP BY YEAR() (#49559)
e56a463 
Grouping By YEAR() is translated to a histogram aggregation, but
previously if there was a scalar function invloved (e.g.:
`YEAR(date + INTERVAL 2 YEARS)`), there was no proper script created
and the histogram was applied on a field with name: `date + INTERVAL 2 YEARS`
which doesn't make sense, and resulted in null result.

Check the underlying field of YEAR() and if it's a function call
`asScript()` to properly get the painless script on which the histogram
is applied.

Fixes: #49386
(cherry picked from commit 93c37ab)
matriv added a commit that referenced this issue Nov 26, 2019
@matriv
SQL: Fix issue with GROUP BY YEAR() (#49559)
e54d7d5 
Grouping By YEAR() is translated to a histogram aggregation, but
previously if there was a scalar function invloved (e.g.:
`YEAR(date + INTERVAL 2 YEARS)`), there was no proper script created
and the histogram was applied on a field with name: `date + INTERVAL 2 YEARS`
which doesn't make sense, and resulted in null result.

Check the underlying field of YEAR() and if it's a function call
`asScript()` to properly get the painless script on which the histogram
is applied.

Fixes: #49386
(cherry picked from commit 93c37ab)
matriv added a commit that referenced this issue Nov 26, 2019
@matriv
SQL: Fix issue with GROUP BY YEAR() (#49559)
90378a9 
Grouping By YEAR() is translated to a histogram aggregation, but
previously if there was a scalar function invloved (e.g.:
`YEAR(date + INTERVAL 2 YEARS)`), there was no proper script created
and the histogram was applied on a field with name: `date + INTERVAL 2 YEARS`
which doesn't make sense, and resulted in null result.

Check the underlying field of YEAR() and if it's a function call
`asScript()` to properly get the painless script on which the histogram
is applied.

Fixes: #49386
(cherry picked from commit 93c37ab)
(cherry picked from commit e54d7d5)
@matriv
Copy link
Contributor

matriv commented Nov 26, 2019

Backported to previous branches where the YEAR() resulted in fixed interval and not a calendar interval:
7.4 : e54d7d5
6.8 : 90378a9

@matriv matriv mentioned this issue Nov 28, 2019
Closed
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

@matriv matriv

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

Successfully merging a pull request may close this issue.

Fix issue with GROUP BY YEAR() matriv/elasticsearch
3 participants
@astefan @matriv @elasticmachine