This is an automated email from the ASF dual-hosted git repository.

krisztiankasa pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/hive.git


The following commit(s) were added to refs/heads/master by this push:
     new a1889d10693 HIVE-27080: Support project pushdown in JDBC storage 
handler even when filters are not pushed (Soumyakanti Das, reviewed by 
Krisztian Kasa)
a1889d10693 is described below

commit a1889d10693567c9366d96fa4bb4fe398ead5323
Author: Soumyakanti Das <[email protected]>
AuthorDate: Thu Sep 12 02:08:57 2024 -0700

    HIVE-27080: Support project pushdown in JDBC storage handler even when 
filters are not pushed (Soumyakanti Das, reviewed by Krisztian Kasa)
---
 .../rules/HiveAggregateProjectMergeRule.java       |  13 +-
 .../optimizer/calcite/rules/RelFieldTrimmer.java   | 170 +++++-
 .../rules/jdbc/JDBCAggregateProjectMergeRule.java  |  42 ++
 .../calcite/translator/PlanModifierForASTConv.java |   9 +-
 .../hadoop/hive/ql/parse/CalcitePlanner.java       |   2 +
 .../queries/clientpositive/jdbc_project_pushdown.q | 106 ++++
 .../clientpositive/llap/cbo_jdbc_joincost.q.out    |  12 +-
 .../llap/external_jdbc_rowcount.q.out              |  32 +-
 .../clientpositive/llap/external_jdbc_table2.q.out |   3 +-
 .../clientpositive/llap/external_jdbc_table3.q.out |   6 +-
 .../clientpositive/llap/external_jdbc_table4.q.out |   6 +-
 .../llap/jdbc_project_pushdown.q.out               | 671 +++++++++++++++++++++
 12 files changed, 1037 insertions(+), 35 deletions(-)

diff --git 
a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/HiveAggregateProjectMergeRule.java
 
b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/HiveAggregateProjectMergeRule.java
index 59b6a3b9a57..89669a5c68d 100644
--- 
a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/HiveAggregateProjectMergeRule.java
+++ 
b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/HiveAggregateProjectMergeRule.java
@@ -19,7 +19,9 @@ package org.apache.hadoop.hive.ql.optimizer.calcite.rules;
 import org.apache.calcite.plan.RelOptRuleCall;
 import org.apache.calcite.rel.core.Aggregate;
 import org.apache.calcite.rel.core.AggregateCall;
+import org.apache.calcite.rel.core.Project;
 import org.apache.calcite.rel.rules.AggregateProjectMergeRule;
+import org.apache.calcite.tools.RelBuilderFactory;
 import org.apache.hadoop.hive.ql.optimizer.calcite.HiveRelFactories;
 import org.apache.hadoop.hive.ql.optimizer.calcite.reloperators.HiveAggregate;
 import org.apache.hadoop.hive.ql.optimizer.calcite.reloperators.HiveGroupingID;
