SSC-EWI-0108

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

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 a few 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
(
    col3 INTEGER,
    col4 VARCHAR(50)
);

create table tableB
(
    col3 INTEGER,
    col4 VARCHAR(50)
);

INSERT INTO tableA VALUES(59, 'Hello');
INSERT INTO tableA VALUES(200, 'Bye');

INSERT INTO tableB VALUES(200, 'Bye');
INSERT INTO tableB VALUES(500, 'Testing');

-- This subquery is considerd correlated non-scalar by Snowflake and will generate unsupported subquery error 
SELECT    col2     FROM     tableA WHERE col1 = (SELECT col3 FROM      tableB     WHERE col2 = col4);

-- This subquery is considered correlated scalar so its safe
SELECT col2 FROM tableA WHERE col1 = (SELECT AVG(col3) FROM tableB WHERE col2 = col4);

Output Code:

OUT -> Oracle_01.sql

CREATE OR REPLACE TABLE tableA
    (
        col3 INTEGER,
        col4 VARCHAR(50)
    )
    COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
    ;

    CREATE OR REPLACE TABLE tableB
    (
        col3 INTEGER,
        col4 VARCHAR(50)
    )
    COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
    ;

    INSERT INTO tableA
    VALUES(59, 'Hello');

    INSERT INTO tableA
    VALUES(200, 'Bye');

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

    INSERT INTO tableB
    VALUES(500, 'Testing');

    -- This subquery is considerd correlated non-scalar by Snowflake and will generate unsupported subquery error 
SELECT col2 FROM
    tableA
    WHERE col1 =
                 !!!RESOLVE EWI!!! /*** SSC-EWI-0108 - THE FOLLOWING SUBQUERY MATCHES AT LEAST ONE OF THE PATTERNS CONSIDERED INVALID AND MAY PRODUCE COMPILATION ERRORS ***/!!! (SELECT col3 FROM
                         tableB
                     WHERE col2 = col4);

    -- This subquery is considered correlated scalar so its safe
SELECT col2 FROM
    tableA
    WHERE col1 =
                 !!!RESOLVE EWI!!! /*** SSC-EWI-0108 - THE FOLLOWING SUBQUERY MATCHES AT LEAST ONE OF THE PATTERNS CONSIDERED INVALID AND MAY PRODUCE COMPILATION ERRORS ***/!!! (SELECT AVG(col3) FROM
                         tableB
                     WHERE col2 = col4);

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 [e-mail]