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:08 UTC
[03/10] impala git commit: [DOCS] Built-in Functions doc format
Changes
http://git-wip-us.apache.org/repos/asf/impala/blob/e8ee827a/docs/topics/impala_math_functions.xml
----------------------------------------------------------------------
diff --git a/docs/topics/impala_math_functions.xml b/docs/topics/impala_math_functions.xml
index b4f97c5..e4eb224 100644
--- a/docs/topics/impala_math_functions.xml
+++ b/docs/topics/impala_math_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="math_functions">
<title>Impala Mathematical Functions</title>
- <titlealts audience="PDF"><navtitle>Mathematical Functions</navtitle></titlealts>
+
+ <titlealts audience="PDF">
+
+ <navtitle>Mathematical Functions</navtitle>
+
+ </titlealts>
+
<prolog>
<metadata>
<data name="Category" value="Impala"/>
@@ -35,29 +42,32 @@ under the License.
<conbody>
<p>
- Mathematical functions, or arithmetic functions, perform numeric calculations that are typically more complex
- than basic addition, subtraction, multiplication, and division. For example, these functions include
- trigonometric, logarithmic, and base conversion operations.
+ Mathematical functions, or arithmetic functions, perform numeric calculations that are
+ typically more complex than basic addition, subtraction, multiplication, and division. For
+ example, these functions include trigonometric, logarithmic, and base conversion
+ operations.
</p>
<note>
- In Impala, exponentiation uses the <codeph>pow()</codeph> function rather than an exponentiation operator
- such as <codeph>**</codeph>.
+ In Impala, exponentiation uses the <codeph>pow()</codeph> function rather than an
+ exponentiation operator such as <codeph>**</codeph>.
</note>
<p conref="../shared/impala_common.xml#common/related_info"/>
<p>
- The mathematical functions operate mainly on these data types: <xref href="impala_int.xml#int"/>,
- <xref href="impala_bigint.xml#bigint"/>, <xref href="impala_smallint.xml#smallint"/>,
- <xref href="impala_tinyint.xml#tinyint"/>, <xref href="impala_double.xml#double"/>,
- <xref href="impala_float.xml#float"/>, and <xref href="impala_decimal.xml#decimal"/>. For the operators that
- perform the standard operations such as addition, subtraction, multiplication, and division, see
+ The mathematical functions operate mainly on these data types:
+ <xref href="impala_int.xml#int"/>, <xref href="impala_bigint.xml#bigint"/>,
+ <xref href="impala_smallint.xml#smallint"/>, <xref href="impala_tinyint.xml#tinyint"/>,
+ <xref href="impala_double.xml#double"/>, <xref href="impala_float.xml#float"/>, and
+ <xref href="impala_decimal.xml#decimal"/>. For the operators that perform the standard
+ operations such as addition, subtraction, multiplication, and division, see
<xref href="impala_operators.xml#arithmetic_operators"/>.
</p>
<p>
- Functions that perform bitwise operations are explained in <xref href="impala_bit_functions.xml#bit_functions"/>.
+ Functions that perform bitwise operations are explained in
+ <xref href="impala_bit_functions.xml#bit_functions"/>.
</p>
<p>
@@ -68,22 +78,231 @@ under the License.
Impala supports the following mathematical functions:
</p>
+ <ul>
+ <li>
+ <xref href="#math_functions/abs">ABS</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/acos">ACOS</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/asin">ASIN</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/atan">ATAN</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/atan2">ATAN2</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/bin">BIN</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/ceil">CEIL, CEILING, DCEIL</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/conv">CONV</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/cos">COS</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/cosh">COSH</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/cot">COT</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/degrees">DEGREES</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/e">E</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/exp">EXP</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/factorial">FACTORIAL</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/floor">FLOOR, DFLOOR</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/fmod">FMOD</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/fnv_hash">FNV_HASH</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/greatest">GREATEST</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/hex">HEX</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/is_inf">IS_INF</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/is_nan">IS_NAN</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/least">LEAST</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/ln">LN</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/log">LOG</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/log10">LOG10</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/log2">LOG2</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/max_int">MAX_INT, MAX_TINYINT, MAX_SMALLINT,
+ MAX_BIGINT</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/min_int">MIN_INT, MIN_TINYINT, MIN_SMALLINT,
+ MIN_BIGINT</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/mod">MOD</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/murmur_hash">MURMUR_HASH</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/negative">NEGATIVE</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/pi">PI</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/pmod">PMOD</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/positive">POSITIVE</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/pow">POW, POWER, DPOW, FPOW</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/precision">PRECISION</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/quotient">QUOTIENT</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/radians">RADIANS</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/rand">RAND, RANDOM</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/round">ROUND, DROUND</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/scale">SCALE</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/sign">SIGN</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/sin">SIN</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/sinh">SINH</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/sqrt">SQRT</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/tan">TAN</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/tanh">TANH</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/truncate">TRUNCATE, DTRUNC, TRUNC</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/unhex">UNHEX</xref>
+ </li>
+
+ <li>
+ <xref href="#math_functions/width_bucket">WIDTH_BUCKET</xref>
+ </li>
+ </ul>
+
<dl>
<dlentry rev="1.4.0" id="abs">
<dt rev="1.4.0 2.0.1">
- <codeph>abs(numeric_type a)</codeph>
-<!-- <codeph>abs(double a), abs(decimal(p,s) a)</codeph> -->
+ ABS(numeric_type a)
</dt>
<dd rev="1.4.0">
- <indexterm audience="hidden">abs() function</indexterm>
<b>Purpose:</b> Returns the absolute value of the argument.
- <p rev="2.0.1" conref="../shared/impala_common.xml#common/return_type_same"/>
+ <p
+ rev="2.0.1"
+ conref="../shared/impala_common.xml#common/return_type_same"/>
+
<p>
- <b>Usage notes:</b> Use this function to ensure all return values are positive. This is different than
- the <codeph>positive()</codeph> function, which returns its argument unchanged (even if the argument
- was negative).
+ <b>Usage notes:</b> Use this function to ensure all return values are positive. This
+ is different than the <codeph>POSITIVE()</codeph> function, which returns its
+ argument unchanged (even if the argument was negative).
</p>
</dd>
@@ -92,14 +311,13 @@ under the License.
<dlentry id="acos">
<dt>
- <codeph>acos(double a)</codeph>
+ ACOS(DOUBLE a)
</dt>
<dd>
- <indexterm audience="hidden">acos() function</indexterm>
<b>Purpose:</b> Returns the arccosine of the argument.
<p>
- <b>Return type:</b> <codeph>double</codeph>
+ <b>Return type:</b> <codeph>DOUBLE</codeph>
</p>
</dd>
@@ -108,14 +326,13 @@ under the License.
<dlentry id="asin">
<dt>
- <codeph>asin(double a)</codeph>
+ ASIN(DOUBLE a)
</dt>
<dd>
- <indexterm audience="hidden">asin() function</indexterm>
<b>Purpose:</b> Returns the arcsine of the argument.
<p>
- <b>Return type:</b> <codeph>double</codeph>
+ <b>Return type:</b> <codeph>DOUBLE</codeph>
</p>
</dd>
@@ -124,14 +341,13 @@ under the License.
<dlentry id="atan">
<dt>
- <codeph>atan(double a)</codeph>
+ ATAN(DOUBLE a)
</dt>
<dd>
- <indexterm audience="hidden">atan() function</indexterm>
<b>Purpose:</b> Returns the arctangent of the argument.
<p>
- <b>Return type:</b> <codeph>double</codeph>
+ <b>Return type:</b> <codeph>DOUBLE</codeph>
</p>
</dd>
@@ -140,15 +356,14 @@ under the License.
<dlentry id="atan2" rev="2.3.0 IMPALA-1771">
<dt rev="2.3.0 IMPALA-1771">
- <codeph>atan2(double a, double b)</codeph>
+ ATAN2(DOUBLE a, DOUBLE b)
</dt>
<dd rev="2.3.0 IMPALA-1771">
- <indexterm audience="hidden">atan2() function</indexterm>
- <b>Purpose:</b> Returns the arctangent of the two arguments, with the signs of the arguments used to determine the
- quadrant of the result.
+ <b>Purpose:</b> Returns the arctangent of the two arguments, with the signs of the
+ arguments used to determine the quadrant of the result.
<p>
- <b>Return type:</b> <codeph>double</codeph>
+ <b>Return type:</b> <codeph>DOUBLE</codeph>
</p>
</dd>
@@ -157,15 +372,14 @@ under the License.
<dlentry id="bin">
<dt>
- <codeph>bin(bigint a)</codeph>
+ BIN(BIGINT a)
</dt>
<dd>
- <indexterm audience="hidden">bin() function</indexterm>
- <b>Purpose:</b> Returns the binary representation of an integer value, that is, a string of 0 and 1
- digits.
+ <b>Purpose:</b> Returns the binary representation of an integer value, that is, a
+ string of 0 and 1 digits.
<p>
- <b>Return type:</b> <codeph>string</codeph>
+ <b>Return type:</b> <codeph>STRING</codeph>
</p>
</dd>
@@ -174,17 +388,13 @@ under the License.
<dlentry rev="1.4.0" id="ceil">
<dt rev="1.4.0">
- <codeph>ceil(double a)</codeph>,
- <codeph>ceil(decimal(p,s) a)</codeph>,
- <codeph id="ceiling">ceiling(double a)</codeph>,
- <codeph>ceiling(decimal(p,s) a)</codeph>,
- <codeph id="dceil" rev="2.3.0">dceil(double a)</codeph>,
- <codeph rev="2.3.0">dceil(decimal(p,s) a)</codeph>
+ CEIL(DOUBLE a), CEIL(DECIMAL(p,s) a), CEILING(DOUBLE a), CEILING(DECIMAL(p,s) a),
+ DCEIL(DOUBLE a), DCEIL(DECIMAL(p,s) a)
</dt>
<dd rev="1.4.0">
- <indexterm audience="hidden">ceil() function</indexterm>
- <b>Purpose:</b> Returns the smallest integer that is greater than or equal to the argument.
+ <b>Purpose:</b> Returns the smallest integer that is greater than or equal to the
+ argument.
<p>
<b>Return type:</b> Same as the input value
</p>
@@ -195,72 +405,104 @@ under the License.
<dlentry id="conv">
<dt>
- <codeph>conv(bigint n, int from_base, int to_base), conv(string s, int
- from_base, int to_base)</codeph>
+ CONV(BIGINT n, INT from_base, INT to_base), CONV(STRING s, INT from_base, INT to_base)
</dt>
<dd>
- <b>Purpose:</b> Returns a string representation of the first argument
- converted from <codeph>from_base</codeph> to <codeph>to_base</codeph>.
- The first argument can be specified as a number or a string. For
- example, <codeph>conv(100, 2, 10)</codeph> and <codeph>conv('100', 2,
- 10)</codeph> both return <codeph>'4'</codeph>. <p>
- <b>Return type:</b>
- <codeph>string</codeph>
+ <b>Purpose:</b> Returns a string representation of the first argument converted from
+ <codeph>from_base</codeph> to <codeph>to_base</codeph>. The first argument can be
+ specified as a number or a string. For example, <codeph>CONV(100, 2, 10)</codeph> and
+ <codeph>CONV('100', 2, 10)</codeph> both return <codeph>'4'</codeph>.
+ <p>
+ <b>Return type:</b> <codeph>STRING</codeph>
</p>
+
<p>
<b>Usage notes:</b>
</p>
- <p> If <codeph>to_base</codeph> is negative, the first argument is
- treated as signed, and otherwise, it is treated as unsigned. For
- example: </p>
+
+ <p>
+ If <codeph>to_base</codeph> is negative, the first argument is treated as signed,
+ and otherwise, it is treated as unsigned. For example:
+ </p>
<ul>
<li>
- <codeph>conv(-17, 10, -2) </codeph>returns
- <codeph>'-10001'</codeph>,<codeph> -17</codeph> in base 2. </li>
+ <codeph>conv(-17, 10, -2) </codeph>returns <codeph>'-10001'</codeph>,<codeph>
+ -17</codeph> in base 2.
+ </li>
+
<li>
<codeph>conv(-17, 10, 10)</codeph> returns
- <codeph>'18446744073709551599'</codeph>. <codeph>-17</codeph> is
- interpreted as an unsigned, 2^64-17, and then the value is
- returned in base 10.</li>
- </ul><p>The function returns <codeph>NULL</codeph> when the following
- illegal arguments are specified: </p>
+ <codeph>'18446744073709551599'</codeph>. <codeph>-17</codeph> is interpreted as an
+ unsigned, 2^64-17, and then the value is returned in base 10.
+ </li>
+ </ul>
+ <p>
+ The function returns <codeph>NULL</codeph> when the following illegal arguments are
+ specified:
+ </p>
<ul>
- <li> Any argument is <codeph>NULL</codeph>. </li>
+ <li>
+ Any argument is <codeph>NULL</codeph>.
+ </li>
+
<li>
<codeph>from_base</codeph> or <codeph>to_base</codeph> is below
- <codeph>-36</codeph> or above <codeph>36</codeph>. </li>
+ <codeph>-36</codeph> or above <codeph>36</codeph>.
+ </li>
+
<li>
- <codeph>from_base</codeph> or <codeph>to_base</codeph> is
- <codeph>-1</codeph>, <codeph>0</codeph>, or <codeph>1</codeph>. </li>
- <li> The first argument represents a positive number and
- <codeph>from_base</codeph> is a negative number.</li>
+ <codeph>from_base</codeph> or <codeph>to_base</codeph> is <codeph>-1</codeph>,
+ <codeph>0</codeph>, or <codeph>1</codeph>.
+ </li>
+
+ <li>
+ The first argument represents a positive number and <codeph>from_base</codeph> is
+ a negative number.
+ </li>
</ul>
- <p>If the first argument represents a negative number and
- <codeph>from_base</codeph> is a negative number, the function
- returns <codeph>0</codeph>.</p><p> If the first argument represents
- a number larger than the maximum <codeph>bigint</codeph>, the
- function returns: </p>
+ <p>
+ If the first argument represents a negative number and <codeph>from_base</codeph> is
+ a negative number, the function returns <codeph>0</codeph>.
+ </p>
+
+ <p>
+ If the first argument represents a number larger than the maximum
+ <codeph>bigint</codeph>, the function returns:
+ </p>
<ul>
- <li> The string representation of -1 in <codeph>to_base</codeph> if
- <codeph>to_base</codeph> is negative. </li>
- <li> The string representation of 18446744073709551615' (2^64 - 1)
- in <codeph>to_base</codeph> if <codeph>to_base</codeph> is
- positive.</li>
+ <li>
+ The string representation of -1 in <codeph>to_base</codeph> if
+ <codeph>to_base</codeph> is negative.
+ </li>
+
+ <li>
+ The string representation of 18446744073709551615' (2^64 - 1) in
+ <codeph>to_base</codeph> if <codeph>to_base</codeph> is positive.
+ </li>
</ul>
- <p> If the first argument does not represent a valid number in
- <codeph>from_base</codeph>, e.g. 3 in base 2 or '1a23' in base 10,
- the digits in the first argument are evaluated from left-to-right
- and used if a valid digit in <codeph>from_base</codeph>. The invalid
- digit and the digits to the right are ignored. </p>
- <p> For example:<ul>
- <li><codeph> conv(445, 5, 10)</codeph> is converted to
- <codeph>conv(44, 5, 10)</codeph> and returns
- <codeph>'24'</codeph>. </li>
- <li><codeph> conv('1a23', 10, 16)</codeph> is converted to
- <codeph>conv('1', 10 , 16)</codeph> and returns
- <codeph>'1'</codeph>. </li>
- </ul></p>
+ <p>
+ If the first argument does not represent a valid number in
+ <codeph>from_base</codeph>, e.g. 3 in base 2 or '1a23' in base 10, the digits in the
+ first argument are evaluated from left-to-right and used if a valid digit in
+ <codeph>from_base</codeph>. The invalid digit and the digits to the right are
+ ignored.
+ </p>
+
+ <p>
+ For example:
+ <ul>
+ <li>
+ <codeph> conv(445, 5, 10)</codeph> is converted to <codeph>conv(44, 5,
+ 10)</codeph> and returns <codeph>'24'</codeph>.
+ </li>
+
+ <li>
+ <codeph> conv('1a23', 10, 16)</codeph> is converted to <codeph>conv('1', 10 ,
+ 16)</codeph> and returns <codeph>'1'</codeph>.
+ </li>
+ </ul>
+ </p>
</dd>
</dlentry>
@@ -268,14 +510,13 @@ under the License.
<dlentry id="cos">
<dt>
- <codeph>cos(double a)</codeph>
+ COS(DOUBLE a)
</dt>
<dd>
- <indexterm audience="hidden">cos() function</indexterm>
<b>Purpose:</b> Returns the cosine of the argument.
<p>
- <b>Return type:</b> <codeph>double</codeph>
+ <b>Return type:</b> <codeph>DOUBLE</codeph>
</p>
</dd>
@@ -284,14 +525,13 @@ under the License.
<dlentry id="cosh" rev="2.3.0 IMPALA-1771">
<dt rev="2.3.0 IMPALA-1771">
- <codeph>cosh(double a)</codeph>
+ COSH(DOUBLE a)
</dt>
<dd rev="2.3.0 IMPALA-1771">
- <indexterm audience="hidden">cosh() function</indexterm>
<b>Purpose:</b> Returns the hyperbolic cosine of the argument.
<p>
- <b>Return type:</b> <codeph>double</codeph>
+ <b>Return type:</b> <codeph>DOUBLE</codeph>
</p>
</dd>
@@ -300,15 +540,15 @@ under the License.
<dlentry id="cot" rev="2.3.0 IMPALA-1771">
<dt rev="2.3.0 IMPALA-1771">
- <codeph>cot(double a)</codeph>
+ COT(DOUBLE a)
</dt>
<dd rev="2.3.0 IMPALA-1771">
- <indexterm audience="hidden">cot() function</indexterm>
<b>Purpose:</b> Returns the cotangent of the argument.
<p>
- <b>Return type:</b> <codeph>double</codeph>
+ <b>Return type:</b> <codeph>DOUBLE</codeph>
</p>
+
<p conref="../shared/impala_common.xml#common/added_in_230"/>
</dd>
@@ -317,14 +557,13 @@ under the License.
<dlentry id="degrees">
<dt>
- <codeph>degrees(double a)</codeph>
+ DEGREES(DOUBLE a)
</dt>
<dd>
- <indexterm audience="hidden">degrees() function</indexterm>
<b>Purpose:</b> Converts argument value from radians to degrees.
<p>
- <b>Return type:</b> <codeph>double</codeph>
+ <b>Return type:</b> <codeph>DOUBLE</codeph>
</p>
</dd>
@@ -333,16 +572,17 @@ under the License.
<dlentry id="e">
<dt>
- <codeph>e()</codeph>
+ E()
</dt>
<dd>
- <indexterm audience="hidden">e() function</indexterm>
<b>Purpose:</b> Returns the
- <xref href="https://en.wikipedia.org/wiki/E_(mathematical_constant" scope="external" format="html">mathematical
+ <xref
+ href="https://en.wikipedia.org/wiki/E_(mathematical_constant"
+ scope="external" format="html">mathematical
constant e</xref>.
<p>
- <b>Return type:</b> <codeph>double</codeph>
+ <b>Return type:</b> <codeph>DOUBLE</codeph>
</p>
</dd>
@@ -351,17 +591,17 @@ under the License.
<dlentry id="exp">
<dt>
- <codeph>exp(double a)</codeph>,
- <codeph rev="2.3.0" id="dexp">dexp(double a)</codeph>
+ EXP(DOUBLE a), DEXP(DOUBLE a)
</dt>
<dd>
- <indexterm audience="hidden">exp() function</indexterm>
<b>Purpose:</b> Returns the
- <xref href="https://en.wikipedia.org/wiki/E_(mathematical_constant" scope="external" format="html">mathematical
+ <xref
+ href="https://en.wikipedia.org/wiki/E_(mathematical_constant"
+ scope="external" format="html">mathematical
constant e</xref> raised to the power of the argument.
<p>
- <b>Return type:</b> <codeph>double</codeph>
+ <b>Return type:</b> <codeph>DOUBLE</codeph>
</p>
</dd>
@@ -370,22 +610,30 @@ under the License.
<dlentry rev="2.3.0" id="factorial">
<dt rev="2.3.0">
- <codeph>factorial(integer_type a)</codeph>
+ FACTORIAL(integer_type a)
</dt>
+
<dd rev="2.3.0">
- <indexterm audience="hidden">factorial() function</indexterm>
- <b>Purpose:</b> Computes the <xref href="https://en.wikipedia.org/wiki/Factorial" scope="external" format="html">factorial</xref> of an integer value.
- It works with any integer type.
- <p conref="../shared/impala_common.xml#common/added_in_230"/>
- <p>
- <b>Usage notes:</b> You can use either the <codeph>factorial()</codeph> function or the <codeph>!</codeph> operator.
- The factorial of 0 is 1. Likewise, the <codeph>factorial()</codeph> function returns 1 for any negative value.
- The maximum positive value for the input argument is 20; a value of 21 or greater overflows the
+ <b>Purpose:</b> Computes the
+ <xref
+ href="https://en.wikipedia.org/wiki/Factorial" scope="external"
+ format="html">factorial</xref>
+ of an integer value. It works with any integer type.
+ <p
+ conref="../shared/impala_common.xml#common/added_in_230"/>
+
+ <p>
+ <b>Usage notes:</b> You can use either the <codeph>factorial()</codeph> function or
+ the <codeph>!</codeph> operator. The factorial of 0 is 1. Likewise, the
+ <codeph>factorial()</codeph> function returns 1 for any negative value. The maximum
+ positive value for the input argument is 20; a value of 21 or greater overflows the
range for a <codeph>BIGINT</codeph> and causes an error.
</p>
+
<p>
- <b>Return type:</b> <codeph>bigint</codeph>
+ <b>Return type:</b> <codeph>BIGINT</codeph>
</p>
+
<p conref="../shared/impala_common.xml#common/added_in_230"/>
<codeblock>select factorial(5);
+--------------+
@@ -422,15 +670,12 @@ select factorial(-100);
<dlentry id="floor">
<dt>
- <codeph>floor(double a)</codeph>,
- <codeph>floor(decimal(p,s) a)</codeph>,
- <codeph rev="2.3.0" id="dfloor">dfloor(double a)</codeph>,
- <codeph rev="2.3.0">dfloor(decimal(p,s) a)</codeph>
+ FLOOR(DOUBLE a), FLOOR(DECIMAL(p,s) a), DFLOOR(DOUBLE a), DFLOOR(DECIMAL(p,s) a)
</dt>
<dd>
- <indexterm audience="hidden">floor() function</indexterm>
- <b>Purpose:</b> Returns the largest integer that is less than or equal to the argument.
+ <b>Purpose:</b> Returns the largest integer that is less than or equal to the
+ argument.
<p>
<b>Return type:</b> Same as the input type
</p>
@@ -441,28 +686,36 @@ select factorial(-100);
<dlentry id="fmod">
<dt>
- <codeph>fmod(double a, double b), fmod(float a, float b)</codeph>
+ FMOD(DOUBLE a, DOUBLE b), FMOD(FLOAT a, FLOAT b)
</dt>
<dd>
- <indexterm audience="hidden">fmod() function</indexterm>
- <b>Purpose:</b> Returns the modulus of a floating-point number.<p>
- <b>Return type:</b>
- <codeph>float</codeph> or <codeph>double</codeph>, depending on type
- of arguments </p>
+ <b>Purpose:</b> Returns the modulus of a floating-point number.
+ <p>
+ <b>Return type:</b> <codeph>FLOAT</codeph> or <codeph>DOUBLE</codeph>, depending on
+ type of arguments
+ </p>
+
<p conref="../shared/impala_common.xml#common/added_in_111"/>
+
<p conref="../shared/impala_common.xml#common/usage_notes_blurb"/>
- <p> Because this function operates on <codeph>DOUBLE</codeph> or
- <codeph>FLOAT</codeph> values, it is subject to potential rounding
- errors for values that cannot be represented precisely. Prefer to
- use whole numbers, or values that you know can be represented
- precisely by the <codeph>DOUBLE</codeph> or <codeph>FLOAT</codeph>
- types. </p>
+
+ <p>
+ Because this function operates on <codeph>DOUBLE</codeph> or <codeph>FLOAT</codeph>
+ values, it is subject to potential rounding errors for values that cannot be
+ represented precisely. Prefer to use whole numbers, or values that you know can be
+ represented precisely by the <codeph>DOUBLE</codeph> or <codeph>FLOAT</codeph>
+ types.
+ </p>
+
<p conref="../shared/impala_common.xml#common/example_blurb"/>
- <p> The following examples show equivalent operations with the
- <codeph>fmod()</codeph> function and the <codeph>%</codeph>
- arithmetic operator, for values not subject to any rounding error. </p>
- <codeblock>select fmod(10,3);
+
+ <p>
+ The following examples show equivalent operations with the <codeph>fmod()</codeph>
+ function and the <codeph>%</codeph> arithmetic operator, for values not subject to
+ any rounding error.
+ </p>
+<codeblock>select fmod(10,3);
+-------------+
| fmod(10, 3) |
+-------------+
@@ -490,16 +743,16 @@ select 5.5 % 2;
| 1.5 |
+---------+
</codeblock>
- <p> The following examples show operations with the
- <codeph>fmod()</codeph> function for values that cannot be
- represented precisely by the <codeph>DOUBLE</codeph> or
- <codeph>FLOAT</codeph> types, and thus are subject to rounding
- error. <codeph>fmod(9.9,3.0)</codeph> returns a value slightly
- different than the expected 0.9 because of rounding.
- <codeph>fmod(9.9,3.3)</codeph> returns a value quite different
- from the expected value of 0 because of rounding error during
- intermediate calculations. </p>
- <codeblock>select fmod(9.9,3.0);
+ <p>
+ The following examples show operations with the <codeph>fmod()</codeph> function for
+ values that cannot be represented precisely by the <codeph>DOUBLE</codeph> or
+ <codeph>FLOAT</codeph> types, and thus are subject to rounding error.
+ <codeph>fmod(9.9,3.0)</codeph> returns a value slightly different than the expected
+ 0.9 because of rounding. <codeph>fmod(9.9,3.3)</codeph> returns a value quite
+ different from the expected value of 0 because of rounding error during intermediate
+ calculations.
+ </p>
+<codeblock>select fmod(9.9,3.0);
+--------------------+
| fmod(9.9, 3.0) |
+--------------------+
@@ -520,43 +773,53 @@ select fmod(9.9,3.3);
<dlentry rev="1.2.2" id="fnv_hash">
<dt rev="1.2.2">
- <codeph>fnv_hash(type v)</codeph>,
+ FNV_HASH(type v),
</dt>
<dd rev="1.2.2">
- <indexterm audience="hidden">fnv_hash() function</indexterm>
- <b>Purpose:</b> Returns a consistent 64-bit value derived from the input argument, for convenience of
- implementing hashing logic in an application.
+ <b>Purpose:</b> Returns a consistent 64-bit value derived from the input argument, for
+ convenience of implementing hashing logic in an application.
<p>
<b>Return type:</b> <codeph>BIGINT</codeph>
</p>
+
<p conref="../shared/impala_common.xml#common/usage_notes_blurb"/>
+
<p>
- You might use the return value in an application where you perform load balancing, bucketing, or some
- other technique to divide processing or storage.
+ You might use the return value in an application where you perform load balancing,
+ bucketing, or some other technique to divide processing or storage.
</p>
+
<p>
- Because the result can be any 64-bit value, to restrict the value to a particular range, you can use an
- expression that includes the <codeph>ABS()</codeph> function and the <codeph>%</codeph> (modulo)
- operator. For example, to produce a hash value in the range 0-9, you could use the expression
- <codeph>ABS(FNV_HASH(x)) % 10</codeph>.
+ Because the result can be any 64-bit value, to restrict the value to a particular
+ range, you can use an expression that includes the <codeph>ABS()</codeph> function
+ and the <codeph>%</codeph> (modulo) operator. For example, to produce a hash value
+ in the range 0-9, you could use the expression <codeph>ABS(FNV_HASH(x)) %
+ 10</codeph>.
</p>
+
<p>
- This function implements the same algorithm that Impala uses internally for hashing, on systems where
- the CRC32 instructions are not available.
+ This function implements the same algorithm that Impala uses internally for hashing,
+ on systems where the CRC32 instructions are not available.
</p>
+
<p>
This function implements the
- <xref href="http://en.wikipedia.org/wiki/Fowler%E2%80%93Noll%E2%80%93Vo_hash_function" scope="external" format="html">Fowler–Noll–Vo
- hash function</xref>, in particular the FNV-1a variation. This is not a perfect hash function: some
- combinations of values could produce the same result value. It is not suitable for cryptographic use.
+ <xref
+ href="http://en.wikipedia.org/wiki/Fowler%E2%80%93Noll%E2%80%93Vo_hash_function"
+ scope="external" format="html">Fowler–Noll–Vo
+ hash function</xref>, in particular the FNV-1a variation. This is not a perfect hash
+ function: some combinations of values could produce the same result value. It is not
+ suitable for cryptographic use.
</p>
+
<p>
- Similar input values of different types could produce different hash values, for example the same
- numeric value represented as <codeph>SMALLINT</codeph> or <codeph>BIGINT</codeph>,
- <codeph>FLOAT</codeph> or <codeph>DOUBLE</codeph>, or <codeph>DECIMAL(5,2)</codeph> or
- <codeph>DECIMAL(20,5)</codeph>.
+ Similar input values of different types could produce different hash values, for
+ example the same numeric value represented as <codeph>SMALLINT</codeph> or
+ <codeph>BIGINT</codeph>, <codeph>FLOAT</codeph> or <codeph>DOUBLE</codeph>, or
+ <codeph>DECIMAL(5,2)</codeph> or <codeph>DECIMAL(20,5)</codeph>.
</p>
+
<p conref="../shared/impala_common.xml#common/example_blurb"/>
<codeblock>[localhost:21000] > create table h (x int, s string);
[localhost:21000] > insert into h values (0, 'hello'), (1,'world'), (1234567890,'antidisestablishmentarianism');
@@ -585,7 +848,8 @@ select fmod(9.9,3.3);
| antidisestablishmentarianism | 4 |
+------------------------------+-------------------------+</codeblock>
<p>
- For short argument values, the high-order bits of the result have relatively low entropy:
+ For short argument values, the high-order bits of the result have relatively low
+ entropy:
</p>
<codeblock>[localhost:21000] > create table b (x boolean);
[localhost:21000] > insert into b values (true), (true), (false), (false);
@@ -608,13 +872,12 @@ select fmod(9.9,3.3);
<dlentry rev="1.4.0" id="greatest">
<dt rev="1.4.0">
- <codeph>greatest(bigint a[, bigint b ...])</codeph>, <codeph>greatest(double a[, double b ...])</codeph>,
- <codeph>greatest(decimal(p,s) a[, decimal(p,s) b ...])</codeph>, <codeph>greatest(string a[, string b
- ...])</codeph>, <codeph>greatest(timestamp a[, timestamp b ...])</codeph>
+ GREATEST(BIGINT a[, BIGINT b ...]), GREATEST(DOUBLE a[, DOUBLE b ...]),
+ GREATEST(DECIMAL(p,s) a[, DECIMAL(p,s) b ...]), GREATEST(STRING a[, STRING b ...]),
+ GREATEST(TIMESTAMP a[, TIMESTAMP b ...])
</dt>
<dd rev="1.4.0">
- <indexterm audience="hidden">greatest() function</indexterm>
<b>Purpose:</b> Returns the largest value from a list of expressions.
<p conref="../shared/impala_common.xml#common/return_same_type"/>
</dd>
@@ -624,15 +887,14 @@ select fmod(9.9,3.3);
<dlentry id="hex">
<dt>
- <codeph>hex(bigint a), hex(string a)</codeph>
+ HEX(BIGINT a), HEX(STRING a)
</dt>
<dd>
- <indexterm audience="hidden">hex() function</indexterm>
- <b>Purpose:</b> Returns the hexadecimal representation of an integer value, or of the characters in a
- string.
+ <b>Purpose:</b> Returns the hexadecimal representation of an integer value, or of the
+ characters in a string.
<p>
- <b>Return type:</b> <codeph>string</codeph>
+ <b>Return type:</b> <codeph>STRING</codeph>
</p>
</dd>
@@ -641,16 +903,18 @@ select fmod(9.9,3.3);
<dlentry rev="1.4.0" id="is_inf">
<dt rev="1.4.0">
- <codeph>is_inf(double a)</codeph>,
+ IS_INF(DOUBLE a)
</dt>
<dd rev="1.4.0">
- <indexterm audience="hidden">is_inf() function</indexterm>
- <b>Purpose:</b> Tests whether a value is equal to the special value <q>inf</q>, signifying infinity.
+ <b>Purpose:</b> Tests whether a value is equal to the special value <q>inf</q>,
+ signifying infinity.
<p>
- <b>Return type:</b> <codeph>boolean</codeph>
+ <b>Return type:</b> <codeph>BOOLEAN</codeph>
</p>
+
<p conref="../shared/impala_common.xml#common/usage_notes_blurb"/>
+
<p conref="../shared/impala_common.xml#common/infinity_and_nan"/>
</dd>
@@ -659,17 +923,18 @@ select fmod(9.9,3.3);
<dlentry rev="1.4.0" id="is_nan">
<dt rev="1.4.0">
- <codeph>is_nan(double a)</codeph>,
+ IS_NAN(DOUBLE a)
</dt>
<dd rev="1.4.0">
- <indexterm audience="hidden">is_nan() function</indexterm>
- <b>Purpose:</b> Tests whether a value is equal to the special value <q>NaN</q>, signifying <q>not a
- number</q>.
+ <b>Purpose:</b> Tests whether a value is equal to the special value <q>NaN</q>,
+ signifying <q>not a number</q>.
<p>
- <b>Return type:</b> <codeph>boolean</codeph>
+ <b>Return type:</b> <codeph>BOOLEAN</codeph>
</p>
+
<p conref="../shared/impala_common.xml#common/usage_notes_blurb"/>
+
<p conref="../shared/impala_common.xml#common/infinity_and_nan"/>
</dd>
@@ -678,13 +943,12 @@ select fmod(9.9,3.3);
<dlentry rev="1.4.0" id="least">
<dt rev="1.4.0">
- <codeph>least(bigint a[, bigint b ...])</codeph>, <codeph>least(double a[, double b ...])</codeph>,
- <codeph>least(decimal(p,s) a[, decimal(p,s) b ...])</codeph>, <codeph>least(string a[, string b
- ...])</codeph>, <codeph>least(timestamp a[, timestamp b ...])</codeph>
+ LEAST(BIGINT a[, BIGINT b ...]), LEAST(DOUBLE a[, DOUBLE b ...]), LEAST(DECIMAL(p,s)
+ a[, DECIMAL(p,s) b ...]), LEAST(STRING a[, STRING b ...]), LEAST(TIMESTAMP a[,
+ TIMESTAMP b ...])
</dt>
<dd rev="1.4.0">
- <indexterm audience="hidden">least() function</indexterm>
<b>Purpose:</b> Returns the smallest value from a list of expressions.
<p conref="../shared/impala_common.xml#common/return_same_type"/>
</dd>
@@ -694,18 +958,17 @@ select fmod(9.9,3.3);
<dlentry id="ln">
<dt>
- <codeph>ln(double a)</codeph>,
- <codeph rev="2.3.0" id="dlog1">dlog1(double a)</codeph>
+ LN(DOUBLE a), DLOG1(DOUBLE a)
</dt>
<dd>
- <indexterm audience="hidden">ln() function</indexterm>
- <indexterm audience="hidden">dlog1() function</indexterm>
<b>Purpose:</b> Returns the
- <xref href="https://en.wikipedia.org/wiki/Natural_logarithm" scope="external" format="html">natural
+ <xref
+ href="https://en.wikipedia.org/wiki/Natural_logarithm"
+ scope="external" format="html">natural
logarithm</xref> of the argument.
<p>
- <b>Return type:</b> <codeph>double</codeph>
+ <b>Return type:</b> <codeph>DOUBLE</codeph>
</p>
</dd>
@@ -714,14 +977,13 @@ select fmod(9.9,3.3);
<dlentry id="log">
<dt>
- <codeph>log(double base, double a)</codeph>
+ LOG(DOUBLE base, DOUBLE a)
</dt>
<dd>
- <indexterm audience="hidden">log() function</indexterm>
<b>Purpose:</b> Returns the logarithm of the second argument to the specified base.
<p>
- <b>Return type:</b> <codeph>double</codeph>
+ <b>Return type:</b> <codeph>DOUBLE</codeph>
</p>
</dd>
@@ -730,16 +992,13 @@ select fmod(9.9,3.3);
<dlentry id="log10">
<dt>
- <codeph>log10(double a)</codeph>,
- <codeph rev="2.3.0" id="dlog10">dlog10(double a)</codeph>
+ LOG10(DOUBLE a), DLOG10(DOUBLE a)
</dt>
<dd>
- <indexterm audience="hidden">log10() function</indexterm>
- <indexterm audience="hidden">dlog10() function</indexterm>
<b>Purpose:</b> Returns the logarithm of the argument to the base 10.
<p>
- <b>Return type:</b> <codeph>double</codeph>
+ <b>Return type:</b> <codeph>DOUBLE</codeph>
</p>
</dd>
@@ -748,14 +1007,13 @@ select fmod(9.9,3.3);
<dlentry id="log2">
<dt>
- <codeph>log2(double a)</codeph>
+ LOG2(DOUBLE a)
</dt>
<dd>
- <indexterm audience="hidden">log2() function</indexterm>
<b>Purpose:</b> Returns the logarithm of the argument to the base 2.
<p>
- <b>Return type:</b> <codeph>double</codeph>
+ <b>Return type:</b> <codeph>DOUBLE</codeph>
</p>
</dd>
@@ -764,25 +1022,21 @@ select fmod(9.9,3.3);
<dlentry rev="1.4.0" id="max_int">
<dt rev="1.4.0">
- <codeph>max_int(), <ph id="max_tinyint">max_tinyint()</ph>, <ph id="max_smallint">max_smallint()</ph>,
- <ph id="max_bigint">max_bigint()</ph></codeph>
+ MAX_INT(), MAX_TINYINT(), MAX_SMALLINT(), MAX_BIGINT()
</dt>
<dd rev="1.4.0">
- <indexterm audience="hidden">max_int() function</indexterm>
- <indexterm audience="hidden">max_tinyint() function</indexterm>
- <indexterm audience="hidden">max_smallint() function</indexterm>
- <indexterm audience="hidden">max_bigint() function</indexterm>
<b>Purpose:</b> Returns the largest value of the associated integral type.
<p>
<b>Return type:</b> The same as the integral type being checked.
</p>
+
<p>
-<!-- Repeated usage text between max_ and min_ functions, could turn into a conref. -->
- <b>Usage notes:</b> Use the corresponding <codeph>min_</codeph> and <codeph>max_</codeph> functions to
- check if all values in a column are within the allowed range, before copying data or altering column
- definitions. If not, switch to the next higher integral type or to a <codeph>DECIMAL</codeph> with
- sufficient precision.
+ <b>Usage notes:</b> Use the corresponding <codeph>min_</codeph> and
+ <codeph>max_</codeph> functions to check if all values in a column are within the
+ allowed range, before copying data or altering column definitions. If not, switch to
+ the next higher integral type or to a <codeph>DECIMAL</codeph> with sufficient
+ precision.
</p>
</dd>
@@ -791,24 +1045,22 @@ select fmod(9.9,3.3);
<dlentry rev="1.4.0" id="min_int">
<dt rev="1.4.0">
- <codeph>min_int(), <ph id="min_tinyint">min_tinyint()</ph>, <ph id="min_smallint">min_smallint()</ph>,
- <ph id="min_bigint">min_bigint()</ph></codeph>
+ MIN_INT(), MIN_TINYINT(), MIN_SMALLINT(), MIN_BIGINT()
</dt>
<dd rev="1.4.0">
- <indexterm audience="hidden">min_int() function</indexterm>
- <indexterm audience="hidden">min_tinyint() function</indexterm>
- <indexterm audience="hidden">min_smallint() function</indexterm>
- <indexterm audience="hidden">min_bigint() function</indexterm>
- <b>Purpose:</b> Returns the smallest value of the associated integral type (a negative number).
+ <b>Purpose:</b> Returns the smallest value of the associated integral type (a negative
+ number).
<p>
<b>Return type:</b> The same as the integral type being checked.
</p>
+
<p>
- <b>Usage notes:</b> Use the corresponding <codeph>min_</codeph> and <codeph>max_</codeph> functions to
- check if all values in a column are within the allowed range, before copying data or altering column
- definitions. If not, switch to the next higher integral type or to a <codeph>DECIMAL</codeph> with
- sufficient precision.
+ <b>Usage notes:</b> Use the corresponding <codeph>min_</codeph> and
+ <codeph>max_</codeph> functions to check if all values in a column are within the
+ allowed range, before copying data or altering column definitions. If not, switch to
+ the next higher integral type or to a <codeph>DECIMAL</codeph> with sufficient
+ precision.
</p>
</dd>
@@ -817,35 +1069,44 @@ select fmod(9.9,3.3);
<dlentry id="mod" rev="2.2.0">
<dt rev="2.2.0">
- <codeph>mod(<varname>numeric_type</varname> a, <varname>same_type</varname> b)</codeph>
+ MOD(numeric_type a, same_type b)
</dt>
<dd rev="2.2.0">
- <indexterm audience="hidden">mod() function</indexterm>
- <b>Purpose:</b> Returns the modulus of a number. Equivalent to the
- <codeph>%</codeph> arithmetic operator. Works with any size integer
- type, any size floating-point type, and <codeph>DECIMAL</codeph> with
- any precision and scale. <p
+ <b>Purpose:</b> Returns the modulus of a number. Equivalent to the <codeph>%</codeph>
+ arithmetic operator. Works with any size integer type, any size floating-point type,
+ and <codeph>DECIMAL</codeph> with any precision and scale.
+ <p
conref="../shared/impala_common.xml#common/return_type_same"/>
+
<p conref="../shared/impala_common.xml#common/added_in_220"/>
+
<p conref="../shared/impala_common.xml#common/usage_notes_blurb"/>
- <p> Because this function works with <codeph>DECIMAL</codeph> values,
- prefer it over <codeph>fmod()</codeph> when working with fractional
- values. It is not subject to the rounding errors that make
- <codeph>fmod()</codeph> problematic with floating-point
- numbers.</p><p rev="IMPALA-6202">Query plans shows the
- <codeph>MOD()</codeph> function as the <codeph>%</codeph>
- operator.</p>
+
+ <p>
+ Because this function works with <codeph>DECIMAL</codeph> values, prefer it over
+ <codeph>fmod()</codeph> when working with fractional values. It is not subject to
+ the rounding errors that make <codeph>fmod()</codeph> problematic with
+ floating-point numbers.
+ </p>
+
+ <p rev="IMPALA-6202">
+ Query plans shows the <codeph>MOD()</codeph> function as the <codeph>%</codeph>
+ operator.
+ </p>
+
<p conref="../shared/impala_common.xml#common/example_blurb"/>
- <p> The following examples show how the <codeph>mod()</codeph>
- function works for whole numbers and fractional values, and how the
- <codeph>%</codeph> operator works the same way. In the case of
- <codeph>mod(9.9,3)</codeph>, the type conversion for the second
- argument results in the first argument being interpreted as
- <codeph>DOUBLE</codeph>, so to produce an accurate
- <codeph>DECIMAL</codeph> result requires casting the second
- argument or writing it as a <codeph>DECIMAL</codeph> literal, 3.0. </p>
- <codeblock>select mod(10,3);
+
+ <p>
+ The following examples show how the <codeph>mod()</codeph> function works for whole
+ numbers and fractional values, and how the <codeph>%</codeph> operator works the
+ same way. In the case of <codeph>mod(9.9,3)</codeph>, the type conversion for the
+ second argument results in the first argument being interpreted as
+ <codeph>DOUBLE</codeph>, so to produce an accurate <codeph>DECIMAL</codeph> result
+ requires casting the second argument or writing it as a <codeph>DECIMAL</codeph>
+ literal, 3.0.
+ </p>
+<codeblock>select mod(10,3);
+-------------+
| fmod(10, 3) |
+-------------+
@@ -908,34 +1169,46 @@ select mod(9.9,3.0);
<dlentry id="murmur_hash" rev="IMPALA-3651 2.12.0">
<dt rev="2.12.0">
- <codeph>murmur_hash(type v)</codeph>
+ MURMUR_HASH(type v)
</dt>
<dd rev="2.12.0">
- <indexterm audience="hidden">murmur_hash() function</indexterm>
- <b>Purpose:</b> Returns a consistent 64-bit value derived from the input argument, for convenience of
- implementing <xref keyref="MurmurHash"> MurmurHash2</xref> non-cryptographic hash function.
+ <b>Purpose:</b> Returns a consistent 64-bit value derived from the input argument, for
+ convenience of implementing <xref
+ keyref="MurmurHash"> MurmurHash2</xref>
+ non-cryptographic hash function.
<p>
<b>Return type:</b> <codeph>BIGINT</codeph>
</p>
+
<p conref="../shared/impala_common.xml#common/usage_notes_blurb"/>
+
<p>
- You might use the return value in an application where you perform load balancing, bucketing, or some
- other technique to divide processing or storage. This function provides a good performance for all kinds
- of keys such as number, ascii string and UTF-8. It can be recommended as general-purpose hashing function.
+ You might use the return value in an application where you perform load balancing,
+ bucketing, or some other technique to divide processing or storage. This function
+ provides a good performance for all kinds of keys such as number, ascii string and
+ UTF-8. It can be recommended as general-purpose hashing function.
</p>
+
<p>
- Regarding comparison of murmur_hash with fnv_hash, murmur_hash is based on Murmur2 hash algorithm and fnv_hash
- function is based on FNV-1a hash algorithm. Murmur2 and FNV-1a can show very good randomness and performance
- compared with well known other hash algorithms, but Murmur2 slightly show better randomness and performance than FNV-1a.
- See <xref keyref="hash_functions1">[1]</xref><xref keyref="hash_functions2">[2]</xref><xref keyref="hash_functions1">[3]</xref> for details.
+ Regarding comparison of murmur_hash with fnv_hash, murmur_hash is based on Murmur2
+ hash algorithm and fnv_hash function is based on FNV-1a hash algorithm. Murmur2 and
+ FNV-1a can show very good randomness and performance compared with well known other
+ hash algorithms, but Murmur2 slightly show better randomness and performance than
+ FNV-1a. See
+ <xref keyref="hash_functions1"
+ >[1]</xref><xref keyref="hash_functions2">[2]</xref><xref
+ keyref="hash_functions1">[3]</xref>
+ for details.
</p>
+
<p>
- Similar input values of different types could produce different hash values, for example the same
- numeric value represented as <codeph>SMALLINT</codeph> or <codeph>BIGINT</codeph>,
- <codeph>FLOAT</codeph> or <codeph>DOUBLE</codeph>, or <codeph>DECIMAL(5,2)</codeph> or
- <codeph>DECIMAL(20,5)</codeph>.
+ Similar input values of different types could produce different hash values, for
+ example the same numeric value represented as <codeph>SMALLINT</codeph> or
+ <codeph>BIGINT</codeph>, <codeph>FLOAT</codeph> or <codeph>DOUBLE</codeph>, or
+ <codeph>DECIMAL(5,2)</codeph> or <codeph>DECIMAL(20,5)</codeph>.
</p>
+
<p conref="../shared/impala_common.xml#common/example_blurb"/>
<codeblock>[localhost:21000] > create table h (x int, s string);
[localhost:21000] > insert into h values (0, 'hello'), (1,'world'), (1234567890,'antidisestablishmentarianism');
@@ -956,7 +1229,8 @@ select mod(9.9,3.0);
| antidisestablishmentarianism | -2261804666958489663 |
+------------------------------+----------------------+ </codeblock>
<p>
- For short argument values, the high-order bits of the result have relatively higher entropy than fnv_hash:
+ For short argument values, the high-order bits of the result have relatively higher
+ entropy than fnv_hash:
</p>
<codeblock>[localhost:21000] > create table b (x boolean);
[localhost:21000] > insert into b values (true), (true), (false), (false);
@@ -979,24 +1253,18 @@ select mod(9.9,3.0);
<dlentry rev="1.4.0" id="negative">
<dt rev="2.0.1">
- <codeph>negative(numeric_type a)</codeph>
-<!-- <codeph>negative(int a), negative(double a), negative(decimal(p,s) a)</codeph> -->
+ NEGATIVE(numeric_type a)
</dt>
<dd>
- <indexterm audience="hidden">negative() function</indexterm>
- <b>Purpose:</b> Returns the argument with the sign reversed; returns a positive value if the argument was
- already negative.
- <p rev="2.0.1" conref="../shared/impala_common.xml#common/return_type_same"/>
-<!--
- <p>
- <b>Return type:</b> <codeph>int</codeph>, <codeph>double</codeph>,
- or <codeph>decimal(p,s)</codeph> depending on type of argument
- </p>
- -->
+ <b>Purpose:</b> Returns the argument with the sign reversed; returns a positive value
+ if the argument was already negative.
+ <p rev="2.0.1"
+ conref="../shared/impala_common.xml#common/return_type_same"/>
+
<p>
- <b>Usage notes:</b> Use <codeph>-abs(a)</codeph> instead if you need to ensure all return values are
- negative.
+ <b>Usage notes:</b> Use <codeph>-ABS(a)</codeph> instead if you need to ensure all
+ return values are negative.
</p>
</dd>
@@ -1005,14 +1273,13 @@ select mod(9.9,3.0);
<dlentry id="pi">
<dt rev="1.4.0">
- <codeph>pi()</codeph>
+ PI()
</dt>
<dd rev="1.4.0">
- <indexterm audience="hidden">pi() function</indexterm>
<b>Purpose:</b> Returns the constant pi.
<p>
- <b>Return type:</b> <codeph>double</codeph>
+ <b>Return type:</b> <codeph>DOUBLE</codeph>
</p>
</dd>
@@ -1021,21 +1288,26 @@ select mod(9.9,3.0);
<dlentry id="pmod">
<dt>
- <codeph>pmod(bigint a, bigint b), pmod(double a, double b)</codeph>
+ PMOD(BIGINT a, BIGINT b), PMOD(DOUBLE a, DOUBLE b)
</dt>
<dd>
- <indexterm audience="hidden">pmod() function</indexterm>
- <b>Purpose:</b> Returns the positive modulus of a number.
- Primarily for <xref href="https://issues.apache.org/jira/browse/HIVE-656" scope="external" format="html">HiveQL compatibility</xref>.
+ <b>Purpose:</b> Returns the positive modulus of a number. Primarily for
+ <xref href="https://issues.apache.org/jira/browse/HIVE-656"
+ scope="external" format="html">HiveQL
+ compatibility</xref>.
<p>
- <b>Return type:</b> <codeph>int</codeph> or <codeph>double</codeph>, depending on type of arguments
+ <b>Return type:</b> <codeph>INT</codeph> or <codeph>DOUBLE</codeph>, depending on
+ type of arguments
</p>
+
<p conref="../shared/impala_common.xml#common/example_blurb"/>
+
<p>
- The following examples show how the <codeph>fmod()</codeph> function sometimes returns a negative value
- depending on the sign of its arguments, and the <codeph>pmod()</codeph> function returns the same value
- as <codeph>fmod()</codeph>, but sometimes with the sign flipped.
+ The following examples show how the <codeph>FMOD()</codeph> function sometimes
+ returns a negative value depending on the sign of its arguments, and the
+ <codeph>PMOD()</codeph> function returns the same value as <codeph>FMOD()</codeph>,
+ but sometimes with the sign flipped.
</p>
<codeblock>select fmod(-5,2);
+-------------+
@@ -1086,23 +1358,18 @@ select pmod(5,-2);
<dlentry rev="1.4.0" id="positive">
<dt rev="2.0.1">
- <codeph>positive(numeric_type a)</codeph>
-<!-- <codeph>positive(int a), positive(double a), positive(decimal(p,s) a</codeph> -->
+ POSITIVE(numeric_type a)
</dt>
<dd>
- <indexterm audience="hidden">positive() function</indexterm>
- <b>Purpose:</b> Returns the original argument unchanged (even if the argument is negative).
- <p rev="2.0.1" conref="../shared/impala_common.xml#common/return_type_same"/>
-<!--
- <p>
- <b>Return type:</b> <codeph>int</codeph>, <codeph>double</codeph>,
- or <codeph>decimal(p,s)</codeph> depending on type of argument
- </p>
- -->
+ <b>Purpose:</b> Returns the original argument unchanged (even if the argument is
+ negative).
+ <p rev="2.0.1"
+ conref="../shared/impala_common.xml#common/return_type_same"/>
+
<p>
- <b>Usage notes:</b> Use <codeph>abs()</codeph> instead if you need to ensure all return values are
- positive.
+ <b>Usage notes:</b> Use <codeph>ABS()</codeph> instead if you need to ensure all
+ return values are positive.
</p>
</dd>
@@ -1111,20 +1378,14 @@ select pmod(5,-2);
<dlentry id="pow">
<dt rev="1.4.0">
- <codeph>pow(double a, double p)</codeph>,
- <codeph id="power">power(double a, double p)</codeph>,
- <codeph rev="2.3.0" id="dpow">dpow(double a, double p)</codeph>,
- <codeph rev="2.3.0" id="fpow">fpow(double a, double p)</codeph>
+ POW(DOUBLE a, DOUBLE p), POWER(DOUBLE a, DOUBLE p), DPOW(DOUBLE a, DOUBLE p),
+ FPOW(DOUBLE a, DOUBLE p)
</dt>
<dd rev="1.4.0">
- <indexterm audience="hidden">pow() function</indexterm>
- <indexterm audience="hidden">power() function</indexterm>
- <indexterm audience="hidden">dpow() function</indexterm>
- <indexterm audience="hidden">fpow() function</indexterm>
<b>Purpose:</b> Returns the first argument raised to the power of the second argument.
<p>
- <b>Return type:</b> <codeph>double</codeph>
+ <b>Return type:</b> <codeph>DOUBLE</codeph>
</p>
</dd>
@@ -1133,24 +1394,31 @@ select pmod(5,-2);
<dlentry rev="1.4.0" id="precision">
<dt rev="1.4.0">
- <codeph>precision(<varname>numeric_expression</varname>)</codeph>
+ PRECISION(numeric_expression)
</dt>
<dd rev="1.4.0">
- <indexterm audience="hidden">precision() function</indexterm>
- <b>Purpose:</b> Computes the precision (number of decimal digits) needed to represent the type of the
- argument expression as a <codeph>DECIMAL</codeph> value.
- <p conref="../shared/impala_common.xml#common/usage_notes_blurb"/>
+ <b>Purpose:</b> Computes the precision (number of decimal digits) needed to represent
+ the type of the argument expression as a <codeph>DECIMAL</codeph> value.
+ <p
+ conref="../shared/impala_common.xml#common/usage_notes_blurb"/>
+
<p>
- Typically used in combination with the <codeph>scale()</codeph> function, to determine the appropriate
- <codeph>DECIMAL(<varname>precision</varname>,<varname>scale</varname>)</codeph> type to declare in a
- <codeph>CREATE TABLE</codeph> statement or <codeph>CAST()</codeph> function.
+ Typically used in combination with the <codeph>SCALE()</codeph> function, to
+ determine the appropriate
+ <codeph>DECIMAL(<varname>precision</varname>,<varname>scale</varname>)</codeph> type
+ to declare in a <codeph>CREATE TABLE</codeph> statement or <codeph>CAST()</codeph>
+ function.
</p>
+
<p>
- <b>Return type:</b> <codeph>int</codeph>
+ <b>Return type:</b> <codeph>INT</codeph>
</p>
+
<p conref="../shared/impala_common.xml#common/example_blurb"/>
- <p conref="../shared/impala_common.xml#common/precision_scale_example"/>
+
+ <p conref="../shared/impala_common.xml#common/precision_scale_example"
+ />
</dd>
</dlentry>
@@ -1158,19 +1426,20 @@ select pmod(5,-2);
<dlentry id="quotient">
<dt>
- <codeph>quotient(bigint numerator, bigint denominator)</codeph>,
- <codeph>quotient(double numerator, double denominator)</codeph>
+ QUOTIENT(BIGINT numerator, BIGINT denominator), QUOTIENT(DOUBLE numerator, DOUBLE
+ denominator)
</dt>
<dd>
- <indexterm audience="hidden">quotient() function</indexterm>
- <b>Purpose:</b> Returns the first argument divided by the second argument, discarding any fractional
- part. Avoids promoting integer arguments to <codeph>DOUBLE</codeph> as happens with the <codeph>/</codeph> SQL
- operator. <ph rev="IMPALA-278">Also includes an overload that accepts <codeph>DOUBLE</codeph> arguments,
- discards the fractional part of each argument value before dividing, and again returns <codeph>BIGINT</codeph>.
- With integer arguments, this function works the same as the <codeph>DIV</codeph> operator.</ph>
+ <b>Purpose:</b> Returns the first argument divided by the second argument, discarding
+ any fractional part. Avoids promoting integer arguments to <codeph>DOUBLE</codeph> as
+ happens with the <codeph>/</codeph> SQL operator. <ph rev="IMPALA-278">Also includes
+ an overload that accepts <codeph>DOUBLE</codeph> arguments, discards the fractional
+ part of each argument value before dividing, and again returns
+ <codeph>BIGINT</codeph>. With integer arguments, this function works the same as the
+ <codeph>DIV</codeph> operator.</ph>
<p>
- <b>Return type:</b> <codeph>bigint</codeph>
+ <b>Return type:</b> <codeph>BIGINT</codeph>
</p>
</dd>
@@ -1179,14 +1448,13 @@ select pmod(5,-2);
<dlentry id="radians">
<dt>
- <codeph>radians(double a)</codeph>
+ RADIANS(DOUBLE a)
</dt>
<dd>
- <indexterm audience="hidden">radians() function</indexterm>
<b>Purpose:</b> Converts argument value from degrees to radians.
<p>
- <b>Return type:</b> <codeph>double</codeph>
+ <b>Return type:</b> <codeph>DOUBLE</codeph>
</p>
</dd>
@@ -1195,37 +1463,41 @@ select pmod(5,-2);
<dlentry id="rand">
<dt>
- <codeph>rand()</codeph>, <codeph>rand(bigint seed)</codeph>,
- <codeph rev="2.3.0" id="random">random()</codeph>,
- <codeph rev="2.3.0">random(bigint seed)</codeph>
+ RAND(), RAND(BIGINT seed), RANDOME(), RANDOME(BIGINT seed)
</dt>
<dd>
- <indexterm audience="hidden">rand() function</indexterm>
- <b>Purpose:</b> Returns a random value between 0 and 1. After <codeph>rand()</codeph> is called with a
- seed argument, it produces a consistent random sequence based on the seed value.
+ <b>Purpose:</b> Returns a random value between 0 and 1. After <codeph>RAND()</codeph>
+ is called with a seed argument, it produces a consistent random sequence based on the
+ seed value.
<p>
- <b>Return type:</b> <codeph>double</codeph>
+ <b>Return type:</b> <codeph>DOUBLE</codeph>
</p>
+
<p>
- <b>Usage notes:</b> Currently, the random sequence is reset after each query, and multiple calls to
- <codeph>rand()</codeph> within the same query return the same value each time. For different number
- sequences that are different for each query, pass a unique seed value to each call to
- <codeph>rand()</codeph>. For example, <codeph>select rand(unix_timestamp()) from ...</codeph>
+ <b>Usage notes:</b> Currently, the random sequence is reset after each query, and
+ multiple calls to <codeph>RAND()</codeph> within the same query return the same
+ value each time. For different number sequences that are different for each query,
+ pass a unique seed value to each call to <codeph>RAND()</codeph>. For example,
+ <codeph>SELECT RAND(UNIX_TIMESTAMP()) FROM ...</codeph>
</p>
+
<p conref="../shared/impala_common.xml#common/example_blurb"/>
+
<p>
- The following examples show how <codeph>rand()</codeph> can produce sequences of varying predictability,
- so that you can reproduce query results involving random values or generate unique sequences of random
- values for each query.
- When <codeph>rand()</codeph> is called with no argument, it generates the same sequence of values each time,
- regardless of the ordering of the result set.
- When <codeph>rand()</codeph> is called with a constant integer, it generates a different sequence of values,
- but still always the same sequence for the same seed value.
- If you pass in a seed value that changes, such as the return value of the expression <codeph>unix_timestamp(now())</codeph>,
- each query will use a different sequence of random values, potentially more useful in probability calculations although
- more difficult to reproduce at a later time. Therefore, the final two examples with an unpredictable seed value
- also include the seed in the result set, to make it possible to reproduce the same random sequence later.
+ The following examples show how <codeph>rand()</codeph> can produce sequences of
+ varying predictability, so that you can reproduce query results involving random
+ values or generate unique sequences of random values for each query. When
+ <codeph>rand()</codeph> is called with no argument, it generates the same sequence
+ of values each time, regardless of the ordering of the result set. When
+ <codeph>rand()</codeph> is called with a constant integer, it generates a different
+ sequence of values, but still always the same sequence for the same seed value. If
+ you pass in a seed value that changes, such as the return value of the expression
+ <codeph>unix_timestamp(now())</codeph>, each query will use a different sequence of
+ random values, potentially more useful in probability calculations although more
+ difficult to reproduce at a later time. Therefore, the final two examples with an
+ unpredictable seed value also include the seed in the result set, to make it
+ possible to reproduce the same random sequence later.
</p>
<codeblock>select x, rand() from three_rows;
+---+-----------------------+
@@ -1290,24 +1562,17 @@ select x, unix_timestamp(now()), rand(unix_timestamp(now()))
<dlentry id="round">
<dt>
- <codeph>round(double a)</codeph>,
- <codeph>round(double a, int d)</codeph>,
- <codeph rev="1.4.0">round(decimal a, int_type d)</codeph>,
- <codeph rev="2.3.0" id="dround">dround(double a)</codeph>,
- <codeph rev="2.3.0">dround(double a, int d)</codeph>,
- <codeph rev="2.3.0">dround(decimal(p,s) a, int_type d)</codeph>
+ ROUND(DOUBLE a), ROUND(DOUBLE a, INT d), ROUND(DECIMAL a, int_type d), DROUND(DOUBLE
+ a), DROUND(DOUBLE a, INT d), DROUND(DECIMAL(p,s) a, int_type d)
</dt>
<dd>
- <indexterm audience="hidden">round() function</indexterm>
- <indexterm audience="hidden">dround() function</indexterm>
- <b>Purpose:</b> Rounds a floating-point value. By default (with a
- single argument), rounds to the nearest integer. Values ending in .5
- are rounded up for positive numbers, down for negative numbers (that
- is, away from zero). The optional second argument specifies how many
- digits to leave after the decimal point; values greater than zero
- produce a floating-point return value rounded to the requested number
- of digits to the right of the decimal point.
+ <b>Purpose:</b> Rounds a floating-point value. By default (with a single argument),
+ rounds to the nearest integer. Values ending in .5 are rounded up for positive
+ numbers, down for negative numbers (that is, away from zero). The optional second
+ argument specifies how many digits to leave after the decimal point; values greater
+ than zero produce a floating-point return value rounded to the requested number of
+ digits to the right of the decimal point.
<p rev="1.4.0">
<b>Return type:</b> Same as the input type
</p>
@@ -1318,24 +1583,32 @@ select x, unix_timestamp(now()), rand(unix_timestamp(now()))
<dlentry rev="1.4.0" id="scale">
<dt rev="1.4.0">
- <codeph>scale(<varname>numeric_expression</varname>)</codeph>
+ SCALE(numeric_expression)
</dt>
<dd rev="1.4.0">
- <indexterm audience="hidden">scale() function</indexterm>
- <b>Purpose:</b> Computes the scale (number of decimal digits to the right of the decimal point) needed to
- represent the type of the argument expression as a <codeph>DECIMAL</codeph> value.
- <p conref="../shared/impala_common.xml#common/usage_notes_blurb"/>
+ <b>Purpose:</b> Computes the scale (number of decimal digits to the right of the
+ decimal point) needed to represent the type of the argument expression as a
+ <codeph>DECIMAL</codeph> value.
+ <p
+ conref="../shared/impala_common.xml#common/usage_notes_blurb"/>
+
<p>
- Typically used in combination with the <codeph>precision()</codeph> function, to determine the
- appropriate <codeph>DECIMAL(<varname>precision</varname>,<varname>scale</varname>)</codeph> type to
- declare in a <codeph>CREATE TABLE</codeph> statement or <codeph>CAST()</codeph> function.
+ Typically used in combination with the <codeph>PRECISION()</codeph> function, to
+ determine the appropriate
+ <codeph>DECIMAL(<varname>precision</varname>,<varname>scale</varname>)</codeph> type
+ to declare in a <codeph>CREATE TABLE</codeph> statement or <codeph>CAST()</codeph>
+ function.
</p>
+
<p>
- <b>Return type:</b> <codeph>int</codeph>
+ <b>Return type:</b> <codeph>INT</codeph>
</p>
+
<p conref="../shared/impala_common.xml#common/example_blurb"/>
- <p conref="../shared/impala_common.xml#common/precision_scale_example"/>
+
+ <p conref="../shared/impala_common.xml#common/precision_scale_example"
+ />
</dd>
</dlentry>
@@ -1343,14 +1616,13 @@ select x, unix_timestamp(now()), rand(unix_timestamp(now()))
<dlentry id="sign">
<dt>
- <codeph>sign(double a)</codeph>
+ SIGN(DOUBLE a)
</dt>
<dd>
- <indexterm audience="hidden">sign() function</indexterm>
<b>Purpose:</b> Returns -1, 0, or 1 to indicate the signedness of the argument value.
<p>
- <b>Return type:</b> <codeph>int</codeph>
+ <b>Return type:</b> <codeph>INT</codeph>
</p>
</dd>
@@ -1359,14 +1631,13 @@ select x, unix_timestamp(now()), rand(unix_timestamp(now()))
<dlentry id="sin">
<dt>
- <codeph>sin(double a)</codeph>
+ SIN(DOUBLE a)
</dt>
<dd>
- <indexterm audience="hidden">sin() function</indexterm>
<b>Purpose:</b> Returns the sine of the argument.
<p>
- <b>Return type:</b> <codeph>double</codeph>
+ <b>Return type:</b> <codeph>DOUBLE</codeph>
</p>
</dd>
@@ -1375,14 +1646,13 @@ select x, unix_timestamp(now()), rand(unix_timestamp(now()))
<dlentry id="sinh" rev="2.3.0 IMPALA-1771">
<dt rev="2.3.0 IMPALA-1771">
- <codeph>sinh(double a)</codeph>
+ SINH(DOUBLE a)
</dt>
<dd rev="2.3.0 IMPALA-1771">
- <indexterm audience="hidden">sinh() function</indexterm>
<b>Purpose:</b> Returns the hyperbolic sine of the argument.
<p>
- <b>Return type:</b> <codeph>double</codeph>
+ <b>Return type:</b> <codeph>DOUBLE</codeph>
</p>
</dd>
@@ -1391,16 +1661,13 @@ select x, unix_timestamp(now()), rand(unix_timestamp(now()))
<dlentry id="sqrt">
<dt>
- <codeph>sqrt(double a)</codeph>,
- <codeph rev="2.3.0" id="dsqrt">dsqrt(double a)</codeph>
+ SQRT(DOUBLE a), DSQRT(DOUBLE a)
</dt>
<dd>
- <indexterm audience="hidden">sqrt() function</indexterm>
- <indexterm audience="hidden">dsqrt() function</indexterm>
<b>Purpose:</b> Returns the square root of the argument.
<p>
- <b>Return type:</b> <codeph>double</codeph>
+ <b>Return type:</b> <codeph>DOUBLE</codeph>
</p>
</dd>
@@ -1409,14 +1676,13 @@ select x, unix_timestamp(now()), rand(unix_timestamp(now()))
<dlentry id="tan">
<dt>
- <codeph>tan(double a)</codeph>
+ TAN(DOUBLE a)
</dt>
<dd>
- <indexterm audience="hidden">tan() function</indexterm>
<b>Purpose:</b> Returns the tangent of the argument.
<p>
- <b>Return type:</b> <codeph>double</codeph>
+ <b>Return type:</b> <codeph>DOUBLE</codeph>
</p>
</dd>
@@ -1425,14 +1691,13 @@ select x, unix_timestamp(now()), rand(unix_timestamp(now()))
<dlentry id="tanh" rev="2.3.0 IMPALA-1771">
<dt rev="2.3.0 IMPALA-1771">
- <codeph>tanh(double a)</codeph>
+ TANH(DOUBLE a)
</dt>
<dd rev="2.3.0 IMPALA-1771">
- <indexterm audience="hidden">tanh() function</indexterm>
<b>Purpose:</b> Returns the hyperbolic tangent of the argument.
<p>
- <b>Return type:</b> <codeph>double</codeph>
+ <b>Return type:</b> <codeph>DOUBLE</codeph>
</p>
</dd>
@@ -1441,62 +1706,67 @@ select x, unix_timestamp(now()), rand(unix_timestamp(now()))
<dlentry rev="2.3.0" id="truncate">
<dt rev="2.3.0">
- <codeph>truncate(double_or_decimal a[, digits_to_leave])</codeph>,
- <ph id="dtrunc"><codeph>dtrunc(double_or_decimal a[, digits_to_leave])</codeph></ph>,
- <ph rev="2.10.0 IMPALA-5529" id="trunc_number"><codeph>trunc(double_or_decimal a[, digits_to_leave])</codeph></ph>
+ TRUNCATE(DOUBLE_or_DECIMAL a[, digits_to_leave]), DTRUNC(DOUBLE_or_DECIMAL a[,
+ digits_to_leave]), TRUNC(DOUBLE_or_DECIMAL a[, digits_to_leave])
</dt>
<dd rev="2.3.0">
- <indexterm audience="hidden">truncate() function</indexterm>
- <indexterm audience="hidden">dtrunc() function</indexterm>
- <indexterm audience="hidden">trunc() function</indexterm>
<b>Purpose:</b> Removes some or all fractional digits from a numeric value.
<p>
- <b>Arguments:</b>
- With a single floating-point argument, removes all fractional digits, leaving an
- integer value. The optional second argument specifies the number of fractional digits
- to include in the return value, and only applies when the argument type is
- <codeph>DECIMAL</codeph>. A second argument of 0 truncates to a whole integer value.
- A second argument of negative N sets N digits to 0 on the left side of the decimal
+ <b>Arguments:</b> With a single floating-point argument, removes all fractional
+ digits, leaving an integer value. The optional second argument specifies the number
+ of fractional digits to include in the return value, and only applies when the
+ argument type is <codeph>DECIMAL</codeph>. A second argument of 0 truncates to a
+ whole integer value. A second argument of negative N sets N digits to 0 on the left
+ side of the decimal
</p>
+
<p rev="2.10.0 IMPALA-5529">
<b>Scale argument:</b> The scale argument applies only when truncating
- <codeph>DECIMAL</codeph> values. It is an integer specifying how many
- significant digits to leave to the right of the decimal point.
- A scale argument of 0 truncates to a whole integer value. A scale
- argument of negative N sets N digits to 0 on the left side of the decimal
- point.
+ <codeph>DECIMAL</codeph> values. It is an integer specifying how many significant
+ digits to leave to the right of the decimal point. A scale argument of 0 truncates
+ to a whole integer value. A scale argument of negative N sets N digits to 0 on the
+ left side of the decimal point.
</p>
+
<p>
- <codeph>truncate()</codeph>, <codeph>dtrunc()</codeph>,
- <ph rev="2.10.0 IMPALA-5529">and <codeph>trunc()</codeph></ph> are aliases for the
- same function.
+ <codeph>TRUNCATE()</codeph>, <codeph>DTRUNC()</codeph>,
+ <ph
+ rev="2.10.0 IMPALA-5529">and <codeph>TRUNC()</codeph></ph> are
+ aliases for the same function.
</p>
+
<p>
<b>Return type:</b> Same as the input type
</p>
+
<p>
- <b>Added in:</b> The <codeph>trunc()</codeph> alias was added in
+ <b>Added in:</b> The <codeph>TRUNC()</codeph> alias was added in
<keyword keyref="impala210_full"/>.
</p>
+
<p conref="../shared/impala_common.xml#common/usage_notes_blurb"/>
+
<p rev="2.10.0 IMPALA-5529">
You can also pass a <codeph>DOUBLE</codeph> argument, or <codeph>DECIMAL</codeph>
- argument with optional scale, to the <codeph>dtrunc()</codeph> or
- <codeph>truncate</codeph> functions. Using the <codeph>trunc()</codeph>
- function for numeric values is common with other industry-standard database
- systems, so you might find such <codeph>trunc()</codeph> calls in code that you
- are porting to Impala.
+ argument with optional scale, to the <codeph>DTRUNC()</codeph> or
+ <codeph>TRUNCATE</codeph> functions. Using the <codeph>TRUNC()</codeph> function for
+ numeric values is common with other industry-standard database systems, so you might
+ find such <codeph>TRUNC()</codeph> calls in code that you are porting to Impala.
</p>
+
<p>
- The <codeph>trunc()</codeph> function also has a signature that applies to
- <codeph>TIMESTAMP</codeph> values. See <xref keyref="datetime_functions"/>
- for details.
+ The <codeph>TRUNC()</codeph> function also has a signature that applies to
+ <codeph>TIMESTAMP</codeph> values. See
+ <xref
+ keyref="datetime_functions"/> for details.
</p>
+
<p conref="../shared/impala_common.xml#common/example_blurb"/>
+
<p>
- The following examples demonstrate the <codeph>truncate()</codeph>
- and <codeph>dtrunc()</codeph> signatures for this function:
+ The following examples demonstrate the <codeph>TRUNCATE()</codeph> and
+ <codeph>DTRUNC()</codeph> signatures for this function:
</p>
<codeblock>select truncate(3.45);
+----------------+
@@ -1542,10 +1812,9 @@ select truncate(3.456,7);
</codeblock>
<p rev="2.10.0 IMPALA-5529">
The following examples demonstrate using <codeph>trunc()</codeph> with
- <codeph>DECIMAL</codeph> or <codeph>DOUBLE</codeph> values, and with
- an optional scale argument for <codeph>DECIMAL</codeph> values.
- (The behavior is the same for the <codeph>truncate()</codeph> and
- <codeph>dtrunc()</codeph> aliases also.)
+ <codeph>DECIMAL</codeph> or <codeph>DOUBLE</codeph> values, and with an optional
+ scale argument for <codeph>DECIMAL</codeph> values. (The behavior is the same for
+ the <codeph>truncate()</codeph> and <codeph>dtrunc()</codeph> aliases also.)
</p>
<codeblock rev="2.10.0 IMPALA-5529">
create table t1 (d decimal(20,7));
@@ -1618,7 +1887,6 @@ from t1 order by d;
| 9999.99 | 15 | 2 |
+-------------+----+---+
</codeblock>
-
<codeblock rev="2.10.0 IMPALA-5529">
create table dbl (d double);
@@ -1647,92 +1915,121 @@ select trunc(d) from dbl order by d;
<dlentry id="unhex">
<dt>
- <codeph>unhex(string a)</codeph>
+ UNHEX(STRING a)
</dt>
<dd>
- <indexterm audience="hidden">unhex() function</indexterm>
- <b>Purpose:</b> Returns a string of characters with ASCII values corresponding to pairs of hexadecimal
- digits in the argument.
+ <b>Purpose:</b> Returns a string of characters with ASCII values corresponding to
+ pairs of hexadecimal digits in the argument.
<p>
- <b>Return type:</b> <codeph>string</codeph>
+ <b>Return type:</b> <codeph>STRING</codeph>
</p>
</dd>
</dlentry>
- <dlentry>
+
+ <dlentry id="width_bucket">
+
<dt>
- <codeph>width_bucket(decimal expr, decimal min_value, decimal
- max_value, int num_buckets)</codeph>
+ WIDTH_BUCKET(DECIMAL expr, DECIMAL min_value, DECIMAL max_value, INT num_buckets)
</dt>
+
<dd>
- <b>Purpose:</b> Returns the bucket number in which the
- <codeph>expr</codeph> value would fall in the histogram where its
- range between <codeph>min_value</codeph> and
- <codeph>max_value</codeph> is divided into
- <codeph>num_buckets</codeph> buckets of identical sizes. </dd>
- <dd>The function returns: <ul>
- <li><codeph>NULL</codeph> if any argument is
- <codeph>NULL</codeph>.</li>
- <li><codeph>0</codeph> if <codeph>expr</codeph> <
- <codeph>min_value</codeph>.</li>
- <li><codeph>num_buckets + 1</codeph> if <codeph>expr</codeph> >=
- <codeph>max_val</codeph>.</li>
- <li>If none of the above, the bucket number where
- <codeph>expr</codeph> falls.</li>
+ <b>Purpose:</b> Returns the bucket number in which the <codeph>expr</codeph> value
+ would fall in the histogram where its range between <codeph>min_value</codeph> and
+ <codeph>max_value</codeph> is divided into <codeph>num_buckets</codeph> buckets of
+ identical sizes.
+ </dd>
+
+ <dd>
+ The function returns:
+ <ul>
+ <li>
+ <codeph>NULL</codeph> if any argument is <codeph>NULL</codeph>.
+ </li>
+
+ <li>
+ <codeph>0</codeph> if <codeph>expr</codeph> < <codeph>min_value</codeph>.
+ </li>
+
+ <li>
+ <codeph>num_buckets + 1</codeph> if <codeph>expr</codeph> >=
+ <codeph>max_val</codeph>.
+ </li>
+
+ <li>
+ If none of the above, the bucket number where <codeph>expr</codeph> falls.
+ </li>
</ul>
<p>
- <b>Arguments:</b>The following rules apply to the arguments. <ul>
+ <b>Arguments:</b>The following rules apply to the arguments.
+ <ul>
<li>
- <codeph>min_val</codeph> is the minimum value of the histogram
- range. </li>
+ <codeph>min_val</codeph> is the minimum value of the histogram range.
+ </li>
+
<li>
- <codeph>max_val</codeph> is the maximum value of the histogram
- range. </li>
+ <codeph>max_val</codeph> is the maximum value of the histogram range.
+ </li>
+
<li>
- <codeph>num_buckets</codeph> must be greater than
- <codeph>0</codeph>. </li>
+ <codeph>num_buckets</codeph> must be greater than <codeph>0</codeph>.
+ </li>
+
<li>
- <codeph>min_value</codeph> must be less than
- <codeph>max_value</codeph>. </li>
+ <codeph>min_value</codeph> must be less than <codeph>max_value</codeph>.
+ </li>
</ul>
</p>
+
<p>
- <b>Usage notes:</b></p><p>Each bucket contains values equal to or
- greater than the base value of that bucket and less than the base
- value of the next bucket. For example, with <codeph>width_bucket(8,
- 1, 10, 3)</codeph>, the bucket ranges are actually the 0th
- "underflow bucket" with the range (-infinity to 0.999...), (1 to
- 3.999...), (4, to 6.999...), (7 to 9.999...), and the "overflow
- bucket" with the range (10 to infinity).</p>
+ <b>Usage notes:</b>
+ </p>
+
<p>
- <b>Return type:</b>
- <codeph>bigint</codeph>
+ Each bucket contains values equal to or greater than the base value of that bucket
+ and less than the base value of the next bucket. For example, with
+ <codeph>width_bucket(8, 1, 10, 3)</codeph>, the bucket ranges are actually the 0th
+ "underflow bucket" with the range (-infinity to 0.999...), (1 to 3.999...), (4, to
+ 6.999...), (7 to 9.999...), and the "overflow bucket" with the range (10 to
+ infinity).
</p>
+
<p>
- <b>Added in:</b>
- <keyword keyref="impala31"/>. </p>
+ <b>Return type:</b> <codeph>BIGINT</codeph>
+ </p>
+
+ <p>
+ <b>Added in:</b> <keyword keyref="impala31"/>.
+ </p>
+
<p>
<b>Examples:</b>
</p>
- <p> The below function creates <codeph>3</codeph> buckets between the
- range of <codeph>1</codeph> and <codeph>20</codeph> with the bucket
- width of 6.333, and returns <codeph>2</codeph> for the bucket #2
- where the value <codeph>8</codeph> falls
- in:<codeblock>width_bucket(8, 1, 20, 3)</codeblock>
+
+ <p>
+ The below function creates <codeph>3</codeph> buckets between the range of
+ <codeph>1</codeph> and <codeph>20</codeph> with the bucket width of 6.333, and
+ returns <codeph>2</codeph> for the bucket #2 where the value <codeph>8</codeph>
+ falls in:
+<codeblock>width_bucket(
<TRUNCATED>