You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@spark.apache.org by ya...@apache.org on 2020/04/15 23:36:16 UTC
[spark] branch branch-3.0 updated: [SPARK-31428][SQL][DOCS]
Document Common Table Expression in SQL Reference
This is an automated email from the ASF dual-hosted git repository.
yamamuro pushed a commit to branch branch-3.0
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/branch-3.0 by this push:
new 4476c85 [SPARK-31428][SQL][DOCS] Document Common Table Expression in SQL Reference
4476c85 is described below
commit 4476c85775d231c8bb26399284c0baf4292bec7c
Author: Huaxin Gao <hu...@us.ibm.com>
AuthorDate: Thu Apr 16 08:34:26 2020 +0900
[SPARK-31428][SQL][DOCS] Document Common Table Expression in SQL Reference
### What changes were proposed in this pull request?
Document Common Table Expression in SQL Reference
### Why are the changes needed?
Make SQL Reference complete
### Does this PR introduce any user-facing change?
Yes
<img width="1050" alt="Screen Shot 2020-04-13 at 12 06 35 AM" src="https://user-images.githubusercontent.com/13592258/79100257-f61def00-7d1a-11ea-8402-17017059232e.png">
<img width="1050" alt="Screen Shot 2020-04-13 at 12 07 09 AM" src="https://user-images.githubusercontent.com/13592258/79100260-f7e7b280-7d1a-11ea-9408-058c0851f0b6.png">
<img width="1050" alt="Screen Shot 2020-04-13 at 12 07 35 AM" src="https://user-images.githubusercontent.com/13592258/79100262-fa4a0c80-7d1a-11ea-8862-eb1d8960296b.png">
Also link to Select page
<img width="1045" alt="Screen Shot 2020-04-12 at 4 14 30 PM" src="https://user-images.githubusercontent.com/13592258/79082246-217fea00-7cd9-11ea-8d96-1a69769d1e19.png">
### How was this patch tested?
Manually build and check
Closes #28196 from huaxingao/cte.
Authored-by: Huaxin Gao <hu...@us.ibm.com>
Signed-off-by: Takeshi Yamamuro <ya...@apache.org>
(cherry picked from commit 92c1b246174948d0c1f4d0833e1ceac265b17be7)
Signed-off-by: Takeshi Yamamuro <ya...@apache.org>
---
docs/_data/menu-sql.yaml | 2 +
docs/sql-ref-syntax-qry-select-cte.md | 109 +++++++++++++++++++++++++++++++++-
docs/sql-ref-syntax-qry-select.md | 3 +-
3 files changed, 112 insertions(+), 2 deletions(-)
diff --git a/docs/_data/menu-sql.yaml b/docs/_data/menu-sql.yaml
index badb98d..7827a0f 100644
--- a/docs/_data/menu-sql.yaml
+++ b/docs/_data/menu-sql.yaml
@@ -166,6 +166,8 @@
url: sql-ref-syntax-qry-select-tvf.html
- text: Inline Table
url: sql-ref-syntax-qry-select-inline-table.html
+ - text: Common Table Expression
+ url: sql-ref-syntax-qry-select-cte.html
- text: EXPLAIN
url: sql-ref-syntax-qry-explain.html
- text: Auxiliary Statements
diff --git a/docs/sql-ref-syntax-qry-select-cte.md b/docs/sql-ref-syntax-qry-select-cte.md
index 2bd7748..2146f8e 100644
--- a/docs/sql-ref-syntax-qry-select-cte.md
+++ b/docs/sql-ref-syntax-qry-select-cte.md
@@ -19,4 +19,111 @@ license: |
limitations under the License.
---
-**This page is under construction**
+### Description
+
+A common table expression (CTE) defines a temporary result set that a user can reference possibly multiple times within the scope of a SQL statement. A CTE is used mainly in a SELECT statement.
+
+### Syntax
+
+{% highlight sql %}
+WITH common_table_expression [ , ... ]
+{% endhighlight %}
+
+While `common_table_expression` is defined as
+{% highlight sql %}
+expression_name [ ( column_name [ , ... ] ) ] [ AS ] ( [ common_table_expression ] query )
+{% endhighlight %}
+
+### Parameters
+
+<dl>
+ <dt><code><em>expression_name</em></code></dt>
+ <dd>
+ Specifies a name for the common table expression.
+ </dd>
+</dl>
+<dl>
+ <dt><code><em>query</em></code></dt>
+ <dd>
+ A <a href="sql-ref-syntax-qry-select.html">SELECT</a> statement.
+ </dd>
+</dl>
+
+### Examples
+
+{% highlight sql %}
+-- CTE with multiple column aliases
+WITH t(x, y) AS (SELECT 1, 2)
+SELECT * FROM t WHERE x = 1 AND y = 2;
+ +---+---+
+ | x| y|
+ +---+---+
+ | 1| 2|
+ +---+---+
+
+-- CTE in CTE definition
+WITH t as (
+ WITH t2 AS (SELECT 1)
+ SELECT * FROM t2
+)
+SELECT * FROM t;
+ +---+
+ | 1|
+ +---+
+ | 1|
+ +---+
+
+-- CTE in subquery
+SELECT max(c) FROM (
+ WITH t(c) AS (SELECT 1)
+ SELECT * FROM t
+);
+ +------+
+ |max(c)|
+ +------+
+ | 1|
+ +------+
+
+-- CTE in subquery expression
+SELECT (
+ WITH t AS (SELECT 1)
+ SELECT * FROM t
+);
+ +----------------+
+ |scalarsubquery()|
+ +----------------+
+ | 1|
+ +----------------+
+
+-- CTE in CREATE VIEW statement
+CREATE VIEW v AS
+ WITH t(a, b, c, d) AS (SELECT 1, 2, 3, 4)
+ SELECT * FROM t;
+SELECT * FROM v;
+ +---+---+---+---+
+ | a| b| c| d|
+ +---+---+---+---+
+ | 1| 2| 3| 4|
+ +---+---+---+---+
+
+-- If name conflict is detected in nested CTE, then AnalysisException is thrown by default.
+-- SET spark.sql.legacy.ctePrecedencePolicy = CORRECTED (which is recommended),
+-- inner CTE definitions take precedence over outer definitions.
+SET spark.sql.legacy.ctePrecedencePolicy = CORRECTED;
+WITH
+ t AS (SELECT 1),
+ t2 AS (
+ WITH t AS (SELECT 2)
+ SELECT * FROM t
+ )
+SELECT * FROM t2;
+ +---+
+ | 2|
+ +---+
+ | 2|
+ +---+
+{% endhighlight %}
+
+### Related Statements
+
+ * [SELECT](sql-ref-syntax-qry-select.html)
diff --git a/docs/sql-ref-syntax-qry-select.md b/docs/sql-ref-syntax-qry-select.md
index 94f69d4..bc2cc02 100644
--- a/docs/sql-ref-syntax-qry-select.md
+++ b/docs/sql-ref-syntax-qry-select.md
@@ -53,7 +53,7 @@ SELECT [ hints , ... ] [ ALL | DISTINCT ] { named_expression [ , ... ] }
<dl>
<dt><code><em>with_query</em></code></dt>
<dd>
- Specifies the common table expressions (CTEs) before the main query block.
+ Specifies the <a href="sql-ref-syntax-qry-select-cte.html">common table expressions (CTEs)</a> before the main query block.
These table expressions are allowed to be referenced later in the FROM clause. This is useful to abstract
out repeated subquery blocks in the FROM clause and improves readability of the query.
</dd>
@@ -159,3 +159,4 @@ SELECT [ hints , ... ] [ ALL | DISTINCT ] { named_expression [ , ... ] }
* [TABLESAMPLE](sql-ref-syntax-qry-sampling.html)
* [JOIN](sql-ref-syntax-qry-select-join.html)
* [SET Operators](sql-ref-syntax-qry-select-setops.html)
+ * [Common Table Expression](sql-ref-syntax-qry-select-cte.html)
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org