Wrong Results for Subquery Filter on Some System Table Columns

Detail name	Value
Changelog Number	7660
Type	Bug
Status	Resolved
Affected Versions	EXASOL 6.0.0, Exasol 6.1.0
Fix Versions	Exasol 6.0.15, Exasol 6.1.3
Resolution Date	2019-03-19

Background

If a query contains a filter that combines a single system table column and a subquery, it is possible that the query returns an empty result set. This happens although there is data in the system table that fulfill the filter condition. Note that this only happens for some system table columns and not for all. For example, column "created" in EXA_ALL_OBJECT_SIZES causes this problem.

Example:

CREATE SCHEMA test;
CREATE OR REPLACE TABLE t1 (col1 timestamp);
INSERT INTO t1 VALUES(TO_TIMESTAMP('1900', 'IYYY'));

-- Expected result: At least 1 row
SELECT * FROM EXA_ALL_OBJECT_SIZES  where created > (SELECT TO_TIMESTAMP('1900', 'IYYY') from dual);

-- Expected result: At least 1 row
WITH tmp AS (SELECT * FROM t1) SELECT * FROM EXA_ALL_OBJECT_SIZES WHERE created > (SELECT col1 FROM tmp);

Workaround

Adding a filter with OR that uses a second column of the same system table and always evaluates to FALSE avoids this problem.

Example:

-- Expected result: At least 1 row
SELECT * FROM EXA_ALL_OBJECT_SIZES  where created > (SELECT TO_TIMESTAMP('1900', 'IYYY') from dual) OR OWNER IS NULL;

-- Expected result: At least 1 row
WITH tmp AS (SELECT * FROM t1) SELECT * FROM EXA_ALL_OBJECT_SIZES WHERE created > (SELECT col1 FROM tmp) OR OWNER IS NULL;