@@ -38,11 +40,14 @@ import 
org.apache.hadoop.hive.ql.optimizer.calcite.reloperators.HiveProject;
  */
 public class HiveAggregateProjectMergeRule extends AggregateProjectMergeRule {
   public static final HiveAggregateProjectMergeRule INSTANCE =
-      new HiveAggregateProjectMergeRule();
+      new HiveAggregateProjectMergeRule(HiveAggregate.class, 
HiveProject.class, HiveRelFactories.HIVE_BUILDER);
 
-  /** Private constructor. */
-  private HiveAggregateProjectMergeRule() {
-    super(HiveAggregate.class, HiveProject.class, 
HiveRelFactories.HIVE_BUILDER);
+  protected HiveAggregateProjectMergeRule(
+      Class<? extends Aggregate> aggregateClass,
+      Class<? extends Project> projectClass,
+      RelBuilderFactory relBuilderFactory
+  ) {
+    super(aggregateClass, projectClass, relBuilderFactory);
   }
 
   @Override
diff --git 
a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/RelFieldTrimmer.java
 
b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/RelFieldTrimmer.java
index 5cefd025db2..a556ddf504d 100644
--- 
a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/RelFieldTrimmer.java
+++ 
b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/RelFieldTrimmer.java
@@ -16,6 +16,18 @@
  */
 package org.apache.hadoop.hive.ql.optimizer.calcite.rules;
 
+import com.google.common.collect.ImmutableList;
+import com.google.common.collect.Iterables;
+import org.apache.calcite.adapter.jdbc.JdbcConvention;
+import org.apache.calcite.adapter.jdbc.JdbcRel;
+import org.apache.calcite.adapter.jdbc.JdbcRules.JdbcAggregate;
+import org.apache.calcite.adapter.jdbc.JdbcRules.JdbcAggregateRule;
+import org.apache.calcite.adapter.jdbc.JdbcRules.JdbcFilter;
+import org.apache.calcite.adapter.jdbc.JdbcRules.JdbcFilterRule;
+import org.apache.calcite.adapter.jdbc.JdbcRules.JdbcJoin;
+import org.apache.calcite.adapter.jdbc.JdbcRules.JdbcJoinRule;
+import org.apache.calcite.adapter.jdbc.JdbcRules.JdbcProject;
+import org.apache.calcite.adapter.jdbc.JdbcRules.JdbcProjectRule;
 import org.apache.calcite.linq4j.Ord;
 import org.apache.calcite.plan.RelOptCluster;
 import org.apache.calcite.plan.RelOptUtil;
@@ -64,9 +76,10 @@ import org.apache.calcite.util.mapping.IntPair;
 import org.apache.calcite.util.mapping.Mapping;
 import org.apache.calcite.util.mapping.MappingType;
 import org.apache.calcite.util.mapping.Mappings;
-
-import com.google.common.collect.ImmutableList;
-import com.google.common.collect.Iterables;
+import 
org.apache.hadoop.hive.ql.optimizer.calcite.reloperators.jdbc.HiveJdbcConverter;
+import 
org.apache.hadoop.hive.ql.optimizer.calcite.reloperators.jdbc.JdbcHiveTableScan;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
 
 import java.math.BigDecimal;
 import java.util.ArrayList;
@@ -74,8 +87,6 @@ import java.util.Collections;
 import java.util.LinkedHashSet;
 import java.util.List;
 import java.util.Set;
-import org.slf4j.Logger;
-import org.slf4j.LoggerFactory;
 
 /**
  * This class comes from Calcite almost as-is. The only change concerns
@@ -351,6 +362,62 @@ public class RelFieldTrimmer implements ReflectiveVisitor {
         Mappings.createIdentity(rel.getRowType().getFieldCount()));
   }
 
+  public TrimResult trimFields(
+      JdbcRel rel,
+      ImmutableBitSet fieldsUsed,
+      Set<RelDataTypeField> extraFields) {
+    // We don't know how to trim this kind of relational expression, so give
+    // it back intact.
+    Util.discard(fieldsUsed);
+    return result(rel,
+        Mappings.createIdentity(rel.getRowType().getFieldCount()));
+  }
+  
+  public TrimResult trimFields(
+      HiveJdbcConverter converter,
+      ImmutableBitSet fieldsUsed,
+      Set<RelDataTypeField> extraFields) {
+    final int fieldCount = converter.getRowType().getFieldCount();
+    final RelNode input = converter.getInput();
+    
+    TrimResult trimResult = trimChild(converter, converter.getInput(), 
fieldsUsed, extraFields);
+    RelNode newInput = trimResult.left;
+    final Mapping inputMapping = trimResult.right;
+
+    // If the input is unchanged, and we need to project all columns,
+    // there's nothing we can do.
+    if (newInput == input
+        && fieldsUsed.cardinality() == fieldCount) {
+      return result(converter, Mappings.createIdentity(fieldCount));
+    }
+    
+    HiveJdbcConverter newConverter = (HiveJdbcConverter) 
converter.copy(converter.getTraitSet(), newInput);
+    
+    return result(newConverter, inputMapping);
+  }
+
+  public TrimResult trimFields(
+      final JdbcProject jdbcProject,
+      ImmutableBitSet fieldsUsed,
+      Set<RelDataTypeField> extraFields) {
+
+    TrimResult trimResult = trimFields((Project) jdbcProject, fieldsUsed, 
extraFields);
+    RelNode newInput = trimResult.left;
+    final Mapping inputMapping = trimResult.right;
+    
+    if (newInput instanceof JdbcProject) {
+      return trimResult;
+    }
+    
+    if (newInput instanceof Project) {
+      JdbcProject newProject =
+          (JdbcProject) JdbcProjectRule.create((JdbcConvention) 
jdbcProject.getConvention()).convert(newInput);
+      return result(newProject, inputMapping);
+    }
+
+    return trimFields((RelNode) jdbcProject, fieldsUsed, extraFields);
+  }
+
   /**
    * Variant of {@link #trimFields(RelNode, ImmutableBitSet, Set)} for
    * {@link org.apache.calcite.rel.logical.LogicalProject}.
@@ -446,6 +513,28 @@ public class RelFieldTrimmer implements ReflectiveVisitor {
     return result(relBuilder.build(), mapping);
   }
 
+  public TrimResult trimFields(
+      final JdbcFilter jdbcFilter,
+      ImmutableBitSet fieldsUsed,
+      Set<RelDataTypeField> extraFields) {
+
+    TrimResult trimResult = trimFields((Filter) jdbcFilter, fieldsUsed, 
extraFields);
+    RelNode newInput = trimResult.left;
+    final Mapping inputMapping = trimResult.right;
+
+    if (newInput instanceof JdbcFilter) {
+      return trimResult;
+    }
+
+    if (newInput instanceof Filter) {
+      JdbcFilter newFilter = 
+          (JdbcFilter) JdbcFilterRule.create((JdbcConvention) 
jdbcFilter.getConvention()).convert(newInput);
+      return result(newFilter, inputMapping);
+    }
+
+    return trimFields((RelNode) jdbcFilter, fieldsUsed, extraFields);
+  }
+
   /**
    * Variant of {@link #trimFields(RelNode, ImmutableBitSet, Set)} for
    * {@link org.apache.calcite.rel.logical.LogicalFilter}.
@@ -555,6 +644,28 @@ public class RelFieldTrimmer implements ReflectiveVisitor {
     return result(relBuilder.build(), inputMapping);
   }
 
+  public TrimResult trimFields(
+      final JdbcJoin jdbcJoin,
+      ImmutableBitSet fieldsUsed,
+      Set<RelDataTypeField> extraFields) {
+
+    TrimResult trimResult = trimFields((Join) jdbcJoin, fieldsUsed, 
extraFields);
+    RelNode newInput = trimResult.left;
+    final Mapping inputMapping = trimResult.right;
+
+    if (newInput instanceof JdbcJoin) {
+      return trimResult;
+    }
+
+    if (newInput instanceof Join) {
+      JdbcJoin newJoin =
+          (JdbcJoin) JdbcJoinRule.create((JdbcConvention) 
jdbcJoin.getConvention()).convert(newInput);
+      return result(newJoin, inputMapping);
+    }
+
+    return trimFields((RelNode) jdbcJoin, fieldsUsed, extraFields);
+  }
+
   /**
    * Variant of {@link #trimFields(RelNode, ImmutableBitSet, Set)} for
    * {@link org.apache.calcite.rel.logical.LogicalJoin}.
@@ -781,6 +892,28 @@ public class RelFieldTrimmer implements ReflectiveVisitor {
     return result(relBuilder.build(), mapping);
   }
 
+  public TrimResult trimFields(
+      final JdbcAggregate jdbcAggregate,
+      ImmutableBitSet fieldsUsed,
+      Set<RelDataTypeField> extraFields) {
+
+    TrimResult trimResult = trimFields((Aggregate) jdbcAggregate, fieldsUsed, 
extraFields);
+    RelNode newInput = trimResult.left;
+    final Mapping inputMapping = trimResult.right;
+
+    if (newInput instanceof JdbcAggregate) {
+      return trimResult;
+    }
+
+    if (newInput instanceof Aggregate) {
+      JdbcAggregate newAggregate =
+          (JdbcAggregate) JdbcAggregateRule.create((JdbcConvention) 
jdbcAggregate.getConvention()).convert(newInput);
+      return result(newAggregate, inputMapping);
+    }
+
+    return trimFields((RelNode) jdbcAggregate, fieldsUsed, extraFields);
+  }
+
   /**
    * Variant of {@link #trimFields(RelNode, ImmutableBitSet, Set)} for
    * {@link org.apache.calcite.rel.logical.LogicalAggregate}.
@@ -1044,6 +1177,33 @@ public class RelFieldTrimmer implements 
ReflectiveVisitor {
     return mapping;
   }
 
+  public TrimResult trimFields(
+      final JdbcHiveTableScan jdbcHiveTableScan,
+      ImmutableBitSet fieldsUsed,
+      Set<RelDataTypeField> extraFields) {
+    
+    TrimResult trimResult = trimFields((TableScan) jdbcHiveTableScan, 
fieldsUsed, extraFields);
+    RelNode newInput = trimResult.left;
+    final Mapping inputMapping = trimResult.right;
+    
+    if (newInput instanceof JdbcHiveTableScan) {
+      return trimResult;
+    }
+    
+    if (newInput instanceof Project && ((Project) newInput).getInput() 
instanceof JdbcHiveTableScan) {
+      JdbcProject newProject = new JdbcProject(
+          newInput.getCluster(),
+          jdbcHiveTableScan.getTraitSet(),
+          ((Project) newInput).getInput(),
+          ((Project) newInput).getProjects(),
+          newInput.getRowType()
+      );
+      return result(newProject, inputMapping);
+    }
+    
+    return trimFields((RelNode) jdbcHiveTableScan, fieldsUsed, extraFields);
+  }
+
   /**
    * Variant of {@link #trimFields(RelNode, ImmutableBitSet, Set)} for
    * {@link org.apache.calcite.rel.logical.LogicalTableScan}.
diff --git 
a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/jdbc/JDBCAggregateProjectMergeRule.java
 
b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/jdbc/JDBCAggregateProjectMergeRule.java
new file mode 100644
index 00000000000..983a1b27e49
--- /dev/null
+++ 
b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/jdbc/JDBCAggregateProjectMergeRule.java
@@ -0,0 +1,42 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements.  See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License.  You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.hadoop.hive.ql.optimizer.calcite.rules.jdbc;
+
+import org.apache.calcite.adapter.jdbc.JdbcRules.JdbcAggregate;
+import org.apache.calcite.adapter.jdbc.JdbcRules.JdbcProject;
+import org.apache.hadoop.hive.ql.optimizer.calcite.HiveRelFactories;
+import 
org.apache.hadoop.hive.ql.optimizer.calcite.rules.HiveAggregateProjectMergeRule;
+
+/**
+ * Planner rule that recognizes a {@link JdbcAggregate}
+ * on top of a {@link JdbcProject} and if possible
+ * aggregate through the project or removes the project.
+ *
+ * <p>This is only possible when the grouping expressions and arguments to
+ * the aggregate functions are field references (i.e. not expressions).
+ *
+ * <p>In some cases, this rule has the effect of trimming: the aggregate will
+ * use fewer columns than the project did.
+ */
+public class JDBCAggregateProjectMergeRule extends 
HiveAggregateProjectMergeRule {
+  public static final JDBCAggregateProjectMergeRule INSTANCE = 
+      new JDBCAggregateProjectMergeRule();
+  
+  private JDBCAggregateProjectMergeRule() {
+    super(JdbcAggregate.class, JdbcProject.class, 
HiveRelFactories.HIVE_BUILDER);
+  }
+}
diff --git 
a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/translator/PlanModifierForASTConv.java
 
b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/translator/PlanModifierForASTConv.java
index 7861cc20e95..f2f41f18133 100644
--- 
a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/translator/PlanModifierForASTConv.java
+++ 
b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/translator/PlanModifierForASTConv.java
@@ -23,6 +23,9 @@ import java.util.List;
 import java.util.stream.Collectors;
 
 import org.apache.calcite.adapter.druid.DruidQuery;
+import org.apache.calcite.adapter.jdbc.JdbcConvention;
+import org.apache.calcite.adapter.jdbc.JdbcRel;
+import org.apache.calcite.adapter.jdbc.JdbcRules;
 import org.apache.calcite.plan.RelOptUtil;
 import org.apache.calcite.plan.hep.HepRelVertex;
 import org.apache.calcite.plan.volcano.RelSubset;
@@ -266,8 +269,12 @@ public class PlanModifierForASTConv {
   private static RelNode introduceDerivedTable(final RelNode rel) {
     List<RexNode> projectList = 
HiveCalciteUtil.getProjsFromBelowAsInputRef(rel);
 
-    HiveProject select = HiveProject.create(rel.getCluster(), rel, projectList,
+    RelNode select = HiveProject.create(rel.getCluster(), rel, projectList,
         rel.getRowType(), Collections.emptyList());
+    
+    if (rel instanceof JdbcRel) {
+      select = JdbcRules.JdbcProjectRule.create((JdbcConvention) 
rel.getConvention()).convert(select);
+    }
 
     return select;
   }
diff --git a/ql/src/java/org/apache/hadoop/hive/ql/parse/CalcitePlanner.java 
b/ql/src/java/org/apache/hadoop/hive/ql/parse/CalcitePlanner.java
index e9f5fde1a22..3e97b7d6331 100644
--- a/ql/src/java/org/apache/hadoop/hive/ql/parse/CalcitePlanner.java
+++ b/ql/src/java/org/apache/hadoop/hive/ql/parse/CalcitePlanner.java
@@ -172,6 +172,7 @@ import 
org.apache.hadoop.hive.ql.optimizer.calcite.rules.HiveAggregateSortLimitR
 import 
org.apache.hadoop.hive.ql.optimizer.calcite.rules.HiveJoinSwapConstraintsRule;
 import 
org.apache.hadoop.hive.ql.optimizer.calcite.rules.HiveRemoveEmptySingleRules;
 import 
org.apache.hadoop.hive.ql.optimizer.calcite.rules.HiveSemiJoinProjectTransposeRule;
+import 
org.apache.hadoop.hive.ql.optimizer.calcite.rules.jdbc.JDBCAggregateProjectMergeRule;
 import 
org.apache.hadoop.hive.ql.optimizer.calcite.rules.views.HiveMaterializationRelMetadataProvider;
 import org.apache.hadoop.hive.ql.optimizer.calcite.HivePlannerContext;
 import org.apache.hadoop.hive.ql.optimizer.calcite.HiveRelDistribution;
@@ -2293,6 +2294,7 @@ public class CalcitePlanner extends SemanticAnalyzer {
         rules.add(JDBCFilterJoinRule.INSTANCE);
         rules.add(JDBCFilterPushDownRule.INSTANCE);
         rules.add(JDBCProjectPushDownRule.INSTANCE);
+        rules.add(JDBCAggregateProjectMergeRule.INSTANCE);
         if (!conf.getBoolVar(ConfVars.HIVE_ENABLE_JDBC_SAFE_PUSHDOWN)) {
           rules.add(JDBCJoinPushDownRule.INSTANCE);
           rules.add(JDBCUnionPushDownRule.INSTANCE);
diff --git a/ql/src/test/queries/clientpositive/jdbc_project_pushdown.q 
b/ql/src/test/queries/clientpositive/jdbc_project_pushdown.q
new file mode 100644
index 00000000000..3274a085898
--- /dev/null
+++ b/ql/src/test/queries/clientpositive/jdbc_project_pushdown.q
@@ -0,0 +1,106 @@
+--!qt:database:postgres:q_test_author_book_tables.sql
+
+CREATE EXTERNAL TABLE book
+(
+    id int,
+    title varchar(20),
+    author int
+)
+STORED BY                                          
+'org.apache.hive.storage.jdbc.JdbcStorageHandler'
+TBLPROPERTIES (                                    
+    "hive.sql.database.type" = "POSTGRES",
+    "hive.sql.jdbc.driver" = "org.postgresql.Driver",
+    "hive.sql.jdbc.url" = "jdbc:postgresql://localhost:5432/qtestDB",
+    "hive.sql.dbcp.username" = "qtestuser",
+    "hive.sql.dbcp.password" = "qtestpassword",
+    "hive.sql.table" = "book"
+);
+
+CREATE EXTERNAL TABLE author
+( id int,
+fname string,
+lname string)
+STORED BY
+'org.apache.hive.storage.jdbc.JdbcStorageHandler'
+TBLPROPERTIES (                                    
+    "hive.sql.database.type" = "POSTGRES",
+    "hive.sql.jdbc.driver" = "org.postgresql.Driver",
+    "hive.sql.jdbc.url" = "jdbc:postgresql://localhost:5432/qtestDB",
+    "hive.sql.dbcp.username" = "qtestuser",
+    "hive.sql.dbcp.password" = "qtestpassword",
+    "hive.sql.table" = "author"
+);
+
+explain cbo select id from book where substring(title, 0, 4) = 'Les';
+explain select id from book where substring(title, 0, 4) = 'Les';
+select id from book where substring(title, 0, 4) = 'Les';
+----
+
+explain cbo select id from book where ucase(title) = 'LES MISERABLES';
+explain select id from book where ucase(title) = 'LES MISERABLES';
+select id from book where ucase(title) = 'LES MISERABLES';
+----
+
+explain cbo select book.title, author.fname from book join author on 
book.author = author.id;
+explain select book.title, author.fname from book join author on book.author = 
author.id;
+select book.title, author.fname from book join author on book.author = 
author.id;
+----
+
+explain cbo select book.title, author.fname from book join author
+where book.author = author.id
+and ucase(book.title) = 'LES MISERABLES'
+and substring(author.lname, 0, 4) = 'Hug';
+
+explain select book.title, author.fname from book join author
+where book.author = author.id
+and ucase(book.title) = 'LES MISERABLES'
+and substring(author.lname, 0, 4) = 'Hug';
+
+select book.title, author.fname from book join author
+where book.author = author.id
+and ucase(book.title) = 'LES MISERABLES'
+and substring(author.lname, 0, 4) = 'Hug';
+----
+
+explain cbo 
+select author.fname, count(book.title) as books
+from book join author
+where book.author = author.id
+group by author.fname;
+
+explain 
+select author.fname, count(book.title) as books
+from book join author
+where book.author = author.id
+group by author.fname;
+
+select author.fname, count(book.title) as books
+from book join author
+where book.author = author.id
+group by author.fname;
+----
+
+explain cbo 
+select ucase(author.fname), count(book.title) as books
+from book join author
+where book.author = author.id
+group by author.fname
+order by ucase(author.fname)
+limit 5;
+
+explain 
+select ucase(author.fname), count(book.title) as books
+from book join author
+where book.author = author.id
+group by author.fname
+order by ucase(author.fname)
+limit 5;
+
+select ucase(author.fname), count(book.title) as books
+from book join author
+where book.author = author.id
+group by author.fname
+order by ucase(author.fname)
+limit 5;
+----
diff --git a/ql/src/test/results/clientpositive/llap/cbo_jdbc_joincost.q.out 
b/ql/src/test/results/clientpositive/llap/cbo_jdbc_joincost.q.out
index 0dc3effcef3..e7766ea6dfa 100644
--- a/ql/src/test/results/clientpositive/llap/cbo_jdbc_joincost.q.out
+++ b/ql/src/test/results/clientpositive/llap/cbo_jdbc_joincost.q.out
@@ -84,10 +84,12 @@ CBO PLAN:
 HiveJdbcConverter(convention=[JDBC.MYSQL]): rowcount = ###Masked###, 
cumulative cost = ###Masked###, id = ###Masked###
   JdbcProject(lname=[$1], title=[$2]): rowcount = ###Masked###, cumulative 
cost = ###Masked###, id = ###Masked###
     JdbcJoin(condition=[=($0, $3)], joinType=[inner]): rowcount = 
###Masked###, cumulative cost = ###Masked###, id = ###Masked###
-      JdbcProject(id=[$0], lname=[$2]): rowcount = ###Masked###, cumulative 
cost = ###Masked###, id = ###Masked###
+      JdbcProject(id=[$0], lname=[$1]): rowcount = ###Masked###, cumulative 
cost = ###Masked###, id = ###Masked###
         JdbcFilter(condition=[IS NOT NULL($0)]): rowcount = ###Masked###, 
cumulative cost = ###Masked###, id = ###Masked###
-          JdbcHiveTableScan(table=[[default, author]], table:alias=[a]): 
rowcount = ###Masked###, cumulative cost = ###Masked###, id = ###Masked###
-      JdbcProject(title=[$1], author=[$2]): rowcount = ###Masked###, 
cumulative cost = ###Masked###, id = ###Masked###
-        JdbcFilter(condition=[IS NOT NULL($2)]): rowcount = ###Masked###, 
cumulative cost = ###Masked###, id = ###Masked###
-          JdbcHiveTableScan(table=[[default, book]], table:alias=[b]): 
rowcount = ###Masked###, cumulative cost = ###Masked###, id = ###Masked###
+          JdbcProject(id=[$0], lname=[$2]): rowcount = ###Masked###, 
cumulative cost = ###Masked###, id = ###Masked###
+            JdbcHiveTableScan(table=[[default, author]], table:alias=[a]): 
rowcount = ###Masked###, cumulative cost = ###Masked###, id = ###Masked###
+      JdbcProject(title=[$0], author=[$1]): rowcount = ###Masked###, 
cumulative cost = ###Masked###, id = ###Masked###
+        JdbcFilter(condition=[IS NOT NULL($1)]): rowcount = ###Masked###, 
cumulative cost = ###Masked###, id = ###Masked###
+          JdbcProject(title=[$1], author=[$2]): rowcount = ###Masked###, 
cumulative cost = ###Masked###, id = ###Masked###
+            JdbcHiveTableScan(table=[[default, book]], table:alias=[b]): 
rowcount = ###Masked###, cumulative cost = ###Masked###, id = ###Masked###
 
diff --git 
a/ql/src/test/results/clientpositive/llap/external_jdbc_rowcount.q.out 
b/ql/src/test/results/clientpositive/llap/external_jdbc_rowcount.q.out
index 6cb4f73652c..10b6e1f93cd 100644
--- a/ql/src/test/results/clientpositive/llap/external_jdbc_rowcount.q.out
+++ b/ql/src/test/results/clientpositive/llap/external_jdbc_rowcount.q.out
@@ -271,21 +271,25 @@ HiveJdbcConverter(convention=[JDBC.DERBY])
       JdbcFilter(condition=[IS NULL($3)])
         JdbcJoin(condition=[AND(=($1, $4), =($0, $3))], joinType=[left])
           JdbcProject(fkey=[$1], ikey=[$0])
-            JdbcAggregate(group=[{0, 2}])
-              JdbcFilter(condition=[IS NULL($6)])
-                JdbcJoin(condition=[=($0, $4)], joinType=[left])
-                  JdbcHiveTableScan(table=[[default, 
ext_simple_derby_table11]], table:alias=[ext_simple_derby_table11])
-                  HiveProject(ikey2=[$0], bkey2=[$1], fkey2=[$2], dkey2=[$3])
+            JdbcAggregate(group=[{0, 1}])
+              JdbcFilter(condition=[IS NULL($4)])
+                JdbcJoin(condition=[=($0, $3)], joinType=[left])
+                  JdbcProject(ikey=[$0], fkey=[$2], dkey=[$3])
+                    JdbcHiveTableScan(table=[[default, 
ext_simple_derby_table11]], table:alias=[ext_simple_derby_table11])
+                  JdbcProject(ikey2=[$0], fkey2=[$1], dkey2=[$2])
                     JdbcFilter(condition=[IS NOT NULL($0)])
-                      JdbcHiveTableScan(table=[[default, 
ext_simple_derby_table13]], table:alias=[ext_simple_derby_table13])
+                      JdbcProject(ikey2=[$0], fkey2=[$2], dkey2=[$3])
+                        JdbcHiveTableScan(table=[[default, 
ext_simple_derby_table13]], table:alias=[ext_simple_derby_table13])
           JdbcProject(datekey=[$2], fkey=[$1], ikey=[$0])
-            JdbcAggregate(group=[{0, 2, 4}])
-              JdbcFilter(condition=[IS NULL($7)])
-                JdbcJoin(condition=[=($0, $5)], joinType=[left])
-                  HiveProject(ikey=[$0], bkey=[$1], fkey=[$2], dkey=[$3], 
datekey=[$4])
-                    JdbcFilter(condition=[AND(IS NOT NULL($0), IS NOT 
NULL($2))])
-                      JdbcHiveTableScan(table=[[default, 
ext_simple_derby_table12]], table:alias=[ext_simple_derby_table12])
-                  HiveProject(ikey2=[$0], bkey2=[$1], fkey2=[$2], dkey2=[$3], 
datekey2=[$4])
+            JdbcAggregate(group=[{0, 1, 3}])
+              JdbcFilter(condition=[IS NULL($5)])
+                JdbcJoin(condition=[=($0, $4)], joinType=[left])
+                  JdbcProject(ikey=[$0], fkey=[$1], dkey=[$2], datekey=[$3])
+                    JdbcFilter(condition=[AND(IS NOT NULL($0), IS NOT 
NULL($1))])
+                      JdbcProject(ikey=[$0], fkey=[$2], dkey=[$3], 
datekey=[$4])
+                        JdbcHiveTableScan(table=[[default, 
ext_simple_derby_table12]], table:alias=[ext_simple_derby_table12])
+                  JdbcProject(ikey2=[$0], fkey2=[$1], dkey2=[$2])
                     JdbcFilter(condition=[IS NOT NULL($0)])
-                      JdbcHiveTableScan(table=[[default, 
ext_simple_derby_table14]], table:alias=[ext_simple_derby_table14])
+                      JdbcProject(ikey2=[$0], fkey2=[$2], dkey2=[$3])
+                        JdbcHiveTableScan(table=[[default, 
ext_simple_derby_table14]], table:alias=[ext_simple_derby_table14])
 
diff --git a/ql/src/test/results/clientpositive/llap/external_jdbc_table2.q.out 
b/ql/src/test/results/clientpositive/llap/external_jdbc_table2.q.out
index 547dc662b36..f6b2e175825 100644
--- a/ql/src/test/results/clientpositive/llap/external_jdbc_table2.q.out
+++ b/ql/src/test/results/clientpositive/llap/external_jdbc_table2.q.out
@@ -714,7 +714,8 @@ STAGE PLANS:
           alias: p
           properties:
             hive.sql.query SELECT "ikey"
-FROM "EXTERNAL_JDBC_SIMPLE_DERBY2_TABLE1"
+FROM (SELECT "ikey", "bkey", "dkey"
+FROM "EXTERNAL_JDBC_SIMPLE_DERBY2_TABLE1") AS "t"
 WHERE ("bkey" = 10 AND "dkey" = 15.15 OR "bkey" = 20 AND "dkey" = 25.25) AND 
"bkey" IN (10, 20) AND ("dkey" IN (15.15, 25.25) AND "ikey" IS NOT NULL)
             hive.sql.query.fieldNames ikey
             hive.sql.query.fieldTypes int
diff --git a/ql/src/test/results/clientpositive/llap/external_jdbc_table3.q.out 
b/ql/src/test/results/clientpositive/llap/external_jdbc_table3.q.out
index d1ccf2ab928..e2f3dd048b7 100644
--- a/ql/src/test/results/clientpositive/llap/external_jdbc_table3.q.out
+++ b/ql/src/test/results/clientpositive/llap/external_jdbc_table3.q.out
@@ -204,7 +204,8 @@ STAGE PLANS:
                   alias: db1_ext_auth3_1
                   properties:
                     hive.sql.query SELECT "ikey", "fkey"
-FROM "EXTERNAL_JDBC_SIMPLE_DERBY2_TABLE1"
+FROM (SELECT "ikey", "fkey"
+FROM "EXTERNAL_JDBC_SIMPLE_DERBY2_TABLE1") AS "t"
 WHERE "ikey" > 20
                     hive.sql.query.fieldNames ikey,fkey
                     hive.sql.query.fieldTypes int,float
@@ -229,7 +230,8 @@ WHERE "ikey" > 20
                   alias: db1_ext_auth3_2
                   properties:
                     hive.sql.query SELECT "ikey", "bkey"
-FROM "EXTERNAL_JDBC_SIMPLE_DERBY2_TABLE2"
+FROM (SELECT "ikey", "bkey"
+FROM "EXTERNAL_JDBC_SIMPLE_DERBY2_TABLE2") AS "t"
 WHERE "ikey" > 20
                     hive.sql.query.fieldNames ikey,bkey
                     hive.sql.query.fieldTypes int,bigint
diff --git a/ql/src/test/results/clientpositive/llap/external_jdbc_table4.q.out 
b/ql/src/test/results/clientpositive/llap/external_jdbc_table4.q.out
index a708655d2d1..a897df577b1 100644
--- a/ql/src/test/results/clientpositive/llap/external_jdbc_table4.q.out
+++ b/ql/src/test/results/clientpositive/llap/external_jdbc_table4.q.out
@@ -558,11 +558,11 @@ STAGE PLANS:
         TableScan
           alias: db1_ext_auth1
           properties:
-            hive.sql.query SELECT "ikey"
-FROM (SELECT "IKEY" AS "ikey"
+            hive.sql.query SELECT "IKEY"
+FROM (SELECT "IKEY"
 FROM "EXTERNAL_JDBC_SIMPLE_DERBY2_TABLE1"
 FETCH NEXT 10 ROWS ONLY) AS "t0"
-            hive.sql.query.fieldNames ikey
+            hive.sql.query.fieldNames IKEY
             hive.sql.query.fieldTypes int
             hive.sql.query.split false
           Select Operator
diff --git 
a/ql/src/test/results/clientpositive/llap/jdbc_project_pushdown.q.out 
b/ql/src/test/results/clientpositive/llap/jdbc_project_pushdown.q.out
new file mode 100644
index 00000000000..8525940bed8
--- /dev/null
+++ b/ql/src/test/results/clientpositive/llap/jdbc_project_pushdown.q.out
@@ -0,0 +1,671 @@
+PREHOOK: query: CREATE EXTERNAL TABLE book
+(
+    id int,
+    title varchar(20),
+    author int
+)
+STORED BY                                          
+'org.apache.hive.storage.jdbc.JdbcStorageHandler'
+TBLPROPERTIES (                                    
+    "hive.sql.database.type" = "POSTGRES",
+    "hive.sql.jdbc.driver" = "org.postgresql.Driver",
+    "hive.sql.jdbc.url" = "jdbc:postgresql://localhost:5432/qtestDB",
+    "hive.sql.dbcp.username" = "qtestuser",
+    "hive.sql.dbcp.password" = "qtestpassword",
+    "hive.sql.table" = "book"
+)
+PREHOOK: type: CREATETABLE
+PREHOOK: Output: database:default
+PREHOOK: Output: default@book
+POSTHOOK: query: CREATE EXTERNAL TABLE book
+(
+    id int,
+    title varchar(20),
+    author int
+)
+STORED BY                                          
+'org.apache.hive.storage.jdbc.JdbcStorageHandler'
+TBLPROPERTIES (                                    
+    "hive.sql.database.type" = "POSTGRES",
+    "hive.sql.jdbc.driver" = "org.postgresql.Driver",
+    "hive.sql.jdbc.url" = "jdbc:postgresql://localhost:5432/qtestDB",
+    "hive.sql.dbcp.username" = "qtestuser",
+    "hive.sql.dbcp.password" = "qtestpassword",
+    "hive.sql.table" = "book"
+)
+POSTHOOK: type: CREATETABLE
+POSTHOOK: Output: database:default
+POSTHOOK: Output: default@book
+PREHOOK: query: CREATE EXTERNAL TABLE author
+( id int,
+fname string,
+lname string)
+STORED BY
+'org.apache.hive.storage.jdbc.JdbcStorageHandler'
+TBLPROPERTIES (                                    
+    "hive.sql.database.type" = "POSTGRES",
+    "hive.sql.jdbc.driver" = "org.postgresql.Driver",
+    "hive.sql.jdbc.url" = "jdbc:postgresql://localhost:5432/qtestDB",
+    "hive.sql.dbcp.username" = "qtestuser",
+    "hive.sql.dbcp.password" = "qtestpassword",
+    "hive.sql.table" = "author"
+)
+PREHOOK: type: CREATETABLE
+PREHOOK: Output: database:default
+PREHOOK: Output: default@author
+POSTHOOK: query: CREATE EXTERNAL TABLE author
+( id int,
+fname string,
+lname string)
+STORED BY
+'org.apache.hive.storage.jdbc.JdbcStorageHandler'
+TBLPROPERTIES (                                    
+    "hive.sql.database.type" = "POSTGRES",
+    "hive.sql.jdbc.driver" = "org.postgresql.Driver",
+    "hive.sql.jdbc.url" = "jdbc:postgresql://localhost:5432/qtestDB",
+    "hive.sql.dbcp.username" = "qtestuser",
+    "hive.sql.dbcp.password" = "qtestpassword",
+    "hive.sql.table" = "author"
+)
+POSTHOOK: type: CREATETABLE
+POSTHOOK: Output: database:default
+POSTHOOK: Output: default@author
+PREHOOK: query: explain cbo select id from book where substring(title, 0, 4) = 
'Les'
+PREHOOK: type: QUERY
+PREHOOK: Input: default@book
+#### A masked pattern was here ####
+POSTHOOK: query: explain cbo select id from book where substring(title, 0, 4) 
= 'Les'
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@book
+#### A masked pattern was here ####
+CBO PLAN:
+HiveJdbcConverter(convention=[JDBC.POSTGRES])
+  JdbcProject(id=[$0])
+    JdbcFilter(condition=[=(SUBSTRING($1, 0, 4), _UTF-16LE'Les')])
+      JdbcProject(id=[$0], title=[$1])
+        JdbcHiveTableScan(table=[[default, book]], table:alias=[book])
+
+PREHOOK: query: explain select id from book where substring(title, 0, 4) = 
'Les'
+PREHOOK: type: QUERY
+PREHOOK: Input: default@book
+#### A masked pattern was here ####
+POSTHOOK: query: explain select id from book where substring(title, 0, 4) = 
'Les'
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@book
+#### A masked pattern was here ####
+STAGE DEPENDENCIES:
+  Stage-0 is a root stage
+
+STAGE PLANS:
+  Stage: Stage-0
+    Fetch Operator
+      limit: -1
+      Processor Tree:
+        TableScan
+          alias: book
+          properties:
+            hive.sql.query SELECT "id"
+FROM (SELECT "id", "title"
+FROM "book") AS "t"
+WHERE SUBSTRING("title" FROM 0 FOR 4) = 'Les'
+            hive.sql.query.fieldNames id
+            hive.sql.query.fieldTypes int
+            hive.sql.query.split true
+          Select Operator
+            expressions: id (type: int)
+            outputColumnNames: _col0
+            ListSink
+
+PREHOOK: query: select id from book where substring(title, 0, 4) = 'Les'
+PREHOOK: type: QUERY
+PREHOOK: Input: default@book
+#### A masked pattern was here ####
+POSTHOOK: query: select id from book where substring(title, 0, 4) = 'Les'
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@book
+#### A masked pattern was here ####
+1
+PREHOOK: query: explain cbo select id from book where ucase(title) = 'LES 
MISERABLES'
+PREHOOK: type: QUERY
+PREHOOK: Input: default@book
+#### A masked pattern was here ####
+POSTHOOK: query: explain cbo select id from book where ucase(title) = 'LES 
MISERABLES'
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@book
+#### A masked pattern was here ####
+CBO PLAN:
+HiveProject(id=[$0])
+  HiveFilter(condition=[=(CAST(ucase($1)):VARCHAR(2147483647) CHARACTER SET 
"UTF-16LE", _UTF-16LE'LES MISERABLES')])
+    HiveProject(id=[$0], title=[$1])
+      HiveJdbcConverter(convention=[JDBC.POSTGRES])
+        JdbcProject(id=[$0], title=[$1])
+          JdbcHiveTableScan(table=[[default, book]], table:alias=[book])
+
+PREHOOK: query: explain select id from book where ucase(title) = 'LES 
MISERABLES'
+PREHOOK: type: QUERY
+PREHOOK: Input: default@book
+#### A masked pattern was here ####
+POSTHOOK: query: explain select id from book where ucase(title) = 'LES 
MISERABLES'
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@book
+#### A masked pattern was here ####
+STAGE DEPENDENCIES:
+  Stage-0 is a root stage
+
+STAGE PLANS:
+  Stage: Stage-0
+    Fetch Operator
+      limit: -1
+      Processor Tree:
+        TableScan
+          alias: book
+          properties:
+            hive.sql.query SELECT "id", "title"
+FROM "book"
+            hive.sql.query.fieldNames id,title
+            hive.sql.query.fieldTypes int,varchar(20)
+            hive.sql.query.split true
+          Select Operator
+            expressions: id (type: int), title (type: varchar(20))
+            outputColumnNames: _col0, _col1
+            Filter Operator
+              predicate: (CAST( upper(_col1) AS STRING) = 'LES MISERABLES') 
(type: boolean)
+              Select Operator
+                expressions: _col0 (type: int)
+                outputColumnNames: _col0
+                ListSink
+
+PREHOOK: query: select id from book where ucase(title) = 'LES MISERABLES'
+PREHOOK: type: QUERY
+PREHOOK: Input: default@book
+#### A masked pattern was here ####
+POSTHOOK: query: select id from book where ucase(title) = 'LES MISERABLES'
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@book
+#### A masked pattern was here ####
+1
+PREHOOK: query: explain cbo select book.title, author.fname from book join 
author on book.author = author.id
+PREHOOK: type: QUERY
+PREHOOK: Input: default@author
+PREHOOK: Input: default@book
+#### A masked pattern was here ####
+POSTHOOK: query: explain cbo select book.title, author.fname from book join 
author on book.author = author.id
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@author
+POSTHOOK: Input: default@book
+#### A masked pattern was here ####
+CBO PLAN:
+HiveJdbcConverter(convention=[JDBC.POSTGRES])
+  JdbcProject(title=[$0], fname=[$3])
+    JdbcJoin(condition=[=($1, $2)], joinType=[inner])
+      JdbcProject(title=[$0], author=[$1])
+        JdbcFilter(condition=[IS NOT NULL($1)])
+          JdbcProject(title=[$1], author=[$2])
+            JdbcHiveTableScan(table=[[default, book]], table:alias=[book])
+      JdbcProject(id=[$0], fname=[$1])
+        JdbcFilter(condition=[IS NOT NULL($0)])
+          JdbcProject(id=[$0], fname=[$1])
+            JdbcHiveTableScan(table=[[default, author]], table:alias=[author])
+
+PREHOOK: query: explain select book.title, author.fname from book join author 
on book.author = author.id
+PREHOOK: type: QUERY
+PREHOOK: Input: default@author
+PREHOOK: Input: default@book
+#### A masked pattern was here ####
+POSTHOOK: query: explain select book.title, author.fname from book join author 
on book.author = author.id
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@author
+POSTHOOK: Input: default@book
+#### A masked pattern was here ####
+STAGE DEPENDENCIES:
+  Stage-0 is a root stage
+
+STAGE PLANS:
+  Stage: Stage-0
+    Fetch Operator
+      limit: -1
+      Processor Tree:
+        TableScan
+          alias: book
+          properties:
+            hive.sql.query SELECT "t1"."title", "t4"."fname"
+FROM (SELECT "title", "author"
+FROM (SELECT "title", "author"
+FROM "book") AS "t"
+WHERE "author" IS NOT NULL) AS "t1"
+INNER JOIN (SELECT "id", "fname"
+FROM (SELECT "id", "fname"
+FROM "author") AS "t2"
+WHERE "id" IS NOT NULL) AS "t4" ON "t1"."author" = "t4"."id"
+            hive.sql.query.fieldNames title,fname
+            hive.sql.query.fieldTypes varchar(20),string
+            hive.sql.query.split false
+          Select Operator
+            expressions: title (type: varchar(20)), fname (type: string)
+            outputColumnNames: _col0, _col1
+            ListSink
+
+PREHOOK: query: select book.title, author.fname from book join author on 
book.author = author.id
+PREHOOK: type: QUERY
+PREHOOK: Input: default@author
+PREHOOK: Input: default@book
+#### A masked pattern was here ####
+POSTHOOK: query: select book.title, author.fname from book join author on 
book.author = author.id
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@author
+POSTHOOK: Input: default@book
+#### A masked pattern was here ####
+Les Miserables Victor
+The Count Of Monte C   Alexandre
+PREHOOK: query: explain cbo select book.title, author.fname from book join 
author
+where book.author = author.id
+and ucase(book.title) = 'LES MISERABLES'
+and substring(author.lname, 0, 4) = 'Hug'
+PREHOOK: type: QUERY
+PREHOOK: Input: default@author
+PREHOOK: Input: default@book
+#### A masked pattern was here ####
+POSTHOOK: query: explain cbo select book.title, author.fname from book join 
author
+where book.author = author.id
+and ucase(book.title) = 'LES MISERABLES'
+and substring(author.lname, 0, 4) = 'Hug'
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@author
+POSTHOOK: Input: default@book
+#### A masked pattern was here ####
+CBO PLAN:
+HiveProject(title=[$0], fname=[$3])
+  HiveJoin(condition=[=($1, $2)], joinType=[inner], algorithm=[none], 
cost=[not available])
+    HiveProject(title=[$0], author=[$1])
+      HiveFilter(condition=[=(CAST(ucase($0)):VARCHAR(2147483647) CHARACTER 
SET "UTF-16LE", _UTF-16LE'LES MISERABLES')])
+        HiveProject(title=[$0], author=[$1])
+          HiveJdbcConverter(convention=[JDBC.POSTGRES])
+            JdbcFilter(condition=[IS NOT NULL($1)])
+              JdbcProject(title=[$1], author=[$2])
+                JdbcHiveTableScan(table=[[default, book]], table:alias=[book])
+    HiveProject(id=[$0], fname=[$1])
+      HiveJdbcConverter(convention=[JDBC.POSTGRES])
+        JdbcProject(id=[$0], fname=[$1])
+          JdbcFilter(condition=[AND(=(SUBSTRING($2, 0, 4), _UTF-16LE'Hug'), IS 
NOT NULL($0))])
+            JdbcHiveTableScan(table=[[default, author]], table:alias=[author])
+
+PREHOOK: query: explain select book.title, author.fname from book join author
+where book.author = author.id
+and ucase(book.title) = 'LES MISERABLES'
+and substring(author.lname, 0, 4) = 'Hug'
+PREHOOK: type: QUERY
+PREHOOK: Input: default@author
+PREHOOK: Input: default@book
+#### A masked pattern was here ####
+POSTHOOK: query: explain select book.title, author.fname from book join author
+where book.author = author.id
+and ucase(book.title) = 'LES MISERABLES'
+and substring(author.lname, 0, 4) = 'Hug'
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@author
+POSTHOOK: Input: default@book
+#### A masked pattern was here ####
+STAGE DEPENDENCIES:
+  Stage-1 is a root stage
+  Stage-0 depends on stages: Stage-1
+
+STAGE PLANS:
+  Stage: Stage-1
+    Tez
+#### A masked pattern was here ####
+      Edges:
+        Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 3 (SIMPLE_EDGE)
+#### A masked pattern was here ####
+      Vertices:
+        Map 1 
+            Map Operator Tree:
+                TableScan
+                  alias: book
+                  properties:
+                    hive.sql.query SELECT "title", "author"
+FROM (SELECT "title", "author"
+FROM "book") AS "t"
+WHERE "author" IS NOT NULL
+                    hive.sql.query.fieldNames title,author
+                    hive.sql.query.fieldTypes varchar(20),int
+                    hive.sql.query.split true
+                  Statistics: Num rows: 1 Data size: 108 Basic stats: COMPLETE 
Column stats: NONE
+                  Select Operator
+                    expressions: title (type: varchar(20)), author (type: int)
+                    outputColumnNames: _col0, _col1
+                    Statistics: Num rows: 1 Data size: 108 Basic stats: 
COMPLETE Column stats: NONE
+                    Filter Operator
+                      predicate: (CAST( upper(_col0) AS STRING) = 'LES 
MISERABLES') (type: boolean)
+                      Statistics: Num rows: 1 Data size: 108 Basic stats: 
COMPLETE Column stats: NONE
+                      Reduce Output Operator
+                        key expressions: _col1 (type: int)
+                        null sort order: z
+                        sort order: +
+                        Map-reduce partition columns: _col1 (type: int)
+                        Statistics: Num rows: 1 Data size: 108 Basic stats: 
COMPLETE Column stats: NONE
+                        value expressions: _col0 (type: varchar(20))
+            Execution mode: vectorized, llap
+            LLAP IO: no inputs
+        Map 3 
+            Map Operator Tree:
+                TableScan
+                  alias: author
+                  properties:
+                    hive.sql.query SELECT "id", "fname"
+FROM "author"
+WHERE SUBSTRING("lname" FROM 0 FOR 4) = 'Hug' AND "id" IS NOT NULL
+                    hive.sql.query.fieldNames id,fname
+                    hive.sql.query.fieldTypes int,string
+                    hive.sql.query.split true
+                  Statistics: Num rows: 1 Data size: 188 Basic stats: COMPLETE 
Column stats: NONE
+                  Select Operator
+                    expressions: id (type: int), fname (type: string)
+                    outputColumnNames: _col0, _col1
+                    Statistics: Num rows: 1 Data size: 188 Basic stats: 
COMPLETE Column stats: NONE
+                    Reduce Output Operator
+                      key expressions: _col0 (type: int)
+                      null sort order: z
+                      sort order: +
+                      Map-reduce partition columns: _col0 (type: int)
+                      Statistics: Num rows: 1 Data size: 188 Basic stats: 
COMPLETE Column stats: NONE
+                      value expressions: _col1 (type: string)
+            Execution mode: vectorized, llap
+            LLAP IO: no inputs
+        Reducer 2 
+            Execution mode: llap
+            Reduce Operator Tree:
+              Merge Join Operator
+                condition map:
+                     Inner Join 0 to 1
+                keys:
+                  0 _col1 (type: int)
+                  1 _col0 (type: int)
+                outputColumnNames: _col0, _col3
+                Statistics: Num rows: 1 Data size: 118 Basic stats: COMPLETE 
Column stats: NONE
+                Select Operator
+                  expressions: _col0 (type: varchar(20)), _col3 (type: string)
+                  outputColumnNames: _col0, _col1
+                  Statistics: Num rows: 1 Data size: 118 Basic stats: COMPLETE 
Column stats: NONE
+                  File Output Operator
+                    compressed: false
+                    Statistics: Num rows: 1 Data size: 118 Basic stats: 
COMPLETE 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-0
+    Fetch Operator
+      limit: -1
+      Processor Tree:
+        ListSink
+
+PREHOOK: query: select book.title, author.fname from book join author
+where book.author = author.id
+and ucase(book.title) = 'LES MISERABLES'
+and substring(author.lname, 0, 4) = 'Hug'
+PREHOOK: type: QUERY
+PREHOOK: Input: default@author
+PREHOOK: Input: default@book
+#### A masked pattern was here ####
+POSTHOOK: query: select book.title, author.fname from book join author
+where book.author = author.id
+and ucase(book.title) = 'LES MISERABLES'
+and substring(author.lname, 0, 4) = 'Hug'
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@author
+POSTHOOK: Input: default@book
+#### A masked pattern was here ####
+Les Miserables Victor
+PREHOOK: query: explain cbo 
+select author.fname, count(book.title) as books
+from book join author
+where book.author = author.id
+group by author.fname
+PREHOOK: type: QUERY
+PREHOOK: Input: default@author
+PREHOOK: Input: default@book
+#### A masked pattern was here ####
+POSTHOOK: query: explain cbo 
+select author.fname, count(book.title) as books
+from book join author
+where book.author = author.id
+group by author.fname
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@author
+POSTHOOK: Input: default@book
+#### A masked pattern was here ####
+CBO PLAN:
+HiveJdbcConverter(convention=[JDBC.POSTGRES])
+  JdbcAggregate(group=[{3}], agg#0=[count($0)])
+    JdbcJoin(condition=[=($1, $2)], joinType=[inner])
+      JdbcProject(title=[$0], author=[$1])
+        JdbcFilter(condition=[IS NOT NULL($1)])
+          JdbcProject(title=[$1], author=[$2])
+            JdbcHiveTableScan(table=[[default, book]], table:alias=[book])
+      JdbcProject(id=[$0], fname=[$1])
+        JdbcFilter(condition=[IS NOT NULL($0)])
+          JdbcProject(id=[$0], fname=[$1])
+            JdbcHiveTableScan(table=[[default, author]], table:alias=[author])
+
+PREHOOK: query: explain 
+select author.fname, count(book.title) as books
+from book join author
+where book.author = author.id
+group by author.fname
+PREHOOK: type: QUERY
+PREHOOK: Input: default@author
+PREHOOK: Input: default@book
+#### A masked pattern was here ####
+POSTHOOK: query: explain 
+select author.fname, count(book.title) as books
+from book join author
+where book.author = author.id
+group by author.fname
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@author
+POSTHOOK: Input: default@book
+#### A masked pattern was here ####
+STAGE DEPENDENCIES:
+  Stage-0 is a root stage
+
+STAGE PLANS:
+  Stage: Stage-0
+    Fetch Operator
+      limit: -1
+      Processor Tree:
+        TableScan
+          alias: book
+          properties:
+            hive.sql.query SELECT "t4"."fname", COUNT("t1"."title") AS "$f1"
+FROM (SELECT "title", "author"
+FROM (SELECT "title", "author"
+FROM "book") AS "t"
+WHERE "author" IS NOT NULL) AS "t1"
+INNER JOIN (SELECT "id", "fname"
+FROM (SELECT "id", "fname"
+FROM "author") AS "t2"
+WHERE "id" IS NOT NULL) AS "t4" ON "t1"."author" = "t4"."id"
+GROUP BY "t4"."fname"
+            hive.sql.query.fieldNames fname,$f1
+            hive.sql.query.fieldTypes string,bigint
+            hive.sql.query.split false
+          Select Operator
+            expressions: fname (type: string), $f1 (type: bigint)
+            outputColumnNames: _col0, _col1
+            ListSink
+
+PREHOOK: query: select author.fname, count(book.title) as books
+from book join author
+where book.author = author.id
+group by author.fname
+PREHOOK: type: QUERY
+PREHOOK: Input: default@author
+PREHOOK: Input: default@book
+#### A masked pattern was here ####
+POSTHOOK: query: select author.fname, count(book.title) as books
+from book join author
+where book.author = author.id
+group by author.fname
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@author
+POSTHOOK: Input: default@book
+#### A masked pattern was here ####
+Victor 1
+Alexandre      1
+PREHOOK: query: explain cbo 
+select ucase(author.fname), count(book.title) as books
+from book join author
+where book.author = author.id
+group by author.fname
+order by ucase(author.fname)
+limit 5
+PREHOOK: type: QUERY
+PREHOOK: Input: default@author
+PREHOOK: Input: default@book
+#### A masked pattern was here ####
+POSTHOOK: query: explain cbo 
+select ucase(author.fname), count(book.title) as books
+from book join author
+where book.author = author.id
+group by author.fname
+order by ucase(author.fname)
+limit 5
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@author
+POSTHOOK: Input: default@book
+#### A masked pattern was here ####
+CBO PLAN:
+HiveProject(_o__c0=[$0], books=[$1])
+  HiveSortLimit(sort0=[$2], dir0=[ASC], fetch=[5])
+    HiveProject(_o__c0=[ucase($0)], books=[$1], _o__col4=[ucase($0)])
+      HiveProject(fname=[$0], $f1=[$1])
+        HiveJdbcConverter(convention=[JDBC.POSTGRES])
+          JdbcAggregate(group=[{3}], agg#0=[count($0)])
+            JdbcJoin(condition=[=($1, $2)], joinType=[inner])
+              JdbcProject(title=[$0], author=[$1])
+                JdbcFilter(condition=[IS NOT NULL($1)])
+                  JdbcProject(title=[$1], author=[$2])
+                    JdbcHiveTableScan(table=[[default, book]], 
table:alias=[book])
+              JdbcProject(id=[$0], fname=[$1])
+                JdbcFilter(condition=[IS NOT NULL($0)])
+                  JdbcProject(id=[$0], fname=[$1])
+                    JdbcHiveTableScan(table=[[default, author]], 
table:alias=[author])
+
+PREHOOK: query: explain 
+select ucase(author.fname), count(book.title) as books
+from book join author
+where book.author = author.id
+group by author.fname
+order by ucase(author.fname)
+limit 5
+PREHOOK: type: QUERY
+PREHOOK: Input: default@author
+PREHOOK: Input: default@book
+#### A masked pattern was here ####
+POSTHOOK: query: explain 
+select ucase(author.fname), count(book.title) as books
+from book join author
+where book.author = author.id
+group by author.fname
+order by ucase(author.fname)
+limit 5
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@author
+POSTHOOK: Input: default@book
+#### A masked pattern was here ####
+STAGE DEPENDENCIES:
+  Stage-1 is a root stage
+  Stage-0 depends on stages: Stage-1
+
+STAGE PLANS:
+  Stage: Stage-1
+    Tez
+#### A masked pattern was here ####
+      Edges:
+        Reducer 2 <- Map 1 (SIMPLE_EDGE)
+#### A masked pattern was here ####
+      Vertices:
+        Map 1 
+            Map Operator Tree:
+                TableScan
+                  alias: book
+                  properties:
+                    hive.sql.query SELECT "t4"."fname", COUNT("t1"."title") AS 
"$f1"
+FROM (SELECT "title", "author"
+FROM (SELECT "title", "author"
+FROM "book") AS "t"
+WHERE "author" IS NOT NULL) AS "t1"
+INNER JOIN (SELECT "id", "fname"
+FROM (SELECT "id", "fname"
+FROM "author") AS "t2"
+WHERE "id" IS NOT NULL) AS "t4" ON "t1"."author" = "t4"."id"
+GROUP BY "t4"."fname"
+                    hive.sql.query.fieldNames fname,$f1
+                    hive.sql.query.fieldTypes string,bigint
+                    hive.sql.query.split false
+                  Statistics: Num rows: 1 Data size: 192 Basic stats: COMPLETE 
Column stats: NONE
+                  Top N Key Operator
+                    sort order: +
+                    keys: upper(fname) (type: string)
+                    null sort order: z
+                    Statistics: Num rows: 1 Data size: 192 Basic stats: 
COMPLETE Column stats: NONE
+                    top n: 5
+                    Select Operator
+                      expressions: $f1 (type: bigint), upper(fname) (type: 
string)
+                      outputColumnNames: _col1, _col2
+                      Statistics: Num rows: 1 Data size: 192 Basic stats: 
COMPLETE Column stats: NONE
+                      Reduce Output Operator
+                        key expressions: _col2 (type: string)
+                        null sort order: z
+                        sort order: +
+                        Statistics: Num rows: 1 Data size: 192 Basic stats: 
COMPLETE Column stats: NONE
+                        value expressions: _col1 (type: bigint)
+            Execution mode: vectorized, llap
+            LLAP IO: no inputs
+        Reducer 2 
+            Execution mode: vectorized, llap
+            Reduce Operator Tree:
+              Select Operator
+                expressions: KEY.reducesinkkey0 (type: string), VALUE._col0 
(type: bigint)
+                outputColumnNames: _col0, _col1
+                Statistics: Num rows: 1 Data size: 192 Basic stats: COMPLETE 
Column stats: NONE
+                Limit
+                  Number of rows: 5
+                  Statistics: Num rows: 1 Data size: 192 Basic stats: COMPLETE 
Column stats: NONE
+                  File Output Operator
+                    compressed: false
+                    Statistics: Num rows: 1 Data size: 192 Basic stats: 
COMPLETE 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-0
+    Fetch Operator
+      limit: -1
+      Processor Tree:
+        ListSink
+
+PREHOOK: query: select ucase(author.fname), count(book.title) as books
+from book join author
+where book.author = author.id
+group by author.fname
+order by ucase(author.fname)
+limit 5
+PREHOOK: type: QUERY
+PREHOOK: Input: default@author
+PREHOOK: Input: default@book
+#### A masked pattern was here ####
+POSTHOOK: query: select ucase(author.fname), count(book.title) as books
+from book join author
+where book.author = author.id
+group by author.fname
+order by ucase(author.fname)
+limit 5
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@author
+POSTHOOK: Input: default@book
+#### A masked pattern was here ####
+ALEXANDRE      1
+VICTOR 1

Reply via email to