feat: adding SUM0 definition for aggregate functions #465

vibhatha · 2023-03-07T00:26:05Z

This PR includes a resolution to #259

github-actions · 2023-03-07T00:26:30Z

ACTION NEEDED

Substrait follows the Conventional Commits specification for release automation.

The PR title and description are used as the merge commit message. Please update your PR title and description to match the specification.

extensions/functions_arithmetic.yaml

EpsilonPrime · 2023-03-07T01:25:41Z

extensions/functions_arithmetic.yaml

@@ -922,6 +922,69 @@ aggregate_functions:
        decomposable: MANY
        intermediate: fp64?
        return: fp64?
+  - name: "sum0"


An analog could be the multiply function returning one for a set of zero elements. Do we want to make a similar change?

I think you’re correct. We should probably add that in another PR?

Agreed, no need to make this PR do too much.

EpsilonPrime · 2023-03-07T01:29:09Z

extensions/functions_arithmetic.yaml

@@ -922,6 +922,69 @@ aggregate_functions:
        decomposable: MANY
        intermediate: fp64?
        return: fp64?
+  - name: "sum0"
+    description: Sum a set of values. The sum of zero elements yields zero.


An alternative is to have one sum function with an option for how to handle the set of zero inputs. Potential complications of course arise if we want to add an option after the fact but is that something we are interested in?

I see your point. Let’s discuss further.
Options feature is interesting and that could simplify things, but not sure how to represent nullability in return statements.

cc @westonpace @jacques-n

It's also pretty easy to get the behavior already with COALESCE(SUM(x), 0) (though this is not exactly the same because it would return 0 for the sum of all null input where this function would hopefully return null). However, I don't think we need to shy away from functions that can be represented elsewhere. So I think adding a new function or option is still a good idea.

I'm not sure where exactly to draw the line between "new function" and "option to an existing function". I don't have a really strong opinion either way.

Actually I think it would behave the same as COALESCE(SUM(x), 0) . It is my understanding that if you had a set of all null values and you do a SUM0() on them, you would get 0, not null.

This is a description I found for SUM0 that I found here https://www.tabnine.com/code/java/classes/org.apache.calcite.sql.fun.SqlSumEmptyIsZeroAggFunction

Sum0 is an aggregator which returns the sum of the values which go into it like Sum. It differs in that when no non null values are applied zero is returned instead of null.

Then let's update the description to something like...

Sum a set of values. The sum of zero elements yields zero. Null values are ignored.

We should probably clarify this for sum as well but that can be left for a future PR.

@westonpace And I think we should probably leave the return type without nulls. Is that okay?

Yes, good catch. Given this updated understanding I think the return type should be DECALRED_OUTPUT nullability and a non-nullable return type.

Got it. I will update the PR. Thanks @wmalpica for your feedback as well.

westonpace

Given:

sum0([1, 2, 3]) = 6
sum0([]) = 0

What does this result in:

sum0([null, null, null]) = ?

Is it null or 0? I think it should be null. If you agree we should note that in the description. In this case, I think the return attribute still needs to be i64?/fp64?.

westonpace · 2023-03-07T14:52:43Z

sqlite has sum and total. total is the sqlite equivalent of sum0. Curiously, in sqlite, total([null, null, null]) = 0 so perhaps my intuition is off :)

EpsilonPrime · 2023-03-07T19:47:05Z

sqlite has sum and total. total is the sqlite equivalent of sum0. Curiously, in sqlite, total([null, null, null]) = 0 so perhaps my intuition is off :)

Ah, so total is more of "add all the values that are present" where sum is treating null as unknown. (So for sum adding a known value to an unknown one is still unknown.)

westonpace · 2023-03-07T22:34:15Z

Note that #401 is probably related

wmalpica · 2023-03-22T19:22:58Z

sqlite has sum and total. total is the sqlite equivalent of sum0. Curiously, in sqlite, total([null, null, null]) = 0 so perhaps my intuition is off :)

Yes I think SUM0 would be like total

westonpace

One small change.

westonpace · 2023-03-24T17:49:05Z

extensions/functions_arithmetic.yaml

+            values: [ SILENT, SATURATE, ERROR ]
+        nullability: DECLARED_OUTPUT
+        decomposable: MANY
+        intermediate: i64?


Suggested change

intermediate: i64?

intermediate: i64

The intermediate is also non-nullable.

westonpace · 2023-03-24T17:49:13Z

extensions/functions_arithmetic.yaml

