Uploaded image for project: 'Couchbase Server'
  1. Couchbase Server
  2. MB-30813

correlated subquery quit correlation on where clause requires use keys - remove or relax limitation

    XMLWordPrintable

Details

    • Improvement
    • Resolution: Fixed
    • Major
    • 7.0.0
    • 4.0.0, 4.1.2, 4.5.1, 4.6.5, 5.0.1, 5.1.1, 5.5.0, 6.0.0, 6.5.0
    • query

    Description

      Consider

      SELECT (SELECT date, ARRAY_AGG(DISTINCT expiry) AS expiries
          FROM `default`
          WHERE _dataType='o2'
          AND underlyingId IN [500046, 527383]
          AND date BETWEEN firstTradeDate AND expiry
          AND expiry IS NOT MISSING
          GROUP BY date) FROM ['2018-01-01', '2018-01-02'] date
      

      (just a query that returns all trades active on specific expiry dates, grouped by expiry dates).

      This is perfectly legal N1QL, which however fails with

      Error evaluating projection. - cause: FROM in correlated subquery must have USE KEYS clause: FROM default
      

      It's not exactly clear why the limitation is there, but if I remove the check from planner/build_select_from.go:builder.VisitKeyspaceTerm() the request goes through happily and returns the expected results.

      I suspect this is a relic from old style joins. It would be good if we could remove this at least for ANSI joins.

      Attachments

        Issue Links

          For Gerrit Dashboard: MB-30813
          # Subject Branch Project Status CR V

          Activity

            People

              pierre.regazzoni Pierre Regazzoni
              marco.greco Marco Greco (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                PagerDuty