Aggregate UDF result for an empty group behaves as NULL, but IS NULL returns false

An aggregate UDF can report that its initial aggregate state represents SQL NULL by setting the is_null output parameter in its *_clear() callback.
For an aggregate receiving no input rows, MariaDB does not appear to propagate this NULL state from udf_handler to Item_udf_sum::null_value.

Consequently, the aggregate result behaves as NULL when its value is evaluated, but the expression aggregate_udf(...) IS NULL returns 0.
Groups containing rows, including groups where every argument is NULL, behave correctly when the UDF preserves the NULL state in its *_add() callback.

Attached reproducer

The attached file mariadb-aggregate-udf-null-reproducer.c contains a complete minimal aggregate UDF named nullable_product.
The UDF multiplies all non-NULL integer arguments.
It returns SQL NULL if it receives no non-NULL arguments.
The attached source was compiled with warnings enabled and tested independently from the library in which the issue was originally discovered.

Building the attached UDF on macOS with Homebrew MariaDB 10.11

Adjust the Homebrew versioned include paths if necessary.

/usr/bin/clang 

    -std=c11 

    -Wall 

    -Wextra 

    -Werror 

    -bundle 

    -undefined dynamic_lookup 

    -I/opt/homebrew/Cellar/mariadb@10.11/10.11.19/include/mysql 

    -I/opt/homebrew/Cellar/mariadb@10.11/10.11.19/include/mysql/mysql 

    mariadb-aggregate-udf-null-reproducer.c 

    -o nullable_product.so

Place nullable_product.so in the MariaDB plugin directory.
The active directory can be determined with:

SHOW VARIABLES LIKE 'plugin_dir';

Register the aggregate UDF:

CREATE AGGREGATE FUNCTION nullable_product

RETURNS INTEGER

SONAME 'nullable_product.so';

Steps to reproduce

Create a database and a table containing ordinary values:

CREATE DATABASE udf_null_test;

USE udf_null_test;

CREATE TABLE values_for_product (

    value INTEGER

);

INSERT INTO values_for_product VALUES (2), (3);

Verify the non-empty case:

SELECT

    nullable_product(value) AS aggregate_value,

    nullable_product(value) IS NULL AS is_null_result,

    COALESCE(nullable_product(value), 999) AS coalesced_value

FROM values_for_product;

The result is correct:

aggregate_value | is_null_result | coalesced_value

6               | 0              | 6

Now exclude every input row:

SELECT

    nullable_product(value) AS aggregate_value,

    nullable_product(value) IS NULL AS is_null_result,

    COALESCE(nullable_product(value), 999) AS coalesced_value

FROM values_for_product

WHERE FALSE;

Actual result

aggregate_value | is_null_result | coalesced_value

NULL            | 0              | 999

The direct result is NULL.
COALESCE() also treats the result as NULL.
However, IS NULL returns 0.

Expected result

aggregate_value | is_null_result | coalesced_value

NULL            | 1              | 999

All three expressions should agree that the aggregate result is NULL.

Control case with NULL input rows

A group containing rows whose arguments are all NULL behaves correctly:

TRUNCATE values_for_product;

INSERT INTO values_for_product VALUES (NULL), (NULL);

SELECT

    nullable_product(value) AS aggregate_value,

    nullable_product(value) IS NULL AS is_null_result,

    COALESCE(nullable_product(value), 999) AS coalesced_value

FROM values_for_product;

Result:

aggregate_value | is_null_result | coalesced_value

NULL            | 1              | 999

This indicates that the defect specifically affects an empty input set for which the UDF's *_add() callback is never invoked.

Complete observed test output

The attached UDF produced the following output on MariaDB 10.11.19:

10.11.19-MariaDB

nonempty    6     0    6

empty       NULL  0    999

all-null    NULL  1    999

The columns after the case name are:
Aggregate result
Result of nullable_product(...) IS NULL
Result of COALESCE(nullable_product(...), 999)

Suspected source of the defect

The MariaDB 10.11.19 source contains the following implementation of udf_handler::clear() in sql/sql_udf.h, lines 118–123:

void clear()

{

  is_null= 0;

  Udf_func_clear func= u_d->func_clear;

  func(&initid, &is_null, &error);

}

The aggregate UDF's *_clear() callback can therefore set udf_handler::is_null.
However, Item_udf_sum::clear() in sql/item_sum.cc, lines 3369–3374, does not propagate that state to Item_udf_sum::null_value:

void Item_udf_sum::clear()

{

  DBUG_ENTER("Item_udf_sum::clear");

  udf.clear();

  DBUG_VOID_RETURN;

}

In comparison, Item_udf_sum::add() explicitly propagates the NULL state in sql/item_sum.cc, lines 3376–3382:

bool Item_udf_sum::add()

{

  my_bool tmp_null_value;

  DBUG_ENTER("Item_udf_sum::add");

  udf.add(&tmp_null_value);

  null_value= tmp_null_value;

  DBUG_RETURN(0);

}

For an empty input set, add() is never called.
The NULL state reported through the UDF's *_clear() callback therefore does not appear to reach Item_udf_sum::null_value.

Item_sum::is_null() only returns this cached member in sql/item_sum.h, line 493:

bool is_null() override { return null_value; }

Item_func_isnull::val_bool() uses this method directly in sql/item_cmpfunc.cc, lines 5724–5729:

bool Item_func_isnull::val_bool()

{

  DBUG_ASSERT(fixed());

  if (const_item() && !args[0]->maybe_null())

    return 0;

  return args[0]->is_null() ? 1: 0;

}

It does not evaluate the aggregate UDF's final function first.
Normal value evaluation does invoke the final UDF function.
The final function in the attached reproducer sets is_null to 1, so direct output and COALESCE() correctly recognize the result as NULL.
This difference explains why the SQL expressions disagree.

Suggested direction for a fix

The NULL and error state produced by an aggregate UDF's *_clear() callback should probably be propagated to Item_udf_sum::null_value, analogously to the existing add() path.
Conceptually, the internal interface could work as follows:

void udf_handler::clear(my_bool *null_value)

{

  is_null= 0;

  error= 0;

  Udf_func_clear func= u_d->func_clear;

  func(&initid, &is_null, &error);

  *null_value= (my_bool) (is_null || error);

}

void Item_udf_sum::clear()

{

  DBUG_ENTER("Item_udf_sum::clear");

  my_bool tmp_null_value;

  udf.clear(&tmp_null_value);

  null_value= tmp_null_value;

  DBUG_VOID_RETURN;

}

This is only a suggested direction.
The essential requirement is that the NULL state reported by the aggregate UDF's *_clear() callback must update the enclosing Item_udf_sum::null_value, including when the aggregate receives no rows.