rubenada commented on code in PR #5370:
URL: https://github.com/apache/calcite/pull/5370#discussion_r4457990453
##########
core/src/test/java/org/apache/calcite/test/RecursiveCteTest.java:
##########
@@ -105,6 +116,232 @@ class RecursiveCteTest {
assertThat(((SqlWithItem) with.withList.get(1)).recursive.booleanValue(),
is(true));
}
+ /** SEARCH follows AS (query); parsing and unparsing preserve its mode and
keys. */
+ @ParameterizedTest
+ @CsvSource(delimiter = '|', value = {
+ "DEPTH|n", "BREADTH|n", "DEPTH|n, depth", "BREADTH|n, depth"
+ })
+ void testSearchClause(String mode, String keys) throws SqlParseException {
+ final String sql = "WITH RECURSIVE t(n, depth) AS (VALUES (1, 0)\n"
+ + "UNION ALL SELECT n + 1, depth + 1 FROM t WHERE n < 3)\n"
+ + "SEARCH " + mode + " FIRST BY " + keys + " SET seq\n"
+ + "SELECT n FROM t ORDER BY seq";
+ final SqlNode parsed = SqlParser.create(sql).parseQuery();
+ final SqlWith with = (SqlWith) ((SqlOrderBy) parsed).query;
+ final SqlSearchClause search =
+ requireNonNull(((SqlWithItem) with.withList.get(0)).searchClause);
+ assertThat(search.getKind(), is(SqlKind.SEARCH_CLAUSE));
+ assertThat(search.breadthFirst.booleanValue(), is(mode.equals("BREADTH")));
+ assertThat(search.orderColumn.getSimple(), is("SEQ"));
+ assertThat(search.columns, hasSize(keys.equals("n") ? 1 : 2));
+ assertThat(search.getParserPosition().getLineNum(), is(3));
+ final String unparsed =
parsed.toSqlString(CalciteSqlDialect.DEFAULT).getSql();
+ final String quotedKeys = keys.equals("n") ? "\"N\"" : "\"N\", \"DEPTH\"";
+ assertThat(Pattern.compile("\\s+").matcher(unparsed).replaceAll(" "),
+ containsString("SEARCH " + mode + " FIRST BY " + quotedKeys + " SET
\"SEQ\""));
+ final SqlNode roundTrip = SqlParser.create(unparsed).parseQuery();
+ assertThat(roundTrip.toSqlString(CalciteSqlDialect.DEFAULT).getSql(),
is(unparsed));
+ }
+
+ /** SEARCH creates a sort key; ORDER BY requests the corresponding row
order. */
+ @Test void testSearchDepthFirst() {
+ CalciteAssert.that()
+ .query(WITH_TREE + "SEARCH DEPTH FIRST BY node SET seq\n"
+ + "SELECT node FROM walk ORDER BY seq")
+ .returnsOrdered("NODE=0", "NODE=1", "NODE=4", "NODE=2", "NODE=3");
+ }
+
+ /** Within a level, BY keys take precedence over the parents' order. */
+ @Test void testSearchBreadthFirst() {
+ CalciteAssert.that()
+ .query(WITH_TREE + "SEARCH BREADTH FIRST BY node SET seq\n"
+ + "SELECT node FROM walk ORDER BY seq")
+ .returnsOrdered("NODE=0", "NODE=1", "NODE=2", "NODE=3", "NODE=4");
+ }
+
+ @ParameterizedTest
+ @ValueSource(strings = {"DEPTH", "BREADTH"})
+ void testSearchCompositeKey(String mode) {
+ CalciteAssert.that()
+ .query("WITH RECURSIVE t(n, k, depth) AS (VALUES (0, 0, 0)\n"
+ + "UNION ALL SELECT v.n, v.k, t.depth + 1\n"
+ + "FROM t CROSS JOIN (VALUES (1, 2), (2, 1), (1, 1)) AS v(n, k)\n"
+ + "WHERE t.depth < 1)\n"
+ + "SEARCH " + mode + " FIRST BY k, n SET seq\n"
+ + "SELECT n, k FROM t ORDER BY seq")
+ .returnsOrdered("N=0; K=0", "N=1; K=1", "N=2; K=1", "N=1; K=2");
+ }
+
+ @ParameterizedTest
+ @ValueSource(strings = {"DEPTH", "BREADTH"})
+ void testSearchFollowingWithItem(String mode) {
+ CalciteAssert.that()
+ .query(WITH_NUMBERS + "SEARCH " + mode + " FIRST BY n SET seq,\n"
+ + "u AS (SELECT * FROM t) SELECT n FROM u ORDER BY seq")
+ .returnsOrdered("N=1", "N=2", "N=3");
+ }
+
+ @ParameterizedTest
+ @ValueSource(strings = {"DEPTH", "BREADTH"})
+ void testSearchQuotedNames(String mode) {
+ CalciteAssert.that()
+ .query("WITH RECURSIVE \"Walk\"(\"Node\") AS (VALUES (1)\n"
+ + "UNION ALL SELECT \"Node\" + 1 FROM \"Walk\" WHERE \"Node\" <
3)\n"
+ + "SEARCH " + mode + " FIRST BY \"Node\" SET \"Sequence\"\n"
+ + "SELECT \"Node\" FROM \"Walk\" ORDER BY \"Sequence\"")
+ .returnsOrdered("Node=1", "Node=2", "Node=3");
+ }
+
+ @ParameterizedTest
+ @ValueSource(strings = {"DEPTH", "BREADTH"})
+ void testSearchEmptySeed(String mode) {
+ CalciteAssert.that()
+ .query("WITH RECURSIVE t(n) AS (SELECT n FROM (VALUES (1)) AS v(n)
WHERE FALSE\n"
+ + "UNION ALL SELECT n + 1 FROM t WHERE n < 3)\n"
+ + "SEARCH " + mode + " FIRST BY n SET seq\n"
+ + "SELECT * FROM t ORDER BY seq")
+ .returnsCount(0);
+ }
+
+ /** SEARCH precedes CYCLE, which stops expansion of the repeated node. */
+ @ParameterizedTest
+ @ValueSource(strings = {"DEPTH", "BREADTH"})
+ void testSearchWithCycle(String mode) {
+ CalciteAssert.that()
+ .query("WITH RECURSIVE t(n, depth) AS (VALUES (1, 0)\n"
+ + "UNION ALL SELECT MOD(n + 1, 3), depth + 1 FROM t WHERE depth <
5)\n"
+ + "SEARCH " + mode + " FIRST BY n SET seq\n"
+ + "CYCLE n SET c TO 'Y' DEFAULT 'N' USING p\n"
+ + "SELECT n, c, CARDINALITY(p) AS path_length FROM t ORDER BY seq")
+ .returnsOrdered("N=1; C=N; PATH_LENGTH=1", "N=2; C=N; PATH_LENGTH=2",
+ "N=0; C=N; PATH_LENGTH=3", "N=1; C=Y; PATH_LENGTH=4");
+ }
+
+ @ParameterizedTest
+ @CsvSource({"DEPTH, UNION ALL", "BREADTH, UNION ALL",
+ "DEPTH, UNION", "BREADTH, UNION"})
+ void testSearchMultipleRoots(String mode, String union) {
+ CalciteAssert.that()
+ .query("WITH RECURSIVE t(n) AS (VALUES (2), (1)\n"
+ + union + " SELECT n + 2 FROM t WHERE n < 3)\n"
+ + "SEARCH " + mode + " FIRST BY n SET seq\n"
+ + "SELECT n FROM t ORDER BY seq")
+ .returnsOrdered(mode.equals("DEPTH")
+ ? new String[]{"N=1", "N=3", "N=2", "N=4"}
+ : new String[]{"N=1", "N=2", "N=3", "N=4"});
+ }
+
+ @ParameterizedTest
+ @ValueSource(strings = {"DEPTH", "BREADTH"})
+ void testSearchNullKey(String mode) {
Review Comment:
Can we have this test with NULL key combined with `ORDER BY seq ASC / DESC
NULLS FIRST / LAST` ?
--
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]
