PREFFERING on global outer join leads to internal server error
Details
| Detail name | Value |
|---|---|
| Changelog Number | 9323 |
| Type | Bug |
| Status | Resolved |
| Affected Versions | EXASOL 6.0.0 |
| Fix Versions | Exasol 6.0.18 |
| Resolution Date | 2023-03-28 |
Problem Description
A PREFERRING clause on an outer join may lead to an internal server error.
For this to happen
- the join has to be global (no matching distribution keys)
- and NULL rows have to be generated by the outer join (missing keys in inner table)
Example
SELECT * FROM big_t1 LEFT JOIN big_t2 ON big_t1.x=big_t2.x PREFERRING HIGH big_t2.y;
Workaround
Materialize the outer join using a subselect/CTE before applying PREFERRING.
