You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@impala.apache.org by mi...@apache.org on 2018/10/11 19:54:12 UTC
[07/10] impala git commit: [DOCS] Built-in Functions doc format
Changes
[DOCS] Built-in Functions doc format Changes
- The function titles were changed to upper case.
- The function titles no longer use <codeph>. <codeph> font appears
smaller than the <p> font.
- Return type were changed to upper case data types.
- Minor typos were fixed, such as extra commas and periods in titles.
- The indexterm dita elememts were removed. Indexterm was incomplete
and WIP. No plan to go ahead and implement it, so removed.
Change-Id: I797532463da8d29fe5bc7543cfdfb5b2b82db197
Reviewed-on: http://gerrit.cloudera.org:8080/11619
Tested-by: Impala Public Jenkins <im...@cloudera.com>
Reviewed-by: Michael Brown <mi...@cloudera.com>
Project: http://git-wip-us.apache.org/repos/asf/impala/repo
Commit: http://git-wip-us.apache.org/repos/asf/impala/commit/e8ee827a
Tree: http://git-wip-us.apache.org/repos/asf/impala/tree/e8ee827a
Diff: http://git-wip-us.apache.org/repos/asf/impala/diff/e8ee827a
Branch: refs/heads/master
Commit: e8ee827a6d39cf470f33a07e0f760ffc36775e1d
Parents: b0d0d73
Author: Alex Rodoni <ar...@cloudera.com>
Authored: Mon Oct 8 15:26:32 2018 -0700
Committer: Alex Rodoni <ar...@cloudera.com>
Committed: Wed Oct 10 18:18:09 2018 +0000
----------------------------------------------------------------------
docs/shared/impala_common.xml | 65 +-
docs/topics/impala_aggregate_functions.xml | 4 -
docs/topics/impala_analytic_functions.xml | 17 +-
docs/topics/impala_bit_functions.xml | 432 +++--
docs/topics/impala_conditional_functions.xml | 487 ++++--
docs/topics/impala_conversion_functions.xml | 872 ++--------
docs/topics/impala_datetime_functions.xml | 1825 +++++++++++++--------
docs/topics/impala_functions.xml | 2 +-
docs/topics/impala_math_functions.xml | 1301 +++++++++------
docs/topics/impala_misc_functions.xml | 184 ++-
docs/topics/impala_string_functions.xml | 919 +++++++----
11 files changed, 3383 insertions(+), 2725 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/impala/blob/e8ee827a/docs/shared/impala_common.xml
----------------------------------------------------------------------
diff --git a/docs/shared/impala_common.xml b/docs/shared/impala_common.xml
index ff0f449..45b7d87 100644
--- a/docs/shared/impala_common.xml
+++ b/docs/shared/impala_common.xml
@@ -1335,34 +1335,34 @@ drop database temp;
href="../topics/impala_s3.xml#s3"/> for details about working with S3
tables. </p>
- <p id="y2k38" rev="2.2.0">
- In Impala 2.2.0 and higher, built-in functions that accept or return integers representing <codeph>TIMESTAMP</codeph> values
- use the <codeph>BIGINT</codeph> type for parameters and return values, rather than <codeph>INT</codeph>.
- This change lets the date and time functions avoid an overflow error that would otherwise occur
- on January 19th, 2038 (known as the
- <xref href="http://en.wikipedia.org/wiki/Year_2038_problem" scope="external" format="html"><q>Year 2038 problem</q> or <q>Y2K38 problem</q></xref>).
- This change affects the <codeph>from_unixtime()</codeph> and <codeph>unix_timestamp()</codeph> functions.
- You might need to change application code that interacts with these functions, change the types of
- columns that store the return values, or add <codeph>CAST()</codeph> calls to SQL statements that
- call these functions.
- </p>
-
- <p id="timestamp_conversions">
- Impala automatically converts <codeph>STRING</codeph> literals of the
- correct format into <codeph>TIMESTAMP</codeph> values. Timestamp values
- are accepted in the format <codeph>"yyyy-MM-dd HH:mm:ss.SSSSSS"</codeph>,
- and can consist of just the date, or just the time, with or without the
- fractional second portion. For example, you can specify <codeph>TIMESTAMP</codeph>
- values such as <codeph>'1966-07-30'</codeph>, <codeph>'08:30:00'</codeph>,
- or <codeph>'1985-09-25 17:45:30.005'</codeph>.
- </p>
- <p>
- Leading zeroes are not required in the numbers representing the date
- component, such as month and date, or the time component, such as
- hour, minute, and second. For example, Impala accepts both
- <codeph>"2018-1-1 01:02:03"</codeph> and
- <codeph>"2018-01-01 1:2:3"</codeph> as valid.
- </p>
+ <p id="y2k38" rev="2.2.0"> In Impala 2.2.0 and higher, built-in functions
+ that accept or return integers representing <codeph>TIMESTAMP</codeph>
+ values use the <codeph>BIGINT</codeph> type for parameters and return
+ values, rather than <codeph>INT</codeph>. This change lets the date and
+ time functions avoid an overflow error that would otherwise occur on
+ January 19th, 2038 (known as the <xref
+ href="http://en.wikipedia.org/wiki/Year_2038_problem" scope="external"
+ format="html"><q>Year 2038 problem</q> or <q>Y2K38
+ problem</q></xref>). This change affects the
+ <codeph>FROM_UNIXTIME()</codeph> and <codeph>UNIX_TIMESTAMP()</codeph>
+ functions. You might need to change application code that interacts with
+ these functions, change the types of columns that store the return
+ values, or add <codeph>CAST()</codeph> calls to SQL statements that call
+ these functions. </p>
+
+ <p id="timestamp_conversions"> Impala automatically converts
+ <codeph>STRING</codeph> literals of the correct format into
+ <codeph>TIMESTAMP</codeph> values. Timestamp values are accepted in
+ the format <codeph>'yyyy-MM-dd HH:mm:ss.SSSSSS'</codeph>, and can
+ consist of just the date, or just the time, with or without the
+ fractional second portion. For example, you can specify
+ <codeph>TIMESTAMP</codeph> values such as
+ <codeph>'1966-07-30'</codeph>, <codeph>'08:30:00'</codeph>, or
+ <codeph>'1985-09-25 17:45:30.005'</codeph>. </p>
+ <p> Leading zeroes are not required in the numbers representing the date
+ component, such as month and date, or the time component, such as hour,
+ minute, and second. For example, Impala accepts both <codeph>'2018-1-1
+ 01:02:03'</codeph> and <codeph>'2018-01-01 1:2:3'</codeph> as valid. </p>
<p>
In <codeph>STRING</codeph> to <codeph>TIMESTAMP</codeph> conversions,
leading and trailing white spaces, such as a space, a tab, a newline, or
@@ -2056,10 +2056,11 @@ show functions in _impala_builtins like '*<varname>substring</varname>*';
</p>
<p id="datetime_function_chaining">
- <codeph>unix_timestamp()</codeph> and <codeph>from_unixtime()</codeph> are often used in combination to
- convert a <codeph>TIMESTAMP</codeph> value into a particular string format. For example:
-<codeblock xml:space="preserve">select from_unixtime(unix_timestamp(now() + interval 3 days),
- 'yyyy/MM/dd HH:mm') as yyyy_mm_dd_hh_mm;
+ <codeph>UNIX_TIMESTAMP()</codeph> and <codeph>FROM_UNIXTIME()</codeph>
+ are often used in combination to convert a <codeph>TIMESTAMP</codeph>
+ value into a particular string format. For example:
+ <codeblock xml:space="preserve">SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(NOW() + interval 3 days),
+ 'yyyy/MM/dd HH:mm') AS yyyy_mm_dd_hh_mm;
+------------------+
| yyyy_mm_dd_hh_mm |
+------------------+
http://git-wip-us.apache.org/repos/asf/impala/blob/e8ee827a/docs/topics/impala_aggregate_functions.xml
----------------------------------------------------------------------
diff --git a/docs/topics/impala_aggregate_functions.xml b/docs/topics/impala_aggregate_functions.xml
index 1e01ad7..1d9118f 100644
--- a/docs/topics/impala_aggregate_functions.xml
+++ b/docs/topics/impala_aggregate_functions.xml
@@ -42,10 +42,6 @@ under the License.
<p conref="../shared/impala_common.xml#common/aggr3"/>
- <p>
- <indexterm audience="hidden">aggregate functions</indexterm>
- </p>
-
<p outputclass="toc"/>
</conbody>
</concept>
http://git-wip-us.apache.org/repos/asf/impala/blob/e8ee827a/docs/topics/impala_analytic_functions.xml
----------------------------------------------------------------------
diff --git a/docs/topics/impala_analytic_functions.xml b/docs/topics/impala_analytic_functions.xml
index 39d4fc5..f139927 100644
--- a/docs/topics/impala_analytic_functions.xml
+++ b/docs/topics/impala_analytic_functions.xml
@@ -43,16 +43,13 @@ under the License.
<conbody>
- <p rev="2.0.0">
- <indexterm audience="hidden">analytic functions</indexterm>
-
- <indexterm audience="hidden">window functions</indexterm>
- Analytic functions (also known as window functions) are a special category of built-in functions. Like
- aggregate functions, they examine the contents of multiple input rows to compute each output value. However,
- rather than being limited to one result value per <codeph>GROUP BY</codeph> group, they operate on
- <term>windows</term> where the input rows are ordered and grouped using flexible conditions expressed through
- an <codeph>OVER()</codeph> clause.
- </p>
+ <p rev="2.0.0"> Analytic functions (also known as window functions) are a
+ special category of built-in functions. Like aggregate functions, they
+ examine the contents of multiple input rows to compute each output value.
+ However, rather than being limited to one result value per <codeph>GROUP
+ BY</codeph> group, they operate on <term>windows</term> where the input
+ rows are ordered and grouped using flexible conditions expressed through
+ an <codeph>OVER()</codeph> clause. </p>
<p conref="../shared/impala_common.xml#common/added_in_20"/>
http://git-wip-us.apache.org/repos/asf/impala/blob/e8ee827a/docs/topics/impala_bit_functions.xml
----------------------------------------------------------------------
diff --git a/docs/topics/impala_bit_functions.xml b/docs/topics/impala_bit_functions.xml
index c42f834..35f584a 100644
--- a/docs/topics/impala_bit_functions.xml
+++ b/docs/topics/impala_bit_functions.xml
@@ -1,4 +1,5 @@
-<?xml version="1.0" encoding="UTF-8"?><!--
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
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
@@ -20,7 +21,13 @@ under the License.
<concept id="bit_functions" rev="2.3.0">
<title>Impala Bit Functions</title>
- <titlealts audience="PDF"><navtitle>Bit Functions</navtitle></titlealts>
+
+ <titlealts audience="PDF">
+
+ <navtitle>Bit Functions</navtitle>
+
+ </titlealts>
+
<prolog>
<metadata>
<data name="Category" value="Impala"/>
@@ -35,32 +42,38 @@ under the License.
<conbody>
<p rev="2.3.0">
- Bit manipulation functions perform bitwise operations involved in scientific processing or computer science algorithms.
- For example, these functions include setting, clearing, or testing bits within an integer value, or changing the
- positions of bits with or without wraparound.
+ Bit manipulation functions perform bitwise operations involved in scientific processing or
+ computer science algorithms. For example, these functions include setting, clearing, or
+ testing bits within an integer value, or changing the positions of bits with or without
+ wraparound.
</p>
<p>
- If a function takes two integer arguments that are required to be of the same type, the smaller argument is promoted
- to the type of the larger one if required. For example, <codeph>BITAND(1,4096)</codeph> treats both arguments as
- <codeph>SMALLINT</codeph>, because 1 can be represented as a <codeph>TINYINT</codeph> but 4096 requires a <codeph>SMALLINT</codeph>.
+ If a function takes two integer arguments that are required to be of the same type, the
+ smaller argument is promoted to the type of the larger one if required. For example,
+ <codeph>BITAND(1,4096)</codeph> treats both arguments as <codeph>SMALLINT</codeph>,
+ because 1 can be represented as a <codeph>TINYINT</codeph> but 4096 requires a
+ <codeph>SMALLINT</codeph>.
</p>
<p>
- Remember that all Impala integer values are signed. Therefore, when dealing with binary values where the most significant
- bit is 1, the specified or returned values might be negative when represented in base 10.
+ Remember that all Impala integer values are signed. Therefore, when dealing with binary
+ values where the most significant bit is 1, the specified or returned values might be
+ negative when represented in base 10.
</p>
<p>
- Whenever any argument is <codeph>NULL</codeph>, either the input value, bit position, or number of shift or rotate positions,
- the return value from any of these functions is also <codeph>NULL</codeph>
+ Whenever any argument is <codeph>NULL</codeph>, either the input value, bit position, or
+ number of shift or rotate positions, the return value from any of these functions is also
+ <codeph>NULL</codeph>
</p>
<p conref="../shared/impala_common.xml#common/related_info"/>
<p>
- The bit functions operate on all the integral data types: <xref href="impala_int.xml#int"/>,
- <xref href="impala_bigint.xml#bigint"/>, <xref href="impala_smallint.xml#smallint"/>, and
+ The bit functions operate on all the integral data types:
+ <xref href="impala_int.xml#int"/>, <xref href="impala_bigint.xml#bigint"/>,
+ <xref href="impala_smallint.xml#smallint"/>, and
<xref href="impala_tinyint.xml#tinyint"/>.
</p>
@@ -72,51 +85,85 @@ under the License.
Impala supports the following bit functions:
</p>
-<!--
-bitand
-bitnot
-bitor
-bitxor
-countset
-getbit
-rotateleft
-rotateright
-setbit
-shiftleft
-shiftright
--->
+ <ul>
+ <li>
+ <xref href="#bit_functions/bitand">BITAND</xref>
+ </li>
- <dl>
+ <li>
+ <xref href="#bit_functions/bitnot">BITNOT</xref>
+ </li>
+
+ <li>
+ <xref href="#bit_functions/bitor">BITOR</xref>
+ </li>
+ <li>
+ <xref href="#bit_functions/bitxor">BITXOR</xref>
+ </li>
+
+ <li>
+ <xref href="#bit_functions/countset">COUNTSET</xref>
+ </li>
+
+ <li>
+ <xref href="#bit_functions/getbit">GETBIT</xref>
+ </li>
+
+ <li>
+ <xref href="#bit_functions/rotateleft">ROTATELEFT</xref>
+ </li>
+
+ <li>
+ <xref href="#bit_functions/rotateright">ROTATERIGHT</xref>
+ </li>
+
+ <li>
+ <xref href="#bit_functions/setbit">SETBIT</xref>
+ </li>
+
+ <li>
+ <xref href="#bit_functions/shiftleft">SHIFTLEFT</xref>
+ </li>
+
+ <li>
+ <xref href="#bit_functions/shiftright">SHIFTRIGHT</xref>
+ </li>
+ </ul>
+
+ <dl>
<dlentry id="bitand">
<dt>
- <codeph>bitand(integer_type a, same_type b)</codeph>
+ BITAND(integer_type a, same_type b)
</dt>
<dd>
- <indexterm audience="hidden">bitand() function</indexterm>
- <b>Purpose:</b> Returns an integer value representing the bits that are set to 1 in both of the arguments.
- If the arguments are of different sizes, the smaller is promoted to the type of the larger.
+ <b>Purpose:</b> Returns an integer value representing the bits that are set to 1 in
+ both of the arguments. If the arguments are of different sizes, the smaller is
+ promoted to the type of the larger.
<p>
- <b>Usage notes:</b> The <codeph>bitand()</codeph> function is equivalent to the <codeph>&</codeph> binary operator.
+ <b>Usage notes:</b> The <codeph>BITAND()</codeph> function is equivalent to the
+ <codeph>&</codeph> binary operator.
</p>
+
<p conref="../shared/impala_common.xml#common/return_type_same"/>
+
<p conref="../shared/impala_common.xml#common/added_in_230"/>
+
<p conref="../shared/impala_common.xml#common/example_blurb"/>
+
<p>
- The following examples show the results of ANDing integer values.
- 255 contains all 1 bits in its lowermost 7 bits.
- 32767 contains all 1 bits in its lowermost 15 bits.
- <!--
+ The following examples show the results of ANDing integer values. 255 contains all 1
+ bits in its lowermost 7 bits. 32767 contains all 1 bits in its lowermost 15 bits.
+<!--
Negative numbers have a 1 in the sign bit and the value is the
<xref href="https://en.wikipedia.org/wiki/Two%27s_complement" scope="external" format="html">two's complement</xref>
of the positive equivalent.
-->
- You can use the <codeph>bin()</codeph> function to check the binary representation of any
- integer value, although the result is always represented as a 64-bit value.
- If necessary, the smaller argument is promoted to the
- type of the larger one.
+ You can use the <codeph>BIN()</codeph> function to check the binary representation
+ of any integer value, although the result is always represented as a 64-bit value.
+ If necessary, the smaller argument is promoted to the type of the larger one.
</p>
<codeblock>select bitand(255, 32767); /* 0000000011111111 & 0111111111111111 */
+--------------------+
@@ -160,23 +207,27 @@ select bitand(-1,15); /* 11111111 & 00001111 */
<dlentry id="bitnot">
<dt>
- <codeph>bitnot(integer_type a)</codeph>
+ BITNOT(integer_type a)
</dt>
<dd>
- <indexterm audience="hidden">bitnot() function</indexterm>
<b>Purpose:</b> Inverts all the bits of the input argument.
<p>
- <b>Usage notes:</b> The <codeph>bitnot()</codeph> function is equivalent to the <codeph>~</codeph> unary operator.
+ <b>Usage notes:</b> The <codeph>BITNOT()</codeph> function is equivalent to the
+ <codeph>~</codeph> unary operator.
</p>
+
<p conref="../shared/impala_common.xml#common/return_type_same"/>
+
<p conref="../shared/impala_common.xml#common/added_in_230"/>
+
<p conref="../shared/impala_common.xml#common/example_blurb"/>
+
<p>
- These examples illustrate what happens when you flip all the bits of an integer value.
- The sign always changes. The decimal representation is one different between the positive and
- negative values.
- <!--
+ These examples illustrate what happens when you flip all the bits of an integer
+ value. The sign always changes. The decimal representation is one different between
+ the positive and negative values.
+<!--
because negative values are represented as the
<xref href="https://en.wikipedia.org/wiki/Two%27s_complement" scope="external" format="html">two's complement</xref>
of the corresponding positive value.
@@ -217,19 +268,24 @@ select bitnot(-128); /* 10000000 -> 01111111 */
<dlentry id="bitor">
<dt>
- <codeph>bitor(integer_type a, same_type b)</codeph>
+ BITOR(integer_type a, same_type b)
</dt>
<dd>
- <indexterm audience="hidden">bitor() function</indexterm>
- <b>Purpose:</b> Returns an integer value representing the bits that are set to 1 in either of the arguments.
- If the arguments are of different sizes, the smaller is promoted to the type of the larger.
+ <b>Purpose:</b> Returns an integer value representing the bits that are set to 1 in
+ either of the arguments. If the arguments are of different sizes, the smaller is
+ promoted to the type of the larger.
<p>
- <b>Usage notes:</b> The <codeph>bitor()</codeph> function is equivalent to the <codeph>|</codeph> binary operator.
+ <b>Usage notes:</b> The <codeph>BITOR()</codeph> function is equivalent to the
+ <codeph>|</codeph> binary operator.
</p>
+
<p conref="../shared/impala_common.xml#common/return_type_same"/>
+
<p conref="../shared/impala_common.xml#common/added_in_230"/>
+
<p conref="../shared/impala_common.xml#common/example_blurb"/>
+
<p>
The following examples show the results of ORing integer values.
</p>
@@ -261,24 +317,30 @@ select bitor(0,7); /* 00000000 | 00000111 */
<dlentry id="bitxor">
<dt>
- <codeph>bitxor(integer_type a, same_type b)</codeph>
+ BITXOR(integer_type a, same_type b)
</dt>
<dd>
- <indexterm audience="hidden">bitxor() function</indexterm>
- <b>Purpose:</b> Returns an integer value representing the bits that are set to 1 in one but not both of the arguments.
- If the arguments are of different sizes, the smaller is promoted to the type of the larger.
+ <b>Purpose:</b> Returns an integer value representing the bits that are set to 1 in
+ one but not both of the arguments. If the arguments are of different sizes, the
+ smaller is promoted to the type of the larger.
<p>
- <b>Usage notes:</b> The <codeph>bitxor()</codeph> function is equivalent to the <codeph>^</codeph> binary operator.
+ <b>Usage notes:</b> The <codeph>BITXOR()</codeph> function is equivalent to the
+ <codeph>^</codeph> binary operator.
</p>
+
<p conref="../shared/impala_common.xml#common/return_type_same"/>
+
<p conref="../shared/impala_common.xml#common/added_in_230"/>
+
<p conref="../shared/impala_common.xml#common/example_blurb"/>
+
<p>
- The following examples show the results of XORing integer values.
- XORing a non-zero value with zero returns the non-zero value.
- XORing two identical values returns zero, because all the 1 bits from the first argument are also 1 bits in the second argument.
- XORing different non-zero values turns off some bits and leaves others turned on, based on whether the same bit is set in both arguments.
+ The following examples show the results of XORing integer values. XORing a non-zero
+ value with zero returns the non-zero value. XORing two identical values returns
+ zero, because all the 1 bits from the first argument are also 1 bits in the second
+ argument. XORing different non-zero values turns off some bits and leaves others
+ turned on, based on whether the same bit is set in both arguments.
</p>
<codeblock>select bitxor(0,15); /* 00000000 ^ 00001111 */
+---------------+
@@ -315,22 +377,30 @@ select bitxor(3,7); /* 00000011 ^ 00000111 */
<dlentry id="countset">
<dt>
- <codeph>countset(integer_type a [, int zero_or_one])</codeph>
+ COUNTSET(integer_type a [, INT zero_or_one])
</dt>
<dd>
- <indexterm audience="hidden">countset() function</indexterm>
- <b>Purpose:</b> By default, returns the number of 1 bits in the specified integer value.
- If the optional second argument is set to zero, it returns the number of 0 bits instead.
- <p conref="../shared/impala_common.xml#common/usage_notes_blurb"/>
+ <b>Purpose:</b> By default, returns the number of 1 bits in the specified integer
+ value. If the optional second argument is set to zero, it returns the number of 0 bits
+ instead.
+ <p
+ conref="../shared/impala_common.xml#common/usage_notes_blurb"/>
+
<p>
In discussions of information theory, this operation is referred to as the
- <q><xref href="https://en.wikipedia.org/wiki/Hamming_weight" scope="external" format="html">population count</xref></q>
- or <q>popcount</q>.
+ <q><xref
+ href="https://en.wikipedia.org/wiki/Hamming_weight"
+ scope="external" format="html">population
+ count</xref></q> or <q>popcount</q>.
</p>
+
<p conref="../shared/impala_common.xml#common/return_type_same"/>
+
<p conref="../shared/impala_common.xml#common/added_in_230"/>
+
<p conref="../shared/impala_common.xml#common/example_blurb"/>
+
<p>
The following examples show how to count the number of 1 bits in an integer value.
</p>
@@ -383,25 +453,29 @@ select countset(7,0); /* 00000111 = 5 0 bits; third argument can only be 0 or 1
<dlentry id="getbit">
<dt>
- <codeph>getbit(integer_type a, int position)</codeph>
+ GETBIT(integer_type a, INT position)
</dt>
<dd>
- <indexterm audience="hidden">getbit() function</indexterm>
- <b>Purpose:</b> Returns a 0 or 1 representing the bit at a
- specified position. The positions are numbered right to left, starting at zero.
- The position argument cannot be negative.
- <p conref="../shared/impala_common.xml#common/usage_notes_blurb"/>
+ <b>Purpose:</b> Returns a 0 or 1 representing the bit at a specified position. The
+ positions are numbered right to left, starting at zero. The position argument cannot
+ be negative.
+ <p
+ conref="../shared/impala_common.xml#common/usage_notes_blurb"/>
+
<p>
- When you use a literal input value, it is treated as an 8-bit, 16-bit,
- and so on value, the smallest type that is appropriate.
- The type of the input value limits the range of the positions.
- Cast the input value to the appropriate type if you need to
+ When you use a literal input value, it is treated as an 8-bit, 16-bit, and so on
+ value, the smallest type that is appropriate. The type of the input value limits the
+ range of the positions. Cast the input value to the appropriate type if you need to
ensure it is treated as a 64-bit, 32-bit, and so on value.
</p>
+
<p conref="../shared/impala_common.xml#common/return_type_same"/>
+
<p conref="../shared/impala_common.xml#common/added_in_230"/>
+
<p conref="../shared/impala_common.xml#common/example_blurb"/>
+
<p>
The following examples show how to test a specific bit within an integer value.
</p>
@@ -457,33 +531,37 @@ select getbit(cast(-1 as int),25); /* 11111111111111111111111111111111 */
<dlentry id="rotateleft">
<dt>
- <codeph>rotateleft(integer_type a, int positions)</codeph>
+ ROTATELEFT(integer_type a, INT positions)
</dt>
<dd>
- <indexterm audience="hidden">rotateleft() function</indexterm>
- <b>Purpose:</b> Rotates an integer value left by a specified number of bits.
- As the most significant bit is taken out of the original value,
- if it is a 1 bit, it is <q>rotated</q> back to the least significant bit.
- Therefore, the final value has the same number of 1 bits as the original value,
- just in different positions.
- In computer science terms, this operation is a
- <q><xref href="https://en.wikipedia.org/wiki/Circular_shift" scope="external" format="html">circular shift</xref></q>.
- <p conref="../shared/impala_common.xml#common/usage_notes_blurb"/>
+ <b>Purpose:</b> Rotates an integer value left by a specified number of bits. As the
+ most significant bit is taken out of the original value, if it is a 1 bit, it is
+ <q>rotated</q> back to the least significant bit. Therefore, the final value has the
+ same number of 1 bits as the original value, just in different positions. In computer
+ science terms, this operation is a
+ <q><xref
+ href="https://en.wikipedia.org/wiki/Circular_shift"
+ scope="external" format="html">circular
+ shift</xref></q>.
+ <p
+ conref="../shared/impala_common.xml#common/usage_notes_blurb"/>
+
<p>
- Specifying a second argument of zero leaves the original value unchanged.
- Rotating a -1 value by any number of positions still returns -1,
- because the original value has all 1 bits and all the 1 bits are
- preserved during rotation.
- Similarly, rotating a 0 value by any number of positions still returns 0.
- Rotating a value by the same number of bits as in the value returns the same value.
- Because this is a circular operation, the number of positions is not limited
- to the number of bits in the input value.
- For example, rotating an 8-bit value by 1, 9, 17, and so on positions returns an
- identical result in each case.
+ Specifying a second argument of zero leaves the original value unchanged. Rotating a
+ -1 value by any number of positions still returns -1, because the original value has
+ all 1 bits and all the 1 bits are preserved during rotation. Similarly, rotating a 0
+ value by any number of positions still returns 0. Rotating a value by the same
+ number of bits as in the value returns the same value. Because this is a circular
+ operation, the number of positions is not limited to the number of bits in the input
+ value. For example, rotating an 8-bit value by 1, 9, 17, and so on positions returns
+ an identical result in each case.
</p>
+
<p conref="../shared/impala_common.xml#common/return_type_same"/>
+
<p conref="../shared/impala_common.xml#common/added_in_230"/>
+
<p conref="../shared/impala_common.xml#common/example_blurb"/>
<codeblock>select rotateleft(1,4); /* 00000001 -> 00010000 */
+------------------+
@@ -521,33 +599,37 @@ select rotateleft(-127,3); /* 10000001 -> 00001100 */
<dlentry id="rotateright">
<dt>
- <codeph>rotateright(integer_type a, int positions)</codeph>
+ ROTATERIGHT(integer_type a, INT positions)
</dt>
<dd>
- <indexterm audience="hidden">rotateright() function</indexterm>
- <b>Purpose:</b> Rotates an integer value right by a specified number of bits.
- As the least significant bit is taken out of the original value,
- if it is a 1 bit, it is <q>rotated</q> back to the most significant bit.
- Therefore, the final value has the same number of 1 bits as the original value,
- just in different positions.
- In computer science terms, this operation is a
- <q><xref href="https://en.wikipedia.org/wiki/Circular_shift" scope="external" format="html">circular shift</xref></q>.
- <p conref="../shared/impala_common.xml#common/usage_notes_blurb"/>
+ <b>Purpose:</b> Rotates an integer value right by a specified number of bits. As the
+ least significant bit is taken out of the original value, if it is a 1 bit, it is
+ <q>rotated</q> back to the most significant bit. Therefore, the final value has the
+ same number of 1 bits as the original value, just in different positions. In computer
+ science terms, this operation is a
+ <q><xref
+ href="https://en.wikipedia.org/wiki/Circular_shift"
+ scope="external" format="html">circular
+ shift</xref></q>.
+ <p
+ conref="../shared/impala_common.xml#common/usage_notes_blurb"/>
+
<p>
- Specifying a second argument of zero leaves the original value unchanged.
- Rotating a -1 value by any number of positions still returns -1,
- because the original value has all 1 bits and all the 1 bits are
- preserved during rotation.
- Similarly, rotating a 0 value by any number of positions still returns 0.
- Rotating a value by the same number of bits as in the value returns the same value.
- Because this is a circular operation, the number of positions is not limited
- to the number of bits in the input value.
- For example, rotating an 8-bit value by 1, 9, 17, and so on positions returns an
- identical result in each case.
+ Specifying a second argument of zero leaves the original value unchanged. Rotating a
+ -1 value by any number of positions still returns -1, because the original value has
+ all 1 bits and all the 1 bits are preserved during rotation. Similarly, rotating a 0
+ value by any number of positions still returns 0. Rotating a value by the same
+ number of bits as in the value returns the same value. Because this is a circular
+ operation, the number of positions is not limited to the number of bits in the input
+ value. For example, rotating an 8-bit value by 1, 9, 17, and so on positions returns
+ an identical result in each case.
</p>
+
<p conref="../shared/impala_common.xml#common/return_type_same"/>
+
<p conref="../shared/impala_common.xml#common/added_in_230"/>
+
<p conref="../shared/impala_common.xml#common/example_blurb"/>
<codeblock>select rotateright(16,4); /* 00010000 -> 00000001 */
+--------------------+
@@ -584,30 +666,31 @@ select rotateright(-127,3); /* 10000001 -> 00110000 */
<dlentry id="setbit">
<dt>
- <codeph>setbit(integer_type a, int position [, int zero_or_one])</codeph>
+ SETBIT(integer_type a, INT position [, INT zero_or_one])
</dt>
<dd>
- <indexterm audience="hidden">setbit() function</indexterm>
- <b>Purpose:</b> By default, changes a bit at a specified position to a 1, if it is not already.
- If the optional third argument is set to zero, the specified bit is set to 0 instead.
- <p conref="../shared/impala_common.xml#common/usage_notes_blurb"/>
- If the bit at the specified position was already 1 (by default)
- or 0 (with a third argument of zero), the return value is
- the same as the first argument.
- The positions are numbered right to left, starting at zero.
- (Therefore, the return value could be different from the first argument
- even if the position argument is zero.)
- The position argument cannot be negative.
+ <b>Purpose:</b> By default, changes a bit at a specified position to a 1, if it is not
+ already. If the optional third argument is set to zero, the specified bit is set to 0
+ instead.
+ <p
+ conref="../shared/impala_common.xml#common/usage_notes_blurb"/>
+ If the bit at the specified position was already 1 (by default) or 0 (with a third
+ argument of zero), the return value is the same as the first argument. The positions
+ are numbered right to left, starting at zero. (Therefore, the return value could be
+ different from the first argument even if the position argument is zero.) The position
+ argument cannot be negative.
<p>
- When you use a literal input value, it is treated as an 8-bit, 16-bit,
- and so on value, the smallest type that is appropriate.
- The type of the input value limits the range of the positions.
- Cast the input value to the appropriate type if you need to
+ When you use a literal input value, it is treated as an 8-bit, 16-bit, and so on
+ value, the smallest type that is appropriate. The type of the input value limits the
+ range of the positions. Cast the input value to the appropriate type if you need to
ensure it is treated as a 64-bit, 32-bit, and so on value.
</p>
+
<p conref="../shared/impala_common.xml#common/return_type_same"/>
+
<p conref="../shared/impala_common.xml#common/added_in_230"/>
+
<p conref="../shared/impala_common.xml#common/example_blurb"/>
<codeblock>select setbit(0,0); /* 00000000 -> 00000001 */
+--------------+
@@ -668,32 +751,39 @@ select setbit(7,2,0); /* 00000111 -> 00000011; third argument of 0 clears instea
<dlentry id="shiftleft">
<dt>
- <codeph>shiftleft(integer_type a, int positions)</codeph>
+ SHIFTLEFT(integer_type a, INT positions)
</dt>
<dd>
- <indexterm audience="hidden">shiftleft() function</indexterm>
- <b>Purpose:</b> Shifts an integer value left by a specified number of bits.
- As the most significant bit is taken out of the original value,
- it is discarded and the least significant bit becomes 0.
- In computer science terms, this operation is a <q><xref href="https://en.wikipedia.org/wiki/Logical_shift" scope="external" format="html">logical shift</xref></q>.
- <p conref="../shared/impala_common.xml#common/usage_notes_blurb"/>
+ <b>Purpose:</b> Shifts an integer value left by a specified number of bits. As the
+ most significant bit is taken out of the original value, it is discarded and the least
+ significant bit becomes 0. In computer science terms, this operation is a
+ <q><xref
+ href="https://en.wikipedia.org/wiki/Logical_shift"
+ scope="external" format="html">logical
+ shift</xref></q>.
+ <p
+ conref="../shared/impala_common.xml#common/usage_notes_blurb"/>
+
<p>
- The final value has either the same number of 1 bits as the original value, or fewer.
- Shifting an 8-bit value by 8 positions, a 16-bit value by 16 positions, and so on produces
- a result of zero.
+ The final value has either the same number of 1 bits as the original value, or
+ fewer. Shifting an 8-bit value by 8 positions, a 16-bit value by 16 positions, and
+ so on produces a result of zero.
</p>
+
<p>
- Specifying a second argument of zero leaves the original value unchanged.
- Shifting any value by 0 returns the original value.
- Shifting any value by 1 is the same as multiplying it by 2,
- as long as the value is small enough; larger values eventually
- become negative when shifted, as the sign bit is set.
- Starting with the value 1 and shifting it left by N positions gives
- the same result as 2 to the Nth power, or <codeph>pow(2,<varname>N</varname>)</codeph>.
+ Specifying a second argument of zero leaves the original value unchanged. Shifting
+ any value by 0 returns the original value. Shifting any value by 1 is the same as
+ multiplying it by 2, as long as the value is small enough; larger values eventually
+ become negative when shifted, as the sign bit is set. Starting with the value 1 and
+ shifting it left by N positions gives the same result as 2 to the Nth power, or
+ <codeph>POW(2,<varname>N</varname>)</codeph>.
</p>
+
<p conref="../shared/impala_common.xml#common/return_type_same"/>
+
<p conref="../shared/impala_common.xml#common/added_in_230"/>
+
<p conref="../shared/impala_common.xml#common/example_blurb"/>
<codeblock>select shiftleft(1,0); /* 00000001 -> 00000001 */
+-----------------+
@@ -744,29 +834,36 @@ select shiftleft(-1,4); /* 11111111 -> 11110000 */
<dlentry id="shiftright">
<dt>
- <codeph>shiftright(integer_type a, int positions)</codeph>
+ SHIFTRIGHT(integer_type a, INT positions)
</dt>
<dd>
- <indexterm audience="hidden">shiftright() function</indexterm>
- <b>Purpose:</b> Shifts an integer value right by a specified number of bits.
- As the least significant bit is taken out of the original value,
- it is discarded and the most significant bit becomes 0.
- In computer science terms, this operation is a <q><xref href="https://en.wikipedia.org/wiki/Logical_shift" scope="external" format="html">logical shift</xref></q>.
- <p conref="../shared/impala_common.xml#common/usage_notes_blurb"/>
+ <b>Purpose:</b> Shifts an integer value right by a specified number of bits. As the
+ least significant bit is taken out of the original value, it is discarded and the most
+ significant bit becomes 0. In computer science terms, this operation is a
+ <q><xref
+ href="https://en.wikipedia.org/wiki/Logical_shift"
+ scope="external" format="html">logical
+ shift</xref></q>.
+ <p
+ conref="../shared/impala_common.xml#common/usage_notes_blurb"/>
+
<p>
- Therefore, the final value has either the same number of 1 bits as the original value, or fewer.
- Shifting an 8-bit value by 8 positions, a 16-bit value by 16 positions, and so on produces
- a result of zero.
+ Therefore, the final value has either the same number of 1 bits as the original
+ value, or fewer. Shifting an 8-bit value by 8 positions, a 16-bit value by 16
+ positions, and so on produces a result of zero.
</p>
+
<p>
- Specifying a second argument of zero leaves the original value unchanged.
- Shifting any value by 0 returns the original value.
- Shifting any positive value right by 1 is the same as dividing it by 2.
- Negative values become positive when shifted right.
+ Specifying a second argument of zero leaves the original value unchanged. Shifting
+ any value by 0 returns the original value. Shifting any positive value right by 1 is
+ the same as dividing it by 2. Negative values become positive when shifted right.
</p>
+
<p conref="../shared/impala_common.xml#common/return_type_same"/>
+
<p conref="../shared/impala_common.xml#common/added_in_230"/>
+
<p conref="../shared/impala_common.xml#common/example_blurb"/>
<codeblock>select shiftright(16,0); /* 00010000 -> 00010000 */
+-------------------+
@@ -806,7 +903,8 @@ select shiftright(-1,5); /* 11111111 -> 00000111 */
</dd>
</dlentry>
-
</dl>
+
</conbody>
+
</concept>
http://git-wip-us.apache.org/repos/asf/impala/blob/e8ee827a/docs/topics/impala_conditional_functions.xml
----------------------------------------------------------------------
diff --git a/docs/topics/impala_conditional_functions.xml b/docs/topics/impala_conditional_functions.xml
index 45717e1..106c518 100644
--- a/docs/topics/impala_conditional_functions.xml
+++ b/docs/topics/impala_conditional_functions.xml
@@ -1,4 +1,5 @@
-<?xml version="1.0" encoding="UTF-8"?><!--
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
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
@@ -20,7 +21,13 @@ under the License.
<concept id="conditional_functions">
<title>Impala Conditional Functions</title>
- <titlealts audience="PDF"><navtitle>Conditional Functions</navtitle></titlealts>
+
+ <titlealts audience="PDF">
+
+ <navtitle>Conditional Functions</navtitle>
+
+ </titlealts>
+
<prolog>
<metadata>
<data name="Category" value="Impala"/>
@@ -35,34 +42,115 @@ under the License.
<conbody>
<p>
- Impala supports the following conditional functions for testing equality, comparison operators, and nullity:
+ Impala supports the following conditional functions for testing equality, comparison
+ operators, and nullity:
</p>
+ <ul>
+ <li>
+ <xref href="#conditional_functions/case">CASE</xref>
+ </li>
+
+ <li>
+ <xref href="#conditional_functions/case2">CASE2</xref>
+ </li>
+
+ <li>
+ <xref href="#conditional_functions/coalesce">COALESCE</xref>
+ </li>
+
+ <li>
+ <xref href="#conditional_functions/decode">DECODE</xref>
+ </li>
+
+ <li>
+ <xref href="#conditional_functions/if">IF</xref>
+ </li>
+
+ <li>
+ <xref href="#conditional_functions/ifnull">IFNULL</xref>
+ </li>
+
+ <li>
+ <xref href="#conditional_functions/isfalse">ISFALSE</xref>
+ </li>
+
+ <li>
+ <xref href="#conditional_functions/isnotfalse">ISNOTFALSE</xref>
+ </li>
+
+ <li>
+ <xref href="#conditional_functions/isnottrue">ISNOTTRUE</xref>
+ </li>
+
+ <li>
+ <xref href="#conditional_functions/isnull">ISNULL</xref>
+ </li>
+
+ <li>
+ <xref href="#conditional_functions/istrue">ISTRUE</xref>
+ </li>
+
+ <li>
+ <xref href="#conditional_functions/nonnullvalue">NONNULLVALUE</xref>
+ </li>
+
+ <li>
+ <xref href="#conditional_functions/nullif">NULLIF</xref>
+ </li>
+
+ <li>
+ <xref href="#conditional_functions/nullifzero">NULLIFZERO</xref>
+ </li>
+
+ <li>
+ <xref href="#conditional_functions/nullvalue">NULLVALUE</xref>
+ </li>
+
+ <li>
+ <xref href="#conditional_functions/nvl">NVL</xref>
+ </li>
+
+ <li>
+ <xref href="#conditional_functions/nvl2">NVL2</xref>
+ </li>
+
+ <li>
+ <xref href="#conditional_functions/zeroifnull">ZEROIFNULL</xref>
+ </li>
+ </ul>
+
<dl>
<dlentry id="case">
<dt>
- <codeph>CASE a WHEN b THEN c [WHEN d THEN e]... [ELSE f] END</codeph>
+ CASE a WHEN b THEN c [WHEN d THEN e]... [ELSE f] END
</dt>
<dd>
- <indexterm audience="hidden">CASE expression</indexterm>
- <b>Purpose:</b> Compares an expression to one or more possible values, and returns a corresponding result
- when a match is found.
- <p conref="../shared/impala_common.xml#common/return_same_type"/>
+ <b>Purpose:</b> Compares an expression to one or more possible values, and returns a
+ corresponding result when a match is found.
+ <p
+ conref="../shared/impala_common.xml#common/return_same_type"/>
+
<p conref="../shared/impala_common.xml#common/usage_notes_blurb"/>
+
<p>
- In this form of the <codeph>CASE</codeph> expression, the initial value <codeph>A</codeph>
- being evaluated for each row it typically a column reference, or an expression involving
- a column. This form can only compare against a set of specified values, not ranges,
- multi-value comparisons such as <codeph>BETWEEN</codeph> or <codeph>IN</codeph>,
- regular expressions, or <codeph>NULL</codeph>.
+ In this form of the <codeph>CASE</codeph> expression, the initial value
+ <codeph>A</codeph> being evaluated for each row it typically a column reference, or
+ an expression involving a column. This form can only compare against a set of
+ specified values, not ranges, multi-value comparisons such as
+ <codeph>BETWEEN</codeph> or <codeph>IN</codeph>, regular expressions, or
+ <codeph>NULL</codeph>.
</p>
+
<p conref="../shared/impala_common.xml#common/example_blurb"/>
+
<p>
- Although this example is split across multiple lines, you can put any or all parts of a <codeph>CASE</codeph> expression
- on a single line, with no punctuation or other separators between the <codeph>WHEN</codeph>,
- <codeph>ELSE</codeph>, and <codeph>END</codeph> clauses.
+ Although this example is split across multiple lines, you can put any or all parts
+ of a <codeph>CASE</codeph> expression on a single line, with no punctuation or other
+ separators between the <codeph>WHEN</codeph>, <codeph>ELSE</codeph>, and
+ <codeph>END</codeph> clauses.
</p>
<codeblock>select case x
when 1 then 'one'
@@ -79,52 +167,61 @@ under the License.
<dlentry id="case2">
<dt>
- <codeph>CASE WHEN a THEN b [WHEN c THEN d]... [ELSE e] END</codeph>
+ CASE WHEN a THEN b [WHEN c THEN d]... [ELSE e] END
</dt>
<dd>
- <indexterm audience="hidden">CASE expression</indexterm>
- <b>Purpose:</b> Tests whether any of a sequence of expressions is true, and returns a corresponding
- result for the first true expression.
- <p conref="../shared/impala_common.xml#common/return_same_type"/>
+ <b>Purpose:</b> Tests whether any of a sequence of expressions is true, and returns a
+ corresponding result for the first true expression.
+ <p
+ conref="../shared/impala_common.xml#common/return_same_type"/>
+
<p conref="../shared/impala_common.xml#common/usage_notes_blurb"/>
+
<p>
- <codeph>CASE</codeph> expressions without an initial test value have more flexibility.
- For example, they can test different columns in different <codeph>WHEN</codeph> clauses,
- or use comparison operators such as <codeph>BETWEEN</codeph>, <codeph>IN</codeph> and <codeph>IS NULL</codeph>
- rather than comparing against discrete values.
+ <codeph>CASE</codeph> expressions without an initial test value have more
+ flexibility. For example, they can test different columns in different
+ <codeph>WHEN</codeph> clauses, or use comparison operators such as
+ <codeph>BETWEEN</codeph>, <codeph>IN</codeph> and <codeph>IS NULL</codeph> rather
+ than comparing against discrete values.
</p>
+
<p>
<codeph>CASE</codeph> expressions are often the foundation of long queries that
- summarize and format results for easy-to-read reports. For example, you might
- use a <codeph>CASE</codeph> function call to turn values from a numeric column
- into category strings corresponding to integer values, or labels such as <q>Small</q>,
- <q>Medium</q> and <q>Large</q> based on ranges. Then subsequent parts of the
- query might aggregate based on the transformed values, such as how many
- values are classified as small, medium, or large. You can also use <codeph>CASE</codeph>
- to signal problems with out-of-bounds values, <codeph>NULL</codeph> values,
- and so on.
+ summarize and format results for easy-to-read reports. For example, you might use a
+ <codeph>CASE</codeph> function call to turn values from a numeric column into
+ category strings corresponding to integer values, or labels such as <q>Small</q>,
+ <q>Medium</q> and <q>Large</q> based on ranges. Then subsequent parts of the query
+ might aggregate based on the transformed values, such as how many values are
+ classified as small, medium, or large. You can also use <codeph>CASE</codeph> to
+ signal problems with out-of-bounds values, <codeph>NULL</codeph> values, and so on.
</p>
+
<p>
By using operators such as <codeph>OR</codeph>, <codeph>IN</codeph>,
- <codeph>REGEXP</codeph>, and so on in <codeph>CASE</codeph> expressions,
- you can build extensive tests and transformations into a single query.
- Therefore, applications that construct SQL statements often rely heavily on <codeph>CASE</codeph>
- calls in the generated SQL code.
+ <codeph>REGEXP</codeph>, and so on in <codeph>CASE</codeph> expressions, you can
+ build extensive tests and transformations into a single query. Therefore,
+ applications that construct SQL statements often rely heavily on
+ <codeph>CASE</codeph> calls in the generated SQL code.
</p>
+
<p>
- Because this flexible form of the <codeph>CASE</codeph> expressions allows you to perform
- many comparisons and call multiple functions when evaluating each row, be careful applying
- elaborate <codeph>CASE</codeph> expressions to queries that process large amounts of data.
- For example, when practical, evaluate and transform values through <codeph>CASE</codeph>
- after applying operations such as aggregations that reduce the size of the result set;
- transform numbers to strings after performing joins with the original numeric values.
+ Because this flexible form of the <codeph>CASE</codeph> expressions allows you to
+ perform many comparisons and call multiple functions when evaluating each row, be
+ careful applying elaborate <codeph>CASE</codeph> expressions to queries that process
+ large amounts of data. For example, when practical, evaluate and transform values
+ through <codeph>CASE</codeph> after applying operations such as aggregations that
+ reduce the size of the result set; transform numbers to strings after performing
+ joins with the original numeric values.
</p>
+
<p conref="../shared/impala_common.xml#common/example_blurb"/>
+
<p>
- Although this example is split across multiple lines, you can put any or all parts of a <codeph>CASE</codeph> expression
- on a single line, with no punctuation or other separators between the <codeph>WHEN</codeph>,
- <codeph>ELSE</codeph>, and <codeph>END</codeph> clauses.
+ Although this example is split across multiple lines, you can put any or all parts
+ of a <codeph>CASE</codeph> expression on a single line, with no punctuation or other
+ separators between the <codeph>WHEN</codeph>, <codeph>ELSE</codeph>, and
+ <codeph>END</codeph> clauses.
</p>
<codeblock>select case
when dayname(now()) in ('Saturday','Sunday') then 'result undefined on weekends'
@@ -142,14 +239,15 @@ under the License.
<dlentry id="coalesce">
<dt>
- <codeph>coalesce(type v1, type v2, ...)</codeph>
+ COALESCE(type v1, type v2, ...)
</dt>
<dd>
- <indexterm audience="hidden">coalesce() function</indexterm>
- <b>Purpose:</b> Returns the first specified argument that is not <codeph>NULL</codeph>, or
- <codeph>NULL</codeph> if all arguments are <codeph>NULL</codeph>.
- <p conref="../shared/impala_common.xml#common/return_same_type"/>
+ <b>Purpose:</b> Returns the first specified argument that is not
+ <codeph>NULL</codeph>, or <codeph>NULL</codeph> if all arguments are
+ <codeph>NULL</codeph>.
+ <p
+ conref="../shared/impala_common.xml#common/return_same_type"/>
</dd>
</dlentry>
@@ -157,32 +255,40 @@ under the License.
<dlentry rev="2.0.0" id="decode">
<dt>
- <codeph>decode(type expression, type search1, type result1 [, type search2, type result2 ...] [, type
- default] )</codeph>
+ DECODE(type expression, type search1, type result1 [, type search2, type result2 ...]
+ [, type default] )
</dt>
<dd>
- <indexterm audience="hidden">decode() function</indexterm>
- <b>Purpose:</b> Compares an expression to one or more possible values, and returns a corresponding result
- when a match is found.
- <p conref="../shared/impala_common.xml#common/return_same_type"/>
+ <b>Purpose:</b> Compares an expression to one or more possible values, and returns a
+ corresponding result when a match is found.
+ <p
+ conref="../shared/impala_common.xml#common/return_same_type"/>
+
<p conref="../shared/impala_common.xml#common/usage_notes_blurb"/>
+
<p>
Can be used as shorthand for a <codeph>CASE</codeph> expression.
</p>
+
<p>
- The original expression and the search expressions must of the same type or convertible types. The
- result expression can be a different type, but all result expressions must be of the same type.
+ The original expression and the search expressions must of the same type or
+ convertible types. The result expression can be a different type, but all result
+ expressions must be of the same type.
</p>
+
<p>
- Returns a successful match If the original expression is <codeph>NULL</codeph> and a search expression
- is also <codeph>NULL</codeph>. the
+ Returns a successful match If the original expression is <codeph>NULL</codeph> and a
+ search expression is also <codeph>NULL</codeph>. the
</p>
+
<p>
- Returns <codeph>NULL</codeph> if the final <codeph>default</codeph> value is omitted and none of the
- search expressions match the original expression.
+ Returns <codeph>NULL</codeph> if the final <codeph>default</codeph> value is omitted
+ and none of the search expressions match the original expression.
</p>
+
<p conref="../shared/impala_common.xml#common/example_blurb"/>
+
<p>
The following example translates numeric day values into descriptive names:
</p>
@@ -197,13 +303,12 @@ under the License.
<dlentry id="if">
<dt>
- <codeph>if(boolean condition, type ifTrue, type ifFalseOrNull)</codeph>
+ IF(BOOLEAN condition, type ifTrue, type ifFalseOrNull)
</dt>
<dd>
- <indexterm audience="hidden">if() function</indexterm>
- <b>Purpose:</b> Tests an expression and returns a corresponding result depending on whether the result is
- true, false, or <codeph>NULL</codeph>.
+ <b>Purpose:</b> Tests an expression and returns a corresponding result depending on
+ whether the result is true, false, or <codeph>NULL</codeph>.
<p>
<b>Return type:</b> Same as the <codeph>ifTrue</codeph> argument value
</p>
@@ -214,13 +319,12 @@ under the License.
<dlentry rev="1.3.0" id="ifnull">
<dt>
- <codeph>ifnull(type a, type ifNull)</codeph>
+ IFNULL(type a, type ifNull)
</dt>
<dd>
- <indexterm audience="hidden">isnull() function</indexterm>
- <b>Purpose:</b> Alias for the <codeph>isnull()</codeph> function, with the same behavior. To simplify
- porting SQL with vendor extensions to Impala.
+ <b>Purpose:</b> Alias for the <codeph>ISNULL()</codeph> function, with the same
+ behavior. To simplify porting SQL with vendor extensions to Impala.
<p conref="../shared/impala_common.xml#common/added_in_130"/>
</dd>
@@ -229,19 +333,24 @@ under the License.
<dlentry id="isfalse" rev="2.2.0">
<dt>
- <codeph>isfalse(<varname>boolean</varname>)</codeph>
+ ISFALSE(BOOLEAN expression)
</dt>
<dd>
- <indexterm audience="hidden">isfalse() function</indexterm>
<b>Purpose:</b> Tests if a Boolean expression is <codeph>false</codeph> or not.
- Returns <codeph>true</codeph> if so.
- If the argument is <codeph>NULL</codeph>, returns <codeph>false</codeph>.
- Identical to <codeph>isnottrue()</codeph>, except it returns the opposite value for a <codeph>NULL</codeph> argument.
- <p conref="../shared/impala_common.xml#common/return_type_boolean"/>
+ Returns <codeph>true</codeph> if so. If the argument is <codeph>NULL</codeph>, returns
+ <codeph>false</codeph>. Similar to <codeph>ISNOTTRUE()</codeph>, except it returns the
+ opposite value for a <codeph>NULL</codeph> argument.
+ <p
+ conref="../shared/impala_common.xml#common/return_type_boolean"/>
+
<p conref="../shared/impala_common.xml#common/added_in_220"/>
+
<p conref="../shared/impala_common.xml#common/usage_notes_blurb"/>
- <p conref="../shared/impala_common.xml#common/boolean_functions_vs_expressions"/>
+
+ <p
+ conref="../shared/impala_common.xml#common/boolean_functions_vs_expressions"
+ />
</dd>
</dlentry>
@@ -249,20 +358,27 @@ under the License.
<dlentry id="isnotfalse" rev="2.2.0">
<dt>
- <codeph>isnotfalse(<varname>boolean</varname>)</codeph>
+ ISNOTFALSE(BOOLEAN expression)
</dt>
<dd>
- <indexterm audience="hidden">isnotfalse() function</indexterm>
- <b>Purpose:</b> Tests if a Boolean expression is not <codeph>false</codeph> (that is, either <codeph>true</codeph> or <codeph>NULL</codeph>).
- Returns <codeph>true</codeph> if so.
- If the argument is <codeph>NULL</codeph>, returns <codeph>true</codeph>.
- Identical to <codeph>istrue()</codeph>, except it returns the opposite value for a <codeph>NULL</codeph> argument.
- <p conref="../shared/impala_common.xml#common/return_type_boolean"/>
+ <b>Purpose:</b> Tests if a Boolean expression is not <codeph>false</codeph> (that is,
+ either <codeph>true</codeph> or <codeph>NULL</codeph>). Returns <codeph>true</codeph>
+ if so. If the argument is <codeph>NULL</codeph>, returns <codeph>true</codeph>.
+ Similar to <codeph>ISTRUE()</codeph>, except it returns the opposite value for a
+ <codeph>NULL</codeph> argument.
+ <p
+ conref="../shared/impala_common.xml#common/return_type_boolean"/>
+
<p conref="../shared/impala_common.xml#common/for_compatibility_only"/>
+
<p conref="../shared/impala_common.xml#common/added_in_220"/>
+
<p conref="../shared/impala_common.xml#common/usage_notes_blurb"/>
- <p conref="../shared/impala_common.xml#common/boolean_functions_vs_expressions"/>
+
+ <p
+ conref="../shared/impala_common.xml#common/boolean_functions_vs_expressions"
+ />
</dd>
</dlentry>
@@ -270,19 +386,25 @@ under the License.
<dlentry id="isnottrue" rev="2.2.0">
<dt>
- <codeph>isnottrue(<varname>boolean</varname>)</codeph>
+ ISNOTTRUE(BOOLEAN expression)
</dt>
<dd>
- <indexterm audience="hidden">isnottrue() function</indexterm>
- <b>Purpose:</b> Tests if a Boolean expression is not <codeph>true</codeph> (that is, either <codeph>false</codeph> or <codeph>NULL</codeph>).
- Returns <codeph>true</codeph> if so.
- If the argument is <codeph>NULL</codeph>, returns <codeph>true</codeph>.
- Identical to <codeph>isfalse()</codeph>, except it returns the opposite value for a <codeph>NULL</codeph> argument.
- <p conref="../shared/impala_common.xml#common/return_type_boolean"/>
+ <b>Purpose:</b> Tests if a Boolean expression is not <codeph>true</codeph> (that is,
+ either <codeph>false</codeph> or <codeph>NULL</codeph>). Returns <codeph>true</codeph>
+ if so. If the argument is <codeph>NULL</codeph>, returns <codeph>true</codeph>.
+ Similar to <codeph>ISFALSE()</codeph>, except it returns the opposite value for a
+ <codeph>NULL</codeph> argument.
+ <p
+ conref="../shared/impala_common.xml#common/return_type_boolean"/>
+
<p conref="../shared/impala_common.xml#common/added_in_220"/>
+
<p conref="../shared/impala_common.xml#common/usage_notes_blurb"/>
- <p conref="../shared/impala_common.xml#common/boolean_functions_vs_expressions"/>
+
+ <p
+ conref="../shared/impala_common.xml#common/boolean_functions_vs_expressions"
+ />
</dd>
</dlentry>
@@ -290,18 +412,19 @@ under the License.
<dlentry id="isnull">
<dt>
- <codeph>isnull(type a, type ifNull)</codeph>
+ ISNULL(type a, type ifNull)
</dt>
<dd>
- <indexterm audience="hidden">isnull() function</indexterm>
- <b>Purpose:</b> Tests if an expression is <codeph>NULL</codeph>, and returns the expression result value
- if not. If the first argument is <codeph>NULL</codeph>, returns the second argument.
+ <b>Purpose:</b> Tests if an expression is <codeph>NULL</codeph>, and returns the
+ expression result value if not. If the first argument is <codeph>NULL</codeph>,
+ returns the second argument.
<p>
- <b>Compatibility notes:</b> Equivalent to the <codeph>nvl()</codeph> function from Oracle Database or
- <codeph>ifnull()</codeph> from MySQL. The <codeph>nvl()</codeph> and <codeph>ifnull()</codeph>
- functions are also available in Impala.
+ <b>Compatibility notes:</b> Equivalent to the <codeph>NVL()</codeph> function from
+ Oracle Database or <codeph>IFNULL()</codeph> from MySQL. The <codeph>NVL()</codeph>
+ and <codeph>IFNULL()</codeph> functions are also available in Impala.
</p>
+
<p>
<b>Return type:</b> Same as the first argument value
</p>
@@ -312,20 +435,26 @@ under the License.
<dlentry id="istrue" rev="2.2.0">
<dt>
- <codeph>istrue(<varname>boolean</varname>)</codeph>
+ ISTRUE(BOOLEAN expression)
</dt>
<dd>
- <indexterm audience="hidden">istrue() function</indexterm>
- <b>Purpose:</b> Tests if a Boolean expression is <codeph>true</codeph> or not.
- Returns <codeph>true</codeph> if so.
- If the argument is <codeph>NULL</codeph>, returns <codeph>false</codeph>.
- Identical to <codeph>isnotfalse()</codeph>, except it returns the opposite value for a <codeph>NULL</codeph> argument.
- <p conref="../shared/impala_common.xml#common/return_type_boolean"/>
+ <b>Purpose:</b> Tests if a Boolean expression is <codeph>true</codeph> or not. Returns
+ <codeph>true</codeph> if so. If the argument is <codeph>NULL</codeph>, returns
+ <codeph>false</codeph>. Similar to <codeph>ISNOTFALSE()</codeph>, except it returns
+ the opposite value for a <codeph>NULL</codeph> argument.
+ <p
+ conref="../shared/impala_common.xml#common/return_type_boolean"/>
+
<p conref="../shared/impala_common.xml#common/for_compatibility_only"/>
+
<p conref="../shared/impala_common.xml#common/added_in_220"/>
+
<p conref="../shared/impala_common.xml#common/usage_notes_blurb"/>
- <p conref="../shared/impala_common.xml#common/boolean_functions_vs_expressions"/>
+
+ <p
+ conref="../shared/impala_common.xml#common/boolean_functions_vs_expressions"
+ />
</dd>
</dlentry>
@@ -333,16 +462,17 @@ under the License.
<dlentry id="nonnullvalue" rev="2.2.0">
<dt>
- <codeph>nonnullvalue(<varname>expression</varname>)</codeph>
+ NONNULLVALUE(type expression)
</dt>
<dd>
- <indexterm audience="hidden">function</indexterm>
<b>Purpose:</b> Tests if an expression (of any type) is <codeph>NULL</codeph> or not.
- Returns <codeph>false</codeph> if so.
- The converse of <codeph>nullvalue()</codeph>.
- <p conref="../shared/impala_common.xml#common/return_type_boolean"/>
+ Returns <codeph>false</codeph> if so. The converse of <codeph>NULLVALUE()</codeph>.
+ <p
+ conref="../shared/impala_common.xml#common/return_type_boolean"/>
+
<p conref="../shared/impala_common.xml#common/for_compatibility_only"/>
+
<p conref="../shared/impala_common.xml#common/added_in_220"/>
</dd>
@@ -351,35 +481,38 @@ under the License.
<dlentry rev="1.3.0" id="nullif">
<dt>
- <codeph>nullif(<varname>expr1</varname>,<varname>expr2</varname>)</codeph>
+ NULLIF(type expr1, type expr2)
</dt>
<dd>
- <indexterm audience="hidden">nullif() function</indexterm>
- <b>Purpose:</b> Returns <codeph>NULL</codeph> if the two specified arguments are equal. If the specified
- arguments are not equal, returns the value of <varname>expr1</varname>. The data types of the expressions
- must be compatible, according to the conversion rules from <xref href="impala_datatypes.xml#datatypes"/>.
- You cannot use an expression that evaluates to <codeph>NULL</codeph> for <varname>expr1</varname>; that
- way, you can distinguish a return value of <codeph>NULL</codeph> from an argument value of
- <codeph>NULL</codeph>, which would never match <varname>expr2</varname>.
+ <b>Purpose:</b> Returns <codeph>NULL</codeph> if the two specified arguments are
+ equal. If the specified arguments are not equal, returns the value of
+ <varname>expr1</varname>. The data types of the expressions must be compatible,
+ according to the conversion rules from <xref href="impala_datatypes.xml#datatypes"/>.
+ You cannot use an expression that evaluates to <codeph>NULL</codeph> for
+ <varname>expr1</varname>; that way, you can distinguish a return value of
+ <codeph>NULL</codeph> from an argument value of <codeph>NULL</codeph>, which would
+ never match <varname>expr2</varname>.
<p>
- <b>Usage notes:</b> This function is effectively shorthand for a <codeph>CASE</codeph> expression of
- the form:
+ <b>Usage notes:</b> This function is effectively shorthand for a
+ <codeph>CASE</codeph> expression of the form:
</p>
<codeblock>CASE
WHEN <varname>expr1</varname> = <varname>expr2</varname> THEN NULL
ELSE <varname>expr1</varname>
END</codeblock>
<p>
- It is commonly used in division expressions, to produce a <codeph>NULL</codeph> result instead of a
- divide-by-zero error when the divisor is equal to zero:
+ It is commonly used in division expressions, to produce a <codeph>NULL</codeph>
+ result instead of a divide-by-zero error when the divisor is equal to zero:
</p>
<codeblock>select 1.0 / nullif(c1,0) as reciprocal from t1;</codeblock>
<p>
- You might also use it for compatibility with other database systems that support the same
- <codeph>NULLIF()</codeph> function.
+ You might also use it for compatibility with other database systems that support the
+ same <codeph>NULLIF()</codeph> function.
</p>
+
<p conref="../shared/impala_common.xml#common/return_same_type"/>
+
<p conref="../shared/impala_common.xml#common/added_in_130"/>
</dd>
@@ -388,21 +521,22 @@ END</codeblock>
<dlentry rev="1.3.0" id="nullifzero">
<dt>
- <codeph>nullifzero(<varname>numeric_expr</varname>)</codeph>
+ NULLIFZERO(type numeric_expr)
</dt>
- <dd><b>Purpose:</b> Returns <codeph>NULL</codeph> if the numeric
- expression evaluates to 0, otherwise returns the result of the
- expression.
+ <dd>
+ <b>Purpose:</b> Returns <codeph>NULL</codeph> if the numeric expression evaluates to
+ 0, otherwise returns the result of the expression.
<p>
- <b>Usage notes:</b> Used to avoid error conditions such as
- divide-by-zero in numeric calculations. Serves as shorthand for a
- more elaborate <codeph>CASE</codeph> expression, to simplify porting
- SQL with vendor extensions to Impala.
+ <b>Usage notes:</b> Used to avoid error conditions such as divide-by-zero in numeric
+ calculations. Serves as shorthand for a more elaborate <codeph>CASE</codeph>
+ expression, to simplify porting SQL with vendor extensions to Impala.
</p>
- <p><b>Return type:</b>
- Same type as the input argument
+
+ <p>
+ <b>Return type:</b> Same type as the input argument
</p>
+
<p conref="../shared/impala_common.xml#common/added_in_130"/>
</dd>
@@ -411,16 +545,17 @@ END</codeblock>
<dlentry id="nullvalue" rev="2.2.0">
<dt>
- <codeph>nullvalue(<varname>expression</varname>)</codeph>
+ NULLVALUE(type expression)
</dt>
<dd>
- <indexterm audience="hidden">function</indexterm>
<b>Purpose:</b> Tests if an expression (of any type) is <codeph>NULL</codeph> or not.
- Returns <codeph>true</codeph> if so.
- The converse of <codeph>nonnullvalue()</codeph>.
- <p conref="../shared/impala_common.xml#common/return_type_boolean"/>
+ Returns <codeph>true</codeph> if so. The converse of <codeph>NONNULLVALUE()</codeph>.
+ <p
+ conref="../shared/impala_common.xml#common/return_type_boolean"/>
+
<p conref="../shared/impala_common.xml#common/for_compatibility_only"/>
+
<p conref="../shared/impala_common.xml#common/added_in_220"/>
</dd>
@@ -429,18 +564,19 @@ END</codeblock>
<dlentry id="nvl" rev="1.1">
<dt>
- <codeph>nvl(type a, type ifNull)</codeph>
+ NVL(type a, type ifNull)
</dt>
<dd>
- <indexterm audience="hidden">nvl() function</indexterm>
- <b>Purpose:</b> Alias for the <codeph>isnull()</codeph> function. Tests if an expression is
- <codeph>NULL</codeph>, and returns the expression result value if not. If the first argument is
- <codeph>NULL</codeph>, returns the second argument. Equivalent to the <codeph>nvl()</codeph> function
- from Oracle Database or <codeph>ifnull()</codeph> from MySQL.
+ <b>Purpose:</b> Alias for the <codeph>ISNULL()</codeph> function. Tests if an
+ expression is <codeph>NULL</codeph>, and returns the expression result value if not.
+ If the first argument is <codeph>NULL</codeph>, returns the second argument.
+ Equivalent to the <codeph>NVL()</codeph> function from Oracle Database or
+ <codeph>IFNULL()</codeph> from MySQL.
<p>
<b>Return type:</b> Same as the first argument value
</p>
+
<p conref="../shared/impala_common.xml#common/added_in_11"/>
</dd>
@@ -449,27 +585,29 @@ END</codeblock>
<dlentry id="nvl2" rev="2.9.0 IMPALA-5030">
<dt>
- <codeph>nvl2(type a, type ifNull, type ifNotNull)</codeph>
+ NVL2(type a, type ifNull, type ifNotNull)
</dt>
<dd>
- <indexterm audience="hidden">nvl2() function</indexterm>
- <b>Purpose:</b> Enhanced variant of the <codeph>nvl()</codeph> function. Tests an expression
- and returns different result values depending on whether it is <codeph>NULL</codeph> or not.
- If the first argument is <codeph>NULL</codeph>, returns the second argument.
- If the first argument is not <codeph>NULL</codeph>, returns the third argument.
- Equivalent to the <codeph>nvl2()</codeph> function from Oracle Database.
+ <b>Purpose:</b> Enhanced variant of the <codeph>NVL()</codeph> function. Tests an
+ expression and returns different result values depending on whether it is
+ <codeph>NULL</codeph> or not. If the first argument is <codeph>NULL</codeph>, returns
+ the second argument. If the first argument is not <codeph>NULL</codeph>, returns the
+ third argument. Equivalent to the <codeph>NVL2()</codeph> function from Oracle
+ Database.
<p>
<b>Return type:</b> Same as the first argument value
</p>
+
<p conref="../shared/impala_common.xml#common/added_in_290"/>
+
<p conref="../shared/impala_common.xml#common/example_blurb"/>
+
<p>
- The following examples show how a query can use special indicator values
- to represent null and not-null expression values. The first example tests
- an <codeph>INT</codeph> column and so uses special integer values.
- The second example tests a <codeph>STRING</codeph> column and so uses
- special string values.
+ The following examples show how a query can use special indicator values to
+ represent null and not-null expression values. The first example tests an
+ <codeph>INT</codeph> column and so uses special integer values. The second example
+ tests a <codeph>STRING</codeph> column and so uses special string values.
</p>
<codeblock>
select x, nvl2(x, 999, 0) from nvl2_demo;
@@ -499,22 +637,29 @@ select s, nvl2(s, 'is not null', 'is null') from nvl2_demo;
<dlentry rev="1.3.0" id="zeroifnull">
<dt>
- <codeph>zeroifnull(<varname>numeric_expr</varname>)</codeph>
+ ZEROIFNULL(type numeric_expr)
</dt>
- <dd><b>Purpose:</b> Returns 0 if the numeric expression evaluates to
- <codeph>NULL</codeph>, otherwise returns the result of the
- expression. <p>
- <b>Usage notes:</b> Used to avoid unexpected results due to
- unexpected propagation of <codeph>NULL</codeph> values in numeric
- calculations. Serves as shorthand for a more elaborate
- <codeph>CASE</codeph> expression, to simplify porting SQL with
- vendor extensions to Impala. </p>
- <p><b>Return type:</b> Same type as the input argument </p>
+ <dd>
+ <b>Purpose:</b> Returns 0 if the numeric expression evaluates to
+ <codeph>NULL</codeph>, otherwise returns the result of the expression.
+ <p>
+ <b>Usage notes:</b> Used to avoid unexpected results due to unexpected propagation
+ of <codeph>NULL</codeph> values in numeric calculations. Serves as shorthand for a
+ more elaborate <codeph>CASE</codeph> expression, to simplify porting SQL with vendor
+ extensions to Impala.
+ </p>
+
+ <p>
+ <b>Return type:</b> Same type as the input argument
+ </p>
+
<p conref="../shared/impala_common.xml#common/added_in_130"/>
</dd>
</dlentry>
</dl>
+
</conbody>
+
</concept>