FOUND could have a different behavior in Snowflake in some scenarios.
Severity
Low
Description
The FOUND property in PostgreSQL is a property based on the last executed query, it can be affected by some statements such as INSERT, UPDATE, DELETE, MERGE, SELECT INTO, PERFORM, FETCH and FOR loops. To read more details about this property, this is PostgreSQL documentation.
In Snowflake there is not a direct translation for this property, for the following scenarios:
INSERT
UPDATE
DELETE
MERGE
The converted code will be SQLFOUND Snowflake property (Here is the documentation) since it behaves like the PostgreSQL FOUND property.
For the other cases such as:
SELECT INTO
PERFORM
FETCH
The converted code will be a custom UDF (IS_FOUND_UDF) that behaves like the PostgreSQL FOUND property.
This happens because SQLFOUND changes its value only when at least one row is affected by the last executed query, if the last query does not change any row, it does not change.
While the IS_FOUND_UDF only works for statements that returns rows, if no row is returned it, it will return FALSE.
SQLFOUND Example
INSERT INTO SampleTable (SampleColumn1)
VALUES ('SampleValue0.1');
The last query affects a table, so the SQLFOUND is the closest to the PostgreSQL functionality.
IS_FOUND_UDF Example
SELECT SampleColumn FROM SampleTable;
The last query will return a row but does not change anything, so the IS_FOUND_UDF() is the closest to the PostgreSQL functionality.
IS_FOUND_UDF Source Code
CREATE OR REPLACE FUNCTION FOUND_UDF()
RETURNS BOOLEAN
LANGUAGE SQL
IMMUTABLE
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "udf", "convertedOn": "09/09/2024" }}'
AS
$$
SELECT (count(*) != 0) FROM TABLE(result_scan(last_query_id()))
$$;
Code Example
Insert Statement:
IN -> Greenplum_01.sql
-- Found property used with INSERT statement.
CREATE OR REPLACE FUNCTION FoundUsingInsertFunction()
RETURNS BOOLEAN
AS
$$
BEGIN
INSERT INTO SampleTable (SampleColumn1)
VALUES ('SampleValue0.1');
RETURN FOUND;
END;
$$
LANGUAGE plpgsql;
OUT -> Greenplum_01.sql
-- Found property used with INSERT statement.
!!!RESOLVE EWI!!! /*** SSC-EWI-GP0003 - RETURNING FUNCTION TRANSLATED TO STORED PROCEDURE ***/!!!
CREATE OR REPLACE PROCEDURE FoundUsingInsertFunction ()
RETURNS BOOLEAN
LANGUAGE SQL
AS
$$
BEGIN
INSERT INTO SampleTable (SampleColumn1)
VALUES ('SampleValue0.1');
RETURN SQLFOUND /*** SSC-FDM-PG0001 - FOUND COULD HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE IN SOME SCENARIOS. ***/;
END;
$$;
Update Statement:
IN -> Greenplum_02.sql
-- Found property used with UPDATE statement.
CREATE OR REPLACE FUNCTION FoundUsingUpdateFunction()
RETURNS BOOLEAN
AS
$$
BEGIN
UPDATE SampleTable
SET SampleColumn1 = 'SampleValue0.1'
WHERE SampleColumn1 = 'SampleValue0.1';
RETURN FOUND;
END;
$$
LANGUAGE plpgsql;
OUT -> Greenplum_02.sql
-- Found property used with UPDATE statement.
!!!RESOLVE EWI!!! /*** SSC-EWI-GP0003 - RETURNING FUNCTION TRANSLATED TO STORED PROCEDURE ***/!!!
CREATE OR REPLACE PROCEDURE FoundUsingUpdateFunction ()
RETURNS BOOLEAN
LANGUAGE SQL
AS
$$
BEGIN
UPDATE SampleTable
SET SampleColumn1 = 'SampleValue0.1'
WHERE SampleColumn1 = 'SampleValue0.1';
RETURN SQLFOUND /*** SSC-FDM-PG0001 - FOUND COULD HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE IN SOME SCENARIOS. ***/;
END;
$$;
Delete Statement:
IN -> Greenplum_03.sql
-- Found property used with DELETE statement.
CREATE OR REPLACE FUNCTION FoundUsingDeleteFunction()
RETURNS BOOLEAN
AS
$$
BEGIN
DELETE FROM SampleTable
WHERE SampleColumn1 = 'SampleValue0.1';
RETURN FOUND;
END;
$$
LANGUAGE plpgsql;
OUT -> Greenplum_03.sql
-- Found property used with DELETE statement.
!!!RESOLVE EWI!!! /*** SSC-EWI-GP0003 - RETURNING FUNCTION TRANSLATED TO STORED PROCEDURE ***/!!!
CREATE OR REPLACE PROCEDURE FoundUsingDeleteFunction ()
RETURNS BOOLEAN
LANGUAGE SQL
AS
$$
BEGIN
DELETE FROM
SampleTable
WHERE SampleColumn1 = 'SampleValue0.1';
RETURN SQLFOUND /*** SSC-FDM-PG0001 - FOUND COULD HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE IN SOME SCENARIOS. ***/;
END;
$$;
Merge Statement:
IN -> Greenplum_04.sql
-- Found property used with MERGE statement.
CREATE OR REPLACE FUNCTION FoundUsingMergeFunction()
RETURNS BOOLEAN
AS
$$
BEGIN
MERGE INTO SampleTableB B
USING (SELECT * FROM SampleTableA) A
ON B.SampleColumn1 = A.SampleColumn2
WHEN MATCHED THEN DELETE;
RETURN FOUND;
END;
$$
LANGUAGE plpgsql;
OUT -> Greenplum_04.sql
-- Found property used with MERGE statement.
!!!RESOLVE EWI!!! /*** SSC-EWI-GP0003 - RETURNING FUNCTION TRANSLATED TO STORED PROCEDURE ***/!!!
CREATE OR REPLACE PROCEDURE FoundUsingMergeFunction ()
RETURNS BOOLEAN
LANGUAGE SQL
AS
$$
BEGIN
MERGE INTO SampleTableB B
USING (SELECT * FROM SampleTableA) A
ON B.SampleColumn1 = A.SampleColumn2
WHEN MATCHED THEN DELETE !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'MergeStatement' NODE ***/!!!;
RETURN SQLFOUND /*** SSC-FDM-PG0001 - FOUND COULD HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE IN SOME SCENARIOS. ***/;
END;
$$;
Select Into Statement
IN -> Greenplum_05.sql
-- Found property used with SELECT INTO statement.
CREATE OR REPLACE FUNCTION FoundUsingSelectIntoFunction()
RETURNS BOOLEAN
AS
$$
DECLARE
SampleNumber INTEGER;
BEGIN
SELECT 1 INTO SampleNumber;
RETURN FOUND;
END;
$$
LANGUAGE plpgsql;
OUT -> Greenplum_05.sql
-- Found property used with SELECT INTO statement.
!!!RESOLVE EWI!!! /*** SSC-EWI-GP0003 - RETURNING FUNCTION TRANSLATED TO STORED PROCEDURE ***/!!!
CREATE OR REPLACE PROCEDURE FoundUsingSelectIntoFunction ()
RETURNS BOOLEAN
LANGUAGE SQL
AS
$$
DECLARE
SampleNumber INTEGER;
BEGIN
SELECT 1 INTO
: SampleNumber;
RETURN FOUND_UDF() /*** SSC-FDM-PG0001 - FOUND COULD HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE IN SOME SCENARIOS. ***/;
END;
$$;
Perform Statement:
IN -> Greenplum_06.sql
-- Found property used with PERFORM statement.
CREATE OR REPLACE FUNCTION FoundUsingPerformFunction()
RETURNS BOOLEAN
AS
$$
BEGIN
PERFORM 1;
RETURN FOUND;
END;
$$
LANGUAGE plpgsql;
OUT -> Greenplum_06.sql
-- Found property used with PERFORM statement.
!!!RESOLVE EWI!!! /*** SSC-EWI-GP0003 - RETURNING FUNCTION TRANSLATED TO STORED PROCEDURE ***/!!!
CREATE OR REPLACE PROCEDURE FoundUsingPerformFunction ()
RETURNS BOOLEAN
LANGUAGE SQL
AS
$$
BEGIN
SELECT
1;
RETURN FOUND_UDF() /*** SSC-FDM-PG0001 - FOUND COULD HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE IN SOME SCENARIOS. ***/;
END;
$$;
Fetch Statement:
IN -> Greenplum_07.sql
-- Found property used with FETCH statement.
CREATE OR REPLACE FUNCTION FoundUsingFetchFunction ()
RETURNS BOOLEAN
AS
$$
DECLARE
SampleRow VARCHAR;
SampleCursor CURSOR FOR SELECT EmptyColumn FROM EmptyTable;
BEGIN
OPEN SampleCursor;
FETCH SampleCursor;
CLOSE SampleCursor;
RETURN FOUND;
END;
$$
LANGUAGE plpgsql;
OUT -> Greenplum_07.sql
-- Found property used with FETCH statement.
!!!RESOLVE EWI!!! /*** SSC-EWI-GP0003 - RETURNING FUNCTION TRANSLATED TO STORED PROCEDURE ***/!!!
CREATE OR REPLACE PROCEDURE FoundUsingFetchFunction ()
RETURNS BOOLEAN
LANGUAGE SQL
AS
$$
DECLARE
SampleRow VARCHAR;
SampleCursor CURSOR FOR SELECT EmptyColumn FROM
EmptyTable;
BEGIN
OPEN SampleCursor !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'CursorOpenBound' NODE ***/!!!;
FETCH SampleCursor;
CLOSE SampleCursor;
RETURN FOUND_UDF() /*** SSC-FDM-PG0001 - FOUND COULD HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE IN SOME SCENARIOS. ***/;
END;
$$;
