Snowflake Unsupported subquery Issue and How to resolve it

Snowflake provides rich support of subqueries. But, some scalar subqueries that are available in the relational databases such as Oracle are not supported in Snowflake yet. We have to identify the alternate methods for such a subqueries. In this article, we will check Snowflake unsupported subquery issue and how to resolve it?

Snowflake Unsupported subquery Issue

SQL compilation error: Unsupported subquery type cannot be evaluated

You are here because you might have got above error message during SQL migration from databases such as Oracle.

For example, consider following Oracle query that will throw an error when you execute on Snowflake.

Oracle:

select 
	t1.id,
	(
	  select listagg(value2, ',') within group (order by value2) as v2
	  from demo_test1 t2
	  where t1.id = t2.id
    group by t1.id
	) v2 
from demo_test t1;

ID      V2
1	v1,v2
1	v1,v2
2	v3,v4
2	v3,v4
3	v4

Well, same query will throw unsupported subquery issue in Snowflake.

For example,

select 
	t1.id,
	(
	  select listagg(value2, ',') within group (order by value2) as v2
	  from demo_test1 t2
	  where t1.id = t2.id
    group by t2.id
	)
	 v2 
from demo_test t1;

ERROR [12:1]: (SQLSTATE: 42601, SQLCODE: 2031): SQL compilation error: Unsupported subquery type cannot be evaluated

This issue is very common when you are trying to convert Oracle scalar correlated subquery into Snowflake equivalent.

However, the Snowflake cloud data warehouse does support scalar subqueries without any correlation.

For example, following query will work without any issues.

select 
	t1.id,
	(
	  select t2.id as v2
	  from demo_test1 t2 limit 1
	) v2 
from demo_test t1;

+----+----+
| ID | V2 |
|----+----|
|  1 |  1 |
|  1 |  1 |
|  2 |  1 |
|  2 |  1 |
|  3 |  1 |
+----+----+

You can read more about types of subqueries in my other articl,

Types of Subqueries in Snowflake and Examples

How to resolve Unsupported subquery Issue in Snowflake?

Snowflake may release solution for these types of subqueries in the future. But, at the moment, the only solution is to rewrite the query.

Here are few important points on Scalar Subqueries

Scalar Subquery return single row and a single column
If there is no matching records, it return NULL value.

Rewrite Subquery as a Left Outer Join

The Left outer join will return matching records and return null for non-matching rows.

For example, consider following query as a Snowflake alternative. The output is same as an original Oracle Query.

select 
	t1.id,
  a1.v2
from test t1
left join (	  select t2.id, listagg(value2, ',') within group (order by value2) as v2
	  from test1 t2    
    group by t2.id) a1
on (t1.id = a1.id)
;

+----+-------+
| ID | V2    |
|----+-------|
|  1 | v1,v2 |
|  1 | v1,v2 |
|  2 | v3,v4 |
|  2 | v3,v4 |
|  3 | v4    |
+----+-------+