Repository: duckdb/duckdb (language: python)

Issue:
Postgresql incompatbility on tuple operations
### What happens?

```
select 1 where (1,2)  = (1,2);
select 1 where (1,2) in ((1,2), (3,4));
select 1 where (1,2) in (select 1,2);
```
In DuckDb only the first 2 what is working.

The last one is throwing error of "Binder Error: Subquery returns 2 columns - expected 1 LINE 1: select 1 where (1,2) in (select 1,2)"

The second statement is proving that there are some level of support but the third one is not supported.

NB: original discussion: https://github.com/duckdb/duckdb/discussions/14472

In discussion there are suggested change of `select 1 where (1,2) in (select (1,2))` what is working in DuckDb but failing in PostgreSql (AWS RDS Aurora - PostgreSQL 16.1 on x86_64-pc-linux-gnu, compiled by x86_64-pc-linux-gnu-gcc (GCC) 9.5.0, 64-bit)

Postgresql error message: "[42601] ERROR: subquery has too few columns"

### To Reproduce

```
select 1 where (1,2)  = (1,2);
select 1 where (1,2) in ((1,2), (3,4));

select 1 where (1,2) in (select 1,2);

select 1 where (1,2) in (select (1,2))

```

### OS:

MacOS Sonoma Apple M1 Pro

### DuckDB Version:

v1.1.1

### DuckDB Client:

JDBC - DataGrip

### Hardware:

_No response_

### Full Name:

Otto TAkacs

### Affiliation:

Software engineer

### What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have not tested with any build

### Did you include all relevant data sets for reproducing the issue?

Yes

### Did you include all code required to reproduce the issue?

- [X] Yes, I have

### Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

- [X] Yes, I have


Discussion / hints:

Fix patch:
diff --git a/src/include/duckdb/planner/expression/bound_subquery_expression.hpp b/src/include/duckdb/planner/expression/bound_subquery_expression.hpp
index 9572036ab5a3..31501dd8cfd0 100644
--- a/src/include/duckdb/planner/expression/bound_subquery_expression.hpp
+++ b/src/include/duckdb/planner/expression/bound_subquery_expression.hpp
@@ -32,12 +32,12 @@ class BoundSubqueryExpression : public Expression {
 	unique_ptr<BoundQueryNode> subquery;
 	//! The subquery type
 	SubqueryType subquery_type;
-	//! the child expression to compare with (in case of IN, ANY, ALL operators)
-	unique_ptr<Expression> child;
+	//! the child expressions to compare with (in case of IN, ANY, ALL operators)
+	vector<unique_ptr<Expression>> children;
 	//! The comparison type of the child expression with the subquery (in case of ANY, ALL operators)
 	ExpressionType comparison_type;
-	//! The LogicalType of the subquery result. Only used for ANY expressions.
-	LogicalType child_type;
+	//! The LogicalTypes of the subquery result. Only used for ANY expressions.
+	vector<LogicalType> child_types;
 	//! The target LogicalType of the subquery result (i.e. to which type it should be casted, if child_type <>
 	//! child_target). Only used for ANY expressions.
 	LogicalType child_target;
diff --git a/src/planner/binder/expression/bind_subquery_expression.cpp b/src/planner/binder/expression/bind_subquery_expression.cpp
index dc0f7624c94b..0bf4dd5a642b 100644
--- a/src/planner/binder/expression/bind_subquery_expression.cpp
+++ b/src/planner/binder/expression/bind_subquery_expression.cpp
@@ -1,6 +1,7 @@
 #include "duckdb/parser/expression/subquery_expression.hpp"
 #include "duckdb/planner/binder.hpp"
 #include "duckdb/planner/expression/bound_cast_expression.hpp"
+#include "duckdb/planner/expression/bound_function_expression.hpp"
 #include "duckdb/planner/expression/bound_subquery_expression.hpp"
 #include "duckdb/planner/expression_binder.hpp"
 #include "duckdb/common/string_util.hpp"
@@ -38,6 +39,48 @@ class BoundSubqueryNode : public QueryNode {
 	}
 };
 
