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

Query returns error, In Subquery LET,LETTING variables are treated as correlated

    XMLWordPrintable

Details

    • Bug
    • Resolution: Fixed
    • Major
    • 5.5.0
    • 4.6.3
    • query
    • None
    • Untriaged
    • No

    Description

      CREATE PRIMARY INDEX ON default;
       insert into default values ("k01",{"a":1});
      SELECT f.f1
        FROM default f
       WHERE f.f2 IN (SELECT RAW s.f3 
                        FROM default s 
                         LET k1 = (SELECT RAW l.ff FROM default USE KEYS "key1" )
                            
                       WHERE s.f4 IN k1);
       
      SELECT f.f1 FROM default f WHERE f.f2 IN (SELECT RAW s.f3 FROM default s LET k1 = [5] WHERE s.f4 IN k1 );
       
       
      "errors": [
              {
                  "code": 5010,
                  "msg": "Error evaluating filter. - cause: FROM in correlated subquery must have USE KEYS clause: FROM default."
              }
          ]
      

      LET,:ETTING variables are considers as new level of bindings and FROM clause info and from clause info is lost while determining correlation. During formalization LET,LETTING info needs to be kept same level as FROM clause.

      Workaround works.

      SELECT f.f1
        FROM default f
       WHERE f.f2 IN (SELECT RAW s.f3
                        FROM default s
                       WHERE s.f4 IN (SELECT RAW l.ff FROM default USE KEYS "key1") );
      
      

      https://forums.couchbase.com/t/error-evaluating-filter-cause-from-in-correlated-subquery-must-have-use-keys/14495

      Attachments

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

        Activity

          People

            ajay.bhullar Ajay Bhullar
            Sitaram.Vemulapalli Sitaram Vemulapalli
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Gerrit Reviews

                There are no open Gerrit changes

                PagerDuty