jkosh44 commented on PR #14289:
URL: https://github.com/apache/datafusion/pull/14289#issuecomment-2625046192
> > ```rust
> > /// Any Null input causes the function to return Null.
> > Propogate,
> > ```
>
> I've updated the PR to use this. I just realized though that window and
aggregate functions (and maybe other function types?) completely ignore this
field in the `Signature`. That feels wrong, but I'll have to think about what
the right behavior should be.
In PostgreSQL, table functions follow the strict field by returning 0 rows:
```
postgres=# CREATE FUNCTION test_table(i1 INT, i2 INT)
RETURNS TABLE(i INT)
STRICT
LANGUAGE plpgsql AS $$
BEGIN
RETURN QUERY SELECT i1 UNION ALL SELECT i2;
END;
$$;
CREATE FUNCTION
postgres=# SELECT test_table(42, 43);
test_table
------------
42
43
(2 rows)
postgres=# SELECT test_table(42, NULL);
test_table
------------
(0 rows)
postgres=# SELECT test_table(NULL, 42);
test_table
------------
(0 rows)
postgres=# SELECT test_table(NULL, NULL);
test_table
------------
(0 rows)
```
Aggregate functions can't directly be labelled `strict`:
```
postgres=# CREATE FUNCTION custom_sum(INT, INT) RETURNS INT AS $$ SELECT $1
+ $2; $$ LANGUAGE SQL IMMUTABLE STRICT;
CREATE FUNCTION
postgres=# CREATE AGGREGATE custome_sum_agg(INT) (SFUNC = custom_sum, STYPE
= INT, STRICT);
WARNING: aggregate attribute "strict" not recognized
CREATE AGGREGATE
```
but, the docs say this about `strict`:
> If the state transition function is declared “strict”, then it cannot be
called with null inputs. With such a transition function, aggregate execution
behaves as follows. Rows with any null input values are ignored (the function
is not called and the previous state value is retained). If the initial state
value is null, then at the first row with all-nonnull input values, the first
argument value replaces the state value, and the transition function is invoked
at each subsequent row with all-nonnull input values. This is handy for
implementing aggregates like max. Note that this behavior is only available
when state_data_type is the same as the first arg_data_type. When these types
are different, you must supply a nonnull initial condition or use a nonstrict
transition function.
>
> If the state transition function is not strict, then it will be called
unconditionally at each input row, and must deal with null inputs and null
state values for itself. This allows the aggregate author to have full control
over the aggregate's handling of null values.
>
> If the final function is declared “strict”, then it will not be called
when the ending state value is null; instead a null result will be returned
automatically. (Of course this is just the normal behavior of strict
functions.) In any case the final function has the option of returning a null
value. For example, the final function for avg returns null when it sees there
were zero input rows.
https://www.postgresql.org/docs/current/sql-createaggregate.html
--
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]
