[
https://issues.apache.org/jira/browse/HIVE-11392?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14645056#comment-14645056
]
Jason Dere commented on HIVE-11392:
-----------------------------------
What's going on here is that in Hive, string literals (like '1 '), are of type
String, not of type char. This affects how the comparison is being done.
Char-to-String comparisons result in the values being converted to the common
type between the two types (string).
So cast('1' as char(4)) = cast('1 ' as string) ends up looking like cast('1' as
string) = cast('1 ' as string). For String (as well as varchar), trailing
spaces is significant during comparison which is why you do not see any
results with that predicate.
As you noticed, the char-to-char comparison shows the correct results (cast('1'
as char(4)) compares as equal to cast('1 ' as char(4))
If string literals were treated as being of type char like they are in other
databases, we would see the expected behavior, but strings/string literals came
first and we might be stuck with that behavior, as changing that would mean a
lot of changes in Hive behavior.
> Trailing spaces in char comparisons
> -----------------------------------
>
> Key: HIVE-11392
> URL: https://issues.apache.org/jira/browse/HIVE-11392
> Project: Hive
> Issue Type: Bug
> Components: Hive
> Affects Versions: 2.0.0
> Reporter: Aihua Xu
>
> Following on HIVE-3745, for char type, hive should ignore trailing spaces for
> comparison while it seems not the case.
> {noformat}
> create table chtest (a char(4));
> insert into chtest values ('1');
> select * from chtest where a='1'; # no whitespace, produces result
> select * from chtest where a='1 '; # 2 spaces, no result
> select * from chtest where a='1 '; # 3 spaces, no result
> select * from chtest where a=cast('1 ' as char(4)); # any amount of spaces,
> cast to char of same length, produces result
> {noformat}
> It's not consistent.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
