SELECT queries fail on Hive table view

[#1]

SYMPTOM

Select statement fails for view with different ordering

FAILING QUERIES:

select id, dept, emp, fname from testview order by id, dept;

select id, emp, dept, fname from testview order by id, dept;

select emp, dept, id, fnamefrom testview order by id, dept;

SUCCESSFUL QUERIES:

select emp, fname, id, dept from testview order by id, dept;

select emp, citystate, fname, dept from testview order by id, dept;

select emp, fname, dept, id from testview order by id, dept;

EXCEPTION:

Caused by: org.apache.hadoop.hive.ql.metadata.HiveException: Error evaluating VALUE._col1
	at org.apache.hadoop.hive.ql.exec.SelectOperator.process(SelectOperator.java:86)
	at org.apache.hadoop.hive.ql.exec.tez.ReduceRecordSource$GroupIterator.next(ReduceRecordSource.java:343)
	... 17 more
Caused by: java.lang.ArrayIndexOutOfBoundsException
	at java.lang.System.arraycopy(Native Method)
	at org.apache.hadoop.io.Text.set(Text.java:225)
	at org.apache.hadoop.hive.serde2.lazybinary.LazyBinaryHiveVarchar.init(LazyBinaryHiveVarchar.java:47)
	at org.apache.hadoop.hive.serde2.lazybinary.LazyBinaryStruct.uncheckedGetField(LazyBinaryStruct.java:267)
	at org.apache.hadoop.hive.serde2.lazybinary.LazyBinaryStruct.getField(LazyBinaryStruct.java:204)
	at org.apache.hadoop.hive.serde2.lazybinary.objectinspector.LazyBinaryStructObjectInspector.getStructFieldData(LazyBinaryStructObjectInspector.java:64)
	at org.apache.hadoop.hive.ql.exec.ExprNodeColumnEvaluator._evaluate(ExprNodeColumnEvaluator.java:98)
	at org.apache.hadoop.hive.ql.exec.ExprNodeEvaluator.evaluate(ExprNodeEvaluator.java:77)
	at org.apache.hadoop.hive.ql.exec.ExprNodeEvaluator.evaluate(ExprNodeEvaluator.java:65)
	at org.apache.hadoop.hive.ql.exec.SelectOperator.process(SelectOperator.java:81)
	... 18 more


2017-05-30 20:12:32,035 [INFO] [TezChild] |exec.FileSinkOperator|: FS[1]: records written - 0
2017-05-30 20:12:32,035 [INFO] [TezChild] |exec.FileSinkOperator|: RECORDS_OUT_0:0, 

ROOT CAUSE

The exception is due to mismatch in the serialization and deserialization on hive table backed upon sequenceinput/sequenceinput file format. The serialization by LazyBinarySerDe from previous MapReduce job used different order of columns. When the current MapReduce job deserialized the intermediate sequence file generated by previous MapReduce job, it will get corrupted data from the deserialization using wrong order of columns by LazyBinaryStruct. The unmatched columns between serialization and deserialization is caused by SelectOperator's Column Pruning ColumnPrunerSelectProc.

WORKAROUND

1] Create an orc table from sequence table as follows create table test_orc stored as orc as select * from testtable;

2] create table view.

REFERENCE:

https://issues.apache.org/jira/browse/HIVE-14564