Repository: incubator-trafodion Updated Branches: refs/heads/master 372ea0ba4 -> 140769281
[TRAFODION-1920] suppress SQL error during HIVE_SCAN when conv error Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/67b89f8a Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/67b89f8a Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/67b89f8a Branch: refs/heads/master Commit: 67b89f8ad6c1dc0b30607d98d6e5a141a79ae3e2 Parents: 9b90f23 Author: Liu Ming <[email protected]> Authored: Tue May 3 15:48:03 2016 +0000 Committer: Liu Ming <[email protected]> Committed: Tue May 3 15:48:03 2016 +0000 ---------------------------------------------------------------------- core/sql/executor/ExHdfsScan.h | 1 + core/sql/exp/exp_clause.cpp | 6 ++- core/sql/exp/exp_clause_derived.h | 9 +++-- core/sql/exp/exp_conv.cpp | 13 ++++++- core/sql/generator/GenItemFunc.cpp | 16 ++++++-- core/sql/generator/GenRelScan.cpp | 14 ++++++- core/sql/optimizer/ItemExpr.cpp | 2 + core/sql/optimizer/ItemFunc.h | 8 ++++ core/sql/regress/hive/EXPECTED005 | 55 +++++++++++++++++++++++++++ core/sql/regress/hive/TEST005 | 22 +++++++++++ core/sql/regress/hive/TEST005_a.hive.sql | 16 ++++++++ core/sql/regress/hive/tbl_bad.data | 8 ++++ 12 files changed, 160 insertions(+), 10 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/67b89f8a/core/sql/executor/ExHdfsScan.h ---------------------------------------------------------------------- diff --git a/core/sql/executor/ExHdfsScan.h b/core/sql/executor/ExHdfsScan.h index 3924473..8cd4690 100644 --- a/core/sql/executor/ExHdfsScan.h +++ b/core/sql/executor/ExHdfsScan.h @@ -39,6 +39,7 @@ #include "ExpHbaseInterface.h" #define HIVE_MODE_DOSFORMAT 1 +#define HIVE_MODE_CONV_ERROR_TO_NULL 2 // ----------------------------------------------------------------------- // Classes defined in this file http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/67b89f8a/core/sql/exp/exp_clause.cpp ---------------------------------------------------------------------- diff --git a/core/sql/exp/exp_clause.cpp b/core/sql/exp/exp_clause.cpp index da951d8..2c8c1e4 100644 --- a/core/sql/exp/exp_clause.cpp +++ b/core/sql/exp/exp_clause.cpp @@ -1632,7 +1632,8 @@ ex_conv_clause::ex_conv_clause(OperatorTypeEnum oper_type, Space * space, short num_operands, NABoolean checkTruncErr, NABoolean reverseDataErrorConversionFlag, - NABoolean noStringTruncWarnings) + NABoolean noStringTruncWarnings, + NABoolean convertToNullWhenErrorFlag) : ex_clause (ex_clause::CONV_TYPE, oper_type, num_operands, attr, space), case_index(CONV_UNKNOWN), lastVOAoffset_(0), @@ -1654,6 +1655,9 @@ ex_conv_clause::ex_conv_clause(OperatorTypeEnum oper_type, if (noStringTruncWarnings) setNoTruncationWarningsFlag(); + + if (convertToNullWhenErrorFlag) + flags_ |= CONV_TO_NULL_WHEN_ERROR; set_case_index(); } http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/67b89f8a/core/sql/exp/exp_clause_derived.h ---------------------------------------------------------------------- diff --git a/core/sql/exp/exp_clause_derived.h b/core/sql/exp/exp_clause_derived.h index d1a83b2..41904f6 100644 --- a/core/sql/exp/exp_clause_derived.h +++ b/core/sql/exp/exp_clause_derived.h @@ -1565,7 +1565,8 @@ public: short num_operands = 2, NABoolean checkTruncErr = FALSE, NABoolean reverseDataErrorConversionFlag = FALSE, - NABoolean noStringTruncWarnings = FALSE); + NABoolean noStringTruncWarnings = FALSE, + NABoolean convertToNullWhenErrorFlag = FALSE); // Values used for dataConvErrorFlag. @@ -1720,9 +1721,11 @@ private: TREAT_ALL_SPACES_AS_ZERO = 0x0002, ALLOW_SIGN_IN_INTERVAL = 0x0004, NO_DATETIME_VALIDATION = 0x0008, - + // source is a varchar value which is a pointer to the actual data. - SRC_IS_VARCHAR_PTR = 0x0010 + SRC_IS_VARCHAR_PTR = 0x0010, + // when convert into error, suppress error, move null into convert target + CONV_TO_NULL_WHEN_ERROR = 0x0020 }; // --------------------------------------------------------------------- // Fillers for potential future extensions without changing class size. http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/67b89f8a/core/sql/exp/exp_conv.cpp ---------------------------------------------------------------------- diff --git a/core/sql/exp/exp_conv.cpp b/core/sql/exp/exp_conv.cpp index 2ed1033..14024ec 100644 --- a/core/sql/exp/exp_conv.cpp +++ b/core/sql/exp/exp_conv.cpp @@ -10473,7 +10473,18 @@ ex_expr::exp_return_type ex_conv_clause::eval(char *op_data[], *dataConversionErrorFlag = -(*dataConversionErrorFlag); } } -// return retcode; + + if( retcode != ex_expr::EXPR_OK && ( flags_ & CONV_TO_NULL_WHEN_ERROR ) != 0) + { + //move null to target + if(tgt->getNullFlag()) + { + ExpTupleDesc::setNullValue( op_data[-2*MAX_OPERANDS], + tgt->getNullBitIndex(), + tgt->getTupleFormat() ); + retcode = ex_expr::EXPR_OK; + } + } }; }; // switch http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/67b89f8a/core/sql/generator/GenItemFunc.cpp ---------------------------------------------------------------------- diff --git a/core/sql/generator/GenItemFunc.cpp b/core/sql/generator/GenItemFunc.cpp index a84f2e1..3b115a2 100644 --- a/core/sql/generator/GenItemFunc.cpp +++ b/core/sql/generator/GenItemFunc.cpp @@ -1264,13 +1264,23 @@ short Cast::codeGen(Generator * generator) #pragma warn(1506) // warning elimination } #pragma nowarn(1506) // warning elimination - ex_conv_clause * conv_clause = - new(generator->getSpace()) ex_conv_clause(getOperatorType(), attr, + ex_conv_clause * conv_clause; + if(attr[0]->getNullFlag()) //if target is nullable + conv_clause = new(generator->getSpace()) ex_conv_clause(getOperatorType(), attr, generator->getSpace(), 1 + getArity(), checkTruncationError(), reverseDataErrorConversionFlag_, - noStringTruncationWarnings()); + noStringTruncationWarnings(), + convertNullWhenError()); + else + conv_clause = new(generator->getSpace()) ex_conv_clause(getOperatorType(), attr, + generator->getSpace(), + 1 + getArity(), + checkTruncationError(), + reverseDataErrorConversionFlag_, + noStringTruncationWarnings(), + FALSE); #pragma warn(1506) // warning elimination conv_clause->setTreatAllSpacesAsZero(treatAllSpacesAsZero()); http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/67b89f8a/core/sql/generator/GenRelScan.cpp ---------------------------------------------------------------------- diff --git a/core/sql/generator/GenRelScan.cpp b/core/sql/generator/GenRelScan.cpp index 0b9ad27..827ed90 100644 --- a/core/sql/generator/GenRelScan.cpp +++ b/core/sql/generator/GenRelScan.cpp @@ -204,7 +204,8 @@ int HbaseAccess::createAsciiColAndCastExpr(Generator * generator, castValue = NULL; CollHeap * h = generator->wHeap(); bool needTranslate = FALSE; - + UInt32 hiveScanMode = CmpCommon::getDefaultLong(HIVE_SCAN_SPECIAL_MODE); + // if this is an upshifted datatype, remove the upshift attr. // We dont want to upshift data during retrievals or while building keys. // Data is only upshifted during insert or updates. @@ -263,6 +264,9 @@ int HbaseAccess::createAsciiColAndCastExpr(Generator * generator, asciiValue = new (h) NATypeToItem(asciiType->newCopy(h)); castValue = new(h) Cast(asciiValue, newGivenType); ((Cast*)castValue)->setSrcIsVarcharPtr(TRUE); + + if(( hiveScanMode & 2 ) >0 ) + ((Cast*)castValue)->setConvertNullWhenError(TRUE); if (newGivenType->getTypeQualifier() == NA_INTERVAL_TYPE) ((Cast*)castValue)->setAllowSignInInterval(TRUE); @@ -880,6 +884,12 @@ short FileScan::codeGenForHive(Generator * generator) } // for (ii = 0; ii < hdfsVals; ii++) + UInt32 hiveScanMode = CmpCommon::getDefaultLong(HIVE_SCAN_SPECIAL_MODE); + //enhance pCode to handle this mode in the future + //this is for JIRA 1920 + if((hiveScanMode & 2 ) > 0) //if HIVE_SCAN_SPECIAL_MODE is 2, disable pCode + exp_gen->setPCodeMode(ex_expr::PCODE_NONE); + // Add ascii columns to the MapTable. After this call the MapTable // has ascii values in the work ATP at index asciiTuppIndex. exp_gen->processValIdList( @@ -1148,7 +1158,7 @@ if (hTabStats->isOrcFile()) char * tablename = space->AllocateAndCopyToAlignedSpace(GenGetQualifiedName(getIndexDesc()->getNAFileSet()->getFileSetName()), 0); - UInt32 hiveScanMode = CmpCommon::getDefaultLong(HIVE_SCAN_SPECIAL_MODE); + // create hdfsscan_tdb ComTdbHdfsScan *hdfsscan_tdb = new(space) ComTdbHdfsScan( http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/67b89f8a/core/sql/optimizer/ItemExpr.cpp ---------------------------------------------------------------------- diff --git a/core/sql/optimizer/ItemExpr.cpp b/core/sql/optimizer/ItemExpr.cpp index a76ddb4..04adb28 100644 --- a/core/sql/optimizer/ItemExpr.cpp +++ b/core/sql/optimizer/ItemExpr.cpp @@ -12034,6 +12034,7 @@ Cast::Cast(ItemExpr *val1Ptr, const NAType *type, OperatorTypeEnum otype, } noStringTruncationWarnings_ = noStringTrunWarnings; + convertNullWhenError_ = FALSE; } Cast::Cast(ItemExpr *val1Ptr, ItemExpr *errorOutPtr, const NAType *type, @@ -12047,6 +12048,7 @@ Cast::Cast(ItemExpr *val1Ptr, ItemExpr *errorOutPtr, const NAType *type, { checkForTruncation_ = checkForTrunc; noStringTruncationWarnings_ = noStringTrunWarnings; + convertNullWhenError_ = FALSE; } Cast::~Cast() {} http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/67b89f8a/core/sql/optimizer/ItemFunc.h ---------------------------------------------------------------------- diff --git a/core/sql/optimizer/ItemFunc.h b/core/sql/optimizer/ItemFunc.h index 0728521..f57d1d8 100644 --- a/core/sql/optimizer/ItemFunc.h +++ b/core/sql/optimizer/ItemFunc.h @@ -2438,6 +2438,8 @@ public: NABoolean noStringTruncationWarnings() { return noStringTruncationWarnings_; } + NABoolean convertNullWhenError() { return convertNullWhenError_; } + // get and set for flags_. See enum Flags. NABoolean matchChildType() { return (flags_ & MATCH_CHILD_TYPE) != 0; } void setMatchChildType(NABoolean v) @@ -2471,6 +2473,9 @@ public: void setNoStringTruncationWarnings(NABoolean v) { noStringTruncationWarnings_ = v; } + void setConvertNullWhenError(NABoolean v) + { convertNullWhenError_= v; } + private: enum Flags @@ -2508,6 +2513,9 @@ private: NABoolean noStringTruncationWarnings_; + // If true, convert error will not returned, move null into target + NABoolean convertNullWhenError_; + UInt32 flags_; }; // class Cast http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/67b89f8a/core/sql/regress/hive/EXPECTED005 ---------------------------------------------------------------------- diff --git a/core/sql/regress/hive/EXPECTED005 b/core/sql/regress/hive/EXPECTED005 index 99b0bf6..10e7121 100644 --- a/core/sql/regress/hive/EXPECTED005 +++ b/core/sql/regress/hive/EXPECTED005 @@ -534,4 +534,59 @@ C1 C2 >>cqd HIVE_SCAN_SPECIAL_MODE reset; --- SQL operation complete. +>>select * from tbl_bad; + +*** ERROR[8413] The string argument contains characters that cannot be converted. + +--- 0 row(s) selected. +>>cqd HIVE_SCAN_SPECIAL_MODE '2'; + +--- SQL operation complete. +>>select * from tbl_bad; + +C1 C2 C3 C4 C5 C6 C7 C8 +----------- -------------------- ------------------------- --------------- ------ -------------------------- ------------------------- ------ + + ? ? c ? ? ? ? ? + ? ? c ? ? 2017-01-01 10:10:10.000000 1.01000000000000000E+000 1 + ? ? ? ? ? ? ? + 1 1 averylongstring -1.0000000E+000 0 2017-01-01 10:10:10.000000 1.00010000000000000E+002 1 + 2 2 good 1.1000000E+000 2 2017-01-01 10:10:10.000000 2.00000000000000000E+002 1000 + 3 3 good 1.0000000E+000 2 2017-01-01 10:10:10.000000 2.10000000000000000E+002 10 + ? 4294967295 good 3.3999999E+038 ? 2017-01-01 10:10:10.000000 1.69999999999999968E+308 10 + 0 9999999999 bad ? ? ? ? ? + +--- 8 row(s) selected. +>>cqd HIVE_SCAN_SPECIAL_MODE reset; + +--- SQL operation complete. +>>drop table if exists trafodion.seabase.traf_tbl_bad; + +--- SQL operation complete. +>>create table trafodion.seabase.traf_tbl_bad ( ++>c1 int, ++>c2 largeint, ++>c3 varchar(25), ++>c4 real, ++>c5 smallint, ++>c6 timestamp(6), ++>c7 float(54), ++>c8 smallint ++>); + +--- SQL operation complete. +>>load with no output into trafodion.seabase.traf_tbl_bad select * from tbl_bad; + +*** ERROR[8413] The string argument contains characters that cannot be converted. + +--- 0 row(s) loaded. +>>cqd HIVE_SCAN_SPECIAL_MODE '2'; + +--- SQL operation complete. +>>load with no output into trafodion.seabase.traf_tbl_bad select * from tbl_bad; + +--- 8 row(s) loaded. +>>cqd HIVE_SCAN_SPECIAL_MODE reset; + +--- SQL operation complete. >>log; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/67b89f8a/core/sql/regress/hive/TEST005 ---------------------------------------------------------------------- diff --git a/core/sql/regress/hive/TEST005 b/core/sql/regress/hive/TEST005 index 6e46c64..cebeff4 100644 --- a/core/sql/regress/hive/TEST005 +++ b/core/sql/regress/hive/TEST005 @@ -33,6 +33,7 @@ sh regrhadoop.ksh fs -mkdir /user/hive/exttables/tbl_type; sh regrhadoop.ksh fs -mkdir /user/hive/exttables/tbl_gbk; sh regrhadoop.ksh fs -mkdir /user/hive/exttables/tbl_dos; sh regrhadoop.ksh fs -mkdir /user/hive/exttables/tbl_dos_num; +sh regrhadoop.ksh fs -mkdir /user/hive/exttables/tbl_bad; --empty folders sh regrhadoop.ksh fs -rm /user/hive/exttables/customer_ddl/*; sh regrhadoop.ksh fs -rm /user/hive/exttables/customer_temp/*; @@ -41,6 +42,7 @@ sh regrhadoop.ksh fs -rm /user/hive/exttables/tbl_type/*; sh regrhadoop.ksh fs -rm /user/hive/exttables/tbl_gbk/*; sh regrhadoop.ksh fs -rm /user/hive/exttables/tbl_dos/*; sh regrhadoop.ksh fs -rm /user/hive/exttables/tbl_dos_num/*; +sh regrhadoop.ksh fs -rm /user/hive/exttables/tbl_bad/*; --- setup Hive tables sh regrhive.ksh -v -f $REGRTSTDIR/TEST005_a.hive.sql; @@ -49,6 +51,7 @@ sh regrhadoop.ksh fs -put $REGRTSTDIR/tbl_type.data /user/hive/exttables/tbl_typ sh regrhadoop.ksh fs -put $REGRTSTDIR/tbl_gbk.data /user/hive/exttables/tbl_gbk; sh regrhadoop.ksh fs -put $REGRTSTDIR/tbl_dos.data /user/hive/exttables/tbl_dos; sh regrhadoop.ksh fs -put $REGRTSTDIR/tbl_dos_numeric.data /user/hive/exttables/tbl_dos_num; +sh regrhadoop.ksh fs -put $REGRTSTDIR/tbl_bad.data /user/hive/exttables/tbl_bad; log LOG005 clear; @@ -243,4 +246,23 @@ cqd HIVE_SCAN_SPECIAL_MODE '1'; load with no output into trafodion.seabase.tbl_dos_num select * from tbl_dos_num; select * from trafodion.seabase.tbl_dos_num; cqd HIVE_SCAN_SPECIAL_MODE reset; +select * from tbl_bad; +cqd HIVE_SCAN_SPECIAL_MODE '2'; +select * from tbl_bad; +cqd HIVE_SCAN_SPECIAL_MODE reset; +drop table if exists trafodion.seabase.traf_tbl_bad; +create table trafodion.seabase.traf_tbl_bad ( +c1 int, +c2 largeint, +c3 varchar(25), +c4 real, +c5 smallint, +c6 timestamp(6), +c7 float(54), +c8 smallint +); +load with no output into trafodion.seabase.traf_tbl_bad select * from tbl_bad; +cqd HIVE_SCAN_SPECIAL_MODE '2'; +load with no output into trafodion.seabase.traf_tbl_bad select * from tbl_bad; +cqd HIVE_SCAN_SPECIAL_MODE reset; log; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/67b89f8a/core/sql/regress/hive/TEST005_a.hive.sql ---------------------------------------------------------------------- diff --git a/core/sql/regress/hive/TEST005_a.hive.sql b/core/sql/regress/hive/TEST005_a.hive.sql index 22662bd..20ecd31 100644 --- a/core/sql/regress/hive/TEST005_a.hive.sql +++ b/core/sql/regress/hive/TEST005_a.hive.sql @@ -181,3 +181,19 @@ CREATE external TABLE tbl_dos_num( row format delimited fields terminated by '|' location '/user/hive/exttables/tbl_dos_num' ; + +drop table tbl_bad; +CREATE EXTERNAL TABLE tbl_bad ( +c1 int, +c2 bigint, +c3 string, +c4 float, +c5 smallint, +c6 timestamp, +c7 double, +c8 tinyint +) +ROW FORMAT DELIMITED + FIELDS TERMINATED BY '|' +LOCATION +'/user/hive/exttables/tbl_bad'; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/67b89f8a/core/sql/regress/hive/tbl_bad.data ---------------------------------------------------------------------- diff --git a/core/sql/regress/hive/tbl_bad.data b/core/sql/regress/hive/tbl_bad.data new file mode 100644 index 0000000..b2f3a4c --- /dev/null +++ b/core/sql/regress/hive/tbl_bad.data @@ -0,0 +1,8 @@ +a|b|c|d|e|f|g| +a||c| |e|2017-01-01 10:10:10|1.01|1 + | | | | | | | +1|1|averylongstring|-1|0|2017-01-01 10:10:10.000|100.01|1 +2|2|good|1.1|2|2017-01-01 10:10:10.000000|2E+002|1000 +3|3|good|1|2|2017-01-01 10:10:10|2.1E+002|10 +4294967295|4294967295|good|3.40E+038|65535|2017-01-01 10:10:10|1.7E+308|10 +0|9999999999|bad|4.0E+038|65536|2017-15-01 10:10:10|2.1E+309|65537
