cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

NamedStruct fails in the 'IN' query

Dhruv-22
New Contributor III

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.

1 REPLY 1

Kaniz
Community Manager
Community Manager

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). 😊12

Let me know if you need further clarification or assistance!

Join 100K+ Data Experts: Register Now & Grow with Us!

Excited to expand your horizons with us? Click here to Register and begin your journey to success!

Already a member? Login and join your local regional user group! If there isn’t one near you, fill out this form and we’ll create one for you to join!