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

Multiple Array Indexes causes inconsistent query plans (wrong results)

    XMLWordPrintable

Details

    • Bug
    • Resolution: Fixed
    • Major
    • 6.0.2, 6.5.0
    • 5.5.1, 5.5.2, 5.5.3, 6.0.1, 6.5.0
    • query
    • None
    • Untriaged
    • No

    Description

      Creating multiple array indexes can lead to certain query's returning inconsistent results and having inconsistent plans that change on execution. The inconsistent plans do continue to use the same index but the "WHERE" clause is not always included.
       
      Steps to recreate :

      • Create bucket called test
      • Create the following index :

        CREATE INDEX `test_index` ON `test`((all (array (`doc`.`Id`) for `doc` in (`test_array`.`test_doc`) end)),(`test_array`.`test_doc`),`field`) WHERE (`type` = "type")

      • Run the following query and note its plan text: 

        SELECT doc.`field_1`, doc.`Id`, count(meta(data).id) as cnt FROM test data UNNEST data.`test_array`.`test_doc` doc WHERE (data.`type`="type") AND (data.`field`="field") AND (doc.`Id` >= "0") AND (doc.`Type` = "doc_type") GROUP BY doc.`field_1`, doc.`Id`
        

      • Create the following index :

        CREATE INDEX test_inconsistent_1 ON test (ALL ARRAY t.id FOR t in diff_test_array END, `field`) WHERE `type`="type"
        

      • Run the previous query multiple times. The execution plan will vary and would produce different results.

      Attached my recreations results below.

      Attachments

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

        Activity

          People

            ajay.bhullar Ajay Bhullar
            thomas.dotters Thomas Dotters
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Gerrit Reviews

                There are no open Gerrit changes

                PagerDuty