You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hive.apache.org by yc...@apache.org on 2016/04/25 01:56:58 UTC
[1/2] hive git commit: HIVE-13570: Some queries with Union all fail
when CBO is off (Yongzhi Chen, reviewed by Chaoyu Tang)
Repository: hive
Updated Branches:
refs/heads/master a42bc6746 -> 413645d1b
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
[2/2] hive git commit: HIVE-13570: Some queries with Union all fail
when CBO is off (Yongzhi Chen, reviewed by Chaoyu Tang)
Posted by yc...@apache.org.
HIVE-13570: Some queries with Union all fail when CBO is off (Yongzhi Chen, reviewed by Chaoyu Tang)
Project: http://git-wip-us.apache.org/repos/asf/hive/repo
Commit: http://git-wip-us.apache.org/repos/asf/hive/commit/413645d1
Tree: http://git-wip-us.apache.org/repos/asf/hive/tree/413645d1
Diff: http://git-wip-us.apache.org/repos/asf/hive/diff/413645d1
Branch: refs/heads/master
Commit: 413645d1b7e5597aa882cc4a23e664693707ae21
Parents: a42bc67
Author: Yongzhi Chen <yc...@apache.org>
Authored: Wed Apr 20 20:52:18 2016 -0400
Committer: Yongzhi Chen <yc...@apache.org>
Committed: Sun Apr 24 19:54:47 2016 -0400
----------------------------------------------------------------------
.../hive/ql/optimizer/ColumnPrunerProcCtx.java | 2 +-
.../test/queries/clientpositive/union_offcbo.q | 483 +++++
.../results/clientpositive/union_offcbo.q.out | 1779 ++++++++++++++++++
3 files changed, 2263 insertions(+), 1 deletion(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/hive/blob/413645d1/ql/src/java/org/apache/hadoop/hive/ql/optimizer/ColumnPrunerProcCtx.java
----------------------------------------------------------------------
diff --git a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/ColumnPrunerProcCtx.java b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/ColumnPrunerProcCtx.java
index 7befd3b..611a6b7 100644
--- a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/ColumnPrunerProcCtx.java
+++ b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/ColumnPrunerProcCtx.java
@@ -277,7 +277,7 @@ public class ColumnPrunerProcCtx implements NodeProcessorCtx {
for (Operator<? extends OperatorDesc> child : curOp.getChildOperators()) {
if (child instanceof UnionOperator) {
- prunList = genColLists(curOp, child);
+ prunList = genColLists(child);
if (prunList == null || prunList.size() == 0 || parentPrunList.size() == prunList.size()) {
continue;
}
http://git-wip-us.apache.org/repos/asf/hive/blob/413645d1/ql/src/test/queries/clientpositive/union_offcbo.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/union_offcbo.q b/ql/src/test/queries/clientpositive/union_offcbo.q
new file mode 100644
index 0000000..66d4bee
--- /dev/null
+++ b/ql/src/test/queries/clientpositive/union_offcbo.q
@@ -0,0 +1,483 @@
+set hive.cbo.enable=false;
+set hive.ppd.remove.duplicatefilters=true;
+set hive.optimize.ppd=true;
+
+DROP TABLE IF EXISTS ttest1;
+DROP TABLE IF EXISTS ttest2;
+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');
+
+CREATE TABLE ttest2 (
+ `id1` bigint,
+ `ts1` string,
+ `dt1` string,
+ `dt2` string,
+ `ac1` string,
+ `kd1` string,
+ `sts` string,
+ `at1` bigint,
+ `khash` string,
+ `rhash` string);
+
+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';
+
+set hive.cbo.enable=true;
+set hive.ppd.remove.duplicatefilters=true;
+set hive.optimize.ppd=true;
+
+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';
+
+set hive.cbo.enable=false;
+set hive.ppd.remove.duplicatefilters=false;
+set hive.optimize.ppd=true;
+
+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';
+
+set hive.cbo.enable=false;
+set hive.optimize.ppd=false;
+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';
+
+set hive.cbo.enable=true;
+set hive.optimize.ppd=false;
+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';
+
+DROP TABLE ttest1;
+DROP TABLE ttest2;
+