Bigquery subtraction on very large number not working as expected-CodePudding

Why is this piece of SQL not giving the expected answer when subtracting.

select '2^63 - 1', cast(power(2, 63) - 1 as bignumeric) union all
select '2^63', cast(power(2, 63) as bignumeric) union all
select '2^63 with diff of 10k', cast(power(2, 63) as bignumeric) - cast(power(2, 63) - 10000 as bignumeric) union all
select '2^64 - 1', cast(power(2, 64) - 1 as bignumeric) union all
select '2^64', cast(power(2, 64) as bignumeric) union all
select '2^64 - 10000', cast(power(2, 64) - 10000 as bignumeric) union all
select '2^64 with diff of 10k', cast(power(2, 64) as bignumeric) - cast(power(2, 64) - 10000 as bignumeric)

It gives the following result. What am I missing here?

CodePudding user response：

POW function has its own rules for the return types:

https://cloud.google.com/bigquery/docs/reference/standard-sql/mathematical_functions#pow

You need to take these rules into account to perform precise calculations.

Let's take your first operation and inspect the return type. You can do it by creating a view, then you can inspect the column datatype.

First, let's take the power operation only:

create or replace view `qwiklabs-gcp-03-0ffb72b5a6c1.data.testview`
as select power(2, 63) as one

The return datatype is FLOAT

Second, let's add the substraction operation:

create or replace view `qwiklabs-gcp-03-0ffb72b5a6c1.data.testview`
as select power(2, 63) - 1 as one

The return type is still FLOAT.

Let's add the CAST operation now:

create or replace view `qwiklabs-gcp-03-0ffb72b5a6c1.data.testview`
as select cast(power(2, 63) - 1 as bignumeric) as one

The column datatype is BIGNUMERIC, as expected. Casting from FLOAT.

So, in order to see the right results you need to check the return types.

Let's fix that first operation following the docs:

create or replace view `qwiklabs-gcp-03-0ffb72b5a6c1.data.testview`
as select power(cast(2 as bignumeric), cast(63 as bignumeric)) - 1 as one

This way, we are providing two BIGNUMERIC and we should expect a BIGNUMERIC, that can be decrease by one.

Now, if you execute the SELECT query, the response is OK:

select * from `qwiklabs-gcp-03-0ffb72b5a6c1.data.testview`

Result: 9223372036854775807

So, please, take into account the return types and set them properly to do all the calculations.