Hi. I have two queries with exactly same predicate but different select clause.
Query 1 is giving 88 documents while Query 2 is giving 12 documents.
Query 1 is also supposed to give 12 documents only but it seems like the documents which satisfy one of the conditions in the any clause are being returned for some weird reason that I can’t figure out.
Query 1:
Select visits
From reporting
where type = 'Person'
and meta().id not like '_sync%'
and any v in visits satisfies v.departmentCode = 'HS' and Date_format_str(v.startDate, '1111-11-11') = '2018-01-30' END
;
Query 2:
Select *
From reporting
where type = 'Person'
and meta().id not like '_sync%'
and any v in visits satisfies v.departmentCode = 'HS' and Date_format_str(v.startDate, '1111-11-11') = '2018-01-30' END
;
Indexes:
* CREATE INDEX `IX_reporting_type` ON `reporting`(`type`)
* CREATE INDEX `IX_reporting_visits_name_Person` ON `reporting`((distinct (array (`vs1`.`departmentCode`) for `vs1` in `visits` end)),`visits`,`firstName`,`lastName`) WHERE (`type` = "Person")
Explain plan for Query 1:
[
{
"plan": {
"#operator": "Sequence",
"~children": [
{
"#operator": "DistinctScan",
"scan": {
"#operator": "IndexScan",
"covers": [
"cover ((distinct (array (`vs1`.`departmentCode`) for `vs1` in (`reporting`.`visits`) end)))",
"cover ((`reporting`.`visits`))",
"cover ((`reporting`.`firstName`))",
"cover ((`reporting`.`lastName`))",
"cover ((meta(`reporting`).`id`))"
],
"filter_covers": {
"cover ((`reporting`.`type`))": "Person",
"cover (any `v` in (`reporting`.`visits`) satisfies (((`v`.`departmentCode`) = \"HS\") and (date_format_str((`v`.`startDate`), \"1111-11-11\") = \"2018-01-30\")) end)": true
},
"index": "IX_reporting_visits_name_Person",
"index_id": "bbc6d70efbb056d3",
"keyspace": "reporting",
"namespace": "default",
"spans": [
{
"Exact": true,
"Range": {
"High": [
"successor(\"HS\")"
],
"Inclusion": 1,
"Low": [
"\"HS\""
]
}
}
],
"using": "gsi"
}
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "(((cover ((`reporting`.`type`)) = \"Person\") and (not (cover ((meta(`reporting`).`id`)) like \"_sync%\"))) and cover (any `v` in (`reporting`.`visits`) satisfies (((`v`.`departmentCode`) = \"HS\") and (date_format_str((`v`.`startDate`), \"1111-11-11\") = \"2018-01-30\")) end))"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "cover ((`reporting`.`visits`))"
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
},
"text": "Select visits\nFrom reporting \nwhere type = 'Person' \nand meta().id not like '_sync%'\nand any v in visits satisfies v.departmentCode = 'HS' and Date_format_str(v.startDate, '1111-11-11') = '2018-01-30' END\n;"
}
]
Explain Plan for Query 2
[
{
"plan": {
"#operator": "Sequence",
"~children": [
{
"#operator": "IntersectScan",
"scans": [
{
"#operator": "DistinctScan",
"scan": {
"#operator": "IndexScan",
"index": "IX_reporting_visits_name_Person",
"index_id": "bbc6d70efbb056d3",
"keyspace": "reporting",
"namespace": "default",
"spans": [
{
"Exact": true,
"Range": {
"High": [
"successor(\"HS\")"
],
"Inclusion": 1,
"Low": [
"\"HS\""
]
}
}
],
"using": "gsi"
}
},
{
"#operator": "IndexScan",
"index": "IX_reporting_type",
"index_id": "9c3932bb42876a94",
"keyspace": "reporting",
"namespace": "default",
"spans": [
{
"Exact": true,
"Range": {
"High": [
"\"Person\""
],
"Inclusion": 3,
"Low": [
"\"Person\""
]
}
}
],
"using": "gsi"
},
{
"#operator": "IndexScan",
"index": "IX_primary_reporting",
"index_id": "ddd6145403987936",
"keyspace": "reporting",
"namespace": "default",
"spans": [
{
"Range": {
"Inclusion": 0,
"Low": [
"null"
]
}
}
],
"using": "gsi"
}
]
},
{
"#operator": "Fetch",
"keyspace": "reporting",
"namespace": "default"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "((((`reporting`.`type`) = \"Person\") and (not ((meta(`reporting`).`id`) like \"_sync%\"))) and any `v` in (`reporting`.`visits`) satisfies (((`v`.`departmentCode`) = \"HS\") and (date_format_str((`v`.`startDate`), \"1111-11-11\") = \"2018-01-30\")) end)"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "self",
"star": true
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
},
"text": "Select *\nFrom reporting \nwhere type = 'Person' \nand meta().id not like '_sync%'\nand any v in visits satisfies v.departmentCode= 'HS' and Date_format_str(v.startDate, '1111-11-11') = '2018-01-30' END\n;"
}
]