exec: some aggregations should promote the return type

[rafiss]

Currently in the vectorized engine, SUM and AVG will always return the same type as the input type.

But consulting the Postgres pg_catalog shows that the return type should differ sometimes:

postgres> select proname, proargtypes[0]::regtype as arg, prorettype::regtype as ret from pg_catalog.pg_proc where proname = 'sum' and proargt
 ypes[0] != prorettype;
+-----------+----------+---------+
| proname   | arg      | ret     |
|-----------+----------+---------|
| sum       | bigint   | numeric |
| sum       | integer  | bigint  |
| sum       | smallint | bigint  |
+-----------+----------+---------+

postgres> select proname, proargtypes[0]::regtype as arg, prorettype::regtype as ret from pg_catalog.pg_proc where proname = 'avg' and proargt
 ypes[0] != prorettype;
+-----------+----------+------------------+
| proname   | arg      | ret              |
|-----------+----------+------------------|
| avg       | bigint   | numeric          |
| avg       | integer  | numeric          |
| avg       | smallint | numeric          |
| avg       | real     | double precision |
+-----------+----------+------------------+

It might be best to knock out #38823 at the same time as doing this.

Also perhaps doing this would eliminate the need for the SUM_INT aggregator.

[rafiss]

We also could consider handling projections. Note that there are two separate errors that can be triggered.

root@127.0.0.1:63701/defaultdb> create table foo (a int8 primary key);
CREATE TABLE

root@127.0.0.1:63701/defaultdb> insert into foo values(10);
INSERT 1

root@127.0.0.1:63701/defaultdb> select (a/1) from foo;
  ?column?
+----------+
        10
(1 row)

root@127.0.0.1:63701/defaultdb> set experimental_vectorize=always;
SET

root@127.0.0.1:63701/?> select (a/1) from foo;
E190718 13:42:55.424737 697 sql/distsqlrun/server.go:464  [n1,client=127.0.0.1:63703,user=root] error setting up flow: unable to columnarize render expression "@1::DECIMAL": unhandled projection expression type: *tree.CastExpr
pq: unable to columnarize render expression "@1::DECIMAL": unhandled projection expression type: *tree.CastExpr

root@127.0.0.1:63701/defaultdb> set experimental_vectorize=off;
SET


root@127.0.0.1:63701/defaultdb> insert into foo values(-9223372036854775808);
INSERT 1

root@127.0.0.1:63701/defaultdb> set experimental_vectorize=always;
SET

root@127.0.0.1:63701/?> select (a/-1) from foo;
E190718 13:47:51.642656 697 sql/distsqlrun/server.go:464  [n1,client=127.0.0.1:63703,user=root] error setting up flow: unable to columnarize render expression "@1 / (-1):::INT8": BinaryExpr on int with decimal result is unhandled
pq: unable to columnarize render expression "@1 / (-1):::INT8": BinaryExpr on int with decimal result is unhandled

[jordanlewis]

I think one of the work items here is to support CastExpr, which I guess gets planned in some of these circumstances. That might be a good bridge to figuring out a better strategy for dealing with mixed type overloads in general.

[rafiss]

The work for CastExpr and mixed type BinaryExpr for projections will be tracked in #39189.