+            values: [ SILENT, SATURATE, ERROR ]
+        nullability: DECLARED_OUTPUT
+        decomposable: MANY
+        intermediate: i64?


Suggested change

intermediate: i64?

intermediate: i64

westonpace · 2023-03-24T17:49:17Z

extensions/functions_arithmetic.yaml

+            values: [ SILENT, SATURATE, ERROR ]
+        nullability: DECLARED_OUTPUT
+        decomposable: MANY
+        intermediate: i64?


Suggested change

intermediate: i64?

intermediate: i64

westonpace · 2023-03-24T17:49:23Z

extensions/functions_arithmetic.yaml

+            values: [ SILENT, SATURATE, ERROR ]
+        nullability: DECLARED_OUTPUT
+        decomposable: MANY
+        intermediate: i64?


Suggested change

intermediate: i64?

intermediate: i64

westonpace · 2023-03-24T17:49:31Z

extensions/functions_arithmetic.yaml

+            values: [ SILENT, SATURATE, ERROR ]
+        nullability: DECLARED_OUTPUT
+        decomposable: MANY
+        intermediate: fp64?


Suggested change

intermediate: fp64?

intermediate: fp64

westonpace · 2023-03-24T17:49:44Z

extensions/functions_arithmetic.yaml

+            values: [ SILENT, SATURATE, ERROR ]
+        nullability: DECLARED_OUTPUT
+        decomposable: MANY
+        intermediate: fp64?


Suggested change

intermediate: fp64?

intermediate: fp64

vibhatha requested review from jacques-n, westonpace and jvanstraten March 7, 2023 00:26

vibhatha self-assigned this Mar 7, 2023

vibhatha added the enhancement New feature or request label Mar 7, 2023

EpsilonPrime reviewed Mar 7, 2023

View reviewed changes

westonpace requested changes Mar 7, 2023

View reviewed changes

vibhatha force-pushed the sum0-agg branch from c885b7e to 240bbdc Compare March 24, 2023 16:57

vibhatha marked this pull request as ready for review March 24, 2023 16:58

westonpace reviewed Mar 24, 2023

View reviewed changes

vibhatha added 3 commits March 27, 2023 12:47

feat(sum0): adding SUM0 definition for aggregate functions

e72900e

fix: update description

ccba491

fix: address reviews

cce01b1

vibhatha force-pushed the sum0-agg branch from 85fe44a to cce01b1 Compare March 27, 2023 07:17

vibhatha requested a review from westonpace March 27, 2023 13:40

westonpace approved these changes Mar 27, 2023

View reviewed changes

westonpace merged commit 73228b4 into substrait-io:main Mar 27, 2023

EpsilonPrime mentioned this pull request Mar 19, 2024

feat: add decimal type support for sum0 function #610

Merged

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

feat: adding SUM0 definition for aggregate functions #465

feat: adding SUM0 definition for aggregate functions #465

vibhatha commented Mar 7, 2023 •

edited

Loading

github-actions bot commented Mar 7, 2023

EpsilonPrime Mar 7, 2023

vibhatha Mar 7, 2023

EpsilonPrime Mar 7, 2023

vibhatha Mar 7, 2023

EpsilonPrime Mar 7, 2023

vibhatha Mar 7, 2023 •

edited

Loading

westonpace Mar 7, 2023

wmalpica Mar 22, 2023

westonpace Mar 23, 2023

vibhatha Mar 24, 2023

westonpace Mar 24, 2023

vibhatha Mar 24, 2023

westonpace left a comment

westonpace commented Mar 7, 2023

EpsilonPrime commented Mar 7, 2023

westonpace commented Mar 7, 2023

wmalpica commented Mar 22, 2023

westonpace left a comment

westonpace Mar 24, 2023

westonpace Mar 24, 2023

westonpace Mar 24, 2023

westonpace Mar 24, 2023

westonpace Mar 24, 2023

westonpace Mar 24, 2023

feat: adding SUM0 definition for aggregate functions #465

feat: adding SUM0 definition for aggregate functions #465

Conversation

vibhatha commented Mar 7, 2023 • edited Loading

github-actions bot commented Mar 7, 2023

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

vibhatha Mar 7, 2023 • edited Loading

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

westonpace left a comment

Choose a reason for hiding this comment

westonpace commented Mar 7, 2023

EpsilonPrime commented Mar 7, 2023

westonpace commented Mar 7, 2023

wmalpica commented Mar 22, 2023

westonpace left a comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

vibhatha commented Mar 7, 2023 •

edited

Loading

vibhatha Mar 7, 2023 •

edited

Loading