I created an external table whose definition contains a varchar column.
Create table test1_dat ( SUBNO bigint , AFFILCODE varchar(5) , EFFDT string )) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/data/testdb/test1_dat';
The following Hql statement returns no records.
select * from jaffilm0_dat where rtrim(AFFILCODE)='LTC' limit 10 ;
The following Hql statements return correct records.
select * from jaffilm0_dat where rtrim(AFFILCODE)='LTC' limit 10 ; select * from jaffilm0_dat where AFFILCODE='LTC ' limit 10 ; -- adding two trailing spaces
Basically, AFFILCODE is treated as char(5) by Hive. Is this supposed to happen? Is there any issue with my table definition script?
I just found out that the flat file behind the table has extra spaces in that AFFILCODE column. So rtrim has to be used all the time when querying against that column. Sorry for the confusion.