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

N1QL: Incorrect results for queries using intersect scan as compared to same query using primary index

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.0.0
    • Fix Version/s: 4.6.2, 5.0.0
    • Component/s: query
    • Environment:
      4.7.0-1505
    • Triage:
      Untriaged
    • Is this a Regression?:
      Yes

      Description

      This is a  regression from 4.6

      {noformat}

      Test run:

      python testrunner.py -i query3.ini -c conf/tuq/py-tuq-index.conf -p doc-per-day=1 -p reload_data=True -p nodes_init=3

       

      Use a 3 node cluster.Example query3.ini:

      global]

      port:8091

      username:root

      password:couchbase

       

      [servers]

      1:vm1

      2:vm2

      3:vm3

       

       

      [vm1]

      ip:172.23.121.127

      services:kv,index,n1ql

       

       

      [vm2]

      ip:172.23.122.194

      services:kv,index,n1ql

       

      [vm3]

      ip:172.23.105.173

      services:kv,index,n1ql

       

      [membase]

      rest_username:Administrator

      rest_password:password

      [tuq_client]

      goroot:/root/n1ql/go

      gopath:/root/n1ql/gocode

       

      This test creates indexes and uses intersect scan for few queries and compares the results to desired results.

      Example query:

      SELECT skills, VMs FROM default WHERE skills is not null AND VMs is not missing

       

      Explain plan:

      "plan": {
      "#operator": "Sequence",
      "~children": [
      {
      "#operator": "IntersectScan",
      "scans": [
      {
      "#operator": "IndexScan",
      "index": "inter_index_1146VMs",
      "index_id": "673c54d63c1c51c1",
      "keyspace": "default",
      "namespace": "default",
      "spans": [
      {
      "Range": {
      "Inclusion": 1,
      "Low": [
      "null"
      ]
      }
      }
      ],
      "using": "gsi"
      },
      {
      "#operator": "IndexScan",
      "index": "inter_index_1146skills",
      "index_id": "37ea861e2051a8bf",
      "keyspace": "default",
      "namespace": "default",
      "spans": [
      {
      "Range": {
      "Inclusion": 0,
      "Low": [
      "null"
      ]
      }
      }
      ],
      "using": "gsi"
      }
      ]
      },
      {
      "#operator": "Fetch",
      "keyspace": "default",
      "namespace": "default"
      },
      {
      "#operator": "Parallel",
      "~child": {
      "#operator": "Sequence",
      "~children": [
      {
      "#operator": "Filter",
      "condition": "(((`default`.`skills`) is not null) and ((`default`.`VMs`) is not missing))"
      },
      {
      "#operator": "InitialProject",
      "result_terms": [
      {
      "expr": "(`default`.`skills`)"
      },
      {
      "expr": "(`default`.`VMs`)"
      }
      ]
      },
      {
      "#operator": "FinalProject"
      }
      ]
      }
      }
      ]

       

      The results of above query does not match with the results from same query using primary index.Uploading dataset also.

      {noformat}

       

        Attachments

          Issue Links

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

            Activity

            Hide
            prathibha Prathibha Bisarahalli added a comment -

            Thanks for simplified repro Prerna. Again, I see that GSI returns right results that is 2016, but n1ql shows random results count like 1859, 1994, 2006 etc. 

            From indexer log:

            PrathibaMacsMBP:ns_server prathibha-mac$ grep -R " RESPONSE rows:" logs/n_0/indexer.log
            logs/n_0/indexer.log:2016-12-23T08:49:18.262+05:30 [Verbose] SCAN##2 RESPONSE rows:2016, waitTime:1.887948ms...
            logs/n_0/indexer.log:2016-12-23T08:49:18.270+05:30 [Verbose] SCAN##3 RESPONSE rows:2016, waitTime:1.947032ms...
            logs/n_0/indexer.log:2016-12-23T08:51:26.457+05:30 [Verbose] SCAN##4 RESPONSE rows:2016, waitTime:1.426191ms...
            logs/n_0/indexer.log:2016-12-23T08:51:34.742+05:30 [Verbose] SCAN##5 RESPONSE rows:2016, waitTime:1.399379ms...
            logs/n_0/indexer.log:2016-12-23T08:51:57.065+05:30 [Verbose] SCAN##7 RESPONSE rows:2016, waitTime:2.877401ms...
            logs/n_0/indexer.log:2016-12-23T08:51:57.073+05:30 [Verbose] SCAN##6 RESPONSE rows:2016, waitTime:2.86429ms...
            logs/n_0/indexer.log:2016-12-23T08:52:02.256+05:30 [Verbose] SCAN##9 RESPONSE rows:2016, waitTime:2.221576ms...
            logs/n_0/indexer.log:2016-12-23T08:52:02.263+05:30 [Verbose] SCAN##8 RESPONSE rows:2016, waitTime:2.2604ms...
            logs/n_0/indexer.log:2016-12-23T08:52:03.831+05:30 [Verbose] SCAN##10 RESPONSE rows:2016, waitTime:2.375559ms...
            logs/n_0/indexer.log:2016-12-23T08:52:03.838+05:30 [Verbose] SCAN##11 RESPONSE rows:2016, waitTime:2.282083ms...

            Again this is the same observation as in MB-22085. Please also re-run with 1482 build which does not contain any indexing changes. 

            Show
            prathibha Prathibha Bisarahalli added a comment - Thanks for simplified repro Prerna. Again, I see that GSI returns right results that is 2016, but n1ql shows random results count like 1859, 1994, 2006 etc.  From indexer log: PrathibaMacsMBP:ns_server prathibha-mac$ grep -R " RESPONSE rows:" logs/n_0/indexer.log logs/n_0/indexer.log:2016-12-23T08:49:18.262+05:30 [Verbose] SCAN##2 RESPONSE rows:2016, waitTime:1.887948ms... logs/n_0/indexer.log:2016-12-23T08:49:18.270+05:30 [Verbose] SCAN##3 RESPONSE rows:2016, waitTime:1.947032ms... logs/n_0/indexer.log:2016-12-23T08:51:26.457+05:30 [Verbose] SCAN##4 RESPONSE rows:2016, waitTime:1.426191ms... logs/n_0/indexer.log:2016-12-23T08:51:34.742+05:30 [Verbose] SCAN##5 RESPONSE rows:2016, waitTime:1.399379ms... logs/n_0/indexer.log:2016-12-23T08:51:57.065+05:30 [Verbose] SCAN##7 RESPONSE rows:2016, waitTime:2.877401ms... logs/n_0/indexer.log:2016-12-23T08:51:57.073+05:30 [Verbose] SCAN##6 RESPONSE rows:2016, waitTime:2.86429ms... logs/n_0/indexer.log:2016-12-23T08:52:02.256+05:30 [Verbose] SCAN##9 RESPONSE rows:2016, waitTime:2.221576ms... logs/n_0/indexer.log:2016-12-23T08:52:02.263+05:30 [Verbose] SCAN##8 RESPONSE rows:2016, waitTime:2.2604ms... logs/n_0/indexer.log:2016-12-23T08:52:03.831+05:30 [Verbose] SCAN##10 RESPONSE rows:2016, waitTime:2.375559ms... logs/n_0/indexer.log:2016-12-23T08:52:03.838+05:30 [Verbose] SCAN##11 RESPONSE rows:2016, waitTime:2.282083ms... Again this is the same observation as in  MB-22085 . Please also re-run with 1482 build which does not contain any indexing changes. 
            Show
            gerald Gerald Sangudi (Inactive) added a comment - Fixed by http://review.couchbase.org/#/c/71257/  
            Hide
            build-team Couchbase Build Team added a comment -

            Build 4.7.0-1525 contains query commit 15619cd87c13997a98dfb471f041ae68f13a6576 with commit message:
            MB-22087. Fix regression in intersect scan.
            https://github.com/couchbase/query/commit/15619cd87c13997a98dfb471f041ae68f13a6576

            Show
            build-team Couchbase Build Team added a comment - Build 4.7.0-1525 contains query commit 15619cd87c13997a98dfb471f041ae68f13a6576 with commit message: MB-22087 . Fix regression in intersect scan. https://github.com/couchbase/query/commit/15619cd87c13997a98dfb471f041ae68f13a6576
            Hide
            build-team Couchbase Build Team added a comment -

            Build 4.6.1-3822 contains query commit 99473054777bad92de07ea307692fb96cb49469e with commit message:
            MB-22087. Fix regression in intersect scan.
            https://github.com/couchbase/query/commit/99473054777bad92de07ea307692fb96cb49469e

            Show
            build-team Couchbase Build Team added a comment - Build 4.6.1-3822 contains query commit 99473054777bad92de07ea307692fb96cb49469e with commit message: MB-22087 . Fix regression in intersect scan. https://github.com/couchbase/query/commit/99473054777bad92de07ea307692fb96cb49469e
            Hide
            Sitaram.Vemulapalli Sitaram Vemulapalli added a comment -

            add 4.6.1

            Show
            Sitaram.Vemulapalli Sitaram Vemulapalli added a comment - add 4.6.1

              People

              • Assignee:
                Prerna.Manaktala Prerna Manaktala (Inactive)
                Reporter:
                Prerna.Manaktala Prerna Manaktala (Inactive)
              • 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

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