IMPALA-2172, IMPALA-6391: [DOCS] Distinguish char_length() from length() Modify both char_length() and length() usage notes to say when they return the same or different results.
Include the same example, showing both STRING and CHAR types, under both functions. Change-Id: I18cabfce66351bb890bfbfc26b93466204a82625 Reviewed-on: http://gerrit.cloudera.org:8080/9014 Reviewed-by: Tim Armstrong <[email protected]> Tested-by: Impala Public Jenkins Project: http://git-wip-us.apache.org/repos/asf/impala/repo Commit: http://git-wip-us.apache.org/repos/asf/impala/commit/ceeb130c Tree: http://git-wip-us.apache.org/repos/asf/impala/tree/ceeb130c Diff: http://git-wip-us.apache.org/repos/asf/impala/diff/ceeb130c Branch: refs/heads/master Commit: ceeb130c5dba12dc37f69b01ec4cca4b577c0549 Parents: 717cb9d Author: John Russell <[email protected]> Authored: Thu Jan 11 18:23:45 2018 -0800 Committer: Impala Public Jenkins <[email protected]> Committed: Fri Jan 12 22:24:34 2018 +0000 ---------------------------------------------------------------------- docs/shared/impala_common.xml | 27 +++++++++++++++++++ docs/topics/impala_string_functions.xml | 39 ++++++++++++++++++++++++---- 2 files changed, 61 insertions(+), 5 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/impala/blob/ceeb130c/docs/shared/impala_common.xml ---------------------------------------------------------------------- diff --git a/docs/shared/impala_common.xml b/docs/shared/impala_common.xml index 9ada9a6..410fa9c 100644 --- a/docs/shared/impala_common.xml +++ b/docs/shared/impala_common.xml @@ -1706,6 +1706,33 @@ DROP VIEW db2.v1; <codeph>CHAR</codeph> or <codeph>VARCHAR</codeph> type with the appropriate length. </p> + <p id="length_demo" rev="IMPALA-6391 IMPALA-2172"> + The following example demonstrates how <codeph>length()</codeph> + and <codeph>char_length()</codeph> sometimes produce the same result, + and sometimes produce different results depending on the type of the + argument and the presence of trailing spaces for <codeph>CHAR</codeph> + values. The <codeph>S</codeph> and <codeph>C</codeph> values are + displayed with enclosing quotation marks to show any trailing spaces. +<codeblock id="length_demo_example">create table length_demo (s string, c char(5)); +insert into length_demo values + ('a',cast('a' as char(5))), + ('abc',cast('abc' as char(5))), + ('hello',cast('hello' as char(5))); + +select concat('"',s,'"') as s, concat('"',c,'"') as c, + length(s), length(c), + char_length(s), char_length(c) +from length_demo; ++---------+---------+-----------+-----------+----------------+----------------+ +| s | c | length(s) | length(c) | char_length(s) | char_length(c) | ++---------+---------+-----------+-----------+----------------+----------------+ +| "a" | "a " | 1 | 1 | 1 | 5 | +| "abc" | "abc " | 3 | 3 | 3 | 5 | +| "hello" | "hello" | 5 | 5 | 5 | 5 | ++---------+---------+-----------+-----------+----------------+----------------+ +</codeblock> + </p> + <p rev="2.0.0" id="subquery_no_limit"> Correlated subqueries used in <codeph>EXISTS</codeph> and <codeph>IN</codeph> operators cannot include a <codeph>LIMIT</codeph> clause. http://git-wip-us.apache.org/repos/asf/impala/blob/ceeb130c/docs/topics/impala_string_functions.xml ---------------------------------------------------------------------- diff --git a/docs/topics/impala_string_functions.xml b/docs/topics/impala_string_functions.xml index 36024f7..4a15167 100644 --- a/docs/topics/impala_string_functions.xml +++ b/docs/topics/impala_string_functions.xml @@ -203,14 +203,24 @@ select concat('[',btrim('xyhelxyzlozyzzxx','xyz'),']'); <codeph>char_length(string a), <ph rev="1.3.0" id="character_length">character_length(string a)</ph></codeph> </dt> - <dd> + <dd rev="IMPALA-6391 IMPALA-2172"> <indexterm audience="hidden">char_length() function</indexterm> <indexterm audience="hidden">character_length() function</indexterm> - <b>Purpose:</b> Returns the length in characters of the argument string. Aliases for the - <codeph>length()</codeph> function. + <b>Purpose:</b> Returns the length in characters of the argument string, including any + trailing spaces that pad a <codeph>CHAR</codeph> value. <p> <b>Return type:</b> <codeph>int</codeph> </p> + <p conref="../shared/impala_common.xml#common/usage_notes_blurb"/> + <p> + When applied to a <codeph>STRING</codeph> value, it returns the + same result as the <codeph>length()</codeph> function. When applied + to a <codeph>CHAR</codeph> value, it might return a larger value + than <codeph>length()</codeph> does, to account for trailing spaces + in the <codeph>CHAR</codeph>. + </p> + <p conref="../shared/impala_common.xml#common/example_blurb"/> + <p conref="../shared/impala_common.xml#common/length_demo"/> </dd> </dlentry> @@ -547,12 +557,31 @@ select instr('foo bar bletch', 'b', 1, null); <codeph>length(string a)</codeph> </dt> - <dd> + <dd rev="IMPALA-6391 IMPALA-2172"> <indexterm audience="hidden">length() function</indexterm> - <b>Purpose:</b> Returns the length in characters of the argument string. + <b>Purpose:</b> Returns the length in characters of the argument string, + ignoring any trailing spaces in <codeph>CHAR</codeph> values. <p> <b>Return type:</b> <codeph>int</codeph> </p> + <p conref="../shared/impala_common.xml#common/usage_notes_blurb"/> + <p> + When applied to a <codeph>STRING</codeph> value, it returns the + same result as the <codeph>char_length()</codeph> function. When applied + to a <codeph>CHAR</codeph> value, it might return a smaller value + than <codeph>char_length()</codeph> does, because <codeph>length()</codeph> + ignores any trailing spaces in the <codeph>CHAR</codeph>. + </p> + <note> + Because the behavior of <codeph>length()</codeph> with <codeph>CHAR</codeph> + values containing trailing spaces is not standardized across the industry, + when porting code from other database systems, evaluate the behavior of + <codeph>length()</codeph> on the source system and switch to + <codeph>char_length()</codeph> for Impala if necessary. + </note> + + <p conref="../shared/impala_common.xml#common/example_blurb"/> + <p conref="../shared/impala_common.xml#common/length_demo"/> </dd> </dlentry>
