Details
-
Bug
-
Resolution: Fixed
-
Major
-
4.5.0
-
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":
,
"index": "coveringindexwithlimit1",
"index_id": "6ff9b72fcae7a56e",
"keyspace": "default",
"namespace": "default",
"spans": [
{
"Range":
},
{
"Range":
},
{
"Range":
}
],
"using": "gsi"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
,
{
"#operator": "InitialProject",
"result_terms": [
]
},
]
}
}
]
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":
},
{
"Range":
},
{
"Range":
},
{
"Range":
},
{
"Range":
},
{
"Range":
}
],
"using": "view"
}
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
,
,
{
"#operator": "InitialProject",
"result_terms": [
,
{ "as": "skills", "expr": "((`default`.`skills`)[0])" } ]
}
]
}
}
]
},
{
"#operator": "Order",
"limit": "15",
"offset": "0",
"sort_terms": [
]
},
,
,
{ "#operator": "FinalProject" }]
Bucket in this test has 2016 documents. Example:
{
"tasks_points":
,
"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": "windows", "name": "vm_11", "memory": 10 } ],
"job_title": "Engineer"
}
Attachments
For Gerrit Dashboard: MB-19623 | ||||||
---|---|---|---|---|---|---|
# | Subject | Branch | Project | Status | CR | V |
64045,4 | MB-19623 View Index giving wrong results for a specific query | master | query | Status: MERGED | +2 | +1 |