[BUG] Aggregation functions return invalid result when processing NULLs

[Yury-Fridlyand]

I use TDVT data set for experiments

dataset

opensearchsql> select int0 i0, int1 i1, int2 from calcs;
fetched rows / total rows = 17/17
+------+------+--------+
| i0   | i1   | int2   |
|------+------+--------|
| 1    | -3   | 5      |
| null | -6   | -4     |
| null | null | 5      |
| null | -4   | -5     |
| 7    | null | 3      |
| 3    | null | 2      |
| 8    | null | 9      |
| null | 2    | 0      |
| null | 3    | -6     |
| 8    | 3    | -9     |
| 4    | null | -3     |
| 10   | -8   | -4     |
| null | null | 0      |
| 4    | null | 4      |
| 11   | null | -8     |
| 4    | null | -9     |
| 8    | -9   | 6      |
+------+------+--------+

The test query:

select max(int0), int1, min(int2) from calcs group by int1;

OpenSearch:

opensearchsql> select max(`int0`), `int1`, min(`int2`) from calcs group by `int1`;
fetched rows / total rows = 8/8
+---------------+--------+---------------+
| max(`int0`)   | int1   | min(`int2`)   |
|---------------+--------+---------------|
| 11            | null   | -9            |
| 8             | -9     | 6             |
| 10            | -8     | -4            |
| -2147483648   | -6     | -4            |
| -2147483648   | -4     | -5            |
| 1             | -3     | 5             |
| -2147483648   | 2      | 0             |
| 8             | 3      | -9            |
+---------------+--------+---------------+

MySQL:

mysql> select max(`int0`), `int1`, min(`int2`) from Calcs group by `int1`;
+-------------+------+-------------+
| max(`int0`) | int1 | min(`int2`) |
+-------------+------+-------------+
|           1 |   -3 |           5 |
|        NULL |   -6 |          -4 |
|          11 | NULL |          -9 |
|        NULL |   -4 |          -5 |
|        NULL |    2 |           0 |
|           8 |    3 |          -9 |
|          10 |   -8 |          -4 |
|           8 |   -9 |           6 |
+-------------+------+-------------+
8 rows in set (0.00 sec)

See also (could be related):

• [BUG] SUM(NULL) unexpectedly returns 0 #549
• [BUG] Aggregate on date function does not work as expected #645
• [BUG] SUM(const) fails with IllegalStateException #799
• [BUG] MIN(str, str) and MAX(str, str) functions fail #279
• [BUG] Opensearch returns null for all columns not listed in GROUP BY statement #258
• Aggregation support improvement #124

[Yury-Fridlyand]

BTW, why GROUP BY reorders rows?