ROWID caclulation is not correct if rows are prefetched from another node

Detail name	Value
Changelog Number	4856
Type	Bug
Status	Resolved
Affected Versions	EXASOL 6.0.0, EXASolution 5.0.0
Fix Versions	EXASOL 6.0.3
Resolution Date	2017-10-27

Problem

ROWID caclulation is not correct, if rows are prefetched from another node. Prefetch is an internal mechanism how column data is exchanged between cluster nodes. This is a case for example by global join. Which table is prefetched depends on join order. ROWID calculation on such prefetched table can lead to unexpected ROWIDs.

Workaround

Materialization of the join is a way to prevent the problem. This can be acomplish with ORDER BY FALSE expression.
Example:

WITH
    V AS (
            SELECT
                    A.ROWID AS V_ROWID
            FROM
                            TABLE1 B
                    INNER JOIN
                            TABLE2 A
                    ON
                            (
                                    B.ID = A.ID
                            ) 
    )
SELECT
        INVALID UNIQUE(V_ROWID) 
FROM
        V;

Enforced materialization with ORDER BY FALSE:

WITH
    V AS (
            SELECT
                    A.ROWID AS V_ROWID
            FROM
                            TABLE1 B
                    INNER JOIN
                            TABLE2 A
                    ON
                            (
                                    B.ID = A.ID
                            )
            ORDER BY FALSE 
    )
SELECT
        INVALID UNIQUE(V_ROWID) 
FROM
        V;