Uploaded image for project: 'Couchbase Documentation'
  1. Couchbase Documentation
  2. DOC-4184

Analytics: Document which variables are in scope in JOIN clauses

    XMLWordPrintable

Details

    • Improvement
    • Resolution: Done
    • Major
    • Alice
    • None
    • analytics
    • None
    • 1

    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

          No reviews matched the request. Check your Options in the drop-down menu of this sections header.

          Activity

            People

              simon.dew Simon Dew
              dmitry.lychagin Dmitry Lychagin (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Gerrit Reviews

                  There are no open Gerrit changes

                  PagerDuty