[TRAFODION-1164] LP Bug: 1443463 - '\N' and empty string are both treated as null when selecting hive table.

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Fixed
• Fix Version/s: 2.1-incubating
• Component/s: sql-exe
• Labels: hive null
• External issue URL: https://bugs.launchpad.net/bugs/1443463
• Assignee: Anoop Sharma
• Reporter: Howard Qin
• Created: 22/Jul/15 18:20
• Updated: 15/Sep/18 07:30
• Resolved: 27/May/16 17:33

Description

When loading/selecting from hive table, we treat both empty string and '\N' as null, this doesn't conform with hive behavior, in which, user can configure what null character is.

The issue can be explain in following steps:
1)
>select * from t1;
A B
----------- ------------------------------------------------------------------------------------------------------------------------
4
1 ?
2 fsdf
3 hhah

2)
unload with null_string '\N' into '/nulltest' select * from t1;
-bash-4.1$ swhdfs dfs -cat /nulltest/file0-20150410015053-828
4|
1|\N
2|fsdf
3|hhah

3)
hive> create external table nulltest ( A int, B string )
> row format delimited fields terminated by '|'
> location '/nulltest'
> tblproperties ('serialization.null.format' = '
N');

hive> select * from nulltest;
OK
nulltest.a nulltest.b
4  <-- empty string
1 NULL
2 fsdf
3 hhah

4)
>>select * from hive.hive.nulltest;
A B
----------- ------------------------------------------------------------------------------------------------------------------------
4 ? <-- empty string is considered null.
1 ?
2 fsdf
3 hhah

>select * from t1;
A B
----------- ------------------------------------------------------------------------------------------------------------------------
4 <-- should consist with t1 and hive.
1 ?
2 fsdf
3 hhah
Assigned to LaunchPad User khaled Bouaziz