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

View Index giving wrong results for a specific query

    XMLWordPrintable

Details

    • Bug
    • Resolution: Fixed
    • Major
    • 4.5.0
    • 4.5.0
    • query
    • 4.5.0-2514
    • Untriaged
    • Yes

    Description

      Test which failed:
      ./testrunner -i test.ini -t tuqquery.tuq_2i_index.QueriesIndexTests.test_covering_orderby_limit,covering_index=true,doc-per-day=1,skip_index=True,index_type=view

      What the test does?
      Index created:
      CREATE INDEX coveringindexwithlimit0 ON default(skills[0], join_yr, VMs[0].os,name) where join_yr =2010 USING view

      Query:
      explain select name,skills[0] as skills from default where skills[0]='skill2010' and join_yr=2010 and ( VMs[0].os IN ['ubuntu','windows','linux'] OR VMs[0].os IN ['ubuntu','windows','linux'] ) order by _id asc LIMIT 10 OFFSET 0;

      Explain gives right result, query uses right index:
      "plan": {
      "#operator": "Sequence",
      "~children": [
      {
      "#operator": "IndexScan",
      "covers": [
      "cover (((`default`.`skills`)[0]))",
      "cover ((`default`.`join_yr`))",
      "cover ((((`default`.`VMs`)[0]).`os`))",
      "cover ((`default`.`name`))",
      "cover ((meta(`default`).`id`))"
      ],
      "filter_covers":

      { "cover ((`default`.`join_yr`))": 2010 }

      ,
      "index": "coveringindexwithlimit1",
      "index_id": "6ff9b72fcae7a56e",
      "keyspace": "default",
      "namespace": "default",
      "spans": [
      {
      "Range":

      { "High": [ "\"skill2010\"", "2010", "\"linux\"" ], "Inclusion": 3, "Low": [ "\"skill2010\"", "2010", "\"linux\"" ] }

      },
      {
      "Range":

      { "High": [ "\"skill2010\"", "2010", "\"ubuntu\"" ], "Inclusion": 3, "Low": [ "\"skill2010\"", "2010", "\"ubuntu\"" ] }

      },
      {
      "Range":

      { "High": [ "\"skill2010\"", "2010", "\"windows\"" ], "Inclusion": 3, "Low": [ "\"skill2010\"", "2010", "\"windows\"" ] }

      }
      ],
      "using": "gsi"
      },
      {
      "#operator": "Parallel",
      "~child": {
      "#operator": "Sequence",
      "~children": [

      { "#operator": "Filter", "condition": "(((cover (((`default`.`skills`)[0])) = \"skill2010\") and (cover ((`default`.`join_yr`)) = 2010)) and (cover ((((`default`.`VMs`)[0]).`os`)) in [\"ubuntu\", \"windows\", \"linux\"]))" }

      ,
      {
      "#operator": "InitialProject",
      "result_terms": [

      { "expr": "cover ((`default`.`name`))" }

      ]
      },

      { "#operator": "FinalProject" }

      ]
      }
      }
      ]

      But the select query above gives 0 results.
      Same query with gsi index gives 15 results.

      Same test when run against beta build gives 15 results for view and gsi index.
      Explain query for beta build:
      "plan": {
      "#operator": "Sequence",
      "~children": [
      {
      "#operator": "Sequence",
      "~children": [
      {
      "#operator": "DistinctScan",
      "scan": {
      "#operator": "IndexScan",
      "index": "idx",
      "index_id": "idx",
      "keyspace": "default",
      "namespace": "default",
      "spans": [
      {
      "Range":

      { "High": [ "\"skill2010\"" ], "Inclusion": 3, "Low": [ "\"skill2010\"" ] }

      },
      {
      "Range":

      { "High": [ "\"skill2010\"" ], "Inclusion": 3, "Low": [ "\"skill2010\"" ] }

      },
      {
      "Range":

      { "High": [ "\"skill2010\"" ], "Inclusion": 3, "Low": [ "\"skill2010\"" ] }

      },
      {
      "Range":

      { "High": [ "\"skill2010\"" ], "Inclusion": 3, "Low": [ "\"skill2010\"" ] }

      },
      {
      "Range":

      { "High": [ "\"skill2010\"" ], "Inclusion": 3, "Low": [ "\"skill2010\"" ] }

      },
      {
      "Range":

      { "High": [ "\"skill2010\"" ], "Inclusion": 3, "Low": [ "\"skill2010\"" ] }

      }
      ],
      "using": "view"
      }
      },
      {
      "#operator": "Parallel",
      "~child": {
      "#operator": "Sequence",
      "~children": [

      { "#operator": "Fetch", "keyspace": "default", "namespace": "default" }

      ,

      { "#operator": "Filter", "condition": "(((((`default`.`skills`)[0]) = \"skill2010\") and ((`default`.`join_yr`) = 2010)) and (((((`default`.`VMs`)[0]).`os`) in [\"ubuntu\", \"windows\", \"linux\"]) or ((((`default`.`VMs`)[0]).`os`) in [\"ubuntu\", \"windows\", \"linux\"])))" }

      ,
      {
      "#operator": "InitialProject",
      "result_terms": [

      { "expr": "(`default`.`name`)" }

      ,

      { "as": "skills", "expr": "((`default`.`skills`)[0])" }

      ]
      }
      ]
      }
      }
      ]
      },
      {
      "#operator": "Order",
      "limit": "15",
      "offset": "0",
      "sort_terms": [

      { "expr": "(`default`.`name`)" }

      ]
      },

      { "#operator": "Offset", "expr": "0" }

      ,

      { "#operator": "Limit", "expr": "15" }

      ,

      { "#operator": "FinalProject" }

      ]

      Bucket in this test has 2016 documents. Example:
      {
      "tasks_points":

      { "task1": 1, "task2": 1 }

      ,
      "name": "employee-9",
      "mutated": 0,
      "skills": [
      "skill2010",
      "skill2011"
      ],
      "join_day": 9,
      "join_mo": 10,
      "email": "9-mail@couchbase.com",
      "test_rate": 10.1,
      "join_yr": 2011,
      "_id": "query-testemployee10153.1877827-0",
      "VMs": [

      { "RAM": 10, "os": "ubuntu", "name": "vm_10", "memory": 10 }

      ,

      { "RAM": 10, "os": "windows", "name": "vm_11", "memory": 10 }

      ],
      "job_title": "Engineer"
      }

      Attachments

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

        Activity

          People

            Sitaram.Vemulapalli Sitaram Vemulapalli
            Prerna.Manaktala Prerna Manaktala (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Gerrit Reviews

                There are no open Gerrit changes

                PagerDuty