You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hive.apache.org by se...@apache.org on 2018/07/04 01:35:15 UTC
[4/5] hive git commit: HIVE-20039 : Bucket pruning: Left Outer Join
on bucketed table gives wrong result (Deepak Jaiswal, reviewed by Gopal V)
HIVE-20039 : Bucket pruning: Left Outer Join on bucketed table gives wrong result (Deepak Jaiswal, reviewed by Gopal V)
Project: http://git-wip-us.apache.org/repos/asf/hive/repo
Commit: http://git-wip-us.apache.org/repos/asf/hive/commit/e972122e
Tree: http://git-wip-us.apache.org/repos/asf/hive/tree/e972122e
Diff: http://git-wip-us.apache.org/repos/asf/hive/diff/e972122e
Branch: refs/heads/master-txnstats
Commit: e972122e0cad69a0a77110bb5ad11990b7bc5747
Parents: 19676a3
Author: Deepak Jaiswal <dj...@apache.org>
Authored: Tue Jul 3 17:22:02 2018 -0700
Committer: Deepak Jaiswal <dj...@apache.org>
Committed: Tue Jul 3 17:22:02 2018 -0700
----------------------------------------------------------------------
.../000000_0 | Bin 0 -> 677 bytes
.../000000_0 | Bin 0 -> 3483 bytes
.../000000_0 | 1 +
.../l3_monthly_dw_dimplan/000056_0 | Bin 0 -> 6988024 bytes
.../test/resources/testconfiguration.properties | 1 +
.../optimizer/FixedBucketPruningOptimizer.java | 89 +-
.../clientpositive/tez_fixed_bucket_pruning.q | 223 +++
.../llap/tez_fixed_bucket_pruning.q.out | 1409 ++++++++++++++++++
8 files changed, 1664 insertions(+), 59 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/hive/blob/e972122e/data/files/bucket_pruning/l3_clarity__l3_monthly_dw_factplan_datajoin_1_s2_2018022300104_1/000000_0
----------------------------------------------------------------------
diff --git a/data/files/bucket_pruning/l3_clarity__l3_monthly_dw_factplan_datajoin_1_s2_2018022300104_1/000000_0 b/data/files/bucket_pruning/l3_clarity__l3_monthly_dw_factplan_datajoin_1_s2_2018022300104_1/000000_0
new file mode 100644
index 0000000..0775994
Binary files /dev/null and b/data/files/bucket_pruning/l3_clarity__l3_monthly_dw_factplan_datajoin_1_s2_2018022300104_1/000000_0 differ
http://git-wip-us.apache.org/repos/asf/hive/blob/e972122e/data/files/bucket_pruning/l3_clarity__l3_monthly_dw_factplan_dw_stg_2018022300104_1/000000_0
----------------------------------------------------------------------
diff --git a/data/files/bucket_pruning/l3_clarity__l3_monthly_dw_factplan_dw_stg_2018022300104_1/000000_0 b/data/files/bucket_pruning/l3_clarity__l3_monthly_dw_factplan_dw_stg_2018022300104_1/000000_0
new file mode 100644
index 0000000..f075b74
Binary files /dev/null and b/data/files/bucket_pruning/l3_clarity__l3_monthly_dw_factplan_dw_stg_2018022300104_1/000000_0 differ
http://git-wip-us.apache.org/repos/asf/hive/blob/e972122e/data/files/bucket_pruning/l3_clarity__l3_snap_number_2018022300104/000000_0
----------------------------------------------------------------------
diff --git a/data/files/bucket_pruning/l3_clarity__l3_snap_number_2018022300104/000000_0 b/data/files/bucket_pruning/l3_clarity__l3_snap_number_2018022300104/000000_0
new file mode 100644
index 0000000..0f1c7b9
--- /dev/null
+++ b/data/files/bucket_pruning/l3_clarity__l3_snap_number_2018022300104/000000_0
@@ -0,0 +1 @@
+201711
http://git-wip-us.apache.org/repos/asf/hive/blob/e972122e/data/files/bucket_pruning/l3_monthly_dw_dimplan/000056_0
----------------------------------------------------------------------
diff --git a/data/files/bucket_pruning/l3_monthly_dw_dimplan/000056_0 b/data/files/bucket_pruning/l3_monthly_dw_dimplan/000056_0
new file mode 100644
index 0000000..ffa9a33
Binary files /dev/null and b/data/files/bucket_pruning/l3_monthly_dw_dimplan/000056_0 differ
http://git-wip-us.apache.org/repos/asf/hive/blob/e972122e/itests/src/test/resources/testconfiguration.properties
----------------------------------------------------------------------
diff --git a/itests/src/test/resources/testconfiguration.properties b/itests/src/test/resources/testconfiguration.properties
index d02c0fe..cd09481 100644
--- a/itests/src/test/resources/testconfiguration.properties
+++ b/itests/src/test/resources/testconfiguration.properties
@@ -702,6 +702,7 @@ minillaplocal.query.files=\
tez_dynpart_hashjoin_1.q,\
tez_dynpart_hashjoin_2.q,\
tez_dynpart_hashjoin_3.q,\
+ tez_fixed_bucket_pruning.q,\
tez_fsstat.q,\
tez_insert_overwrite_local_directory_1.q,\
tez_join.q,\
http://git-wip-us.apache.org/repos/asf/hive/blob/e972122e/ql/src/java/org/apache/hadoop/hive/ql/optimizer/FixedBucketPruningOptimizer.java
----------------------------------------------------------------------
diff --git a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/FixedBucketPruningOptimizer.java b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/FixedBucketPruningOptimizer.java
index 2debaca..334b8e9 100644
--- a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/FixedBucketPruningOptimizer.java
+++ b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/FixedBucketPruningOptimizer.java
@@ -43,6 +43,7 @@ import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.metadata.Partition;
import org.apache.hadoop.hive.ql.metadata.Table;
import org.apache.hadoop.hive.ql.optimizer.PrunerOperatorFactory.FilterPruner;
+import org.apache.hadoop.hive.ql.optimizer.calcite.reloperators.HiveExcept;
import org.apache.hadoop.hive.ql.optimizer.ppr.PartitionPruner;
import org.apache.hadoop.hive.ql.parse.ParseContext;
import org.apache.hadoop.hive.ql.parse.PrunedPartitionList;
@@ -82,54 +83,34 @@ public class FixedBucketPruningOptimizer extends Transform {
}
}
- public class FixedBucketPartitionWalker extends FilterPruner {
-
- @Override
- protected void generatePredicate(NodeProcessorCtx procCtx,
- FilterOperator fop, TableScanOperator top) throws SemanticException {
- FixedBucketPruningOptimizerCtxt ctxt = ((FixedBucketPruningOptimizerCtxt) procCtx);
- Table tbl = top.getConf().getTableMetadata();
- if (tbl.getNumBuckets() > 0) {
- final int nbuckets = tbl.getNumBuckets();
- ctxt.setNumBuckets(nbuckets);
- ctxt.setBucketCols(tbl.getBucketCols());
- ctxt.setSchema(tbl.getFields());
- if (tbl.isPartitioned()) {
- // Run partition pruner to get partitions
- ParseContext parseCtx = ctxt.pctx;
- PrunedPartitionList prunedPartList;
- try {
- String alias = (String) parseCtx.getTopOps().keySet().toArray()[0];
- prunedPartList = PartitionPruner.prune(top, parseCtx, alias);
- } catch (HiveException e) {
- throw new SemanticException(e.getMessage(), e);
- }
- if (prunedPartList != null) {
- ctxt.setPartitions(prunedPartList);
- for (Partition p : prunedPartList.getPartitions()) {
- if (nbuckets != p.getBucketCount()) {
- // disable feature
- ctxt.setNumBuckets(-1);
- break;
- }
- }
- }
- }
- }
- }
- }
-
public static class BucketBitsetGenerator extends FilterPruner {
@Override
protected void generatePredicate(NodeProcessorCtx procCtx,
- FilterOperator fop, TableScanOperator top) throws SemanticException {
+ FilterOperator fop, TableScanOperator top) throws SemanticException{
FixedBucketPruningOptimizerCtxt ctxt = ((FixedBucketPruningOptimizerCtxt) procCtx);
- if (ctxt.getNumBuckets() <= 0 || ctxt.getBucketCols().size() != 1) {
+ Table tbl = top.getConf().getTableMetadata();
+ int numBuckets = tbl.getNumBuckets();
+ if (numBuckets <= 0 || tbl.getBucketCols().size() != 1) {
// bucketing isn't consistent or there are >1 bucket columns
// optimizer does not extract multiple column predicates for this
return;
}
+
+ if (tbl.isPartitioned()) {
+ // Make sure all the partitions have same bucket count.
+ PrunedPartitionList prunedPartList =
+ PartitionPruner.prune(top, ctxt.pctx, top.getConf().getAlias());
+ if (prunedPartList != null) {
+ for (Partition p : prunedPartList.getPartitions()) {
+ if (numBuckets != p.getBucketCount()) {
+ // disable feature
+ return;
+ }
+ }
+ }
+ }
+
ExprNodeGenericFuncDesc filter = top.getConf().getFilterExpr();
if (filter == null) {
return;
@@ -139,9 +120,9 @@ public class FixedBucketPruningOptimizer extends Transform {
if (sarg == null) {
return;
}
- final String bucketCol = ctxt.getBucketCols().get(0);
+ final String bucketCol = tbl.getBucketCols().get(0);
StructField bucketField = null;
- for (StructField fs : ctxt.getSchema()) {
+ for (StructField fs : tbl.getFields()) {
if(fs.getFieldName().equals(bucketCol)) {
bucketField = fs;
}
@@ -221,7 +202,7 @@ public class FixedBucketPruningOptimizer extends Transform {
}
}
// invariant: bucket-col IN literals of type bucketField
- BitSet bs = new BitSet(ctxt.getNumBuckets());
+ BitSet bs = new BitSet(numBuckets);
bs.clear();
PrimitiveObjectInspector bucketOI = (PrimitiveObjectInspector)bucketField.getFieldObjectInspector();
PrimitiveObjectInspector constOI = PrimitiveObjectInspectorFactory.getPrimitiveWritableObjectInspector(bucketOI.getPrimitiveCategory());
@@ -237,22 +218,22 @@ public class FixedBucketPruningOptimizer extends Transform {
}
Object convCols[] = new Object[] {conv.convert(literal)};
int n = bucketingVersion == 2 ?
- ObjectInspectorUtils.getBucketNumber(convCols, new ObjectInspector[]{constOI}, ctxt.getNumBuckets()) :
- ObjectInspectorUtils.getBucketNumberOld(convCols, new ObjectInspector[]{constOI}, ctxt.getNumBuckets());
+ ObjectInspectorUtils.getBucketNumber(convCols, new ObjectInspector[]{constOI}, numBuckets) :
+ ObjectInspectorUtils.getBucketNumberOld(convCols, new ObjectInspector[]{constOI}, numBuckets);
bs.set(n);
if (bucketingVersion == 1 && ctxt.isCompat()) {
int h = ObjectInspectorUtils.getBucketHashCodeOld(convCols, new ObjectInspector[]{constOI});
// -ve hashcodes had conversion to positive done in different ways in the past
// abs() is now obsolete and all inserts now use & Integer.MAX_VALUE
// the compat mode assumes that old data could've been loaded using the other conversion
- n = ObjectInspectorUtils.getBucketNumber(Math.abs(h), ctxt.getNumBuckets());
+ n = ObjectInspectorUtils.getBucketNumber(Math.abs(h), numBuckets);
bs.set(n);
}
}
- if (bs.cardinality() < ctxt.getNumBuckets()) {
+ if (bs.cardinality() < numBuckets) {
// there is a valid bucket pruning filter
top.getConf().setIncludedBuckets(bs);
- top.getConf().setNumBuckets(ctxt.getNumBuckets());
+ top.getConf().setNumBuckets(numBuckets);
}
}
@@ -339,19 +320,9 @@ public class FixedBucketPruningOptimizer extends Transform {
FixedBucketPruningOptimizerCtxt opPartWalkerCtx = new FixedBucketPruningOptimizerCtxt(compat,
pctx);
- // Retrieve all partitions generated from partition pruner and partition
- // column pruner
+ // walk operator tree to create expression tree for filter buckets
PrunerUtils.walkOperatorTree(pctx, opPartWalkerCtx,
- new FixedBucketPartitionWalker(), new NoopWalker());
-
- if (opPartWalkerCtx.getNumBuckets() < 0) {
- // bail out
- return pctx;
- } else {
- // walk operator tree to create expression tree for filter buckets
- PrunerUtils.walkOperatorTree(pctx, opPartWalkerCtx,
- new BucketBitsetGenerator(), new NoopWalker());
- }
+ new BucketBitsetGenerator(), new NoopWalker());
return pctx;
}
http://git-wip-us.apache.org/repos/asf/hive/blob/e972122e/ql/src/test/queries/clientpositive/tez_fixed_bucket_pruning.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/tez_fixed_bucket_pruning.q b/ql/src/test/queries/clientpositive/tez_fixed_bucket_pruning.q
new file mode 100644
index 0000000..cbc3997
--- /dev/null
+++ b/ql/src/test/queries/clientpositive/tez_fixed_bucket_pruning.q
@@ -0,0 +1,223 @@
+CREATE TABLE l3_clarity__l3_snap_number_2018022300104(l3_snapshot_number bigint)
+ROW FORMAT SERDE
+'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
+STORED AS INPUTFORMAT
+'org.apache.hadoop.mapred.TextInputFormat'
+OUTPUTFORMAT
+'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
+;
+
+CREATE TABLE l3_clarity__l3_monthly_dw_factplan_dw_stg_2018022300104_1(
+ plan_detail_object_id bigint,
+ project_object_id bigint,
+ charge_code_object_id bigint,
+ transclass_object_id bigint,
+ resource_object_id bigint,
+ slice_date varchar(50),
+ split_amount varchar(50),
+ split_units varchar(50),
+ year_key varchar(20),
+ quarter_key varchar(20),
+ month_key varchar(50),
+ week_key varchar(50),
+ date_key varchar(50),
+ fy_year_key varchar(50),
+ fy_quarter_key string,
+ fy_month_key string,
+ supplier_object_id bigint,
+ business_dept_object_id bigint,
+ business_partner_percentage decimal(38,8))
+ROW FORMAT SERDE
+'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
+STORED AS INPUTFORMAT
+'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
+OUTPUTFORMAT
+'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
+;
+
+CREATE TABLE l3_monthly_dw_dimplan(
+ idp_warehouse_id bigint,
+ idp_audit_id bigint,
+ idp_data_date date,
+ l3_snapshot_number bigint,
+ plan_key bigint,
+ project_key bigint,
+ charge_code_key bigint,
+ transclass_key bigint,
+ resource_key bigint,
+ finplan_detail_object_id bigint,
+ project_object_id bigint,
+ txn_class_object_id bigint,
+ charge_code_object_id bigint,
+ resoruce_object_id bigint,
+ plan_name varchar(1500),
+ plan_code varchar(500),
+ plan_type varchar(50),
+ period_type varchar(50),
+ plan_description varchar(3000),
+ plan_status varchar(50),
+ period_start varchar(50),
+ period_end varchar(50),
+ plan_of_record varchar(1),
+ percentage decimal(32,6),
+ l3_created_date timestamp,
+ bmo_cost_type varchar(30),
+ bmo_fiscal_year varchar(50),
+ clarity_updated_date timestamp,
+ is_latest_snapshot bigint,
+ latest_fiscal_budget_plan bigint,
+ plan_category varchar(70),
+ last_updated_by varchar(250))
+CLUSTERED BY (
+idp_data_date)
+INTO 64 BUCKETS
+ROW FORMAT SERDE
+'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
+STORED AS INPUTFORMAT
+'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
+OUTPUTFORMAT
+'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
+;
+
+
+CREATE TABLE l3_monthly_dw_dimplan_unbucketed(
+ idp_warehouse_id bigint,
+ idp_audit_id bigint,
+ idp_data_date date,
+ l3_snapshot_number bigint,
+ plan_key bigint,
+ project_key bigint,
+ charge_code_key bigint,
+ transclass_key bigint,
+ resource_key bigint,
+ finplan_detail_object_id bigint,
+ project_object_id bigint,
+ txn_class_object_id bigint,
+ charge_code_object_id bigint,
+ resoruce_object_id bigint,
+ plan_name varchar(1500),
+ plan_code varchar(500),
+ plan_type varchar(50),
+ period_type varchar(50),
+ plan_description varchar(3000),
+ plan_status varchar(50),
+ period_start varchar(50),
+ period_end varchar(50),
+ plan_of_record varchar(1),
+ percentage decimal(32,6),
+ l3_created_date timestamp,
+ bmo_cost_type varchar(30),
+ bmo_fiscal_year varchar(50),
+ clarity_updated_date timestamp,
+ is_latest_snapshot bigint,
+ latest_fiscal_budget_plan bigint,
+ plan_category varchar(70),
+ last_updated_by varchar(250))
+ROW FORMAT SERDE
+'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
+STORED AS INPUTFORMAT
+'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
+OUTPUTFORMAT
+'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
+;
+
+CREATE TABLE l3_clarity__l3_monthly_dw_factplan_datajoin_1_s2_2018022300104_1(
+ project_key bigint,
+ l3_snapshot_number bigint,
+ l3_created_date timestamp,
+ project_object_id bigint,
+ idp_data_date date)
+ROW FORMAT SERDE
+'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
+STORED AS INPUTFORMAT
+'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
+OUTPUTFORMAT
+'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
+;
+
+
+load data local inpath '../../data/files/bucket_pruning/l3_clarity__l3_monthly_dw_factplan_datajoin_1_s2_2018022300104_1' into table l3_clarity__l3_monthly_dw_factplan_datajoin_1_s2_2018022300104_1;
+load data local inpath '../../data/files/bucket_pruning/l3_clarity__l3_monthly_dw_factplan_dw_stg_2018022300104_1' into table l3_clarity__l3_monthly_dw_factplan_dw_stg_2018022300104_1;
+load data local inpath '../../data/files/bucket_pruning/l3_clarity__l3_snap_number_2018022300104' into table l3_clarity__l3_snap_number_2018022300104;
+load data local inpath '../../data/files/bucket_pruning/l3_monthly_dw_dimplan' into table l3_monthly_dw_dimplan_unbucketed;
+
+INSERT OVERWRITE TABLE l3_monthly_dw_dimplan select * from l3_monthly_dw_dimplan_unbucketed;
+
+analyze table l3_clarity__l3_monthly_dw_factplan_datajoin_1_s2_2018022300104_1 compute statistics;
+analyze table l3_clarity__l3_monthly_dw_factplan_datajoin_1_s2_2018022300104_1 compute statistics for columns;
+
+analyze table l3_clarity__l3_monthly_dw_factplan_dw_stg_2018022300104_1 compute statistics;
+analyze table l3_clarity__l3_monthly_dw_factplan_dw_stg_2018022300104_1 compute statistics for columns;
+
+analyze table l3_clarity__l3_snap_number_2018022300104 compute statistics;
+analyze table l3_clarity__l3_snap_number_2018022300104 compute statistics for columns;
+
+analyze table l3_monthly_dw_dimplan compute statistics;
+analyze table l3_monthly_dw_dimplan compute statistics for columns;
+
+set hive.explain.user=false;
+set hive.auto.convert.join=true;
+set hive.vectorized.execution.enabled=true;
+set hive.auto.convert.join.noconditionaltask.size=200000000;
+set hive.optimize.index.filter=true;
+
+-- fixed bucket pruning off
+set hive.tez.bucket.pruning=false;
+EXPLAIN EXTENDED
+SELECT DW.PROJECT_OBJECT_ID, S1.PLAN_KEY as PLAN_KEY, S2.PROJECT_KEY AS PROJECT_KEY
+FROM l3_clarity__L3_SNAP_NUMBER_2018022300104 snap inner join
+l3_clarity__L3_MONTHLY_DW_FACTPLAN_DW_STG_2018022300104_1 DW on 1=1
+ LEFT OUTER JOIN L3_MONTHLY_DW_DIMPLAN S1
+ ON S1.FINPLAN_DETAIL_OBJECT_ID = DW.PLAN_DETAIL_OBJECT_ID AND S1.L3_SNAPSHOT_NUMBER =snap.L3_snapshot_number
+ AND S1.IDP_DATA_DATE = '2017-12-28'
+ LEFT OUTER JOIN l3_clarity__L3_MONTHLY_DW_FACTPLAN_DATAJOIN_1_s2_2018022300104_1 S2
+ ON S2.PROJECT_OBJECT_ID = DW.PROJECT_OBJECT_ID AND S2.L3_SNAPSHOT_NUMBER =snap.L3_snapshot_number
+ AND S2.IDP_DATA_DATE = '2017-12-28'
+where DW.PROJECT_OBJECT_ID =7147200
+order by DW.PROJECT_OBJECT_ID, PLAN_KEY, PROJECT_KEY
+limit 5;
+
+SELECT DW.PROJECT_OBJECT_ID, S1.PLAN_KEY as PLAN_KEY, S2.PROJECT_KEY AS PROJECT_KEY
+FROM l3_clarity__L3_SNAP_NUMBER_2018022300104 snap inner join
+l3_clarity__L3_MONTHLY_DW_FACTPLAN_DW_STG_2018022300104_1 DW on 1=1
+ LEFT OUTER JOIN L3_MONTHLY_DW_DIMPLAN S1
+ ON S1.FINPLAN_DETAIL_OBJECT_ID = DW.PLAN_DETAIL_OBJECT_ID AND S1.L3_SNAPSHOT_NUMBER =snap.L3_snapshot_number
+ AND S1.IDP_DATA_DATE = '2017-12-28'
+ LEFT OUTER JOIN l3_clarity__L3_MONTHLY_DW_FACTPLAN_DATAJOIN_1_s2_2018022300104_1 S2
+ ON S2.PROJECT_OBJECT_ID = DW.PROJECT_OBJECT_ID AND S2.L3_SNAPSHOT_NUMBER =snap.L3_snapshot_number
+ AND S2.IDP_DATA_DATE = '2017-12-28'
+where DW.PROJECT_OBJECT_ID =7147200
+order by DW.PROJECT_OBJECT_ID, PLAN_KEY, PROJECT_KEY
+limit 5;
+
+
+-- fixed bucket pruning on
+set hive.tez.bucket.pruning=true;
+
+EXPLAIN EXTENDED
+SELECT DW.PROJECT_OBJECT_ID, S1.PLAN_KEY as PLAN_KEY, S2.PROJECT_KEY AS PROJECT_KEY
+FROM l3_clarity__L3_SNAP_NUMBER_2018022300104 snap inner join
+l3_clarity__L3_MONTHLY_DW_FACTPLAN_DW_STG_2018022300104_1 DW on 1=1
+ LEFT OUTER JOIN L3_MONTHLY_DW_DIMPLAN S1
+ ON S1.FINPLAN_DETAIL_OBJECT_ID = DW.PLAN_DETAIL_OBJECT_ID AND S1.L3_SNAPSHOT_NUMBER =snap.L3_snapshot_number
+ AND S1.IDP_DATA_DATE = '2017-12-28'
+ LEFT OUTER JOIN l3_clarity__L3_MONTHLY_DW_FACTPLAN_DATAJOIN_1_s2_2018022300104_1 S2
+ ON S2.PROJECT_OBJECT_ID = DW.PROJECT_OBJECT_ID AND S2.L3_SNAPSHOT_NUMBER =snap.L3_snapshot_number
+ AND S2.IDP_DATA_DATE = '2017-12-28'
+where DW.PROJECT_OBJECT_ID =7147200
+order by DW.PROJECT_OBJECT_ID, PLAN_KEY, PROJECT_KEY
+limit 5;
+
+SELECT DW.PROJECT_OBJECT_ID, S1.PLAN_KEY as PLAN_KEY, S2.PROJECT_KEY AS PROJECT_KEY
+FROM l3_clarity__L3_SNAP_NUMBER_2018022300104 snap inner join
+l3_clarity__L3_MONTHLY_DW_FACTPLAN_DW_STG_2018022300104_1 DW on 1=1
+ LEFT OUTER JOIN L3_MONTHLY_DW_DIMPLAN S1
+ ON S1.FINPLAN_DETAIL_OBJECT_ID = DW.PLAN_DETAIL_OBJECT_ID AND S1.L3_SNAPSHOT_NUMBER =snap.L3_snapshot_number
+ AND S1.IDP_DATA_DATE = '2017-12-28'
+ LEFT OUTER JOIN l3_clarity__L3_MONTHLY_DW_FACTPLAN_DATAJOIN_1_s2_2018022300104_1 S2
+ ON S2.PROJECT_OBJECT_ID = DW.PROJECT_OBJECT_ID AND S2.L3_SNAPSHOT_NUMBER =snap.L3_snapshot_number
+ AND S2.IDP_DATA_DATE = '2017-12-28'
+where DW.PROJECT_OBJECT_ID =7147200
+order by DW.PROJECT_OBJECT_ID, PLAN_KEY, PROJECT_KEY
+limit 5;
+