Joins with geometry predicates on varchar datatypes may cause unexpected result.

Detail name	Value
Changelog Number	10622
Type	Bug
Status	Resolved
Affected Versions	Exasol 6.2.8, Exasol 7.0.rc2
Fix Versions	Exasol 7.0.4, Exasol 6.2.12
Resolution Date	2020-11-20

Description

If a query contains the following conditions, the query may return unexpected (usually empty) results:

• The query contains a JOIN
• The joined columns are of data type VARCHAR
• The join condition contains a geometry predicate (e.g ST_CONTAINS)

Example 

-- Preparation
create table t1 (c1 varchar(2000));
create table t2 (c2 varchar(2000));

-- insert some geometry data into the tables

select * from t1  join t2 on ST_CONTAINS(t1.c1,t2.c2); 
-- Returns empty resultset

Workaround:

Change the datatypes to GEOMETRY type instead of VARCHAR. This will also perform better.

alter table t1 modify column c1 geometry;
alter table t2 modify column c2 geometry;

If changing the datatype is not possible, you can re-write the query to put the join conditions into a WHERE clause, or to cast the columns to a GEOMETRY type inside the predicate. (Note: Both options will have a negative performance impact):

--rewrite join condition
select * from t1, t2 where ST_CONTAINS(1.c1 ,t2.c2 );

--rewrite query with explicit cast
select * from t1 join t2 on ST_CONTAINS(cast(t1.c1 as geometry),cast(t2.c2 as geometry));

Fix

The query will return the expected number of results.