SSC-EWI-0108

The following subquery matches at least one of the patterns considered invalid and may produce compilation errors

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Severity

High

Description

In Snowflake, there are multiple patterns and elements in a subquery that are not supported and make it not executable. According to the Snowflake documentation on subqueries the following subquery types are supported:

• Uncorrelated scalar subqueries in any place that a value expression can be used.

• Correlated scalar subqueries in WHERE clauses.

• EXISTS, ANY / ALL, and IN subqueries in WHERE clauses. These subqueries can be correlated or uncorrelated.

Please note that the list above is not exhaustive, meaning that subqueries that match none of the specified types may still be considered valid.

To help avoid errors, SnowConvert knows a set of subquery patterns that normally invalidate subqueries, this EWI is added to warn the user that the subquery matches at least one of these patterns and therefore may produce errors when compiled in Snowflake.

Example Code

Input Code:

IN -> Oracle_01.sql

CREATE TABLE tableA
(
    col1 INTEGER,
    col2 VARCHAR(20)
);

CREATE TABLE tableB
(
    col3 INTEGER,
    col4 VARCHAR(20)
);

INSERT INTO tableA VALUES (50, 'Hey');

INSERT INTO tableB VALUES (50, 'Hey');
INSERT INTO tableB VALUES (50, 'Example');
INSERT INTO tableB VALUES (10, 'Bye');

-- Snowflake only allows the usage of FETCH in subqueries that are uncorrelated scalar, this subquery execution will fail
SELECT col2
FROM tableA
WHERE col2 = (SELECT col4 FROM tableB WHERE col3 = col1 FETCH FIRST ROW ONLY);

-- This subquery is uncorrelated scalar so FETCH is valid to use
SELECT col2
FROM tableA
WHERE col2 = (SELECT col4 FROM tableB FETCH FIRST ROW ONLY);

Output Code:

OUT -> Oracle_01.sql

CREATE OR REPLACE TABLE tableA
    (
        col1 INTEGER,
        col2 VARCHAR(20)
    )
    COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "12/05/2024",  "domain": "test" }}'
    ;

    CREATE OR REPLACE TABLE tableB
    (
        col3 INTEGER,
        col4 VARCHAR(20)
    )
    COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "12/05/2024",  "domain": "test" }}'
    ;

    INSERT INTO tableA
    VALUES (50, 'Hey');

    INSERT INTO tableB
    VALUES (50, 'Hey');

    INSERT INTO tableB
    VALUES (50, 'Example');

    INSERT INTO tableB
    VALUES (10, 'Bye');

    -- Snowflake only allows the usage of FETCH in subqueries that are uncorrelated scalar, this subquery execution will fail
SELECT col2
FROM
    tableA
    WHERE col2 =
                 --** SSC-FDM-0002 - CORRELATED SUBQUERIES MAY HAVE SOME FUNCTIONAL DIFFERENCES. **
                 !!!RESOLVE EWI!!! /*** SSC-EWI-0108 - THE FOLLOWING SUBQUERY MATCHES AT LEAST ONE OF THE PATTERNS CONSIDERED INVALID AND MAY PRODUCE COMPILATION ERRORS ***/!!! (SELECT
                         ANY_VALUE( col4) FROM
                         tableB
                     WHERE col3 = col1
                     FETCH FIRST 1 ROW ONLY);

    -- This subquery is uncorrelated scalar so FETCH is valid to use
SELECT col2
FROM
    tableA
    WHERE col2 = (SELECT col4 FROM
                         tableB
                     FETCH FIRST 1 ROW ONLY);

Recommendations

• Check the subquery in Snowflake, if it compiles without problems then this EWI can be safely ignored.

• Please check the complex patterns section for subqueries inside the assessment report, it contains a list of the patterns that normally invalidate subqueries and their occurrences, it can be used to review the migrated subqueries and why are they considered invalid.

• If you need more support, you can email us at [email protected]