+bool TypeIsUnnamedStruct(const LogicalType &type) {
+	if (type.id() != LogicalTypeId::STRUCT) {
+		return false;
+	}
+	return StructType::IsUnnamed(type);
+}
+
+void ExtractSubqueryChildren(unique_ptr<Expression> &child, vector<unique_ptr<Expression>> &result,
+                             const vector<LogicalType> &types) {
+	// two scenarios
+	// Single Expression (standard):
+	// x IN (...)
+	// Multi-Expression/Struct:
+	// (a, b) IN (SELECT ...)
+	// the latter has an unnamed struct on the LHS that is created by a "ROW" expression
+	auto &return_type = child->return_type;
+	if (!TypeIsUnnamedStruct(return_type)) {
+		// child is not an unnamed struct
+		return;
+	}
+	if (child->expression_class != ExpressionClass::BOUND_FUNCTION) {
+		// not a function
+		return;
+	}
+	auto &function = child->Cast<BoundFunctionExpression>();
+	if (function.function.name != "row") {
+		// not "ROW"
+		return;
+	}
+	// we found (a, b, ...) - we can extract all children of this function
+	// note that we don't always want to do this
+	if (types.size() == 1 && TypeIsUnnamedStruct(types[0]) && function.children.size() != types.size()) {
+		// old case: we have an unnamed struct INSIDE the subquery as well
+		// i.e. (a, b) IN (SELECT (a, b) ...)
+		// unnesting the struct is guaranteed to throw an error - match the structs against each-other instead
+		return;
+	}
+	for (auto &row_child : function.children) {
+		result.push_back(std::move(row_child));
+	}
+}
+
 BindResult ExpressionBinder::BindExpression(SubqueryExpression &expr, idx_t depth) {
 	if (expr.subquery->node->type != QueryNodeType::BOUND_SUBQUERY_NODE) {
 		// first bind the actual subquery in a new binder
@@ -54,9 +97,6 @@ BindResult ExpressionBinder::BindExpression(SubqueryExpression &expr, idx_t dept
 				binder.AddCorrelatedColumn(corr);
 			}
 		}
-		if (expr.subquery_type != SubqueryType::EXISTS && bound_node->types.size() > 1) {
-			throw BinderException(expr, "Subquery returns %zu columns - expected 1", bound_node->types.size());
-		}
 		auto prior_subquery = std::move(expr.subquery);
 		expr.subquery = make_uniq<SelectStatement>();
 		expr.subquery->node =
@@ -70,9 +110,25 @@ BindResult ExpressionBinder::BindExpression(SubqueryExpression &expr, idx_t dept
 			return BindResult(std::move(error));
 		}
 	}
+	auto &bound_subquery = expr.subquery->node->Cast<BoundSubqueryNode>();
+	vector<unique_ptr<Expression>> child_expressions;
+	if (expr.subquery_type != SubqueryType::EXISTS) {
+		idx_t expected_columns = 1;
+		if (expr.child) {
+			auto &child = BoundExpression::GetExpression(*expr.child);
+			ExtractSubqueryChildren(child, child_expressions, bound_subquery.bound_node->types);
+			if (child_expressions.empty()) {
+				child_expressions.push_back(std::move(child));
+			}
+			expected_columns = child_expressions.size();
+		}
+		if (bound_subquery.bound_node->types.size() != expected_columns) {
+			throw BinderException(expr, "Subquery returns %zu columns - expected %d",
+			                      bound_subquery.bound_node->types.size(), expected_columns);
+		}
+	}
 	// both binding the child and binding the subquery was successful
 	D_ASSERT(expr.subquery->node->type == QueryNodeType::BOUND_SUBQUERY_NODE);
-	auto &bound_subquery = expr.subquery->node->Cast<BoundSubqueryNode>();
 	auto subquery_binder = std::move(bound_subquery.subquery_binder);
 	auto bound_node = std::move(bound_subquery.bound_node);
 	LogicalType return_type =
