UNION ALL optimization does not work with UTF8 data

Detail name	Value
Changelog Number	10512
Type	Bug
Status	Resolved
Affected Versions	Exasol 6.2.0, Exasol 7.0.0
Fix Versions	Exasol 7.0.0, Exasol 6.2.9
Resolution Date	2020-07-31

Description

In order to create a sort of manual partitioning with improved performance, UNION ALL queries matching certain criteria can take advantage of this optimization. This is further described in SOL-452. This UNION ALL optimization tries to eliminate parts of the UNION ALL based on the filter that is being used. If the column has the datatype VARCHAR UTF8 and the column contains UTF8 data, the query will return the following error:

data exception - Invalid ASCII bytestream.

The below example reproduces this behavior:

create table t(x varchar(100) utf8);
create table u like t;
create view v as
select * from t
union all
select * from u;
insert into t values 0, '?', '⭕';
--> leads to error: data exception - Invalid ASCII bytestream.
select * from v where x = 'foo';

Workaround

As a workaround you can change the view so that the UNION ALL optimization is not possible by removing one of the needed criteria, for example adding expressions or conditions. One way of doing this is to use an expression in the select list:

create or replace view v as
select cast(x as varchar(100) utf8) x from t
union all
select x from u;

This will no longer use the optimization, and the query will not return an error. This may lead to a worse performance compared to using the optimization, however.

Fix

The above behavior will no longer lead to a data exception and will run as expected.