Index creation for geometry type, if used in an expression fails and causes 'Internal server error'.

Detail name	Value
Changelog Number	7934
Type	Bug
Status	Resolved
Affected Versions	Exasol 6.1.0
Fix Versions	Exasol 7.0.0, Exasol 6.1.4, Exasol 6.2.x
Resolution Date	2019-06-25

Background

Exasol automatically creates indices for equality join conditions, even when expressions are used for comparison.
Exasol 6.1 introduced indices on geospatial data types (see EXASOL-2169) for joins using geospatial functions like ST_CONTAINS or ST_INTERSECTS.

Problem Description

The combination of expression-indices and geospatial data types is not handled properly when resolvable views are involved.
Queries containing such a combination fail with an 'internal server error'.

Example:

create table t1(g1 geometry(4326));
create or replace view v1 as (
    select g1 as buffer from t1
);

-- query fails
select t1.g1
from v1 
    join t1
    on st_intersects(st_transform(v1.buffer,4326), t1.g1);

 

Workaround:

Enforce materialization of the view either explicitly or by adding some complexity (usually we recommend ORDER BY FALSE in such cases):

-- explicit
create table MAT_1 as ( select * from v1 );
select t1.g1
from MAT_1 as v1
    join t1
    on st_intersects(st_transform(v1.buffer,4326), t1.g1);

-- order by in query
select t1.g1
from (
    select * from v1 ORDER BY FALSE
) as v1
    join t1
    on st_intersects(st_transform(v1.buffer,4326), t1.g1);

-- order by in view
create or replace view v1 as (
    select g1 as buffer from t1
    ORDER BY FALSE
);
select t1.g1
from v1
    join t1
    on st_intersects(st_transform(v1.buffer,4326), t1.g1);