vldpyatkov commented on code in PR #5370:
URL: https://github.com/apache/calcite/pull/5370#discussion_r4529801171
##########
site/_docs/reference.md:
##########
@@ -562,6 +563,56 @@ MATCH_CONDITION left_table.timecol <= right_table.timecol
ON left_table.col = right_table.col
```
+### Search ordering in recursive queries
+
+`SEARCH` generates a column that can be used to order the output of a recursive
+query. `SEARCH DEPTH FIRST` orders a node before its descendants and those
+descendants before the next sibling. `SEARCH BREADTH FIRST` orders by depth,
+then by the specified columns within each level. The columns in `BY` are
+compared in their listed order, with null values sorting last.
+
+For example:
+
+{% highlight sql %}
+WITH RECURSIVE
+ edges(src, dst) AS (VALUES (0, 2), (0, 1), (1, 4), (2, 3)),
+ walk(node) AS (
+ VALUES (0)
+ UNION ALL
+ SELECT e.dst FROM walk AS w JOIN edges AS e ON e.src = w.node
+ ) SEARCH DEPTH FIRST BY node SET search_order
+SELECT node FROM walk ORDER BY search_order;
+{% endhighlight %}
+
+This returns `0, 1, 4, 2, 3`. Changing `DEPTH` to `BREADTH` returns
+`0, 1, 2, 3, 4`. `SEARCH` does not itself sort the result or control the
+physical evaluation order; use an explicit `ORDER BY` on the generated column.
+Rows with equal search keys have no guaranteed relative order.
+
+The depth-first column is an array of key rows representing the path. The
+breadth-first column is a row containing the depth (starting at zero) and the
Review Comment:
I don't want to publish the $DEPTH field, because it requires supporting the
field in the feature. And we already shared that the depth is the first in the
in-depth-first column.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
