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

Advisor can recommend syntactically incorrect and non-sargable array index

    XMLWordPrintable

Details

    • Untriaged
    • 0
    • Unknown

    Description

      Original repro in CBSE-17177

      A Simple Repro:

      1. 

      ADVISE SELECT * FROM collection1 WHERE any i IN [1,2] SATISFIES i = id END;

       

      2. Results:

      The recommended array index creation statement is syntactically incorrect. There is a "_" prefix before the DISTINCT keyword.

      CREATE INDEX adv_DISTINCT ON `default`:`bucket1`.`scope1`.`kids`(_DISTINCT ARRAY `i` FOR `i` IN [1, 2] END)

      {
              "#operator": "Advise",
              "advice": {
                  "#operator": "IndexAdvice",
                  "adviseinfo": {
                      "recommended_indexes": {
                          "indexes": [
                              {
                                  "index_statement": "CREATE INDEX adv_DISTINCT ON `default`:`bucket1`.`scope1`.`kids`(_DISTINCT ARRAY `i` FOR `i` IN [1, 2] END)",
                                  "index_statement_relative": "",
                                  "keyspace_alias": "kids",
                                  "query_context": "bucket1.scope1",
                                  "recommending_rule": "Index keys follow order of predicate types: 2. equality/null/missing."
                              }
                          ]
                      }
                  }
              },
              "query": "SELECT * FROM kids WHERE any i IN [1,2]  SATISFIES i = id END;"
          } 

      Attachments

        Activity

          People

            pierre.regazzoni Pierre Regazzoni
            dhanya.gowrish Dhanya Gowrish
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              PagerDuty