Wrong results from explicit local join on table with correlated EXISTS
Details
| Detail name | Value |
|---|---|
| Changelog Number | 8188 |
| Type | Bug |
| Status | Resolved |
| Affected Versions | Exasol 6.1.2, Exasol 6.2.beta1 |
| Fix Versions | Exasol 6.2.0, Exasol 7.0.0, Exasol 6.1.4 |
| Resolution Date | 2019-06-21 |
Background
Correlated EXISTS as well as IN sub-select expressions on large tables are processed by means of an index in a join-like data pipeline. For technical reasons, such correlated exists prevent explicit local joins between properly distributed tables in rest of the pipeline.
Problem
There is a bug in the optimizer so that it may choose an explicit local join after a correlated exists leading to wrong results (too few rows).
The following preconditions are needed for the bug to occur
- database with more than one node
- properly distributed tables for an explicit local join
- one table of the join must be replicated (small table) the other not (big table)
- a correlated EXISTS or IN sub-select on the small table
Under above condition the optimizer might choose a join order having only small tables before the said explicit local join (replicated pipeline). In this case, the query will deliver wrong results.
Fix
The bug in the optimizer will be fixed.
Workarounds
To avoid the problem you can drop all distribution keys on tables below replication border or reduce the replication border properly (SOL-639).
