Details
-
Improvement
-
Resolution: Fixed
-
Major
-
7.1.0
-
1
Description
https://forums.couchbase.com/t/n1ql-sort-by-via-place-holder/30285
Place holders are not allowed in ORDER BY expression, direction, nulls placement.
If one used ORDER BY $1 DESC. Place holders are constants. This becomes ORDER BY constant DESC. This NO OP because ORDER BY constant across results (duplicates).
These are needed for Optimizer plan purpose, use index order.
Indirectly we allow dynamic fields in ORDER BY fields (i.e. can be place holders). In that case no index order will be used, covering is not possible (i.e. all index pushdowns are off).
SELECT *
FROM default AS d
WHERE …
ORDER BY d.[$1] DESC NULLS LAST OFFSET $2 LIMIT $3;
$1 must be string of field in the document, if not string it raises error.
Is it possible DESC, ASC, NULLS LAST|FIRST (at least ASC, DESC) can be place holders. These can't use index order, Optimizer really doesn't depend on these values.
FYI: How Dynamic fields works? ex : d.[d.b]
After dot(.) array brackets starts it is dynamic field
|
It evaluates expression with in the array brackets. It must be evaluated to string.
|
Then string is consider as filed name and retrieve the field name value
|
In the following example
|
first document d.[d.b] ====> d.["a"] ===> d.`a` ===> 10
|
second document d.[d.b] ====> d.["c"] ===> d.`c` ===> 20
|
|
SELECT d.[d.b] AS val FROM [{"a":10, "b":"a", "c": 100}, {"a":100, "b":"c", "c":20}] AS d;
|
"results": [
|
{
|
"val": 10
|
},
|
{
|
"val": 20
|
}
|
]
|
Normally ORDER BY you need sort same fields across query results. you can use named/query parameter as dynamic field.