Internal server error when using LIMIT in combination with data exceptions in select-list expressions

Detail name	Value
Changelog Number	5682
Type	Bug
Status	Resolved
Affected Versions	EXASOL 6.0.0, EXASolution 5.0.12
Fix Versions	Exasol 6.1.0, EXASolution 5.0.22, EXASOL 6.0.9
Resolution Date	2018-06-05

Problem

In some cases a query containing a LIMIT clause will not behave correctly when a data exception occurs during computation of an expression from the select list of the query. Instead of showing the data exception, the query might lead to an internal server error or - in rare cases - even to wrong results.

This only happens if:

• The query contains a SELECT with a LIMIT clause
• In the select-list of the SELECT with the LIMIT clause there is an expression that causes a data exception for some rows, but not for all.

Example

The following query will sometimes show a data exception (since '2018-02-29' is no valid date), sometimes an internal server error and other times a lot of 'SUNDAY'-lines. What happens depends on internal computation order of the table rows. 'SUNDAY' as well as a 'data exception' are correct results, the internal server error is not.

drop schema test cascade;
create schema test;
create table t1 ( id int, vc varchar(10));
create table ten (i int);
insert into ten values 0,1,2,3,4,5,6,7,8,9;
insert into t1 select rn, '2012-01-01' from (select row_number() over (order by i) rn from (select 1 as i from ten,ten,ten,ten,ten,ten));
update t1 set vc = '2018-02-29' where id<1234;

-- might cause internal server error
select weekday from
(
select to_char(cast(vc as date),'DAY') weekday
from t1 limit 100
);

In some cases where the result of the SELECT containing the LIMIT is directly seen by a client program, some result rows/columns might appear to be empty. This would be a wrong result.