Getting "Unsupported subquery type cannot be evaluated" when using UPPER in Snowflake IN Subquery

Question

I have a Snowflake table as following:

create table field_names_to_retrieve("QualifiedApiName" varchar(80));
INSERT INTO field_names_to_retrieve VALUES ('reason');
INSERT INTO field_names_to_retrieve VALUES ('reason__c');
INSERT INTO field_names_to_retrieve VALUES ('name__c'); 
INSERT INTO field_names_to_retrieve VALUES ('email__c');

If I run the following query, it works:

SELECT
    "QualifiedApiName",
    CASE WHEN UPPER(REGEXP_REPLACE("QualifiedApiName", '__c$', '')) NOT IN 
            (SELECT "QualifiedApiName" FROM "field_names_to_retrieve")
        THEN REGEXP_REPLACE("QualifiedApiName", '__c$', '')
    ELSE "QualifiedApiName"
END
FROM
"field_names_to_retrieve";

However the following query does NOT work. Notice that there in UPPER in the subquery:

SELECT
    "QualifiedApiName",
    CASE WHEN UPPER(REGEXP_REPLACE("QualifiedApiName", '__c$', '')) NOT IN 
            (SELECT UPPER("QualifiedApiName") FROM "field_names_to_retrieve")
        THEN REGEXP_REPLACE("QualifiedApiName", '__c$', '')
    ELSE "QualifiedApiName"
END
FROM
"field_names_to_retrieve";

Any ideas on why the subquery with UPPER fails?

score 1 · Answer 1 · answered Jul 16 '20 at 17:36

Clearly, the expression is an issue with NOT IN. I discourage using NOT IN with subqueries anyway because NULL values are not handled intuitively.

Does it work with NOT EXISTS?

SELECT (CASE WHEN NOT EXISTS (SELECT 1
                              FROM "field_names_to_retrieve" fntr2
                              WHERE UPPER(REGEXP_REPLACE(fntr."QualifiedApiName", '__c$', '')) = UPPER(fntr2."QualifiedApiName")
                             )
            THEN REGEXP_REPLACE("QualifiedApiName", '__c$', '')
            ELSE "QualifiedApiName"
        END)
FROM "field_names_to_retrieve" fntr;

Getting "Unsupported subquery type cannot be evaluated" when using UPPER in Snowflake IN Subquery

1 Answers1