Filter on rowid can return deleted rows

Detail name	Value
Changelog Number	14225
Type	Bug
Status	Resolved
Affected Versions	Exasol 7.1.0
Fix Versions	Exasol 8.0.0, Exasol 7.1.7
Resolution Date	2022-03-16

Background

When a DELETE operation is executed, rows are logically marked as deleted. When the number of marked rows exceeds 25% of the total rows, rows are physically deleted.
You can see the percentage of rows marked as deleted in EXA_USER_TABLES in the column DELETE_PERCENTAGE.

Description

A query with a filter on rowid can return matching rows that were marked as deleted, but not yet cleaned up.

Example

create schema test;
create table t(id int, txt varchar(20));
insert into t values (1, null), (2, 'I will be back.'), (3, null), (4, null), (5, null);

delete from t where id=2;

-- This returns the second row as well, even though it was deleted
select * from t where rowid != 1;

Workaround

Calling REORGANIZE TABLE ... ENFORCE on a table cleans up all marked rows, but this statement can be expensive.

Fix

Rows that are logically marked as deleted are not returned.