Queries with LIMIT return too few results in very specific circumstances

Detail name	Value
Changelog Number	11043
Type	Bug
Status	Resolved
Affected Versions	Exasol 6.1.0, Exasol 6.2.0, Exasol 7.0.0
Fix Versions	Exasol 7.0.12, Exasol 7.1.1
Resolution Date	2021-08-31

Description

A query will return too few rows in the resultset in some cases when a result from query-cache is re-used with a different LIMIT clause. This only happens in very specific circumstances, including:

• The query must contain a LIMIT clause
• The query must contain an ORDER BY clause
• The query contains a GROUP BY clause (or DISTINCT in select-list)
• GROUP BY clause (or DISTINCT) contains at least 4 columns from a single table
• One of the columns from the GROUP BY-clause (or DISTINCT) is unique and has an index on it
• Query Cache is activated
• An identical query has been executed previously with a smaller LIMIT clause (and stored in query cache)

In those cases, the query cache result that is already limited by the LIMIT from the first execution will be returned. So if the LIMIT (or OFFSET) exceeds the range of the first LIMIT, the resultset will not include all rows.

Example

-- Preparation
drop schema if exists test cascade;
create schema test;
create table t(pk int primary key, col1 int, col2 int, col3 int);
insert into t values (1,1,1,1),(2,2,1,1),(3,1,2,1),(4,2,2,1),(5,1,3,1);

-- ok - first execution
select distinct pk, col1, col2, col3 from t order by pk limit 2;
-- wrong (2 rows only)
select distinct pk, col1, col2, col3 from t order by pk limit 4;
-- ok (since narrowing down, 1 row)
select distinct pk, col1, col2, col3 from t order by pk limit 1;
-- wrong (0 rows, because range starts at row 3 where the resul)
select distinct pk, col1, col2, col3 from t order by pk limit 1 offset 2;

Workaround

Generally, two options for workarounds:
1. Disable the query cache for the session

ALTER SESSION SET QUERY_CACHE='OFF';
-- correct result - not taken from cache
select distinct pk, col1, col2, col3 from t order by pk limit 4;
ALTER SESSION SET QUERY_CACHE='ON';

2. Rewrite the query such that the specific conditions for this bug are no longer given. Easiest way is to put a select around the query (which causes the query to no longer be able to reuse the resultset for a different ORDER BY LIMIT-combination):

select * from (select distinct pk, col1, col2, col3 from t order by pk limit 4);

Fix

The query returns the correct results on each execution - independent of the query cache.