The SQL UNION is the reason here that's causing (a) the table is not append only, and (b) the inner GroupBy.
If you check out the UNION operator, it suggests that: "Any duplicate records are automatically removed unless
So: (1) it is definitely not append-only operation as you need to revision when duplicate records are generated. and
(2) I think Calcite optimizer is translating the entire execution into two individual projection operations followed by a all column GroupBy to dedup the messages.