Andrew Pilloud created BEAM-9711:
------------------------------------
Summary: sum(null) should be null not 0
Key: BEAM-9711
URL: https://issues.apache.org/jira/browse/BEAM-9711
Project: Beam
Issue Type: Bug
Components: dsl-sql-zetasql
Reporter: Andrew Pilloud
one failure in shard 3
{code}
Expected: ARRAY<STRUCT<row_id INT64, int64_sum INT64>>[
{1, NULL},
{2, NULL},
{3, NULL},
{4, 3},
{5, 4},
{6, 5},
{7, 6},
{8, 7},
{9, 8},
{10, 9},
{11, 10},
{12, 11},
{13, 12},
{14, 13}
]
Actual: ARRAY<STRUCT<row_id INT64, int64_sum INT64>>[
{1, 0},
{10, 9},
{7, 6},
{2, 0},
{13, 12},
{5, 4},
{4, 3},
{14, 13},
{6, 5},
{11, 10},
{12, 11},
{8, 7},
{3, 0},
{9, 8}
],
{code}
{code}
[prepare_database]
CREATE TABLE TableLarge AS
SELECT CAST(1 AS int64) as row_id,
CAST(NULL AS bool) as bool_val, CAST(NULL AS double) as double_val,
CAST(NULL AS int64) as int64_val, CAST(NULL AS uint64) as uint64_val,
CAST(NULL AS string) as str_val UNION ALL
SELECT 2, true, NULL, NULL, NULL, NULL UNION ALL
SELECT 3, false, 0.2, NULL, NULL, NULL UNION ALL
SELECT 4, true, 0.3, 3, NULL, NULL UNION ALL
SELECT 5, false, 0.4, 4, 15, "4" UNION ALL
SELECT 6, true, 0.5, 5, 17, "5" UNION ALL
SELECT 7, false, 0.6, 6, 19, "6" UNION ALL
SELECT 8, true, 0.7, 7, 21, "7" UNION ALL
SELECT 9, false, 0.8, 8, 23, "8" UNION ALL
SELECT 10, true, 0.9, 9, 25, "9" UNION ALL
SELECT 11, false, 1.0, 10, 27, "10" UNION ALL
SELECT 12, true, IEEE_DIVIDE(1, 0), 11, 29, "11" UNION ALL
SELECT 13, false, IEEE_DIVIDE(-1, 0), 12, 31, "12" UNION ALL
SELECT 14, true, IEEE_DIVIDE(0, 0), 13, 33, "13"
--
ARRAY<STRUCT<row_id INT64,
bool_val BOOL,
double_val DOUBLE,
int64_val INT64,
uint64_val UINT64,
str_val STRING>>
[
{1, NULL, NULL, NULL, NULL, NULL},
{2, true, NULL, NULL, NULL, NULL},
{3, false, 0.2, NULL, NULL, NULL},
{4, true, 0.3, 3, NULL, NULL},
{5, false, 0.4, 4, 15, "4"},
{6, true, 0.5, 5, 17, "5"},
{7, false, 0.6, 6, 19, "6"},
{8, true, 0.7, 7, 21, "7"},
{9, false, 0.8, 8, 23, "8"},
{10, true, 0.9, 9, 25, "9"},
{11, false, 1, 10, 27, "10"},
{12, true, inf, 11, 29, "11"},
{13, false, -inf, 12, 31, "12"},
{14, true, nan, 13, 33, "13"}
]
==
# SUM should work with GROUP BY.
[name=aggregation_sum_group_by]
SELECT row_id, SUM(int64_val) int64_sum FROM TableLarge GROUP BY row_id
--
ARRAY<STRUCT<row_id INT64, int64_sum INT64>>[
{1, NULL},
{2, NULL},
{3, NULL},
{4, 3},
{5, 4},
{6, 5},
{7, 6},
{8, 7},
{9, 8},
{10, 9},
{11, 10},
{12, 11},
{13, 12},
{14, 13}
]
{code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
