Hello! I am trying to perform a LEFT OUTER JOIN while filtering on the right part of the join.
I have created the following index to achieve this:
CREATE INDEX `idx_store_order` ON `myBucket`(("Store::" || `storeId`))
WHERE ((`docType` = "Order") or (`docType` is missing))
and I am trying to execute the following query:
SELECT store.status, order.userId, store.docId
FROM myBucket store
LEFT OUTER JOIN myBucket order ON KEY ("Store::" || order.storeId) FOR store
WHERE store.docType="Store"
AND (order.docType="Order" OR order.docType IS MISSING)
AND order.clientId="9281ae36-a418-4ea3-93f0-bfd7b1a38248"
I have 30 documents with docType="Store", but when I perform this query I don’t get the 30 results. If I remove the last clause and group by store, then I get the 30 results, so it’s the last clause that affects the final results.
I have also tried the following statement (unsucessfully) as the last clause:
(AND order.clientId="9281ae36-a418-4ea3-93f0-bfd7b1a38248" OR order.docType IS MISSING)
Here is the EXPLAIN clause for the latter:
[
{
"plan": {
"#operator": "Sequence",
"~children": [
{
"#operator": "IndexScan",
"index": "idx_docType",
"index_id": "e498d0c0ee2f0d9d",
"keyspace": "myBucket",
"namespace": "default",
"spans": [
{
"Range": {
"High": [
"\"Store\""
],
"Inclusion": 3,
"Low": [
"\"Store\""
]
}
}
],
"using": "gsi"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Fetch",
"as": "store",
"keyspace": "myBucket",
"namespace": "default"
},
{
"#operator": "IndexJoin",
"as": "order",
"for": "store",
"keyspace": "myBucket",
"namespace": "default",
"on_key": "(\"Store::\" || (`order`.`storeId`))",
"outer": true,
"scan": {
"index": "idx_store_order",
"index_id": "a97fce5158e6e573",
"using": "gsi"
}
},
{
"#operator": "Filter",
"condition": "((((`store`.`docType`) = \"Store\") and (((`order`.`docType`) = \"Order\") or ((`order`.`docType`) is missing))) and (((`order`.`clientId`) = \"9281ae36-a418-4ea3-93f0-bfd7b1a138248\") or (`order` is missing)))"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "(`store`.`status`)"
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
},
"text": "SELECT store.status\nFROM myBucket store\nLEFT OUTER JOIN myBucket order ON KEY (\"Store::\" || order.storeId) FOR store\nWHERE store.docType=\"Store\"\nAND (order.docType=\"Order\" OR order.docType IS MISSING)\nAND (order.clientId=\"9281ae36-a418-4ea3-93f0-bfd7b1a138248\" OR order IS MISSING)"
}
]
Am I missing something? Why am I not getting the expected results? Let me know if you need more info. Thank you!
