trafodion-codereview mailing list archives

Github user liuyu000 commented on a diff in the pull request:

    https://github.com/apache/trafodion/pull/1544#discussion_r186333746
  
    --- Diff: docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
---
    @@ -581,12 +581,12 @@ characters. See <<character_value_expressions,Character Value
Expressions>>.
     [[considerations_for_ascii]]
     === Considerations For ASCII
     
    -For a string expression in the UTF8 character set, if the value of the
    +If the value of the
    --- End diff --
    
    @DaveBirdsall Thanks for your help :yum:  
    
    If I have understood you correctly, do you mean:
    
    ASCII function handles string and column differently:
    
    * For a string, ASCII function accepts only single-byte string expression, or else it
returns the following error:
    ```
    *** ERROR[4106] The character set for the operand of function ASCII must be ISO88591.

    ```
    
    For example.
    ```
    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]
    ```
    
    * For a column, the string expression(s) in this column can be only single-byte,
      * For a string expression in the UTF8 character set, the value of the first byte in
the string cannot be greater than 127
      * The first byte in the string can be all characters in the ISO88591 character set
    
      or else it returns the following error:
    ```
    *** ERROR[8428] The argument to function ASCII is not valid.
    ```
    
    For example,
    1. Suppose that we have the UTF8 table _t1_ like this:
    ```
    SQL>showddl t1;
    
    CREATE TABLE TRAFODION.SEABASE.T1
      ( 
        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.T1 TO DB__ROOT
WITH GRANT OPTION;
    
    --- SQL operation complete.
    ```
    
    ```
    SQL>select * from t1;
    
    C1
    ----------------
    ñ               
    
    --- 1 row(s) selected.
    ```
    Select the column c1 and the ASCII function returns the error 8428 because the ñ (UTF8)
is not single-byte .
    ```
    SQL>select ascii(c1) from t1;
    
    *** ERROR[8428] The argument to function ASCII is not valid. [2018-05-07 02:13:42]
    ```
    
    2. Suppose that we have the ISO88591 table _t2_ like this:
    ```
    SQL>showddl t2;   
     
    CREATE TABLE TRAFODION.SEABASE.T2
      ( 
        C1                               CHAR(4) CHARACTER SET ISO88591 COLLATE
          DEFAULT DEFAULT NULL NOT SERIALIZED
      )
     ATTRIBUTES ALIGNED FORMAT 
    ;
     
    -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.T2 TO DB__ROOT
WITH GRANT OPTION;
    
    --- SQL operation complete.
    ```
    
    ```
    SQL>select * from t2;
    
    C1
    ----
    ñ   
    
    --- 1 row(s) selected.
    
    ```
    
    Select the column c1 and return the ASCII code of the first character ñ:
    ```
    SQL>select ascii(c1) from t2;
    
    (EXPR)
    ----------
           241
    
    --- 1 row(s) selected.
    ```
    3. For a UTF8 table contains multiple rows, all string expressions must be single-byte,
or else it returns the following error:
    ```
    *** ERROR[8428] The argument to function ASCII is not valid.
    ```
    
    * Suppose that we have the UTF8 table _t6_ like this:
    ```
    SQL>showddl t6;
    
     CREATE TABLE TRAFODION.SEABASE.T6
      ( 
        C1                               VARCHAR(4 CHARS) CHARACTER SET UTF8
          COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
      )
     ATTRIBUTES ALIGNED FORMAT 
    ;
     
    -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.T6 TO DB__ROOT
WITH GRANT OPTION;
    
    --- SQL operation complete.
    ```
    ```
    SQL>select * from t6;
    
    C1
    --------------------
    a                   
    ñ                   
    
    --- 2 row(s) selected.
    ```
    The ASCII function returns the error 8428 because ñ (UTF8) is not single-byte.
    ```
    SQL>select ascii(c1) from t6;
    
    *** ERROR[8428] The argument to function ASCII is not valid. [2018-05-07 03:11:58]
    ```
    
    * Suppose that we have the UTF8 table _t8_ like this:
    ```
    SQL>showddl t8;
     
    CREATE TABLE TRAFODION.SEABASE.T8
      ( 
        C1                               VARCHAR(4 CHARS) CHARACTER SET UTF8
          COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
      )
     ATTRIBUTES ALIGNED FORMAT 
    ;
     
    -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.T8 TO DB__ROOT
WITH GRANT OPTION;
    
    --- SQL operation complete.
    ```
    ```
    SQL>select * from t8;
    
    C1
    ----------------
    a               
    b                
    
    --- 2 row(s) selected.
    ```
    Select the column c1 and return the ASCII codes of the first characters for all string
expressions in the c1:
    ```
    SQL>select ascii(c1) from t8;
    
    (EXPR)
    ----------
            97
            98
    
    --- 2 row(s) selected.
    ```
    
    4. For a ISO88951 table contains multiple rows, the ASCII function returns the first ASCII
codes for all string expressions in the selected column.
    * Suppose we have the ISO88591 table _t7_ like folliowing:
    ```
    SQL>showddl t7;
    
     CREATE TABLE TRAFODION.SCH.T7
      ( 
        C1                               VARCHAR(4) CHARACTER SET ISO88591 COLLATE
          DEFAULT DEFAULT NULL NOT SERIALIZED
      )
     ATTRIBUTES ALIGNED FORMAT 
    ;
     
    -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.T7 TO DB__ROOT
WITH GRANT OPTION;
    
    --- SQL operation complete.
    ```
    ```
    SQL>select * from t7;
    
    C1
    ----
    a   
    ñ   
    
    --- 2 row(s) selected.
    ```
    ```
    SQL>select ascii(c1) from t7;
    
    (EXPR)
    ----------
            97
           241
    
    --- 2 row(s) selected.
    ```


---