Native support for BigInt data type for SUM() and other aggregate functions

[HamudeHomsi]

Hello, SUM() and other aggr functions should also support BigInt data type beside Number. BigInt is part of JavaScript (since ES2020) and is increasingly important for handling large integers without loss of precision.

• Describe the problem. If the column in the table is stored as bigint then SUM() function should add them together. Possible solution: check the typeof of the first value and based on this, sum all values and return the result as the given type. Workaround: manually create an aggregate function:

import alasql from "alasql";

alasql.fn.SUM64 = function(value, accumulator, stage) {
    if (stage == 1) {
        var newAccumulator = value;
        return newAccumulator;
    } else if (stage == 2) {
        accumulator = accumulator + value;
        return accumulator;
    } else if (stage == 3) {
        return accumulator;
    }
};

• Provide data and code that replicates the problem. "node": "^18.x" "alasql": "^4.5.1"

import alasql from "alasql";

this.database = new alasql.Database();
this.database.exec("CREATE TABLE myTable (id INT PRIMARY KEY AUTOINCREMENT, name STRING, size BIGINT)");
this.database.exec("INSERT INTO myTable (name, size) VALUES (?, ?), ['first item name', 10n];
this.database.exec("INSERT INTO myTable (name, size) VALUES (?, ?), ['second item name', 214748364733443n];

Where this.database.exec("SELECT SUM(size) FROM myTable") => Error because it cannot add a bigint data type with a number data type.

• We suggest spawning a jsfiddle from https://jsfiddle.net/b5ajLveq/

[mathiasrw]

Great idea!

I replicated the problem in https://jsfiddle.net/yb4fteas/

It seems to be the sanitiser that is blocking for the use of BigInt

[KamilStefanco]

Hello, i would like to work on this issue.

[mathiasrw]

We are sanetising by converting to JSON and back. A solid regex that only runs on string would be much more efficient and solve this problem.

[KamilStefanco]

Thank you for assigning the issue. I made a test for this issue as instructed. Any tips on where and how to fix this?

[mathiasrw]

hm. Not super sure where it is. You can try the old fashion and insert console.log close to all the places we call JSON.stringify - or you can try to recreate the problem using the code from my previous JSfiddle as that will throw the actual error providing you with a line 

[Charlesnorris509]

hey @mathiasrw @KamilStefanco Did this issue got resolved if not I would like to participate finding the solution, I would love to be assigned with this issue

[KamilStefanco]

I already submitted a pull request for this issue in #1981