Have "count" return a signed type

[philrz]

Currently in SuperSQL, count (both the aggregate function and operator) returns a uint64 type, but it would be more in keeping with SQL conventions to return a signed type such as int64. It may also provide a better UX.

Details

Repro is with super commit 14c966f.

A user recently inquired in a community Slack thread:

I wish count would just return an int64, not uint64. To me, it’s just noise and I basically am always casting it to int64 so I can drop the type specification in my scripts. The technical correctness resulting in a chore for me feels blerg.

That is, the user is responding to this effect with SUP output:

$ super -version
Version: 14c966f0a

$ echo '{a:1}' | super -c 'count()' -
1::uint64

Hence having to do something like this to avoid the type decorator:

$ echo '{a:1}' | super -c 'count() | values this::int64' -
1

The fact it's been uint64 is in some ways a reflection of our tendency to borrow from programming language concepts (after all, a count result can never be negative, so while it's unlikely the extra numeric range will be needed, why not make it available?) and also dating back to our having once borrowed concepts from the Zeek type system (their count type happens to be a a 64-bit unsigned integer for similar reasons).

Meanwhile, many SQL systems do seem to use signed types for the value returned by COUNT, e.g.,

$ psql postgres -c "SELECT pg_typeof(COUNT(*)) FROM (SELECT 1);"
 pg_typeof 
-----------
 bigint
(1 row)

$ duckdb -c "SELECT typeof(COUNT(*)) FROM (SELECT 1);"
┌──────────────────────┐
│ typeof(count_star()) │
│       varchar        │
├──────────────────────┤
│ BIGINT               │
└──────────────────────┘

Whereas ClickHouse happens to use a uint64 as well.

$ clickhouse --query "SELECT toTypeName(COUNT(*)) FROM (SELECT 1);"
UInt64

While the SQL standard doesn't seem to say anything specific about what the type must be, if we happen to go along with the "signed" pattern here among SQL implementations it will also give this user what they were seeking. It will also make the count examples in the docs a little less noisy.

[philrz]

Verified in super commit b08bfb7.

Both the count operator and aggregate function as well as the dcount aggregate function and uniq -c all return an int64 now, which means no type decorator is shown in SUP output for such values.

$ super -version
Version: b08bfb7da

$ echo '{a:1}' | super -c 'count()' -
1

$ echo '{a:1}' | super -c 'dcount(a)' -
1

$ echo '{a:1}' | super -c 'count' -
{that:{a:1},count:1}

$ echo '{a:1}' | super -c 'uniq -c' -
{value:{a:1},count:1}

Thanks @nwt!