Informix Error -26525: OUTER join with in-join filter (extends NULLs)
Cause and resolution
OUTER join with in-join filter (extends NULLs)
The query was not accelerated because it contains an OUTER join with in-join filter. The ON clause of an ANSI OUTER join query must not contain other expressions than equality joins between columns (e.g. non-equality filters or table filter). The WHERE clause of an INFORMIX OUTER join query must not contain non-equality filters or table-level filters on subservient tables.
For example, the following queries cannot be accelerated. ANSI outer join queries : SELECT * FROM f left join d ON (f.col1 = d.col1 AND f.col2 > d.col2) SELECT * FROM f left join d ON (f.col1 = d.col1 AND f.col2 > 0) SELECT * FROM f left join d ON (f.col1 = d.col1 AND d.col2 > 0) Informix outer join queries : SELECT * FROM f outer d WHERE f.col1 = d.col1 AND f.col2 > d.col2 SELECT * FROM f outer d WHERE f.col1 = d.col1 AND d.col2 > 0
Oninit® Troubleshooting Guidance
Reasons / Common Causes
-26525 fires when a query isn't accelerated because an OUTER join has an in-join
filter — per the official guidance.
- An ANSI
OUTERjoin'sONclause has expressions other than equality joins between columns, per the official guidance — the first named case (e.g. a non-equality filter, or a table-level filter in theONclause). - An Informix
OUTERjoin'sWHEREclause has non-equality filters or table-level filters on subservient tables, per the official guidance — the second named case.
Solutions / Resolution
- No action needed to "fix" this — per the official guidance, such a query runs on the database server instead; restructuring the join to use only equality predicates between columns may help if acceleration is desired.
Examples
Queries that cannot be accelerated
SELECT * FROM f LEFT JOIN d ON (f.col1 = d.col1 AND f.col2 > d.col2);
SELECT * FROM f LEFT JOIN d ON (f.col1 = d.col1 AND f.col2 > 0);
SELECT * FROM f LEFT JOIN d ON (f.col1 = d.col1 AND d.col2 > 0);
SELECT * FROM f OUTER d WHERE f.col1 = d.col1 AND f.col2 > d.col2;
SELECT * FROM f OUTER d WHERE f.col1 = d.col1 AND d.col2 > 0;
Diagnostic Checks
- Check the outer join's
ON/WHEREclause for non-equality or table-level filters, and restructure if acceleration is desired.
Related Errors / Related Topics
- -26520 — "ON clause contains a non-equality join between columns" The general equality-join-only restriction this is a specific outer-join-filter instance of.
An outer join had an in-join filter (non-equality or table-level) — such queries run on the database server instead.