Wrong results due to GROUP BY with odd group key sizes

Detail name	Value
Changelog Number	8804
Type	Bug
Status	Resolved
Affected Versions	Exasol 6.2.0, Exasol 6.2.1
Fix Versions	Exasol 6.2.2
Resolution Date	2019-10-07

Problem

If a query uses a group key that is of size 27, 29, 31, 35, 37, or 39 bytes, it is possible that Exasol does not return the correct results.
The size of the group key is the sum of all column sizes in the GROUP BY clause (e.g., decimal(18,0) is 8 bytes, decimal(6, 0) is 3 bytes).
Whether the error actually occurs depends on the internal hash values of the group keys and the data distribution.

Example:

CREATE SCHEMA test;
CREATE OR REPLACE TABLE ten(x int);
INSERT INTO ten VALUES 0,1,2,3,4,5,6,7,8,9;

CREATE OR REPLACE TABLE tmp(x int);
INSERT INTO tmp
    SELECT f.x * 100000 + e.x * 10000 + d.x * 1000 + c.x * 100 + b.x * 10 + a.x 
    FROM ten a, ten b, ten c, ten d, ten e, ten f;

CREATE OR REPLACE TABLE t1(col1 decimal(18,0), col2 decimal(18,0), col3 decimal(18,0), col4 DECIMAL(6,0));

INSERT INTO t1 SELECT tmp.x, tmp.x, tmp.x, CASE WHEN tmp.x > 99999 THEN 1 ELSE tmp.x END FROM tmp;
INSERT INTO t1 SELECT tmp.x, tmp.x, tmp.x, CASE WHEN tmp.x > 99999 THEN 1 ELSE tmp.x END FROM tmp;

-- Expected result: 1000000
SELECT COUNT(*) FROM (SELECT col1 FROM t1 GROUP BY col1, col2, col3, col4);

Workaround

Cast the decimal(6,0) column to decimal(18,0) to create a group key with an even size (32 bytes in the example).
Example:

SELECT COUNT(*) FROM (SELECT col1 FROM t1 GROUP BY col1, col2, col3, CAST(col4 AS decimal(18,0)));