trafodion-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From dbirds...@apache.org
Subject [2/4] trafodion git commit: Incorporate Comments 1
Date Thu, 10 May 2018 18:05:41 GMT
Incorporate Comments 1


Project: http://git-wip-us.apache.org/repos/asf/trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/trafodion/commit/b1d72633
Tree: http://git-wip-us.apache.org/repos/asf/trafodion/tree/b1d72633
Diff: http://git-wip-us.apache.org/repos/asf/trafodion/diff/b1d72633

Branch: refs/heads/master
Commit: b1d726334c28f1650e4a904d401dcb3cd4c0a8a2
Parents: 1c3e1c6
Author: liu.yu <qwertyioz@hotmail.com>
Authored: Tue May 8 19:19:18 2018 +0800
Committer: liu.yu <qwertyioz@hotmail.com>
Committed: Tue May 8 19:19:18 2018 +0800

----------------------------------------------------------------------
 .../sql_functions_and_expressions.adoc          | 570 ++++++++++++++++++-
 1 file changed, 563 insertions(+), 7 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/trafodion/blob/b1d72633/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
----------------------------------------------------------------------
diff --git a/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
b/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
index 324407e..e7dc38b 100644
--- a/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
+++ b/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
@@ -565,7 +565,7 @@ ADD_MONTHS(timestamp'2008-02-29 00:00:00',12,1)
 
 The ASCII function returns the integer that is the ASCII code of the
 first character in a character string expression associated with 
-the ISO88591 character set.
+the ISO88591 character set or the UTF8 character set.
 
 ASCII is a {project-name} SQL extension.
 
@@ -573,22 +573,578 @@ ASCII is a {project-name} SQL extension.
 ASCII (character-expression)
 ```
 
-* `_character-expression`
+* `_character-expression_`
 +
 is an SQL character value expression that specifies a string of
-characters. See <<character_value_expressions,Character Value Expressions>>.
+characters. 
++
+For more information, see <<character_value_expressions,Character Value Expressions>>.
 
 [[considerations_for_ascii]]
 === Considerations For ASCII
 
-If the value of the
-first byte in the string is greater than 127, {project-name} SQL returns this
-error message:
+ASCII function handles string and column differently:
+
+* For a string, the value of the character or *the value of each character* in the string
cannot be greater than 255, or else it returns the following error:
++
+```
+*** ERROR[4106] The character set for the operand of function ASCII must be ISO88591
+```
+
++
+*Example*
+
++
+```
+SQL>SELECT ASCII('ÿ') FROM DUAL;
+
+(EXPR)
+----------
+       255
+
+--- 1 row(s) selected.
+```
+
++
+```
+SQL>SELECT ASCII('u') FROM DUAL;
+
+(EXPR)
+----------
+       117
+
+--- 1 row(s) selected.
+```
 
++
 ```
