NULL handling in sketch functions

[nikunjbhartia]

Can we handle nulls passed as sketches in sketch functions more gracefully ?
Current error messages are confusing and unless we dig deeper into a sql query and intermediate data, its difficult to find root cause of the error.

Example:
select bqutil.datasketches.theta_sketch_a_not_b(NULL, NULL)

returns error: BindingError: Cannot pass non-string to std::string at UDF$1(BYTES, BYTES, INT64) line 6, columns 25-26

select bqutil.datasketches.theta_sketch_union(NULL, NULL)

returns : BindingError: Cannot pass non-string to std::string at UDF$1(BYTES, BYTES, INT64, INT64) line 12, columns 25-26

Possible handling:

• Can nulls be treated as a no-op and we return null ?
  Or
• Return more meaningful error message

[will-lauer]

I believe Alex has already been addressing this issue, as we also ran into it and needed a solution. I'm not sure when the fix will be available, but I believe the semantics will end up being that a NULL sketch is treated the same as an non-null, empty sketch.

[AlexanderSaydakov]

This issue is slightly different. I addressed aggregate functions here: #140
Now aggregate functions always produce a sketch. Input of all nulls results in an empty sketch.
This issue is about better handling in scalar functions. I agree that we can do better (at least give a better error message). I am inclined to require valid sketches in scalar functions. This is debatable, of course. We can look at how we handle this in other systems like Hive, Pig, Druid and such.
Given that aggregates always produce a sketch, there should be no problem doing things like:
get_estimate(union(column of nulls)) - this will result in 0 estimate
a_not_b(union(nulls), union(nulls)) - this will produce empty sketch
and so on

[will-lauer]

For scalar functions, I strongly believe that NULL input should result in valid (possibly NULL) output and that we should NOT generate an error on NULL. There are multiple ways NULLs can creep into the system, and we should not fail a query if we encounter one. I believe this would match normal SQL expectations. So, I suggest that the various single argument scalar functions (such as theta_get_estimate) produce null when given null input. The multi-argument scalar functions, such as theta_sketch_union and theta_sketch_a_not_b are a bit more controversial. For those, while traditional SQL semantics would require one null input to nullify the output, I think we want to treat nulls the same way aggregation should - null sketches behave like empty sketches. So A union NULL produces A (not null). A a_not_b NULL also produces A (not null). Intersection is slightly weird, but I think A intersect NULL could produce the empty sketch rather than NULL and not confuse anyone.

[AlexanderSaydakov]

I would suggest to be consistent. If we treat NULL as empty sketch, then get_estimate(NULL) must be 0 and to_string(NULL) prints an empty sketch.

Alternatively we can produce NULL for NULL inputs, and change our approach to aggregates and return NULL as well.

In PostgreSQL functions can be declared "strict", which means any NULL input results in NULL output. These functions are not even called by the engine with NULL inputs. I don't think BQ has such a thing, but I believe that aggregate() method for aggregate functions is not called with NULLs.

[AlexanderSaydakov]

I would like to reach consensus regarding our approach to NULLs. I agree that query should not fail. And I believe we should be consistent. So we have two options:

• NULLs produce NULLs
• NULLs are treated as empty sketches

Which one should we prefer?

[nikunjbhartia]

Below are specs for null and valid-but-empty sketches in BigQuery native KLL sketch functions :
Would be good to align the behaviour for UD(A)Fs as well

Scalar functions

Input	KLL
NULL	NULL
Empty sketch	NULL
Invalid sketch	ERROR

Aggregate functions

Input	KLL
NULLs only	NULL
>=1 Invalid sketch	ERROR
Empty sketches + NULLs	NULL

[nikunjbhartia]

There might be some deviations depending on the sketch type.

For example:
SELECT HLL_COUNT.EXTRACT(null); returns 0 instead of null

[AlexanderSaydakov]

I am not sure I understand your tables.
What are these native KLL functions?
Why do you think we should follow them?
What does this mean: input "Empty sketch", KLL (is this output?) NULL?
I don't believe that a valid sketch (even an empty one) should produce a NULL.

I am not sure I like "Empty sketches + NULLs produce NULL" for aggregates. I understand treating NULLs as empty sketches as an alternative to "NULLs produce NULLs" rule. But this seems to suggest the opposite - treat empty sketches as NULLs.

[will-lauer]

For completeness for aggregates, I think there is one other case you want to define: no rows aggregated. So, I think the cases are

Input	Treatment
No rows to aggregate	NULL (This appears to be standard SQL semantics)
Only NULL rows to aggregate	NULL (This appears to be standard SQL semantics)
Some NULL, some valid sketches	Skip the nulls and aggregate the rest
All valid, non-null sketches	aggregate everything
At least 1 Invalid (error) sketch	error

I haven't found a clear definition for what should happen in SQL with single row (scalar) functions that receive NULL arguments. I think standard behavior is that if any argument is NULL, the result should be NULL, but I think that's probably not what we want for some of our functions. For the theta functions, I think we want something like this:

Function	Arguments	Treatment
Set functions (union, intersection, a Not B)	All arguments NULL	NULL
Set functions	Some arguments NULL	Treat NULLs as empty, so A ∪ NULL ∪ C is the same as A ∪ C and A ∩ NULL ∩ C is the same as A ∩ Empty ∩ C, which results in the empty set
Set functions	All non-null arguments	Normal treatment
Status functions (getEstimate, num retained, etc)	All arguments NULL	NULL
Status functions	Some arguments NULL	NULL (I think there is a special case here, where the seed or lgk argument is null. Those may deserve special handling, such that NULL causes an error)
Status functions	ALl non-null arguments	Normal treatment

[AlexanderSaydakov]

#148 should make scalar functions null-tolerant.
Perhaps after that we could change aggregate functions to produce nulls in case of all-nulls input