[07/50] [abbrv] hive git commit: HIVE-13570: Some queries with Union all fail when CBO is off (Yongzhi Chen, reviewed by Chaoyu Tang)

[jdere]

http://git-wip-us.apache.org/repos/asf/hive/blob/413645d1/ql/src/test/results/clientpositive/union_offcbo.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/union_offcbo.q.out 
b/ql/src/test/results/clientpositive/union_offcbo.q.out
new file mode 100644
index 0000000..7eff464
--- /dev/null
+++ b/ql/src/test/results/clientpositive/union_offcbo.q.out
@@ -0,0 +1,1779 @@
+PREHOOK: query: DROP TABLE IF EXISTS ttest1
+PREHOOK: type: DROPTABLE
+POSTHOOK: query: DROP TABLE IF EXISTS ttest1
+POSTHOOK: type: DROPTABLE
+PREHOOK: query: DROP TABLE IF EXISTS ttest2
+PREHOOK: type: DROPTABLE
+POSTHOOK: query: DROP TABLE IF EXISTS ttest2
+POSTHOOK: type: DROPTABLE
+PREHOOK: query: CREATE TABLE ttest1 (
+  `id1` bigint COMMENT 'from deserializer',
+  `ts1` string COMMENT 'from deserializer',
+  `dt1` string COMMENT 'from deserializer',
+  `dt2` string COMMENT 'from deserializer',
+  `ac1` string COMMENT 'from deserializer',
+  `kd1` string COMMENT 'from deserializer',
+  `sts` string COMMENT 'from deserializer',
+  `at1` bigint COMMENT 'from deserializer')
+PREHOOK: type: CREATETABLE
+PREHOOK: Output: database:default
+PREHOOK: Output: default@ttest1
+POSTHOOK: query: CREATE TABLE ttest1 (
+  `id1` bigint COMMENT 'from deserializer',
+  `ts1` string COMMENT 'from deserializer',
+  `dt1` string COMMENT 'from deserializer',
+  `dt2` string COMMENT 'from deserializer',
+  `ac1` string COMMENT 'from deserializer',
+  `kd1` string COMMENT 'from deserializer',
+  `sts` string COMMENT 'from deserializer',
+  `at1` bigint COMMENT 'from deserializer')
+POSTHOOK: type: CREATETABLE
+POSTHOOK: Output: database:default
+POSTHOOK: Output: default@ttest1
+PREHOOK: query: CREATE TABLE ttest2 (
+  `id1` bigint,
+  `ts1` string,
+  `dt1` string,
+  `dt2` string,
+  `ac1` string,
+  `kd1` string,
+  `sts` string,
+  `at1` bigint,
+  `khash` string,
+  `rhash` string)
+PREHOOK: type: CREATETABLE
+PREHOOK: Output: database:default
+PREHOOK: Output: default@ttest2
+POSTHOOK: query: CREATE TABLE ttest2 (
+  `id1` bigint,
+  `ts1` string,
+  `dt1` string,
+  `dt2` string,
+  `ac1` string,
+  `kd1` string,
+  `sts` string,
+  `at1` bigint,
+  `khash` string,
+  `rhash` string)
+POSTHOOK: type: CREATETABLE
+POSTHOOK: Output: database:default
+POSTHOOK: Output: default@ttest2
+PREHOOK: query: explain SELECT
+  A2.id1, A2.sts,A2.at1,
+    CASE WHEN FLAG = 'A_INS' THEN date_add('2015-11-20', 1) ELSE '2015-11-20' 
END dt1
+        ,A2.dt2
+        ,A2.khash
+        ,A2.rhash
+        ,A2.FLAG
+  FROM (
+   SELECT
+  A2.id1, A2.sts,A2.at1
+        ,A2.dt1
+        ,A2.dt2
+        ,A2.khash
+        ,A2.rhash
+    ,CASE
+     WHEN A2.khash IS NOT NULL
+     AND A1.khash IS NULL
+         AND A2.dt1 >= '2016-02-05'
+     THEN 'DEL'
+
+         WHEN A2.khash IS NOT NULL
+     AND A1.khash IS NULL
+     AND A2.dt1 <= '2016-02-05'
+     THEN 'RET'
+
+     WHEN
+     (
+     A2.khash = A1.khash
+     AND A2.rhash <> A1.rhash
+     )
+     THEN 'A_INS'
+
+     ELSE 'NA'
+    END FLAG
+        FROM (
+                SELECT *
+                        
,reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(id1)) 
khash
+                        
,reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(at1)) 
rhash
+                FROM ttest1
+                WHERE ts1 = '2015-11-20'
+                ) A1
+        FULL OUTER JOIN (
+                SELECT *
+                FROM ttest2
+                WHERE '2015-11-20' BETWEEN dt1 AND dt2
+                ) A2
+     ON A1.khash = A2.khash
+     WHERE NOT (
+        NVL(A1.khash, - 1) = NVL(A2.khash, - 1)
+        AND NVL(A1.rhash, - 1) = NVL(A2.rhash, - 1)
+        )
+     AND A2.khash IS NOT NULL
+
+   UNION ALL
+
+   SELECT A1.id1, A1.sts,A1.at1
+        ,A1.dt1
+                , '2099-12-31' dt2
+        ,A1.khash
+        ,A1.rhash
+     ,CASE WHEN A2.khash IS NOT NULL
+     AND A1.khash IS NULL
+     AND A2.ts1 <= A1.ts1
+     THEN 'DEL'
+
+     WHEN ( A2.khash IS NULL AND A1.khash IS NOT NULL )
+     OR ( A2.khash = A1.khash AND A2.rhash <> A1.rhash ) THEN 'INS' ELSE 'NA' 
END FLAG
+   FROM (
+     SELECT *
+        ,reflect('org.apache.commons.codec.digest.DigestUtils', 
'sha256Hex',concat(id1)) khash
+        
,reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(at1)) 
rhash
+      FROM ttest1
+     WHERE ts1 = '2015-11-20'
+    ) A1
+   FULL OUTER JOIN (
+        SELECT *
+        FROM ttest2
+        WHERE '2015-11-20' BETWEEN dt1
+          AND dt2
+       ) A2 ON A1.khash = A2.khash
+       WHERE NOT (
+          NVL(A1.khash, - 1) = NVL(A2.khash, - 1)
+          AND NVL(A1.rhash, - 1) = NVL(A2.rhash, - 1)
+          )
+       AND A1.khash IS NOT NULL
+   ) A2
+   where a2.flag <> 'RET'
+PREHOOK: type: QUERY
+POSTHOOK: query: explain SELECT
+  A2.id1, A2.sts,A2.at1,
+    CASE WHEN FLAG = 'A_INS' THEN date_add('2015-11-20', 1) ELSE '2015-11-20' 
END dt1
+        ,A2.dt2
+        ,A2.khash
+        ,A2.rhash
+        ,A2.FLAG
+  FROM (
+   SELECT
+  A2.id1, A2.sts,A2.at1
+        ,A2.dt1
+        ,A2.dt2
+        ,A2.khash
+        ,A2.rhash
+    ,CASE
+     WHEN A2.khash IS NOT NULL
+     AND A1.khash IS NULL
+         AND A2.dt1 >= '2016-02-05'
+     THEN 'DEL'
+
+         WHEN A2.khash IS NOT NULL
+     AND A1.khash IS NULL
+     AND A2.dt1 <= '2016-02-05'
+     THEN 'RET'
+
+     WHEN
+     (
+     A2.khash = A1.khash
+     AND A2.rhash <> A1.rhash
+     )
+     THEN 'A_INS'
+
+     ELSE 'NA'
+    END FLAG
+        FROM (
+                SELECT *
+                        
,reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(id1)) 
khash
+                        
,reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(at1)) 
rhash
+                FROM ttest1
+                WHERE ts1 = '2015-11-20'
+                ) A1
+        FULL OUTER JOIN (
+                SELECT *
+                FROM ttest2
+                WHERE '2015-11-20' BETWEEN dt1 AND dt2
+                ) A2
+     ON A1.khash = A2.khash
+     WHERE NOT (
+        NVL(A1.khash, - 1) = NVL(A2.khash, - 1)
+        AND NVL(A1.rhash, - 1) = NVL(A2.rhash, - 1)
+        )
+     AND A2.khash IS NOT NULL
+
+   UNION ALL
+
+   SELECT A1.id1, A1.sts,A1.at1
+        ,A1.dt1
+                , '2099-12-31' dt2
+        ,A1.khash
+        ,A1.rhash
+     ,CASE WHEN A2.khash IS NOT NULL
+     AND A1.khash IS NULL
+     AND A2.ts1 <= A1.ts1
+     THEN 'DEL'
+
+     WHEN ( A2.khash IS NULL AND A1.khash IS NOT NULL )
+     OR ( A2.khash = A1.khash AND A2.rhash <> A1.rhash ) THEN 'INS' ELSE 'NA' 
END FLAG
+   FROM (
+     SELECT *
+        ,reflect('org.apache.commons.codec.digest.DigestUtils', 
'sha256Hex',concat(id1)) khash
+        
,reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(at1)) 
rhash
+      FROM ttest1
+     WHERE ts1 = '2015-11-20'
+    ) A1
+   FULL OUTER JOIN (
+        SELECT *
+        FROM ttest2
+        WHERE '2015-11-20' BETWEEN dt1
+          AND dt2
+       ) A2 ON A1.khash = A2.khash
+       WHERE NOT (
+          NVL(A1.khash, - 1) = NVL(A2.khash, - 1)
+          AND NVL(A1.rhash, - 1) = NVL(A2.rhash, - 1)
+          )
+       AND A1.khash IS NOT NULL
+   ) A2
+   where a2.flag <> 'RET'
+POSTHOOK: type: QUERY
+STAGE DEPENDENCIES:
+  Stage-1 is a root stage
+  Stage-2 depends on stages: Stage-1, Stage-4
+  Stage-4 is a root stage
+  Stage-0 depends on stages: Stage-2
+
+STAGE PLANS:
+  Stage: Stage-1
+    Map Reduce
+      Map Operator Tree:
+          TableScan
+            alias: ttest1
+            Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+            Filter Operator
+              predicate: (ts1 = '2015-11-20') (type: boolean)
+              Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+              Select Operator
+                expressions: 
reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(id1)) 
(type: string), 
reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(at1)) 
(type: string)
+                outputColumnNames: _col8, _col9
+                Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                Reduce Output Operator
+                  key expressions: _col8 (type: string)
+                  sort order: +
+                  Map-reduce partition columns: _col8 (type: string)
+                  Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                  value expressions: _col9 (type: string)
+          TableScan
+            alias: ttest2
+            Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+            Filter Operator
+              predicate: '2015-11-20' BETWEEN dt1 AND dt2 (type: boolean)
+              Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+              Select Operator
+                expressions: id1 (type: bigint), dt1 (type: string), dt2 
(type: string), sts (type: string), at1 (type: bigint), khash (type: string), 
rhash (type: string)
+                outputColumnNames: _col0, _col2, _col3, _col6, _col7, _col8, 
_col9
+                Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                Reduce Output Operator
+                  key expressions: _col8 (type: string)
+                  sort order: +
+                  Map-reduce partition columns: _col8 (type: string)
+                  Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                  value expressions: _col0 (type: bigint), _col2 (type: 
string), _col3 (type: string), _col6 (type: string), _col7 (type: bigint), 
_col9 (type: string)
+      Reduce Operator Tree:
+        Join Operator
+          condition map:
+               Outer Join 0 to 1
+          keys:
+            0 _col8 (type: string)
+            1 _col8 (type: string)
+          outputColumnNames: _col8, _col9, _col10, _col12, _col13, _col16, 
_col17, _col18, _col19
+          Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+          Filter Operator
+            predicate: (((NVL(_col8,-1) <> NVL(_col18,-1)) or (NVL(_col9,-1) 
<> NVL(_col19,-1))) and _col18 is not null) (type: boolean)
+            Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+            Select Operator
+              expressions: _col10 (type: bigint), _col16 (type: string), 
_col17 (type: bigint), _col13 (type: string), _col18 (type: string), _col19 
(type: string), CASE WHEN ((_col18 is not null and _col8 is null and (_col12 >= 
'2016-02-05'))) THEN ('DEL') WHEN ((_col18 is not null and _col8 is null and 
(_col12 <= '2016-02-05'))) THEN ('RET') WHEN (((_col18 = _col8) and (_col19 <> 
_col9))) THEN ('A_INS') ELSE ('NA') END (type: string)
+              outputColumnNames: _col0, _col1, _col2, _col4, _col5, _col6, 
_col7
+              Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+              Filter Operator
+                predicate: (_col7 <> 'RET') (type: boolean)
+                Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                File Output Operator
+                  compressed: false
+                  table:
+                      input format: 
org.apache.hadoop.mapred.SequenceFileInputFormat
+                      output format: 
org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
+                      serde: 
org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe
+
+  Stage: Stage-2
+    Map Reduce
+      Map Operator Tree:
+          TableScan
+            Union
+              Statistics: Num rows: 2 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+              Select Operator
+                expressions: _col0 (type: bigint), _col1 (type: string), _col2 
(type: bigint), CASE WHEN ((_col7 = 'A_INS')) THEN ('2015-11-21') ELSE 
('2015-11-20') END (type: string), _col4 (type: string), _col5 (type: string), 
_col6 (type: string), _col7 (type: string)
+                outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, 
_col6, _col7
+                Statistics: Num rows: 2 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                File Output Operator
+                  compressed: false
+                  Statistics: Num rows: 2 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                  table:
+                      input format: 
org.apache.hadoop.mapred.SequenceFileInputFormat
+                      output format: 
org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
+                      serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
+          TableScan
+            Union
+              Statistics: Num rows: 2 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+              Select Operator
+                expressions: _col0 (type: bigint), _col1 (type: string), _col2 
(type: bigint), CASE WHEN ((_col7 = 'A_INS')) THEN ('2015-11-21') ELSE 
('2015-11-20') END (type: string), _col4 (type: string), _col5 (type: string), 
_col6 (type: string), _col7 (type: string)
+                outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, 
_col6, _col7
+                Statistics: Num rows: 2 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                File Output Operator
+                  compressed: false
+                  Statistics: Num rows: 2 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                  table:
+                      input format: 
org.apache.hadoop.mapred.SequenceFileInputFormat
+                      output format: 
org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
+                      serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
+
+  Stage: Stage-4
+    Map Reduce
+      Map Operator Tree:
+          TableScan
+            alias: ttest1
+            Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+            Filter Operator
+              predicate: (ts1 = '2015-11-20') (type: boolean)
+              Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+              Select Operator
+                expressions: id1 (type: bigint), '2015-11-20' (type: string), 
sts (type: string), at1 (type: bigint), 
reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(id1)) 
(type: string), 
reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(at1)) 
(type: string)
+                outputColumnNames: _col0, _col1, _col6, _col7, _col8, _col9
+                Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                Reduce Output Operator
+                  key expressions: _col8 (type: string)
+                  sort order: +
+                  Map-reduce partition columns: _col8 (type: string)
+                  Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                  value expressions: _col0 (type: bigint), _col1 (type: 
string), _col6 (type: string), _col7 (type: bigint), _col9 (type: string)
+          TableScan
+            alias: ttest2
+            Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+            Filter Operator
+              predicate: '2015-11-20' BETWEEN dt1 AND dt2 (type: boolean)
+              Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+              Select Operator
+                expressions: ts1 (type: string), khash (type: string), rhash 
(type: string)
+                outputColumnNames: _col1, _col8, _col9
+                Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                Reduce Output Operator
+                  key expressions: _col8 (type: string)
+                  sort order: +
+                  Map-reduce partition columns: _col8 (type: string)
+                  Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                  value expressions: _col1 (type: string), _col9 (type: string)
+      Reduce Operator Tree:
+        Join Operator
+          condition map:
+               Outer Join 0 to 1
+          keys:
+            0 _col8 (type: string)
+            1 _col8 (type: string)
+          outputColumnNames: _col0, _col1, _col6, _col7, _col8, _col9, _col11, 
_col18, _col19
+          Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+          Filter Operator
+            predicate: (((NVL(_col8,-1) <> NVL(_col18,-1)) or (NVL(_col9,-1) 
<> NVL(_col19,-1))) and _col8 is not null) (type: boolean)
+            Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+            Select Operator
+              expressions: _col0 (type: bigint), _col6 (type: string), _col7 
(type: bigint), '2099-12-31' (type: string), _col8 (type: string), _col9 (type: 
string), CASE WHEN ((_col18 is not null and _col8 is null and (_col11 <= 
_col1))) THEN ('DEL') WHEN (((_col18 is null and _col8 is not null) or ((_col18 
= _col8) and (_col19 <> _col9)))) THEN ('INS') ELSE ('NA') END (type: string)
+              outputColumnNames: _col0, _col1, _col2, _col4, _col5, _col6, 
_col7
+              Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+              Filter Operator
+                predicate: (_col7 <> 'RET') (type: boolean)
+                Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                File Output Operator
+                  compressed: false
+                  table:
+                      input format: 
org.apache.hadoop.mapred.SequenceFileInputFormat
+                      output format: 
org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
+                      serde: 
org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe
+
+  Stage: Stage-0
+    Fetch Operator
+      limit: -1
+      Processor Tree:
+        ListSink
+
+PREHOOK: query: explain SELECT
+  A2.id1, A2.sts,A2.at1,
+    CASE WHEN FLAG = 'A_INS' THEN date_add('2015-11-20', 1) ELSE '2015-11-20' 
END dt1
+        ,A2.dt2
+        ,A2.khash
+        ,A2.rhash
+        ,A2.FLAG
+  FROM (
+   SELECT
+  A2.id1, A2.sts,A2.at1
+        ,A2.dt1
+        ,A2.dt2
+        ,A2.khash
+        ,A2.rhash
+    ,CASE
+     WHEN A2.khash IS NOT NULL
+     AND A1.khash IS NULL
+         AND A2.dt1 >= '2016-02-05'
+     THEN 'DEL'
+
+         WHEN A2.khash IS NOT NULL
+     AND A1.khash IS NULL
+     AND A2.dt1 <= '2016-02-05'
+     THEN 'RET'
+
+     WHEN
+     (
+     A2.khash = A1.khash
+     AND A2.rhash <> A1.rhash
+     )
+     THEN 'A_INS'
+
+     ELSE 'NA'
+    END FLAG
+        FROM (
+                SELECT *
+                        
,reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(id1)) 
khash
+                        
,reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(at1)) 
rhash
+                FROM ttest1
+                WHERE ts1 = '2015-11-20'
+                ) A1
+        FULL OUTER JOIN (
+                SELECT *
+                FROM ttest2
+                WHERE '2015-11-20' BETWEEN dt1 AND dt2
+                ) A2
+     ON A1.khash = A2.khash
+     WHERE NOT (
+        NVL(A1.khash, - 1) = NVL(A2.khash, - 1)
+        AND NVL(A1.rhash, - 1) = NVL(A2.rhash, - 1)
+        )
+     AND A2.khash IS NOT NULL
+
+   UNION ALL
+
+   SELECT A1.id1, A1.sts,A1.at1
+        ,A1.dt1
+                , '2099-12-31' dt2
+        ,A1.khash
+        ,A1.rhash
+     ,CASE WHEN A2.khash IS NOT NULL
+     AND A1.khash IS NULL
+     AND A2.ts1 <= A1.ts1
+     THEN 'DEL'
+
+     WHEN ( A2.khash IS NULL AND A1.khash IS NOT NULL )
+     OR ( A2.khash = A1.khash AND A2.rhash <> A1.rhash ) THEN 'INS' ELSE 'NA' 
END FLAG
+   FROM (
+     SELECT *
+        ,reflect('org.apache.commons.codec.digest.DigestUtils', 
'sha256Hex',concat(id1)) khash
+        
,reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(at1)) 
rhash
+      FROM ttest1
+     WHERE ts1 = '2015-11-20'
+    ) A1
+   FULL OUTER JOIN (
+        SELECT *
+        FROM ttest2
+        WHERE '2015-11-20' BETWEEN dt1
+          AND dt2
+       ) A2 ON A1.khash = A2.khash
+       WHERE NOT (
+          NVL(A1.khash, - 1) = NVL(A2.khash, - 1)
+          AND NVL(A1.rhash, - 1) = NVL(A2.rhash, - 1)
+          )
+       AND A1.khash IS NOT NULL
+   ) A2
+   where a2.flag <> 'RET'
+PREHOOK: type: QUERY
+POSTHOOK: query: explain SELECT
+  A2.id1, A2.sts,A2.at1,
+    CASE WHEN FLAG = 'A_INS' THEN date_add('2015-11-20', 1) ELSE '2015-11-20' 
END dt1
+        ,A2.dt2
+        ,A2.khash
+        ,A2.rhash
+        ,A2.FLAG
+  FROM (
+   SELECT
+  A2.id1, A2.sts,A2.at1
+        ,A2.dt1
+        ,A2.dt2
+        ,A2.khash
+        ,A2.rhash
+    ,CASE
+     WHEN A2.khash IS NOT NULL
+     AND A1.khash IS NULL
+         AND A2.dt1 >= '2016-02-05'
+     THEN 'DEL'
+
+         WHEN A2.khash IS NOT NULL
+     AND A1.khash IS NULL
+     AND A2.dt1 <= '2016-02-05'
+     THEN 'RET'
+
+     WHEN
+     (
+     A2.khash = A1.khash
+     AND A2.rhash <> A1.rhash
+     )
+     THEN 'A_INS'
+
+     ELSE 'NA'
+    END FLAG
+        FROM (
+                SELECT *
+                        
,reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(id1)) 
khash
+                        
,reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(at1)) 
rhash
+                FROM ttest1
+                WHERE ts1 = '2015-11-20'
+                ) A1
+        FULL OUTER JOIN (
+                SELECT *
+                FROM ttest2
+                WHERE '2015-11-20' BETWEEN dt1 AND dt2
+                ) A2
+     ON A1.khash = A2.khash
+     WHERE NOT (
+        NVL(A1.khash, - 1) = NVL(A2.khash, - 1)
+        AND NVL(A1.rhash, - 1) = NVL(A2.rhash, - 1)
+        )
+     AND A2.khash IS NOT NULL
+
+   UNION ALL
+
+   SELECT A1.id1, A1.sts,A1.at1
+        ,A1.dt1
+                , '2099-12-31' dt2
+        ,A1.khash
+        ,A1.rhash
+     ,CASE WHEN A2.khash IS NOT NULL
+     AND A1.khash IS NULL
+     AND A2.ts1 <= A1.ts1
+     THEN 'DEL'
+
+     WHEN ( A2.khash IS NULL AND A1.khash IS NOT NULL )
+     OR ( A2.khash = A1.khash AND A2.rhash <> A1.rhash ) THEN 'INS' ELSE 'NA' 
END FLAG
+   FROM (
+     SELECT *
+        ,reflect('org.apache.commons.codec.digest.DigestUtils', 
'sha256Hex',concat(id1)) khash
+        
,reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(at1)) 
rhash
+      FROM ttest1
+     WHERE ts1 = '2015-11-20'
+    ) A1
+   FULL OUTER JOIN (
+        SELECT *
+        FROM ttest2
+        WHERE '2015-11-20' BETWEEN dt1
+          AND dt2
+       ) A2 ON A1.khash = A2.khash
+       WHERE NOT (
+          NVL(A1.khash, - 1) = NVL(A2.khash, - 1)
+          AND NVL(A1.rhash, - 1) = NVL(A2.rhash, - 1)
+          )
+       AND A1.khash IS NOT NULL
+   ) A2
+   where a2.flag <> 'RET'
+POSTHOOK: type: QUERY
+STAGE DEPENDENCIES:
+  Stage-1 is a root stage
+  Stage-2 depends on stages: Stage-1, Stage-4
+  Stage-4 is a root stage
+  Stage-0 depends on stages: Stage-2
+
+STAGE PLANS:
+  Stage: Stage-1
+    Map Reduce
+      Map Operator Tree:
+          TableScan
+            alias: ttest1
+            Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+            Filter Operator
+              predicate: ((ts1 = '2015-11-20') and 
reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(id1)) 
is not null) (type: boolean)
+              Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+              Select Operator
+                expressions: 
reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(id1)) 
(type: string), 
reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(at1)) 
(type: string)
+                outputColumnNames: _col0, _col1
+                Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                Reduce Output Operator
+                  key expressions: _col0 (type: string)
+                  sort order: +
+                  Map-reduce partition columns: _col0 (type: string)
+                  Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                  value expressions: _col1 (type: string)
+          TableScan
+            alias: ttest2
+            Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+            Filter Operator
+              predicate: ('2015-11-20' BETWEEN dt1 AND dt2 and khash is not 
null) (type: boolean)
+              Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+              Select Operator
+                expressions: id1 (type: bigint), dt1 (type: string), dt2 
(type: string), sts (type: string), at1 (type: bigint), khash (type: string), 
rhash (type: string)
+                outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, 
_col6
+                Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                Reduce Output Operator
+                  key expressions: _col5 (type: string)
+                  sort order: +
+                  Map-reduce partition columns: _col5 (type: string)
+                  Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                  value expressions: _col0 (type: bigint), _col1 (type: 
string), _col2 (type: string), _col3 (type: string), _col4 (type: bigint), 
_col6 (type: string)
+      Reduce Operator Tree:
+        Join Operator
+          condition map:
+               Right Outer Join0 to 1
+          keys:
+            0 _col0 (type: string)
+            1 _col5 (type: string)
+          outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, 
_col7, _col8
+          Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+          Filter Operator
+            predicate: ((CASE WHEN ((_col7 is not null and _col0 is null and 
(_col3 >= '2016-02-05'))) THEN ('DEL') WHEN ((_col7 is not null and _col0 is 
null and (_col3 <= '2016-02-05'))) THEN ('RET') WHEN (((_col7 = _col0) and 
(_col8 <> _col1))) THEN ('A_INS') ELSE ('NA') END <> 'RET') and ((NVL(_col0,-1) 
<> NVL(_col7,-1)) or (NVL(_col1,-1) <> NVL(_col8,-1)))) (type: boolean)
+            Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+            Select Operator
+              expressions: _col2 (type: bigint), _col5 (type: string), _col6 
(type: bigint), _col4 (type: string), _col7 (type: string), _col8 (type: 
string), CASE WHEN ((_col7 is not null and _col0 is null and (_col3 >= 
'2016-02-05'))) THEN ('DEL') WHEN ((_col7 is not null and _col0 is null and 
(_col3 <= '2016-02-05'))) THEN ('RET') WHEN (((_col7 = _col0) and (_col8 <> 
_col1))) THEN ('A_INS') ELSE ('NA') END (type: string)
+              outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, 
_col6
+              Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+              File Output Operator
+                compressed: false
+                table:
+                    input format: 
org.apache.hadoop.mapred.SequenceFileInputFormat
+                    output format: 
org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
+                    serde: 
org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe
+
+  Stage: Stage-2
+    Map Reduce
+      Map Operator Tree:
+          TableScan
+            Union
+              Statistics: Num rows: 2 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+              Select Operator
+                expressions: _col0 (type: bigint), _col1 (type: string), _col2 
(type: bigint), CASE WHEN ((_col6 = 'A_INS')) THEN ('2015-11-21') ELSE 
('2015-11-20') END (type: string), _col3 (type: string), _col4 (type: string), 
_col5 (type: string), _col6 (type: string)
+                outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, 
_col6, _col7
+                Statistics: Num rows: 2 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                File Output Operator
+                  compressed: false
+                  Statistics: Num rows: 2 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                  table:
+                      input format: 
org.apache.hadoop.mapred.SequenceFileInputFormat
+                      output format: 
org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
+                      serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
+          TableScan
+            Union
+              Statistics: Num rows: 2 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+              Select Operator
+                expressions: _col0 (type: bigint), _col1 (type: string), _col2 
(type: bigint), CASE WHEN ((_col6 = 'A_INS')) THEN ('2015-11-21') ELSE 
('2015-11-20') END (type: string), _col3 (type: string), _col4 (type: string), 
_col5 (type: string), _col6 (type: string)
+                outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, 
_col6, _col7
+                Statistics: Num rows: 2 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                File Output Operator
+                  compressed: false
+                  Statistics: Num rows: 2 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                  table:
+                      input format: 
org.apache.hadoop.mapred.SequenceFileInputFormat
+                      output format: 
org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
+                      serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
+
+  Stage: Stage-4
+    Map Reduce
+      Map Operator Tree:
+          TableScan
+            alias: ttest1
+            Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+            Filter Operator
+              predicate: ((ts1 = '2015-11-20') and 
reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(id1)) 
is not null) (type: boolean)
+              Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+              Select Operator
+                expressions: id1 (type: bigint), '2015-11-20' (type: string), 
sts (type: string), at1 (type: bigint), 
reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(id1)) 
(type: string), 
reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(at1)) 
(type: string)
+                outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5
+                Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                Reduce Output Operator
+                  key expressions: _col4 (type: string)
+                  sort order: +
+                  Map-reduce partition columns: _col4 (type: string)
+                  Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                  value expressions: _col0 (type: bigint), _col1 (type: 
string), _col2 (type: string), _col3 (type: bigint), _col5 (type: string)
+          TableScan
+            alias: ttest2
+            Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+            Filter Operator
+              predicate: '2015-11-20' BETWEEN dt1 AND dt2 (type: boolean)
+              Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+              Select Operator
+                expressions: ts1 (type: string), khash (type: string), rhash 
(type: string)
+                outputColumnNames: _col0, _col1, _col2
+                Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                Reduce Output Operator
+                  key expressions: _col1 (type: string)
+                  sort order: +
+                  Map-reduce partition columns: _col1 (type: string)
+                  Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                  value expressions: _col0 (type: string), _col2 (type: string)
+      Reduce Operator Tree:
+        Join Operator
+          condition map:
+               Left Outer Join0 to 1
+          keys:
+            0 _col4 (type: string)
+            1 _col1 (type: string)
+          outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, 
_col7, _col8
+          Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+          Filter Operator
+            predicate: ((CASE WHEN ((_col7 is not null and _col4 is null and 
(_col6 <= _col1))) THEN ('DEL') WHEN (((_col7 is null and _col4 is not null) or 
((_col7 = _col4) and (_col8 <> _col5)))) THEN ('INS') ELSE ('NA') END <> 'RET') 
and ((NVL(_col4,-1) <> NVL(_col7,-1)) or (NVL(_col5,-1) <> NVL(_col8,-1)))) 
(type: boolean)
+            Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+            Select Operator
+              expressions: _col0 (type: bigint), _col2 (type: string), _col3 
(type: bigint), '2099-12-31' (type: string), _col4 (type: string), _col5 (type: 
string), CASE WHEN ((_col7 is not null and _col4 is null and (_col6 <= _col1))) 
THEN ('DEL') WHEN (((_col7 is null and _col4 is not null) or ((_col7 = _col4) 
and (_col8 <> _col5)))) THEN ('INS') ELSE ('NA') END (type: string)
+              outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, 
_col6
+              Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+              File Output Operator
+                compressed: false
+                table:
+                    input format: 
org.apache.hadoop.mapred.SequenceFileInputFormat
+                    output format: 
org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
+                    serde: 
org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe
+
+  Stage: Stage-0
+    Fetch Operator
+      limit: -1
+      Processor Tree:
+        ListSink
+
+PREHOOK: query: explain SELECT
+  A2.id1, A2.sts,A2.at1,
+    CASE WHEN FLAG = 'A_INS' THEN date_add('2015-11-20', 1) ELSE '2015-11-20' 
END dt1
+        ,A2.dt2
+        ,A2.khash
+        ,A2.rhash
+        ,A2.FLAG
+  FROM (
+   SELECT
+  A2.id1, A2.sts,A2.at1
+        ,A2.dt1
+        ,A2.dt2
+        ,A2.khash
+        ,A2.rhash
+    ,CASE
+     WHEN A2.khash IS NOT NULL
+     AND A1.khash IS NULL
+         AND A2.dt1 >= '2016-02-05'
+     THEN 'DEL'
+
+         WHEN A2.khash IS NOT NULL
+     AND A1.khash IS NULL
+     AND A2.dt1 <= '2016-02-05'
+     THEN 'RET'
+
+     WHEN
+     (
+     A2.khash = A1.khash
+     AND A2.rhash <> A1.rhash
+     )
+     THEN 'A_INS'
+
+     ELSE 'NA'
+    END FLAG
+        FROM (
+                SELECT *
+                        
,reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(id1)) 
khash
+                        
,reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(at1)) 
rhash
+                FROM ttest1
+                WHERE ts1 = '2015-11-20'
+                ) A1
+        FULL OUTER JOIN (
+                SELECT *
+                FROM ttest2
+                WHERE '2015-11-20' BETWEEN dt1 AND dt2
+                ) A2
+     ON A1.khash = A2.khash
+     WHERE NOT (
+        NVL(A1.khash, - 1) = NVL(A2.khash, - 1)
+        AND NVL(A1.rhash, - 1) = NVL(A2.rhash, - 1)
+        )
+     AND A2.khash IS NOT NULL
+
+   UNION ALL
+
+   SELECT A1.id1, A1.sts,A1.at1
+        ,A1.dt1
+                , '2099-12-31' dt2
+        ,A1.khash
+        ,A1.rhash
+     ,CASE WHEN A2.khash IS NOT NULL
+     AND A1.khash IS NULL
+     AND A2.ts1 <= A1.ts1
+     THEN 'DEL'
+
+     WHEN ( A2.khash IS NULL AND A1.khash IS NOT NULL )
+     OR ( A2.khash = A1.khash AND A2.rhash <> A1.rhash ) THEN 'INS' ELSE 'NA' 
END FLAG
+   FROM (
+     SELECT *
+        ,reflect('org.apache.commons.codec.digest.DigestUtils', 
'sha256Hex',concat(id1)) khash
+        
,reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(at1)) 
rhash
+      FROM ttest1
+     WHERE ts1 = '2015-11-20'
+    ) A1
+   FULL OUTER JOIN (
+        SELECT *
+        FROM ttest2
+        WHERE '2015-11-20' BETWEEN dt1
+          AND dt2
+       ) A2 ON A1.khash = A2.khash
+       WHERE NOT (
+          NVL(A1.khash, - 1) = NVL(A2.khash, - 1)
+          AND NVL(A1.rhash, - 1) = NVL(A2.rhash, - 1)
+          )
+       AND A1.khash IS NOT NULL
+   ) A2
+   where a2.flag <> 'RET'
+PREHOOK: type: QUERY
+POSTHOOK: query: explain SELECT
+  A2.id1, A2.sts,A2.at1,
+    CASE WHEN FLAG = 'A_INS' THEN date_add('2015-11-20', 1) ELSE '2015-11-20' 
END dt1
+        ,A2.dt2
+        ,A2.khash
+        ,A2.rhash
+        ,A2.FLAG
+  FROM (
+   SELECT
+  A2.id1, A2.sts,A2.at1
+        ,A2.dt1
+        ,A2.dt2
+        ,A2.khash
+        ,A2.rhash
+    ,CASE
+     WHEN A2.khash IS NOT NULL
+     AND A1.khash IS NULL
+         AND A2.dt1 >= '2016-02-05'
+     THEN 'DEL'
+
+         WHEN A2.khash IS NOT NULL
+     AND A1.khash IS NULL
+     AND A2.dt1 <= '2016-02-05'
+     THEN 'RET'
+
+     WHEN
+     (
+     A2.khash = A1.khash
+     AND A2.rhash <> A1.rhash
+     )
+     THEN 'A_INS'
+
+     ELSE 'NA'
+    END FLAG
+        FROM (
+                SELECT *
+                        
,reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(id1)) 
khash
+                        
,reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(at1)) 
rhash
+                FROM ttest1
+                WHERE ts1 = '2015-11-20'
+                ) A1
+        FULL OUTER JOIN (
+                SELECT *
+                FROM ttest2
+                WHERE '2015-11-20' BETWEEN dt1 AND dt2
+                ) A2
+     ON A1.khash = A2.khash
+     WHERE NOT (
+        NVL(A1.khash, - 1) = NVL(A2.khash, - 1)
+        AND NVL(A1.rhash, - 1) = NVL(A2.rhash, - 1)
+        )
+     AND A2.khash IS NOT NULL
+
+   UNION ALL
+
+   SELECT A1.id1, A1.sts,A1.at1
+        ,A1.dt1
+                , '2099-12-31' dt2
+        ,A1.khash
+        ,A1.rhash
+     ,CASE WHEN A2.khash IS NOT NULL
+     AND A1.khash IS NULL
+     AND A2.ts1 <= A1.ts1
+     THEN 'DEL'
+
+     WHEN ( A2.khash IS NULL AND A1.khash IS NOT NULL )
+     OR ( A2.khash = A1.khash AND A2.rhash <> A1.rhash ) THEN 'INS' ELSE 'NA' 
END FLAG
+   FROM (
+     SELECT *
+        ,reflect('org.apache.commons.codec.digest.DigestUtils', 
'sha256Hex',concat(id1)) khash
+        
,reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(at1)) 
rhash
+      FROM ttest1
+     WHERE ts1 = '2015-11-20'
+    ) A1
+   FULL OUTER JOIN (
+        SELECT *
+        FROM ttest2
+        WHERE '2015-11-20' BETWEEN dt1
+          AND dt2
+       ) A2 ON A1.khash = A2.khash
+       WHERE NOT (
+          NVL(A1.khash, - 1) = NVL(A2.khash, - 1)
+          AND NVL(A1.rhash, - 1) = NVL(A2.rhash, - 1)
+          )
+       AND A1.khash IS NOT NULL
+   ) A2
+   where a2.flag <> 'RET'
+POSTHOOK: type: QUERY
+STAGE DEPENDENCIES:
+  Stage-1 is a root stage
+  Stage-2 depends on stages: Stage-1, Stage-4
+  Stage-4 is a root stage
+  Stage-0 depends on stages: Stage-2
+
+STAGE PLANS:
+  Stage: Stage-1
+    Map Reduce
+      Map Operator Tree:
+          TableScan
+            alias: ttest1
+            Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+            Filter Operator
+              predicate: (ts1 = '2015-11-20') (type: boolean)
+              Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+              Select Operator
+                expressions: 
reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(id1)) 
(type: string), 
reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(at1)) 
(type: string)
+                outputColumnNames: _col8, _col9
+                Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                Reduce Output Operator
+                  key expressions: _col8 (type: string)
+                  sort order: +
+                  Map-reduce partition columns: _col8 (type: string)
+                  Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                  value expressions: _col9 (type: string)
+          TableScan
+            alias: ttest2
+            Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+            Filter Operator
+              predicate: '2015-11-20' BETWEEN dt1 AND dt2 (type: boolean)
+              Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+              Select Operator
+                expressions: id1 (type: bigint), dt1 (type: string), dt2 
(type: string), sts (type: string), at1 (type: bigint), khash (type: string), 
rhash (type: string)
+                outputColumnNames: _col0, _col2, _col3, _col6, _col7, _col8, 
_col9
+                Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                Reduce Output Operator
+                  key expressions: _col8 (type: string)
+                  sort order: +
+                  Map-reduce partition columns: _col8 (type: string)
+                  Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                  value expressions: _col0 (type: bigint), _col2 (type: 
string), _col3 (type: string), _col6 (type: string), _col7 (type: bigint), 
_col9 (type: string)
+      Reduce Operator Tree:
+        Join Operator
+          condition map:
+               Outer Join 0 to 1
+          keys:
+            0 _col8 (type: string)
+            1 _col8 (type: string)
+          outputColumnNames: _col8, _col9, _col10, _col12, _col13, _col16, 
_col17, _col18, _col19
+          Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+          Filter Operator
+            predicate: (((NVL(_col8,-1) <> NVL(_col18,-1)) or (NVL(_col9,-1) 
<> NVL(_col19,-1))) and _col18 is not null) (type: boolean)
+            Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+            Select Operator
+              expressions: _col10 (type: bigint), _col16 (type: string), 
_col17 (type: bigint), _col13 (type: string), _col18 (type: string), _col19 
(type: string), CASE WHEN ((_col18 is not null and _col8 is null and (_col12 >= 
'2016-02-05'))) THEN ('DEL') WHEN ((_col18 is not null and _col8 is null and 
(_col12 <= '2016-02-05'))) THEN ('RET') WHEN (((_col18 = _col8) and (_col19 <> 
_col9))) THEN ('A_INS') ELSE ('NA') END (type: string)
+              outputColumnNames: _col0, _col1, _col2, _col4, _col5, _col6, 
_col7
+              Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+              File Output Operator
+                compressed: false
+                table:
+                    input format: 
org.apache.hadoop.mapred.SequenceFileInputFormat
+                    output format: 
org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
+                    serde: 
org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe
+
+  Stage: Stage-2
+    Map Reduce
+      Map Operator Tree:
+          TableScan
+            Union
+              Statistics: Num rows: 2 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+              Filter Operator
+                predicate: (_col7 <> 'RET') (type: boolean)
+                Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                Select Operator
+                  expressions: _col0 (type: bigint), _col1 (type: string), 
_col2 (type: bigint), CASE WHEN ((_col7 = 'A_INS')) THEN ('2015-11-21') ELSE 
('2015-11-20') END (type: string), _col4 (type: string), _col5 (type: string), 
_col6 (type: string), _col7 (type: string)
+                  outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, 
_col6, _col7
+                  Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                  File Output Operator
+                    compressed: false
+                    Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                    table:
+                        input format: 
org.apache.hadoop.mapred.SequenceFileInputFormat
+                        output format: 
org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
+                        serde: 
org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
+          TableScan
+            Union
+              Statistics: Num rows: 2 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+              Filter Operator
+                predicate: (_col7 <> 'RET') (type: boolean)
+                Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                Select Operator
+                  expressions: _col0 (type: bigint), _col1 (type: string), 
_col2 (type: bigint), CASE WHEN ((_col7 = 'A_INS')) THEN ('2015-11-21') ELSE 
('2015-11-20') END (type: string), _col4 (type: string), _col5 (type: string), 
_col6 (type: string), _col7 (type: string)
+                  outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, 
_col6, _col7
+                  Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                  File Output Operator
+                    compressed: false
+                    Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                    table:
+                        input format: 
org.apache.hadoop.mapred.SequenceFileInputFormat
+                        output format: 
org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
+                        serde: 
org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
+
+  Stage: Stage-4
+    Map Reduce
+      Map Operator Tree:
+          TableScan
+            alias: ttest1
+            Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+            Filter Operator
+              predicate: (ts1 = '2015-11-20') (type: boolean)
+              Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+              Select Operator
+                expressions: id1 (type: bigint), '2015-11-20' (type: string), 
sts (type: string), at1 (type: bigint), 
reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(id1)) 
(type: string), 
reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(at1)) 
(type: string)
+                outputColumnNames: _col0, _col1, _col6, _col7, _col8, _col9
+                Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                Reduce Output Operator
+                  key expressions: _col8 (type: string)
+                  sort order: +
+                  Map-reduce partition columns: _col8 (type: string)
+                  Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                  value expressions: _col0 (type: bigint), _col1 (type: 
string), _col6 (type: string), _col7 (type: bigint), _col9 (type: string)
+          TableScan
+            alias: ttest2
+            Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+            Filter Operator
+              predicate: '2015-11-20' BETWEEN dt1 AND dt2 (type: boolean)
+              Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+              Select Operator
+                expressions: ts1 (type: string), khash (type: string), rhash 
(type: string)
+                outputColumnNames: _col1, _col8, _col9
+                Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                Reduce Output Operator
+                  key expressions: _col8 (type: string)
+                  sort order: +
+                  Map-reduce partition columns: _col8 (type: string)
+                  Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                  value expressions: _col1 (type: string), _col9 (type: string)
+      Reduce Operator Tree:
+        Join Operator
+          condition map:
+               Outer Join 0 to 1
+          keys:
+            0 _col8 (type: string)
+            1 _col8 (type: string)
+          outputColumnNames: _col0, _col1, _col6, _col7, _col8, _col9, _col11, 
_col18, _col19
+          Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+          Filter Operator
+            predicate: (((NVL(_col8,-1) <> NVL(_col18,-1)) or (NVL(_col9,-1) 
<> NVL(_col19,-1))) and _col8 is not null) (type: boolean)
+            Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+            Select Operator
+              expressions: _col0 (type: bigint), _col6 (type: string), _col7 
(type: bigint), '2099-12-31' (type: string), _col8 (type: string), _col9 (type: 
string), CASE WHEN ((_col18 is not null and _col8 is null and (_col11 <= 
_col1))) THEN ('DEL') WHEN (((_col18 is null and _col8 is not null) or ((_col18 
= _col8) and (_col19 <> _col9)))) THEN ('INS') ELSE ('NA') END (type: string)
+              outputColumnNames: _col0, _col1, _col2, _col4, _col5, _col6, 
_col7
+              Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+              File Output Operator
+                compressed: false
+                table:
+                    input format: 
org.apache.hadoop.mapred.SequenceFileInputFormat
+                    output format: 
org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
+                    serde: 
org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe
+
+  Stage: Stage-0
+    Fetch Operator
+      limit: -1
+      Processor Tree:
+        ListSink
+
+PREHOOK: query: explain SELECT
+  A2.id1, A2.sts,A2.at1,
+    CASE WHEN FLAG = 'A_INS' THEN date_add('2015-11-20', 1) ELSE '2015-11-20' 
END dt1
+        ,A2.dt2
+        ,A2.khash
+        ,A2.rhash
+        ,A2.FLAG
+  FROM (
+   SELECT
+  A2.id1, A2.sts,A2.at1
+        ,A2.dt1
+        ,A2.dt2
+        ,A2.khash
+        ,A2.rhash
+    ,CASE
+     WHEN A2.khash IS NOT NULL
+     AND A1.khash IS NULL
+         AND A2.dt1 >= '2016-02-05'
+     THEN 'DEL'
+
+         WHEN A2.khash IS NOT NULL
+     AND A1.khash IS NULL
+     AND A2.dt1 <= '2016-02-05'
+     THEN 'RET'
+
+     WHEN
+     (
+     A2.khash = A1.khash
+     AND A2.rhash <> A1.rhash
+     )
+     THEN 'A_INS'
+
+     ELSE 'NA'
+    END FLAG
+        FROM (
+                SELECT *
+                        
,reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(id1)) 
khash
+                        
,reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(at1)) 
rhash
+                FROM ttest1
+                WHERE ts1 = '2015-11-20'
+                ) A1
+        FULL OUTER JOIN (
+                SELECT *
+                FROM ttest2
+                WHERE '2015-11-20' BETWEEN dt1 AND dt2
+                ) A2
+     ON A1.khash = A2.khash
+     WHERE NOT (
+        NVL(A1.khash, - 1) = NVL(A2.khash, - 1)
+        AND NVL(A1.rhash, - 1) = NVL(A2.rhash, - 1)
+        )
+     AND A2.khash IS NOT NULL
+
+   UNION ALL
+
+   SELECT A1.id1, A1.sts,A1.at1
+        ,A1.dt1
+                , '2099-12-31' dt2
+        ,A1.khash
+        ,A1.rhash
+     ,CASE WHEN A2.khash IS NOT NULL
+     AND A1.khash IS NULL
+     AND A2.ts1 <= A1.ts1
+     THEN 'DEL'
+
+     WHEN ( A2.khash IS NULL AND A1.khash IS NOT NULL )
+     OR ( A2.khash = A1.khash AND A2.rhash <> A1.rhash ) THEN 'INS' ELSE 'NA' 
END FLAG
+   FROM (
+     SELECT *
+        ,reflect('org.apache.commons.codec.digest.DigestUtils', 
'sha256Hex',concat(id1)) khash
+        
,reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(at1)) 
rhash
+      FROM ttest1
+     WHERE ts1 = '2015-11-20'
+    ) A1
+   FULL OUTER JOIN (
+        SELECT *
+        FROM ttest2
+        WHERE '2015-11-20' BETWEEN dt1
+          AND dt2
+       ) A2 ON A1.khash = A2.khash
+       WHERE NOT (
+          NVL(A1.khash, - 1) = NVL(A2.khash, - 1)
+          AND NVL(A1.rhash, - 1) = NVL(A2.rhash, - 1)
+          )
+       AND A1.khash IS NOT NULL
+   ) A2
+   where a2.flag <> 'RET'
+PREHOOK: type: QUERY
+POSTHOOK: query: explain SELECT
+  A2.id1, A2.sts,A2.at1,
+    CASE WHEN FLAG = 'A_INS' THEN date_add('2015-11-20', 1) ELSE '2015-11-20' 
END dt1
+        ,A2.dt2
+        ,A2.khash
+        ,A2.rhash
+        ,A2.FLAG
+  FROM (
+   SELECT
+  A2.id1, A2.sts,A2.at1
+        ,A2.dt1
+        ,A2.dt2
+        ,A2.khash
+        ,A2.rhash
+    ,CASE
+     WHEN A2.khash IS NOT NULL
+     AND A1.khash IS NULL
+         AND A2.dt1 >= '2016-02-05'
+     THEN 'DEL'
+
+         WHEN A2.khash IS NOT NULL
+     AND A1.khash IS NULL
+     AND A2.dt1 <= '2016-02-05'
+     THEN 'RET'
+
+     WHEN
+     (
+     A2.khash = A1.khash
+     AND A2.rhash <> A1.rhash
+     )
+     THEN 'A_INS'
+
+     ELSE 'NA'
+    END FLAG
+        FROM (
+                SELECT *
+                        
,reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(id1)) 
khash
+                        
,reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(at1)) 
rhash
+                FROM ttest1
+                WHERE ts1 = '2015-11-20'
+                ) A1
+        FULL OUTER JOIN (
+                SELECT *
+                FROM ttest2
+                WHERE '2015-11-20' BETWEEN dt1 AND dt2
+                ) A2
+     ON A1.khash = A2.khash
+     WHERE NOT (
+        NVL(A1.khash, - 1) = NVL(A2.khash, - 1)
+        AND NVL(A1.rhash, - 1) = NVL(A2.rhash, - 1)
+        )
+     AND A2.khash IS NOT NULL
+
+   UNION ALL
+
+   SELECT A1.id1, A1.sts,A1.at1
+        ,A1.dt1
+                , '2099-12-31' dt2
+        ,A1.khash
+        ,A1.rhash
+     ,CASE WHEN A2.khash IS NOT NULL
+     AND A1.khash IS NULL
+     AND A2.ts1 <= A1.ts1
+     THEN 'DEL'
+
+     WHEN ( A2.khash IS NULL AND A1.khash IS NOT NULL )
+     OR ( A2.khash = A1.khash AND A2.rhash <> A1.rhash ) THEN 'INS' ELSE 'NA' 
END FLAG
+   FROM (
+     SELECT *
+        ,reflect('org.apache.commons.codec.digest.DigestUtils', 
'sha256Hex',concat(id1)) khash
+        
,reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(at1)) 
rhash
+      FROM ttest1
+     WHERE ts1 = '2015-11-20'
+    ) A1
+   FULL OUTER JOIN (
+        SELECT *
+        FROM ttest2
+        WHERE '2015-11-20' BETWEEN dt1
+          AND dt2
+       ) A2 ON A1.khash = A2.khash
+       WHERE NOT (
+          NVL(A1.khash, - 1) = NVL(A2.khash, - 1)
+          AND NVL(A1.rhash, - 1) = NVL(A2.rhash, - 1)
+          )
+       AND A1.khash IS NOT NULL
+   ) A2
+   where a2.flag <> 'RET'
+POSTHOOK: type: QUERY
+STAGE DEPENDENCIES:
+  Stage-1 is a root stage
+  Stage-2 depends on stages: Stage-1, Stage-4
+  Stage-4 is a root stage
+  Stage-0 depends on stages: Stage-2
+
+STAGE PLANS:
+  Stage: Stage-1
+    Map Reduce
+      Map Operator Tree:
+          TableScan
+            alias: ttest1
+            Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+            Filter Operator
+              predicate: (ts1 = '2015-11-20') (type: boolean)
+              Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+              Select Operator
+                expressions: 
reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(id1)) 
(type: string), 
reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(at1)) 
(type: string)
+                outputColumnNames: _col8, _col9
+                Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                Reduce Output Operator
+                  key expressions: _col8 (type: string)
+                  sort order: +
+                  Map-reduce partition columns: _col8 (type: string)
+                  Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                  value expressions: _col9 (type: string)
+          TableScan
+            alias: ttest2
+            Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+            Filter Operator
+              predicate: '2015-11-20' BETWEEN dt1 AND dt2 (type: boolean)
+              Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+              Select Operator
+                expressions: id1 (type: bigint), dt1 (type: string), dt2 
(type: string), sts (type: string), at1 (type: bigint), khash (type: string), 
rhash (type: string)
+                outputColumnNames: _col0, _col2, _col3, _col6, _col7, _col8, 
_col9
+                Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                Reduce Output Operator
+                  key expressions: _col8 (type: string)
+                  sort order: +
+                  Map-reduce partition columns: _col8 (type: string)
+                  Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                  value expressions: _col0 (type: bigint), _col2 (type: 
string), _col3 (type: string), _col6 (type: string), _col7 (type: bigint), 
_col9 (type: string)
+      Reduce Operator Tree:
+        Join Operator
+          condition map:
+               Outer Join 0 to 1
+          keys:
+            0 _col8 (type: string)
+            1 _col8 (type: string)
+          outputColumnNames: _col8, _col9, _col10, _col12, _col13, _col16, 
_col17, _col18, _col19
+          Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+          Filter Operator
+            predicate: (((NVL(_col8,-1) <> NVL(_col18,-1)) or (NVL(_col9,-1) 
<> NVL(_col19,-1))) and _col18 is not null) (type: boolean)
+            Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+            Select Operator
+              expressions: _col10 (type: bigint), _col16 (type: string), 
_col17 (type: bigint), _col13 (type: string), _col18 (type: string), _col19 
(type: string), CASE WHEN ((_col18 is not null and _col8 is null and (_col12 >= 
'2016-02-05'))) THEN ('DEL') WHEN ((_col18 is not null and _col8 is null and 
(_col12 <= '2016-02-05'))) THEN ('RET') WHEN (((_col18 = _col8) and (_col19 <> 
_col9))) THEN ('A_INS') ELSE ('NA') END (type: string)
+              outputColumnNames: _col0, _col1, _col2, _col4, _col5, _col6, 
_col7
+              Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+              File Output Operator
+                compressed: false
+                table:
+                    input format: 
org.apache.hadoop.mapred.SequenceFileInputFormat
+                    output format: 
org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
+                    serde: 
org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe
+
+  Stage: Stage-2
+    Map Reduce
+      Map Operator Tree:
+          TableScan
+            Union
+              Statistics: Num rows: 2 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+              Filter Operator
+                predicate: (_col7 <> 'RET') (type: boolean)
+                Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                Select Operator
+                  expressions: _col0 (type: bigint), _col1 (type: string), 
_col2 (type: bigint), CASE WHEN ((_col7 = 'A_INS')) THEN ('2015-11-21') ELSE 
('2015-11-20') END (type: string), _col4 (type: string), _col5 (type: string), 
_col6 (type: string), _col7 (type: string)
+                  outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, 
_col6, _col7
+                  Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                  File Output Operator
+                    compressed: false
+                    Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                    table:
+                        input format: 
org.apache.hadoop.mapred.SequenceFileInputFormat
+                        output format: 
org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
+                        serde: 
org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
+          TableScan
+            Union
+              Statistics: Num rows: 2 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+              Filter Operator
+                predicate: (_col7 <> 'RET') (type: boolean)
+                Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                Select Operator
+                  expressions: _col0 (type: bigint), _col1 (type: string), 
_col2 (type: bigint), CASE WHEN ((_col7 = 'A_INS')) THEN ('2015-11-21') ELSE 
('2015-11-20') END (type: string), _col4 (type: string), _col5 (type: string), 
_col6 (type: string), _col7 (type: string)
+                  outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, 
_col6, _col7
+                  Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                  File Output Operator
+                    compressed: false
+                    Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                    table:
+                        input format: 
org.apache.hadoop.mapred.SequenceFileInputFormat
+                        output format: 
org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
+                        serde: 
org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
+
+  Stage: Stage-4
+    Map Reduce
+      Map Operator Tree:
+          TableScan
+            alias: ttest1
+            Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+            Filter Operator
+              predicate: (ts1 = '2015-11-20') (type: boolean)
+              Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+              Select Operator
+                expressions: id1 (type: bigint), '2015-11-20' (type: string), 
sts (type: string), at1 (type: bigint), 
reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(id1)) 
(type: string), 
reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(at1)) 
(type: string)
+                outputColumnNames: _col0, _col1, _col6, _col7, _col8, _col9
+                Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                Reduce Output Operator
+                  key expressions: _col8 (type: string)
+                  sort order: +
+                  Map-reduce partition columns: _col8 (type: string)
+                  Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                  value expressions: _col0 (type: bigint), _col1 (type: 
string), _col6 (type: string), _col7 (type: bigint), _col9 (type: string)
+          TableScan
+            alias: ttest2
+            Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+            Filter Operator
+              predicate: '2015-11-20' BETWEEN dt1 AND dt2 (type: boolean)
+              Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+              Select Operator
+                expressions: ts1 (type: string), khash (type: string), rhash 
(type: string)
+                outputColumnNames: _col1, _col8, _col9
+                Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                Reduce Output Operator
+                  key expressions: _col8 (type: string)
+                  sort order: +
+                  Map-reduce partition columns: _col8 (type: string)
+                  Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                  value expressions: _col1 (type: string), _col9 (type: string)
+      Reduce Operator Tree:
+        Join Operator
+          condition map:
+               Outer Join 0 to 1
+          keys:
+            0 _col8 (type: string)
+            1 _col8 (type: string)
+          outputColumnNames: _col0, _col1, _col6, _col7, _col8, _col9, _col11, 
_col18, _col19
+          Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+          Filter Operator
+            predicate: (((NVL(_col8,-1) <> NVL(_col18,-1)) or (NVL(_col9,-1) 
<> NVL(_col19,-1))) and _col8 is not null) (type: boolean)
+            Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+            Select Operator
+              expressions: _col0 (type: bigint), _col6 (type: string), _col7 
(type: bigint), '2099-12-31' (type: string), _col8 (type: string), _col9 (type: 
string), CASE WHEN ((_col18 is not null and _col8 is null and (_col11 <= 
_col1))) THEN ('DEL') WHEN (((_col18 is null and _col8 is not null) or ((_col18 
= _col8) and (_col19 <> _col9)))) THEN ('INS') ELSE ('NA') END (type: string)
+              outputColumnNames: _col0, _col1, _col2, _col4, _col5, _col6, 
_col7
+              Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+              File Output Operator
+                compressed: false
+                table:
+                    input format: 
org.apache.hadoop.mapred.SequenceFileInputFormat
+                    output format: 
org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
+                    serde: 
org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe
+
+  Stage: Stage-0
+    Fetch Operator
+      limit: -1
+      Processor Tree:
+        ListSink
+
+PREHOOK: query: explain SELECT
+  A2.id1, A2.sts,A2.at1,
+    CASE WHEN FLAG = 'A_INS' THEN date_add('2015-11-20', 1) ELSE '2015-11-20' 
END dt1
+        ,A2.dt2
+        ,A2.khash
+        ,A2.rhash
+        ,A2.FLAG
+  FROM (
+   SELECT
+  A2.id1, A2.sts,A2.at1
+        ,A2.dt1
+        ,A2.dt2
+        ,A2.khash
+        ,A2.rhash
+    ,CASE
+     WHEN A2.khash IS NOT NULL
+     AND A1.khash IS NULL
+         AND A2.dt1 >= '2016-02-05'
+     THEN 'DEL'
+
+         WHEN A2.khash IS NOT NULL
+     AND A1.khash IS NULL
+     AND A2.dt1 <= '2016-02-05'
+     THEN 'RET'
+
+     WHEN
+     (
+     A2.khash = A1.khash
+     AND A2.rhash <> A1.rhash
+     )
+     THEN 'A_INS'
+
+     ELSE 'NA'
+    END FLAG
+        FROM (
+                SELECT *
+                        
,reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(id1)) 
khash
+                        
,reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(at1)) 
rhash
+                FROM ttest1
+                WHERE ts1 = '2015-11-20'
+                ) A1
+        FULL OUTER JOIN (
+                SELECT *
+                FROM ttest2
+                WHERE '2015-11-20' BETWEEN dt1 AND dt2
+                ) A2
+     ON A1.khash = A2.khash
+     WHERE NOT (
+        NVL(A1.khash, - 1) = NVL(A2.khash, - 1)
+        AND NVL(A1.rhash, - 1) = NVL(A2.rhash, - 1)
+        )
+     AND A2.khash IS NOT NULL
+
+   UNION ALL
+
+   SELECT A1.id1, A1.sts,A1.at1
+        ,A1.dt1
+                , '2099-12-31' dt2
+        ,A1.khash
+        ,A1.rhash
+     ,CASE WHEN A2.khash IS NOT NULL
+     AND A1.khash IS NULL
+     AND A2.ts1 <= A1.ts1
+     THEN 'DEL'
+
+     WHEN ( A2.khash IS NULL AND A1.khash IS NOT NULL )
+     OR ( A2.khash = A1.khash AND A2.rhash <> A1.rhash ) THEN 'INS' ELSE 'NA' 
END FLAG
+   FROM (
+     SELECT *
+        ,reflect('org.apache.commons.codec.digest.DigestUtils', 
'sha256Hex',concat(id1)) khash
+        
,reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(at1)) 
rhash
+      FROM ttest1
+     WHERE ts1 = '2015-11-20'
+    ) A1
+   FULL OUTER JOIN (
+        SELECT *
+        FROM ttest2
+        WHERE '2015-11-20' BETWEEN dt1
+          AND dt2
+       ) A2 ON A1.khash = A2.khash
+       WHERE NOT (
+          NVL(A1.khash, - 1) = NVL(A2.khash, - 1)
+          AND NVL(A1.rhash, - 1) = NVL(A2.rhash, - 1)
+          )
+       AND A1.khash IS NOT NULL
+   ) A2
+   where a2.flag <> 'RET'
+PREHOOK: type: QUERY
+POSTHOOK: query: explain SELECT
+  A2.id1, A2.sts,A2.at1,
+    CASE WHEN FLAG = 'A_INS' THEN date_add('2015-11-20', 1) ELSE '2015-11-20' 
END dt1
+        ,A2.dt2
+        ,A2.khash
+        ,A2.rhash
+        ,A2.FLAG
+  FROM (
+   SELECT
+  A2.id1, A2.sts,A2.at1
+        ,A2.dt1
+        ,A2.dt2
+        ,A2.khash
+        ,A2.rhash
+    ,CASE
+     WHEN A2.khash IS NOT NULL
+     AND A1.khash IS NULL
+         AND A2.dt1 >= '2016-02-05'
+     THEN 'DEL'
+
+         WHEN A2.khash IS NOT NULL
+     AND A1.khash IS NULL
+     AND A2.dt1 <= '2016-02-05'
+     THEN 'RET'
+
+     WHEN
+     (
+     A2.khash = A1.khash
+     AND A2.rhash <> A1.rhash
+     )
+     THEN 'A_INS'
+
+     ELSE 'NA'
+    END FLAG
+        FROM (
+                SELECT *
+                        
,reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(id1)) 
khash
+                        
,reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(at1)) 
rhash
+                FROM ttest1
+                WHERE ts1 = '2015-11-20'
+                ) A1
+        FULL OUTER JOIN (
+                SELECT *
+                FROM ttest2
+                WHERE '2015-11-20' BETWEEN dt1 AND dt2
+                ) A2
+     ON A1.khash = A2.khash
+     WHERE NOT (
+        NVL(A1.khash, - 1) = NVL(A2.khash, - 1)
+        AND NVL(A1.rhash, - 1) = NVL(A2.rhash, - 1)
+        )
+     AND A2.khash IS NOT NULL
+
+   UNION ALL
+
+   SELECT A1.id1, A1.sts,A1.at1
+        ,A1.dt1
+                , '2099-12-31' dt2
+        ,A1.khash
+        ,A1.rhash
+     ,CASE WHEN A2.khash IS NOT NULL
+     AND A1.khash IS NULL
+     AND A2.ts1 <= A1.ts1
+     THEN 'DEL'
+
+     WHEN ( A2.khash IS NULL AND A1.khash IS NOT NULL )
+     OR ( A2.khash = A1.khash AND A2.rhash <> A1.rhash ) THEN 'INS' ELSE 'NA' 
END FLAG
+   FROM (
+     SELECT *
+        ,reflect('org.apache.commons.codec.digest.DigestUtils', 
'sha256Hex',concat(id1)) khash
+        
,reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(at1)) 
rhash
+      FROM ttest1
+     WHERE ts1 = '2015-11-20'
+    ) A1
+   FULL OUTER JOIN (
+        SELECT *
+        FROM ttest2
+        WHERE '2015-11-20' BETWEEN dt1
+          AND dt2
+       ) A2 ON A1.khash = A2.khash
+       WHERE NOT (
+          NVL(A1.khash, - 1) = NVL(A2.khash, - 1)
+          AND NVL(A1.rhash, - 1) = NVL(A2.rhash, - 1)
+          )
+       AND A1.khash IS NOT NULL
+   ) A2
+   where a2.flag <> 'RET'
+POSTHOOK: type: QUERY
+STAGE DEPENDENCIES:
+  Stage-1 is a root stage
+  Stage-2 depends on stages: Stage-1, Stage-4
+  Stage-4 is a root stage
+  Stage-0 depends on stages: Stage-2
+
+STAGE PLANS:
+  Stage: Stage-1
+    Map Reduce
+      Map Operator Tree:
+          TableScan
+            alias: ttest1
+            Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+            Filter Operator
+              predicate: ((ts1 = '2015-11-20') and 
reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(id1)) 
is not null) (type: boolean)
+              Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+              Select Operator
+                expressions: 
reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(id1)) 
(type: string), 
reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(at1)) 
(type: string)
+                outputColumnNames: _col0, _col1
+                Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                Reduce Output Operator
+                  key expressions: _col0 (type: string)
+                  sort order: +
+                  Map-reduce partition columns: _col0 (type: string)
+                  Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                  value expressions: _col1 (type: string)
+          TableScan
+            alias: ttest2
+            Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+            Filter Operator
+              predicate: ('2015-11-20' BETWEEN dt1 AND dt2 and khash is not 
null) (type: boolean)
+              Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+              Select Operator
+                expressions: id1 (type: bigint), dt1 (type: string), dt2 
(type: string), sts (type: string), at1 (type: bigint), khash (type: string), 
rhash (type: string)
+                outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, 
_col6
+                Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                Reduce Output Operator
+                  key expressions: _col5 (type: string)
+                  sort order: +
+                  Map-reduce partition columns: _col5 (type: string)
+                  Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                  value expressions: _col0 (type: bigint), _col1 (type: 
string), _col2 (type: string), _col3 (type: string), _col4 (type: bigint), 
_col6 (type: string)
+      Reduce Operator Tree:
+        Join Operator
+          condition map:
+               Right Outer Join0 to 1
+          keys:
+            0 _col0 (type: string)
+            1 _col5 (type: string)
+          outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, 
_col7, _col8
+          Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+          Filter Operator
+            predicate: ((CASE WHEN ((_col7 is not null and _col0 is null and 
(_col3 >= '2016-02-05'))) THEN ('DEL') WHEN ((_col7 is not null and _col0 is 
null and (_col3 <= '2016-02-05'))) THEN ('RET') WHEN (((_col7 = _col0) and 
(_col8 <> _col1))) THEN ('A_INS') ELSE ('NA') END <> 'RET') and ((NVL(_col0,-1) 
<> NVL(_col7,-1)) or (NVL(_col1,-1) <> NVL(_col8,-1)))) (type: boolean)
+            Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+            Select Operator
+              expressions: _col2 (type: bigint), _col5 (type: string), _col6 
(type: bigint), _col4 (type: string), _col7 (type: string), _col8 (type: 
string), CASE WHEN ((_col7 is not null and _col0 is null and (_col3 >= 
'2016-02-05'))) THEN ('DEL') WHEN ((_col7 is not null and _col0 is null and 
(_col3 <= '2016-02-05'))) THEN ('RET') WHEN (((_col7 = _col0) and (_col8 <> 
_col1))) THEN ('A_INS') ELSE ('NA') END (type: string)
+              outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, 
_col6
+              Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+              File Output Operator
+                compressed: false
+                table:
+                    input format: 
org.apache.hadoop.mapred.SequenceFileInputFormat
+                    output format: 
org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
+                    serde: 
org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe
+
+  Stage: Stage-2
+    Map Reduce
+      Map Operator Tree:
+          TableScan
+            Union
+              Statistics: Num rows: 2 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+              Select Operator
+                expressions: _col0 (type: bigint), _col1 (type: string), _col2 
(type: bigint), CASE WHEN ((_col6 = 'A_INS')) THEN ('2015-11-21') ELSE 
('2015-11-20') END (type: string), _col3 (type: string), _col4 (type: string), 
_col5 (type: string), _col6 (type: string)
+                outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, 
_col6, _col7
+                Statistics: Num rows: 2 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                File Output Operator
+                  compressed: false
+                  Statistics: Num rows: 2 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                  table:
+                      input format: 
org.apache.hadoop.mapred.SequenceFileInputFormat
+                      output format: 
org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
+                      serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
+          TableScan
+            Union
+              Statistics: Num rows: 2 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+              Select Operator
+                expressions: _col0 (type: bigint), _col1 (type: string), _col2 
(type: bigint), CASE WHEN ((_col6 = 'A_INS')) THEN ('2015-11-21') ELSE 
('2015-11-20') END (type: string), _col3 (type: string), _col4 (type: string), 
_col5 (type: string), _col6 (type: string)
+                outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, 
_col6, _col7
+                Statistics: Num rows: 2 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                File Output Operator
+                  compressed: false
+                  Statistics: Num rows: 2 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                  table:
+                      input format: 
org.apache.hadoop.mapred.SequenceFileInputFormat
+                      output format: 
org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
+                      serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
+
+  Stage: Stage-4
+    Map Reduce
+      Map Operator Tree:
+          TableScan
+            alias: ttest1
+            Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+            Filter Operator
+              predicate: ((ts1 = '2015-11-20') and 
reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(id1)) 
is not null) (type: boolean)
+              Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+              Select Operator
+                expressions: id1 (type: bigint), '2015-11-20' (type: string), 
sts (type: string), at1 (type: bigint), 
reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(id1)) 
(type: string), 
reflect('org.apache.commons.codec.digest.DigestUtils','sha256Hex',concat(at1)) 
(type: string)
+                outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5
+                Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                Reduce Output Operator
+                  key expressions: _col4 (type: string)
+                  sort order: +
+                  Map-reduce partition columns: _col4 (type: string)
+                  Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                  value expressions: _col0 (type: bigint), _col1 (type: 
string), _col2 (type: string), _col3 (type: bigint), _col5 (type: string)
+          TableScan
+            alias: ttest2
+            Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+            Filter Operator
+              predicate: '2015-11-20' BETWEEN dt1 AND dt2 (type: boolean)
+              Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+              Select Operator
+                expressions: ts1 (type: string), khash (type: string), rhash 
(type: string)
+                outputColumnNames: _col0, _col1, _col2
+                Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                Reduce Output Operator
+                  key expressions: _col1 (type: string)
+                  sort order: +
+                  Map-reduce partition columns: _col1 (type: string)
+                  Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 
Column stats: NONE
+                  value expressions: _col0 (type: string), _col2 (type: string)
+      Reduce Operator Tree:
+        Join Operator
+          condition map:
+               Left Outer Join0 to 1
+          keys:
+            0 _col4 (type: string)
+            1 _col1 (type: string)
+          outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, 
_col7, _col8
+          Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+          Filter Operator
+            predicate: ((CASE WHEN ((_col7 is not null and _col4 is null and 
(_col6 <= _col1))) THEN ('DEL') WHEN (((_col7 is null and _col4 is not null) or 
((_col7 = _col4) and (_col8 <> _col5)))) THEN ('INS') ELSE ('NA') END <> 'RET') 
and ((NVL(_col4,-1) <> NVL(_col7,-1)) or (NVL(_col5,-1) <> NVL(_col8,-1)))) 
(type: boolean)
+            Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+            Select Operator
+              expressions: _col0 (type: bigint), _col2 (type: string), _col3 
(type: bigint), '2099-12-31' (type: string), _col4 (type: string), _col5 (type: 
string), CASE WHEN ((_col7 is not null and _col4 is null and (_col6 <= _col1))) 
THEN ('DEL') WHEN (((_col7 is null and _col4 is not null) or ((_col7 = _col4) 
and (_col8 <> _col5)))) THEN ('INS') ELSE ('NA') END (type: string)
+              outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, 
_col6
+              Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column 
stats: NONE
+              File Output Operator
+                compressed: false
+                table:
+                    input format: 
org.apache.hadoop.mapred.SequenceFileInputFormat
+                    output format: 
org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
+                    serde: 
org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe
+
+  Stage: Stage-0
+    Fetch Operator
+      limit: -1
+      Processor Tree:
+        ListSink
+
+PREHOOK: query: DROP TABLE ttest1
+PREHOOK: type: DROPTABLE
+PREHOOK: Input: default@ttest1
+PREHOOK: Output: default@ttest1
+POSTHOOK: query: DROP TABLE ttest1
+POSTHOOK: type: DROPTABLE
+POSTHOOK: Input: default@ttest1
+POSTHOOK: Output: default@ttest1
+PREHOOK: query: DROP TABLE ttest2
+PREHOOK: type: DROPTABLE
+PREHOOK: Input: default@ttest2
+PREHOOK: Output: default@ttest2
+POSTHOOK: query: DROP TABLE ttest2
+POSTHOOK: type: DROPTABLE
+POSTHOOK: Input: default@ttest2
+POSTHOOK: Output: default@ttest2