EXISTS on replicated table may return wrong results

Detail name	Value
Changelog Number	13421
Type	Bug
Status	Resolved
Affected Versions	Exasol 7.0.0, Exasol 7.1.0
Fix Versions	Exasol 7.1.5, Exasol 7.0.15
Resolution Date	2022-01-26

Background

Sometimes a replicated table T_REP is selected as the SCAN table by the join optimizer. In that case each database node operates on the complete (small) table. As a result an INNER JOIN with a non-replicated table T can be executed locally by just joining the local part of that table. The result of that local join is distributed exactly like the joined non-replicated table T. The optimizer uses this property to allow further local joins in cases where T is distributed by the columns used in another join. See https://docs.exasol.com/performance/best_practices.htm#Distribu for more details on distribution keys and local distributed joins.

Description

A query can produce wrong results if the following conditions are met:

1)

• a replicated table T_REP is selected as SCAN-table
• AND there is an EXISTS condition filtering only on columns of T_REP
• AND T_REP is joined to a distributed table T_DIST

AND

2) the execution of the query makes use of the distribution keys of T_DIST in the following ways:

• another table T_DIST2 is joined on the distribution keys
• OR there is an EXISTS with another distributed table T_DIST2 on the distribution keys
• OR there is a GROUP BY only on the distribution keys of T_DIST

In these cases the operations from 2) might return wrong results because they are executed locally.

The preconditions from 1) can be checked via PROFILING (see https://docs.exasol.com/database_concepts/profiling.htm).
EXA_USER_PROFILE_LAST_DAY will show something like this:

PART_NAME	PART_INFO	OBJECT_SCHEMA	OBJECT_NAME
SCAN	on REPLICATED table	TEST	T_REP
EXISTS	GLOBAL	TEST	T_EXISTS
JOIN	GLOBAL	TEST	T_DIST

Example

Preparation

drop schema if exists test cascade;
create schema test;
create table t_rep(i int);
insert into t_rep values 1,1;
create table t_exists(x int);
insert into t_exists values 42;
create table t_dist(i int, j int, distribute by j);
insert into t_dist values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10),(11,11);
create table t_dist2(j int, distribute by j);
insert into t_dist2 values 1,2,3,4,5,6,7,8,9,10,11;

Testcase
The following queries might have wrong results depending on the node count of the system and selected join order.

-- GROUP BY distribution key of T_DIST
select t_dist.j
from t_rep 
join t_dist on t_rep.i=t_dist.i 
where not exists (select 1 from t_exists where t_rep.i=t_exists.x) 
group by t_dist.j;

-- EXISTS on distribution key of T_DIST
select t_rep.i 
from t_rep 
join t_dist on t_rep.i=t_dist.i 
where not exists (select 1 from t_exists where t_rep.i=t_exists.x) 
and exists (select 1 from t_dist2 where t_dist2.j=t_dist.j);

-- JOIN on distribution key of T_DIST
select t_rep.i 
from t_rep 
join t_dist on t_rep.i=t_dist.i 
join t_dist2 on t_dist.j=t_dist2.j 
where not exists (select 1 from t_exists where t_rep.i=t_exists.x);

 

Workaround

Enforce materialization of the distributed table using ORDER BY FALSE

select t_dist.j
from t_rep
join (select * from t_dist order by false) as t_dist on t_rep.i=t_dist.i 
where not exists (select 1 from t_exists where t_rep.i=t_exists.x) 
and t_dist.i is not null
group by t_dist.j;

Fix

The queries return the correct result.