[BUG] Sum multiple null values should return null instead of 0

[LantaoJin]

What is the bug?
In most databases. Below query will return null

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary INT
);

INSERT INTO employees VALUES
(1, 'John', 'IT', 60000),
(2, 'Alice', 'IT', 55000),
(3, 'Bob', 'IT', null),
(4, 'Sarah', 'HR', 52000),
(5, 'Mike', 'HR', 48000),
(6, 'Lisa', 'Sales', null),
(7, 'Tom', 'Sales', null);

select sum(salary) from employees where department = 'Sales';

Return

+-------------+
| sum(salary) |
+-------------+
|        NULL |
+-------------+

But in PPL v2, it return 0. For example

  @Test
  public void testSumNull() {
    String response =
        execute(
            String.format(
                "source=%s | where department = 'Sales' | stats sum(salary)",
                TEST_INDEX_BANK_WITH_NULL_VALUES));
    assertEquals(
        ""
            + "{\n"
            + "  \"schema\": [\n"
            + "    {\n"
            + "      \"name\": \"sum(salary)\",\n"
            + "      \"type\": \"long\"\n"
            + "    }\n"
            + "  ],\n"
            + "  \"datarows\": [\n"
            + "    [\n"
            + "      0\n"
            + "    ]\n"
            + "  ],\n"
            + "  \"total\": 1,\n"
            + "  \"size\": 1\n"
            + "}",
        response);
  }

Current the v3 (Calcite) will return null as well. Should we change this behaviour align with other databases?

[LantaoJin]

If to align with other database, then will introduce a breaking change. cc @dai-chen @penghuo

[LantaoJin]

The root cause is the behavior of return 0 is from OpenSearch (agg pushdown applied). When we disable agg pushdown, the test case above will return null.

So I think it should be a bug of OpenSearch.

[LantaoJin]

cc @gaobinlong

[gaobinlong]

It should be a bug, created an issue in OpenSearch core.