vldpyatkov commented on code in PR #13479:
URL: https://github.com/apache/ignite/pull/13479#discussion_r3926528403
##########
modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/RecursiveCteIntegrationTest.java:
##########
@@ -43,18 +96,209 @@ public void testHierarchicalQueryIsNotSupported() {
"FROM employee e " +
"JOIN employee_hierarchy h ON e.manager_id = h.id" +
") " +
- "SELECT id, manager_id, name, depth FROM employee_hierarchy ORDER
BY depth, id";
+ "SELECT id, manager_id, name, depth FROM employee_hierarchy";
+
+ String plan = (String)sql("EXPLAIN PLAN FOR " + qry).get(0).get(0);
+
+ assertTrue(plan, plan.contains("IgniteRepeatUnion"));
+ assertTrue(plan, plan.contains("IgniteRecursiveTableScan"));
+ assertFalse(plan, plan.contains("IgniteRecursiveTableSpool"));
+ }
- Throwable err = GridTestUtils.assertThrows(
- log,
- () -> sql(qry),
+ /** */
+ @Test
+ public void testRecursiveTermWithoutSelfReferenceAfterOptimization() {
+ assertQuery("WITH RECURSIVE numbers(n) AS (" +
+ "SELECT 1 " +
+ "UNION ALL " +
+ "SELECT n + 1 FROM numbers WHERE FALSE" +
+ ") " +
+ "SELECT n FROM numbers")
+ .returns(1)
+ .check();
+ }
+
+ /** */
+ @Test
+ public void testRecursiveTermWithMultipleSelfReferencesIsRejected() {
+ assertThrows(
+ "WITH RECURSIVE numbers(n) AS (" +
+ "SELECT 1 " +
+ "UNION ALL " +
+ "SELECT left_numbers.n + 1 " +
+ "FROM numbers left_numbers " +
+ "JOIN numbers right_numbers ON left_numbers.n =
right_numbers.n " +
+ "WHERE left_numbers.n < 3" +
+ ") " +
+ "SELECT n FROM numbers",
+ IgniteSQLException.class,
+ "the recursive term must contain no more than one self-reference"
+ );
+ }
+
+ /** */
+ @Test
+ public void testRecursiveCteWithMultipleRecursiveBranchesIsRejected() {
+ assertThrows(
+ "WITH RECURSIVE numbers(n) AS (" +
+ "SELECT 1 " +
+ "UNION ALL " +
+ "(" +
+ "SELECT n + 1 FROM numbers WHERE n < 3 " +
+ "UNION ALL " +
+ "SELECT n + 10 FROM numbers WHERE n < 3" +
+ ")" +
+ ") " +
+ "SELECT n FROM numbers",
IgniteSQLException.class,
- "Failed to plan query"
+ "the recursive term must contain no more than one self-reference"
);
+ }
+
+ /** */
+ @Test
+ public void testRecursiveCteWithDistinctUnionIsRejected() {
+ assertThrows(
+ "WITH RECURSIVE numbers(n) AS (" +
+ "SELECT 1 " +
+ "UNION " +
+ "SELECT n + 1 FROM numbers WHERE n < 3" +
+ ") " +
+ "SELECT n FROM numbers",
+ IgniteSQLException.class,
+ "only UNION ALL is supported"
+ );
+ }
+
+ /** */
+ @Test
+ public void testStateIsIsolatedBetweenSameNamedRecursiveCtes() {
+ assertQuery("SELECT /*+ MERGE_JOIN */ l.n, r.n " +
+ "FROM (" +
+ "WITH RECURSIVE numbers(n) AS (" +
+ "SELECT 1 " +
+ "UNION ALL " +
+ "SELECT n + 1 FROM numbers WHERE n < 3" +
+ ") " +
+ "SELECT n, n + 9 AS join_key FROM numbers" +
+ ") l " +
+ "JOIN (" +
+ "WITH RECURSIVE numbers(n) AS (" +
+ "SELECT 10 " +
+ "UNION ALL " +
+ "SELECT n + 1 FROM numbers WHERE n < 12" +
+ ") " +
+ "SELECT n, n - 9 AS join_key FROM numbers" +
+ ") r ON l.join_key = r.n")
+ .matches(QueryChecker.containsSubPlan("IgniteMergeJoin"))
+ .returns(1, 10)
+ .returns(2, 11)
+ .returns(3, 12)
+ .check();
+ }
+
+ /** */
+ @Test
+ public void
testIndependentNonDeterministicSubtreeIsEvaluatedForEveryIteration() {
+ registerRecursiveFunctions();
+
+ nonDeterministicCallCnt.set(0);
+
+ String qry = "WITH RECURSIVE numbers(n, marker) AS (" +
+ "SELECT 1, 0 " +
+ "UNION ALL " +
+ "SELECT n + 1, v.marker " +
+ "FROM numbers " +
+ "CROSS JOIN (SELECT nextRecursiveValue() AS marker) v " +
+ "WHERE n < 4" +
+ ") " +
+ "SELECT n, marker FROM numbers ORDER BY n";
+
+ assertQuery(qry)
+ .returns(1, 0)
+ .returns(2, 1)
+ .returns(3, 2)
+ .returns(4, 3)
+ .check();
+ }
+
+ /** */
+ @Test
+ public void testNonDeterministicTableScanIsNotMaterialized() {
Review Comment:
Moved.
##########
modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/RecursiveCteIntegrationTest.java:
##########
@@ -43,18 +96,209 @@ public void testHierarchicalQueryIsNotSupported() {
"FROM employee e " +
"JOIN employee_hierarchy h ON e.manager_id = h.id" +
") " +
- "SELECT id, manager_id, name, depth FROM employee_hierarchy ORDER
BY depth, id";
+ "SELECT id, manager_id, name, depth FROM employee_hierarchy";
+
+ String plan = (String)sql("EXPLAIN PLAN FOR " + qry).get(0).get(0);
+
+ assertTrue(plan, plan.contains("IgniteRepeatUnion"));
+ assertTrue(plan, plan.contains("IgniteRecursiveTableScan"));
+ assertFalse(plan, plan.contains("IgniteRecursiveTableSpool"));
+ }
- Throwable err = GridTestUtils.assertThrows(
- log,
- () -> sql(qry),
+ /** */
+ @Test
+ public void testRecursiveTermWithoutSelfReferenceAfterOptimization() {
+ assertQuery("WITH RECURSIVE numbers(n) AS (" +
+ "SELECT 1 " +
+ "UNION ALL " +
+ "SELECT n + 1 FROM numbers WHERE FALSE" +
+ ") " +
+ "SELECT n FROM numbers")
+ .returns(1)
+ .check();
+ }
+
+ /** */
+ @Test
+ public void testRecursiveTermWithMultipleSelfReferencesIsRejected() {
+ assertThrows(
+ "WITH RECURSIVE numbers(n) AS (" +
+ "SELECT 1 " +
+ "UNION ALL " +
+ "SELECT left_numbers.n + 1 " +
+ "FROM numbers left_numbers " +
+ "JOIN numbers right_numbers ON left_numbers.n =
right_numbers.n " +
+ "WHERE left_numbers.n < 3" +
+ ") " +
+ "SELECT n FROM numbers",
+ IgniteSQLException.class,
+ "the recursive term must contain no more than one self-reference"
+ );
+ }
+
+ /** */
+ @Test
+ public void testRecursiveCteWithMultipleRecursiveBranchesIsRejected() {
+ assertThrows(
+ "WITH RECURSIVE numbers(n) AS (" +
+ "SELECT 1 " +
+ "UNION ALL " +
+ "(" +
+ "SELECT n + 1 FROM numbers WHERE n < 3 " +
+ "UNION ALL " +
+ "SELECT n + 10 FROM numbers WHERE n < 3" +
+ ")" +
+ ") " +
+ "SELECT n FROM numbers",
IgniteSQLException.class,
- "Failed to plan query"
+ "the recursive term must contain no more than one self-reference"
);
+ }
+
+ /** */
+ @Test
+ public void testRecursiveCteWithDistinctUnionIsRejected() {
+ assertThrows(
+ "WITH RECURSIVE numbers(n) AS (" +
+ "SELECT 1 " +
+ "UNION " +
+ "SELECT n + 1 FROM numbers WHERE n < 3" +
+ ") " +
+ "SELECT n FROM numbers",
+ IgniteSQLException.class,
+ "only UNION ALL is supported"
+ );
+ }
+
+ /** */
+ @Test
+ public void testStateIsIsolatedBetweenSameNamedRecursiveCtes() {
+ assertQuery("SELECT /*+ MERGE_JOIN */ l.n, r.n " +
+ "FROM (" +
+ "WITH RECURSIVE numbers(n) AS (" +
+ "SELECT 1 " +
+ "UNION ALL " +
+ "SELECT n + 1 FROM numbers WHERE n < 3" +
+ ") " +
+ "SELECT n, n + 9 AS join_key FROM numbers" +
+ ") l " +
+ "JOIN (" +
+ "WITH RECURSIVE numbers(n) AS (" +
+ "SELECT 10 " +
+ "UNION ALL " +
+ "SELECT n + 1 FROM numbers WHERE n < 12" +
+ ") " +
+ "SELECT n, n - 9 AS join_key FROM numbers" +
+ ") r ON l.join_key = r.n")
+ .matches(QueryChecker.containsSubPlan("IgniteMergeJoin"))
+ .returns(1, 10)
+ .returns(2, 11)
+ .returns(3, 12)
+ .check();
+ }
+
+ /** */
+ @Test
+ public void
testIndependentNonDeterministicSubtreeIsEvaluatedForEveryIteration() {
+ registerRecursiveFunctions();
+
+ nonDeterministicCallCnt.set(0);
+
+ String qry = "WITH RECURSIVE numbers(n, marker) AS (" +
+ "SELECT 1, 0 " +
+ "UNION ALL " +
+ "SELECT n + 1, v.marker " +
+ "FROM numbers " +
+ "CROSS JOIN (SELECT nextRecursiveValue() AS marker) v " +
+ "WHERE n < 4" +
+ ") " +
+ "SELECT n, marker FROM numbers ORDER BY n";
+
+ assertQuery(qry)
+ .returns(1, 0)
+ .returns(2, 1)
+ .returns(3, 2)
+ .returns(4, 3)
+ .check();
+ }
+
+ /** */
+ @Test
+ public void testNonDeterministicTableScanIsNotMaterialized() {
+ registerRecursiveFunctions();
+
+ sql("CREATE TABLE recursive_markers (id INT) WITH
TEMPLATE=REPLICATED");
+ sql("INSERT INTO recursive_markers VALUES (1)");
+
+ String qry = "WITH RECURSIVE numbers(n, marker) AS (" +
+ "SELECT 1, 0 " +
+ "UNION ALL " +
+ "SELECT n + 1, v.marker " +
+ "FROM numbers " +
+ "CROSS JOIN (" +
+ "SELECT nextRecursiveValue() AS marker FROM recursive_markers"
+
+ ") v " +
+ "WHERE n < 4" +
+ ") " +
+ "SELECT n, marker FROM numbers ORDER BY n";
+
+ String plan = (String)sql("EXPLAIN PLAN FOR " + qry).get(0).get(0);
+
+ assertTrue(plan, plan.contains("IgniteTableScan"));
+ assertTrue(plan, plan.contains("NEXTRECURSIVEVALUE"));
+ assertFalse(plan, plan.contains("IgniteTableSpool"));
+ }
+
+ /** */
+ @Test
+ public void testNonDeterministicIndexScanIsNotMaterialized() {
Review Comment:
Moved.
--
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]
