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

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

    XMLWordPrintable

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 6.5.0
    • 6.5.0
    • query, secondary-index
    • I am using 6.5.0 build 2796
    • 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

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

        Activity

          Eben Haber:  There should be a way to show the error from UI when the create index has issues.

          keshav Keshav Murthy added a comment - Eben Haber :  There should be a way to show the error from UI when the create index has issues.

          Build couchbase-server-6.5.0-3278 contains query commit 670811f with commit message:
          MB-34198 fix the parenthesis misplacement and malformed indexname(add to test)

          build-team Couchbase Build Team added a comment - Build couchbase-server-6.5.0-3278 contains query commit 670811f with commit message: MB-34198 fix the parenthesis misplacement and malformed indexname(add to test)

          Build couchbase-server-6.5.0-3278 contains query-ee commit 4b149da with commit message:
          MB-34198 fix the parenthesis misplacement and malformed indexname

          build-team Couchbase Build Team added a comment - Build couchbase-server-6.5.0-3278 contains query-ee commit 4b149da with commit message: MB-34198 fix the parenthesis misplacement and malformed indexname

          This query has been added to unit test cases for verification.

          chang.liu Chang Liu (Inactive) added a comment - This query has been added to unit test cases for verification.
          ajay.bhullar Ajay Bhullar added a comment -

          verified in 6.5.0-3458:
          Here is the output now:

          "covering_indexes": [

          { "index_statement": "CREATE INDEX adv_ifmissing_ranking_scores_emma_0_add_ifmissing_ranking_scores3271086292 ON `familyPhotos`(ifmissing((((`ranking`).`scores`).`emma`), 0)+ifmissing((((`ranking`).`scores`).`matt`), 0)+ifmissing((((`ranking`).`scores`).`ali`), 0)+ifmissing((((`ranking`).`scores`).`matthew`), 0),`file`.`fullName`)" }

          ],

          ajay.bhullar Ajay Bhullar added a comment - verified in 6.5.0-3458: Here is the output now: "covering_indexes": [ { "index_statement": "CREATE INDEX adv_ifmissing_ranking_scores_emma_0_add_ifmissing_ranking_scores3271086292 ON `familyPhotos`(ifmissing((((`ranking`).`scores`).`emma`), 0)+ifmissing((((`ranking`).`scores`).`matt`), 0)+ifmissing((((`ranking`).`scores`).`ali`), 0)+ifmissing((((`ranking`).`scores`).`matthew`), 0),`file`.`fullName`)" } ],

          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:

              Gerrit Reviews

                There are no open Gerrit changes

                PagerDuty