Clickhouse: Wrong result on Negate UInt64

[yohannj]

Describe the bug
On v20.10, negate seems to change a non existing bit sign on UInt64 and considers the result to be Int64.
On v20.3 LTS, there is the same behavior querying through HTTP. However using the CLI, UInt64 is cast to Float64 and result is OK (with some precision loss).

Apart from the CLI in v20.3, the result of negate can even be positive.

How to reproduce

docker run -d --name ch20.3 --ulimit nofile=262144:262144 -p 8124:8123 yandex/clickhouse-server:20.3.8.53
curl -s "http://localhost:8124/" -d 'SELECT toTypeName(negate(18446744073709551000)), negate(18446744073709551000)'
docker exec -it ch20.3 clickhouse-client --query "SELECT toTypeName(negate(18446744073709551000)), negate(18446744073709551000)"
docker stop ch20.3

docker run -d --name ch20.10 --ulimit nofile=262144:262144 -p 8125:8123 yandex/clickhouse-server:20.10.3.30
curl -s "http://localhost:8125/" -d 'SELECT toTypeName(negate(18446744073709551000)), negate(18446744073709551000)'
docker exec -it ch20.10 clickhouse-client --query "SELECT toTypeName(negate(18446744073709551000)), negate(18446744073709551000)"
docker stop ch20.20

Expected behavior
toTypeName(negate(18446744073709551000)) returns Float64 in 20.3, Int128 in 20.10
negate(18446744073709551000) returns -18446744073709552000 in 20.3, -18446744073709551000 in 20.10

Error message and/or stacktrace

Additional context
At Contentsquare we are working on building a Scala driver to write and execute ClickHouse queries, helping us in our day to day work. One fundamental feature that we will support is type safety.
Our tests helps us making sure the output Scala type supports the different return types of ClickHouse, but also helps us identify such issues so that we do not allow user to write queries that can return unexpected results like here.

[alexey-milovidov]

Tests:

SELECT toTypeName(-toUInt64(1))

It should be the same as:

SELECT toTypeName(0 - toUInt64(1))

I think that returning Int64 (with overflow) is correct.

[alexey-milovidov]

I expect that promotion to Float64 was a bug in some old version but now the behaviour is correct.

The principle:

• arithmetic operations on integers promotes to the larger type, up to 64bit types, but not larger;
• promotion to floating point types never performed if arguments are integers;
• result of subtraction or negation is a signed type;
• if 64bit type cannot accomodate the result, overflow happens;
• overflow is performed in implementation specific way that is 2-complement in all supported CPUs.

PS. SQL standard does not have unsigned integers at all.

[yohannj]

Hello Alexei,
Thank you, the reasons behind ClickHouse behavior is very clear.

Regarding avoiding Int / UInt -> Float. This behavior is still there on the latest LTS version (20.3.21.2).
As for promoting up to 64bit, since we got Int128 and Int256 in 20.10, is there any purpose in not using them here?

For the latest, if we keep the existing behavior (64bit), I think we should write this special consideration under https://clickhouse.tech/docs/en/sql-reference/data-types/int-uint/
I would be happy to create a PR for that purpose, to avoid other users misunderstanding with these new types.