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

array indexing plan wrongly include filter covers

    XMLWordPrintable

Details

    • Bug
    • Status: Closed
    • Critical
    • Resolution: Fixed
    • 4.6.3, 5.0.0, 5.5.0
    • 5.1.1, 5.5.0
    • query
    • Untriaged
    • Unknown

    Description

      CREATE INDEX `IX_reporting_visits_name_Person` ON `default`((distinct (array (`vs1`.`departmentCode`) for `vs1` in `visits` end)),`visits`,`firstName`,`lastName`) WHERE (`type` = "Person") ;
       explain select visits from default where type = 'Person' and meta().id not like '_sync%' and any v in visits satisfies v.departmentCode = 'HS' and Date_format_str(v.startDate, '1111-11-11') = '2018-01-30' END ;
      {
          "requestID": "aea8c31d-3e5f-45bb-b476-0d98ffa04089",
          "signature": "json",
          "results": [
              {
                  "plan": {
                      "#operator": "Sequence",
                      "~children": [
                          {
                              "#operator": "DistinctScan",
                              "scan": {
                                  "#operator": "IndexScan3",
                                  "covers": [
                                      "cover ((distinct (array (`vs1`.`departmentCode`) for `vs1` in (`default`.`visits`) end)))",
                                      "cover ((`default`.`visits`))",
                                      "cover ((`default`.`firstName`))",
                                      "cover ((`default`.`lastName`))",
                                      "cover ((meta(`default`).`id`))"
                                  ],
                                  "filter_covers": {
                                      "cover ((`default`.`type`))": "Person",
                                      "cover (any `v` in (`default`.`visits`) satisfies (((`v`.`departmentCode`) = \"HS\") and (date_format_str((`v`.`startDate`), \"1111-11-11\") = \"2018-01-30\")) end)": true
                                  },
                                  "index": "IX_reporting_visits_name_Person",
                                  "index_id": "df8b11b171935cf",
                                  "index_projection": {
                                      "entry_keys": [
                                          1
                                      ],
                                      "primary_key": true
                                  },
                                  "keyspace": "default",
                                  "namespace": "default",
                                  "spans": [
                                      {
                                          "exact": true,
                                          "range": [
                                              {
                                                  "high": "\"HS\"",
                                                  "inclusion": 3,
                                                  "low": "\"HS\""
                                              }
                                          ]
                                      }
                                  ],
                                  "using": "gsi"
                              }
                          },
                          {
                              "#operator": "Parallel",
                              "~child": {
                                  "#operator": "Sequence",
                                  "~children": [
                                      {
                                          "#operator": "Filter",
                                          "condition": "(((cover ((`default`.`type`)) = \"Person\") and (not (cover ((meta(`default`).`id`)) like \"_sync%\"))) and cover (any `v` in (`default`.`visits`) satisfies (((`v`.`departmentCode`) = \"HS\") and (date_format_str((`v`.`startDate`), \"1111-11-11\") = \"2018-01-30\")) end))"
                                      },
                                      {
                                          "#operator": "InitialProject",
                                          "result_terms": [
                                              {
                                                  "expr": "cover ((`default`.`visits`))"
                                              }
                                          ]
                                      },
                                      {
                                          "#operator": "FinalProject"
                                      }
                                  ]
                              }
                          }
                      ]
                  },
                  "text": "select visits from default where type = 'Person' and meta().id not like '_sync%' and any v in visits satisfies v.departmentCode = 'HS' and Date_format_str(v.startDate, '1111-11-11') = '2018-01-30' END;"
              }
          ],
          "status": "success",
          "metrics": {
              "elapsedTime": "3.529461ms",
              "executionTime": "3.505716ms",
              "resultCount": 1,
              "resultSize": 3718
          }
      }
      

      Filter cover should not have contain "cover (any `v` in (`default`.`visits`) satisfies (((`v`.`departmentCode`) = \"HS\") and (date_format_str((`v`.`startDate`), \"1111-11-11\") = \"2018-01-30\")) end)": true because predicate not pushed to spans as exact.

      https://forums.couchbase.com/t/any-clause-giving-different-results-when-selecting-specific-fields-vs-entire-document/15641/2

      Attachments

        Issue Links

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

          Activity

            Build couchbase-server-5.5.0-2527 contains query commit e970ac8c5e1e05ca38a3ec25da7f2e85ab54dbf4 with commit message:
            MB-27815 Consider Array index filter covers, when pushed to indexer
            https://github.com/couchbase/query/commit/e970ac8c5e1e05ca38a3ec25da7f2e85ab54dbf4

            build-team Couchbase Build Team added a comment - Build couchbase-server-5.5.0-2527 contains query commit e970ac8c5e1e05ca38a3ec25da7f2e85ab54dbf4 with commit message: MB-27815 Consider Array index filter covers, when pushed to indexer https://github.com/couchbase/query/commit/e970ac8c5e1e05ca38a3ec25da7f2e85ab54dbf4

            Build couchbase-server-6.0.0-1031 contains query commit e970ac8c5e1e05ca38a3ec25da7f2e85ab54dbf4 with commit message:
            MB-27815 Consider Array index filter covers, when pushed to indexer
            https://github.com/couchbase/query/commit/e970ac8c5e1e05ca38a3ec25da7f2e85ab54dbf4

            build-team Couchbase Build Team added a comment - Build couchbase-server-6.0.0-1031 contains query commit e970ac8c5e1e05ca38a3ec25da7f2e85ab54dbf4 with commit message: MB-27815 Consider Array index filter covers, when pushed to indexer https://github.com/couchbase/query/commit/e970ac8c5e1e05ca38a3ec25da7f2e85ab54dbf4

            verified in 5.1.1-5708 and 5.5.0-2556

            ajay.bhullar Ajay Bhullar added a comment - verified in 5.1.1-5708 and 5.5.0-2556

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Gerrit Reviews

                  There are no open Gerrit changes

                  PagerDuty