You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@hive.apache.org by "Adam Kramer (JIRA)" <ji...@apache.org> on 2009/03/24 18:41:52 UTC
[jira] Created: (HIVE-362) avg() returns null if any item in the
list is null; create function that doesn't
avg() returns null if any item in the list is null; create function that doesn't
--------------------------------------------------------------------------------
Key: HIVE-362
URL: https://issues.apache.org/jira/browse/HIVE-362
Project: Hadoop Hive
Issue Type: Wish
Components: Clients, Query Processor
Reporter: Adam Kramer
Some of the current aggregates (sum, avg) have a fairly standard behavior: If any item in the list is NULL, the sum, average, etc., cannot be computed. And so, NULL is returned.
1) If this is the case, the query should return much faster--see a null, return NULL, exit(0).
2) It would be nice to have versions or ways to use these functions with NULL data--specifically, to treat the NULL as zero or to ignore the NULL and return the results for non-NULL data.
This also would apply to the variance functions referenced in https://issues.apache.org/jira/browse/HIVE-165
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Resolved: (HIVE-362) avg() ignores null values; consider
variant that doesn't
Posted by "Zheng Shao (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/HIVE-362?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Zheng Shao resolved HIVE-362.
-----------------------------
Resolution: Won't Fix
> avg() ignores null values; consider variant that doesn't
> --------------------------------------------------------
>
> Key: HIVE-362
> URL: https://issues.apache.org/jira/browse/HIVE-362
> Project: Hadoop Hive
> Issue Type: Bug
> Components: Query Processor
> Reporter: Adam Kramer
>
> Some of the current aggregates (sum, avg) have a fairly standard behavior: If any item in the list is NULL, the sum, average, etc., cannot be computed. And so, NULL is returned.
> 1) If this is the case, the query should return much faster--see a null, return NULL, exit(0).
> 2) It would be nice to have versions or ways to use these functions with NULL data--specifically, to treat the NULL as zero or to ignore the NULL and return the results for non-NULL data.
> This also would apply to the variance functions referenced in https://issues.apache.org/jira/browse/HIVE-165
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (HIVE-362) avg() returns null if any item in the
list is null; create function that doesn't
Posted by "Adam Kramer (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/HIVE-362?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12688777#action_12688777 ]
Adam Kramer commented on HIVE-362:
----------------------------------
Very sorry! After further testing, the actual problem I was having was that SUM returns a double, and I was putting it into an INT container without an explicit cast.
So, maybe this bug is a request to change the wiki to make this clear.
Also...let's actually say the opposite. We really should have AVG or some version of it return NULL if any NULL values are found. This is the default behavior in R, and for good reason...I will link to the description of why once I find it.
Basically, the argument is that when a sum, average, or variance depends on n in some sense, comparisons among these groups cannot be trusted. For example, if there are 5 nulls in a set of 20 variables, SUM(x) / COUNT(x) returns a different value than AVG(x)...that is very unintuitive.
> avg() returns null if any item in the list is null; create function that doesn't
> --------------------------------------------------------------------------------
>
> Key: HIVE-362
> URL: https://issues.apache.org/jira/browse/HIVE-362
> Project: Hadoop Hive
> Issue Type: Bug
> Components: Query Processor
> Reporter: Adam Kramer
>
> Some of the current aggregates (sum, avg) have a fairly standard behavior: If any item in the list is NULL, the sum, average, etc., cannot be computed. And so, NULL is returned.
> 1) If this is the case, the query should return much faster--see a null, return NULL, exit(0).
> 2) It would be nice to have versions or ways to use these functions with NULL data--specifically, to treat the NULL as zero or to ignore the NULL and return the results for non-NULL data.
> This also would apply to the variance functions referenced in https://issues.apache.org/jira/browse/HIVE-165
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Issue Comment Edited: (HIVE-362) avg() returns null if any
item in the list is null; create function that doesn't
Posted by "Raghotham Murthy (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/HIVE-362?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12688827#action_12688827 ]
Raghotham Murthy edited comment on HIVE-362 at 3/24/09 12:47 PM:
-----------------------------------------------------------------
In your example, even with NULLs, SUM(a) / COUNT(a) will be the same as AVG(a). This is because COUNT(a) returns the number of rows which have non-null 'a'. This is different from COUNT(1) which returns the count of all rows.
was (Author: rsm):
In your example, even with NULLs, SUM(a) / COUNT(a) will be the same as AVG(a). This is because COUNT(a) returns the number of rows which have non-null 'a'. This is different from COUNT(*) which returns the count of all rows.
> avg() returns null if any item in the list is null; create function that doesn't
> --------------------------------------------------------------------------------
>
> Key: HIVE-362
> URL: https://issues.apache.org/jira/browse/HIVE-362
> Project: Hadoop Hive
> Issue Type: Bug
> Components: Query Processor
> Reporter: Adam Kramer
>
> Some of the current aggregates (sum, avg) have a fairly standard behavior: If any item in the list is NULL, the sum, average, etc., cannot be computed. And so, NULL is returned.
> 1) If this is the case, the query should return much faster--see a null, return NULL, exit(0).
> 2) It would be nice to have versions or ways to use these functions with NULL data--specifically, to treat the NULL as zero or to ignore the NULL and return the results for non-NULL data.
> This also would apply to the variance functions referenced in https://issues.apache.org/jira/browse/HIVE-165
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (HIVE-362) avg() returns null if any item in the
list is null; create function that doesn't
Posted by "Raghotham Murthy (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/HIVE-362?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Raghotham Murthy updated HIVE-362:
----------------------------------
Component/s: (was: Clients)
Issue Type: Bug (was: Wish)
Marking this as a bug.
> avg() returns null if any item in the list is null; create function that doesn't
> --------------------------------------------------------------------------------
>
> Key: HIVE-362
> URL: https://issues.apache.org/jira/browse/HIVE-362
> Project: Hadoop Hive
> Issue Type: Bug
> Components: Query Processor
> Reporter: Adam Kramer
>
> Some of the current aggregates (sum, avg) have a fairly standard behavior: If any item in the list is NULL, the sum, average, etc., cannot be computed. And so, NULL is returned.
> 1) If this is the case, the query should return much faster--see a null, return NULL, exit(0).
> 2) It would be nice to have versions or ways to use these functions with NULL data--specifically, to treat the NULL as zero or to ignore the NULL and return the results for non-NULL data.
> This also would apply to the variance functions referenced in https://issues.apache.org/jira/browse/HIVE-165
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (HIVE-362) avg() ignores null values; consider
variant that doesn't
Posted by "Adam Kramer (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/HIVE-362?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12697577#action_12697577 ]
Adam Kramer commented on HIVE-362:
----------------------------------
This is not a problem anymore.
Just to be a bit opinionated for a few moments, though, I do believe the standards to be wrong on this issue; NULL values are an excellent way to force scientists to really think about the query they're running, and implicitly removing them will generally lead to harder-to-debug errors and more wasted time than having to call the "remove nulls" version, call it avg_rn, explicitly.
> avg() ignores null values; consider variant that doesn't
> --------------------------------------------------------
>
> Key: HIVE-362
> URL: https://issues.apache.org/jira/browse/HIVE-362
> Project: Hadoop Hive
> Issue Type: Bug
> Components: Query Processor
> Reporter: Adam Kramer
>
> Some of the current aggregates (sum, avg) have a fairly standard behavior: If any item in the list is NULL, the sum, average, etc., cannot be computed. And so, NULL is returned.
> 1) If this is the case, the query should return much faster--see a null, return NULL, exit(0).
> 2) It would be nice to have versions or ways to use these functions with NULL data--specifically, to treat the NULL as zero or to ignore the NULL and return the results for non-NULL data.
> This also would apply to the variance functions referenced in https://issues.apache.org/jira/browse/HIVE-165
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (HIVE-362) avg() returns null if any item in the
list is null; create function that doesn't
Posted by "Raghotham Murthy (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/HIVE-362?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12688827#action_12688827 ]
Raghotham Murthy commented on HIVE-362:
---------------------------------------
In your example, even with NULLs, SUM(a) / COUNT(a) will be the same as AVG(a). This is because COUNT(a) returns the number of rows which have non-null 'a'. This is different from COUNT(*) which returns the count of all rows.
> avg() returns null if any item in the list is null; create function that doesn't
> --------------------------------------------------------------------------------
>
> Key: HIVE-362
> URL: https://issues.apache.org/jira/browse/HIVE-362
> Project: Hadoop Hive
> Issue Type: Bug
> Components: Query Processor
> Reporter: Adam Kramer
>
> Some of the current aggregates (sum, avg) have a fairly standard behavior: If any item in the list is NULL, the sum, average, etc., cannot be computed. And so, NULL is returned.
> 1) If this is the case, the query should return much faster--see a null, return NULL, exit(0).
> 2) It would be nice to have versions or ways to use these functions with NULL data--specifically, to treat the NULL as zero or to ignore the NULL and return the results for non-NULL data.
> This also would apply to the variance functions referenced in https://issues.apache.org/jira/browse/HIVE-165
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (HIVE-362) avg() ignores null values; consider
variant that doesn't
Posted by "Zheng Shao (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/HIVE-362?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12695283#action_12695283 ]
Zheng Shao commented on HIVE-362:
---------------------------------
Hi Adam, it seems this is not a problem any more. Can I close the issue?
> avg() ignores null values; consider variant that doesn't
> --------------------------------------------------------
>
> Key: HIVE-362
> URL: https://issues.apache.org/jira/browse/HIVE-362
> Project: Hadoop Hive
> Issue Type: Bug
> Components: Query Processor
> Reporter: Adam Kramer
>
> Some of the current aggregates (sum, avg) have a fairly standard behavior: If any item in the list is NULL, the sum, average, etc., cannot be computed. And so, NULL is returned.
> 1) If this is the case, the query should return much faster--see a null, return NULL, exit(0).
> 2) It would be nice to have versions or ways to use these functions with NULL data--specifically, to treat the NULL as zero or to ignore the NULL and return the results for non-NULL data.
> This also would apply to the variance functions referenced in https://issues.apache.org/jira/browse/HIVE-165
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (HIVE-362) avg() returns null if any item in the
list is null; create function that doesn't
Posted by "Raghotham Murthy (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/HIVE-362?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12688769#action_12688769 ]
Raghotham Murthy commented on HIVE-362:
---------------------------------------
Aggregates are supposed to ignore nulls according to the standard. The code for SUM and AVG seems to indicate that they do ignore NULLs. Are you getting NULLs?
> avg() returns null if any item in the list is null; create function that doesn't
> --------------------------------------------------------------------------------
>
> Key: HIVE-362
> URL: https://issues.apache.org/jira/browse/HIVE-362
> Project: Hadoop Hive
> Issue Type: Wish
> Components: Clients, Query Processor
> Reporter: Adam Kramer
>
> Some of the current aggregates (sum, avg) have a fairly standard behavior: If any item in the list is NULL, the sum, average, etc., cannot be computed. And so, NULL is returned.
> 1) If this is the case, the query should return much faster--see a null, return NULL, exit(0).
> 2) It would be nice to have versions or ways to use these functions with NULL data--specifically, to treat the NULL as zero or to ignore the NULL and return the results for non-NULL data.
> This also would apply to the variance functions referenced in https://issues.apache.org/jira/browse/HIVE-165
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (HIVE-362) avg() ignores null values; consider
variant that doesn't
Posted by "Neil Conway (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/HIVE-362?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Neil Conway updated HIVE-362:
-----------------------------
Summary: avg() ignores null values; consider variant that doesn't (was: avg() returns null if any item in the list is null; create function that doesn't)
> avg() ignores null values; consider variant that doesn't
> --------------------------------------------------------
>
> Key: HIVE-362
> URL: https://issues.apache.org/jira/browse/HIVE-362
> Project: Hadoop Hive
> Issue Type: Bug
> Components: Query Processor
> Reporter: Adam Kramer
>
> Some of the current aggregates (sum, avg) have a fairly standard behavior: If any item in the list is NULL, the sum, average, etc., cannot be computed. And so, NULL is returned.
> 1) If this is the case, the query should return much faster--see a null, return NULL, exit(0).
> 2) It would be nice to have versions or ways to use these functions with NULL data--specifically, to treat the NULL as zero or to ignore the NULL and return the results for non-NULL data.
> This also would apply to the variance functions referenced in https://issues.apache.org/jira/browse/HIVE-165
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.