[jira] [Created] (HIVE-9745) predicate evaluation of character fields with spaces and literals with spaces returns unexpected result

["N Campbell (JIRA)" <ji...@apache.org>]

N Campbell created HIVE-9745:
--------------------------------

             Summary: predicate evaluation of character fields with spaces and literals with spaces returns unexpected result
                 Key: HIVE-9745
                 URL: https://issues.apache.org/jira/browse/HIVE-9745
             Project: Hive
          Issue Type: Bug
          Components: SQL
    Affects Versions: 0.14.0
            Reporter: N Campbell


The following query should return 5 rows but Hive returns 3


select rnum, tchar.cchar from tchar where not (  tchar.cchar = ' ' or ( tchar.cchar is null and ' ' is null ))

Consider the following project of the base table


select rnum, tchar.cchar, 
case tchar.cchar when ' ' then 'space' else 'not space' end, 
case when tchar.cchar is null then 'is null' else 'not null' end, case when ' ' is null then 'is null' else 'not null' end
from tchar
order by rnum

Row 0 is a NULL
Row 1 was loaded with a zero length string ''
Row 2 was loaded with a single space ' '

rnum	tchar.cchar	_c2	_c3	_c4
0	<null>	not space	is null	not null
1	                                	not space	not null	not null
2	                                	not space	not null	not null
3	BB                              	not space	not null	not null
4	EE                              	not space	not null	not null
5	FF                              	not space	not null	not null

Explicitly type cast the literal which many  SQL developers would not expect need to do.

select rnum, tchar.cchar, 
case tchar.cchar when cast(' ' as char(1)) then 'space' else 'not space' end, 
case when tchar.cchar is null then 'is null' else 'not null' end, case when cast( ' ' as char(1)) is null then 'is null' else 'not null' end
from tchar
order by rnum

rnum	tchar.cchar	_c2	_c3	_c4
0	<null>	not space	is null	not null
1	                                	space	not null	not null
2	                                	space	not null	not null
3	BB                              	not space	not null	not null
4	EE                              	not space	not null	not null
5	FF                              	not space	not null	not null


create table  if not exists T_TCHAR ( RNUM int , CCHAR char(32 )    )
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' 
 STORED AS TEXTFILE  ;

0|\N
1|
2| 
3|BB
4|EE
5|FF


create table  if not exists TCHAR ( RNUM int , CCHAR char(32 )    )
 STORED AS orc  ;

insert overwrite table TCHAR select * from  T_TCHAR;




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