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

Index advice is malformed - LET, ISMISSING, parenthesis, multiple index names

    XMLWordPrintable

Details

    • Untriaged
    • Unknown

    Description

      Summary: When entering a query that has a relatively complex LET, the index recommendation is malformed.

      Repro:

      1. In query workbench, enter a query like below
      2. Click "Advice"
      3. Observe the index recommendation

      Actual:

      Index recommendation is:

      (I attached as screenshot and a text file since Jira keeps mangling the formatting because of the backticks)

      When trying to execute that recommendation, there is a syntax error.

      Expected:

      No syntax error, index executes correctly. It appears there are multiple issues with this index, including multiple names separated by commas, and some parenthesis misplacement (at least).

      Notes:

      The query:

      select p.file.fullName, score
      from familyPhotos p
      let score = IFMISSING(p.ranking.scores.emma,0) + IFMISSING(p.ranking.scores.matt,0) + IFMISSING(p.ranking.scores.ali,0) + IFMISSING(p.ranking.scores.matthew,0)
      where score > 0
      order by score asc, p.file.fullName asc LIMIT 50;

       

       

      Attachments

        Activity

          People

            ajay.bhullar Ajay Bhullar
            matthew.groves Matthew Groves
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              PagerDuty