hive-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "N Campbell (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (HIVE-9537) string expressions on a fixed length character do not preserve trailing spaces
Date Thu, 19 Feb 2015 13:39:12 GMT

    [ https://issues.apache.org/jira/browse/HIVE-9537?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14327408#comment-14327408
] 

N Campbell commented on HIVE-9537:
----------------------------------

I wouldn't suggest that use MySQL and Postgres alone is ideal to cross check SQL semantics.


Should the Apache implementation for CHAR(n) stay this way going forward I recommend you get
the documentation improved. 

Having seen companies migrate applications across vendors where this sort of thing burns them.
Many may not know what ISO-SQL 20xx states (let alone care) and will presume that their source
vendor(s) have followed the specification etc. 

Given the server side string operations etc, they may as well stay with VARCHAR and know that
trailing spaces are not preserved vs the 'appears' to have spaces when projected but not in
other cases. 

See discussion: http://www.postgresql.org/docs/9.1/static/datatype-character.html

See discussion of MySQL JDBC: https://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html
re padCharsWithSpace

If you apply a <fold> (upper/lower) the type of the result is supposed to be from the
<character value expression>

Length should be the number of characters in the <string value expression> - where number
of  characters is based upon the semantics of the character set of the <string value expression>

If both operands are fixed length character strings, concatenation result is a fixed length
character string with a length equal to the sum of the lengths of the operands where the length
cannot exceed the maximum allowed for a fixed length character string.

Cheers.

> string expressions on a fixed length character do not preserve trailing spaces
> ------------------------------------------------------------------------------
>
>                 Key: HIVE-9537
>                 URL: https://issues.apache.org/jira/browse/HIVE-9537
>             Project: Hive
>          Issue Type: Bug
>          Components: SQL
>            Reporter: N Campbell
>            Assignee: Aihua Xu
>
> When a string expression such as upper or lower is applied to a fixed length column the
trailing spaces of the fixed length character are not preserved.
> {code:sql}
> CREATE TABLE  if not exists TCHAR ( 
> RNUM int, 
> CCHAR char(32)    
> )
> ROW FORMAT DELIMITED 
> FIELDS TERMINATED BY '|' 
> LINES TERMINATED BY '\n' 
> STORED AS TEXTFILE;
> {code}
> {{cchar}} as a {{char(32)}}.
> {code:sql}
> select cchar, concat(cchar, cchar), concat(lower(cchar), cchar), concat(upper(cchar),
cchar) 
> from tchar;
> {code}
> 0|\N
> 1|
> 2| 
> 3|BB
> 4|EE
> 5|FF



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Mime
View raw message