trafodion-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From se...@apache.org
Subject [1/4] incubator-trafodion git commit: [TRAFODION-1920] suppress SQL error during HIVE_SCAN when conv error
Date Sun, 08 May 2016 01:24:26 GMT
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 <ming.liu@esgyn.cn>
Authored: Tue May 3 15:48:03 2016 +0000
Committer: Liu Ming <ming.liu@esgyn.cn>
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


Mime
View raw message