Comment on page
MSCEWI1054
Unsupported outer join subquery
Low
This error happens when a correlated subquery is found within an OR logical expression of an OUTER JOIN (Left, Right or Full). In those cases they could produce inconsistent results or cause the following error:
SQL compilation error: Unsupported subquery type cannot be evaluated.These limitations with subqueries are briefly mentioned in Snowflake documentation and some information about them can also be found in Snowflake forums.
SELECT a.Column1, b.Column2
FROM
Table1 a
LEFT JOIN Table2 b
ON (a.Column1 = b.Column1)
AND (a.Column2 = b.Column2 OR EXISTS(SELECT *
FROM Table3 c
WHERE c.Column1 = a.Column1));
SELECT
a.Column1,
b.Column2
FROM PUBLIC.Table1 a
LEFT JOIN PUBLIC.Table2 b
ON (a.Column1 = b.Column1)
AND (a.Column2 = b.Column2 OR EXISTS --** MSC-WARNING - MSCEWI1054 - CORRELATED SUBQUERIES WITHIN AN OR EXPRESSION OF AN OUTER JOIN COULD CAUSE COMPILATION ERRORS **
(SELECT
*
FROM PUBLIC.Table3 c
WHERE c.Column1 = a.Column1));
- Verify the output code does not produce a compilation error.
- Verify the output code's functional equivalence.