SSC-FDM-BQ0008

Where clause references a column of STRUCT type.

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.

Description

If an Snowflake Object has a key value format, when a comparison is made with another Object, Snowflake compares both key and value. On the other hand, when BigQuery compares between Structs, it compares only values, regardless if the Struct has keys or not.

This difference between comparisons may cause that the results are not the same in SnowConvert and BigQuery for the same comparison. This FDM is added when a comparison is made on a Snowflake Object created from a BigQuery Struct.

Code Example

Input:

BigQuery

IN -> BigQuery_01.sql

CREATE OR REPLACE TABLE test.compExprTable
(
  COL1 STRUCT<sc1 INT64>,
  COL2 STRUCT<sc2 INT64>  
);

SELECT * FROM test.compExprTable WHERE COL1 <> (COL2);

Output:

Snowflake

OUT -> BigQuery_01.sql

CREATE OR REPLACE TABLE test.compExprTable
(
  COL1 VARIANT /*** SSC-FDM-0034 - STRUCT<INT64> CONVERTED TO VARIANT. SOME OF ITS USAGES MIGHT HAVE FUNCTIONAL DIFFERENCES. ***/,
  COL2 VARIANT /*** SSC-FDM-0034 - STRUCT<INT64> CONVERTED TO VARIANT. SOME OF ITS USAGES MIGHT HAVE FUNCTIONAL DIFFERENCES. ***/
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "bigquery",  "convertedOn": "07/02/2025",  "domain": "no-domain-provided" }}';

SELECT * FROM
  test.compExprTable
--** SSC-FDM-BQ0008 - WHERE CLAUSE REFERENCES A COLUMN OF STRUCT TYPE. COMPARISON OPERATIONS MAY PRODUCE DIFFERENT RESULTS IN SNOWFLAKE. **
WHERE COL1 <> (COL2);

Recommendations

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