okumin commented on PR #5091:
URL: https://github.com/apache/hive/pull/5091#issuecomment-2064276270
@kasakrisz Thanks for checking. I double-checked the SQL:2023 Part 2.
(A) `10.9 <aggregate function> -> Syntax Rules -> 7) -> g)` mentions the
specification of SUM and AVG. It says the type of value expression shall be
numeric. So, if we strictly adhere to the standard, it could be best to throw
an exception when a string type is specified.
(B) If we allow implicit cast here(I've not found the rule justifying it),
it is reasonable to follow the rules of CAST. Based on `6.13 <cast
specification> -> General Rules -> 8) -> b)`, it should throw an exception.
(C) If the implicit numeric cast of `text` can be `null` here(though it
sounds like we accumulate too many assumptions, it can keep the current
behavior...), based on `10.9 <aggregate function> -> General Rules -> 7) ->
a)`, we should eliminate null values with warning. If the result set is empty,
based on `10.9 <aggregate function> -> General Rules -> 7) -> d) -> ⅱ)`, it
should return NULL.
So, if (A) we follow the standard strictly or (B) we accept implicit cast,
`SUM` and `AVG` should fail.
If we justify (C) the null conversion, `avg('text')` and `sum('text')`
should return `NULL`. `NULL / 1` should be `NULL` based on `6.30 <numeric value
expression> -> General Rules -> 1)`. So, in this case, all the cases,
`avg('text')`, `sum('text')`, `sum('text') / count('text')` should be null with
raising a warning.
In summary, if we accept a breaking change, I would say either of the
following ways sounds consistent. What do you think?
- We no longer accept the string type for `AVG` or `SUM`. It leads to a huge
incompatibility, but we can follow the standard
- We will make the UDFs skip invalid texts and `SUM('text')` return NULL.
Maybe we will fix `GenericUDAFSum.`
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
