Major Description
INSERT INTO default VALUES (UUID(), {"provider_uuid": "p2", "provider_apps_uuid": ["ap1","ap2","ap4"],"type": "app-provider" , "sub_type":"provider-extra"}); |
|
|
WITH orderobj AS ({"us":1, "gb":2, "es":3, "fr":4}) |
SELECT p.provider_uuid, orderobj
|
FROM `default` AS p |
WHERE p.type = "app-provider" AND p.sub_type = "provider-extra" AND p.provider_uuid = "p2"; |
|
|
WITH orderobj AS ({"us":1, "gb":2, "es":3, "fr":4}) |
SELECT p.provider_uuid, orderobj
|
FROM (SELECT d.provider_uuid
|
FROM `default` AS d |
WHERE d.type = "app-provider" AND d.sub_type = "provider-extra" AND d.provider_uuid = "p2") AS p; |
When executed first query you will have orderobj, not in second query (MISSING field).
https://forums.couchbase.com/t/how-can-you-use-array-v-in-v-for-array-end-as-where-clause/29964/27
explain WITH orderobj AS ({"us":1, "gb":2, "es":3, "fr":4}) SELECT p.provider_uuid, orderobj FROM (SELECT d.provider_uuid FROM `default` AS d WHERE d.type = "app-provider" AND d.sub_type = "provider-extra" AND d.provider_uuid = "p2") AS p; |
{
|
"requestID": "8a686d34-9178-4560-9a92-08b9d0c2bdad", |
"signature": "json", |
"results": [ |
{
|
"plan": { |
"#operator": "With", |
"bindings": [ |
{
|
"expr": "{\"es\": 3, \"fr\": 4, \"gb\": 2, \"us\": 1}", |
"static": true, |
"var": "orderobj" |
}
|
],
|
"~child": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "IndexScan3", |
"as": "d", |
"covers": [ |
"cover ((`d`.`provider_uuid`))", |
"cover ((`d`.`provider_apps_uuid`))", |
"cover ((meta(`d`).`id`))" |
],
|
"filter": "((cover ((`d`.`type`)) = \"app-provider\") and (cover ((`d`.`sub_type`)) = \"provider-extra\") and (cover ((`d`.`provider_uuid`)) = \"p2\"))", |
"filter_covers": { |
"cover ((`d`.`sub_type`))": "provider-extra", |
"cover ((`d`.`type`))": "app-provider" |
},
|
"index": "ix2", |
"index_id": "1c52cf840f050e91", |
"index_projection": { |
"entry_keys": [ |
0 |
]
|
},
|
"keyspace": "default", |
"namespace": "default", |
"spans": [ |
{
|
"exact": true, |
"range": [ |
{
|
"high": "\"p2\"", |
"inclusion": 3, |
"low": "\"p2\"" |
}
|
]
|
}
|
],
|
"using": "gsi" |
},
|
{
|
"#operator": "Parallel", |
"~child": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "InitialProject", |
"result_terms": [ |
{
|
"expr": "cover ((`d`.`provider_uuid`))" |
}
|
]
|
}
|
]
|
}
|
}
|
]
|
},
|
{
|
"#operator": "Alias", |
"as": "p" |
},
|
{
|
"#operator": "Parallel", |
"~child": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "InitialProject", |
"result_terms": [ |
{
|
"expr": "(`p`.`provider_uuid`)" |
},
|
{
|
"expr": "`orderobj`" |
}
|
]
|
}
|
]
|
}
|
}
|
]
|
}
|
},
|
"text": "WITH orderobj AS ({\"us\":1, \"gb\":2, \"es\":3, \"fr\":4}) SELECT p.provider_uuid, orderobj FROM (SELECT d.provider_uuid FROM `default` AS d WHERE d.type = \"app-provider\" AND d.sub_type = \"provider-extra\" AND d.provider_uuid = \"p2\") AS p;" |
}
|
],
|
"status": "success", |
"metrics": { |
"elapsedTime": "3.270138ms", |
"executionTime": "3.193246ms", |
"resultCount": 1, |
"resultSize": 4551, |
"serviceLoad": 2 |
}
|
}
|
|
Subquery needs parent CTE variables because of correlation. FROM clause subquery Alias only takes the document from the subquery projected. It is not added the CTE variables from current level (might need scope value as parent during alias).
Attachments
Issue Links
- links to
