Major Description
|
|
Post from :
|
http://stackoverflow.com/questions/36063384/couchbase-order-by-retuning-null-results
|
|
|
|
|
create primary index on default;
|
|
|
insert into default values("c001", {
|
"p_brand1": "MFGR#1121",
|
"lineorder": [{
|
"lo_revenue": 4282453,
|
"orderdate": [{ "d_year": 1992}],
|
"supplier": [{ "s_region": "AMERICA" }]
|
}] });
|
|
|
|
|
SELECT l.lo_revenue as revenue, o.d_year, p.p_brand1 from (SELECT p_brand1, lineorder from default where p_brand1='MFGR#1121') as p UNNEST p.lineorder l UNNEST l.supplier s UNNEST l.orderdate o where s.s_region='AMERICA' order by o.d_year;
|
|
|
|
|
Above query returns empty result set. If you remove order by it works fine.
|
|
|
explain SELECT l.lo_revenue as revenue, o.d_year, p.p_brand1 from (SELECT p_brand1, lineorder from default where p_brand1='MFGR#1121') as p UNNEST p.lineorder l UNNEST l.supplier s UNNEST l.orderdate o where s.s_region='AMERICA' order by o.d_year;
|
{
|
"requestID": "05334684-cc17-4cf0-9364-5f107b48dde4",
|
"signature": "json",
|
"results": [
|
{
|
"plan": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "PrimaryScan",
|
"index": "#primary",
|
"keyspace": "default",
|
"namespace": "default",
|
"using": "gsi"
|
},
|
{
|
"#operator": "Parallel",
|
"~child": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "Fetch",
|
"keyspace": "default",
|
"namespace": "default"
|
},
|
{
|
"#operator": "Filter",
|
"condition": "((`default`.`p_brand1`) = \"MFGR#1121\")"
|
},
|
{
|
"#operator": "InitialProject",
|
"result_terms": [
|
{
|
"expr": "(`default`.`p_brand1`)"
|
},
|
{
|
"expr": "(`default`.`lineorder`)"
|
}
|
]
|
}
|
]
|
}
|
}
|
]
|
},
|
{
|
"#operator": "Alias",
|
"as": "p"
|
},
|
{
|
"#operator": "Parallel",
|
"~child": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "Unnest",
|
"as": "l",
|
"expr": "(`p`.`lineorder`)"
|
},
|
{
|
"#operator": "Unnest",
|
"as": "s",
|
"expr": "(`l`.`supplier`)"
|
},
|
{
|
"#operator": "Unnest",
|
"as": "o",
|
"expr": "(`l`.`orderdate`)"
|
},
|
{
|
"#operator": "Filter",
|
"condition": "((`s`.`s_region`) = \"AMERICA\")"
|
},
|
{
|
"#operator": "InitialProject",
|
"result_terms": [
|
{
|
"as": "revenue",
|
"expr": "(`l`.`lo_revenue`)"
|
},
|
{
|
"expr": "(`o`.`d_year`)"
|
},
|
{
|
"expr": "(`p`.`p_brand1`)"
|
}
|
]
|
}
|
]
|
}
|
}
|
]
|
},
|
{
|
"#operator": "Order",
|
"sort_terms": [
|
{
|
"expr": "(`o`.`d_year`)"
|
}
|
]
|
},
|
{
|
"#operator": "FinalProject"
|
}
|
]
|
},
|
"text": "SELECT l.lo_revenue as revenue, o.d_year, p.p_brand1 from (SELECT p_brand1, lineorder from default where p_brand1='MFGR#1121') as p UNNEST p.lineorder l UNNEST l.supplier s UNNEST l.orderdate o where s.s_region='AMERICA' order by o.d_year"
|
}
|
],
|
"status": "success",
|
"metrics": {
|
"elapsedTime": "1.747152ms",
|
"executionTime": "1.723018ms",
|
"resultCount": 1,
|
"resultSize": 5717
|
}
|
}
|
|
|
|