bug: With 100GB data, Sum() or Avg() leads StoneDB memory overflow.

[422351990]

Describe the problem

subquery subquery - aggregation overflow

problem Aggregation overflow occurs under 100g data volume run query 1 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1105 (HY000) at line 4 in file: '/data5/zsp/tpc-h/queries/query1. sql': The query includes syntax that is not supported by the storage engine. Stonedb: Aggregation overflow.

SQL statement -- using default substitutions select l_ returnflag, l_ linestatus, sum(l_quantity) as sum_ qty, sum(l_extendedprice) as sum_ base_ price, sum(l_extendedprice * (1 - l_discount)) as sum_ disc_ price, sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_ charge, avg(l_quantity) as avg_ qty, avg(l_extendedprice) as avg_ price, avg(l_discount) as avg_ disc, count(*) as count_ order from lineitem where -- l_ shipdate <= date '1998-12-01' - interval '90' day (3) l_ shipdate <= date '1998-12-01' - interval '90' day group by l_ returnflag, l_ linestatus order by l_ returnflag, l_ linestatus; -- limit -1;

Solution

1. Reduce the amount of aggregate data processed by a single instance;
2. Rewrite SQL statements;
3. Research MariaDB and other column storage engines to process this part of the source code of aggregation, and rewrite the operation logic of stonedb.

conclusion The amount of data is large, reaching the maximum value of the aggregation operation processed by a single instance, causing the aggregation overflow to be thrown.

Expected behavior

How To Reproduce

Environment

Additional context

[RingsC]

The error messages don't tell the reason which you mentioned above., and you should copye the original error message firstly. Or, Please, Gives the full steps which can lead to your conclusion- "Aggregation Overflow".

[RingsC]

@adofsauron What the root cause? Is it similar with #408? If yes, Do we fix this?

[adofsauron]

@adofsauron What the root cause? Is it similar with #408? If yes, Do we fix this?

ACK

[adofsauron]

Now aggregate operations are written to in-memory hashes, and if they exceed the capacity of RAM, they will OOM

[adofsauron]

To demand development: https://github.com/stoneatom/stonedb/issues/949

[adofsauron]

Close the current BUG list and use the required features as development, please check https://github.com/stoneatom/stonedb/issues/949