Query over indexed bucket returns no result

SQL++
1

Hello, I have a query like this:

SELECT Extent1.document.payload as result FROM H2H as Extent1
WHERE (Extent1.type = ‘ATCore.ReadModel.ODC_RM’)
AND ((upper((((Extent1.document).payload).H2H_EQUIP).PERSONRESPONSIBLE) = ‘FOO’))

Result count says 12.

Then I create a INDEX like this:
CREATE INDEX index_odc_pr ON H2H(upper(((((Extent1.document).payload).H2H_EQUIP).PERSONRESPONSIBLE)))

Now the same query returns 0 results.

What am I missing?
Couchbase Version: 4.6.0-3573 Enterprise Edition (build-3573)

thank you
diego

2

When you do an EXPLAIN, what do you get? Does it claim to be using the correct index?

3

this is the EXPLAIN before and after the index created.

As further investigation I define te index without the ‘Extent1’ part because this property does not exist in any document, but linq2couchbase defines in generated query.

CREATE INDEX index_odc_pr ON H2H(upper((((document.payload).H2H_EQUIP).PERSONRESPONSIBLE)))

and the query returns correct result.

EXPLAIN SELECT Extent1.document.payload as result FROM H2H as Extent1
WHERE (Extent1.type = ‘ATCore.ReadModel.ODC_RM’)
AND ((upper((((Extent1.document).payload).H2H_EQUIP).PERSONRESPONSIBLE) = ‘FOO’))

{
“requestID”: “df71d79c-05d1-4abf-8c60-9a7dbdc3061d”,
“clientContextID”: “c5010a8e-15e6-4d45-80a2-53fa551eae5f”,
“signature”: “json”,
“results”: [
{
“plan”: {
"#operator": “Sequence”,
"~children": [
{
"#operator": “PrimaryScan”,
“index”: “H2H”,
“keyspace”: “H2H”,
“namespace”: “default”,
“using”: “gsi”
},
{
"#operator": “Fetch”,
“as”: “Extent1”,
“keyspace”: “H2H”,
“namespace”: “default”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Filter”,
“condition”: “(((Extent1.type) = “ATCore.ReadModel.ODC_RM”) and (upper(((((Extent1.document).payload).H2H_EQUIP).PERSONRESPONSIBLE)) = “DBROCCHIAD”))”
},
{
"#operator": “InitialProject”,
“result_terms”: [
{
“as”: “result”,
“expr”: “((Extent1.document).payload)”
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
},
“text”: “SELECT Extent1.document.payload as result FROM H2H as Extent1 \nWHERE (Extent1.type = ‘ATCore.ReadModel.ODC_RM’) \nAND ((upper((((Extent1.document).payload).H2H_EQUIP).PERSONRESPONSIBLE) = ‘DBROCCHIAD’))”
}
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “2ms”,
“executionTime”: “2ms”,
“resultCount”: 1,
“resultSize”: 2086
}
}

after the index created:
{
“requestID”: “66062f57-b6e9-4f0b-ab22-38d573bea0ff”,
“clientContextID”: “ce865c11-4f0b-420c-8e70-e8a128b27345”,
“signature”: “json”,
“results”: [
{
“plan”: {
"#operator": “Sequence”,
"~children": [
{
"#operator": “IndexScan”,
“index”: “index_odc_pr”,
“index_id”: “dc02462f9de70905”,
“keyspace”: “H2H”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
"“FOO”"
],
“Inclusion”: 3,
“Low”: [
"“FOO”"
]
}
}
],
“using”: “gsi”
},
{
"#operator": “Fetch”,
“as”: “Extent1”,
“keyspace”: “H2H”,
“namespace”: “default”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Filter”,
“condition”: “(((Extent1.type) = “ATCore.ReadModel.ODC_RM”) and (upper(((((Extent1.document).payload).H2H_EQUIP).PERSONRESPONSIBLE)) = “FOO”))”
},
{
"#operator": “InitialProject”,
“result_terms”: [
{
“as”: “result”,
“expr”: “((Extent1.document).payload)”
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
},
“text”: “SELECT Extent1.document.payload as result FROM H2H as Extent1 \nWHERE (Extent1.type = ‘ATCore.ReadModel.ODC_RM’) \nAND ((upper((((Extent1.document).payload).H2H_EQUIP).PERSONRESPONSIBLE) = ‘FOO’))”
}
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “7.994ms”,
“executionTime”: “6.9821ms”,
“resultCount”: 1,
“resultSize”: 2682
}
}

4

The new index is right and plan looks fine. Did you still have issue?

5

Hello,
I don’t have issue anymore.

The execution plans refer to previously defined index (the one with ‘Extent1’ property) and seems ok to me, because it says that correct index will be processed during query.
In spite of that the result was empty.

The problem was the index defined with ‘Extent1’ in expression.

thank you

6

Execution plan will refer the full qualified name due to alias in the from clause. As Index creation doesn’t have aliases and index key or index conditions needs to start with top level attributes in the document.