NamedStruct fails in the 'IN' query

[Dhruv-22]

I've posted the same question on stackoverflow (link) as well. I will post any solution I get there.

I was trying to understand using many columns in the IN query and came across this statement.

SELECT (1, 2) IN (SELECT c1, c2 FROM VALUES(1, 2), (3, 4) AS (c1, c2));

Its output is as follows

(named_struct('1', 1, '2', 2) IN (listquery()))

true

I tried using named_struct directly as follows

SELECT NAMED_STRUCT('1', 1, '2', 2) IN (SELECT c1, c2 FROM VALUES(1, 2), (3, 4) AS (c1, c2));

It gives the following error

Cannot resolve "(named_struct(1, 1, 2, 2) IN (listquery()))" due to data type mismatch:
The number of columns in the left hand side of an IN subquery does not match the number of columns in the output of subquery.
Left hand side columns(length: 1): ["named_struct(1, 1, 2, 2)"],
right hand side columns(length: 2): ["c1", "c2"].;
line 1 pos 36;
'Project [unresolvedalias(named_struct(1, 1, 2, 2) IN (list#1529887 []), None)]
:  +- Project [c1#1529888, c2#1529889]
:     +- SubqueryAlias AS
:        +- LocalRelation [c1#1529888, c2#1529889]
+- OneRowRelation

Why does the number of columns becomes an issue now? The above query works fine even when I replace named_struct with struct.

Any help is appreciated.

[Kaniz]

Hi @Dhruv-22, 

1. The IN operator expects a subquery to return a single column. When you use a subquery with IN, it compares the value from the left side (in your case, the named struct) with the values returned by the subquery on the right side.

2. In your first query, you used (1, 2) on the left side and (SELECT c1, c2 FROM VALUES(1, 2), (3, 4) AS (c1, c2)) on the right side. The subquery returns two columns (c1 and c2), which doesn’t match the single value on the left side. Hence, the error.

3. When you replaced named_struct with struct, it worked because struct is a single value (essentially a composite type), and the subquery also returned a single column.

To fix the issue, ensure that the subquery on the right side of the IN operator returns only one column. You can select a single column or use a function that returns a single value. For example:

SELECT (1, 2) IN (SELECT c1 FROM VALUES(1, 2), (3, 4) AS (c1, c2));

This query will work because the subquery now returns only one column (c1). 😊¹²

Let me know if you need further clarification or assistance!