zstan commented on code in PR #13761:
URL: https://github.com/apache/ignite/pull/13761#discussion_r4444293638
##########
modules/calcite/pom.xml:
##########
@@ -451,6 +451,14 @@
value="<FETCH> ( <FIRST> |
<NEXT> ) offsetFetch[1] = FetchCount()"
failOnNoReplacements="true"
/>
+ <!-- TODO:
https://issues.apache.org/jira/browse/IGNITE-29207
Review Comment:
So it need to be removed after 1.43 update isn\`t it ? If so you need to
clarify it in issue and here plz ?
##########
modules/calcite/src/main/codegen/includes/parserImpls.ftl:
##########
@@ -932,6 +932,58 @@ SqlNode SqlSelectForUpdate() :
{ return query; }
}
+// TODO: https://issues.apache.org/jira/browse/IGNITE-29207 Use Calcite's
CYCLE grammar after upgrading.
Review Comment:
what expected to be done here after version change ? If I were to change the
version - I don\`t know what to do with this comment (
##########
docs/_docs/SQL/sql-calcite.adoc:
##########
@@ -20,6 +20,77 @@ Apache Calcite is a dynamic data management framework, which
mainly serves for m
The current H2-based SQL engine has a number of fundamental limitations of
query execution in a distributed environment. To address these limitations, a
new SQL engine was implemented. The new engine uses tools provided by Apache
Calcite for parsing and planning queries. It also has a new query execution
flow.
+== Recursive Common Table Expressions
+
+A recursive common table expression (CTE) defines a temporary named query that
can refer to itself.
+Use it to traverse hierarchies or graphs, or to generate a sequence of values.
+A recursive CTE combines an initial query (the seed) and a recursive query
using `UNION ALL` or `UNION`.
+
+[source,sql]
+----
+WITH RECURSIVE numbers(n) AS (
+ VALUES (1)
+ UNION ALL
+ SELECT n + 1 FROM numbers WHERE n < 3
+)
+SELECT n FROM numbers ORDER BY n;
+----
+
+This query returns `1`, `2`, and `3`. The column list `(n)` names the column
produced by the CTE.
+The seed runs once and produces `1`. Each recursive step reads only the rows
produced by the previous step,
+not the entire accumulated result: `1` produces `2`, then `2` produces `3`.
+The condition `n < 3` is evaluated before computing `n + 1`, so `3` is
returned but produces no further rows.
+Recursion stops when a step produces no new rows. Use an outer `ORDER BY` when
result order matters.
+
+`UNION ALL` retains duplicates. `UNION` removes duplicate rows from the seed
and subsequent steps,
+including rows already present in the accumulated result. Duplicate comparison
uses the entire CTE row;
+changing a depth or path column can therefore prevent otherwise repeated keys
from being eliminated.
+For repeated-key detection along a path, use `CYCLE` as described below.
+
+=== Iteration Limit
+
+The distributed property `sql.calcite.recursiveCteIterationLimit` limits
recursive execution to `100` iterations
+by default. If execution reaches the limit without terminating, the query
fails with an iteration-limit error.
+A negative value disables the limit. This limit also applies to CTEs with
`CYCLE`.
+
+For example, set a limit of `1000` iterations using the
link:tools/control-script[control script]:
+
+[source,shell]
+----
+control.sh --property set --name sql.calcite.recursiveCteIterationLimit --val
1000
Review Comment:
Common code for such cases need to be like :
```
[tabs]
--
tab:Unix[]
[source,shell]
----
control.sh --property set --name ...
----
tab:Windows[]
[source,shell]
----
control.bat --property set --name ...
----
--
```
##########
modules/calcite/src/main/java/org/apache/calcite/sql/validate/IgniteWithItemNamespace.java:
##########
@@ -0,0 +1,34 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.calcite.sql.validate;
+
+import org.apache.calcite.sql.SqlNode;
+import org.apache.calcite.sql.SqlWithItem;
+import org.jetbrains.annotations.Nullable;
+
+/** Makes Calcite's package-private WITH namespace available when registering
a rewritten CYCLE item. */
+public class IgniteWithItemNamespace extends WithItemNamespace {
+ // TODO: https://issues.apache.org/jira/browse/IGNITE-29207 Remove with
Ignite's CYCLE registration hook.
Review Comment:
Not clear what need to do here (
##########
modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/IgniteSqlFunctions.java:
##########
@@ -45,6 +49,73 @@
* Ignite SQL functions.
*/
public class IgniteSqlFunctions {
+ /** Calcite 1.42 exposes compareListItems through this comparator, despite
its Comparable generic bound. */
+ @SuppressWarnings({"rawtypes", "unchecked"})
+ private static final Comparator<Object> CYCLE_KEY_COMPARATOR =
(Comparator)Functions.nullsComparator(false, false);
+
+ /**
+ * Returns whether the path contains a SQL-equal key tuple. UNKNOWN does
not close a cycle.
+ * Ported from CALCITE-7814; do not replace row equality with Java deep
equality for nullable keys.
+ */
+ public static boolean cyclePathContains(List<?> path, Object key) {
+ // TODO: https://issues.apache.org/jira/browse/IGNITE-29207 Use
SqlFunctions.cyclePathContains after upgrading.
Review Comment:
Seems we need a strict plan - how 1.43 upgrade will be, can you fill an
issue ?
##########
docs/_docs/SQL/sql-calcite.adoc:
##########
@@ -20,6 +20,77 @@ Apache Calcite is a dynamic data management framework, which
mainly serves for m
The current H2-based SQL engine has a number of fundamental limitations of
query execution in a distributed environment. To address these limitations, a
new SQL engine was implemented. The new engine uses tools provided by Apache
Calcite for parsing and planning queries. It also has a new query execution
flow.
+== Recursive Common Table Expressions
+
+A recursive common table expression (CTE) defines a temporary named query that
can refer to itself.
+Use it to traverse hierarchies or graphs, or to generate a sequence of values.
+A recursive CTE combines an initial query (the seed) and a recursive query
using `UNION ALL` or `UNION`.
+
+[source,sql]
+----
+WITH RECURSIVE numbers(n) AS (
+ VALUES (1)
+ UNION ALL
+ SELECT n + 1 FROM numbers WHERE n < 3
+)
+SELECT n FROM numbers ORDER BY n;
+----
+
+This query returns `1`, `2`, and `3`. The column list `(n)` names the column
produced by the CTE.
+The seed runs once and produces `1`. Each recursive step reads only the rows
produced by the previous step,
+not the entire accumulated result: `1` produces `2`, then `2` produces `3`.
+The condition `n < 3` is evaluated before computing `n + 1`, so `3` is
returned but produces no further rows.
+Recursion stops when a step produces no new rows. Use an outer `ORDER BY` when
result order matters.
+
+`UNION ALL` retains duplicates. `UNION` removes duplicate rows from the seed
and subsequent steps,
+including rows already present in the accumulated result. Duplicate comparison
uses the entire CTE row;
+changing a depth or path column can therefore prevent otherwise repeated keys
from being eliminated.
+For repeated-key detection along a path, use `CYCLE` as described below.
+
+=== Iteration Limit
+
+The distributed property `sql.calcite.recursiveCteIterationLimit` limits
recursive execution to `100` iterations
+by default. If execution reaches the limit without terminating, the query
fails with an iteration-limit error.
+A negative value disables the limit. This limit also applies to CTEs with
`CYCLE`.
+
+For example, set a limit of `1000` iterations using the
link:tools/control-script[control script]:
+
+[source,shell]
+----
+control.sh --property set --name sql.calcite.recursiveCteIterationLimit --val
1000
+----
+
+On Windows, use `control.bat` instead of `control.sh`.
+
+=== Cycle Detection
+
+Use `CYCLE` after a recursive CTE definition to detect repeated keys along
each path:
+
+[source,sql]
+----
+WITH RECURSIVE walk(n) AS (
+ VALUES (1)
+ UNION ALL
+ SELECT MOD(n + 1, 3) FROM walk
+)
+CYCLE n SET is_cycle USING path
Review Comment:
it can be 'path' or 'path_array' here ? can you give additional clue cause
it seems not a sql standard .. and extended doc is helpful
##########
docs/_docs/SQL/sql-calcite.adoc:
##########
@@ -20,6 +20,77 @@ Apache Calcite is a dynamic data management framework, which
mainly serves for m
The current H2-based SQL engine has a number of fundamental limitations of
query execution in a distributed environment. To address these limitations, a
new SQL engine was implemented. The new engine uses tools provided by Apache
Calcite for parsing and planning queries. It also has a new query execution
flow.
+== Recursive Common Table Expressions
+
+A recursive common table expression (CTE) defines a temporary named query that
can refer to itself.
+Use it to traverse hierarchies or graphs, or to generate a sequence of values.
+A recursive CTE combines an initial query (the seed) and a recursive query
using `UNION ALL` or `UNION`.
+
+[source,sql]
+----
+WITH RECURSIVE numbers(n) AS (
+ VALUES (1)
+ UNION ALL
+ SELECT n + 1 FROM numbers WHERE n < 3
+)
+SELECT n FROM numbers ORDER BY n;
+----
+
+This query returns `1`, `2`, and `3`. The column list `(n)` names the column
produced by the CTE.
+The seed runs once and produces `1`. Each recursive step reads only the rows
produced by the previous step,
+not the entire accumulated result: `1` produces `2`, then `2` produces `3`.
+The condition `n < 3` is evaluated before computing `n + 1`, so `3` is
returned but produces no further rows.
+Recursion stops when a step produces no new rows. Use an outer `ORDER BY` when
result order matters.
+
+`UNION ALL` retains duplicates. `UNION` removes duplicate rows from the seed
and subsequent steps,
+including rows already present in the accumulated result. Duplicate comparison
uses the entire CTE row;
+changing a depth or path column can therefore prevent otherwise repeated keys
from being eliminated.
+For repeated-key detection along a path, use `CYCLE` as described below.
+
+=== Iteration Limit
+
+The distributed property `sql.calcite.recursiveCteIterationLimit` limits
recursive execution to `100` iterations
+by default. If execution reaches the limit without terminating, the query
fails with an iteration-limit error.
+A negative value disables the limit. This limit also applies to CTEs with
`CYCLE`.
+
+For example, set a limit of `1000` iterations using the
link:tools/control-script[control script]:
+
+[source,shell]
+----
+control.sh --property set --name sql.calcite.recursiveCteIterationLimit --val
1000
+----
+
+On Windows, use `control.bat` instead of `control.sh`.
+
+=== Cycle Detection
+
+Use `CYCLE` after a recursive CTE definition to detect repeated keys along
each path:
+
+[source,sql]
+----
+WITH RECURSIVE walk(n) AS (
+ VALUES (1)
+ UNION ALL
+ SELECT MOD(n + 1, 3) FROM walk
+)
+CYCLE n SET is_cycle USING path
Review Comment:
More accurate example as for me ...
```
WITH RECURSIVE path (a, b) AS (
SELECT edges.* FROM edges
WHERE a = 1
UNION ALL
SELECT edges.* FROM edges
JOIN path
ON edges.a = path.b
)
CYCLE a
SET cycl TO 'Y' DEFAULT 'N'
USING path_array
SELECT *
FROM path
WHERE cycl = 'N'
```
##########
docs/_docs/SQL/sql-calcite.adoc:
##########
@@ -20,6 +20,77 @@ Apache Calcite is a dynamic data management framework, which
mainly serves for m
The current H2-based SQL engine has a number of fundamental limitations of
query execution in a distributed environment. To address these limitations, a
new SQL engine was implemented. The new engine uses tools provided by Apache
Calcite for parsing and planning queries. It also has a new query execution
flow.
+== Recursive Common Table Expressions
+
+A recursive common table expression (CTE) defines a temporary named query that
can refer to itself.
+Use it to traverse hierarchies or graphs, or to generate a sequence of values.
+A recursive CTE combines an initial query (the seed) and a recursive query
using `UNION ALL` or `UNION`.
+
+[source,sql]
+----
+WITH RECURSIVE numbers(n) AS (
+ VALUES (1)
+ UNION ALL
+ SELECT n + 1 FROM numbers WHERE n < 3
+)
+SELECT n FROM numbers ORDER BY n;
+----
+
+This query returns `1`, `2`, and `3`. The column list `(n)` names the column
produced by the CTE.
+The seed runs once and produces `1`. Each recursive step reads only the rows
produced by the previous step,
+not the entire accumulated result: `1` produces `2`, then `2` produces `3`.
+The condition `n < 3` is evaluated before computing `n + 1`, so `3` is
returned but produces no further rows.
+Recursion stops when a step produces no new rows. Use an outer `ORDER BY` when
result order matters.
+
+`UNION ALL` retains duplicates. `UNION` removes duplicate rows from the seed
and subsequent steps,
+including rows already present in the accumulated result. Duplicate comparison
uses the entire CTE row;
+changing a depth or path column can therefore prevent otherwise repeated keys
from being eliminated.
+For repeated-key detection along a path, use `CYCLE` as described below.
+
+=== Iteration Limit
+
+The distributed property `sql.calcite.recursiveCteIterationLimit` limits
recursive execution to `100` iterations
+by default. If execution reaches the limit without terminating, the query
fails with an iteration-limit error.
+A negative value disables the limit. This limit also applies to CTEs with
`CYCLE`.
+
+For example, set a limit of `1000` iterations using the
link:tools/control-script[control script]:
+
+[source,shell]
+----
+control.sh --property set --name sql.calcite.recursiveCteIterationLimit --val
1000
+----
+
+On Windows, use `control.bat` instead of `control.sh`.
Review Comment:
need to be removed
--
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]
