Wrong Results for Queries with GROUP BY, Primary Key and LIMIT/OFFSET

Detail name	Value
Changelog Number	6957
Type	Bug
Status	Resolved
Affected Versions
Fix Versions	Exasol 6.0.14
Resolution Date	2018-11-27

Problem
Some queries with the following properties might lead to erroneous results:

• The query contains an ORDER BY element that is a primary key in combination with LIMIT and OFFSET.
• The query has a GROUP BY clause that contains columns only and one is the primary key.
• There are at least three elements in the GROUP BY list that are on the same table as the primary key.
• The columns of expressions (excluding set functions) are all in the GROUP BY list.
• The expressions of the select list do not contain analytic functions.

The erroneous results might also occur if a query is transformed internally into such a query before its execution.

Example
For instance, the following query returns no results although the result set should contain two elements.

CREATE SCHEMA S;
CREATE OR REPLACE TABLE T (C1 INT, C2 INT, C3 INT);
ALTER TABLE T ADD CONSTRAINT PRIMARY KEY (C1);
INSERT INTO T VALUES (1,1,1),(2,2,2),(3,3,3),(4,4,4);

SELECT T.C1,T.C2,T.C3 FROM T GROUP BY T.C1,T.C2,T.C3 ORDER BY C1 LIMIT 2 OFFSET 2;

The following query is internally transformed to the query from the previous example. Therefore, this query is also affected by the problem.

SELECT DISTINCT * FROM T ORDER BY C1 LIMIT 2 OFFSET 2;

Workaround
The problem can be avoided by setting the following command-line parameter.

-disableBigGroupByOptimization=1