MSCCP0001

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

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Medium

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.

Code Example

Input Code:

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:

CREATE TABLE tableA
  (
  col1 INTEGER,
  col2 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 =
      /*** MSC-ERROR - MSC-CP0001 - 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 by Snowflake so its safe
        SELECT
  col2 FROM
  tableA
        WHERE col1 = (
      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 [email protected]