@@ -85,19 +141,21 @@ BindResult ExpressionBinder::BindExpression(SubqueryExpression &expr, idx_t dept
 	if (expr.subquery_type == SubqueryType::ANY) {
 		// ANY comparison
 		// cast child and subquery child to equivalent types
-		D_ASSERT(bound_node->types.size() == 1);
-		auto &child = BoundExpression::GetExpression(*expr.child);
-		auto child_type = ExpressionBinder::GetExpressionReturnType(*child);
-		LogicalType compare_type;
-		if (!LogicalType::TryGetMaxLogicalType(context, child_type, bound_node->types[0], compare_type)) {
-			throw BinderException(
-			    expr, "Cannot compare values of type %s and %s in IN/ANY/ALL clause - an explicit cast is required",
-			    child_type.ToString(), bound_node->types[0]);
+		for (idx_t child_idx = 0; child_idx < child_expressions.size(); child_idx++) {
+			auto &child = child_expressions[child_idx];
+			auto child_type = ExpressionBinder::GetExpressionReturnType(*child);
+			auto &subquery_type = bound_node->types[child_idx];
+			LogicalType compare_type;
+			if (!LogicalType::TryGetMaxLogicalType(context, child_type, subquery_type, compare_type)) {
+				throw BinderException(
+				    expr, "Cannot compare values of type %s and %s in IN/ANY/ALL clause - an explicit cast is required",
+				    child_type.ToString(), subquery_type);
+			}
+			child = BoundCastExpression::AddCastToType(context, std::move(child), compare_type);
+			result->child_types.push_back(subquery_type);
+			result->child_target = compare_type;
+			result->children.push_back(std::move(child));
 		}
-		child = BoundCastExpression::AddCastToType(context, std::move(child), compare_type);
-		result->child_type = bound_node->types[0];
-		result->child_target = compare_type;
-		result->child = std::move(child);
 	}
 	result->binder = std::move(subquery_binder);
 	result->subquery = std::move(bound_node);
diff --git a/src/planner/binder/query_node/plan_subquery.cpp b/src/planner/binder/query_node/plan_subquery.cpp
index 4b5857f573e7..b0361dcafa93 100644
--- a/src/planner/binder/query_node/plan_subquery.cpp
+++ b/src/planner/binder/query_node/plan_subquery.cpp
@@ -163,12 +163,15 @@ static unique_ptr<Expression> PlanUncorrelatedSubquery(Binder &binder, BoundSubq
 		join->AddChild(std::move(root));
 		join->AddChild(std::move(plan));
 		// create the JOIN condition
-		JoinCondition cond;
-		cond.left = std::move(expr.child);
-		cond.right = BoundCastExpression::AddDefaultCastToType(
-		    make_uniq<BoundColumnRefExpression>(expr.child_type, plan_columns[0]), expr.child_target);
-		cond.comparison = expr.comparison_type;
-		join->conditions.push_back(std::move(cond));
+		for (idx_t child_idx = 0; child_idx < expr.children.size(); child_idx++) {
+			JoinCondition cond;
+			cond.left = std::move(expr.children[child_idx]);
+			auto &child_type = expr.child_types[child_idx];
+			cond.right = BoundCastExpression::AddDefaultCastToType(
+			    make_uniq<BoundColumnRefExpression>(child_type, plan_columns[child_idx]), expr.child_target);
+			cond.comparison = expr.comparison_type;
+			join->conditions.push_back(std::move(cond));
+		}
 		root = std::move(join);
 
 		// we replace the original subquery with a BoundColumnRefExpression referring to the mark column
@@ -354,13 +357,24 @@ static unique_ptr<Expression> PlanCorrelatedSubquery(Binder &binder, BoundSubque
 
 		// now we create the join conditions between the dependent join and the original table
 		CreateDelimJoinConditions(*delim_join, correlated_columns, plan_columns, flatten.delim_offset, perform_delim);
+		if (expr.children.size() > 1) {
+			// FIXME: the code to generate the plan here is actually correct
+			// the problem is in the hash join - specifically PhysicalHashJoin::InitializeHashTable
+			// this contains code that is hard-coded for a single comparison
+			// -> (delim_types.size() + 1 == conditions.size())
+			// this needs to be generalized to get this to work
+			throw NotImplementedException("Correlated IN/ANY/ALL with multiple columns not yet supported");
+		}
 		// add the actual condition based on the ANY/ALL predicate
-		JoinCondition compare_cond;
-		compare_cond.left = std::move(expr.child);
-		compare_cond.right = BoundCastExpression::AddDefaultCastToType(
-		    make_uniq<BoundColumnRefExpression>(expr.child_type, plan_columns[0]), expr.child_target);
-		compare_cond.comparison = expr.comparison_type;
-		delim_join->conditions.push_back(std::move(compare_cond));
+		for (idx_t child_idx = 0; child_idx < expr.children.size(); child_idx++) {
+			JoinCondition compare_cond;
+			compare_cond.left = std::move(expr.children[child_idx]);
+			auto &child_type = expr.child_types[child_idx];
+			compare_cond.right = BoundCastExpression::AddDefaultCastToType(
+			    make_uniq<BoundColumnRefExpression>(child_type, plan_columns[child_idx]), expr.child_target);
+			compare_cond.comparison = expr.comparison_type;
+			delim_join->conditions.push_back(std::move(compare_cond));
+		}
 
 		delim_join->AddChild(std::move(dependent_join));
 		root = std::move(delim_join);
diff --git a/src/planner/expression_iterator.cpp b/src/planner/expression_iterator.cpp
index 1b271112e39a..1188958b8a81 100644
--- a/src/planner/expression_iterator.cpp
+++ b/src/planner/expression_iterator.cpp
@@ -88,8 +88,8 @@ void ExpressionIterator::EnumerateChildren(Expression &expr,
 	}
 	case ExpressionClass::BOUND_SUBQUERY: {
 		auto &subquery_expr = expr.Cast<BoundSubqueryExpression>();
-		if (subquery_expr.child) {
-			callback(subquery_expr.child);
+		for (auto &child : subquery_expr.children) {
+			callback(child);
 		}
 		break;
 	}
diff --git a/src/planner/joinside.cpp b/src/planner/joinside.cpp
index ba5b49cc350a..38cf0f68063e 100644
--- a/src/planner/joinside.cpp
+++ b/src/planner/joinside.cpp
@@ -69,8 +69,9 @@ JoinSide JoinSide::GetJoinSide(Expression &expression, const unordered_set<idx_t
 		D_ASSERT(expression.GetExpressionClass() == ExpressionClass::BOUND_SUBQUERY);
 		auto &subquery = expression.Cast<BoundSubqueryExpression>();
 		JoinSide side = JoinSide::NONE;
-		if (subquery.child) {
-			side = GetJoinSide(*subquery.child, left_bindings, right_bindings);
+		for (auto &child : subquery.children) {
+			auto child_side = GetJoinSide(*child, left_bindings, right_bindings);
+			side = CombineJoinSide(side, child_side);
 		}
 		// correlated subquery, check the side of each of correlated columns in the subquery
 		for (auto &corr : subquery.binder->correlated_columns) {


Test patch:
diff --git a/test/fuzzer/pedro/complex_type_all_subquery.test b/test/fuzzer/pedro/complex_type_all_subquery.test
index f88a8dcbbc68..7bb0ca8f55ad 100644
--- a/test/fuzzer/pedro/complex_type_all_subquery.test
+++ b/test/fuzzer/pedro/complex_type_all_subquery.test
@@ -9,7 +9,7 @@ PRAGMA enable_verification
 statement error
 VALUES((0, 0) = ALL(SELECT 2));
 ----
-explicit cast is required
+Subquery returns 1 columns - expected 2
 
 # use ALL with complex types
 statement error
diff --git a/test/sql/subquery/scalar/subquery_row_in_any.test b/test/sql/subquery/scalar/subquery_row_in_any.test
new file mode 100644
index 000000000000..f0f700097beb
--- /dev/null
+++ b/test/sql/subquery/scalar/subquery_row_in_any.test
@@ -0,0 +1,75 @@
+# name: test/sql/subquery/scalar/subquery_row_in_any.test
+# description: Test (a, b) IN (SELECT a, b ...)
+# group: [scalar]
+
+statement ok
+CREATE TABLE integers(i INTEGER);
+
+statement ok
+INSERT INTO integers VALUES (1), (2), (3), (NULL);
+
+query I
+SELECT (1, 2) IN (SELECT i, i + 1 FROM integers)
+----
+true
+
+# it still works with the row inside the subquery
+query I
+SELECT (1, 2) IN (SELECT (i, i + 1) FROM integers)
+----
+true
+
+query I
+SELECT row(1) IN (SELECT i FROM integers)
+----
+true
+
+# also works with explicit row function
+query I
+SELECT ROW(1, 2) IN (SELECT i, i + 1 FROM integers)
+----
+true
+
+# NULL semantics
+query I
+SELECT ROW(1, 2) IN (SELECT i, i + 2 FROM integers)
+----
+NULL
+
+query I
+SELECT ROW(1, 2) IN (SELECT i, i + 2 FROM integers WHERE i IS NOT NULL)
+----
+false
+
+query I
+select 1 where (1,2) in (select 1,2);
+----
+1
+
+query I
+select 1 where (1,2) not in (select 1,2);
+----
+
+statement error
+SELECT (1, 2) IN (SELECT (i, i + 1, i + 2) FROM integers)
+----
+Cannot compare values of type
+
+# FIXME: correlated not yet supported
+statement error
+SELECT ROW(1, 2) IN (SELECT i1.i, i1.i + 1)
+FROM integers i1
+----
+not yet supported
+
+mode skip
+
+# correlated
+query I
+SELECT ROW(1, 2) IN (SELECT i1.i, i1.i + 1)
+FROM integers i1
+----
+true
+false
+false
+NULL