You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@spark.apache.org by li...@apache.org on 2020/05/01 17:15:50 UTC
[spark] branch branch-3.0 updated: [MINOR][SQL][DOCS] Remove two
leading spaces from sql tables
This is an automated email from the ASF dual-hosted git repository.
lixiao 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 7ddaaed [MINOR][SQL][DOCS] Remove two leading spaces from sql tables
7ddaaed is described below
commit 7ddaaed4feec6e227f5178b1d888730751ba6d29
Author: Huaxin Gao <hu...@us.ibm.com>
AuthorDate: Fri May 1 10:11:43 2020 -0700
[MINOR][SQL][DOCS] Remove two leading spaces from sql tables
### What changes were proposed in this pull request?
Remove two leading spaces from sql tables.
### Why are the changes needed?
Follow the format of other references such as https://docs.snowflake.com/en/sql-reference/constructs/join.html, https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10002.htm, https://www.postgresql.org/docs/10/sql-select.html.
### Does this PR introduce any user-facing change?
before
```
SELECT * FROM test;
+-+
...
+-+
```
after
```
SELECT * FROM test;
+-+
...
+-+
```
### How was this patch tested?
Manually build and check
Closes #28348 from huaxingao/sql-format.
Authored-by: Huaxin Gao <hu...@us.ibm.com>
Signed-off-by: gatorsmile <ga...@gmail.com>
(cherry picked from commit 75da05038b68839c2b665675c80455826fc426b5)
Signed-off-by: gatorsmile <ga...@gmail.com>
---
docs/sql-ref-ansi-compliance.md | 40 +-
docs/sql-ref-functions-udf-hive.md | 82 ++--
docs/sql-ref-null-semantics.md | 512 +++++++++++-----------
docs/sql-ref-syntax-aux-analyze-table.md | 88 ++--
docs/sql-ref-syntax-aux-conf-mgmt-set.md | 10 +-
docs/sql-ref-syntax-aux-describe-database.md | 44 +-
docs/sql-ref-syntax-aux-describe-function.md | 84 ++--
docs/sql-ref-syntax-aux-describe-query.md | 60 +--
docs/sql-ref-syntax-aux-describe-table.md | 164 +++----
docs/sql-ref-syntax-aux-show-columns.md | 42 +-
docs/sql-ref-syntax-aux-show-create-table.md | 20 +-
docs/sql-ref-syntax-aux-show-databases.md | 40 +-
docs/sql-ref-syntax-aux-show-functions.md | 96 ++--
docs/sql-ref-syntax-aux-show-partitions.md | 60 +--
docs/sql-ref-syntax-aux-show-table.md | 178 ++++----
docs/sql-ref-syntax-aux-show-tables.md | 64 +--
docs/sql-ref-syntax-aux-show-tblproperties.md | 48 +-
docs/sql-ref-syntax-aux-show-views.md | 68 +--
docs/sql-ref-syntax-ddl-alter-database.md | 16 +-
docs/sql-ref-syntax-ddl-alter-table.md | 252 +++++------
docs/sql-ref-syntax-ddl-alter-view.md | 112 ++---
docs/sql-ref-syntax-ddl-create-database.md | 16 +-
docs/sql-ref-syntax-ddl-create-function.md | 46 +-
docs/sql-ref-syntax-ddl-drop-function.md | 32 +-
docs/sql-ref-syntax-ddl-repair-table.md | 18 +-
docs/sql-ref-syntax-ddl-truncate-table.md | 32 +-
docs/sql-ref-syntax-dml-insert-into.md | 164 +++----
docs/sql-ref-syntax-dml-insert-overwrite-table.md | 124 +++---
docs/sql-ref-syntax-dml-load.md | 44 +-
docs/sql-ref-syntax-qry-aggregation.md | 22 -
docs/sql-ref-syntax-qry-explain.md | 100 ++---
docs/sql-ref-syntax-qry-sampling.md | 82 ++--
docs/sql-ref-syntax-qry-select-clusterby.md | 40 +-
docs/sql-ref-syntax-qry-select-cte.md | 60 +--
docs/sql-ref-syntax-qry-select-distinct.md | 22 -
docs/sql-ref-syntax-qry-select-distribute-by.md | 40 +-
docs/sql-ref-syntax-qry-select-groupby.md | 216 ++++-----
docs/sql-ref-syntax-qry-select-having.md | 68 +--
docs/sql-ref-syntax-qry-select-inline-table.md | 36 +-
docs/sql-ref-syntax-qry-select-join.md | 175 ++++----
docs/sql-ref-syntax-qry-select-limit.md | 50 +--
docs/sql-ref-syntax-qry-select-orderby.md | 90 ++--
docs/sql-ref-syntax-qry-select-setops.md | 190 ++++----
docs/sql-ref-syntax-qry-select-sortby.md | 132 +++---
docs/sql-ref-syntax-qry-select-tvf.md | 68 +--
docs/sql-ref-syntax-qry-select-where.md | 82 ++--
docs/sql-ref-syntax-qry-window.md | 168 +++----
47 files changed, 2076 insertions(+), 2121 deletions(-)
diff --git a/docs/sql-ref-ansi-compliance.md b/docs/sql-ref-ansi-compliance.md
index 3a0c2c1..6cf1653 100644
--- a/docs/sql-ref-ansi-compliance.md
+++ b/docs/sql-ref-ansi-compliance.md
@@ -73,11 +73,11 @@ SELECT 2147483647 + 1;
-- `spark.sql.ansi.enabled=false`
SELECT 2147483647 + 1;
- +----------------+
- |(2147483647 + 1)|
- +----------------+
- | -2147483648|
- +----------------+
++----------------+
+|(2147483647 + 1)|
++----------------+
+| -2147483648|
++----------------+
{% endhighlight %}
### Type Conversion
@@ -101,18 +101,18 @@ SELECT CAST(2147483648L AS INT);
-- `spark.sql.ansi.enabled=false` (This is a default behaviour)
SELECT CAST('a' AS INT);
- +--------------+
- |CAST(a AS INT)|
- +--------------+
- | null|
- +--------------+
++--------------+
+|CAST(a AS INT)|
++--------------+
+| null|
++--------------+
SELECT CAST(2147483648L AS INT);
- +-----------------------+
- |CAST(2147483648 AS INT)|
- +-----------------------+
- | -2147483648|
- +-----------------------+
++-----------------------+
+|CAST(2147483648 AS INT)|
++-----------------------+
+| -2147483648|
++-----------------------+
-- Examples of store assignment rules
CREATE TABLE t (v INT);
@@ -125,11 +125,11 @@ INSERT INTO t VALUES ('1');
-- `spark.sql.storeAssignmentPolicy=LEGACY` (This is a legacy behaviour until Spark 2.x)
INSERT INTO t VALUES ('1');
SELECT * FROM t;
- +---+
- | v|
- +---+
- | 1|
- +---+
++---+
+| v|
++---+
+| 1|
++---+
{% endhighlight %}
### SQL Functions
diff --git a/docs/sql-ref-functions-udf-hive.md b/docs/sql-ref-functions-udf-hive.md
index a87266d..97d72c4 100644
--- a/docs/sql-ref-functions-udf-hive.md
+++ b/docs/sql-ref-functions-udf-hive.md
@@ -36,22 +36,22 @@ An example below uses [GenericUDFAbs](https://github.com/apache/hive/blob/master
CREATE TEMPORARY FUNCTION testUDF AS 'org.apache.hadoop.hive.ql.udf.generic.GenericUDFAbs';
SELECT * FROM t;
- +-----+
- |value|
- +-----+
- | -1.0|
- | 2.0|
- | -3.0|
- +-----+
++-----+
+|value|
++-----+
+| -1.0|
+| 2.0|
+| -3.0|
++-----+
SELECT testUDF(value) FROM t;
- +--------------+
- |testUDF(value)|
- +--------------+
- | 1.0|
- | 2.0|
- | 3.0|
- +--------------+
++--------------+
+|testUDF(value)|
++--------------+
+| 1.0|
+| 2.0|
+| 3.0|
++--------------+
{% endhighlight %}
@@ -63,22 +63,22 @@ CREATE TEMPORARY FUNCTION hiveUDTF
AS 'org.apache.hadoop.hive.ql.udf.generic.GenericUDTFExplode';
SELECT * FROM t;
- +------+
- | value|
- +------+
- |[1, 2]|
- |[3, 4]|
- +------+
++------+
+| value|
++------+
+|[1, 2]|
+|[3, 4]|
++------+
SELECT hiveUDTF(value) FROM t;
- +---+
- |col|
- +---+
- | 1|
- | 2|
- | 3|
- | 4|
- +---+
++---+
+|col|
++---+
+| 1|
+| 2|
+| 3|
+| 4|
++---+
{% endhighlight %}
Hive has two UDAF interfaces: [UDAF](https://github.com/apache/hive/blob/master/udf/src/java/org/apache/hadoop/hive/ql/exec/UDAF.java) and [GenericUDAFResolver](https://github.com/apache/hive/blob/master/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDAFResolver.java).
@@ -90,19 +90,19 @@ CREATE TEMPORARY FUNCTION hiveUDAF
AS 'org.apache.hadoop.hive.ql.udf.generic.GenericUDAFSum';
SELECT * FROM t;
- +---+-----+
- |key|value|
- +---+-----+
- | a| 1|
- | a| 2|
- | b| 3|
- +---+-----+
++---+-----+
+|key|value|
++---+-----+
+| a| 1|
+| a| 2|
+| b| 3|
++---+-----+
SELECT key, hiveUDAF(value) FROM t GROUP BY key;
- +---+---------------+
- |key|hiveUDAF(value)|
- +---+---------------+
- | b| 3|
- | a| 3|
- +---+---------------+
++---+---------------+
+|key|hiveUDAF(value)|
++---+---------------+
+| b| 3|
+| a| 3|
++---+---------------+
{% endhighlight %}
\ No newline at end of file
diff --git a/docs/sql-ref-null-semantics.md b/docs/sql-ref-null-semantics.md
index dc48a36..d1f4f17 100644
--- a/docs/sql-ref-null-semantics.md
+++ b/docs/sql-ref-null-semantics.md
@@ -119,35 +119,35 @@ one or both operands are `NULL`:
{% highlight sql %}
-- Normal comparison operators return `NULL` when one of the operand is `NULL`.
SELECT 5 > null AS expression_output;
- +-----------------+
- |expression_output|
- +-----------------+
- | null|
- +-----------------+
++-----------------+
+|expression_output|
++-----------------+
+| null|
++-----------------+
-- Normal comparison operators return `NULL` when both the operands are `NULL`.
SELECT null = null AS expression_output;
- +-----------------+
- |expression_output|
- +-----------------+
- | null|
- +-----------------+
++-----------------+
+|expression_output|
++-----------------+
+| null|
++-----------------+
-- Null-safe equal operator return `False` when one of the operand is `NULL`
SELECT 5 <=> null AS expression_output;
- +-----------------+
- |expression_output|
- +-----------------+
- | false|
- +-----------------+
++-----------------+
+|expression_output|
++-----------------+
+| false|
++-----------------+
-- Null-safe equal operator return `True` when one of the operand is `NULL`
SELECT NULL <=> NULL;
- +-----------------+
- |expression_output|
- +-----------------+
- | true|
- +-----------------+
++-----------------+
+|expression_output|
++-----------------+
+| true|
++-----------------+
{% endhighlight %}
### Logical Operators <a name="logical-operators"></a>
@@ -212,27 +212,27 @@ The following tables illustrate the behavior of logical operators when one or bo
{% highlight sql %}
-- Normal comparison operators return `NULL` when one of the operands is `NULL`.
SELECT (true OR null) AS expression_output;
- +-----------------+
- |expression_output|
- +-----------------+
- | true|
- +-----------------+
++-----------------+
+|expression_output|
++-----------------+
+| true|
++-----------------+
-- Normal comparison operators return `NULL` when both the operands are `NULL`.
SELECT (null OR false) AS expression_output
- +-----------------+
- |expression_output|
- +-----------------+
- | null|
- +-----------------+
++-----------------+
+|expression_output|
++-----------------+
+| null|
++-----------------+
-- Null-safe equal operator returns `False` when one of the operands is `NULL`
SELECT NOT(null) AS expression_output;
- +-----------------+
- |expression_output|
- +-----------------+
- | null|
- +-----------------+
++-----------------+
+|expression_output|
++-----------------+
+| null|
++-----------------+
{% endhighlight %}
### Expressions <a name="expressions"></a>
@@ -254,25 +254,25 @@ expression are `NULL` and most of the expressions fall in this category.
{% highlight sql %}
SELECT concat('John', null) AS expression_output;
- +-----------------+
- |expression_output|
- +-----------------+
- | null|
- +-----------------+
++-----------------+
+|expression_output|
++-----------------+
+| null|
++-----------------+
SELECT positive(null) AS expression_output;
- +-----------------+
- |expression_output|
- +-----------------+
- | null|
- +-----------------+
++-----------------+
+|expression_output|
++-----------------+
+| null|
++-----------------+
SELECT to_date(null) AS expression_output;
- +-----------------+
- |expression_output|
- +-----------------+
- | null|
- +-----------------+
++-----------------+
+|expression_output|
++-----------------+
+| null|
++-----------------+
{% endhighlight %}
#### Expressions That Can Process Null Value Operands <a name="can-process-null"></a>
@@ -298,34 +298,34 @@ returns the first non `NULL` value in its list of operands. However, `coalesce`
{% highlight sql %}
SELECT isnull(null) AS expression_output;
- +-----------------+
- |expression_output|
- +-----------------+
- | true|
- +-----------------+
++-----------------+
+|expression_output|
++-----------------+
+| true|
++-----------------+
-- Returns the first occurrence of non `NULL` value.
SELECT coalesce(null, null, 3, null) AS expression_output;
- +-----------------+
- |expression_output|
- +-----------------+
- | 3|
- +-----------------+
++-----------------+
+|expression_output|
++-----------------+
+| 3|
++-----------------+
-- Returns `NULL` as all its operands are `NULL`.
SELECT coalesce(null, null, null, null) AS expression_output;
- +-----------------+
- |expression_output|
- +-----------------+
- | null|
- +-----------------+
++-----------------+
+|expression_output|
++-----------------+
+| null|
++-----------------+
SELECT isnan(null) AS expression_output;
- +-----------------+
- |expression_output|
- +-----------------+
- | false|
- +-----------------+
++-----------------+
+|expression_output|
++-----------------+
+| false|
++-----------------+
{% endhighlight %}
#### Builtin Aggregate Expressions <a name="built-in-aggregate"></a>
@@ -349,44 +349,44 @@ the rules of how `NULL` values are handled by aggregate functions.
{% highlight sql %}
-- `count(*)` does not skip `NULL` values.
SELECT count(*) FROM person;
- +--------+
- |count(1)|
- +--------+
- | 7|
- +--------+
++--------+
+|count(1)|
++--------+
+| 7|
++--------+
-- `NULL` values in column `age` are skipped from processing.
SELECT count(age) FROM person;
- +----------+
- |count(age)|
- +----------+
- | 5|
- +----------+
++----------+
+|count(age)|
++----------+
+| 5|
++----------+
-- `count(*)` on an empty input set returns 0. This is unlike the other
-- aggregate functions, such as `max`, which return `NULL`.
SELECT count(*) FROM person where 1 = 0;
- +--------+
- |count(1)|
- +--------+
- | 0|
- +--------+
++--------+
+|count(1)|
++--------+
+| 0|
++--------+
-- `NULL` values are excluded from computation of maximum value.
SELECT max(age) FROM person;
- +--------+
- |max(age)|
- +--------+
- | 50|
- +--------+
++--------+
+|max(age)|
++--------+
+| 50|
++--------+
-- `max` returns `NULL` on an empty input set.
SELECT max(age) FROM person where 1 = 0;
- +--------+
- |max(age)|
- +--------+
- | null|
- +--------+
++--------+
+|max(age)|
++--------+
+| null|
++--------+
{% endhighlight %}
### Condition Expressions in WHERE, HAVING and JOIN Clauses <a name="condition-expressions"></a>
@@ -401,71 +401,71 @@ For all the three operators, a condition expression is a boolean expression and
{% highlight sql %}
-- Persons whose age is unknown (`NULL`) are filtered out from the result set.
SELECT * FROM person WHERE age > 0;
- +--------+---+
- | name|age|
- +--------+---+
- |Michelle| 30|
- | Fred| 50|
- | Mike| 18|
- | Dan| 50|
- | Joe| 30|
- +--------+---+
++--------+---+
+| name|age|
++--------+---+
+|Michelle| 30|
+| Fred| 50|
+| Mike| 18|
+| Dan| 50|
+| Joe| 30|
++--------+---+
-- `IS NULL` expression is used in disjunction to select the persons
-- with unknown (`NULL`) records.
SELECT * FROM person WHERE age > 0 OR age IS NULL;
- +--------+----+
- | name| age|
- +--------+----+
- | Albert|null|
- |Michelle| 30|
- | Fred| 50|
- | Mike| 18|
- | Dan| 50|
- | Marry|null|
- | Joe| 30|
- +--------+----+
++--------+----+
+| name| age|
++--------+----+
+| Albert|null|
+|Michelle| 30|
+| Fred| 50|
+| Mike| 18|
+| Dan| 50|
+| Marry|null|
+| Joe| 30|
++--------+----+
-- Person with unknown(`NULL`) ages are skipped from processing.
SELECT * FROM person GROUP BY age HAVING max(age) > 18;
- +---+--------+
- |age|count(1)|
- +---+--------+
- | 50| 2|
- | 30| 2|
- +---+--------+
++---+--------+
+|age|count(1)|
++---+--------+
+| 50| 2|
+| 30| 2|
++---+--------+
-- A self join case with a join condition `p1.age = p2.age AND p1.name = p2.name`.
-- The persons with unknown age (`NULL`) are filtered out by the join operator.
SELECT * FROM person p1, person p2
WHERE p1.age = p2.age
AND p1.name = p2.name;
- +--------+---+--------+---+
- | name|age| name|age|
- +--------+---+--------+---+
- |Michelle| 30|Michelle| 30|
- | Fred| 50| Fred| 50|
- | Mike| 18| Mike| 18|
- | Dan| 50| Dan| 50|
- | Joe| 30| Joe| 30|
- +--------+---+--------+---+
++--------+---+--------+---+
+| name|age| name|age|
++--------+---+--------+---+
+|Michelle| 30|Michelle| 30|
+| Fred| 50| Fred| 50|
+| Mike| 18| Mike| 18|
+| Dan| 50| Dan| 50|
+| Joe| 30| Joe| 30|
++--------+---+--------+---+
-- The age column from both legs of join are compared using null-safe equal which
-- is why the persons with unknown age (`NULL`) are qualified by the join.
SELECT * FROM person p1, person p2
WHERE p1.age <=> p2.age
AND p1.name = p2.name;
- +--------+----+--------+----+
- | name| age| name| age|
- +--------+----+--------+----+
- | Albert|null| Albert|null|
- |Michelle| 30|Michelle| 30|
- | Fred| 50| Fred| 50|
- | Mike| 18| Mike| 18|
- | Dan| 50| Dan| 50|
- | Marry|null| Marry|null|
- | Joe| 30| Joe| 30|
- +--------+----+--------+----+
++--------+----+--------+----+
+| name| age| name| age|
++--------+----+--------+----+
+| Albert|null| Albert|null|
+|Michelle| 30|Michelle| 30|
+| Fred| 50| Fred| 50|
+| Mike| 18| Mike| 18|
+| Dan| 50| Dan| 50|
+| Marry|null| Marry|null|
+| Joe| 30| Joe| 30|
++--------+----+--------+----+
{% endhighlight %}
### Aggregate Operator (GROUP BY, DISTINCT) <a name="aggregate-operator"></a>
@@ -480,25 +480,25 @@ standard and with other enterprise database management systems.
{% highlight sql %}
-- `NULL` values are put in one bucket in `GROUP BY` processing.
SELECT age, count(*) FROM person GROUP BY age;
- +----+--------+
- | age|count(1)|
- +----+--------+
- |null| 2|
- | 50| 2|
- | 30| 2|
- | 18| 1|
- +----+--------+
++----+--------+
+| age|count(1)|
++----+--------+
+|null| 2|
+| 50| 2|
+| 30| 2|
+| 18| 1|
++----+--------+
-- All `NULL` ages are considered one distinct value in `DISTINCT` processing.
SELECT DISTINCT age FROM person;
- +----+
- | age|
- +----+
- |null|
- | 50|
- | 30|
- | 18|
- +----+
++----+
+| age|
++----+
+|null|
+| 50|
+| 30|
+| 18|
++----+
{% endhighlight %}
### Sort Operator (ORDER BY Clause) <a name="order-by"></a>
@@ -513,47 +513,47 @@ the `NULL` values are placed at first.
-- `NULL` values are shown at first and other values
-- are sorted in ascending way.
SELECT age, name FROM person ORDER BY age;
- +----+--------+
- | age| name|
- +----+--------+
- |null| Marry|
- |null| Albert|
- | 18| Mike|
- | 30|Michelle|
- | 30| Joe|
- | 50| Fred|
- | 50| Dan|
- +----+--------+
++----+--------+
+| age| name|
++----+--------+
+|null| Marry|
+|null| Albert|
+| 18| Mike|
+| 30|Michelle|
+| 30| Joe|
+| 50| Fred|
+| 50| Dan|
++----+--------+
-- Column values other than `NULL` are sorted in ascending
-- way and `NULL` values are shown at the last.
SELECT age, name FROM person ORDER BY age NULLS LAST;
- +----+--------+
- | age| name|
- +----+--------+
- | 18| Mike|
- | 30|Michelle|
- | 30| Joe|
- | 50| Dan|
- | 50| Fred|
- |null| Marry|
- |null| Albert|
- +----+--------+
++----+--------+
+| age| name|
++----+--------+
+| 18| Mike|
+| 30|Michelle|
+| 30| Joe|
+| 50| Dan|
+| 50| Fred|
+|null| Marry|
+|null| Albert|
++----+--------+
-- Columns other than `NULL` values are sorted in descending
-- and `NULL` values are shown at the last.
SELECT age, name FROM person ORDER BY age DESC NULLS LAST;
- +----+--------+
- | age| name|
- +----+--------+
- | 50| Fred|
- | 50| Dan|
- | 30|Michelle|
- | 30| Joe|
- | 18| Mike|
- |null| Marry|
- |null| Albert|
- +----+--------+
++----+--------+
+| age| name|
++----+--------+
+| 50| Fred|
+| 50| Dan|
+| 30|Michelle|
+| 30| Joe|
+| 18| Mike|
+|null| Marry|
+|null| Albert|
++----+--------+
{% endhighlight %}
### Set Operators (UNION, INTERSECT, EXCEPT) <a name="set-operators"></a>
@@ -573,27 +573,27 @@ CREATE VIEW unknown_age SELECT * FROM person WHERE age IS NULL;
SELECT name, age FROM person
INTERSECT
SELECT name, age from unknown_age;
- +------+----+
- | name| age|
- +------+----+
- |Albert|null|
- | Marry|null|
- +------+----+
++------+----+
+| name| age|
++------+----+
+|Albert|null|
+| Marry|null|
++------+----+
-- `NULL` values from two legs of the `EXCEPT` are not in output.
-- This basically shows that the comparison happens in a null-safe manner.
SELECT age, name FROM person
EXCEPT
SELECT age FROM unknown_age;
- +---+--------+
- |age| name|
- +---+--------+
- | 30| Joe|
- | 50| Fred|
- | 30|Michelle|
- | 18| Mike|
- | 50| Dan|
- +---+--------+
++---+--------+
+|age| name|
++---+--------+
+| 30| Joe|
+| 50| Fred|
+| 30|Michelle|
+| 18| Mike|
+| 50| Dan|
++---+--------+
-- Performs `UNION` operation between two sets of data.
-- The comparison between columns of the row ae done in
@@ -601,17 +601,17 @@ SELECT age, name FROM person
SELECT name, age FROM person
UNION
SELECT name, age FROM unknown_age;
- +--------+----+
- | name| age|
- +--------+----+
- | Albert|null|
- | Joe| 30|
- |Michelle| 30|
- | Marry|null|
- | Fred| 50|
- | Mike| 18|
- | Dan| 50|
- +--------+----+
++--------+----+
+| name| age|
++--------+----+
+| Albert|null|
+| Joe| 30|
+|Michelle| 30|
+| Marry|null|
+| Fred| 50|
+| Mike| 18|
+| Dan| 50|
++--------+----+
{% endhighlight %}
### EXISTS/NOT EXISTS Subquery <a name="exists-not-exists"></a>
@@ -633,39 +633,39 @@ semijoins / anti-semijoins without special provisions for null awareness.
-- Even if subquery produces rows with `NULL` values, the `EXISTS` expression
-- evaluates to `TRUE` as the subquery produces 1 row.
SELECT * FROM person WHERE EXISTS (SELECT null);
- +--------+----+
- | name| age|
- +--------+----+
- | Albert|null|
- |Michelle| 30|
- | Fred| 50|
- | Mike| 18|
- | Dan| 50|
- | Marry|null|
- | Joe| 30|
- +--------+----+
++--------+----+
+| name| age|
++--------+----+
+| Albert|null|
+|Michelle| 30|
+| Fred| 50|
+| Mike| 18|
+| Dan| 50|
+| Marry|null|
+| Joe| 30|
++--------+----+
-- `NOT EXISTS` expression returns `FALSE`. It returns `TRUE` only when
-- subquery produces no rows. In this case, it returns 1 row.
SELECT * FROM person WHERE NOT EXISTS (SELECT null);
- +----+---+
- |name|age|
- +----+---+
- +----+---+
++----+---+
+|name|age|
++----+---+
++----+---+
-- `NOT EXISTS` expression returns `TRUE`.
SELECT * FROM person WHERE NOT EXISTS (SELECT 1 WHERE 1 = 0);
- +--------+----+
- | name| age|
- +--------+----+
- | Albert|null|
- |Michelle| 30|
- | Fred| 50|
- | Mike| 18|
- | Dan| 50|
- | Marry|null|
- | Joe| 30|
- +--------+----+
++--------+----+
+| name| age|
++--------+----+
+| Albert|null|
+|Michelle| 30|
+| Fred| 50|
+| Mike| 18|
+| Dan| 50|
+| Marry|null|
+| Joe| 30|
++--------+----+
{% endhighlight %}
### IN/NOT IN Subquery <a name="in-not-in"></a>
@@ -696,29 +696,29 @@ and because NOT UNKNOWN is again UNKNOWN.
-- The subquery has only `NULL` value in its result set. Therefore,
-- the result of `IN` predicate is UNKNOWN.
SELECT * FROM person WHERE age IN (SELECT null);
- +----+---+
- |name|age|
- +----+---+
- +----+---+
++----+---+
+|name|age|
++----+---+
++----+---+
-- The subquery has `NULL` value in the result set as well as a valid
-- value `50`. Rows with age = 50 are returned.
SELECT * FROM person
WHERE age IN (SELECT age FROM VALUES (50), (null) sub(age));
- +----+---+
- |name|age|
- +----+---+
- |Fred| 50|
- | Dan| 50|
- +----+---+
++----+---+
+|name|age|
++----+---+
+|Fred| 50|
+| Dan| 50|
++----+---+
-- Since subquery has `NULL` value in the result set, the `NOT IN`
-- predicate would return UNKNOWN. Hence, no rows are
-- qualified for this query.
SELECT * FROM person
WHERE age NOT IN (SELECT age FROM VALUES (50), (null) sub(age));
- +----+---+
- |name|age|
- +----+---+
- +----+---+
++----+---+
+|name|age|
++----+---+
++----+---+
{% endhighlight %}
diff --git a/docs/sql-ref-syntax-aux-analyze-table.md b/docs/sql-ref-syntax-aux-analyze-table.md
index 739e692..f6a6c5f 100644
--- a/docs/sql-ref-syntax-aux-analyze-table.md
+++ b/docs/sql-ref-syntax-aux-analyze-table.md
@@ -79,60 +79,60 @@ INSERT INTO students PARTITION (student_id = 222222) VALUES ('John');
ANALYZE TABLE students COMPUTE STATISTICS NOSCAN;
DESC EXTENDED students;
- +--------------------+--------------------+-------+
- | col_name| data_type|comment|
- +--------------------+--------------------+-------+
- | name| string| null|
- | student_id| int| null|
- | ...| ...| ...|
- | Statistics| 864 bytes| |
- | ...| ...| ...|
- | Partition Provider| Catalog| |
- +--------------------+--------------------+-------+
++--------------------+--------------------+-------+
+| col_name| data_type|comment|
++--------------------+--------------------+-------+
+| name| string| null|
+| student_id| int| null|
+| ...| ...| ...|
+| Statistics| 864 bytes| |
+| ...| ...| ...|
+| Partition Provider| Catalog| |
++--------------------+--------------------+-------+
ANALYZE TABLE students COMPUTE STATISTICS;
DESC EXTENDED students;
- +--------------------+--------------------+-------+
- | col_name| data_type|comment|
- +--------------------+--------------------+-------+
- | name| string| null|
- | student_id| int| null|
- | ...| ...| ...|
- | Statistics| 864 bytes, 2 rows| |
- | ...| ...| ...|
- | Partition Provider| Catalog| |
- +--------------------+--------------------+-------+
++--------------------+--------------------+-------+
+| col_name| data_type|comment|
++--------------------+--------------------+-------+
+| name| string| null|
+| student_id| int| null|
+| ...| ...| ...|
+| Statistics| 864 bytes, 2 rows| |
+| ...| ...| ...|
+| Partition Provider| Catalog| |
++--------------------+--------------------+-------+
ANALYZE TABLE students PARTITION (student_id = 111111) COMPUTE STATISTICS;
DESC EXTENDED students PARTITION (student_id = 111111);
- +--------------------+--------------------+-------+
- | col_name| data_type|comment|
- +--------------------+--------------------+-------+
- | name| string| null|
- | student_id| int| null|
- | ...| ...| ...|
- |Partition Statistics| 432 bytes, 1 rows| |
- | ...| ...| ...|
- | OutputFormat|org.apache.hadoop...| |
- +--------------------+--------------------+-------+
++--------------------+--------------------+-------+
+| col_name| data_type|comment|
++--------------------+--------------------+-------+
+| name| string| null|
+| student_id| int| null|
+| ...| ...| ...|
+|Partition Statistics| 432 bytes, 1 rows| |
+| ...| ...| ...|
+| OutputFormat|org.apache.hadoop...| |
++--------------------+--------------------+-------+
ANALYZE TABLE students COMPUTE STATISTICS FOR COLUMNS name;
DESC EXTENDED students name;
- +--------------+----------+
- | info_name|info_value|
- +--------------+----------+
- | col_name| name|
- | data_type| string|
- | comment| NULL|
- | min| NULL|
- | max| NULL|
- | num_nulls| 0|
- |distinct_count| 2|
- | avg_col_len| 4|
- | max_col_len| 4|
- | histogram| NULL|
- +--------------+----------+
++--------------+----------+
+| info_name|info_value|
++--------------+----------+
+| col_name| name|
+| data_type| string|
+| comment| NULL|
+| min| NULL|
+| max| NULL|
+| num_nulls| 0|
+|distinct_count| 2|
+| avg_col_len| 4|
+| max_col_len| 4|
+| histogram| NULL|
++--------------+----------+
{% endhighlight %}
diff --git a/docs/sql-ref-syntax-aux-conf-mgmt-set.md b/docs/sql-ref-syntax-aux-conf-mgmt-set.md
index 2ca5130..330a1a6 100644
--- a/docs/sql-ref-syntax-aux-conf-mgmt-set.md
+++ b/docs/sql-ref-syntax-aux-conf-mgmt-set.md
@@ -62,11 +62,11 @@ SET;
-- List the value of specified property key.
SET spark.sql.variable.substitute;
- +-----------------------------+-----+
- | key|value|
- +-----------------------------+-----+
- |spark.sql.variable.substitute|false|
- +-----------------------------+-----+
++-----------------------------+-----+
+| key|value|
++-----------------------------+-----+
+|spark.sql.variable.substitute|false|
++-----------------------------+-----+
{% endhighlight %}
### Related Statements
diff --git a/docs/sql-ref-syntax-aux-describe-database.md b/docs/sql-ref-syntax-aux-describe-database.md
index 590438b..39a40dd 100644
--- a/docs/sql-ref-syntax-aux-describe-database.md
+++ b/docs/sql-ref-syntax-aux-describe-database.md
@@ -52,13 +52,13 @@ CREATE DATABASE employees COMMENT 'For software companies';
-- Returns Database Name, Description and Root location of the filesystem
-- for the employees DATABASE.
DESCRIBE DATABASE employees;
- +-------------------------+-----------------------------+
- |database_description_item| database_description_value|
- +-------------------------+-----------------------------+
- | Database Name| employees|
- | Description| For software companies|
- | Location|file:/Users/Temp/employees.db|
- +-------------------------+-----------------------------+
++-------------------------+-----------------------------+
+|database_description_item| database_description_value|
++-------------------------+-----------------------------+
+| Database Name| employees|
+| Description| For software companies|
+| Location|file:/Users/Temp/employees.db|
++-------------------------+-----------------------------+
-- Create employees DATABASE
CREATE DATABASE employees COMMENT 'For software companies';
@@ -68,27 +68,27 @@ ALTER DATABASE employees SET DBPROPERTIES ('Create-by' = 'Kevin', 'Create-date'
-- Describe employees DATABASE with EXTENDED option to return additional database properties
DESCRIBE DATABASE EXTENDED employees;
- +-------------------------+---------------------------------------------+
- |database_description_item| database_description_value|
- +-------------------------+---------------------------------------------+
- | Database Name| employees|
- | Description| For software companies|
- | Location| file:/Users/Temp/employees.db|
- | Properties|((Create-by,kevin), (Create-date,09/01/2019))|
- +-------------------------+---------------------------------------------+
++-------------------------+---------------------------------------------+
+|database_description_item| database_description_value|
++-------------------------+---------------------------------------------+
+| Database Name| employees|
+| Description| For software companies|
+| Location| file:/Users/Temp/employees.db|
+| Properties|((Create-by,kevin), (Create-date,09/01/2019))|
++-------------------------+---------------------------------------------+
-- Create deployment SCHEMA
CREATE SCHEMA deployment COMMENT 'Deployment environment';
-- Describe deployment, the DATABASE and SCHEMA are interchangeable, their meaning are the same.
DESC DATABASE deployment;
- +-------------------------+------------------------------+
- |database_description_item|database_description_value |
- +-------------------------+------------------------------+
- | Database Name| deployment|
- | Description| Deployment environment|
- | Location|file:/Users/Temp/deployment.db|
- +-------------------------+------------------------------+
++-------------------------+------------------------------+
+|database_description_item|database_description_value |
++-------------------------+------------------------------+
+| Database Name| deployment|
+| Description| Deployment environment|
+| Location|file:/Users/Temp/deployment.db|
++-------------------------+------------------------------+
{% endhighlight %}
### Related Statements
diff --git a/docs/sql-ref-syntax-aux-describe-function.md b/docs/sql-ref-syntax-aux-describe-function.md
index a4ff76b..76c9efa 100644
--- a/docs/sql-ref-syntax-aux-describe-function.md
+++ b/docs/sql-ref-syntax-aux-describe-function.md
@@ -54,59 +54,59 @@ metadata information is returned along with the extended usage information.
-- Describe a builtin scalar function.
-- Returns function name, implementing class and usage
DESC FUNCTION abs;
- +-------------------------------------------------------------------+
- |function_desc |
- +-------------------------------------------------------------------+
- |Function: abs |
- |Class: org.apache.spark.sql.catalyst.expressions.Abs |
- |Usage: abs(expr) - Returns the absolute value of the numeric value.|
- +-------------------------------------------------------------------+
++-------------------------------------------------------------------+
+|function_desc |
++-------------------------------------------------------------------+
+|Function: abs |
+|Class: org.apache.spark.sql.catalyst.expressions.Abs |
+|Usage: abs(expr) - Returns the absolute value of the numeric value.|
++-------------------------------------------------------------------+
-- Describe a builtin scalar function.
-- Returns function name, implementing class and usage and examples.
DESC FUNCTION EXTENDED abs;
- +-------------------------------------------------------------------+
- |function_desc |
- +-------------------------------------------------------------------+
- |Function: abs |
- |Class: org.apache.spark.sql.catalyst.expressions.Abs |
- |Usage: abs(expr) - Returns the absolute value of the numeric value.|
- |Extended Usage: |
- | Examples: |
- | > SELECT abs(-1); |
- | 1 |
- | |
- +-------------------------------------------------------------------+
++-------------------------------------------------------------------+
+|function_desc |
++-------------------------------------------------------------------+
+|Function: abs |
+|Class: org.apache.spark.sql.catalyst.expressions.Abs |
+|Usage: abs(expr) - Returns the absolute value of the numeric value.|
+|Extended Usage: |
+| Examples: |
+| > SELECT abs(-1); |
+| 1 |
+| |
++-------------------------------------------------------------------+
-- Describe a builtin aggregate function
DESC FUNCTION max;
- +--------------------------------------------------------------+
- |function_desc |
- +--------------------------------------------------------------+
- |Function: max |
- |Class: org.apache.spark.sql.catalyst.expressions.aggregate.Max|
- |Usage: max(expr) - Returns the maximum value of `expr`. |
- +--------------------------------------------------------------+
++--------------------------------------------------------------+
+|function_desc |
++--------------------------------------------------------------+
+|Function: max |
+|Class: org.apache.spark.sql.catalyst.expressions.aggregate.Max|
+|Usage: max(expr) - Returns the maximum value of `expr`. |
++--------------------------------------------------------------+
-- Describe a builtin user defined aggregate function
-- Returns function name, implementing class and usage and examples.
DESC FUNCTION EXTENDED explode
- +---------------------------------------------------------------+
- |function_desc |
- +---------------------------------------------------------------+
- |Function: explode |
- |Class: org.apache.spark.sql.catalyst.expressions.Explode |
- |Usage: explode(expr) - Separates the elements of array `expr` |
- | into multiple rows, or the elements of map `expr` into |
- | multiple rows and columns. Unless specified otherwise, uses |
- | the default column name `col` for elements of the array or |
- | `key` and `value` for the elements of the map. |
- |Extended Usage: |
- | Examples: |
- | > SELECT explode(array(10, 20)); |
- | 10 |
- | 20 |
- +---------------------------------------------------------------+
++---------------------------------------------------------------+
+|function_desc |
++---------------------------------------------------------------+
+|Function: explode |
+|Class: org.apache.spark.sql.catalyst.expressions.Explode |
+|Usage: explode(expr) - Separates the elements of array `expr` |
+| into multiple rows, or the elements of map `expr` into |
+| multiple rows and columns. Unless specified otherwise, uses |
+| the default column name `col` for elements of the array or |
+| `key` and `value` for the elements of the map. |
+|Extended Usage: |
+| Examples: |
+| > SELECT explode(array(10, 20)); |
+| 10 |
+| 20 |
++---------------------------------------------------------------+
{% endhighlight %}
diff --git a/docs/sql-ref-syntax-aux-describe-query.md b/docs/sql-ref-syntax-aux-describe-query.md
index f64416a..07ac39b 100644
--- a/docs/sql-ref-syntax-aux-describe-query.md
+++ b/docs/sql-ref-syntax-aux-describe-query.md
@@ -59,50 +59,50 @@ CREATE TABLE person (name STRING , age INT COMMENT 'Age column', address STRING)
-- Returns column metadata information for a simple select query
DESCRIBE QUERY select age, sum(age) FROM person GROUP BY age;
- +--------+---------+----------+
- |col_name|data_type| comment|
- +--------+---------+----------+
- | age| int|Age column|
- |sum(age)| bigint| null|
- +--------+---------+----------+
++--------+---------+----------+
+|col_name|data_type| comment|
++--------+---------+----------+
+| age| int|Age column|
+|sum(age)| bigint| null|
++--------+---------+----------+
-- Returns column metadata information for common table expression (`CTE`).
DESCRIBE QUERY WITH all_names_cte
AS (SELECT name from person) SELECT * FROM all_names_cte;
- +--------+---------+-------+
- |col_name|data_type|comment|
- +--------+---------+-------+
- | name| string| null|
- +--------+---------+-------+
++--------+---------+-------+
+|col_name|data_type|comment|
++--------+---------+-------+
+| name| string| null|
++--------+---------+-------+
-- Returns column metadata information for a inline table.
DESC QUERY VALUES(100, 'John', 10000.20D) AS employee(id, name, salary);
- +--------+---------+-------+
- |col_name|data_type|comment|
- +--------+---------+-------+
- | id| int| null|
- | name| string| null|
- | salary| double| null|
- +--------+---------+-------+
++--------+---------+-------+
+|col_name|data_type|comment|
++--------+---------+-------+
+| id| int| null|
+| name| string| null|
+| salary| double| null|
++--------+---------+-------+
-- Returns column metadata information for `TABLE` statement.
DESC QUERY TABLE person;
- +--------+---------+----------+
- |col_name|data_type| comment|
- +--------+---------+----------+
- | name| string| null|
- | age| int| Agecolumn|
- | address| string| null|
- +--------+---------+----------+
++--------+---------+----------+
+|col_name|data_type| comment|
++--------+---------+----------+
+| name| string| null|
+| age| int| Agecolumn|
+| address| string| null|
++--------+---------+----------+
-- Returns column metadata information for a `FROM` statement.
-- `QUERY` clause is optional and can be omitted.
DESCRIBE FROM person SELECT age;
- +--------+---------+----------+
- |col_name|data_type| comment|
- +--------+---------+----------+
- | age| int| Agecolumn|
- +--------+---------+----------+
++--------+---------+----------+
+|col_name|data_type| comment|
++--------+---------+----------+
+| age| int| Agecolumn|
++--------+---------+----------+
{% endhighlight %}
### Related Statements
diff --git a/docs/sql-ref-syntax-aux-describe-table.md b/docs/sql-ref-syntax-aux-describe-table.md
index a8eee97..63bf056 100644
--- a/docs/sql-ref-syntax-aux-describe-table.md
+++ b/docs/sql-ref-syntax-aux-describe-table.md
@@ -88,101 +88,101 @@ INSERT INTO customer PARTITION (state = 'AR') VALUES (100, 'Mike');
-- Returns basic metadata information for unqualified table `customer`
DESCRIBE TABLE customer;
- +-----------------------+---------+----------+
- | col_name|data_type| comment|
- +-----------------------+---------+----------+
- | cust_id| int| null|
- | name| string|Short name|
- | state| string| null|
- |# Partition Information| | |
- | # col_name|data_type| comment|
- | state| string| null|
- +-----------------------+---------+----------+
++-----------------------+---------+----------+
+| col_name|data_type| comment|
++-----------------------+---------+----------+
+| cust_id| int| null|
+| name| string|Short name|
+| state| string| null|
+|# Partition Information| | |
+| # col_name|data_type| comment|
+| state| string| null|
++-----------------------+---------+----------+
-- Returns basic metadata information for qualified table `customer`
DESCRIBE TABLE salesdb.customer;
- +-----------------------+---------+----------+
- | col_name|data_type| comment|
- +-----------------------+---------+----------+
- | cust_id| int| null|
- | name| string|Short name|
- | state| string| null|
- |# Partition Information| | |
- | # col_name|data_type| comment|
- | state| string| null|
- +-----------------------+---------+----------+
++-----------------------+---------+----------+
+| col_name|data_type| comment|
++-----------------------+---------+----------+
+| cust_id| int| null|
+| name| string|Short name|
+| state| string| null|
+|# Partition Information| | |
+| # col_name|data_type| comment|
+| state| string| null|
++-----------------------+---------+----------+
-- Returns additional metadata such as parent database, owner, access time etc.
DESCRIBE TABLE EXTENDED customer;
- +----------------------------+------------------------------+----------+
- | col_name| data_type| comment|
- +----------------------------+------------------------------+----------+
- | cust_id| int| null|
- | name| string|Short name|
- | state| string| null|
- | # Partition Information| | |
- | # col_name| data_type| comment|
- | state| string| null|
- | | | |
- |# Detailed Table Information| | |
- | Database| default| |
- | Table| customer| |
- | Owner| <TABLE OWNER>| |
- | Created Time| Tue Apr 07 22:56:34 JST 2020| |
- | Last Access| UNKNOWN| |
- | Created By| <SPARK VERSION>| |
- | Type| MANAGED| |
- | Provider| parquet| |
- | Location|file:/tmp/salesdb.db/custom...| |
- | Serde Library|org.apache.hadoop.hive.ql.i...| |
- | InputFormat|org.apache.hadoop.hive.ql.i...| |
- | OutputFormat|org.apache.hadoop.hive.ql.i...| |
- | Partition Provider| Catalog| |
- +----------------------------+------------------------------+----------+
++----------------------------+------------------------------+----------+
+| col_name| data_type| comment|
++----------------------------+------------------------------+----------+
+| cust_id| int| null|
+| name| string|Short name|
+| state| string| null|
+| # Partition Information| | |
+| # col_name| data_type| comment|
+| state| string| null|
+| | | |
+|# Detailed Table Information| | |
+| Database| default| |
+| Table| customer| |
+| Owner| <TABLE OWNER>| |
+| Created Time| Tue Apr 07 22:56:34 JST 2020| |
+| Last Access| UNKNOWN| |
+| Created By| <SPARK VERSION>| |
+| Type| MANAGED| |
+| Provider| parquet| |
+| Location|file:/tmp/salesdb.db/custom...| |
+| Serde Library|org.apache.hadoop.hive.ql.i...| |
+| InputFormat|org.apache.hadoop.hive.ql.i...| |
+| OutputFormat|org.apache.hadoop.hive.ql.i...| |
+| Partition Provider| Catalog| |
++----------------------------+------------------------------+----------+
-- Returns partition metadata such as partitioning column name, column type and comment.
DESCRIBE TABLE EXTENDED customer PARTITION (state = 'AR');
- +------------------------------+------------------------------+----------+
- | col_name| data_type| comment|
- +------------------------------+------------------------------+----------+
- | cust_id| int| null|
- | name| string|Short name|
- | state| string| null|
- | # Partition Information| | |
- | # col_name| data_type| comment|
- | state| string| null|
- | | | |
- |# Detailed Partition Inform...| | |
- | Database| default| |
- | Table| customer| |
- | Partition Values| [state=AR]| |
- | Location|file:/tmp/salesdb.db/custom...| |
- | Serde Library|org.apache.hadoop.hive.ql.i...| |
- | InputFormat|org.apache.hadoop.hive.ql.i...| |
- | OutputFormat|org.apache.hadoop.hive.ql.i...| |
- | Storage Properties|[serialization.format=1, pa...| |
- | Partition Parameters|{transient_lastDdlTime=1586...| |
- | Created Time| Tue Apr 07 23:05:43 JST 2020| |
- | Last Access| UNKNOWN| |
- | Partition Statistics| 659 bytes| |
- | | | |
- | # Storage Information| | |
- | Location|file:/tmp/salesdb.db/custom...| |
- | Serde Library|org.apache.hadoop.hive.ql.i...| |
- | InputFormat|org.apache.hadoop.hive.ql.i...| |
- | OutputFormat|org.apache.hadoop.hive.ql.i...| |
- +------------------------------+------------------------------+----------+
++------------------------------+------------------------------+----------+
+| col_name| data_type| comment|
++------------------------------+------------------------------+----------+
+| cust_id| int| null|
+| name| string|Short name|
+| state| string| null|
+| # Partition Information| | |
+| # col_name| data_type| comment|
+| state| string| null|
+| | | |
+|# Detailed Partition Inform...| | |
+| Database| default| |
+| Table| customer| |
+| Partition Values| [state=AR]| |
+| Location|file:/tmp/salesdb.db/custom...| |
+| Serde Library|org.apache.hadoop.hive.ql.i...| |
+| InputFormat|org.apache.hadoop.hive.ql.i...| |
+| OutputFormat|org.apache.hadoop.hive.ql.i...| |
+| Storage Properties|[serialization.format=1, pa...| |
+| Partition Parameters|{transient_lastDdlTime=1586...| |
+| Created Time| Tue Apr 07 23:05:43 JST 2020| |
+| Last Access| UNKNOWN| |
+| Partition Statistics| 659 bytes| |
+| | | |
+| # Storage Information| | |
+| Location|file:/tmp/salesdb.db/custom...| |
+| Serde Library|org.apache.hadoop.hive.ql.i...| |
+| InputFormat|org.apache.hadoop.hive.ql.i...| |
+| OutputFormat|org.apache.hadoop.hive.ql.i...| |
++------------------------------+------------------------------+----------+
-- Returns the metadata for `name` column.
-- Optional `TABLE` clause is omitted and column is fully qualified.
DESCRIBE customer salesdb.customer.name;
- +---------+----------+
- |info_name|info_value|
- +---------+----------+
- | col_name| name|
- |data_type| string|
- | comment|Short name|
- +---------+----------+
++---------+----------+
+|info_name|info_value|
++---------+----------+
+| col_name| name|
+|data_type| string|
+| comment|Short name|
++---------+----------+
{% endhighlight %}
### Related Statements
diff --git a/docs/sql-ref-syntax-aux-show-columns.md b/docs/sql-ref-syntax-aux-show-columns.md
index c8c90a9..7229bba 100644
--- a/docs/sql-ref-syntax-aux-show-columns.md
+++ b/docs/sql-ref-syntax-aux-show-columns.md
@@ -62,33 +62,33 @@ CREATE TABLE customer(
-- List the columns of `customer` table in current database.
SHOW COLUMNS IN customer;
- +---------+
- | col_name|
- +---------+
- | cust_cd|
- | name|
- |cust_addr|
- +---------+
++---------+
+| col_name|
++---------+
+| cust_cd|
+| name|
+|cust_addr|
++---------+
-- List the columns of `customer` table in `salesdb` database.
SHOW COLUMNS IN salesdb.customer;
- +---------+
- | col_name|
- +---------+
- | cust_cd|
- | name|
- |cust_addr|
- +---------+
++---------+
+| col_name|
++---------+
+| cust_cd|
+| name|
+|cust_addr|
++---------+
-- List the columns of `customer` table in `salesdb` database
SHOW COLUMNS IN customer IN salesdb;
- +---------+
- | col_name|
- +---------+
- | cust_cd|
- | name|
- |cust_addr|
- +---------+
++---------+
+| col_name|
++---------+
+| cust_cd|
+| name|
+|cust_addr|
++---------+
```
### Related Statements
diff --git a/docs/sql-ref-syntax-aux-show-create-table.md b/docs/sql-ref-syntax-aux-show-create-table.md
index 0a37c96..47a5290 100644
--- a/docs/sql-ref-syntax-aux-show-create-table.md
+++ b/docs/sql-ref-syntax-aux-show-create-table.md
@@ -50,16 +50,16 @@ CREATE TABLE test (c INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
TBLPROPERTIES ('prop1' = 'value1', 'prop2' = 'value2');
SHOW CREATE TABLE test;
- +----------------------------------------------------+
- | createtab_stmt|
- +----------------------------------------------------+
- |CREATE TABLE `default`.`test` (`c` INT)
- USING text
- TBLPROPERTIES (
- 'transient_lastDdlTime' = '1586269021',
- 'prop1' = 'value1',
- 'prop2' = 'value2')
- +----------------------------------------------------+
++----------------------------------------------------+
+| createtab_stmt|
++----------------------------------------------------+
+|CREATE TABLE `default`.`test` (`c` INT)
+ USING text
+ TBLPROPERTIES (
+ 'transient_lastDdlTime' = '1586269021',
+ 'prop1' = 'value1',
+ 'prop2' = 'value2')
++----------------------------------------------------+
{% endhighlight %}
### Related Statements
diff --git a/docs/sql-ref-syntax-aux-show-databases.md b/docs/sql-ref-syntax-aux-show-databases.md
index 3599009..c84898a 100644
--- a/docs/sql-ref-syntax-aux-show-databases.md
+++ b/docs/sql-ref-syntax-aux-show-databases.md
@@ -57,32 +57,32 @@ CREATE DATABASE payments_db;
-- Lists all the databases.
SHOW DATABASES;
- +------------+
- |databaseName|
- +------------+
- | default|
- | payments_db|
- | payroll_db|
- +------------+
++------------+
+|databaseName|
++------------+
+| default|
+| payments_db|
+| payroll_db|
++------------+
-- Lists databases with name starting with string pattern `pay`
SHOW DATABASES LIKE 'pay*';
- +------------+
- |databaseName|
- +------------+
- | payments_db|
- | payroll_db|
- +------------+
++------------+
+|databaseName|
++------------+
+| payments_db|
+| payroll_db|
++------------+
-- Lists all databases. Keywords SCHEMAS and DATABASES are interchangeable.
SHOW SCHEMAS;
- +------------+
- |databaseName|
- +------------+
- | default|
- | payments_db|
- | payroll_db|
- +------------+
++------------+
+|databaseName|
++------------+
+| default|
+| payments_db|
+| payroll_db|
++------------+
{% endhighlight %}
### Related Statements
diff --git a/docs/sql-ref-syntax-aux-show-functions.md b/docs/sql-ref-syntax-aux-show-functions.md
index ed22a3a..8a6de40 100644
--- a/docs/sql-ref-syntax-aux-show-functions.md
+++ b/docs/sql-ref-syntax-aux-show-functions.md
@@ -74,70 +74,70 @@ SHOW [ function_kind ] FUNCTIONS ( [ LIKE ] function_name | regex_pattern )
-- List a system function `trim` by searching both user defined and system
-- defined functions.
SHOW FUNCTIONS trim;
- +--------+
- |function|
- +--------+
- | trim|
- +--------+
++--------+
+|function|
++--------+
+| trim|
++--------+
-- List a system function `concat` by searching system defined functions.
SHOW SYSTEM FUNCTIONS concat;
- +--------+
- |function|
- +--------+
- | concat|
- +--------+
++--------+
+|function|
++--------+
+| concat|
++--------+
-- List a qualified function `max` from database `salesdb`.
SHOW SYSTEM FUNCTIONS salesdb.max;
- +--------+
- |function|
- +--------+
- | max|
- +--------+
++--------+
+|function|
++--------+
+| max|
++--------+
-- List all functions starting with `t`
SHOW FUNCTIONS LIKE 't*';
- +-----------------+
- | function|
- +-----------------+
- | tan|
- | tanh|
- | timestamp|
- | tinyint|
- | to_csv|
- | to_date|
- | to_json|
- | to_timestamp|
- |to_unix_timestamp|
- | to_utc_timestamp|
- | transform|
- | transform_keys|
- | transform_values|
- | translate|
- | trim|
- | trunc|
- | typeof|
- +-----------------+
++-----------------+
+| function|
++-----------------+
+| tan|
+| tanh|
+| timestamp|
+| tinyint|
+| to_csv|
+| to_date|
+| to_json|
+| to_timestamp|
+|to_unix_timestamp|
+| to_utc_timestamp|
+| transform|
+| transform_keys|
+| transform_values|
+| translate|
+| trim|
+| trunc|
+| typeof|
++-----------------+
-- List all functions starting with `yea` or `windo`
SHOW FUNCTIONS LIKE 'yea*|windo*';
- +--------+
- |function|
- +--------+
- | window|
- | year|
- +--------+
++--------+
+|function|
++--------+
+| window|
+| year|
++--------+
-- Use normal regex pattern to list function names that has 4 characters
-- with `t` as the starting character.
SHOW FUNCTIONS LIKE 't[a-z][a-z][a-z]';
- +--------+
- |function|
- +--------+
- | tanh|
- | trim|
- +--------+
++--------+
+|function|
++--------+
+| tanh|
+| trim|
++--------+
{% endhighlight %}
### Related Statements
diff --git a/docs/sql-ref-syntax-aux-show-partitions.md b/docs/sql-ref-syntax-aux-show-partitions.md
index 31b881e..592833b 100644
--- a/docs/sql-ref-syntax-aux-show-partitions.md
+++ b/docs/sql-ref-syntax-aux-show-partitions.md
@@ -67,48 +67,48 @@ INSERT INTO customer PARTITION (state = 'AZ', city = 'Peoria') VALUES (300, 'Dan
-- Lists all partitions for table `customer`
SHOW PARTITIONS customer;
- +----------------------+
- | partition|
- +----------------------+
- | state=AZ/city=Peoria|
- | state=CA/city=Fremont|
- |state=CA/city=San Jose|
- +----------------------+
++----------------------+
+| partition|
++----------------------+
+| state=AZ/city=Peoria|
+| state=CA/city=Fremont|
+|state=CA/city=San Jose|
++----------------------+
-- Lists all partitions for the qualified table `customer`
SHOW PARTITIONS salesdb.customer;
- +----------------------+
- | partition|
- +----------------------+
- | state=AZ/city=Peoria|
- | state=CA/city=Fremont|
- |state=CA/city=San Jose|
- +----------------------+
++----------------------+
+| partition|
++----------------------+
+| state=AZ/city=Peoria|
+| state=CA/city=Fremont|
+|state=CA/city=San Jose|
++----------------------+
-- Specify a full partition spec to list specific partition
SHOW PARTITIONS customer PARTITION (state = 'CA', city = 'Fremont');
- +---------------------+
- | partition|
- +---------------------+
- |state=CA/city=Fremont|
- +---------------------+
++---------------------+
+| partition|
++---------------------+
+|state=CA/city=Fremont|
++---------------------+
-- Specify a partial partition spec to list the specific partitions
SHOW PARTITIONS customer PARTITION (state = 'CA');
- +----------------------+
- | partition|
- +----------------------+
- | state=CA/city=Fremont|
- |state=CA/city=San Jose|
- +----------------------+
++----------------------+
+| partition|
++----------------------+
+| state=CA/city=Fremont|
+|state=CA/city=San Jose|
++----------------------+
-- Specify a partial spec to list specific partition
SHOW PARTITIONS customer PARTITION (city = 'San Jose');
- +----------------------+
- | partition|
- +----------------------+
- |state=CA/city=San Jose|
- +----------------------+
++----------------------+
+| partition|
++----------------------+
+|state=CA/city=San Jose|
++----------------------+
{% endhighlight %}
### Related Statements
diff --git a/docs/sql-ref-syntax-aux-show-table.md b/docs/sql-ref-syntax-aux-show-table.md
index c688a99..3f58804 100644
--- a/docs/sql-ref-syntax-aux-show-table.md
+++ b/docs/sql-ref-syntax-aux-show-table.md
@@ -75,104 +75,104 @@ INSERT INTO employee PARTITION (grade = 2) VALUES ('suj');
-- Show the details of the table
SHOW TABLE EXTENDED LIKE 'employee';
- +--------+---------+-----------+--------------------------------------------------------------+
- |database|tableName|isTemporary| information |
- +--------+---------+-----------+--------------------------------------------------------------+
- |default |employee |false |Database: default
- Table: employee
- Owner: root
- Created Time: Fri Aug 30 15:10:21 IST 2019
- Last Access: Thu Jan 01 05:30:00 IST 1970
- Created By: Spark 3.0.0-SNAPSHOT
- Type: MANAGED
- Provider: hive
- Table Properties: [transient_lastDdlTime=1567158021]
- Location: file:/opt/spark1/spark/spark-warehouse/employee
- Serde Library: org.apache.hadoop.hive.serde2.lazy
- .LazySimpleSerDe
- InputFormat: org.apache.hadoop.mapred.TextInputFormat
- OutputFormat: org.apache.hadoop.hive.ql.io
- .HiveIgnoreKeyTextOutputFormat
- Storage Properties: [serialization.format=1]
- Partition Provider: Catalog
- Partition Columns: [`grade`]
- Schema: root
- |-- name: string (nullable = true)
- |-- grade: integer (nullable = true)
++--------+---------+-----------+--------------------------------------------------------------+
+|database|tableName|isTemporary| information |
++--------+---------+-----------+--------------------------------------------------------------+
+|default |employee |false |Database: default
+ Table: employee
+ Owner: root
+ Created Time: Fri Aug 30 15:10:21 IST 2019
+ Last Access: Thu Jan 01 05:30:00 IST 1970
+ Created By: Spark 3.0.0-SNAPSHOT
+ Type: MANAGED
+ Provider: hive
+ Table Properties: [transient_lastDdlTime=1567158021]
+ Location: file:/opt/spark1/spark/spark-warehouse/employee
+ Serde Library: org.apache.hadoop.hive.serde2.lazy
+ .LazySimpleSerDe
+ InputFormat: org.apache.hadoop.mapred.TextInputFormat
+ OutputFormat: org.apache.hadoop.hive.ql.io
+ .HiveIgnoreKeyTextOutputFormat
+ Storage Properties: [serialization.format=1]
+ Partition Provider: Catalog
+ Partition Columns: [`grade`]
+ Schema: root
+ |-- name: string (nullable = true)
+ |-- grade: integer (nullable = true)
- +--------+---------+-----------+--------------------------------------------------------------+
++--------+---------+-----------+--------------------------------------------------------------+
-- showing the multiple table details with pattern matching
SHOW TABLE EXTENDED LIKE `employe*`;
- +--------+---------+-----------+--------------------------------------------------------------+
- |database|tableName|isTemporary| information |
- +--------+---------+-----------+--------------------------------------------------------------+
- |default |employee |false |Database: default
- Table: employee
- Owner: root
- Created Time: Fri Aug 30 15:10:21 IST 2019
- Last Access: Thu Jan 01 05:30:00 IST 1970
- Created By: Spark 3.0.0-SNAPSHOT
- Type: MANAGED
- Provider: hive
- Table Properties: [transient_lastDdlTime=1567158021]
- Location: file:/opt/spark1/spark/spark-warehouse/employee
- Serde Library: org.apache.hadoop.hive.serde2.lazy
- .LazySimpleSerDe
- InputFormat: org.apache.hadoop.mapred.TextInputFormat
- OutputFormat: org.apache.hadoop.hive.ql.io
- .HiveIgnoreKeyTextOutputFormat
- Storage Properties: [serialization.format=1]
- Partition Provider: Catalog
- Partition Columns: [`grade`]
- Schema: root
- |-- name: string (nullable = true)
- |-- grade: integer (nullable = true)
++--------+---------+-----------+--------------------------------------------------------------+
+|database|tableName|isTemporary| information |
++--------+---------+-----------+--------------------------------------------------------------+
+|default |employee |false |Database: default
+ Table: employee
+ Owner: root
+ Created Time: Fri Aug 30 15:10:21 IST 2019
+ Last Access: Thu Jan 01 05:30:00 IST 1970
+ Created By: Spark 3.0.0-SNAPSHOT
+ Type: MANAGED
+ Provider: hive
+ Table Properties: [transient_lastDdlTime=1567158021]
+ Location: file:/opt/spark1/spark/spark-warehouse/employee
+ Serde Library: org.apache.hadoop.hive.serde2.lazy
+ .LazySimpleSerDe
+ InputFormat: org.apache.hadoop.mapred.TextInputFormat
+ OutputFormat: org.apache.hadoop.hive.ql.io
+ .HiveIgnoreKeyTextOutputFormat
+ Storage Properties: [serialization.format=1]
+ Partition Provider: Catalog
+ Partition Columns: [`grade`]
+ Schema: root
+ |-- name: string (nullable = true)
+ |-- grade: integer (nullable = true)
- |default |employee1|false |Database: default
- Table: employee1
- Owner: root
- Created Time: Fri Aug 30 15:22:33 IST 2019
- Last Access: Thu Jan 01 05:30:00 IST 1970
- Created By: Spark 3.0.0-SNAPSHOT
- Type: MANAGED
- Provider: hive
- Table Properties: [transient_lastDdlTime=1567158753]
- Location: file:/opt/spark1/spark/spark-warehouse/employee1
- Serde Library: org.apache.hadoop.hive.serde2.lazy
- .LazySimpleSerDe
- InputFormat: org.apache.hadoop.mapred.TextInputFormat
- OutputFormat: org.apache.hadoop.hive.ql.io
- .HiveIgnoreKeyTextOutputFormat
- Storage Properties: [serialization.format=1]
- Partition Provider: Catalog
- Schema: root
- |-- name: string (nullable = true)
+|default |employee1|false |Database: default
+ Table: employee1
+ Owner: root
+ Created Time: Fri Aug 30 15:22:33 IST 2019
+ Last Access: Thu Jan 01 05:30:00 IST 1970
+ Created By: Spark 3.0.0-SNAPSHOT
+ Type: MANAGED
+ Provider: hive
+ Table Properties: [transient_lastDdlTime=1567158753]
+ Location: file:/opt/spark1/spark/spark-warehouse/employee1
+ Serde Library: org.apache.hadoop.hive.serde2.lazy
+ .LazySimpleSerDe
+ InputFormat: org.apache.hadoop.mapred.TextInputFormat
+ OutputFormat: org.apache.hadoop.hive.ql.io
+ .HiveIgnoreKeyTextOutputFormat
+ Storage Properties: [serialization.format=1]
+ Partition Provider: Catalog
+ Schema: root
+ |-- name: string (nullable = true)
- +--------+---------+----------+---------------------------------------------------------------+
++--------+---------+----------+---------------------------------------------------------------+
-- show partition file system details
SHOW TABLE EXTENDED IN `default` LIKE `employee` PARTITION (`grade=1`);
- +--------+---------+-----------+--------------------------------------------------------------+
- |database|tableName|isTemporary| information |
- +--------+---------+-----------+--------------------------------------------------------------+
- |default |employee |false |Partition Values: [grade=1]
- Location: file:/opt/spark1/spark/spark-warehouse/employee
- /grade=1
- Serde Library: org.apache.hadoop.hive.serde2.lazy
- .LazySimpleSerDe
- InputFormat: org.apache.hadoop.mapred.TextInputFormat
- OutputFormat: org.apache.hadoop.hive.ql.io
- .HiveIgnoreKeyTextOutputFormat
- Storage Properties: [serialization.format=1]
- Partition Parameters: {rawDataSize=-1, numFiles=1,
- transient_lastDdlTime=1567158221, totalSize=4,
- COLUMN_STATS_ACCURATE=false, numRows=-1}
- Created Time: Fri Aug 30 15:13:41 IST 2019
- Last Access: Thu Jan 01 05:30:00 IST 1970
- Partition Statistics: 4 bytes
- |
- +--------+---------+-----------+--------------------------------------------------------------+
++--------+---------+-----------+--------------------------------------------------------------+
+|database|tableName|isTemporary| information |
++--------+---------+-----------+--------------------------------------------------------------+
+|default |employee |false |Partition Values: [grade=1]
+ Location: file:/opt/spark1/spark/spark-warehouse/employee
+ /grade=1
+ Serde Library: org.apache.hadoop.hive.serde2.lazy
+ .LazySimpleSerDe
+ InputFormat: org.apache.hadoop.mapred.TextInputFormat
+ OutputFormat: org.apache.hadoop.hive.ql.io
+ .HiveIgnoreKeyTextOutputFormat
+ Storage Properties: [serialization.format=1]
+ Partition Parameters: {rawDataSize=-1, numFiles=1,
+ transient_lastDdlTime=1567158221, totalSize=4,
+ COLUMN_STATS_ACCURATE=false, numRows=-1}
+ Created Time: Fri Aug 30 15:13:41 IST 2019
+ Last Access: Thu Jan 01 05:30:00 IST 1970
+ Partition Statistics: 4 bytes
+ |
++--------+---------+-----------+--------------------------------------------------------------+
-- show partition file system details with regex fails as shown below
SHOW TABLE EXTENDED IN `default` LIKE `empl*` PARTITION (`grade=1`);
diff --git a/docs/sql-ref-syntax-aux-show-tables.md b/docs/sql-ref-syntax-aux-show-tables.md
index cd54d45..62eb3dd 100644
--- a/docs/sql-ref-syntax-aux-show-tables.md
+++ b/docs/sql-ref-syntax-aux-show-tables.md
@@ -57,50 +57,50 @@ SHOW TABLES [ { FROM | IN } database_name ] [ LIKE regex_pattern ]
{% highlight sql %}
-- List all tables in default database
SHOW TABLES;
- +--------+---------+-----------+
- |database|tableName|isTemporary|
- +--------+---------+-----------+
- | default| sam| false|
- | default| sam1| false|
- | default| suj| false|
- +--------+---------+-----------+
++--------+---------+-----------+
+|database|tableName|isTemporary|
++--------+---------+-----------+
+| default| sam| false|
+| default| sam1| false|
+| default| suj| false|
++--------+---------+-----------+
-- List all tables from userdb database
SHOW TABLES FROM userdb;
- +--------+---------+-----------+
- |database|tableName|isTemporary|
- +--------+---------+-----------+
- | userdb| user1| false|
- | userdb| user2| false|
- +--------+---------+-----------+
++--------+---------+-----------+
+|database|tableName|isTemporary|
++--------+---------+-----------+
+| userdb| user1| false|
+| userdb| user2| false|
++--------+---------+-----------+
-- List all tables in userdb database
SHOW TABLES IN userdb;
- +--------+---------+-----------+
- |database|tableName|isTemporary|
- +--------+---------+-----------+
- | userdb| user1| false|
- | userdb| user2| false|
- +--------+---------+-----------+
++--------+---------+-----------+
+|database|tableName|isTemporary|
++--------+---------+-----------+
+| userdb| user1| false|
+| userdb| user2| false|
++--------+---------+-----------+
-- List all tables from default database matching the pattern `sam*`
SHOW TABLES FROM default LIKE 'sam*';
- +--------+---------+-----------+
- |database|tableName|isTemporary|
- +--------+---------+-----------+
- | default| sam| false|
- | default| sam1| false|
- +--------+---------+-----------+
++--------+---------+-----------+
+|database|tableName|isTemporary|
++--------+---------+-----------+
+| default| sam| false|
+| default| sam1| false|
++--------+---------+-----------+
-- List all tables matching the pattern `sam*|suj`
SHOW TABLES LIKE 'sam*|suj';
- +--------+---------+-----------+
- |database|tableName|isTemporary|
- +--------+---------+-----------+
- | default| sam| false|
- | default| sam1| false|
- | default| suj| false|
- +--------+---------+-----------+
++--------+---------+-----------+
+|database|tableName|isTemporary|
++--------+---------+-----------+
+| default| sam| false|
+| default| sam1| false|
+| default| suj| false|
++--------+---------+-----------+
{% endhighlight %}
### Related Statements
diff --git a/docs/sql-ref-syntax-aux-show-tblproperties.md b/docs/sql-ref-syntax-aux-show-tblproperties.md
index 26e57ef..662aaad 100644
--- a/docs/sql-ref-syntax-aux-show-tblproperties.md
+++ b/docs/sql-ref-syntax-aux-show-tblproperties.md
@@ -76,40 +76,40 @@ CREATE TABLE customer(cust_code INT, name VARCHAR(100), cust_addr STRING)
-- show all the user specified properties for table `customer`
SHOW TBLPROPERTIES customer;
- +---------------------+----------+
- | key| value|
- +---------------------+----------+
- | created.by.user| John|
- | created.date|01-01-2001|
- |transient_lastDdlTime|1567554931|
- +---------------------+----------+
++---------------------+----------+
+| key| value|
++---------------------+----------+
+| created.by.user| John|
+| created.date|01-01-2001|
+|transient_lastDdlTime|1567554931|
++---------------------+----------+
-- show all the user specified properties for a qualified table `customer`
-- in database `salesdb`
SHOW TBLPROPERTIES salesdb.customer;
- +---------------------+----------+
- | key| value|
- +---------------------+----------+
- | created.by.user| John|
- | created.date|01-01-2001|
- |transient_lastDdlTime|1567554931|
- +---------------------+----------+
++---------------------+----------+
+| key| value|
++---------------------+----------+
+| created.by.user| John|
+| created.date|01-01-2001|
+|transient_lastDdlTime|1567554931|
++---------------------+----------+
-- show value for unquoted property key `created.by.user`
SHOW TBLPROPERTIES customer (created.by.user);
- +-----+
- |value|
- +-----+
- | John|
- +-----+
++-----+
+|value|
++-----+
+| John|
++-----+
-- show value for property `created.date`` specified as string literal
SHOW TBLPROPERTIES customer ('created.date');
- +----------+
- | value|
- +----------+
- |01-01-2001|
- +----------+
++----------+
+| value|
++----------+
+|01-01-2001|
++----------+
{% endhighlight %}
### Related Statements
diff --git a/docs/sql-ref-syntax-aux-show-views.md b/docs/sql-ref-syntax-aux-show-views.md
index b1a8d3b..29ad6ca 100644
--- a/docs/sql-ref-syntax-aux-show-views.md
+++ b/docs/sql-ref-syntax-aux-show-views.md
@@ -66,52 +66,52 @@ CREATE TEMP VIEW temp2 AS SELECT 1 as col1;
-- List all views in default database
SHOW VIEWS;
- +-------------+------------+--------------+--+
- | namespace | viewName | isTemporary |
- +-------------+------------+--------------+--+
- | default | sam | false |
- | default | sam1 | false |
- | default | suj | false |
- | | temp2 | true |
- +-------------+------------+--------------+--+
++-------------+------------+--------------+
+| namespace | viewName | isTemporary |
++-------------+------------+--------------+
+| default | sam | false |
+| default | sam1 | false |
+| default | suj | false |
+| | temp2 | true |
++-------------+------------+--------------+
-- List all views from userdb database
SHOW VIEWS FROM userdb;
- +-------------+------------+--------------+--+
- | namespace | viewName | isTemporary |
- +-------------+------------+--------------+--+
- | userdb | user1 | false |
- | userdb | user2 | false |
- | | temp2 | true |
- +-------------+------------+--------------+--+
++-------------+------------+--------------+
+| namespace | viewName | isTemporary |
++-------------+------------+--------------+
+| userdb | user1 | false |
+| userdb | user2 | false |
+| | temp2 | true |
++-------------+------------+--------------+
-- List all views in global temp view database
SHOW VIEWS IN global_temp;
- +-------------+------------+--------------+--+
- | namespace | viewName | isTemporary |
- +-------------+------------+--------------+--+
- | global_temp | temp1 | true |
- | | temp2 | true |
- +-------------+------------+--------------+--+
++-------------+------------+--------------+
+| namespace | viewName | isTemporary |
++-------------+------------+--------------+
+| global_temp | temp1 | true |
+| | temp2 | true |
++-------------+------------+--------------+
-- List all views from default database matching the pattern `sam*`
SHOW VIEWS FROM default LIKE 'sam*';
- +-----------+------------+--------------+--+
- | namespace | viewName | isTemporary |
- +-----------+------------+--------------+--+
- | default | sam | false |
- | default | sam1 | false |
- +-----------+------------+--------------+--+
++-----------+------------+--------------+
+| namespace | viewName | isTemporary |
++-----------+------------+--------------+
+| default | sam | false |
+| default | sam1 | false |
++-----------+------------+--------------+
-- List all views from the current database matching the pattern `sam|suj｜temp*`
SHOW VIEWS LIKE 'sam|suj|temp*';
- +-------------+------------+--------------+--+
- | namespace | viewName | isTemporary |
- +-------------+------------+--------------+--+
- | default | sam | false |
- | default | suj | false |
- | | temp2 | true |
- +-------------+------------+--------------+--+
++-------------+------------+--------------+
+| namespace | viewName | isTemporary |
++-------------+------------+--------------+
+| default | sam | false |
+| default | suj | false |
+| | temp2 | true |
++-------------+------------+--------------+
{% endhighlight %}
diff --git a/docs/sql-ref-syntax-ddl-alter-database.md b/docs/sql-ref-syntax-ddl-alter-database.md
index 65b85dc..2d5860c 100644
--- a/docs/sql-ref-syntax-ddl-alter-database.md
+++ b/docs/sql-ref-syntax-ddl-alter-database.md
@@ -52,14 +52,14 @@ ALTER DATABASE inventory SET DBPROPERTIES ('Edited-by' = 'John', 'Edit-date' = '
-- Verify that properties are set.
DESCRIBE DATABASE EXTENDED inventory;
- +-------------------------+------------------------------------------+
- |database_description_item| database_description_value|
- +-------------------------+------------------------------------------+
- | Database Name| inventory|
- | Description| |
- | Location| file:/temp/spark-warehouse/inventory.db|
- | Properties|((Edit-date,01/01/2001), (Edited-by,John))|
- +-------------------------+------------------------------------------+
++-------------------------+------------------------------------------+
+|database_description_item| database_description_value|
++-------------------------+------------------------------------------+
+| Database Name| inventory|
+| Description| |
+| Location| file:/temp/spark-warehouse/inventory.db|
+| Properties|((Edit-date,01/01/2001), (Edited-by,John))|
++-------------------------+------------------------------------------+
{% endhighlight %}
### Related Statements
diff --git a/docs/sql-ref-syntax-ddl-alter-table.md b/docs/sql-ref-syntax-ddl-alter-table.md
index 0a74aa0..f81585f 100644
--- a/docs/sql-ref-syntax-ddl-alter-table.md
+++ b/docs/sql-ref-syntax-ddl-alter-table.md
@@ -282,186 +282,186 @@ ALTER TABLE table_identifier [ partition_spec ] SET LOCATION 'new_location'
{% highlight sql %}
-- RENAME table
DESC student;
- +-----------------------+---------+-------+
- | col_name|data_type|comment|
- +-----------------------+---------+-------+
- | name| string| NULL|
- | rollno| int| NULL|
- | age| int| NULL|
- |# Partition Information| | |
- | # col_name|data_type|comment|
- | age| int| NULL|
- +-----------------------+---------+-------+
++-----------------------+---------+-------+
+| col_name|data_type|comment|
++-----------------------+---------+-------+
+| name| string| NULL|
+| rollno| int| NULL|
+| age| int| NULL|
+|# Partition Information| | |
+| # col_name|data_type|comment|
+| age| int| NULL|
++-----------------------+---------+-------+
ALTER TABLE Student RENAME TO StudentInfo;
-- After Renaming the table
DESC StudentInfo;
- +-----------------------+---------+-------+
- | col_name|data_type|comment|
- +-----------------------+---------+-------+
- | name| string| NULL|
- | rollno| int| NULL|
- | age| int| NULL|
- |# Partition Information| | |
- | # col_name|data_type|comment|
- | age| int| NULL|
- +-----------------------+---------+-------+
++-----------------------+---------+-------+
+| col_name|data_type|comment|
++-----------------------+---------+-------+
+| name| string| NULL|
+| rollno| int| NULL|
+| age| int| NULL|
+|# Partition Information| | |
+| # col_name|data_type|comment|
+| age| int| NULL|
++-----------------------+---------+-------+
-- RENAME partition
SHOW PARTITIONS StudentInfo;
- +---------+
- |partition|
- +---------+
- | age=10|
- | age=11|
- | age=12|
- +---------+
++---------+
+|partition|
++---------+
+| age=10|
+| age=11|
+| age=12|
++---------+
ALTER TABLE default.StudentInfo PARTITION (age='10') RENAME TO PARTITION (age='15');
-- After renaming Partition
SHOW PARTITIONS StudentInfo;
- +---------+
- |partition|
- +---------+
- | age=11|
- | age=12|
- | age=15|
- +---------+
++---------+
+|partition|
++---------+
+| age=11|
+| age=12|
+| age=15|
++---------+
-- Add new columns to a table
DESC StudentInfo;
- +-----------------------+---------+-------+
- | col_name|data_type|comment|
- +-----------------------+---------+-------+
- | name| string| NULL|
- | rollno| int| NULL|
- | age| int| NULL|
- |# Partition Information| | |
- | # col_name|data_type|comment|
- | age| int| NULL|
- +-----------------------+---------+-------+
++-----------------------+---------+-------+
+| col_name|data_type|comment|
++-----------------------+---------+-------+
+| name| string| NULL|
+| rollno| int| NULL|
+| age| int| NULL|
+|# Partition Information| | |
+| # col_name|data_type|comment|
+| age| int| NULL|
++-----------------------+---------+-------+
ALTER TABLE StudentInfo ADD columns (LastName string, DOB timestamp);
-- After Adding New columns to the table
DESC StudentInfo;
- +-----------------------+---------+-------+
- | col_name|data_type|comment|
- +-----------------------+---------+-------+
- | name| string| NULL|
- | rollno| int| NULL|
- | LastName| string| NULL|
- | DOB|timestamp| NULL|
- | age| int| NULL|
- |# Partition Information| | |
- | # col_name|data_type|comment|
- | age| int| NULL|
- +-----------------------+---------+-------+
++-----------------------+---------+-------+
+| col_name|data_type|comment|
++-----------------------+---------+-------+
+| name| string| NULL|
+| rollno| int| NULL|
+| LastName| string| NULL|
+| DOB|timestamp| NULL|
+| age| int| NULL|
+|# Partition Information| | |
+| # col_name|data_type|comment|
+| age| int| NULL|
++-----------------------+---------+-------+
-- Add a new partition to a table
SHOW PARTITIONS StudentInfo;
- +---------+
- |partition|
- +---------+
- | age=11|
- | age=12|
- | age=15|
- +---------+
++---------+
+|partition|
++---------+
+| age=11|
+| age=12|
+| age=15|
++---------+
ALTER TABLE StudentInfo ADD IF NOT EXISTS PARTITION (age=18);
-- After adding a new partition to the table
SHOW PARTITIONS StudentInfo;
- +---------+
- |partition|
- +---------+
- | age=11|
- | age=12|
- | age=15|
- | age=18|
- +---------+
++---------+
+|partition|
++---------+
+| age=11|
+| age=12|
+| age=15|
+| age=18|
++---------+
-- Drop a partition from the table
SHOW PARTITIONS StudentInfo;
- +---------+
- |partition|
- +---------+
- | age=11|
- | age=12|
- | age=15|
- | age=18|
- +---------+
++---------+
+|partition|
++---------+
+| age=11|
+| age=12|
+| age=15|
+| age=18|
++---------+
ALTER TABLE StudentInfo DROP IF EXISTS PARTITION (age=18);
-- After dropping the partition of the table
SHOW PARTITIONS StudentInfo;
- +---------+
- |partition|
- +---------+
- | age=11|
- | age=12|
- | age=15|
- +---------+
++---------+
+|partition|
++---------+
+| age=11|
+| age=12|
+| age=15|
++---------+
-- Adding multiple partitions to the table
SHOW PARTITIONS StudentInfo;
- +---------+
- |partition|
- +---------+
- | age=11|
- | age=12|
- | age=15|
- +---------+
++---------+
+|partition|
++---------+
+| age=11|
+| age=12|
+| age=15|
++---------+
ALTER TABLE StudentInfo ADD IF NOT EXISTS PARTITION (age=18) PARTITION (age=20);
-- After adding multiple partitions to the table
SHOW PARTITIONS StudentInfo;
- +---------+
- |partition|
- +---------+
- | age=11|
- | age=12|
- | age=15|
- | age=18|
- | age=20|
- +---------+
++---------+
+|partition|
++---------+
+| age=11|
+| age=12|
+| age=15|
+| age=18|
+| age=20|
++---------+
-- ALTER OR CHANGE COLUMNS
DESC StudentInfo;
- +-----------------------+---------+-------+
- | col_name|data_type|comment|
- +-----------------------+---------+-------+
- | name| string| NULL|
- | rollno| int| NULL|
- | LastName| string| NULL|
- | DOB|timestamp| NULL|
- | age| int| NULL|
- |# Partition Information| | |
- | # col_name|data_type|comment|
- | age| int| NULL|
- +-----------------------+---------+-------+
++-----------------------+---------+-------+
+| col_name|data_type|comment|
++-----------------------+---------+-------+
+| name| string| NULL|
+| rollno| int| NULL|
+| LastName| string| NULL|
+| DOB|timestamp| NULL|
+| age| int| NULL|
+|# Partition Information| | |
+| # col_name|data_type|comment|
+| age| int| NULL|
++-----------------------+---------+-------+
ALTER TABLE StudentInfo ALTER COLUMN name COMMENT "new comment";
--After ALTER or CHANGE COLUMNS
DESC StudentInfo;
- +-----------------------+---------+-----------+
- | col_name|data_type| comment|
- +-----------------------+---------+-----------+
- | name| string|new comment|
- | rollno| int| NULL|
- | LastName| string| NULL|
- | DOB|timestamp| NULL|
- | age| int| NULL|
- |# Partition Information| | |
- | # col_name|data_type| comment|
- | age| int| NULL|
- +-----------------------+---------+-----------+
++-----------------------+---------+-----------+
+| col_name|data_type| comment|
++-----------------------+---------+-----------+
+| name| string|new comment|
+| rollno| int| NULL|
+| LastName| string| NULL|
+| DOB|timestamp| NULL|
+| age| int| NULL|
+|# Partition Information| | |
+| # col_name|data_type| comment|
+| age| int| NULL|
++-----------------------+---------+-----------+
-- Change the fileformat
ALTER TABLE loc_orc SET fileformat orc;
diff --git a/docs/sql-ref-syntax-ddl-alter-view.md b/docs/sql-ref-syntax-ddl-alter-view.md
index 06c1b65..c288769 100644
--- a/docs/sql-ref-syntax-ddl-alter-view.md
+++ b/docs/sql-ref-syntax-ddl-alter-view.md
@@ -141,83 +141,83 @@ ALTER VIEW tempdb1.v1 RENAME TO tempdb1.v2;
-- Verify that the new view is created.
DESCRIBE TABLE EXTENDED tempdb1.v2;
- +----------------------------+----------+-------+
- | col_name|data_type |comment|
- +----------------------------+----------+-------+
- | c1| int| null|
- | c2| string| null|
- | | | |
- |# Detailed Table Information| | |
- | Database| tempdb1| |
- | Table| v2| |
- +----------------------------+----------+-------+
++----------------------------+----------+-------+
+| col_name|data_type |comment|
++----------------------------+----------+-------+
+| c1| int| null|
+| c2| string| null|
+| | | |
+|# Detailed Table Information| | |
+| Database| tempdb1| |
+| Table| v2| |
++----------------------------+----------+-------+
-- Before ALTER VIEW SET TBLPROPERTIES
DESC TABLE EXTENDED tempdb1.v2;
- +----------------------------+----------+-------+
- | col_name| data_type|comment|
- +----------------------------+----------+-------+
- | c1| int| null|
- | c2| string| null|
- | | | |
- |# Detailed Table Information| | |
- | Database| tempdb1| |
- | Table| v2| |
- | Table Properties| [....]| |
- +----------------------------+----------+-------+
++----------------------------+----------+-------+
+| col_name| data_type|comment|
++----------------------------+----------+-------+
+| c1| int| null|
+| c2| string| null|
+| | | |
+|# Detailed Table Information| | |
+| Database| tempdb1| |
+| Table| v2| |
+| Table Properties| [....]| |
++----------------------------+----------+-------+
-- Set properties in TBLPROPERTIES
ALTER VIEW tempdb1.v2 SET TBLPROPERTIES ('created.by.user' = "John", 'created.date' = '01-01-2001' );
-- Use `DESCRIBE TABLE EXTENDED tempdb1.v2` to verify
DESC TABLE EXTENDED tempdb1.v2;
- +----------------------------+-----------------------------------------------------+-------+
- | col_name| data_type|comment|
- +----------------------------+-----------------------------------------------------+-------+
- | c1| int| null|
- | c2| string| null|
- | | | |
- |# Detailed Table Information| | |
- | Database| tempdb1| |
- | Table| v2| |
- | Table Properties|[created.by.user=John, created.date=01-01-2001, ....]| |
- +----------------------------+-----------------------------------------------------+-------+
++----------------------------+-----------------------------------------------------+-------+
+| col_name| data_type|comment|
++----------------------------+-----------------------------------------------------+-------+
+| c1| int| null|
+| c2| string| null|
+| | | |
+|# Detailed Table Information| | |
+| Database| tempdb1| |
+| Table| v2| |
+| Table Properties|[created.by.user=John, created.date=01-01-2001, ....]| |
++----------------------------+-----------------------------------------------------+-------+
-- Remove the key `created.by.user` and `created.date` from `TBLPROPERTIES`
ALTER VIEW tempdb1.v2 UNSET TBLPROPERTIES ('created.by.user', 'created.date');
--Use `DESC TABLE EXTENDED tempdb1.v2` to verify the changes
DESC TABLE EXTENDED tempdb1.v2;
- +----------------------------+----------+-------+
- | col_name| data_type|comment|
- +----------------------------+----------+-------+
- | c1| int| null|
- | c2| string| null|
- | | | |
- |# Detailed Table Information| | |
- | Database| tempdb1| |
- | Table| v2| |
- | Table Properties| [....]| |
- +----------------------------+----------+-------+
++----------------------------+----------+-------+
+| col_name| data_type|comment|
++----------------------------+----------+-------+
+| c1| int| null|
+| c2| string| null|
+| | | |
+|# Detailed Table Information| | |
+| Database| tempdb1| |
+| Table| v2| |
+| Table Properties| [....]| |
++----------------------------+----------+-------+
-- Change the view definition
ALTER VIEW tempdb1.v2 AS SELECT * FROM tempdb1.v1;
-- Use `DESC TABLE EXTENDED` to verify
DESC TABLE EXTENDED tempdb1.v2;
- +----------------------------+---------------------------+-------+
- | col_name| data_type|comment|
- +----------------------------+---------------------------+-------+
- | c1| int| null|
- | c2| string| null|
- | | | |
- |# Detailed Table Information| | |
- | Database| tempdb1| |
- | Table| v2| |
- | Type| VIEW| |
- | View Text| select * from tempdb1.v1| |
- | View Original Text| select * from tempdb1.v1| |
- +----------------------------+---------------------------+-------+
++----------------------------+---------------------------+-------+
+| col_name| data_type|comment|
++----------------------------+---------------------------+-------+
+| c1| int| null|
+| c2| string| null|
+| | | |
+|# Detailed Table Information| | |
+| Database| tempdb1| |
+| Table| v2| |
+| Type| VIEW| |
+| View Text| select * from tempdb1.v1| |
+| View Original Text| select * from tempdb1.v1| |
++----------------------------+---------------------------+-------+
{% endhighlight %}
### Related Statements
diff --git a/docs/sql-ref-syntax-ddl-create-database.md b/docs/sql-ref-syntax-ddl-create-database.md
index 6f74acd..0ef0dfb 100644
--- a/docs/sql-ref-syntax-ddl-create-database.md
+++ b/docs/sql-ref-syntax-ddl-create-database.md
@@ -68,14 +68,14 @@ CREATE DATABASE IF NOT EXISTS customer_db COMMENT 'This is customer database' LO
-- Verify that properties are set.
DESCRIBE DATABASE EXTENDED customer_db;
- +-------------------------+--------------------------+
- |database_description_item|database_description_value|
- +-------------------------+--------------------------+
- | Database Name| customer_db|
- | Description| This is customer database|
- | Location| hdfs://hacluster/user|
- | Properties| ((ID,001), (Name,John))|
- +-------------------------+--------------------------+
++-------------------------+--------------------------+
+|database_description_item|database_description_value|
++-------------------------+--------------------------+
+| Database Name| customer_db|
+| Description| This is customer database|
+| Location| hdfs://hacluster/user|
+| Properties| ((ID,001), (Name,John))|
++-------------------------+--------------------------+
{% endhighlight %}
### Related Statements
diff --git a/docs/sql-ref-syntax-ddl-create-function.md b/docs/sql-ref-syntax-ddl-create-function.md
index 5009069..e3f21f7 100644
--- a/docs/sql-ref-syntax-ddl-create-function.md
+++ b/docs/sql-ref-syntax-ddl-create-function.md
@@ -114,20 +114,20 @@ CREATE FUNCTION simple_udf AS 'SimpleUdf'
-- Verify that the function is in the registry.
SHOW USER FUNCTIONS;
- +------------------+
- | function|
- +------------------+
- |default.simple_udf|
- +------------------+
++------------------+
+| function|
++------------------+
+|default.simple_udf|
++------------------+
-- Invoke the function. Every selected value should be incremented by 10.
SELECT simple_udf(c1) AS function_return_value FROM t1;
- +---------------------+
- |function_return_value|
- +---------------------+
- | 11|
- | 12|
- +---------------------+
++---------------------+
+|function_return_value|
++---------------------+
+| 11|
+| 12|
++---------------------+
-- Created a temporary function.
CREATE TEMPORARY FUNCTION simple_temp_udf AS 'SimpleUdf'
@@ -137,12 +137,12 @@ CREATE TEMPORARY FUNCTION simple_temp_udf AS 'SimpleUdf'
-- Please note that the temporary function does not have a qualified
-- database associated with it.
SHOW USER FUNCTIONS;
- +------------------+
- | function|
- +------------------+
- |default.simple_udf|
- | simple_temp_udf|
- +------------------+
++------------------+
+| function|
++------------------+
+|default.simple_udf|
+| simple_temp_udf|
++------------------+
-- 1. Modify `SimpleUdf`'s implementation to add supplied integral value by 20.
-- import org.apache.hadoop.hive.ql.exec.UDF;
@@ -160,12 +160,12 @@ CREATE OR REPLACE FUNCTION simple_udf AS 'SimpleUdfR'
-- Invoke the function. Every selected value should be incremented by 20.
SELECT simple_udf(c1) AS function_return_value FROM t1;
- +---------------------+
- |function_return_value|
- +---------------------+
- | 21|
- | 22|
- +---------------------+
++---------------------+
+|function_return_value|
++---------------------+
+| 21|
+| 22|
++---------------------+
{% endhighlight %}
### Related Statements
diff --git a/docs/sql-ref-syntax-ddl-drop-function.md b/docs/sql-ref-syntax-ddl-drop-function.md
index b783b0e..66a405c 100644
--- a/docs/sql-ref-syntax-ddl-drop-function.md
+++ b/docs/sql-ref-syntax-ddl-drop-function.md
@@ -62,11 +62,11 @@ CREATE FUNCTION test_avg as 'org.apache.hadoop.hive.ql.udf.generic.GenericUDAFAv
-- List user functions
SHOW USER FUNCTIONS;
- +----------------+
- | function|
- +----------------+
- |default.test_avg|
- +----------------+
++----------------+
+| function|
++----------------+
+|default.test_avg|
++----------------+
-- Create Temporary function `test_avg`
CREATE TEMPORARY FUNCTION test_avg AS
@@ -74,12 +74,12 @@ CREATE TEMPORARY FUNCTION test_avg AS
-- List user functions
SHOW USER FUNCTIONS;
- +----------------+
- | function|
- +----------------+
- |default.test_avg|
- | test_avg|
- +----------------+
++----------------+
+| function|
++----------------+
+|default.test_avg|
+| test_avg|
++----------------+
-- Drop Permanent function
DROP FUNCTION test_avg;
@@ -92,11 +92,11 @@ DROP FUNCTION test_avg;
-- List the functions after dropping, it should list only temporary function
SHOW USER FUNCTIONS;
- +--------+
- |function|
- +--------+
- |test_avg|
- +--------+
++--------+
+|function|
++--------+
+|test_avg|
++--------+
-- Drop Temporary function
DROP TEMPORARY FUNCTION IF EXISTS test_avg;
diff --git a/docs/sql-ref-syntax-ddl-repair-table.md b/docs/sql-ref-syntax-ddl-repair-table.md
index 499b2bf..c48b731 100644
--- a/docs/sql-ref-syntax-ddl-repair-table.md
+++ b/docs/sql-ref-syntax-ddl-repair-table.md
@@ -57,15 +57,15 @@ MSCK REPAIR TABLE t1;
-- SELECT * FROM t1 returns results
SELECT * FROM t1;
- +-------+---+
- | name|age|
- +-------+---+
- |Michael| 20|
- +-------+---+
- | Justin| 19|
- +-------+---+
- | Andy| 30|
- +-------+---+
++-------+---+
+| name|age|
++-------+---+
+|Michael| 20|
++-------+---+
+| Justin| 19|
++-------+---+
+| Andy| 30|
++-------+---+
{% endhighlight %}
### Related Statements
diff --git a/docs/sql-ref-syntax-ddl-truncate-table.md b/docs/sql-ref-syntax-ddl-truncate-table.md
index e2d1834..820f439 100644
--- a/docs/sql-ref-syntax-ddl-truncate-table.md
+++ b/docs/sql-ref-syntax-ddl-truncate-table.md
@@ -62,33 +62,33 @@ TRUNCATE TABLE table_identifier [ partition_spec ]
CREATE TABLE Student (name STRING, rollno INT) PARTITIONED BY (age INT);
SELECT * FROM Student;
- +----+------+---+
- |name|rollno|age|
- +----+------+---+
- | ABC| 1| 10|
- | DEF| 2| 10|
- | XYZ| 3| 12|
- +----+------+---+
++----+------+---+
+|name|rollno|age|
++----+------+---+
+| ABC| 1| 10|
+| DEF| 2| 10|
+| XYZ| 3| 12|
++----+------+---+
-- Removes all rows from the table in the partition specified
TRUNCATE TABLE Student partition(age=10);
-- After truncate execution, records belonging to partition age=10 are removed
SELECT * FROM Student;
- +----+------+---+
- |name|rollno|age|
- +----+------+---+
- | XYZ| 3| 12|
- +----+------+---+
++----+------+---+
+|name|rollno|age|
++----+------+---+
+| XYZ| 3| 12|
++----+------+---+
-- Removes all rows from the table from all partitions
TRUNCATE TABLE Student;
SELECT * FROM Student;
- +----+------+---+
- |name|rollno|age|
- +----+------+---+
- +----+------+---+
++----+------+---+
+|name|rollno|age|
++----+------+---+
++----+------+---+
{% endhighlight %}
### Related Statements
diff --git a/docs/sql-ref-syntax-dml-insert-into.md b/docs/sql-ref-syntax-dml-insert-into.md
index 2498726..924831f 100644
--- a/docs/sql-ref-syntax-dml-insert-into.md
+++ b/docs/sql-ref-syntax-dml-insert-into.md
@@ -83,11 +83,11 @@ INSERT INTO students VALUES
('Amy Smith', '123 Park Ave, San Jose', 111111);
SELECT * FROM students;
- +---------+---------------------+----------+
- | name| address|student_id|
- +---------+---------------------+----------+
- |Amy Smith|123 Park Ave,San Jose| 111111|
- +---------+---------------------+----------+
++---------+---------------------+----------+
+| name| address|student_id|
++---------+---------------------+----------+
+|Amy Smith|123 Park Ave,San Jose| 111111|
++---------+---------------------+----------+
{% endhighlight %}
#### Multi-Row Insert Using a VALUES Clause
@@ -98,15 +98,15 @@ INSERT INTO students VALUES
('Cathy Johnson', '789 Race Ave, Palo Alto', 333333);
SELECT * FROM students;
- +-------------+------------------------+----------+
- | name| address|student_id|
- +-------------+------------------------+----------+
- | Amy Smith| 123 Park Ave, San Jose| 111111|
- +-------------+------------------------+----------+
- | Bob Brown|456 Taylor St, Cupertino| 222222|
- +-------------+------------------------+----------+
- |Cathy Johnson| 789 Race Ave, Palo Alto| 333333|
- +--------------+-----------------------+----------+
++-------------+------------------------+----------+
+| name| address|student_id|
++-------------+------------------------+----------+
+| Amy Smith| 123 Park Ave, San Jose| 111111|
++-------------+------------------------+----------+
+| Bob Brown|456 Taylor St, Cupertino| 222222|
++-------------+------------------------+----------+
+|Cathy Johnson| 789 Race Ave, Palo Alto| 333333|
++--------------+-----------------------+----------+
{% endhighlight %}
#### Insert Using a SELECT Statement
@@ -114,29 +114,29 @@ SELECT * FROM students;
{% highlight sql %}
-- Assuming the persons table has already been created and populated.
SELECT * FROM persons;
- +-------------+-------------------------+---------+
- | name| address| ssn|
- +-------------+-------------------------+---------+
- |Dora Williams|134 Forest Ave, Melo Park|123456789|
- +-------------+-------------------------+---------+
- | Eddie Davis| 245 Market St, Milpitas|345678901|
- +-------------+-------------------------+---------+
++-------------+-------------------------+---------+
+| name| address| ssn|
++-------------+-------------------------+---------+
+|Dora Williams|134 Forest Ave, Melo Park|123456789|
++-------------+-------------------------+---------+
+| Eddie Davis| 245 Market St, Milpitas|345678901|
++-------------+-------------------------+---------+
INSERT INTO students PARTITION (student_id = 444444)
SELECT name, address FROM persons WHERE name = "Dora Williams";
SELECT * FROM students;
- +-------------+-------------------------+----------+
- | name| address|student_id|
- +-------------+-------------------------+----------+
- | Amy Smith| 123 Park Ave, San Jose| 111111|
- +-------------+-------------------------+----------+
- | Bob Brown| 456 Taylor St, Cupertino| 222222|
- +-------------+-------------------------+----------+
- |Cathy Johnson| 789 Race Ave, Palo Alto| 333333|
- +-------------+-------------------------+----------+
- |Dora Williams|134 Forest Ave, Melo Park| 444444|
- +-------------+-------------------------+----------+
++-------------+-------------------------+----------+
+| name| address|student_id|
++-------------+-------------------------+----------+
+| Amy Smith| 123 Park Ave, San Jose| 111111|
++-------------+-------------------------+----------+
+| Bob Brown| 456 Taylor St, Cupertino| 222222|
++-------------+-------------------------+----------+
+|Cathy Johnson| 789 Race Ave, Palo Alto| 333333|
++-------------+-------------------------+----------+
+|Dora Williams|134 Forest Ave, Melo Park| 444444|
++-------------+-------------------------+----------+
{% endhighlight %}
#### Insert Using a TABLE Statement
@@ -144,32 +144,32 @@ SELECT * FROM students;
{% highlight sql %}
-- Assuming the visiting_students table has already been created and populated.
SELECT * FROM visiting_students;
- +-------------+---------------------+----------+
- | name| address|student_id|
- +-------------+---------------------+----------+
- |Fleur Laurent|345 Copper St, London| 777777|
- +-------------+---------------------+----------+
- |Gordon Martin| 779 Lake Ave, Oxford| 888888|
- +-------------+---------------------+----------+
++-------------+---------------------+----------+
+| name| address|student_id|
++-------------+---------------------+----------+
+|Fleur Laurent|345 Copper St, London| 777777|
++-------------+---------------------+----------+
+|Gordon Martin| 779 Lake Ave, Oxford| 888888|
++-------------+---------------------+----------+
INSERT INTO students TABLE visiting_students;
SELECT * FROM students;
- +-------------+-------------------------+----------+
- | name| address|student_id|
- +-------------+-------------------------+----------+
- | Amy Smith| 123 Park Ave,San Jose| 111111|
- +-------------+-------------------------+----------+
- | Bob Brown| 456 Taylor St, Cupertino| 222222|
- +-------------+-------------------------+----------+
- |Cathy Johnson| 789 Race Ave, Palo Alto| 333333|
- +-------------+-------------------------+----------+
- |Dora Williams|134 Forest Ave, Melo Park| 444444|
- +-------------+-------------------------+----------+
- |Fleur Laurent| 345 Copper St, London| 777777|
- +-------------+-------------------------+----------+
- |Gordon Martin| 779 Lake Ave, Oxford| 888888|
- +-------------+-------------------------+----------+
++-------------+-------------------------+----------+
+| name| address|student_id|
++-------------+-------------------------+----------+
+| Amy Smith| 123 Park Ave,San Jose| 111111|
++-------------+-------------------------+----------+
+| Bob Brown| 456 Taylor St, Cupertino| 222222|
++-------------+-------------------------+----------+
+|Cathy Johnson| 789 Race Ave, Palo Alto| 333333|
++-------------+-------------------------+----------+
+|Dora Williams|134 Forest Ave, Melo Park| 444444|
++-------------+-------------------------+----------+
+|Fleur Laurent| 345 Copper St, London| 777777|
++-------------+-------------------------+----------+
+|Gordon Martin| 779 Lake Ave, Oxford| 888888|
++-------------+-------------------------+----------+
{% endhighlight %}
#### Insert Using a FROM Statement
@@ -177,39 +177,39 @@ SELECT * FROM students;
{% highlight sql %}
-- Assuming the applicants table has already been created and populated.
SELECT * FROM applicants;
- +-----------+--------------------------+----------+---------+
- | name| address|student_id|qualified|
- +-----------+--------------------------+----------+---------+
- |Helen Davis| 469 Mission St, San Diego| 999999| true|
- +-----------+--------------------------+----------+---------+
- | Ivy King|367 Leigh Ave, Santa Clara| 101010| false|
- +-----------+--------------------------+----------+---------+
- | Jason Wang| 908 Bird St, Saratoga| 121212| true|
- +-----------+--------------------------+----------+---------+
++-----------+--------------------------+----------+---------+
+| name| address|student_id|qualified|
++-----------+--------------------------+----------+---------+
+|Helen Davis| 469 Mission St, San Diego| 999999| true|
++-----------+--------------------------+----------+---------+
+| Ivy King|367 Leigh Ave, Santa Clara| 101010| false|
++-----------+--------------------------+----------+---------+
+| Jason Wang| 908 Bird St, Saratoga| 121212| true|
++-----------+--------------------------+----------+---------+
INSERT INTO students
FROM applicants SELECT name, address, id applicants WHERE qualified = true;
SELECT * FROM students;
- +-------------+-------------------------+----------+
- | name| address|student_id|
- +-------------+-------------------------+----------+
- | Amy Smith| 123 Park Ave, San Jose| 111111|
- +-------------+-------------------------+----------+
- | Bob Brown| 456 Taylor St, Cupertino| 222222|
- +-------------+-------------------------+----------+
- |Cathy Johnson| 789 Race Ave, Palo Alto| 333333|
- +-------------+-------------------------+----------+
- |Dora Williams|134 Forest Ave, Melo Park| 444444|
- +-------------+-------------------------+----------+
- |Fleur Laurent| 345 Copper St, London| 777777|
- +-------------+-------------------------+----------+
- |Gordon Martin| 779 Lake Ave, Oxford| 888888|
- +-------------+-------------------------+----------+
- | Helen Davis|469 Mission St, San Diego| 999999|
- +-------------+-------------------------+----------+
- | Jason Wang| 908 Bird St, Saratoga| 121212|
- +-------------+-------------------------+----------+
++-------------+-------------------------+----------+
+| name| address|student_id|
++-------------+-------------------------+----------+
+| Amy Smith| 123 Park Ave, San Jose| 111111|
++-------------+-------------------------+----------+
+| Bob Brown| 456 Taylor St, Cupertino| 222222|
++-------------+-------------------------+----------+
+|Cathy Johnson| 789 Race Ave, Palo Alto| 333333|
++-------------+-------------------------+----------+
+|Dora Williams|134 Forest Ave, Melo Park| 444444|
++-------------+-------------------------+----------+
+|Fleur Laurent| 345 Copper St, London| 777777|
++-------------+-------------------------+----------+
+|Gordon Martin| 779 Lake Ave, Oxford| 888888|
++-------------+-------------------------+----------+
+| Helen Davis|469 Mission St, San Diego| 999999|
++-------------+-------------------------+----------+
+| Jason Wang| 908 Bird St, Saratoga| 121212|
++-------------+-------------------------+----------+
{% endhighlight %}
### Related Statements
diff --git a/docs/sql-ref-syntax-dml-insert-overwrite-table.md b/docs/sql-ref-syntax-dml-insert-overwrite-table.md
index f2874b5..5c760f0 100644
--- a/docs/sql-ref-syntax-dml-insert-overwrite-table.md
+++ b/docs/sql-ref-syntax-dml-insert-overwrite-table.md
@@ -78,30 +78,30 @@ INSERT OVERWRITE [ TABLE ] table_identifier [ partition_spec [ IF NOT EXISTS ] ]
{% highlight sql %}
-- Assuming the students table has already been created and populated.
SELECT * FROM students;
- +-------------+-------------------------+----------+
- | name| address|student_id|
- +-------------+-------------------------+----------+
- | Amy Smith| 123 Park Ave, San Jose| 111111|
- | Bob Brown| 456 Taylor St, Cupertino| 222222|
- |Cathy Johnson| 789 Race Ave, Palo Alto| 333333|
- |Dora Williams|134 Forest Ave, Melo Park| 444444|
- |Fleur Laurent| 345 Copper St, London| 777777|
- |Gordon Martin| 779 Lake Ave, Oxford| 888888|
- | Helen Davis|469 Mission St, San Diego| 999999|
- | Jason Wang| 908 Bird St, Saratoga| 121212|
- +-------------+-------------------------+----------+
++-------------+-------------------------+----------+
+| name| address|student_id|
++-------------+-------------------------+----------+
+| Amy Smith| 123 Park Ave, San Jose| 111111|
+| Bob Brown| 456 Taylor St, Cupertino| 222222|
+|Cathy Johnson| 789 Race Ave, Palo Alto| 333333|
+|Dora Williams|134 Forest Ave, Melo Park| 444444|
+|Fleur Laurent| 345 Copper St, London| 777777|
+|Gordon Martin| 779 Lake Ave, Oxford| 888888|
+| Helen Davis|469 Mission St, San Diego| 999999|
+| Jason Wang| 908 Bird St, Saratoga| 121212|
++-------------+-------------------------+----------+
INSERT OVERWRITE students VALUES
('Ashua Hill', '456 Erica Ct, Cupertino', 111111),
('Brian Reed', '723 Kern Ave, Palo Alto', 222222);
SELECT * FROM students;
- +----------+-----------------------+----------+
- | name| address|student_id|
- +----------+-----------------------+----------+
- |Ashua Hill|456 Erica Ct, Cupertino| 111111|
- |Brian Reed|723 Kern Ave, Palo Alto| 222222|
- +----------+-----------------------+----------+
++----------+-----------------------+----------+
+| name| address|student_id|
++----------+-----------------------+----------+
+|Ashua Hill|456 Erica Ct, Cupertino| 111111|
+|Brian Reed|723 Kern Ave, Palo Alto| 222222|
++----------+-----------------------+----------+
{% endhighlight %}
@@ -110,25 +110,25 @@ SELECT * FROM students;
{% highlight sql %}
-- Assuming the persons table has already been created and populated.
SELECT * FROM persons;
- +-------------+-------------------------+---------+
- | name| address| ssn|
- +-------------+-------------------------+---------+
- |Dora Williams|134 Forest Ave, Melo Park|123456789|
- +-------------+-------------------------+---------+
- | Eddie Davis| 245 Market St,Milpitas|345678901|
- +-------------+-------------------------+---------+
++-------------+-------------------------+---------+
+| name| address| ssn|
++-------------+-------------------------+---------+
+|Dora Williams|134 Forest Ave, Melo Park|123456789|
++-------------+-------------------------+---------+
+| Eddie Davis| 245 Market St,Milpitas|345678901|
++-------------+-------------------------+---------+
INSERT OVERWRITE students PARTITION (student_id = 222222)
SELECT name, address FROM persons WHERE name = "Dora Williams";
SELECT * FROM students;
- +-------------+-------------------------+----------+
- | name| address|student_id|
- +-------------+-------------------------+----------+
- | Ashua Hill| 456 Erica Ct, Cupertino| 111111|
- +-------------+-------------------------+----------+
- |Dora Williams|134 Forest Ave, Melo Park| 222222|
- +-------------+-------------------------+----------+
++-------------+-------------------------+----------+
+| name| address|student_id|
++-------------+-------------------------+----------+
+| Ashua Hill| 456 Erica Ct, Cupertino| 111111|
++-------------+-------------------------+----------+
+|Dora Williams|134 Forest Ave, Melo Park| 222222|
++-------------+-------------------------+----------+
{% endhighlight %}
#### Insert Using a TABLE Statement
@@ -136,24 +136,24 @@ SELECT * FROM students;
{% highlight sql %}
-- Assuming the visiting_students table has already been created and populated.
SELECT * FROM visiting_students;
- +-------------+---------------------+----------+
- | name| address|student_id|
- +-------------+---------------------+----------+
- |Fleur Laurent|345 Copper St, London| 777777|
- +-------------+---------------------+----------+
- |Gordon Martin| 779 Lake Ave, Oxford| 888888|
- +-------------+---------------------+----------+
++-------------+---------------------+----------+
+| name| address|student_id|
++-------------+---------------------+----------+
+|Fleur Laurent|345 Copper St, London| 777777|
++-------------+---------------------+----------+
+|Gordon Martin| 779 Lake Ave, Oxford| 888888|
++-------------+---------------------+----------+
INSERT OVERWRITE students TABLE visiting_students;
SELECT * FROM students;
- +-------------+---------------------+----------+
- | name| address|student_id|
- +-------------+---------------------+----------+
- |Fleur Laurent|345 Copper St, London| 777777|
- +-------------+---------------------+----------+
- |Gordon Martin| 779 Lake Ave, Oxford| 888888|
- +-------------+---------------------+----------+
++-------------+---------------------+----------+
+| name| address|student_id|
++-------------+---------------------+----------+
+|Fleur Laurent|345 Copper St, London| 777777|
++-------------+---------------------+----------+
+|Gordon Martin| 779 Lake Ave, Oxford| 888888|
++-------------+---------------------+----------+
{% endhighlight %}
#### Insert Using a FROM Statement
@@ -161,27 +161,27 @@ SELECT * FROM students;
{% highlight sql %}
-- Assuming the applicants table has already been created and populated.
SELECT * FROM applicants;
- +-----------+--------------------------+----------+---------+
- | name| address|student_id|qualified|
- +-----------+--------------------------+----------+---------+
- |Helen Davis| 469 Mission St, San Diego| 999999| true|
- +-----------+--------------------------+----------+---------+
- | Ivy King|367 Leigh Ave, Santa Clara| 101010| false|
- +-----------+--------------------------+----------+---------+
- | Jason Wang| 908 Bird St, Saratoga| 121212| true|
- +-----------+--------------------------+----------+---------+
++-----------+--------------------------+----------+---------+
+| name| address|student_id|qualified|
++-----------+--------------------------+----------+---------+
+|Helen Davis| 469 Mission St, San Diego| 999999| true|
++-----------+--------------------------+----------+---------+
+| Ivy King|367 Leigh Ave, Santa Clara| 101010| false|
++-----------+--------------------------+----------+---------+
+| Jason Wang| 908 Bird St, Saratoga| 121212| true|
++-----------+--------------------------+----------+---------+
INSERT OVERWRITE students
FROM applicants SELECT name, address, id applicants WHERE qualified = true;
SELECT * FROM students;
- +-----------+-------------------------+----------+
- | name| address|student_id|
- +-----------+-------------------------+----------+
- |Helen Davis|469 Mission St, San Diego| 999999|
- +-----------+-------------------------+----------+
- | Jason Wang| 908 Bird St, Saratoga| 121212|
- +-----------+-------------------------+----------+
++-----------+-------------------------+----------+
+| name| address|student_id|
++-----------+-------------------------+----------+
+|Helen Davis|469 Mission St, San Diego| 999999|
++-----------+-------------------------+----------+
+| Jason Wang| 908 Bird St, Saratoga| 121212|
++-----------+-------------------------+----------+
{% endhighlight %}
### Related Statements
diff --git a/docs/sql-ref-syntax-dml-load.md b/docs/sql-ref-syntax-dml-load.md
index 9a9bf23..01ece31 100644
--- a/docs/sql-ref-syntax-dml-load.md
+++ b/docs/sql-ref-syntax-dml-load.md
@@ -75,11 +75,11 @@ LOAD DATA [ LOCAL ] INPATH path [ OVERWRITE ] INTO TABLE table_identifier [ part
-- Example without partition specification.
-- Assuming the students table has already been created and populated.
SELECT * FROM students;
- +---------+----------------------+----------+
- | name| address|student_id|
- +---------+----------------------+----------+
- |Amy Smith|123 Park Ave, San Jose| 111111|
- +---------+----------------------+----------+
++---------+----------------------+----------+
+| name| address|student_id|
++---------+----------------------+----------+
+|Amy Smith|123 Park Ave, San Jose| 111111|
++---------+----------------------+----------+
CREATE TABLE test_load (name VARCHAR(64), address VARCHAR(64), student_id INT) USING HIVE;
@@ -87,11 +87,11 @@ CREATE TABLE test_load (name VARCHAR(64), address VARCHAR(64), student_id INT) U
LOAD DATA LOCAL INPATH '/user/hive/warehouse/students' OVERWRITE INTO TABLE test_load;
SELECT * FROM test_load;
- +---------+----------------------+----------+
- | name| address|student_id|
- +---------+----------------------+----------+
- |Amy Smith|123 Park Ave, San Jose| 111111|
- +---------+----------------------+----------+
++---------+----------------------+----------+
+| name| address|student_id|
++---------+----------------------+----------+
+|Amy Smith|123 Park Ave, San Jose| 111111|
++---------+----------------------+----------+
-- Example with partition specification.
CREATE TABLE test_partition (c1 INT, c2 INT, c3 INT) PARTITIONED BY (c2, c3);
@@ -103,13 +103,13 @@ INSERT INTO test_partition PARTITION (c2 = 5, c3 = 6) VALUES (4);
INSERT INTO test_partition PARTITION (c2 = 8, c3 = 9) VALUES (7);
SELECT * FROM test_partition;
- +---+---+---+
- | c1| c2| c3|
- +---+---+---+
- | 1| 2| 3|
- | 4| 5| 6|
- | 7| 8| 9|
- +---+---+---+
++---+---+---+
+| c1| c2| c3|
++---+---+---+
+| 1| 2| 3|
+| 4| 5| 6|
+| 7| 8| 9|
++---+---+---+
CREATE TABLE test_load_partition (c1 INT, c2 INT, c3 INT) USING HIVE PARTITIONED BY (c2, c3);
@@ -118,9 +118,9 @@ LOAD DATA LOCAL INPATH '/user/hive/warehouse/test_partition/c2=2/c3=3'
OVERWRITE INTO TABLE test_load_partition PARTITION (c2=2, c3=3);
SELECT * FROM test_load_partition;
- +---+---+---+
- | c1| c2| c3|
- +---+---+---+
- | 1| 2| 3|
- +---+---+---+
++---+---+---+
+| c1| c2| c3|
++---+---+---+
+| 1| 2| 3|
++---+---+---+
{% endhighlight %}
diff --git a/docs/sql-ref-syntax-qry-aggregation.md b/docs/sql-ref-syntax-qry-aggregation.md
deleted file mode 100644
index fbe6e61..0000000
--- a/docs/sql-ref-syntax-qry-aggregation.md
+++ /dev/null
@@ -1,22 +0,0 @@
----
-layout: global
-title: Aggregation (CUBE/ROLLUP/GROUPING)
-displayTitle: Aggregation (CUBE/ROLLUP/GROUPING)
-license: |
- 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.
----
-
-**This page is under construction**
diff --git a/docs/sql-ref-syntax-qry-explain.md b/docs/sql-ref-syntax-qry-explain.md
index 7ad297d..298a2ed 100644
--- a/docs/sql-ref-syntax-qry-explain.md
+++ b/docs/sql-ref-syntax-qry-explain.md
@@ -68,68 +68,68 @@ EXPLAIN [ EXTENDED | CODEGEN | COST | FORMATTED ] statement
{% highlight sql %}
-- Default Output
EXPLAIN select k, sum(v) from values (1, 2), (1, 3) t(k, v) group by k;
- +----------------------------------------------------+
- | plan|
- +----------------------------------------------------+
- | == Physical Plan ==
- *(2) HashAggregate(keys=[k#33], functions=[sum(cast(v#34 as bigint))])
- +- Exchange hashpartitioning(k#33, 200), true, [id=#59]
- +- *(1) HashAggregate(keys=[k#33], functions=[partial_sum(cast(v#34 as bigint))])
- +- *(1) LocalTableScan [k#33, v#34]
- |
- +----------------------------------------------------
++----------------------------------------------------+
+| plan|
++----------------------------------------------------+
+| == Physical Plan ==
+ *(2) HashAggregate(keys=[k#33], functions=[sum(cast(v#34 as bigint))])
+ +- Exchange hashpartitioning(k#33, 200), true, [id=#59]
+ +- *(1) HashAggregate(keys=[k#33], functions=[partial_sum(cast(v#34 as bigint))])
+ +- *(1) LocalTableScan [k#33, v#34]
+|
++----------------------------------------------------
-- Using Extended
EXPLAIN EXTENDED select k, sum(v) from values (1, 2), (1, 3) t(k, v) group by k;
- +----------------------------------------------------+
- | plan|
- +----------------------------------------------------+
- | == Parsed Logical Plan ==
- 'Aggregate ['k], ['k, unresolvedalias('sum('v), None)]
- +- 'SubqueryAlias `t`
- +- 'UnresolvedInlineTable [k, v], [List(1, 2), List(1, 3)]
++----------------------------------------------------+
+| plan|
++----------------------------------------------------+
+| == Parsed Logical Plan ==
+ 'Aggregate ['k], ['k, unresolvedalias('sum('v), None)]
+ +- 'SubqueryAlias `t`
+ +- 'UnresolvedInlineTable [k, v], [List(1, 2), List(1, 3)]
- == Analyzed Logical Plan ==
- k: int, sum(v): bigint
- Aggregate [k#47], [k#47, sum(cast(v#48 as bigint)) AS sum(v)#50L]
- +- SubqueryAlias `t`
- +- LocalRelation [k#47, v#48]
+ == Analyzed Logical Plan ==
+ k: int, sum(v): bigint
+ Aggregate [k#47], [k#47, sum(cast(v#48 as bigint)) AS sum(v)#50L]
+ +- SubqueryAlias `t`
+ +- LocalRelation [k#47, v#48]
- == Optimized Logical Plan ==
- Aggregate [k#47], [k#47, sum(cast(v#48 as bigint)) AS sum(v)#50L]
- +- LocalRelation [k#47, v#48]
+ == Optimized Logical Plan ==
+ Aggregate [k#47], [k#47, sum(cast(v#48 as bigint)) AS sum(v)#50L]
+ +- LocalRelation [k#47, v#48]
- == Physical Plan ==
- *(2) HashAggregate(keys=[k#47], functions=[sum(cast(v#48 as bigint))], output=[k#47, sum(v)#50L])
- +- Exchange hashpartitioning(k#47, 200), true, [id=#79]
- +- *(1) HashAggregate(keys=[k#47], functions=[partial_sum(cast(v#48 as bigint))], output=[k#47, sum#52L])
- +- *(1) LocalTableScan [k#47, v#48]
- |
- +----------------------------------------------------+
+ == Physical Plan ==
+ *(2) HashAggregate(keys=[k#47], functions=[sum(cast(v#48 as bigint))], output=[k#47, sum(v)#50L])
++- Exchange hashpartitioning(k#47, 200), true, [id=#79]
+ +- *(1) HashAggregate(keys=[k#47], functions=[partial_sum(cast(v#48 as bigint))], output=[k#47, sum#52L])
+ +- *(1) LocalTableScan [k#47, v#48]
+|
++----------------------------------------------------+
-- Using Formatted
EXPLAIN FORMATTED select k, sum(v) from values (1, 2), (1, 3) t(k, v) group by k;
- +----------------------------------------------------+
- | plan|
- +----------------------------------------------------+
- | == Physical Plan ==
- * HashAggregate (4)
- +- Exchange (3)
- +- * HashAggregate (2)
- +- * LocalTableScan (1)
++----------------------------------------------------+
+| plan|
++----------------------------------------------------+
+| == Physical Plan ==
+ * HashAggregate (4)
+ +- Exchange (3)
+ +- * HashAggregate (2)
+ +- * LocalTableScan (1)
- (1) LocalTableScan [codegen id : 1]
- Output: [k#19, v#20]
+ (1) LocalTableScan [codegen id : 1]
+ Output: [k#19, v#20]
- (2) HashAggregate [codegen id : 1]
- Input: [k#19, v#20]
+ (2) HashAggregate [codegen id : 1]
+ Input: [k#19, v#20]
- (3) Exchange
- Input: [k#19, sum#24L]
+ (3) Exchange
+ Input: [k#19, sum#24L]
- (4) HashAggregate [codegen id : 2]
- Input: [k#19, sum#24L]
- |
- +----------------------------------------------------+
+ (4) HashAggregate [codegen id : 2]
+ Input: [k#19, sum#24L]
+|
++----------------------------------------------------+
{% endhighlight %}
diff --git a/docs/sql-ref-syntax-qry-sampling.md b/docs/sql-ref-syntax-qry-sampling.md
index 3bc45cc4..82f6588 100644
--- a/docs/sql-ref-syntax-qry-sampling.md
+++ b/docs/sql-ref-syntax-qry-sampling.md
@@ -40,53 +40,53 @@ TABLESAMPLE ((integer_expression | decimal_expression) PERCENT)
{% highlight sql %}
SELECT * FROM test;
- +--+----+
- |id|name|
- +--+----+
- | 5|Alex|
- | 8|Lucy|
- | 2|Mary|
- | 4|Fred|
- | 1|Lisa|
- | 9|Eric|
- |10|Adam|
- | 6|Mark|
- | 7|Lily|
- | 3|Evan|
- +--+----+
++--+----+
+|id|name|
++--+----+
+| 5|Alex|
+| 8|Lucy|
+| 2|Mary|
+| 4|Fred|
+| 1|Lisa|
+| 9|Eric|
+|10|Adam|
+| 6|Mark|
+| 7|Lily|
+| 3|Evan|
++--+----+
SELECT * FROM test TABLESAMPLE (50 PERCENT);
- +--+----+
- |id|name|
- +--+----+
- | 5|Alex|
- | 2|Mary|
- | 4|Fred|
- | 9|Eric|
- |10|Adam|
- | 3|Evan|
- +--+----+
++--+----+
+|id|name|
++--+----+
+| 5|Alex|
+| 2|Mary|
+| 4|Fred|
+| 9|Eric|
+|10|Adam|
+| 3|Evan|
++--+----+
SELECT * FROM test TABLESAMPLE (5 ROWS);
- +--+----+
- |id|name|
- +--+----+
- | 5|Alex|
- | 8|Lucy|
- | 2|Mary|
- | 4|Fred|
- | 1|Lisa|
- +--+----+
++--+----+
+|id|name|
++--+----+
+| 5|Alex|
+| 8|Lucy|
+| 2|Mary|
+| 4|Fred|
+| 1|Lisa|
++--+----+
SELECT * FROM test TABLESAMPLE (BUCKET 4 OUT OF 10);
- +--+----+
- |id|name|
- +--+----+
- | 8|Lucy|
- | 2|Mary|
- | 9|Eric|
- | 6|Mark|
- +--+----+
++--+----+
+|id|name|
++--+----+
+| 8|Lucy|
+| 2|Mary|
+| 9|Eric|
+| 6|Mark|
++--+----+
{% endhighlight %}
### Related Statement
diff --git a/docs/sql-ref-syntax-qry-select-clusterby.md b/docs/sql-ref-syntax-qry-select-clusterby.md
index 687b2b5..ac1e1cc 100644
--- a/docs/sql-ref-syntax-qry-select-clusterby.md
+++ b/docs/sql-ref-syntax-qry-select-clusterby.md
@@ -64,32 +64,32 @@ SET spark.sql.shuffle.partitions = 2;
-- of a query when `CLUSTER BY` is not used vs when it's used. The query below produces rows
-- where age column is not sorted.
SELECT age, name FROM person;
- +---+-------+
- |age| name|
- +---+-------+
- | 16|Shone S|
- | 25|Zen Hui|
- | 16| Jack N|
- | 25| Mike A|
- | 18| John A|
- | 18| Anil B|
- +---+-------+
++---+-------+
+|age| name|
++---+-------+
+| 16|Shone S|
+| 25|Zen Hui|
+| 16| Jack N|
+| 25| Mike A|
+| 18| John A|
+| 18| Anil B|
++---+-------+
-- Produces rows clustered by age. Persons with same age are clustered together.
-- In the query below, persons with age 18 and 25 are in first partition and the
-- persons with age 16 are in the second partition. The rows are sorted based
-- on age within each partition.
SELECT age, name FROM person CLUSTER BY age;
- +---+-------+
- |age| name|
- +---+-------+
- | 18| John A|
- | 18| Anil B|
- | 25|Zen Hui|
- | 25| Mike A|
- | 16|Shone S|
- | 16| Jack N|
- +---+-------+
++---+-------+
+|age| name|
++---+-------+
+| 18| John A|
+| 18| Anil B|
+| 25|Zen Hui|
+| 25| Mike A|
+| 16|Shone S|
+| 16| Jack N|
++---+-------+
{% endhighlight %}
### Related Statements
diff --git a/docs/sql-ref-syntax-qry-select-cte.md b/docs/sql-ref-syntax-qry-select-cte.md
index 2146f8e..2408c88 100644
--- a/docs/sql-ref-syntax-qry-select-cte.md
+++ b/docs/sql-ref-syntax-qry-select-cte.md
@@ -55,11 +55,11 @@ expression_name [ ( column_name [ , ... ] ) ] [ AS ] ( [ common_table_expression
-- 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|
- +---+---+
++---+---+
+| x| y|
++---+---+
+| 1| 2|
++---+---+
-- CTE in CTE definition
WITH t as (
@@ -67,44 +67,44 @@ WITH t as (
SELECT * FROM t2
)
SELECT * FROM t;
- +---+
- | 1|
- +---+
- | 1|
- +---+
++---+
+| 1|
++---+
+| 1|
++---+
-- CTE in subquery
SELECT max(c) FROM (
WITH t(c) AS (SELECT 1)
SELECT * FROM t
);
- +------+
- |max(c)|
- +------+
- | 1|
- +------+
++------+
+|max(c)|
++------+
+| 1|
++------+
-- CTE in subquery expression
SELECT (
WITH t AS (SELECT 1)
SELECT * FROM t
);
- +----------------+
- |scalarsubquery()|
- +----------------+
- | 1|
- +----------------+
++----------------+
+|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|
- +---+---+---+---+
++---+---+---+---+
+| 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),
@@ -117,11 +117,11 @@ WITH
SELECT * FROM t
)
SELECT * FROM t2;
- +---+
- | 2|
- +---+
- | 2|
- +---+
++---+
+| 2|
++---+
+| 2|
++---+
{% endhighlight %}
### Related Statements
diff --git a/docs/sql-ref-syntax-qry-select-distinct.md b/docs/sql-ref-syntax-qry-select-distinct.md
deleted file mode 100644
index 2ed7931..0000000
--- a/docs/sql-ref-syntax-qry-select-distinct.md
+++ /dev/null
@@ -1,22 +0,0 @@
----
-layout: global
-title: Distinct operator
-displayTitle: Distinct operator
-license: |
- 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.
----
-
-**This page is under construction**
diff --git a/docs/sql-ref-syntax-qry-select-distribute-by.md b/docs/sql-ref-syntax-qry-select-distribute-by.md
index 18d73c7..9e2db27 100644
--- a/docs/sql-ref-syntax-qry-select-distribute-by.md
+++ b/docs/sql-ref-syntax-qry-select-distribute-by.md
@@ -61,30 +61,30 @@ SET spark.sql.shuffle.partitions = 2;
-- behavior of `DISTRIBUTE BY`. The query below produces rows where age columns are not
-- clustered together.
SELECT age, name FROM person;
- +---+-------+
- |age| name|
- +---+-------+
- | 16|Shone S|
- | 25|Zen Hui|
- | 16| Jack N|
- | 25| Mike A|
- | 18| John A|
- | 18| Anil B|
- +---+-------+
++---+-------+
+|age| name|
++---+-------+
+| 16|Shone S|
+| 25|Zen Hui|
+| 16| Jack N|
+| 25| Mike A|
+| 18| John A|
+| 18| Anil B|
++---+-------+
-- Produces rows clustered by age. Persons with same age are clustered together.
-- Unlike `CLUSTER BY` clause, the rows are not sorted within a partition.
SELECT age, name FROM person DISTRIBUTE BY age;
- +---+-------+
- |age| name|
- +---+-------+
- | 25|Zen Hui|
- | 25| Mike A|
- | 18| John A|
- | 18| Anil B|
- | 16|Shone S|
- | 16| Jack N|
- +---+-------+
++---+-------+
+|age| name|
++---+-------+
+| 25|Zen Hui|
+| 25| Mike A|
+| 18| John A|
+| 18| Anil B|
+| 16|Shone S|
+| 16| Jack N|
++---+-------+
{% endhighlight %}
### Related Statements
diff --git a/docs/sql-ref-syntax-qry-select-groupby.md b/docs/sql-ref-syntax-qry-select-groupby.md
index 1676ca9..22fe782 100644
--- a/docs/sql-ref-syntax-qry-select-groupby.md
+++ b/docs/sql-ref-syntax-qry-select-groupby.md
@@ -112,58 +112,58 @@ INSERT INTO dealer VALUES
-- Sum of quantity per dealership. Group by `id`.
SELECT id, sum(quantity) FROM dealer GROUP BY id ORDER BY id;
- +---+-------------+
- | id|sum(quantity)|
- +---+-------------+
- |100| 32|
- |200| 33|
- |300| 13|
- +---+-------------+
++---+-------------+
+| id|sum(quantity)|
++---+-------------+
+|100| 32|
+|200| 33|
+|300| 13|
++---+-------------+
-- Use column position in GROUP by clause.
SELECT id, sum(quantity) FROM dealer GROUP BY 1 ORDER BY 1;
- +---+-------------+
- | id|sum(quantity)|
- +---+-------------+
- |100| 32|
- |200| 33|
- |300| 13|
- +---+-------------+
++---+-------------+
+| id|sum(quantity)|
++---+-------------+
+|100| 32|
+|200| 33|
+|300| 13|
++---+-------------+
-- Multiple aggregations.
-- 1. Sum of quantity per dealership.
-- 2. Max quantity per dealership.
SELECT id, sum(quantity) AS sum, max(quantity) AS max FROM dealer GROUP BY id ORDER BY id;
- +---+---+---+
- | id|sum|max|
- +---+---+---+
- |100| 32| 15|
- |200| 33| 20|
- |300| 13| 8|
- +---+---+---+
++---+---+---+
+| id|sum|max|
++---+---+---+
+|100| 32| 15|
+|200| 33| 20|
+|300| 13| 8|
++---+---+---+
-- Count the number of distinct dealer cities per car_model.
SELECT car_model, count(DISTINCT city) AS count FROM dealer GROUP BY car_model;
- +------------+-----+
- | car_model|count|
- +------------+-----+
- | Honda Civic| 3|
- | Honda CRV| 2|
- |Honda Accord| 3|
- +------------+-----+
++------------+-----+
+| car_model|count|
++------------+-----+
+| Honda Civic| 3|
+| Honda CRV| 2|
+|Honda Accord| 3|
++------------+-----+
-- Sum of only 'Honda Civic' and 'Honda CRV' quantities per dealership.
SELECT id, sum(quantity) FILTER (
WHERE car_model IN ('Honda Civic', 'Honda CRV')
) AS `sum(quantity)` FROM dealer
GROUP BY id ORDER BY id;
- +---+-------------+
- | id|sum(quantity)|
- +---+-------------+
- |100| 17|
- |200| 23|
- |300| 5|
- +---+-------------+
++---+-------------+
+| id|sum(quantity)|
++---+-------------+
+|100| 17|
+|200| 23|
+|300| 5|
++---+-------------+
-- Aggregations using multiple sets of grouping columns in a single statement.
-- Following performs aggregations based on four sets of grouping columns.
@@ -174,97 +174,97 @@ SELECT id, sum(quantity) FILTER (
SELECT city, car_model, sum(quantity) AS sum FROM dealer
GROUP BY GROUPING SETS ((city, car_model), (city), (car_model), ())
ORDER BY city;
- +--------+------------+---+
- | city| car_model|sum|
- +--------+------------+---+
- | null| null| 78|
- | null| HondaAccord| 33|
- | null| HondaCRV| 10|
- | null| HondaCivic| 35|
- | Dublin| null| 33|
- | Dublin| HondaAccord| 10|
- | Dublin| HondaCRV| 3|
- | Dublin| HondaCivic| 20|
- | Fremont| null| 32|
- | Fremont| HondaAccord| 15|
- | Fremont| HondaCRV| 7|
- | Fremont| HondaCivic| 10|
- | SanJose| null| 13|
- | SanJose| HondaAccord| 8|
- | SanJose| HondaCivic| 5|
- +--------+------------+---+
++--------+------------+---+
+| city| car_model|sum|
++--------+------------+---+
+| null| null| 78|
+| null| HondaAccord| 33|
+| null| HondaCRV| 10|
+| null| HondaCivic| 35|
+| Dublin| null| 33|
+| Dublin| HondaAccord| 10|
+| Dublin| HondaCRV| 3|
+| Dublin| HondaCivic| 20|
+| Fremont| null| 32|
+| Fremont| HondaAccord| 15|
+| Fremont| HondaCRV| 7|
+| Fremont| HondaCivic| 10|
+| SanJose| null| 13|
+| SanJose| HondaAccord| 8|
+| SanJose| HondaCivic| 5|
++--------+------------+---+
-- Alternate syntax for `GROUPING SETS` in which both `GROUP BY` and `GROUPING SETS`
-- specifications are present.
SELECT city, car_model, sum(quantity) AS sum FROM dealer
GROUP BY city, car_model GROUPING SETS ((city, car_model), (city), (car_model), ())
ORDER BY city, car_model;
- +--------+------------+---+
- | city| car_model|sum|
- +--------+------------+---+
- | null| null| 78|
- | null| HondaAccord| 33|
- | null| HondaCRV| 10|
- | null| HondaCivic| 35|
- | Dublin| null| 33|
- | Dublin| HondaAccord| 10|
- | Dublin| HondaCRV| 3|
- | Dublin| HondaCivic| 20|
- | Fremont| null| 32|
- | Fremont| HondaAccord| 15|
- | Fremont| HondaCRV| 7|
- | Fremont| HondaCivic| 10|
- | SanJose| null| 13|
- | SanJose| HondaAccord| 8|
- | SanJose| HondaCivic| 5|
- +--------+------------+---+
++--------+------------+---+
+| city| car_model|sum|
++--------+------------+---+
+| null| null| 78|
+| null| HondaAccord| 33|
+| null| HondaCRV| 10|
+| null| HondaCivic| 35|
+| Dublin| null| 33|
+| Dublin| HondaAccord| 10|
+| Dublin| HondaCRV| 3|
+| Dublin| HondaCivic| 20|
+| Fremont| null| 32|
+| Fremont| HondaAccord| 15|
+| Fremont| HondaCRV| 7|
+| Fremont| HondaCivic| 10|
+| SanJose| null| 13|
+| SanJose| HondaAccord| 8|
+| SanJose| HondaCivic| 5|
++--------+------------+---+
-- Group by processing with `ROLLUP` clause.
-- Equivalent GROUP BY GROUPING SETS ((city, car_model), (city), ())
SELECT city, car_model, sum(quantity) AS sum FROM dealer
GROUP BY city, car_model WITH ROLLUP
ORDER BY city, car_model;
- +--------+------------+---+
- | city| car_model|sum|
- +--------+------------+---+
- | null| null| 78|
- | Dublin| null| 33|
- | Dublin| HondaAccord| 10|
- | Dublin| HondaCRV| 3|
- | Dublin| HondaCivic| 20|
- | Fremont| null| 32|
- | Fremont| HondaAccord| 15|
- | Fremont| HondaCRV| 7|
- | Fremont| HondaCivic| 10|
- | SanJose| null| 13|
- | SanJose| HondaAccord| 8|
- | SanJose| HondaCivic| 5|
- +--------+------------+---+
++--------+------------+---+
+| city| car_model|sum|
++--------+------------+---+
+| null| null| 78|
+| Dublin| null| 33|
+| Dublin| HondaAccord| 10|
+| Dublin| HondaCRV| 3|
+| Dublin| HondaCivic| 20|
+| Fremont| null| 32|
+| Fremont| HondaAccord| 15|
+| Fremont| HondaCRV| 7|
+| Fremont| HondaCivic| 10|
+| SanJose| null| 13|
+| SanJose| HondaAccord| 8|
+| SanJose| HondaCivic| 5|
++--------+------------+---+
-- Group by processing with `CUBE` clause.
-- Equivalent GROUP BY GROUPING SETS ((city, car_model), (city), (car_model), ())
SELECT city, car_model, sum(quantity) AS sum FROM dealer
GROUP BY city, car_model WITH CUBE
ORDER BY city, car_model;
- +--------+------------+---+
- | city| car_model|sum|
- +--------+------------+---+
- | null| null| 78|
- | null| HondaAccord| 33|
- | null| HondaCRV| 10|
- | null| HondaCivic| 35|
- | Dublin| null| 33|
- | Dublin| HondaAccord| 10|
- | Dublin| HondaCRV| 3|
- | Dublin| HondaCivic| 20|
- | Fremont| null| 32|
- | Fremont| HondaAccord| 15|
- | Fremont| HondaCRV| 7|
- | Fremont| HondaCivic| 10|
- | SanJose| null| 13|
- | SanJose| HondaAccord| 8|
- | SanJose| HondaCivic| 5|
- +--------+------------+---+
++--------+------------+---+
+| city| car_model|sum|
++--------+------------+---+
+| null| null| 78|
+| null| HondaAccord| 33|
+| null| HondaCRV| 10|
+| null| HondaCivic| 35|
+| Dublin| null| 33|
+| Dublin| HondaAccord| 10|
+| Dublin| HondaCRV| 3|
+| Dublin| HondaCivic| 20|
+| Fremont| null| 32|
+| Fremont| HondaAccord| 15|
+| Fremont| HondaCRV| 7|
+| Fremont| HondaCivic| 10|
+| SanJose| null| 13|
+| SanJose| HondaAccord| 8|
+| SanJose| HondaCivic| 5|
++--------+------------+---+
{% endhighlight %}
### Related Statements
diff --git a/docs/sql-ref-syntax-qry-select-having.md b/docs/sql-ref-syntax-qry-select-having.md
index b84ad179..c8c4f2c 100644
--- a/docs/sql-ref-syntax-qry-select-having.md
+++ b/docs/sql-ref-syntax-qry-select-having.md
@@ -67,56 +67,56 @@ INSERT INTO dealer VALUES
-- `HAVING` clause referring to column in `GROUP BY`.
SELECT city, sum(quantity) AS sum FROM dealer GROUP BY city HAVING city = 'Fremont';
- +-------+---+
- | city|sum|
- +-------+---+
- |Fremont| 32|
- +-------+---+
++-------+---+
+| city|sum|
++-------+---+
+|Fremont| 32|
++-------+---+
-- `HAVING` clause referring to aggregate function.
SELECT city, sum(quantity) AS sum FROM dealer GROUP BY city HAVING sum(quantity) > 15;
- +-------+---+
- | city|sum|
- +-------+---+
- | Dublin| 33|
- |Fremont| 32|
- +-------+---+
++-------+---+
+| city|sum|
++-------+---+
+| Dublin| 33|
+|Fremont| 32|
++-------+---+
-- `HAVING` clause referring to aggregate function by its alias.
SELECT city, sum(quantity) AS sum FROM dealer GROUP BY city HAVING sum > 15;
- +-------+---+
- | city|sum|
- +-------+---+
- | Dublin| 33|
- |Fremont| 32|
- +-------+---+
++-------+---+
+| city|sum|
++-------+---+
+| Dublin| 33|
+|Fremont| 32|
++-------+---+
-- `HAVING` clause referring to a different aggregate function than what is present in
-- `SELECT` list.
SELECT city, sum(quantity) AS sum FROM dealer GROUP BY city HAVING max(quantity) > 15;
- +------+---+
- | city|sum|
- +------+---+
- |Dublin| 33|
- +------+---+
++------+---+
+| city|sum|
++------+---+
+|Dublin| 33|
++------+---+
-- `HAVING` clause referring to constant expression.
SELECT city, sum(quantity) AS sum FROM dealer GROUP BY city HAVING 1 > 0 ORDER BY city;
- +--------+---+
- | city|sum|
- +--------+---+
- | Dublin| 33|
- | Fremont| 32|
- |San Jose| 13|
- +--------+---+
++--------+---+
+| city|sum|
++--------+---+
+| Dublin| 33|
+| Fremont| 32|
+|San Jose| 13|
++--------+---+
-- `HAVING` clause without a `GROUP BY` clause.
SELECT sum(quantity) AS sum FROM dealer HAVING sum(quantity) > 10;
- +---+
- |sum|
- +---+
- | 78|
- +---+
++---+
+|sum|
++---+
+| 78|
++---+
{% endhighlight %}
### Related Statements
diff --git a/docs/sql-ref-syntax-qry-select-inline-table.md b/docs/sql-ref-syntax-qry-select-inline-table.md
index 7a53285..9c33cbc 100644
--- a/docs/sql-ref-syntax-qry-select-inline-table.md
+++ b/docs/sql-ref-syntax-qry-select-inline-table.md
@@ -53,30 +53,30 @@ VALUES ( expression [ , ... ] ) [ table_alias ]
{% highlight sql %}
-- single row, without a table alias
SELECT * FROM VALUES ("one", 1);
- +----+----+
- |col1|col2|
- +----+----+
- | one| 1|
- +----+----+
++----+----+
+|col1|col2|
++----+----+
+| one| 1|
++----+----+
-- three rows with a table alias
SELECT * FROM VALUES ("one", 1), ("two", 2), ("three", null) AS data(a, b);
- +-----+----+
- | a| b|
- +-----+----+
- | one| 1|
- | two| 2|
- |three|null|
- +-----+----+
++-----+----+
+| a| b|
++-----+----+
+| one| 1|
+| two| 2|
+|three|null|
++-----+----+
-- complex types with a table alias
SELECT * FROM VALUES ("one", array(0, 1)), ("two", array(2, 3)) AS data(a, b);
- +---+------+
- | a| b|
- +---+------+
- |one|[0, 1]|
- |two|[2, 3]|
- +---+------+
++---+------+
+| a| b|
++---+------+
+|one|[0, 1]|
+|two|[2, 3]|
++---+------+
{% endhighlight %}
### Related Statement
diff --git a/docs/sql-ref-syntax-qry-select-join.md b/docs/sql-ref-syntax-qry-select-join.md
index 41b7603..0b1bb1e 100644
--- a/docs/sql-ref-syntax-qry-select-join.md
+++ b/docs/sql-ref-syntax-qry-select-join.md
@@ -139,121 +139,120 @@ An anti join returns values from the left relation that has no match with the ri
{% highlight sql %}
-- Use employee and department tables to demonstrate different type of joins.
SELECT * FROM employee;
-
- +---+-----+------+
- | id| name|deptno|
- +---+-----+------+
- |105|Chloe| 5|
- |103| Paul| 3|
- |101| John| 1|
- |102| Lisa| 2|
- |104| Evan| 4|
- |106| Amy| 6|
- +---+-----+------+
++---+-----+------+
+| id| name|deptno|
++---+-----+------+
+|105|Chloe| 5|
+|103| Paul| 3|
+|101| John| 1|
+|102| Lisa| 2|
+|104| Evan| 4|
+|106| Amy| 6|
++---+-----+------+
SELECT * FROM department;
- +------+-----------+
- |deptno| deptname|
- +------+-----------+
- | 3|Engineering|
- | 2| Sales|
- | 1| Marketing|
- +------+-----------+
++------+-----------+
+|deptno| deptname|
++------+-----------+
+| 3|Engineering|
+| 2| Sales|
+| 1| Marketing|
++------+-----------+
-- Use employee and department tables to demonstrate inner join.
SELECT id, name, employee.deptno, deptname
FROM employee INNER JOIN department ON employee.deptno = department.deptno;
- +---+-----+------+-----------|
- | id| name|deptno| deptname|
- +---+-----+------+-----------|
- |103| Paul| 3|Engineering|
- |101| John| 1| Marketing|
- |102| Lisa| 2| Sales|
- +---+-----+------+-----------|
++---+-----+------+-----------|
+| id| name|deptno| deptname|
++---+-----+------+-----------|
+|103| Paul| 3|Engineering|
+|101| John| 1| Marketing|
+|102| Lisa| 2| Sales|
++---+-----+------+-----------|
-- Use employee and department tables to demonstrate left join.
SELECT id, name, employee.deptno, deptname
FROM employee LEFT JOIN department ON employee.deptno = department.deptno;
- +---+-----+------+-----------|
- | id| name|deptno| deptname|
- +---+-----+------+-----------|
- |105|Chloe| 5| NULL|
- |103| Paul| 3|Engineering|
- |101| John| 1| Marketing|
- |102| Lisa| 2| Sales|
- |104| Evan| 4| NULL|
- |106| Amy| 6| NULL|
- +---+-----+------+-----------|
++---+-----+------+-----------|
+| id| name|deptno| deptname|
++---+-----+------+-----------|
+|105|Chloe| 5| NULL|
+|103| Paul| 3|Engineering|
+|101| John| 1| Marketing|
+|102| Lisa| 2| Sales|
+|104| Evan| 4| NULL|
+|106| Amy| 6| NULL|
++---+-----+------+-----------|
-- Use employee and department tables to demonstrate right join.
SELECT id, name, employee.deptno, deptname
FROM employee RIGHT JOIN department ON employee.deptno = department.deptno;
- +---+-----+------+-----------|
- | id| name|deptno| deptname|
- +---+-----+------+-----------|
- |103| Paul| 3|Engineering|
- |101| John| 1| Marketing|
- |102| Lisa| 2| Sales|
- +---+-----+------+-----------|
++---+-----+------+-----------|
+| id| name|deptno| deptname|
++---+-----+------+-----------|
+|103| Paul| 3|Engineering|
+|101| John| 1| Marketing|
+|102| Lisa| 2| Sales|
++---+-----+------+-----------|
-- Use employee and department tables to demonstrate full join.
SELECT id, name, employee.deptno, deptname
FROM employee FULL JOIN department ON employee.deptno = department.deptno;
- +---+-----+------+-----------|
- | id| name|deptno| deptname|
- +---+-----+------+-----------|
- |101| John| 1| Marketing|
- |106| Amy| 6| NULL|
- |103| Paul| 3|Engineering|
- |105|Chloe| 5| NULL|
- |104| Evan| 4| NULL|
- |102| Lisa| 2| Sales|
- +---+-----+------+-----------|
++---+-----+------+-----------|
+| id| name|deptno| deptname|
++---+-----+------+-----------|
+|101| John| 1| Marketing|
+|106| Amy| 6| NULL|
+|103| Paul| 3|Engineering|
+|105|Chloe| 5| NULL|
+|104| Evan| 4| NULL|
+|102| Lisa| 2| Sales|
++---+-----+------+-----------|
-- Use employee and department tables to demonstrate cross join.
SELECT id, name, employee.deptno, deptname FROM employee CROSS JOIN department;
- +---+-----+------+-----------|
- | id| name|deptno| deptname|
- +---+-----+------+-----------|
- |105|Chloe| 5|Engineering|
- |105|Chloe| 5| Marketing|
- |105|Chloe| 5| Sales|
- |103| Paul| 3|Engineering|
- |103| Paul| 3| Marketing|
- |103| Paul| 3| Sales|
- |101| John| 1|Engineering|
- |101| John| 1| Marketing|
- |101| John| 1| Sales|
- |102| Lisa| 2|Engineering|
- |102| Lisa| 2| Marketing|
- |102| Lisa| 2| Sales|
- |104| Evan| 4|Engineering|
- |104| Evan| 4| Marketing|
- |104| Evan| 4| Sales|
- |106| Amy| 4|Engineering|
- |106| Amy| 4| Marketing|
- |106| Amy| 4| Sales|
- +---+-----+------+-----------|
++---+-----+------+-----------|
+| id| name|deptno| deptname|
++---+-----+------+-----------|
+|105|Chloe| 5|Engineering|
+|105|Chloe| 5| Marketing|
+|105|Chloe| 5| Sales|
+|103| Paul| 3|Engineering|
+|103| Paul| 3| Marketing|
+|103| Paul| 3| Sales|
+|101| John| 1|Engineering|
+|101| John| 1| Marketing|
+|101| John| 1| Sales|
+|102| Lisa| 2|Engineering|
+|102| Lisa| 2| Marketing|
+|102| Lisa| 2| Sales|
+|104| Evan| 4|Engineering|
+|104| Evan| 4| Marketing|
+|104| Evan| 4| Sales|
+|106| Amy| 4|Engineering|
+|106| Amy| 4| Marketing|
+|106| Amy| 4| Sales|
++---+-----+------+-----------|
-- Use employee and department tables to demonstrate semi join.
SELECT * FROM employee SEMI JOIN department ON employee.deptno = department.deptno;
- +---+-----+------+
- | id| name|deptno|
- +---+-----+------+
- |103| Paul| 3|
- |101| John| 1|
- |102| Lisa| 2|
- +---+-----+------+
++---+-----+------+
+| id| name|deptno|
++---+-----+------+
+|103| Paul| 3|
+|101| John| 1|
+|102| Lisa| 2|
++---+-----+------+
-- Use employee and department tables to demonstrate anti join.
SELECT * FROM employee ANTI JOIN department ON employee.deptno = department.deptno;
- +---+-----+------+
- | id| name|deptno|
- +---+-----+------+
- |105|Chloe| 5|
- |104| Evan| 4|
- |106| Amy| 6|
- +---+-----+------+
++---+-----+------+
+| id| name|deptno|
++---+-----+------+
+|105|Chloe| 5|
+|104| Evan| 4|
+|106| Amy| 6|
++---+-----+------+
{% endhighlight %}
### Related Statements
diff --git a/docs/sql-ref-syntax-qry-select-limit.md b/docs/sql-ref-syntax-qry-select-limit.md
index 0ceb705..eaeaed0 100644
--- a/docs/sql-ref-syntax-qry-select-limit.md
+++ b/docs/sql-ref-syntax-qry-select-limit.md
@@ -60,37 +60,37 @@ INSERT INTO person VALUES
-- Select the first two rows.
SELECT name, age FROM person ORDER BY name LIMIT 2;
- +------+---+
- | name|age|
- +------+---+
- |Anil B| 18|
- |Jack N| 16|
- +------+---+
++------+---+
+| name|age|
++------+---+
+|Anil B| 18|
+|Jack N| 16|
++------+---+
-- Specifying ALL option on LIMIT returns all the rows.
SELECT name, age FROM person ORDER BY name LIMIT ALL;
- +-------+---+
- | name|age|
- +-------+---+
- | Anil B| 18|
- | Jack N| 16|
- | John A| 18|
- | Mike A| 25|
- |Shone S| 16|
- |Zen Hui| 25|
- +-------+---+
++-------+---+
+| name|age|
++-------+---+
+| Anil B| 18|
+| Jack N| 16|
+| John A| 18|
+| Mike A| 25|
+|Shone S| 16|
+|Zen Hui| 25|
++-------+---+
-- A function expression as an input to LIMIT.
SELECT name, age FROM person ORDER BY name LIMIT length('SPARK');
- +-------+---+
- | name|age|
- +-------+---+
- | Anil B| 18|
- | Jack N| 16|
- | John A| 18|
- | Mike A| 25|
- |Shone S| 16|
- +-------+---+
++-------+---+
+| name|age|
++-------+---+
+| Anil B| 18|
+| Jack N| 16|
+| John A| 18|
+| Mike A| 25|
+|Shone S| 16|
++-------+---+
-- A non-foldable expression as an input to LIMIT is not allowed.
SELECT name, age FROM person ORDER BY name LIMIT length(name);
diff --git a/docs/sql-ref-syntax-qry-select-orderby.md b/docs/sql-ref-syntax-qry-select-orderby.md
index cc75367..d927177 100644
--- a/docs/sql-ref-syntax-qry-select-orderby.md
+++ b/docs/sql-ref-syntax-qry-select-orderby.md
@@ -81,64 +81,64 @@ INSERT INTO person VALUES
-- Sort rows by age. By default rows are sorted in ascending manner with NULL FIRST.
SELECT name, age FROM person ORDER BY age;
- +-----+----+
- | name| age|
- +-----+----+
- |Jerry|null|
- | Mary|null|
- | John| 30|
- | Dan| 50|
- | Mike| 80|
- +-----+----+
++-----+----+
+| name| age|
++-----+----+
+|Jerry|null|
+| Mary|null|
+| John| 30|
+| Dan| 50|
+| Mike| 80|
++-----+----+
-- Sort rows in ascending manner keeping null values to be last.
SELECT name, age FROM person ORDER BY age NULLS LAST;
- +-----+----+
- | name| age|
- +-----+----+
- | John| 30|
- | Dan| 50|
- | Mike| 80|
- | Mary|null|
- |Jerry|null|
- +-----+----+
++-----+----+
+| name| age|
++-----+----+
+| John| 30|
+| Dan| 50|
+| Mike| 80|
+| Mary|null|
+|Jerry|null|
++-----+----+
-- Sort rows by age in descending manner, which defaults to NULL LAST.
SELECT name, age FROM person ORDER BY age DESC;
- +-----+----+
- | name| age|
- +-----+----+
- | Mike| 80|
- | Dan| 50|
- | John| 30|
- |Jerry|null|
- | Mary|null|
- +-----+----+
++-----+----+
+| name| age|
++-----+----+
+| Mike| 80|
+| Dan| 50|
+| John| 30|
+|Jerry|null|
+| Mary|null|
++-----+----+
-- Sort rows in ascending manner keeping null values to be first.
SELECT name, age FROM person ORDER BY age DESC NULLS FIRST;
- +-----+----+
- | name| age|
- +-----+----+
- |Jerry|null|
- | Mary|null|
- | Mike| 80|
- | Dan| 50|
- | John| 30|
- +-----+----+
++-----+----+
+| name| age|
++-----+----+
+|Jerry|null|
+| Mary|null|
+| Mike| 80|
+| Dan| 50|
+| John| 30|
++-----+----+
-- Sort rows based on more than one column with each column having different
-- sort direction.
SELECT * FROM person ORDER BY name ASC, age DESC;
- +---+-----+----+
- | id| name| age|
- +---+-----+----+
- |500| Dan| 50|
- |400|Jerry|null|
- |100| John| 30|
- |200| Mary|null|
- |300| Mike| 80|
- +---+-----+----+
++---+-----+----+
+| id| name| age|
++---+-----+----+
+|500| Dan| 50|
+|400|Jerry|null|
+|100| John| 30|
+|200| Mary|null|
+|300| Mike| 80|
++---+-----+----+
{% endhighlight %}
### Related Statements
diff --git a/docs/sql-ref-syntax-qry-select-setops.md b/docs/sql-ref-syntax-qry-select-setops.md
index 09a207a..98c2094 100644
--- a/docs/sql-ref-syntax-qry-select-setops.md
+++ b/docs/sql-ref-syntax-qry-select-setops.md
@@ -44,60 +44,60 @@ Note that input relations must have the same number of columns and compatible da
{% highlight sql %}
-- Use number1 and number2 tables to demonstrate set operators in this page.
SELECT * FROM number1;
- +---+
- | c|
- +---+
- | 3|
- | 1|
- | 2|
- | 2|
- | 3|
- | 4|
- +---+
++---+
+| c|
++---+
+| 3|
+| 1|
+| 2|
+| 2|
+| 3|
+| 4|
++---+
SELECT * FROM number2;
- +---+
- | c|
- +---+
- | 5|
- | 1|
- | 2|
- | 2|
- +---+
++---+
+| c|
++---+
+| 5|
+| 1|
+| 2|
+| 2|
++---+
SELECT c FROM number1 EXCEPT SELECT c FROM number2;
- +---+
- | c|
- +---+
- | 3|
- | 4|
- +---+
++---+
+| c|
++---+
+| 3|
+| 4|
++---+
SELECT c FROM number1 MINUS SELECT c FROM number2;
- +---+
- | c|
- +---+
- | 3|
- | 4|
- +---+
++---+
+| c|
++---+
+| 3|
+| 4|
++---+
SELECT c FROM number1 EXCEPT ALL (SELECT c FROM number2);
- +---+
- | c|
- +---+
- | 3|
- | 3|
- | 4|
- +---+
++---+
+| c|
++---+
+| 3|
+| 3|
+| 4|
++---+
SELECT c FROM number1 MINUS ALL (SELECT c FROM number2);
- +---+
- | c|
- +---+
- | 3|
- | 3|
- | 4|
- +---+
++---+
+| c|
++---+
+| 3|
+| 3|
+| 4|
++---+
{% endhighlight %}
### INTERSECT
@@ -114,29 +114,29 @@ SELECT c FROM number1 MINUS ALL (SELECT c FROM number2);
{% highlight sql %}
(SELECT c FROM number1) INTERSECT (SELECT c FROM number2);
- +---+
- | c|
- +---+
- | 1|
- | 2|
- +---+
++---+
+| c|
++---+
+| 1|
+| 2|
++---+
(SELECT c FROM number1) INTERSECT DISTINCT (SELECT c FROM number2);
- +---+
- | c|
- +---+
- | 1|
- | 2|
- +---+
++---+
+| c|
++---+
+| 1|
+| 2|
++---+
(SELECT c FROM number1) INTERSECT ALL (SELECT c FROM number2);
- +---+
- | c|
- +---+
- | 1|
- | 2|
- | 2|
- +---+
++---+
+| c|
++---+
+| 1|
+| 2|
+| 2|
++---+
{% endhighlight %}
### UNION
@@ -153,42 +153,42 @@ SELECT c FROM number1 MINUS ALL (SELECT c FROM number2);
{% highlight sql %}
(SELECT c FROM number1) UNION (SELECT c FROM number2);
- +---+
- | c|
- +---+
- | 1|
- | 3|
- | 5|
- | 4|
- | 2|
- +---+
++---+
+| c|
++---+
+| 1|
+| 3|
+| 5|
+| 4|
+| 2|
++---+
(SELECT c FROM number1) UNION DISTINCT (SELECT c FROM number2);
- +---+
- | c|
- +---+
- | 1|
- | 3|
- | 5|
- | 4|
- | 2|
- +---+
++---+
+| c|
++---+
+| 1|
+| 3|
+| 5|
+| 4|
+| 2|
++---+
SELECT c FROM number1 UNION ALL (SELECT c FROM number2);
- +---+
- | c|
- +---+
- | 3|
- | 1|
- | 2|
- | 2|
- | 3|
- | 4|
- | 5|
- | 1|
- | 2|
- | 2|
- +---+
++---+
+| c|
++---+
+| 3|
+| 1|
+| 2|
+| 2|
+| 3|
+| 4|
+| 5|
+| 1|
+| 2|
+| 2|
++---+
{% endhighlight %}
### Related Statements
diff --git a/docs/sql-ref-syntax-qry-select-sortby.md b/docs/sql-ref-syntax-qry-select-sortby.md
index 315faa5..1dfa104 100644
--- a/docs/sql-ref-syntax-qry-select-sortby.md
+++ b/docs/sql-ref-syntax-qry-select-sortby.md
@@ -89,89 +89,89 @@ INSERT INTO person VALUES
-- Sort rows by `name` within each partition in ascending manner
SELECT /*+ REPARTITION(zip_code) */ name, age, zip_code FROM person SORT BY name;
- +--------+----+--------+
- | name| age|zip_code|
- +--------+----+--------+
- | Anil K| 27| 94588|
- | Dan Li| 18| 94588|
- | John V|null| 94588|
- | Zen Hui| 50| 94588|
- |Aryan B.| 18| 94511|
- | David K| 42| 94511|
- |Lalit B.|null| 94511|
- +--------+----+--------+
++--------+----+--------+
+| name| age|zip_code|
++--------+----+--------+
+| Anil K| 27| 94588|
+| Dan Li| 18| 94588|
+| John V|null| 94588|
+| Zen Hui| 50| 94588|
+|Aryan B.| 18| 94511|
+| David K| 42| 94511|
+|Lalit B.|null| 94511|
++--------+----+--------+
-- Sort rows within each partition using column position.
SELECT /*+ REPARTITION(zip_code) */ name, age, zip_code FROM person SORT BY 1;
- +--------+----+--------+
- | name| age|zip_code|
- +--------+----+--------+
- | Anil K| 27| 94588|
- | Dan Li| 18| 94588|
- | John V|null| 94588|
- | Zen Hui| 50| 94588|
- |Aryan B.| 18| 94511|
- | David K| 42| 94511|
- |Lalit B.|null| 94511|
- +--------+----+--------+
++--------+----+--------+
+| name| age|zip_code|
++--------+----+--------+
+| Anil K| 27| 94588|
+| Dan Li| 18| 94588|
+| John V|null| 94588|
+| Zen Hui| 50| 94588|
+|Aryan B.| 18| 94511|
+| David K| 42| 94511|
+|Lalit B.|null| 94511|
++--------+----+--------+
-- Sort rows within partition in ascending manner keeping null values to be last.
SELECT /*+ REPARTITION(zip_code) */ age, name, zip_code FROM person SORT BY age NULLS LAST;
- +----+--------+--------+
- | age| name|zip_code|
- +----+--------+--------+
- | 18| Dan Li| 94588|
- | 27| Anil K| 94588|
- | 50| Zen Hui| 94588|
- |null| John V| 94588|
- | 18|Aryan B.| 94511|
- | 42| David K| 94511|
- |null|Lalit B.| 94511|
- +----+--------+--------+
++----+--------+--------+
+| age| name|zip_code|
++----+--------+--------+
+| 18| Dan Li| 94588|
+| 27| Anil K| 94588|
+| 50| Zen Hui| 94588|
+|null| John V| 94588|
+| 18|Aryan B.| 94511|
+| 42| David K| 94511|
+|null|Lalit B.| 94511|
++----+--------+--------+
-- Sort rows by age within each partition in descending manner, which defaults to NULL LAST.
SELECT /*+ REPARTITION(zip_code) */ age, name, zip_code FROM person SORT BY age DESC;
- +----+--------+--------+
- | age| name|zip_code|
- +----+--------+--------+
- | 50| Zen Hui| 94588|
- | 27| Anil K| 94588|
- | 18| Dan Li| 94588|
- |null| John V| 94588|
- | 42| David K| 94511|
- | 18|Aryan B.| 94511|
- |null|Lalit B.| 94511|
- +----+--------+--------+
++----+--------+--------+
+| age| name|zip_code|
++----+--------+--------+
+| 50| Zen Hui| 94588|
+| 27| Anil K| 94588|
+| 18| Dan Li| 94588|
+|null| John V| 94588|
+| 42| David K| 94511|
+| 18|Aryan B.| 94511|
+|null|Lalit B.| 94511|
++----+--------+--------+
-- Sort rows by age within each partition in descending manner keeping null values to be first.
SELECT /*+ REPARTITION(zip_code) */ age, name, zip_code FROM person SORT BY age DESC NULLS FIRST;
- +----+--------+--------+
- | age| name|zip_code|
- +----+--------+--------+
- |null| John V| 94588|
- | 50| Zen Hui| 94588|
- | 27| Anil K| 94588|
- | 18| Dan Li| 94588|
- |null|Lalit B.| 94511|
- | 42| David K| 94511|
- | 18|Aryan B.| 94511|
- +----+--------+--------+
++----+--------+--------+
+| age| name|zip_code|
++----+--------+--------+
+|null| John V| 94588|
+| 50| Zen Hui| 94588|
+| 27| Anil K| 94588|
+| 18| Dan Li| 94588|
+|null|Lalit B.| 94511|
+| 42| David K| 94511|
+| 18|Aryan B.| 94511|
++----+--------+--------+
-- Sort rows within each partition based on more than one column with each column having
-- different sort direction.
SELECT /*+ REPARTITION(zip_code) */ name, age, zip_code FROM person
SORT BY name ASC, age DESC;
- +--------+----+--------+
- | name| age|zip_code|
- +--------+----+--------+
- | Anil K| 27| 94588|
- | Dan Li| 18| 94588|
- | John V|null| 94588|
- | Zen Hui| 50| 94588|
- |Aryan B.| 18| 94511|
- | David K| 42| 94511|
- |Lalit B.|null| 94511|
- +--------+----+--------+
++--------+----+--------+
+| name| age|zip_code|
++--------+----+--------+
+| Anil K| 27| 94588|
+| Dan Li| 18| 94588|
+| John V|null| 94588|
+| Zen Hui| 50| 94588|
+|Aryan B.| 18| 94511|
+| David K| 42| 94511|
+|Lalit B.|null| 94511|
++--------+----+--------+
{% endhighlight %}
### Related Statements
diff --git a/docs/sql-ref-syntax-qry-select-tvf.md b/docs/sql-ref-syntax-qry-select-tvf.md
index bbfd870..ac8feca 100644
--- a/docs/sql-ref-syntax-qry-select-tvf.md
+++ b/docs/sql-ref-syntax-qry-select-tvf.md
@@ -81,49 +81,49 @@ function_name ( expression [ , ... ] ) [ table_alias ]
{% highlight sql %}
-- range call with end
SELECT * FROM range(6 + cos(3));
- +---+
- | id|
- +---+
- | 0|
- | 1|
- | 2|
- | 3|
- | 4|
- +---+
++---+
+| id|
++---+
+| 0|
+| 1|
+| 2|
+| 3|
+| 4|
++---+
-- range call with start and end
SELECT * FROM range(5, 10);
- +---+
- | id|
- +---+
- | 5|
- | 6|
- | 7|
- | 8|
- | 9|
- +---+
++---+
+| id|
++---+
+| 5|
+| 6|
+| 7|
+| 8|
+| 9|
++---+
-- range call with numPartitions
SELECT * FROM range(0, 10, 2, 200);
- +---+
- | id|
- +---+
- | 0|
- | 2|
- | 4|
- | 6|
- | 8|
- +---+
++---+
+| id|
++---+
+| 0|
+| 2|
+| 4|
+| 6|
+| 8|
++---+
-- range call with a table alias
SELECT * FROM range(5, 8) AS test;
- +---+
- | id|
- +---+
- | 5|
- | 6|
- | 7|
- +---+
++---+
+| id|
++---+
+| 5|
+| 6|
+| 7|
++---+
{% endhighlight %}
### Related Statement
diff --git a/docs/sql-ref-syntax-qry-select-where.md b/docs/sql-ref-syntax-qry-select-where.md
index 1960367..360313f 100644
--- a/docs/sql-ref-syntax-qry-select-where.md
+++ b/docs/sql-ref-syntax-qry-select-where.md
@@ -53,57 +53,57 @@ INSERT INTO person VALUES
-- Comparison operator in `WHERE` clause.
SELECT * FROM person WHERE id > 200 ORDER BY id;
- +---+----+---+
- | id|name|age|
- +---+----+---+
- |300|Mike| 80|
- |400| Dan| 50|
- +---+----+---+
++---+----+---+
+| id|name|age|
++---+----+---+
+|300|Mike| 80|
+|400| Dan| 50|
++---+----+---+
-- Comparison and logical operators in `WHERE` clause.
SELECT * FROM person WHERE id = 200 OR id = 300 ORDER BY id;
- +---+----+----+
- | id|name| age|
- +---+----+----+
- |200|Mary|null|
- |300|Mike| 80|
- +---+----+----+
++---+----+----+
+| id|name| age|
++---+----+----+
+|200|Mary|null|
+|300|Mike| 80|
++---+----+----+
-- IS NULL expression in `WHERE` clause.
SELECT * FROM person WHERE id > 300 OR age IS NULL ORDER BY id;
- +---+----+----+
- | id|name| age|
- +---+----+----+
- |200|Mary|null|
- |400| Dan| 50|
- +---+----+----+
++---+----+----+
+| id|name| age|
++---+----+----+
+|200|Mary|null|
+|400| Dan| 50|
++---+----+----+
-- Function expression in `WHERE` clause.
SELECT * FROM person WHERE length(name) > 3 ORDER BY id;
- +---+----+----+
- | id|name| age|
- +---+----+----+
- |100|John| 30|
- |200|Mary|null|
- |300|Mike| 80|
- +---+----+----+
++---+----+----+
+| id|name| age|
++---+----+----+
+|100|John| 30|
+|200|Mary|null|
+|300|Mike| 80|
++---+----+----+
-- `BETWEEN` expression in `WHERE` clause.
SELECT * FROM person WHERE id BETWEEN 200 AND 300 ORDER BY id;
- +---+----+----+
- | id|name| age|
- +---+----+----+
- |200|Mary|null|
- |300|Mike| 80|
- +---+----+----+
++---+----+----+
+| id|name| age|
++---+----+----+
+|200|Mary|null|
+|300|Mike| 80|
++---+----+----+
-- Scalar Subquery in `WHERE` clause.
SELECT * FROM person WHERE age > (SELECT avg(age) FROM person);
- +---+----+---+
- | id|name|age|
- +---+----+---+
- |300|Mike| 80|
- +---+----+---+
++---+----+---+
+| id|name|age|
++---+----+---+
+|300|Mike| 80|
++---+----+---+
-- Correlated Subquery in `WHERE` clause.
SELECT * FROM person AS parent
@@ -111,11 +111,11 @@ SELECT * FROM person AS parent
SELECT 1 FROM person AS child
WHERE parent.id = child.id AND child.age IS NULL
);
- +---+----+----+
- |id |name|age |
- +---+----+----+
- |200|Mary|null|
- +---+----+----+
++---+----+----+
+|id |name|age |
++---+----+----+
+|200|Mary|null|
++---+----+----+
{% endhighlight %}
### Related Statements
diff --git a/docs/sql-ref-syntax-qry-window.md b/docs/sql-ref-syntax-qry-window.md
index 4ec1af7..e376292 100644
--- a/docs/sql-ref-syntax-qry-window.md
+++ b/docs/sql-ref-syntax-qry-window.md
@@ -99,106 +99,106 @@ INSERT INTO employees VALUES ("Paul", "Engineering", 29000, 23);
INSERT INTO employees VALUES ("Chloe", "Engineering", 23000, 25);
SELECT * FROM employees;
- +-----+-----------+------+-----+
- | name| dept|salary| age|
- +-----+-----------+------+-----+
- |Chloe|Engineering| 23000| 25|
- | Fred|Engineering| 21000| 28|
- | Paul|Engineering| 29000| 23|
- |Helen| Marketing| 29000| 40|
- | Tom|Engineering| 23000| 33|
- | Jane| Marketing| 29000| 28|
- | Jeff| Marketing| 35000| 38|
- | Evan| Sales| 32000| 38|
- | Lisa| Sales| 10000| 35|
- | Alex| Sales| 30000| 33|
- +-----+-----------+------+-----+
++-----+-----------+------+-----+
+| name| dept|salary| age|
++-----+-----------+------+-----+
+|Chloe|Engineering| 23000| 25|
+| Fred|Engineering| 21000| 28|
+| Paul|Engineering| 29000| 23|
+|Helen| Marketing| 29000| 40|
+| Tom|Engineering| 23000| 33|
+| Jane| Marketing| 29000| 28|
+| Jeff| Marketing| 35000| 38|
+| Evan| Sales| 32000| 38|
+| Lisa| Sales| 10000| 35|
+| Alex| Sales| 30000| 33|
++-----+-----------+------+-----+
SELECT name, dept, RANK() OVER (PARTITION BY dept ORDER BY salary) AS rank FROM employees;
- +-----+-----------+------+----+
- | name| dept|salary|rank|
- +-----+-----------+------+----+
- | Lisa| Sales| 10000| 1|
- | Alex| Sales| 30000| 2|
- | Evan| Sales| 32000| 3|
- | Fred|Engineering| 21000| 1|
- | Tom|Engineering| 23000| 2|
- |Chloe|Engineering| 23000| 2|
- | Paul|Engineering| 29000| 4|
- |Helen| Marketing| 29000| 1|
- | Jane| Marketing| 29000| 1|
- | Jeff| Marketing| 35000| 3|
- +-----+-----------+------+----+
++-----+-----------+------+----+
+| name| dept|salary|rank|
++-----+-----------+------+----+
+| Lisa| Sales| 10000| 1|
+| Alex| Sales| 30000| 2|
+| Evan| Sales| 32000| 3|
+| Fred|Engineering| 21000| 1|
+| Tom|Engineering| 23000| 2|
+|Chloe|Engineering| 23000| 2|
+| Paul|Engineering| 29000| 4|
+|Helen| Marketing| 29000| 1|
+| Jane| Marketing| 29000| 1|
+| Jeff| Marketing| 35000| 3|
++-----+-----------+------+----+
SELECT name, dept, DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary ROWS BETWEEN
UNBOUNDED PRECEDING AND CURRENT ROW) AS dense_rank FROM employees;
- +-----+-----------+------+----------+
- | name| dept|salary|dense_rank|
- +-----+-----------+------+----------+
- | Lisa| Sales| 10000| 1|
- | Alex| Sales| 30000| 2|
- | Evan| Sales| 32000| 3|
- | Fred|Engineering| 21000| 1|
- | Tom|Engineering| 23000| 2|
- |Chloe|Engineering| 23000| 2|
- | Paul|Engineering| 29000| 3|
- |Helen| Marketing| 29000| 1|
- | Jane| Marketing| 29000| 1|
- | Jeff| Marketing| 35000| 2|
- +-----+-----------+------+----------+
++-----+-----------+------+----------+
+| name| dept|salary|dense_rank|
++-----+-----------+------+----------+
+| Lisa| Sales| 10000| 1|
+| Alex| Sales| 30000| 2|
+| Evan| Sales| 32000| 3|
+| Fred|Engineering| 21000| 1|
+| Tom|Engineering| 23000| 2|
+|Chloe|Engineering| 23000| 2|
+| Paul|Engineering| 29000| 3|
+|Helen| Marketing| 29000| 1|
+| Jane| Marketing| 29000| 1|
+| Jeff| Marketing| 35000| 2|
++-----+-----------+------+----------+
SELECT name, dept, age, CUME_DIST() OVER (PARTITION BY dept ORDER BY age
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cume_dist FROM employees;
- +-----+-----------+------+------------------+
- | name| dept|age | cume_dist|
- +-----+-----------+------+------------------+
- | Alex| Sales| 33|0.3333333333333333|
- | Lisa| Sales| 35|0.6666666666666666|
- | Evan| Sales| 38| 1.0|
- | Paul|Engineering| 23| 0.25|
- |Chloe|Engineering| 25| 0.75|
- | Fred|Engineering| 28| 0.25|
- | Tom|Engineering| 33| 1.0|
- | Jane| Marketing| 28|0.3333333333333333|
- | Jeff| Marketing| 38|0.6666666666666666|
- |Helen| Marketing| 40| 1.0|
- +-----+-----------+------+------------------+
++-----+-----------+------+------------------+
+| name| dept|age | cume_dist|
++-----+-----------+------+------------------+
+| Alex| Sales| 33|0.3333333333333333|
+| Lisa| Sales| 35|0.6666666666666666|
+| Evan| Sales| 38| 1.0|
+| Paul|Engineering| 23| 0.25|
+|Chloe|Engineering| 25| 0.75|
+| Fred|Engineering| 28| 0.25|
+| Tom|Engineering| 33| 1.0|
+| Jane| Marketing| 28|0.3333333333333333|
+| Jeff| Marketing| 38|0.6666666666666666|
+|Helen| Marketing| 40| 1.0|
++-----+-----------+------+------------------+
SELECT name, dept, salary, MIN(salary) OVER (PARTITION BY dept ORDER BY salary) AS min
FROM employees;
- +-----+-----------+------+-----+
- | name| dept|salary| min|
- +-----+-----------+------+-----+
- | Lisa| Sales| 10000|10000|
- | Alex| Sales| 30000|10000|
- | Evan| Sales| 32000|10000|
- |Helen| Marketing| 29000|29000|
- | Jane| Marketing| 29000|29000|
- | Jeff| Marketing| 35000|29000|
- | Fred|Engineering| 21000|21000|
- | Tom|Engineering| 23000|21000|
- |Chloe|Engineering| 23000|21000|
- | Paul|Engineering| 29000|21000|
- +-----+-----------+------+-----+
++-----+-----------+------+-----+
+| name| dept|salary| min|
++-----+-----------+------+-----+
+| Lisa| Sales| 10000|10000|
+| Alex| Sales| 30000|10000|
+| Evan| Sales| 32000|10000|
+|Helen| Marketing| 29000|29000|
+| Jane| Marketing| 29000|29000|
+| Jeff| Marketing| 35000|29000|
+| Fred|Engineering| 21000|21000|
+| Tom|Engineering| 23000|21000|
+|Chloe|Engineering| 23000|21000|
+| Paul|Engineering| 29000|21000|
++-----+-----------+------+-----+
SELECT name, salary,
LAG(salary) OVER (PARTITION BY dept ORDER BY salary) AS lag,
LEAD(salary, 1, 0) OVER (PARTITION BY dept ORDER BY salary) AS lead
FROM employees;
- +-----+-----------+------+-----+-----+
- | name| dept|salary| lag| lead|
- +-----+-----------+------+-----+-----+
- | Lisa| Sales| 10000|NULL |30000|
- | Alex| Sales| 30000|10000|32000|
- | Evan| Sales| 32000|30000| 0|
- | Fred|Engineering| 21000| NULL|23000|
- |Chloe|Engineering| 23000|21000|23000|
- | Tom|Engineering| 23000|23000|29000|
- | Paul|Engineering| 29000|23000| 0|
- |Helen| Marketing| 29000| NULL|29000|
- | Jane| Marketing| 29000|29000|35000|
- | Jeff| Marketing| 35000|29000| 0|
- +-----+-----------+------+-----+-----+
++-----+-----------+------+-----+-----+
+| name| dept|salary| lag| lead|
++-----+-----------+------+-----+-----+
+| Lisa| Sales| 10000|NULL |30000|
+| Alex| Sales| 30000|10000|32000|
+| Evan| Sales| 32000|30000| 0|
+| Fred|Engineering| 21000| NULL|23000|
+|Chloe|Engineering| 23000|21000|23000|
+| Tom|Engineering| 23000|23000|29000|
+| Paul|Engineering| 29000|23000| 0|
+|Helen| Marketing| 29000| NULL|29000|
+| Jane| Marketing| 29000|29000|35000|
+| Jeff| Marketing| 35000|29000| 0|
++-----+-----------+------+-----+-----+
{% endhighlight %}
### Related Statements
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org