-ERROR[4106] The character set for the operand of function ASCII must be ISO88591.
+SQL>SELECT ASCII('ÿu') FROM DUAL;
+
+(EXPR)
+----------
+       255
+
+--- 1 row(s) selected.
 ```
 
++
+```
+SQL>SELECT ASCII('昱') FROM DUAL;
+
+*** ERROR[4106] The character set for the operand of function ASCII must be ISO88591. [2018-05-07
06:01:55]
+```
+
++
+```
+SQL>SELECT ASCII('ÿu昱') FROM DUAL;
+
+*** ERROR[4106] The character set for the operand of function ASCII must be ISO88591. [2018-05-08
08:21:34]
+```
+
+* For a column, the string expression(s) in this column can be only single-byte, or else
it returns the following error:
+
++
+```
+*** ERROR[8428] The argument to function ASCII is not valid.
+```
+
++
+** For a string in a column with definition of UTF8, the value of the first byte in the string
cannot be greater than 127.
+** For a string in a column with definition of ISO88591, the value of the first byte in the
string can be all characters in the ISO88591 character set.
+
++
+*Example*
+
++
+.UTF8 (with and without error) -- Table with One Row
+[cols="10%,45%,45%"]
+|===
+| *Column Definition* | *UTF8* | *UTF8* 
+| *Table Structure*   
+| 
+`SQL>SHOWDDL t25;
+
+CREATE TABLE TRAFODION.SEABASE.T25
+  ( 
+    C1                               CHAR(4 CHARS) CHARACTER SET UTF8 COLLATE
+      DEFAULT DEFAULT NULL NOT SERIALIZED
+  )
+ ATTRIBUTES ALIGNED FORMAT 
+;
+
+--- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.T25 TO DB__ROOT
WITH GRANT OPTION;
+
+--- SQL operation complete.`
+
+| 
+`SQL>SHOWDDL t24;   
+ 
+CREATE TABLE TRAFODION.SEABASE.T24
+  ( 
+    C1                               CHAR(4 CHARS) CHARACTER SET UTF8 COLLATE
+      DEFAULT DEFAULT NULL NOT SERIALIZED
+  )
+ ATTRIBUTES ALIGNED FORMAT 
+;
+ 
+--- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.T24 TO DB__ROOT
WITH GRANT OPTION;
+
+--- SQL operation complete.`
+
+| *Data*
+| 
+`SQL>SELECT * FROM t25;
+
+C1
+
+----
+
+昱u    
+
+--- 1 row(s) selected.`
+|
+`SQL>SELECT * FROM t24;
+
+C1
+
+----
+
+u昱   
+
+--- 1 row(s) selected.`
+| *Result*
+| 
+`SQL>SELECT ASCII(c1) FROM t25;
+
+***ERROR[8428] The argument to function ASCII is not valid. [2018-05-08 10:14:08]`
+|
+`SQL>SELECT ASCII(c1) FROM t24;
+
+
+(EXPR)
+
+----------
+
+       117
+
+--- 1 row(s) selected.`
+
+|===
+
+
++
+.UTF8 (with and without error) -- Table with Two Rows
+[cols="10%,45%,45%"]
+|===
+| *Column Definition* | *UTF8* | *UTF8* 
+| *Table Structure*   
+| 
+`SQL>SHOWDDL t26;
+
+CREATE TABLE TRAFODION.SEABASE.T26
+  ( 
+    C1                               CHAR(4 CHARS) CHARACTER SET UTF8 COLLATE
+      DEFAULT DEFAULT NULL NOT SERIALIZED
+  )
+ ATTRIBUTES ALIGNED FORMAT 
+;
+
+--- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.T26 TO DB__ROOT
WITH GRANT OPTION;
+
+--- SQL operation complete.`
+
+| 
+`SQL>SHOWDDL t27;   
+ 
+CREATE TABLE TRAFODION.SEABASE.T27
+  ( 
+    C1                               CHAR(4 CHARS) CHARACTER SET UTF8 COLLATE
+      DEFAULT DEFAULT NULL NOT SERIALIZED
+  )
+ ATTRIBUTES ALIGNED FORMAT 
+;
+ 
+--- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.T27 TO DB__ROOT
WITH GRANT OPTION;
+
+--- SQL operation complete.`
+
+| *Data*
+| 
+`SQL>SELECT * FROM t26;
+
+C1
+
+----
+
+u昱 
+
+昱u    
+
+--- 2 row(s) selected.`
+|
+`SQL>SELECT * FROM t27;
+
+C1
+
+----
+
+u昱 
+
+u昱   
+
+--- 2 row(s) selected.`
+| *Result*
+| 
+`SQL>SELECT ASCII(c1) FROM t26;
+
+***ERROR[8428] The argument to function ASCII is not valid. [2018-05-08 10:29:03]`
+|
+`SQL>SELECT ASCII(c1) FROM t27;
+
+
+(EXPR)
+
+----------
+
+       117
+
+       117
+
+--- 2 row(s) selected.`
+
+|===
+
+
++
+.Comparison between UTF8 and ISO88591 (with error) -- Table with One Row 
+[cols="10%,45%,45%"]
+|===
+| *Column Definition* | *UTF8* | *ISO99591* 
+| *Table Structure*   
+| 
+`SQL>SHOWDDL t11;
+
+CREATE TABLE TRAFODION.SEABASE.T11
+  ( 
+    C1                               CHAR(4 CHARS) CHARACTER SET UTF8 COLLATE
+      DEFAULT DEFAULT NULL NOT SERIALIZED
+  )
+ ATTRIBUTES ALIGNED FORMAT 
+;
+
+--- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.T11 TO DB__ROOT
WITH GRANT OPTION;
+
+--- SQL operation complete.`
+
+| 
+`SQL>SHOWDDL t12;   
+ 
+CREATE TABLE TRAFODION.SEABASE.T12
+  ( 
+    C1                               CHAR(4) CHARACTER SET ISO88591 COLLATE
+      DEFAULT DEFAULT NULL NOT SERIALIZED
+  )
+ ATTRIBUTES ALIGNED FORMAT 
+;
+ 
+--- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.T12 TO DB__ROOT
WITH GRANT OPTION;
+
+--- SQL operation complete.`
+
+| *Data*
+| 
+`SQL>SELECT * FROM t11;
+
+C1
+
+----
+
+ÿu   
+
+--- 1 row(s) selected.`
+|
+`SQL>SELECT * FROM t12;
+
+C1
+
+----
+
+ÿu   
+
+--- 1 row(s) selected.`
+| *Result*
+| 
+`SQL>SELECT ASCII(c1) FROM t1;
+
+***ERROR[8428] The argument to function ASCII is not valid. [2018-05-07 02:13:42]`
+|
+`SQL>SELECT ASCII(c1) FROM t2;
+
+(EXPR)
+
+----------
+
+       255
+
+--- 1 row(s) selected.`
+|===
+
+
++
+.Comparison between UTF8 and ISO88591 (without error) -- Table with One Row 
+[cols="10%,45%,45%"]
+|===
+| *Column Definition* | *UTF8* | *ISO99591* 
+| *Table Structure*   
+| 
+`SQL>SHOWDDL t17;
+
+CREATE TABLE TRAFODION.SEABASE.T17
+  ( 
+    C1                               CHAR(4 CHARS) CHARACTER SET UTF8 COLLATE
+      DEFAULT DEFAULT NULL NOT SERIALIZED
+  )
+ ATTRIBUTES ALIGNED FORMAT 
+;
+
+--- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.T17 TO DB__ROOT
WITH GRANT OPTION;
+
+--- SQL operation complete.`
+
+| 
+`SQL>SHOWDDL t18;   
+ 
+CREATE TABLE TRAFODION.SEABASE.T18
+  ( 
+    C1                               CHAR(4) CHARACTER SET ISO88591 COLLATE
+      DEFAULT DEFAULT NULL NOT SERIALIZED
+  )
+ ATTRIBUTES ALIGNED FORMAT 
+;
+ 
+--- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.T18 TO DB__ROOT
WITH GRANT OPTION;
+
+--- SQL operation complete.`
+
+| *Data*
+| 
+`SQL>SELECT * FROM t17;
+
+C1
+
+----
+
+uÿ   
+
+--- 1 row(s) selected.`
+|
+`SQL>SELECT * FROM t18;
+
+C1
+
+----
+
+uÿ   
+
+--- 1 row(s) selected.`
+| *Result*
+| 
+`SQL>SELECT ASCII(c1) FROM t17;
+
+(EXPR)
+
+----------
+
+       117
+
+--- 1 row(s) selected.`
+|
+`SQL>SELECT ASCII(c1) FROM t18;
+
+(EXPR)
+
+----------
+
+       117
+
+--- 1 row(s) selected.`
+|===
+
+
++
+.Comparison between UTF8 and ISO88591 (with error) -- Table with Two Rows 
+[cols="10%,45%,45%"]
+|===
+| *Column Definition* | *UTF8* | *ISO99591* 
+| *Table Structure*   
+| 
+`SQL>SHOWDDL t22;
+
+CREATE TABLE TRAFODION.SEABASE.T22
+  ( 
+    C1                               CHAR(4 CHARS) CHARACTER SET UTF8 COLLATE
+      DEFAULT DEFAULT NULL NOT SERIALIZED
+  )
+ ATTRIBUTES ALIGNED FORMAT 
+;
+
+
+--- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.T22 TO DB__ROOT
WITH GRANT OPTION;
+
+--- SQL operation complete.`
+
+| 
+`SQL>SHOWDDL t23;   
+ 
+CREATE TABLE TRAFODION.SEABASE.T23
+  ( 
+    C1                               CHAR(4) CHARACTER SET ISO88591 COLLATE
+      DEFAULT DEFAULT NULL NOT SERIALIZED
+  )
+ ATTRIBUTES ALIGNED FORMAT 
+;
+ 
+--- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.T23 TO DB__ROOT
WITH GRANT OPTION;
+
+--- SQL operation complete.`
+
+| *Data*
+| 
+`SQL>SELECT * FROM t22;
+
+C1
+
+----
+
+uÿ
+
+ÿu
+
+--- 2 row(s) selected.`
+|
+`SQL>SELECT * FROM t23;
+
+C1
+
+----
+
+uÿ
+
+ÿu 
+
+--- 2 row(s) selected.`
+| *Result*
+| 
+`SQL>SELECT ASCII(c1) FROM t22;
+
+*** ERROR[8428] The argument to function ASCII is not valid. [2018-05-08 10:01:29]`
+|
+`SQL>SELECT ASCII(c1) FROM t23;
+
+(EXPR)
+
+----------
+
+       117
+       
+       255
+
+--- 2 row(s) selected.`
+|===
+
+
+
+
+
++
+.Comparison between UTF8 and ISO88591 (without error) -- Table with Two Rows
+[cols="10%,45%,45%"]
+|===
+| *Column Definition* | *UTF8* | *ISO99591* 
+| *Table Structure*   
+| 
+`SQL>SHOWDDL t20;
+
+CREATE TABLE TRAFODION.SEABASE.T20
+  ( 
+    C1                               CHAR(4 CHARS) CHARACTER SET UTF8 COLLATE
+      DEFAULT DEFAULT NULL NOT SERIALIZED
+  )
+ ATTRIBUTES ALIGNED FORMAT 
+;
+
+
+-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.T20 TO DB__ROOT
WITH GRANT OPTION;
+
+--- SQL operation complete.`
+
+| 
+`SQL>SHOWDDL t21;   
+ 
+CREATE TABLE TRAFODION.SEABASE.T21
+  ( 
+    C1                               CHAR(4) CHARACTER SET ISO88591 COLLATE
+      DEFAULT DEFAULT NULL NOT SERIALIZED
+  )
+ ATTRIBUTES ALIGNED FORMAT 
+;
+ 
+--- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.T21 TO DB__ROOT
WITH GRANT OPTION;
+
+--- SQL operation complete.`
+
+| *Data*
+| 
+`SQL>SELECT * FROM t20;
+
+C1
+
+----
+
+uÿ
+
+uÿ 
+
+--- 2 row(s) selected.`
+|
+`SQL>SELECT * FROM t21;
+
+C1
+
+----
+
+uÿ
+
+uÿ 
+
+--- 2 row(s) selected.`
+| *Result*
+| 
+`SQL>SELECT ASCII(c1) FROM t20;
+
+(EXPR)
+
+----------
+
+       117
+       
+       117
+
+--- 2 row(s) selected.`
+|
+`SQL>SELECT ASCII(c1) FROM t21;
+
+(EXPR)
+
+----------
+
+       117
+       
+       117
+
+--- 2 row(s) selected.`
+|===
+
 [[examples_of_ascii]]
 === Examples of ASCII
 


Mime
View raw message