This is an automated email from the ASF dual-hosted git repository. yamamuro pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/master by this push: new 03fe9ee [SPARK-31465][SQL][DOCS] Document Literal in SQL Reference 03fe9ee is described below commit 03fe9ee428ebb4544f0f47e861bccea43e0cb325 Author: Huaxin Gao <huax...@us.ibm.com> AuthorDate: Thu Apr 23 14:12:10 2020 +0900 [SPARK-31465][SQL][DOCS] Document Literal in SQL Reference ### What changes were proposed in this pull request? Document Literal in SQL Reference ### Why are the changes needed? Make SQL Reference complete ### Does this PR introduce any user-facing change? Yes <img width="1049" alt="Screen Shot 2020-04-22 at 8 50 04 PM" src="https://user-images.githubusercontent.com/13592258/80057912-9ecb0c00-84dc-11ea-881e-1415108d674f.png"> <img width="1050" alt="Screen Shot 2020-04-22 at 8 50 29 PM" src="https://user-images.githubusercontent.com/13592258/80057917-a12d6600-84dc-11ea-8884-81f2a94644d5.png"> <img width="1050" alt="Screen Shot 2020-04-22 at 8 50 54 PM" src="https://user-images.githubusercontent.com/13592258/80057922-a4c0ed00-84dc-11ea-9857-75db50f7b054.png"> <img width="1050" alt="Screen Shot 2020-04-22 at 8 51 15 PM" src="https://user-images.githubusercontent.com/13592258/80057927-a7234700-84dc-11ea-9124-45ae1f6143fd.png"> <img width="1050" alt="Screen Shot 2020-04-22 at 8 51 44 PM" src="https://user-images.githubusercontent.com/13592258/80057932-ab4f6480-84dc-11ea-8393-cf005af13ce9.png"> <img width="1050" alt="Screen Shot 2020-04-22 at 8 52 03 PM" src="https://user-images.githubusercontent.com/13592258/80057936-ad192800-84dc-11ea-8d78-9f071a82f1df.png"> <img width="1050" alt="Screen Shot 2020-04-22 at 8 52 28 PM" src="https://user-images.githubusercontent.com/13592258/80057940-b0141880-84dc-11ea-97a7-f787cad0ee03.png"> <img width="1050" alt="Screen Shot 2020-04-22 at 8 53 14 PM" src="https://user-images.githubusercontent.com/13592258/80057945-b30f0900-84dc-11ea-985f-c070609e2329.png"> <img width="1050" alt="Screen Shot 2020-04-22 at 8 53 34 PM" src="https://user-images.githubusercontent.com/13592258/80057949-b5716300-84dc-11ea-9452-3f51137fe03d.png"> <img width="1050" alt="Screen Shot 2020-04-22 at 8 53 56 PM" src="https://user-images.githubusercontent.com/13592258/80057957-b904ea00-84dc-11ea-8b12-a6f00362aa55.png"> <img width="1049" alt="Screen Shot 2020-04-22 at 8 54 12 PM" src="https://user-images.githubusercontent.com/13592258/80057962-bacead80-84dc-11ea-94da-916b1d1c1756.png"> ### How was this patch tested? Manually build and check Closes #28237 from huaxingao/literal. Authored-by: Huaxin Gao <huax...@us.ibm.com> Signed-off-by: Takeshi Yamamuro <yamam...@apache.org> --- docs/_data/menu-sql.yaml | 2 + docs/sql-ref-literals.md | 532 +++++++++++++++++++++++++++++++++++++++++++++++ 2 files changed, 534 insertions(+) diff --git a/docs/_data/menu-sql.yaml b/docs/_data/menu-sql.yaml index a16e114..5d8c265 100644 --- a/docs/_data/menu-sql.yaml +++ b/docs/_data/menu-sql.yaml @@ -78,6 +78,8 @@ subitems: - text: Data Types url: sql-ref-datatypes.html + - text: Literals + url: sql-ref-literals.html - text: Null Semantics url: sql-ref-null-semantics.html - text: NaN Semantics diff --git a/docs/sql-ref-literals.md b/docs/sql-ref-literals.md new file mode 100644 index 0000000..7cf078c --- /dev/null +++ b/docs/sql-ref-literals.md @@ -0,0 +1,532 @@ +--- +layout: global +title: Literals +displayTitle: Literals +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. +--- + +A literal (also known as a constant) represents a fixed data value. Spark SQL supports the following literals: + + * [String Literal](#string-literal) + * [Binary Literal](#binary-literal) + * [Null Literal](#null-literal) + * [Boolean Literal](#boolean-literal) + * [Numeric Literal](#numeric-literal) + * [Datetime Literal](#datetime-literal) + * [Interval Literal](#interval-literal) + +### String Literal + +A string literal is used to specify a character string value. + +#### Syntax + +{% highlight sql %} +'c [ ... ]' | "c [ ... ]" +{% endhighlight %} + +#### <em>Parameters</em> + +<dl> + <dt><code><em>c</em></code></dt> + <dd> + One character from the character set. Use <code>\</code> to escape special characters (e.g., <code>'</code> or <code>\</code>). + </dd> +</dl> + +#### <em>Examples</em> + +{% highlight sql %} +SELECT 'Hello, World!' AS col; ++-------------+ +| col| ++-------------+ +|Hello, World!| ++-------------+ + +SELECT "SPARK SQL" AS col; ++---------+ +| col| ++---------+ +|Spark SQL| ++---------+ + +SELECT 'it\'s $10.' AS col; ++---------+ +| col| ++---------+ +|It's $10.| ++---------+ +{% endhighlight %} + +### Binary Literal + +A binary literal is used to specify a byte sequence value. + +#### Syntax + +{% highlight sql %} +X { 'c [ ... ]' | "c [ ... ]" } +{% endhighlight %} + +#### <em>Parameters</em> + +<dl> + <dt><code><em>c</em></code></dt> + <dd> + One character from the character set. + </dd> +</dl> + +#### <em>Examples</em> + +{% highlight sql %} +SELECT X'123456' AS col; ++----------+ +| col| ++----------+ +|[12 34 56]| ++----------+ +{% endhighlight %} + +### Null Literal + +A null literal is used to specify a null value. + +#### Syntax + +{% highlight sql %} +NULL +{% endhighlight %} + +#### Examples + +{% highlight sql %} +SELECT NULL AS col; ++----+ +| col| ++----+ +|NULL| ++----+ +{% endhighlight %} + +### Boolean Literal + +A boolean literal is used to specify a boolean value. + +#### Syntax + +{% highlight sql %} +TRUE | FALSE +{% endhighlight %} + +#### Examples + +{% highlight sql %} +SELECT TRUE AS col; ++----+ +| col| ++----+ +|true| ++----+ +{% endhighlight %} + +### Numeric Literal + +A numeric literal is used to specify a fixed or floating-point number. + +#### Integral Literal + +#### Syntax + +{% highlight sql %} +[ + | - ] digit [ ... ] [ L | S | Y ] +{% endhighlight %} + +#### Parameters + +<dl> + <dt><code><em>digit</em></code></dt> + <dd> + Any numeral from 0 to 9. + </dd> +</dl> +<dl> + <dt><code><em>L</em></code></dt> + <dd> + Case insensitive, indicates <code>BIGINT</code>, which is a 8-byte signed integer number. + </dd> +</dl> +<dl> + <dt><code><em>S</em></code></dt> + <dd> + Case insensitive, indicates <code>SMALLINT</code>, which is a 2-byte signed integer number. + </dd> +</dl> +<dl> + <dt><code><em>Y</em></code></dt> + <dd> + Case insensitive, indicates <code>TINYINT</code>, which is a 1-byte signed integer number. + </dd> +</dl> +<dl> + <dt><code><em>default (no postfix)</em></code></dt> + <dd> + Indicates a 4-byte signed integer number. + </dd> +</dl> + +#### Examples + +{% highlight sql %} +SELECT -2147483648 AS col; ++-----------+ +| col| ++-----------+ +|-2147483648| ++-----------+ + +SELECT 9223372036854775807l AS col; ++-------------------+ +| col| ++-------------------+ +|9223372036854775807| ++-------------------+ + +SELECT -32Y AS col; ++---+ +|col| ++---+ +|-32| ++---+ + +SELECT 482S AS col; ++---+ +|col| ++---+ +|482| ++---+ +{% endhighlight %} + +#### Fractional Literals + +#### Syntax + +decimal literals: +{% highlight sql %} +decimal_digits { [ BD ] | [ exponent BD ] } | digit [ ... ] [ exponent ] BD +{% endhighlight %} + +double literals: +{% highlight sql %} +decimal_digits { D | exponent [ D ] } | digit [ ... ] { exponent [ D ] | [ exponent ] D } +{% endhighlight %} + +While decimal_digits is defined as +{% highlight sql %} +[ + | - ] { digit [ ... ] . [ digit [ ... ] ] | . digit [ ... ] } +{% endhighlight %} + +and exponent is defined as +{% highlight sql %} +E [ + | - ] digit [ ... ] +{% endhighlight %} + +#### Parameters + +<dl> + <dt><code><em>digit</em></code></dt> + <dd> + Any numeral from 0 to 9. + </dd> +</dl> +<dl> + <dt><code><em>D</em></code></dt> + <dd> + Case insensitive, indicates <code>DOUBLE</code>, which is a 8-byte double-precision floating point number. + </dd> +</dl> +<dl> + <dt><code><em>BD</em></code></dt> + <dd> + Case insensitive, indicates <code>DECIMAL</code>, with the total number of digits as precision and the number of digits to right of decimal point as scale. + </dd> +</dl> + +#### Examples + +{% highlight sql %} +SELECT 12.578 AS col; ++------+ +| col| ++------+ +|12.578| ++------+ + +SELECT -0.1234567 AS col; ++----------+ +| col| ++----------+ +|-0.1234567| ++----------+ + +SELECT -.1234567 AS col; ++----------+ +| col| ++----------+ +|-0.1234567| ++----------+ + +SELECT 123. AS col; ++---+ +|col| ++---+ +|123| ++---+ + +SELECT 123.BD AS col; ++---+ +|col| ++---+ +|123| ++---+ + +SELECT 5E2 AS col; ++-----+ +| col| ++-----+ +|500.0| ++-----+ + +SELECT 5D AS col; ++---+ +|col| ++---+ +|5.0| ++---+ + +SELECT -5BD AS col; ++---+ +|col| ++---+ +| -5| ++---+ + +SELECT 12.578e-2d AS col; ++-------+ +| col| ++-------+ +|0.12578| ++-------+ + +SELECT -.1234567E+2BD AS col; ++---------+ +| col| ++---------+ +|-12.34567| ++---------+ + +SELECT +3.e+3 AS col; ++------+ +| col| ++------+ +|3000.0| ++------+ + +SELECT -3.E-3D AS col; ++------+ +| col| ++------+ +|-0.003| ++------+ +{% endhighlight %} + +### Datetime Literal + +A Datetime literal is used to specify a datetime value. + +#### Date Literal + +#### Syntax + +{% highlight sql %} +DATE { 'yyyy' | + 'yyyy-[m]m' | + 'yyyy-[m]m-[d]d' | + 'yyyy-[m]m-[d]d[T]' } +{% endhighlight %} +Note: defaults to <code>01</code> if month or day is not specified. + +#### Examples + +{% highlight sql %} +SELECT DATE '1997' AS col; ++----------+ +| col| ++----------+ +|1997-01-01| ++----------+ + +SELECT DATE '1997-01' AS col; ++----------+ +| col| ++----------+ +|1997-01-01| ++----------+ + +SELECT DATE '2011-11-11' AS col; ++----------+ +| col| ++----------+ +|2011-11-11| ++----------+ +{% endhighlight %} + +#### Timestamp Literal + +#### Syntax + +{% highlight sql %} +TIMESTAMP { 'yyyy' | + 'yyyy-[m]m' | + 'yyyy-[m]m-[d]d' | + 'yyyy-[m]m-[d]d ' | + 'yyyy-[m]m-[d]d[T][h]h[:]' | + 'yyyy-[m]m-[d]d[T][h]h:[m]m[:]' | + 'yyyy-[m]m-[d]d[T][h]h:[m]m:[s]s[.]' | + 'yyyy-[m]m-[d]d[T][h]h:[m]m:[s]s.[ms][ms][ms][us][us][us][zone_id]'} +{% endhighlight %} +Note: defaults to <code>00</code> if hour, minute or second is not specified. <br><br> +`zone_id` should have one of the forms: +<ul> + <li>Z - Zulu time zone UTC+0</li> + <li>+|-[h]h:[m]m</li> + <li>An id with one of the prefixes UTC+, UTC-, GMT+, GMT-, UT+ or UT-, and a suffix in the formats: + <ul> + <li>+|-h[h]</li> + <li>+|-hh[:]mm</li> + <li>+|-hh:mm:ss</li> + <li>+|-hhmmss</li> + </ul> + </li> + <li>Region-based zone IDs in the form <code>area/city</code>, such as <code>Europe/Paris</code></li> +</ul> +Note: defaults to the session local timezone (set via <code>spark.sql.session.timeZone</code>) if <code>zone_id</code> is not specified. + +#### Examples + +{% highlight sql %} +SELECT TIMESTAMP '1997-01-31 09:26:56.123' AS col; ++-----------------------+ +| col| ++-----------------------+ +|1997-01-31 09:26:56.123| ++-----------------------+ + +SELECT TIMESTAMP '1997-01-31 09:26:56.66666666CST' AS col; ++--------------------------+ +| col | ++--------------------------+ +|1997-01-31 07:26:56.666666| ++--------------------------+ + +SELECT TIMESTAMP '1997-01' AS col; ++-------------------+ +| col| ++-------------------+ +|1997-01-01 00:00:00| ++-------------------+ +{% endhighlight %} + +### Interval Literal + +An interval literal is used to specify a fixed period of time. + +#### Syntax +{% highlight sql %} +{ INTERVAL interval_value interval_unit [ interval_value interval_unit ... ] | + INTERVAL 'interval_value interval_unit [ interval_value interval_unit ... ]' | + INTERVAL interval_string_value interval_unit TO interval_unit } +{% endhighlight %} + +#### Parameters + +<dl> + <dt><code><em>interval_value</em></code></dt> + <dd> + <b>Syntax:</b> + <code> + [ + | - ] number_value | '[ + | - ] number_value' + </code><br> + </dd> +</dl> +<dl> + <dt><code><em>interval_string_value</em></code></dt> + <dd> + year-month/day-time interval string. + </dd> +</dl> +<dl> + <dt><code><em>interval_unit</em></code></dt> + <dd> + <b>Syntax:</b><br> + <code> + YEAR[S] | MONTH[S] | WEEK[S] | DAY[S] | HOUR[S] | MINUTE[S] | SECOND[S] | <br> + MILLISECOND[S] | MICROSECOND[S] + </code> + </dd> +</dl> + +#### Examples + +{% highlight sql %} +SELECT INTERVAL 3 YEAR AS col; ++-------+ +| col| ++-------+ +|3 years| ++-------+ + +SELECT INTERVAL -2 HOUR '3' MINUTE AS col; ++--------------------+ +| col| ++--------------------+ +|-1 hours -57 minutes| ++--------------------+ + +SELECT INTERVAL 'INTERVAL 1 YEAR 2 DAYS 3 HOURS'; ++----------------------+ +| col| ++----------------------+ +|1 years 2 days 3 hours| ++----------------------+ + +SELECT INTERVAL 1 YEARS 2 MONTH 3 WEEK 4 DAYS 5 HOUR 6 MINUTES 7 SECOND 8 + MILLISECOND 9 MICROSECONDS AS col; ++-----------------------------------------------------------+ +| col| ++-----------------------------------------------------------+ +|1 years 2 months 25 days 5 hours 6 minutes 7.008009 seconds| ++-----------------------------------------------------------+ + +SELECT INTERVAL '20 15:40:32.99899999' DAY TO SECOND AS col; ++---------------------------------------------+ +| col| ++---------------------------------------------+ +|20 days 15 hours 40 minutes 32.998999 seconds| ++---------------------------------------------+ +{% endhighlight %} --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@spark.apache.org For additional commands, e-mail: commits-h...@spark.apache.org