DuckDB 中大整数类型的使用

以前认为DuckDB不支持其他数据库如postgresql中的任意长度数字类型，需要自己实现。
其实DuckDB从 1.1版开始，支持这种类型，起初称为VARINT类型，支持最大约126万位十进制数字，从1.4版起改为BIGNUM类型，支持最大约2000万位。

1.1-1.3文档中的描述

Variable Integer
The previously mentioned integer types all have in common that the numbers in the minimum and maximum range all have the same storage size, UTINYINT is 1 byte, SMALLINT is 2 bytes, etc. But sometimes you need numbers that are even bigger than what is supported by a HUGEINT! For these situations the VARINT type can come in handy, as the VARINT type has a much bigger limit (the value can consist of up to 1,262,612 digits). The minimum storage size for a VARINT is 4 bytes, every digit takes up an extra bit, rounded up to 8 (12 digits take 12 bits, rounded up to 16, becomes two extra bytes).

Both negative and positive values are supported by the VARINT type.

1.4文档中的描述改为

Variable-Length Integers
The previously mentioned integer types all have in common that the numbers in the minimum and maximum range all have the same storage size, UTINYINT is 1 byte, SMALLINT is 2 bytes, etc. But sometimes you need numbers that are even bigger than what is supported by a HUGEINT! In these situations, you can use the BIGNUM type, which stores positive numbers in a similar fashion as other integer types, but uses three additional bytes to store the required size and a sign bit. A number with N decimal digits requires approximately 0.415 * N + 3 bytes when stored in a BIGNUM.

Unlike variable-length integer implementations in other systems, there are limits to BIGNUM: the maximal and minimal representable values are approximately ±4.27e20201778. Those are numbers with 20,201,779 decimal digits and storing a single such number requires 8 megabytes.

下面看这个类型支持哪些操作。

把字面量转化为大整数, 注意整数字面量超过hugeint就转成double了，丢失了精度，字符串字面量则能完整保留。

D select 12343545::bignum;
┌──────────────────────────┐
│ CAST(12343545 AS BIGNUM) │
│          bignum          │
├──────────────────────────┤
│ 12343545                 │
└──────────────────────────┘
D select 123435457879797543::bignum;
┌────────────────────────────────────┐
│ CAST(123435457879797543 AS BIGNUM) │
│               bignum               │
├────────────────────────────────────┤
│ 123435457879797543                 │
└────────────────────────────────────┘
D select 1234354578797975899999999999999943::bignum;
┌────────────────────────────────────────────────────┐
│ CAST(1234354578797975899999999999999943 AS BIGNUM) │
│                       bignum                       │
├────────────────────────────────────────────────────┤
│ 1234354578797975899999999999999943                 │
└────────────────────────────────────────────────────┘
D select 123435457879797589999997878685746333333333333399999999943::bignum;
┌───────────────────────────────────────────────────────────┐
│           CAST(1.234354578797976e+56 AS BIGNUM)           │
│                          bignum                           │
├───────────────────────────────────────────────────────────┤
│ 123435457879797594386601086096523660755866383744810090496 │
└───────────────────────────────────────────────────────────┘
                              ^
D select '123435457879797589999997878685746333333333333399999999943'::bignum;
┌─────────────────────────────────────────────────────────────────────────────┐
│ CAST('123435457879797589999997878685746333333333333399999999943' AS BIGNUM) │
│                                   bignum                                    │
├─────────────────────────────────────────────────────────────────────────────┤
│ 123435457879797589999997878685746333333333333399999999943                   │
└─────────────────────────────────────────────────────────────────────────────┘

用它来计算大数阶乘，报错了，无论是VARINT或bignum。说明它不支持大数乘法，而是转化为浮点数计算，范围大大缩小，除法也是按浮点数计算。

with recursive t as (select 1::VARINT lv, 1::VARINT a
union all
select 1::VARINT+lv, a*(lv+1::VARINT) from t where lv<1000)
select max(a) from t;

Conversion Error:
LINE 3: select 1::VARINT+lv, a*(lv+1::VARINT) from t where lv<1000)
                              ^

with recursive t as (select 1 lv, 1::bignum a
union all
select lv+1, a*(lv+1)::bignum from t where lv<1000)
select max(a) from t;

Conversion Error:
Type DOUBLE with value inf can't be cast to the destination type VARCHAR

LINE 3: select lv+1, a*(lv+1)::bignum from t where lv<1000)
                      ^


D select '123435457879797589999997878685746333333333333399999999943'::bignum//1234;
┌───────────────────────────────────────────────────────────────────────────────────────┐
│ (CAST('123435457879797589999997878685746333333333333399999999943' AS BIGNUM) // 1234) │
│                                        double                                         │
├───────────────────────────────────────────────────────────────────────────────────────┤
│                                 1.000287341003222e+53                                 │
└───────────────────────────────────────────────────────────────────────────────────────┘

用来计算2^n则可以，说明它支持加法运算

with recursive t as (select 1 lv, 1::bignum a
union all
select lv+1, a+a from t where lv<1000)
select max(a) from t;

┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                        max(a)                                                        │
│                                                        bignum                                                        │
├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ 535754303593133660474212524530000905280702405852766803721875194185175525562468061246599189407847929063797336458776…  │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Run Time (s): real 0.199 user 0.158731 sys 0.169856

位运算是不支持的

D select 1::bignum<<1000;
Run Time (s): real 0.009 user 0.000000 sys 0.001912
Binder Error:
No function matches the given name and argument types '<<(BIGNUM, INTEGER_LITERAL)'.

综上所述，DuckDB的大整数类型支持少数的运算，用途有限。