Details
-
Bug
-
Resolution: Duplicate
-
Critical
-
Cheshire-Cat
-
Untriaged
-
1
-
Yes
Description
Query involving unnest or nest and with presence of array index results in: "
syntax error - at )"
To repro:
- create default bucket
- create primary index on default
- CREATE INDEX `nested_idx_attr_nest` ON `default`((all (array (distinct (array (`j`.`region1`) for `j` in (`i`.`Marketing`) end)) for `i` in `tasks` end)),`tasks`,`name`);
- explain SELECT emp.name FROM default emp UNNEST emp.tasks as i UNNEST i.Marketing as j where j.region1 = 'South' ;
with index you will see:
cbq> explain SELECT emp.name FROM default emp UNNEST emp.tasks as i UNNEST i.Marketing as j where j.region1 = 'South' ; |
|
|
{
|
"requestID": "abe0bd5f-a71a-48ba-b2bf-98409ce80911", |
"errors": [ |
{
|
"code": 4000, |
"msg": "syntax error - at )" |
}
|
],
|
"status": "fatal", |
"metrics": { |
"elapsedTime": "2.471211ms", |
"executionTime": "2.423361ms", |
"resultCount": 0, |
"resultSize": 0, |
"serviceLoad": 6, |
"errorCount": 1 |
}
|
}
|
without index works fine:
cbq> explain SELECT emp.name FROM default emp UNNEST emp.tasks as i UNNEST i.Marketing as j where j.region1 = 'South' ; |
{
|
"requestID": "7de7d0a3-69fc-4459-916c-6bef31370c70", |
"signature": "json", |
"results": [ |
{
|
"plan": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "PrimaryScan3", |
"as": "emp", |
"index": "#primary", |
"index_projection": { |
"primary_key": true |
},
|
"keyspace": "default", |
"namespace": "default", |
"using": "gsi" |
},
|
{
|
"#operator": "Fetch", |
"as": "emp", |
"keyspace": "default", |
"namespace": "default" |
},
|
{
|
"#operator": "Parallel", |
"~child": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "Filter", |
"condition": "is_array((`emp`.`tasks`))" |
},
|
{
|
"#operator": "Unnest", |
"as": "i", |
"expr": "(`emp`.`tasks`)", |
"filter": "(`i` is not missing)" |
}
|
]
|
}
|
},
|
{
|
"#operator": "Parallel", |
"~child": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "Unnest", |
"as": "j", |
"expr": "(`i`.`Marketing`)", |
"filter": "((`j`.`region1`) = \"South\")" |
}
|
]
|
}
|
},
|
{
|
"#operator": "Parallel", |
"~child": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "InitialProject", |
"result_terms": [ |
{
|
"expr": "(`emp`.`name`)" |
}
|
]
|
}
|
]
|
}
|
}
|
]
|
},
|
"text": "SELECT emp.name FROM default emp UNNEST emp.tasks as i UNNEST i.Marketing as j where j.region1 = 'South';" |
}
|
],
|
"status": "success", |
"metrics": { |
"elapsedTime": "1.713098ms", |
"executionTime": "1.660996ms", |
"resultCount": 1, |
"resultSize": 2689, |
"serviceLoad": 6 |
}
|
}
|