The data type is not compatible with other system, for example spark or PG database

[liukun4515]

Describe the bug
In the doc, we can know the datatypes supported in the datafusion.

In other system, such as PG database and spark, the systems just support signed numeric datatypes.

But in the datafusion, we can create a table with unsigned numeric, this is not compatible with the doc and other system.

In the code, we also consider the case of unsigned numeric datatypes.

To Reproduce

❯ create table test as select count(1);
0 rows in set. Query took 0.004 seconds.
❯ \d test
+---------------+--------------+------------+-----------------+-----------+-------------+
| table_catalog | table_schema | table_name | column_name     | data_type | is_nullable |
+---------------+--------------+------------+-----------------+-----------+-------------+
| datafusion    | public       | test       | COUNT(UInt8(1)) | UInt64    | YES         |
+---------------+--------------+------------+-----------------+-----------+-------------+
1 row in set. Query took 0.008 seconds.
❯

From example, we can can see that the datatype is UINT64.

Expected behavior
A clear and concise description of what you expected to happen.

Additional context
Add any other context about the problem here.

[liukun4515]

In the datafusion, the result type of some function or expr is unsigned numeric datatype. if we use the evaluation result as the create table as select xxxx, we may get some table with unexpected data type compared with the doc.

@houqp @alamb

[houqp]

I think this should be fine? Being a superset of postgres is still considered being compatible with postgers right? I.e. if users want to get the exact semantic in postgres, they can opt to only use signed numeric numbers as input. The only problem I see is we need to make sure to only return signed values for functions that might return value with different signs compared to the input so we don't return unsigned value with signed input.

[alamb]

I think this ticket is proposing to align datafusion more closely to postgres's types (e.g. produce Int64 instead of UInt64 as the result of a count() aggregate function), which seems like a reasonable improvement to me

I may misunderstand the intent, however

[liukun4515]

@alamb @houqp The superset of the PG is a good point.
So we can just resolve the return value(UInt64 to Int64) which should be signed in some functions.
Please assign this issue to me.

[liukun4515]

It was fixed.