Qfyilyi

I am creating a table and inserting data like below

CREATE TABLE `mydb.mytable`(

  `seq_num` decimal(18,0),

  `ins_upd_flag` char(1)

  )

stored as parquet;



INSERT OVERWRITE TABLE mydb.mytable

SELECT 10457 SEQ_NUM,'I' INS_UPD_FLAG UNION ALL 

SELECT 10462,'I' UNION ALL 

SELECT 10461,'I' UNION ALL 

SELECT 10460,'I' UNION ALL 

SELECT 10459,'I' UNION ALL 

SELECT 10456,'I' UNION ALL 

SELECT 10458,'I';

As you can see above, the second column value is just 'I' and it does not have any extra characters.

When I run the following query, it does not result anything

SELECT T.*, length(INS_UPD_FLAG), length(trim(INS_UPD_FLAG))

FROM mydb.mytable T

WHERE SEQ_NUM <> 0  OR INS_UPD_FLAG <> 'I' ;

However, if I run the below query it returns all the inserted rows as expected

SELECT T.*, length(INS_UPD_FLAG), length(trim(INS_UPD_FLAG)) -- both length =1

FROM mydb.mytable T

WHERE SEQ_NUM <> 0 OR TRIM(INS_UPD_FLAG) <> 'I' ;

Any explanation on why this is happening and how to resolve the same? I need the results without using trim

Please note that this issue is happening only with OR condition. If I run the below query, I am getting results

SELECT T.*, length(INS_UPD_FLAG), length(trim(INS_UPD_FLAG))

FROM mydb.mytable T

WHERE SEQ_NUM <> 0 and  INS_UPD_FLAG = 'I' ;

Also, if I create table with ORC or Text Format, I am getting results without trim.

I managed to re-pro your issue and getting same results as you have explained.

I ran a query to see what exactly your where condition is producing:

SELECT

  T.*,

  length(INS_UPD_FLAG),

  length(trim(INS_UPD_FLAG)),

  SEQ_NUM <> 0,

  INS_UPD_FLAG = 'I',

  SEQ_NUM <> 0 or INS_UPD_FLAG = 'I'

FROM mydb.mytable T

and results are as following:

|seq_num|ins_upd_flag|_c1|_c2|_c3 |_c4 |_c5 |

|10457  |I           |1  |1  |true|true|true|

|10462  |I           |1  |1  |true|true|true|

|10461  |I           |1  |1  |true|true|true|

|10460  |I           |1  |1  |true|true|true|

|10459  |I           |1  |1  |true|true|true|

|10456  |I           |1  |1  |true|true|true|

|10458  |I           |1  |1  |true|true|true|

As you can see it is returning true for your join condition.

Out of curiosity, I wrapped the where condition around () and got the results without trim.

SELECT

  T.*,

  length(INS_UPD_FLAG),

  length(trim(INS_UPD_FLAG)),

  SEQ_NUM <> 0,

  INS_UPD_FLAG = 'I',

  SEQ_NUM <> 0 or INS_UPD_FLAG = 'I'

FROM mydb.mytable T

where (SEQ_NUM <> 0 or INS_UPD_FLAG = 'I')

Results: Same as previous query.

I am using Hive 1.2.1000 via Hue 2.6.1-227.

I don't know what magic () is doing and why query is not working without it.

may i know reason why you dont want to use the trim function.As hadoop stores the data in files there is alot of chances that it can add the space while inserting.i faced this issue a lot of time while using parque format.it depends sometimes you face it some time you wont face it.
whatever it is can you run you query only based on this condition
SELECT T.*, length(INS_UPD_FLAG), length(trim(INS_UPD_FLAG))
FROM mydb.mytable T
WHERE SEQ_NUM <> 0

if it is not returning anything the way data inserted is wrong..please try it let me know

The first thing is that I am not sure why you aren't seeing the expected results. I ran a very similar test, below, and received the expected results. What version of Hive are you on? I did the same tests on hive-1.1.0+cdh5.13.3+1307 (Cloudera distro w/v5.13):

CREATE TABLE test_cond_parq

(

 seq_num decimal(10,0),

 ind string

)

STORED AS PARQUET;



INSERT INTO TABLE test_cond_parq

SELECT 10457, 'I' UNION ALL

SELECT 10458, 'I'

;

Then I ran the following query, similar to yours above...

SELECT *, LENGTH(ind) len_ind, LENGTH(TRIM(ind)) len_trim_ind

FROM test_cond_parq

WHERE seq_num <> 0 OR ind <> 'I';

This returned the following results:

10457 | I | 1 | 1

10458 | I | 1 | 1