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

[Index API2] Distinct=true gives wrong results when projection is passed as nil

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.0.0
    • Fix Version/s: 5.0.0
    • Component/s: query, secondary-index
    • Labels:
      None
    • Triage:
      Untriaged
    • Is this a Regression?:
      Unknown

      Description

      When projection is nil means project the all the index keys and meta().id
      distinct= true means send distinct values of projection (i.e. when projection is nil the distinct needs to applied on all keys + meta().id).
      Distinct is hint to index API2. When passed it can ignore and provide duplicates. But it can't wrongly eliminate values.

      INSERT INTO default VALUES("k11",{"ka0":[5, 15], "k0":1});
      INSERT INTO default VALUES("k12",{"ka0":[5, 25], "k0":1});
      INSERT INTO default VALUES("k13",{"ka0":[4, 15], "k0":2});
      INSERT INTO default VALUES("k14",{"ka0":[4, 15], "k0":2});
      CREATE INDEX ix1 ON default (k0);
      SELECT DISTINCT meta().id, k0 FROM default WHERE k0 = 1;
      

      curl -H "Accept: application/json" -v Administrator:password@127.0.0.1:9102/api/index/17910814830959724252?multiscan=true -X GET -d '{"reverse": false, "projection": null, "distinct": true, "limit": 10000, "offset": 0, "stale": "false", "scans": "[{\"Filter\": [

      {\"Low\": 1, \"High\": 1, \"Inclusion\": 3}

      ]}]"}'

        Attachments

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

          Activity

          Hide
          Sitaram.Vemulapalli Sitaram Vemulapalli added a comment -

          Query can expose the feature for use cases where it can give right results to user. I can think of only use case that can expose at present is when index keys include meta().id and user does DISTINCT on all the keys including meta().id.

          I just gave my opinion on earlier comment on "enhancing filtering function in index to allow filters on meta.id" not sure if it approved by others.

          Show
          Sitaram.Vemulapalli Sitaram Vemulapalli added a comment - Query can expose the feature for use cases where it can give right results to user. I can think of only use case that can expose at present is when index keys include meta().id and user does DISTINCT on all the keys including meta().id. I just gave my opinion on earlier comment on "enhancing filtering function in index to allow filters on meta.id" not sure if it approved by others.
          Hide
          Sitaram.Vemulapalli Sitaram Vemulapalli added a comment -

          Distinct pushed only when index keys has meta().id and query uses all the index keys and projection has distinct. All other cases distinct push down disabled due to wrong results.

          Example of pushdown.

          CREATE INDEX ix2 ON default (k0, META().id);
          SELECT DISTINCT k0, META().id FROM default WHERE k0 = 1;

          Show
          Sitaram.Vemulapalli Sitaram Vemulapalli added a comment - Distinct pushed only when index keys has meta().id and query uses all the index keys and projection has distinct. All other cases distinct push down disabled due to wrong results. Example of pushdown. CREATE INDEX ix2 ON default (k0, META().id); SELECT DISTINCT k0, META().id FROM default WHERE k0 = 1;
          Hide
          siri Sriram Melkote added a comment - - edited

          Sitaram, below can be pushed to index today:

          CREATE INDEX ix1 ON default (k0);
          SELECT DISTINCT k0 FROM default WHERE k0 = 1;

          Show
          siri Sriram Melkote added a comment - - edited Sitaram, below can be pushed to index today: CREATE INDEX ix1 ON default (k0); SELECT DISTINCT k0 FROM default WHERE k0 = 1;
          Hide
          Sitaram.Vemulapalli Sitaram Vemulapalli added a comment - - edited

          CREATE INDEX ix1 ON default (k0);
          SELECT DISTINCT k0 FROM default WHERE k0 = 1;
          Above query requires only k0 not meta().id, so the projection is not nil and Indexer will ignore distinct=true. If indexer implements that we can pushdown.

          Show
          Sitaram.Vemulapalli Sitaram Vemulapalli added a comment - - edited CREATE INDEX ix1 ON default (k0); SELECT DISTINCT k0 FROM default WHERE k0 = 1; Above query requires only k0 not meta().id, so the projection is not nil and Indexer will ignore distinct=true. If indexer implements that we can pushdown.
          Hide
          siri Sriram Melkote added a comment - - edited

          We do not need to ignore projection when distinct=true. Further, we provide fully accurate distinct. We've put that logic to ignore distinct when project is non null, as we were requested to do so. We can turn it off (as long as we don't expect project to run before distinct).

          Show
          siri Sriram Melkote added a comment - - edited We do not need to ignore projection when distinct=true. Further, we provide fully accurate distinct. We've put that logic to ignore distinct when project is non null, as we were requested to do so. We can turn it off (as long as we don't expect project to run before distinct).

            People

            • Assignee:
              Sitaram.Vemulapalli Sitaram Vemulapalli
              Reporter:
              Sitaram.Vemulapalli Sitaram Vemulapalli
            • 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

                  Error rendering 'com.pagerduty.jira-server-plugin:PagerDuty'. Please contact your Jira administrators.