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

          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