Set operations joined with tables/subqueries may be pushed down wrongly
- Dominant language
- C
- Stars
- 12.8k
- Forks
- 794
- Avg merge
- 2d 14h
- Merged PRs (30d)
- 31
Description
Citus pushes down the following query (or some of its variants)
```SQL
SELECT
(SELECT user_id FROM users_table_part as u1 WHERE user_id = e.value_1)
FROM
(SELECT * FROM users_table_part as u2 UNION ALL SELECT * FROM users_table_part as u3) as e
LIMIT 1;
SELECT
*
FROM
(SELECT * FROM users_table_part UNION ALL SELECT * FROM users_table_part) as e
JOIN users_table_part ON (users_table_part.user_id = e.value_1)
LIMIT 1;
```
The problem here seems we use the same `AttributeEquivalenceClass` while adding set operations and all other JOINs.
This approach misses to understand `WHERE user_id = e.value_1` is not on the distribution key. It first adds `u1` to the equivalence as it has a join on the dist key. Later, it adds the distribution columns of `u2` an `u3` to the same equivalence assuming that this is a top-level union query.
Instead, we should probably treat `UNION [ALL]` restrictions differently, where we should treat UNION [ALL] restrictions in a different manner, such as we create a new restriction per union [all]:
```
iff --git a/src/backend/distributed/planner/relation_restriction_equivalence.c b/src/backend/distributed/planner/relation_restriction_equivalence.c
index e20120f91..047642cae 100644
--- a/src/backend/distributed/planner/relation_restriction_equivalence.c
+++ b/src/backend/distributed/planner/relation_restriction_equivalence.c
@@ -1304,11 +1304,16 @@ AddRteSubqueryToAttributeEquivalenceClass(AttributeEquivalenceClass
}
else if (targetSubquery->setOperations)
{
- AddUnionSetOperationsToAttributeEquivalenceClass(attributeEquivalenceClass,
+
+ AttributeEquivalenceClass *unionAllEquivalence =
+ palloc0(sizeof(AttributeEquivalenceClass));
+
+ AddUnionSetOperationsToAttributeEquivalenceClass(unionAllEquivalence,
baseRelOptInfo->subroot,
(SetOperationStmt *)
targetSubquery->setOperations,
varToBeAdded);
+
}
else if (varToBeAdded && IsA(varToBeAdded, Var) && varToBeAdded->varlevelsup == 0)
```
related to https://github.com/citusdata/citus/issues/4703
Extra notes: We don't have the same problems for JOINs because we already create a separate EquivalenceClass per JOIN/planner equivalence:
```SQL
SELECT * FROM users_table_part u1, users_table_part u2 WHERE u1.user_id = u2.value_1 AND u2.user_id = u1.value_1;
ERROR: complex joins are only supported when all distributed tables are joined on their distribution columns with equal operator
```
Contributor guide
Assessment
This issue has not been assessed yet.