Global Join with a local condition containing OR returns deleted rows

Detail name	Value
Changelog Number	7305
Type	Bug
Status	Resolved
Affected Versions	Exasol 6.1.0
Fix Versions	Exasol 6.1.1
Resolution Date	2019-01-18

Background

A global join with a local condition that contains an OR can return rows, that have already been deleted, if those rows have not been deleted physically yet.

Example
If the following join is global this will lead to the wrong results:

delete from u where id='2';
 SELECT *
    FROM t
    JOIN u ON t.id = u.id
    WHERE  t.id = '2'
    AND  (u.col1 = 123 or u.col2 ='ABC' );

Workaround

• Perform REORGANIZE TABLE ENFORCE in order to delete the deleted rows physically
• If possible use a local join (by partitioning properly)