[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 <impala-public-jenk...@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 <arod...@cloudera.com> Authored: Mon Oct 8 15:26:32 2018 -0700 Committer: Alex Rodoni <arod...@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>
