Description
We need to document which variables are in scope when processing JOIN clause branches. See discussion at https://issues.couchbase.com/browse/MB-29518
When compiling
SELECT * FROM customers c JOIN (select value o from orders o where o.custid = c.custid) o
ON c.custid = o.custid
LIMIT 1;
The variable "c" defined by FROM clause is not visible inside the JOIN branch (in the subquery: select value o from orders o where o.custid = c.custid)
We need to that variables defined by JOIN branches are not visible in the subsequent branches in the same JOIN clause. This also applies to the FROM variable that starts the JOIN clause. Example:
FROM dataset1 AS a JOIN (subquery2) AS b JOIN (subquery3) AS c
subquery2 does not see variable "a", and subquery3 does not see "a" and "b".
If "a" and "b" are used in these subqueries then they'll be resolved according to regular name resolution rules.
In contrast, variables remain in scope if FROM clause contains multiple terms:
FROM dataset1 as a, (subquery2) AS b
subquery2 will see "a" variable bound by the previous term to an item in dataset1.
Let's mention this in two places:
1) "N1QL for Analytics Language Reference / Queries"
https://docs.couchbase.com/server/6.0/analytics/3_query.html
We already talk about multiple FROM terms:
"Multiple FROM Terms
The query language permits correlations among FROM terms. Specifically, a FROM binding expression can refer to variables defined to its left in the given FROM clause. "
but we also need to specify the behavior when it comes to JOIN/UNNEST clauses (in the "JOIN Clauses" section)
2) "N1QL for Analytics Language Reference /
Appendix 3: Variable Bindings and Name Resolution"
https://docs.couchbase.com/server/6.0/analytics/appendix_3_resolution.html
We now have: "Special case: In an expression inside a FROM clause, a variable is in scope if it was bound in an earlier expression in the same FROM clause."
Let's add a note that it does not apply to JOIN/UNNEST clauses. So variables defined in the FROM or prior JOIN clauses are not in scope for a JOIN clause expression
Attachments
Issue Links
- relates to
-
MB-29518 Strange difference between implicit and explicit joins with left correlation
-
- Closed
-