You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@quickstep.apache.org by zu...@apache.org on 2016/05/16 20:55:56 UTC

[19/46] incubator-quickstep git commit: Adds support for multiple SharedSubplanReference. (#199)

Adds support for multiple SharedSubplanReference. (#199)

Project: http://git-wip-us.apache.org/repos/asf/incubator-quickstep/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-quickstep/commit/c5460f40
Tree: http://git-wip-us.apache.org/repos/asf/incubator-quickstep/tree/c5460f40
Diff: http://git-wip-us.apache.org/repos/asf/incubator-quickstep/diff/c5460f40

Branch: refs/heads/master
Commit: c5460f40e82b9e2c81e14380f17609a6e7230eb4
Parents: 07ca1e7
Author: Jianqiao Zhu <ji...@cs.wisc.edu>
Authored: Thu Apr 28 16:09:32 2016 -0500
Committer: Jignesh Patel <pa...@users.noreply.github.com>
Committed: Thu Apr 28 16:09:32 2016 -0500

----------------------------------------------------------------------
 query_optimizer/ExecutionGenerator.cpp          |  13 ++
 query_optimizer/ExecutionGenerator.hpp          |  11 +-
 query_optimizer/cost_model/CMakeLists.txt       |   1 +
 query_optimizer/cost_model/SimpleCostModel.cpp  |   9 +
 query_optimizer/cost_model/SimpleCostModel.hpp  |   8 +-
 .../logical/SharedSubplanReference.cpp          |   7 +-
 .../logical/SharedSubplanReference.hpp          |  34 +++-
 .../physical/SharedSubplanReference.cpp         |   5 +
 .../physical/SharedSubplanReference.hpp         |  34 +++-
 query_optimizer/resolver/Resolver.cpp           |  25 ++-
 query_optimizer/strategy/OneToOne.cpp           |   1 +
 .../tests/execution_generator/Select.test       |  25 +++
 .../tests/logical_generator/Select.test         | 106 ++++++++++-
 .../tests/physical_generator/Select.test        | 165 ++++++++++++++++-
 query_optimizer/tests/resolver/Select.test      | 181 +++++++++++++++----
 15 files changed, 542 insertions(+), 83 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/c5460f40/query_optimizer/ExecutionGenerator.cpp
----------------------------------------------------------------------
diff --git a/query_optimizer/ExecutionGenerator.cpp b/query_optimizer/ExecutionGenerator.cpp
index abcdd6c..c34f084 100644
--- a/query_optimizer/ExecutionGenerator.cpp
+++ b/query_optimizer/ExecutionGenerator.cpp
@@ -154,6 +154,9 @@ void ExecutionGenerator::generatePlan(const P::PhysicalPtr &physical_plan) {
   CHECK(P::SomeTopLevelPlan::MatchesWithConditionalCast(physical_plan, &top_level_physical_plan_))
       << "The physical plan must be rooted by a TopLevelPlan";
 
+  cost_model_.reset(
+      new cost::SimpleCostModel(top_level_physical_plan_->shared_subplans()));
+
   const CatalogRelation *result_relation = nullptr;
 
   try {
@@ -550,6 +553,16 @@ void ExecutionGenerator::convertSharedSubplanReference(const physical::SharedSub
           top_level_physical_plan_->shared_subplan_at(physical_plan->subplan_id()));
   if (found_it != physical_to_output_relation_map_.end()) {
     physical_to_output_relation_map_.emplace(physical_plan, found_it->second);
+
+    // Propagate the (ExprId -> CatalogAttribute) mapping.
+    const std::vector<E::AttributeReferencePtr> &referenced_attributes =
+        physical_plan->referenced_attributes();
+    const std::vector<E::AttributeReferencePtr> &output_attributes =
+        physical_plan->output_attributes();
+    for (std::size_t i = 0; i < referenced_attributes.size(); ++i) {
+      attribute_substitution_map_[output_attributes[i]->id()] =
+          attribute_substitution_map_[referenced_attributes[i]->id()];
+    }
   }
 }
 

http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/c5460f40/query_optimizer/ExecutionGenerator.hpp
----------------------------------------------------------------------
diff --git a/query_optimizer/ExecutionGenerator.hpp b/query_optimizer/ExecutionGenerator.hpp
index df47b31..7c563d4 100644
--- a/query_optimizer/ExecutionGenerator.hpp
+++ b/query_optimizer/ExecutionGenerator.hpp
@@ -1,6 +1,8 @@
 /**
  *   Copyright 2011-2015 Quickstep Technologies LLC.
  *   Copyright 2015-2016 Pivotal Software, Inc.
+ *   Copyright 2016, Quickstep Research Group, Computer Sciences Department,
+ *     University of Wisconsin\u2014Madison.
  *
  *   Licensed under the Apache License, Version 2.0 (the "License");
  *   you may not use this file except in compliance with the License.
@@ -104,8 +106,6 @@ class ExecutionGenerator {
 #ifdef QUICKSTEP_DISTRIBUTED
     catalog_database_cache_proto_ = DCHECK_NOTNULL(query_handle->getCatalogDatabaseCacheProtoMutable());
 #endif
-
-    setupCostModel();
   }
 
   /**
@@ -163,13 +163,6 @@ class ExecutionGenerator {
   void generatePlanInternal(const physical::PhysicalPtr &physical_plan);
 
   /**
-   * @brief Sets up the cost model.
-   */
-  void setupCostModel() {
-    cost_model_.reset(new cost::SimpleCostModel());
-  }
-
-  /**
    * @brief Finds the CatalogRelationInfo from <physical_to_output_relation_map_>
    *        by the physical node \p physical. Returns NULL if not found.
    *

http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/c5460f40/query_optimizer/cost_model/CMakeLists.txt
----------------------------------------------------------------------
diff --git a/query_optimizer/cost_model/CMakeLists.txt b/query_optimizer/cost_model/CMakeLists.txt
index e561fc8..6697d52 100644
--- a/query_optimizer/cost_model/CMakeLists.txt
+++ b/query_optimizer/cost_model/CMakeLists.txt
@@ -31,6 +31,7 @@ target_link_libraries(quickstep_queryoptimizer_costmodel_SimpleCostModel
                       quickstep_queryoptimizer_physical_Physical
                       quickstep_queryoptimizer_physical_PhysicalType
                       quickstep_queryoptimizer_physical_Selection
+                      quickstep_queryoptimizer_physical_SharedSubplanReference
                       quickstep_queryoptimizer_physical_TableGenerator
                       quickstep_queryoptimizer_physical_TableReference
                       quickstep_queryoptimizer_physical_TopLevelPlan

http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/c5460f40/query_optimizer/cost_model/SimpleCostModel.cpp
----------------------------------------------------------------------
diff --git a/query_optimizer/cost_model/SimpleCostModel.cpp b/query_optimizer/cost_model/SimpleCostModel.cpp
index 291fb9d..48f76fa 100644
--- a/query_optimizer/cost_model/SimpleCostModel.cpp
+++ b/query_optimizer/cost_model/SimpleCostModel.cpp
@@ -1,6 +1,8 @@
 /**
  *   Copyright 2011-2015 Quickstep Technologies LLC.
  *   Copyright 2015 Pivotal Software, Inc.
+ *   Copyright 2016, Quickstep Research Group, Computer Sciences Department,
+ *     University of Wisconsin\u2014Madison.
  *
  *   Licensed under the Apache License, Version 2.0 (the "License");
  *   you may not use this file except in compliance with the License.
@@ -27,6 +29,7 @@
 #include "query_optimizer/physical/Physical.hpp"
 #include "query_optimizer/physical/PhysicalType.hpp"
 #include "query_optimizer/physical/Selection.hpp"
+#include "query_optimizer/physical/SharedSubplanReference.hpp"
 #include "query_optimizer/physical/TableGenerator.hpp"
 #include "query_optimizer/physical/TableReference.hpp"
 #include "query_optimizer/physical/TopLevelPlan.hpp"
@@ -63,6 +66,12 @@ std::size_t SimpleCostModel::estimateCardinality(
     case P::PhysicalType::kAggregate:
       return estimateCardinalityForAggregate(
           std::static_pointer_cast<const P::Aggregate>(physical_plan));
+    case P::PhysicalType::kSharedSubplanReference: {
+      const P::SharedSubplanReferencePtr shared_subplan_reference =
+          std::static_pointer_cast<const P::SharedSubplanReference>(physical_plan);
+      return estimateCardinality(
+          shared_subplans_[shared_subplan_reference->subplan_id()]);
+    }
     default:
       LOG(FATAL) << "Unsupported physical plan:" << physical_plan->toString();
   }

http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/c5460f40/query_optimizer/cost_model/SimpleCostModel.hpp
----------------------------------------------------------------------
diff --git a/query_optimizer/cost_model/SimpleCostModel.hpp b/query_optimizer/cost_model/SimpleCostModel.hpp
index e53887d..9862198 100644
--- a/query_optimizer/cost_model/SimpleCostModel.hpp
+++ b/query_optimizer/cost_model/SimpleCostModel.hpp
@@ -1,6 +1,8 @@
 /**
  *   Copyright 2011-2015 Quickstep Technologies LLC.
  *   Copyright 2015 Pivotal Software, Inc.
+ *   Copyright 2016, Quickstep Research Group, Computer Sciences Department,
+ *     University of Wisconsin\u2014Madison.
  *
  *   Licensed under the Apache License, Version 2.0 (the "License");
  *   you may not use this file except in compliance with the License.
@@ -19,6 +21,7 @@
 #define QUERY_OPTIMIZER_COST_MODEL_SIMPLE_COST_MODEL_HPP_
 
 #include <cstddef>
+#include <vector>
 
 #include "query_optimizer/cost_model/CostModel.hpp"
 #include "query_optimizer/physical/Aggregate.hpp"
@@ -47,7 +50,8 @@ class SimpleCostModel : public CostModel {
   /**
    * @brief Constructor.
    */
-  SimpleCostModel() {}
+  explicit SimpleCostModel(const std::vector<physical::PhysicalPtr> &shared_subplans)
+      : shared_subplans_(shared_subplans) {}
 
   std::size_t estimateCardinality(
       const physical::PhysicalPtr &physical_plan) override;
@@ -84,6 +88,8 @@ class SimpleCostModel : public CostModel {
   std::size_t estimateCardinalityForAggregate(
       const physical::AggregatePtr &physical_plan);
 
+  const std::vector<physical::PhysicalPtr> &shared_subplans_;
+
   DISALLOW_COPY_AND_ASSIGN(SimpleCostModel);
 };
 

http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/c5460f40/query_optimizer/logical/SharedSubplanReference.cpp
----------------------------------------------------------------------
diff --git a/query_optimizer/logical/SharedSubplanReference.cpp b/query_optimizer/logical/SharedSubplanReference.cpp
index 8a5bec9..b5d3143 100644
--- a/query_optimizer/logical/SharedSubplanReference.cpp
+++ b/query_optimizer/logical/SharedSubplanReference.cpp
@@ -1,6 +1,8 @@
 /**
  *   Copyright 2011-2015 Quickstep Technologies LLC.
  *   Copyright 2015 Pivotal Software, Inc.
+ *   Copyright 2016, Quickstep Research Group, Computer Sciences Department,
+ *     University of Wisconsin\u2014Madison.
  *
  *   Licensed under the Apache License, Version 2.0 (the "License");
  *   you may not use this file except in compliance with the License.
@@ -41,13 +43,16 @@ void SharedSubplanReference::getFieldStringItems(
   inline_field_names->push_back("subplan_id");
   inline_field_values->push_back(std::to_string(subplan_id_));
 
+  container_child_field_names->push_back("referenced_attributes");
+  container_child_fields->push_back(CastSharedPtrVector<OptimizerTreeBase>(referenced_attributes_));
+
   container_child_field_names->push_back("output_attributes");
   container_child_fields->push_back(CastSharedPtrVector<OptimizerTreeBase>(output_attributes_));
 }
 
 LogicalPtr SharedSubplanReference::copyWithNewChildren(const std::vector<LogicalPtr> &new_children) const {
   DCHECK(new_children.empty());
-  return Create(subplan_id_, output_attributes_);
+  return Create(subplan_id_, referenced_attributes_, output_attributes_);
 }
 
 }  // namespace logical

http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/c5460f40/query_optimizer/logical/SharedSubplanReference.hpp
----------------------------------------------------------------------
diff --git a/query_optimizer/logical/SharedSubplanReference.hpp b/query_optimizer/logical/SharedSubplanReference.hpp
index 55035d1..8f0e37b 100644
--- a/query_optimizer/logical/SharedSubplanReference.hpp
+++ b/query_optimizer/logical/SharedSubplanReference.hpp
@@ -1,6 +1,8 @@
 /**
  *   Copyright 2011-2015 Quickstep Technologies LLC.
  *   Copyright 2015 Pivotal Software, Inc.
+ *   Copyright 2016, Quickstep Research Group, Computer Sciences Department,
+ *     University of Wisconsin\u2014Madison.
  *
  *   Licensed under the Apache License, Version 2.0 (the "License");
  *   you may not use this file except in compliance with the License.
@@ -28,6 +30,8 @@
 #include "query_optimizer/logical/LogicalType.hpp"
 #include "utility/Macros.hpp"
 
+#include "glog/logging.h"
+
 namespace quickstep {
 namespace optimizer {
 namespace logical {
@@ -60,7 +64,14 @@ class SharedSubplanReference : public Logical {
   }
 
   /**
-   * @return The output attributes of the shared subplan.
+   * @return The attributes from the referenced shared subplan.
+   */
+  const std::vector<expressions::AttributeReferencePtr>& referenced_attributes() const {
+    return referenced_attributes_;
+  }
+
+  /**
+   * @return The output attributes of this shared subplan reference.
    */
   const std::vector<expressions::AttributeReferencePtr>& output_attributes() const {
     return output_attributes_;
@@ -81,7 +92,7 @@ class SharedSubplanReference : public Logical {
   LogicalPtr copyWithNewChildren(const std::vector<LogicalPtr> &new_children) const override;
 
   std::vector<expressions::AttributeReferencePtr> getReferencedAttributes() const override {
-    return output_attributes_;
+    return referenced_attributes_;
   }
 
   /**
@@ -89,21 +100,30 @@ class SharedSubplanReference : public Logical {
    *
    * @param subplan_id The ID of the shared subplan, which is the index of the referenced
    *        shared subplan in <shared_subplans_> of the TopLevelPlan.
-   * @param output_attributes The output attributes of the shared subplan.
+   * @param referenced_attributes The attributes from the referenced shared subplan.
+   * @param output_attributes The output attributes of this shared subplan reference.
    * @return An immutable SharedSubplanReference.
    */
-  static SharedSubplanReferencePtr Create(int subplan_id,
-                                          const std::vector<expressions::AttributeReferencePtr> &output_attributes) {
-    return SharedSubplanReferencePtr(new SharedSubplanReference(subplan_id, output_attributes));
+  static SharedSubplanReferencePtr Create(
+      int subplan_id,
+      const std::vector<expressions::AttributeReferencePtr> &referenced_attributes,
+      const std::vector<expressions::AttributeReferencePtr> &output_attributes) {
+    return SharedSubplanReferencePtr(
+        new SharedSubplanReference(subplan_id, referenced_attributes, output_attributes));
   }
 
  private:
   SharedSubplanReference(int subplan_id,
+                         const std::vector<expressions::AttributeReferencePtr> &referenced_attributes,
                          const std::vector<expressions::AttributeReferencePtr> &output_attributes)
     : subplan_id_(subplan_id),
-      output_attributes_(output_attributes) {}
+      referenced_attributes_(referenced_attributes),
+      output_attributes_(output_attributes) {
+    DCHECK_EQ(output_attributes_.size(), referenced_attributes_.size());
+  }
 
   int subplan_id_;
+  std::vector<expressions::AttributeReferencePtr> referenced_attributes_;
   std::vector<expressions::AttributeReferencePtr> output_attributes_;
 
   DISALLOW_COPY_AND_ASSIGN(SharedSubplanReference);

http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/c5460f40/query_optimizer/physical/SharedSubplanReference.cpp
----------------------------------------------------------------------
diff --git a/query_optimizer/physical/SharedSubplanReference.cpp b/query_optimizer/physical/SharedSubplanReference.cpp
index ce851ac..eb33d32 100644
--- a/query_optimizer/physical/SharedSubplanReference.cpp
+++ b/query_optimizer/physical/SharedSubplanReference.cpp
@@ -1,6 +1,8 @@
 /**
  *   Copyright 2011-2015 Quickstep Technologies LLC.
  *   Copyright 2015 Pivotal Software, Inc.
+ *   Copyright 2016, Quickstep Research Group, Computer Sciences Department,
+ *     University of Wisconsin\u2014Madison.
  *
  *   Licensed under the Apache License, Version 2.0 (the "License");
  *   you may not use this file except in compliance with the License.
@@ -40,6 +42,9 @@ void SharedSubplanReference::getFieldStringItems(
   inline_field_names->push_back("subplan_id");
   inline_field_values->push_back(std::to_string(subplan_id_));
 
+  container_child_field_names->push_back("referenced_attributes");
+  container_child_fields->push_back(CastSharedPtrVector<OptimizerTreeBase>(referenced_attributes_));
+
   container_child_field_names->push_back("output_attributes");
   container_child_fields->push_back(CastSharedPtrVector<OptimizerTreeBase>(output_attributes_));
 }

http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/c5460f40/query_optimizer/physical/SharedSubplanReference.hpp
----------------------------------------------------------------------
diff --git a/query_optimizer/physical/SharedSubplanReference.hpp b/query_optimizer/physical/SharedSubplanReference.hpp
index 5005598..4439256 100644
--- a/query_optimizer/physical/SharedSubplanReference.hpp
+++ b/query_optimizer/physical/SharedSubplanReference.hpp
@@ -1,6 +1,8 @@
 /**
  *   Copyright 2011-2015 Quickstep Technologies LLC.
  *   Copyright 2015 Pivotal Software, Inc.
+ *   Copyright 2016, Quickstep Research Group, Computer Sciences Department,
+ *     University of Wisconsin\u2014Madison.
  *
  *   Licensed under the Apache License, Version 2.0 (the "License");
  *   you may not use this file except in compliance with the License.
@@ -63,7 +65,14 @@ class SharedSubplanReference : public physical::Physical {
   }
 
   /**
-   * @return The output attributes of the shared subplan.
+   * @return The attributes from the referenced shared subplan.
+   */
+  const std::vector<expressions::AttributeReferencePtr>& referenced_attributes() const {
+    return referenced_attributes_;
+  }
+
+  /**
+   * @return The output attributes of this shared subplan reference.
    */
   const std::vector<expressions::AttributeReferencePtr>& output_attributes() const {
     return output_attributes_;
@@ -83,11 +92,11 @@ class SharedSubplanReference : public physical::Physical {
 
   PhysicalPtr copyWithNewChildren(const std::vector<PhysicalPtr> &new_children) const override {
     DCHECK(new_children.empty());
-    return Create(subplan_id_, output_attributes_);
+    return Create(subplan_id_, referenced_attributes_, output_attributes_);
   }
 
   std::vector<expressions::AttributeReferencePtr> getReferencedAttributes() const override {
-    return output_attributes_;
+    return referenced_attributes_;
   }
 
   bool maybeCopyWithPrunedExpressions(
@@ -101,21 +110,30 @@ class SharedSubplanReference : public physical::Physical {
    *
    * @param subplan_id The ID of the shared subplan, which is the index of
    *        the referenced shared subplan in <shared_subplans_> of the TopLevelPlan.
-   * @param output_attributes The output attributes of the shared subplan.
+   * @param referenced_attributes The attributes from the referenced shared subplan.
+   * @param output_attributes The output attributes of this shared subplan reference.
    * @return An immutable SharedSubplanReference.
    */
-  static SharedSubplanReferencePtr Create(int subplan_id,
-                                          const std::vector<expressions::AttributeReferencePtr> &output_attributes) {
-    return SharedSubplanReferencePtr(new SharedSubplanReference(subplan_id, output_attributes));
+  static SharedSubplanReferencePtr Create(
+      int subplan_id,
+      const std::vector<expressions::AttributeReferencePtr> &referenced_attributes,
+      const std::vector<expressions::AttributeReferencePtr> &output_attributes) {
+    return SharedSubplanReferencePtr(
+        new SharedSubplanReference(subplan_id, referenced_attributes, output_attributes));
   }
 
  private:
   SharedSubplanReference(int subplan_id,
+                         const std::vector<expressions::AttributeReferencePtr> &referenced_attributes,
                          const std::vector<expressions::AttributeReferencePtr> &output_attributes)
     : subplan_id_(subplan_id),
-      output_attributes_(output_attributes) {}
+      referenced_attributes_(referenced_attributes),
+      output_attributes_(output_attributes) {
+    DCHECK_EQ(output_attributes_.size(), referenced_attributes_.size());
+  }
 
   int subplan_id_;
+  std::vector<expressions::AttributeReferencePtr> referenced_attributes_;
   std::vector<expressions::AttributeReferencePtr> output_attributes_;
 
   DISALLOW_COPY_AND_ASSIGN(SharedSubplanReference);

http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/c5460f40/query_optimizer/resolver/Resolver.cpp
----------------------------------------------------------------------
diff --git a/query_optimizer/resolver/Resolver.cpp b/query_optimizer/resolver/Resolver.cpp
index 1cf5c28..45ecf33 100644
--- a/query_optimizer/resolver/Resolver.cpp
+++ b/query_optimizer/resolver/Resolver.cpp
@@ -1541,9 +1541,28 @@ L::LogicalPtr Resolver::resolveSimpleTableReference(
       with_queries_info_.with_query_name_to_vector_position.find(lower_table_name);
   if (subplan_it != with_queries_info_.with_query_name_to_vector_position.end()) {
     with_queries_info_.unreferenced_query_indexes.erase(subplan_it->second);
-    return L::SharedSubplanReference::Create(
-        subplan_it->second,
-        with_queries_info_.with_query_plans[subplan_it->second]->getOutputAttributes());
+
+    const std::vector<E::AttributeReferencePtr> with_query_attributes =
+        with_queries_info_.with_query_plans[subplan_it->second]->getOutputAttributes();
+
+    // Create a vector of new attributes to delegate the original output attributes
+    // from the WITH query, to avoid (ExprId -> CatalogAttribute) mapping collision
+    // later in ExecutionGenerator when there are multiple SharedSubplanReference's
+    // referencing a same shared subplan.
+    std::vector<E::AttributeReferencePtr> delegator_attributes;
+    for (const E::AttributeReferencePtr &attribute : with_query_attributes) {
+      delegator_attributes.emplace_back(
+          E::AttributeReference::Create(context_->nextExprId(),
+                                        attribute->attribute_name(),
+                                        attribute->attribute_alias(),
+                                        attribute->relation_name(),
+                                        attribute->getValueType(),
+                                        attribute->scope()));
+    }
+
+    return L::SharedSubplanReference::Create(subplan_it->second,
+                                             with_query_attributes,
+                                             delegator_attributes);
   }
 
   // Then look up the name in the database.

http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/c5460f40/query_optimizer/strategy/OneToOne.cpp
----------------------------------------------------------------------
diff --git a/query_optimizer/strategy/OneToOne.cpp b/query_optimizer/strategy/OneToOne.cpp
index d4d79f9..7f59151 100644
--- a/query_optimizer/strategy/OneToOne.cpp
+++ b/query_optimizer/strategy/OneToOne.cpp
@@ -84,6 +84,7 @@ bool OneToOne::generatePlan(const L::LogicalPtr &logical_input,
       const L::SharedSubplanReferencePtr shared_subplan_reference =
           std::static_pointer_cast<const L::SharedSubplanReference>(logical_input);
       *physical_output = P::SharedSubplanReference::Create(shared_subplan_reference->subplan_id(),
+                                                           shared_subplan_reference->referenced_attributes(),
                                                            shared_subplan_reference->output_attributes());
       return true;
     }

http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/c5460f40/query_optimizer/tests/execution_generator/Select.test
----------------------------------------------------------------------
diff --git a/query_optimizer/tests/execution_generator/Select.test b/query_optimizer/tests/execution_generator/Select.test
index 3a64c9b..a08b012 100644
--- a/query_optimizer/tests/execution_generator/Select.test
+++ b/query_optimizer/tests/execution_generator/Select.test
@@ -891,6 +891,31 @@ FROM (
 +-----------+--------------------+-----------+------------------------+
 ==
 
+# Same shared subplan referenced multiple times.
+WITH t(x, y) AS (
+  SELECT i % 5, i
+  FROM generate_series(1, 20) AS g(i)
+)
+SELECT *
+FROM t
+WHERE t.y = (
+  SELECT MAX(y)
+  FROM t t1
+  WHERE t.x = t1.x
+)
+ORDER BY x;
+--
++-----------+-----------+
+|x          |y          |
++-----------+-----------+
+|          0|         20|
+|          1|         16|
+|          2|         17|
+|          3|         18|
+|          4|         19|
++-----------+-----------+
+==
+
 # TODO(team): Fix Issue #9 to enable COUNT(*).
 SELECT COUNT(long_col)
 FROM test,

http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/c5460f40/query_optimizer/tests/logical_generator/Select.test
----------------------------------------------------------------------
diff --git a/query_optimizer/tests/logical_generator/Select.test b/query_optimizer/tests/logical_generator/Select.test
index 6dff3e7..3c152e8 100644
--- a/query_optimizer/tests/logical_generator/Select.test
+++ b/query_optimizer/tests/logical_generator/Select.test
@@ -502,22 +502,29 @@ TopLevelPlan
 | | | |   type=VarChar(20) NULL]
 | | | +-right=Filter
 | | | | +-input=SharedSubplanReference[subplan_id=0]
+| | | | | +-referenced_attributes=
+| | | | | | +-AttributeReference[id=0,name=subquery_int0,relation=subquery,
+| | | | | | | type=Int NULL]
+| | | | | | +-AttributeReference[id=7,name=subquery_long,relation=subquery,
+| | | | | | | type=Long]
+| | | | | | +-AttributeReference[id=12,name=subquery_int1,relation=subquery,
+| | | | | |   type=Int NULL]
 | | | | | +-output_attributes=
-| | | | |   +-AttributeReference[id=0,name=subquery_int0,relation=subquery,
+| | | | |   +-AttributeReference[id=36,name=subquery_int0,relation=subquery,
 | | | | |   | type=Int NULL]
-| | | | |   +-AttributeReference[id=7,name=subquery_long,relation=subquery,
+| | | | |   +-AttributeReference[id=37,name=subquery_long,relation=subquery,
 | | | | |   | type=Long]
-| | | | |   +-AttributeReference[id=12,name=subquery_int1,relation=subquery,
+| | | | |   +-AttributeReference[id=38,name=subquery_int1,relation=subquery,
 | | | | |     type=Int NULL]
 | | | | +-filter_predicate=Equal
-| | | |   +-AttributeReference[id=12,name=subquery_int1,relation=subquery,
+| | | |   +-AttributeReference[id=38,name=subquery_int1,relation=subquery,
 | | | |   | type=Int NULL]
-| | | |   +-AttributeReference[id=7,name=subquery_long,relation=subquery,
+| | | |   +-AttributeReference[id=37,name=subquery_long,relation=subquery,
 | | | |     type=Long]
 | | | +-left_join_attributes=
 | | | | +-AttributeReference[id=30,name=int_col,relation=c,type=Int NULL]
 | | | +-right_join_attributes=
-| | |   +-AttributeReference[id=0,name=subquery_int0,relation=subquery,
+| | |   +-AttributeReference[id=36,name=subquery_int0,relation=subquery,
 | | |     type=Int NULL]
 | | +-join_predicate=Literal[value=true]
 | +-project_list=
@@ -572,11 +579,14 @@ select * from subquery
 TopLevelPlan
 +-plan=Project
 | +-input=SharedSubplanReference[subplan_id=0]
+| | +-referenced_attributes=
+| | | +-AttributeReference[id=7,name=,alias=(int_col+2),relation=subquery,
+| | |   type=Int NULL]
 | | +-output_attributes=
-| |   +-AttributeReference[id=7,name=,alias=(int_col+2),relation=subquery,
+| |   +-AttributeReference[id=8,name=,alias=(int_col+2),relation=subquery,
 | |     type=Int NULL]
 | +-project_list=
-|   +-AttributeReference[id=7,name=,alias=(int_col+2),relation=subquery,
+|   +-AttributeReference[id=8,name=,alias=(int_col+2),relation=subquery,
 |     type=Int NULL]
 +-shared_subplans=
 | +-Project
@@ -596,7 +606,7 @@ TopLevelPlan
 |         | +-Literal[value=1,type=Int]
 |         +-Literal[value=2,type=Int]
 +-output_attributes=
-  +-AttributeReference[id=7,name=,alias=(int_col+2),relation=subquery,
+  +-AttributeReference[id=8,name=,alias=(int_col+2),relation=subquery,
     type=Int NULL]
 ==
 
@@ -1266,3 +1276,81 @@ SELECT x + (
 FROM b;
 --
 ERROR: Nested queries can only reference attributes in the outer query one level above
+==
+
+# Same shared subplan referenced multiple times.
+WITH t(x, y) AS (
+  SELECT i % 5, i
+  FROM generate_series(1, 20) AS g(i)
+)
+SELECT *
+FROM t
+WHERE t.y = (
+  SELECT MAX(y)
+  FROM t t1
+  WHERE t.x = t1.x
+)
+ORDER BY x;
+--
+TopLevelPlan
++-plan=Project
+| +-input=Sort[is_ascending=[true],nulls_first=[false]]
+| | +-input=Filter
+| | | +-input=HashJoin
+| | | | +-left=SharedSubplanReference[subplan_id=0]
+| | | | | +-referenced_attributes=
+| | | | | | +-AttributeReference[id=3,name=x,relation=,type=Int]
+| | | | | | +-AttributeReference[id=4,name=y,relation=,type=Int]
+| | | | | +-output_attributes=
+| | | | |   +-AttributeReference[id=5,name=x,relation=,type=Int]
+| | | | |   +-AttributeReference[id=6,name=y,relation=,type=Int]
+| | | | +-right=Project
+| | | | | +-input=Aggregate
+| | | | | | +-input=SharedSubplanReference[subplan_id=0]
+| | | | | | | +-referenced_attributes=
+| | | | | | | | +-AttributeReference[id=3,name=x,relation=,type=Int]
+| | | | | | | | +-AttributeReference[id=4,name=y,relation=,type=Int]
+| | | | | | | +-output_attributes=
+| | | | | | |   +-AttributeReference[id=7,name=x,relation=,type=Int]
+| | | | | | |   +-AttributeReference[id=8,name=y,relation=,type=Int]
+| | | | | | +-grouping_expressions=
+| | | | | | | +-AttributeReference[id=7,name=x,relation=,type=Int]
+| | | | | | +-aggregate_expressions=
+| | | | | |   +-Alias[id=9,name=,alias=$aggregate0,relation=$aggregate,
+| | | | | |     type=Int NULL]
+| | | | | |     +-AggregateFunction[function=MAX]
+| | | | | |       +-AttributeReference[id=8,name=y,relation=,type=Int]
+| | | | | +-project_list=
+| | | | |   +-AttributeReference[id=7,name=x,relation=,type=Int]
+| | | | |   +-Alias[id=9,name=,alias=MAX(y),relation=,type=Int NULL]
+| | | | |     +-AttributeReference[id=9,name=,alias=$aggregate0,
+| | | | |       relation=$aggregate,type=Int NULL]
+| | | | +-left_join_attributes=
+| | | | | +-AttributeReference[id=5,name=x,relation=,type=Int]
+| | | | +-right_join_attributes=
+| | | |   +-AttributeReference[id=7,name=x,relation=,type=Int]
+| | | +-filter_predicate=Equal
+| | |   +-AttributeReference[id=6,name=y,relation=,type=Int]
+| | |   +-AttributeReference[id=9,name=,alias=MAX(y),relation=,type=Int NULL]
+| | +-sort_expressions=
+| |   +-AttributeReference[id=5,name=x,relation=,type=Int]
+| +-project_list=
+|   +-AttributeReference[id=5,name=x,relation=,type=Int]
+|   +-AttributeReference[id=6,name=y,relation=,type=Int]
++-shared_subplans=
+| +-Project
+|   +-input=TableGenerator[function_name=generate_series,table_alias=g]
+|   | +-AttributeReference[id=0,name=generate_series,alias=g,
+|   |   relation=generate_series,type=Int]
+|   +-project_list=
+|     +-Alias[id=3,name=x,relation=,type=Int]
+|     | +-Modulo
+|     |   +-AttributeReference[id=0,name=generate_series,alias=g,
+|     |   | relation=generate_series,type=Int]
+|     |   +-Literal[value=5,type=Int]
+|     +-Alias[id=4,name=y,relation=,type=Int]
+|       +-AttributeReference[id=0,name=generate_series,alias=g,
+|         relation=generate_series,type=Int]
++-output_attributes=
+  +-AttributeReference[id=5,name=x,relation=,type=Int]
+  +-AttributeReference[id=6,name=y,relation=,type=Int]

http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/c5460f40/query_optimizer/tests/physical_generator/Select.test
----------------------------------------------------------------------
diff --git a/query_optimizer/tests/physical_generator/Select.test b/query_optimizer/tests/physical_generator/Select.test
index 62d09f5..3365206 100644
--- a/query_optimizer/tests/physical_generator/Select.test
+++ b/query_optimizer/tests/physical_generator/Select.test
@@ -1542,12 +1542,16 @@ select int_col from subquery
 TopLevelPlan
 +-plan=Project
 | +-input=SharedSubplanReference[subplan_id=0]
+| | +-referenced_attributes=
+| | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
+| | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
+| | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL]
 | | +-output_attributes=
-| |   +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
-| |   +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
-| |   +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL]
+| |   +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
+| |   +-AttributeReference[id=7,name=long_col,relation=test,type=Long]
+| |   +-AttributeReference[id=8,name=double_col,relation=test,type=Double NULL]
 | +-project_list=
-|   +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
+|   +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
 +-shared_subplans=
 | +-Project
 |   +-input=TableReference[relation_name=Test,relation_alias=test]
@@ -1563,17 +1567,21 @@ TopLevelPlan
 |     +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
 |     +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL]
 +-output_attributes=
-  +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
+  +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
 [Physical Plan]
 TopLevelPlan
 +-plan=Selection
 | +-input=SharedSubplanReference[subplan_id=0]
+| | +-referenced_attributes=
+| | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
+| | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
+| | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL]
 | | +-output_attributes=
-| |   +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
-| |   +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
-| |   +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL]
+| |   +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
+| |   +-AttributeReference[id=7,name=long_col,relation=test,type=Long]
+| |   +-AttributeReference[id=8,name=double_col,relation=test,type=Double NULL]
 | +-project_expressions=
-|   +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
+|   +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
 +-shared_subplans=
 | +-Selection
 |   +-input=TableReference[relation=Test,alias=test]
@@ -1589,7 +1597,7 @@ TopLevelPlan
 |     +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
 |     +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL]
 +-output_attributes=
-  +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
+  +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
 ==
 
 SELECT COUNT(DISTINCT int_col), SUM(float_col)
@@ -2731,3 +2739,140 @@ TopLevelPlan
 +-output_attributes=
   +-AttributeReference[id=8,name=,alias=(x*SubqueryExpression),relation=,
     type=Long NULL]
+==
+
+# Same shared subplan referenced multiple times.
+WITH t(x, y) AS (
+  SELECT i % 5, i
+  FROM generate_series(1, 20) AS g(i)
+)
+SELECT *
+FROM t
+WHERE t.y = (
+  SELECT MAX(y)
+  FROM t t1
+  WHERE t.x = t1.x
+)
+ORDER BY x;
+--
+[Optimized Logical Plan]
+TopLevelPlan
++-plan=Project
+| +-input=Sort[is_ascending=[true],nulls_first=[false]]
+| | +-input=Filter
+| | | +-input=HashJoin
+| | | | +-left=SharedSubplanReference[subplan_id=0]
+| | | | | +-referenced_attributes=
+| | | | | | +-AttributeReference[id=3,name=x,relation=,type=Int]
+| | | | | | +-AttributeReference[id=4,name=y,relation=,type=Int]
+| | | | | +-output_attributes=
+| | | | |   +-AttributeReference[id=5,name=x,relation=,type=Int]
+| | | | |   +-AttributeReference[id=6,name=y,relation=,type=Int]
+| | | | +-right=Project
+| | | | | +-input=Aggregate
+| | | | | | +-input=SharedSubplanReference[subplan_id=0]
+| | | | | | | +-referenced_attributes=
+| | | | | | | | +-AttributeReference[id=3,name=x,relation=,type=Int]
+| | | | | | | | +-AttributeReference[id=4,name=y,relation=,type=Int]
+| | | | | | | +-output_attributes=
+| | | | | | |   +-AttributeReference[id=7,name=x,relation=,type=Int]
+| | | | | | |   +-AttributeReference[id=8,name=y,relation=,type=Int]
+| | | | | | +-grouping_expressions=
+| | | | | | | +-AttributeReference[id=7,name=x,relation=,type=Int]
+| | | | | | +-aggregate_expressions=
+| | | | | |   +-Alias[id=9,name=,alias=$aggregate0,relation=$aggregate,
+| | | | | |     type=Int NULL]
+| | | | | |     +-AggregateFunction[function=MAX]
+| | | | | |       +-AttributeReference[id=8,name=y,relation=,type=Int]
+| | | | | +-project_list=
+| | | | |   +-AttributeReference[id=7,name=x,relation=,type=Int]
+| | | | |   +-Alias[id=9,name=,alias=MAX(y),relation=,type=Int NULL]
+| | | | |     +-AttributeReference[id=9,name=,alias=$aggregate0,
+| | | | |       relation=$aggregate,type=Int NULL]
+| | | | +-left_join_attributes=
+| | | | | +-AttributeReference[id=5,name=x,relation=,type=Int]
+| | | | +-right_join_attributes=
+| | | |   +-AttributeReference[id=7,name=x,relation=,type=Int]
+| | | +-filter_predicate=Equal
+| | |   +-AttributeReference[id=6,name=y,relation=,type=Int]
+| | |   +-AttributeReference[id=9,name=,alias=MAX(y),relation=,type=Int NULL]
+| | +-sort_expressions=
+| |   +-AttributeReference[id=5,name=x,relation=,type=Int]
+| +-project_list=
+|   +-AttributeReference[id=5,name=x,relation=,type=Int]
+|   +-AttributeReference[id=6,name=y,relation=,type=Int]
++-shared_subplans=
+| +-Project
+|   +-input=TableGenerator[function_name=generate_series,table_alias=g]
+|   | +-AttributeReference[id=0,name=generate_series,alias=g,
+|   |   relation=generate_series,type=Int]
+|   +-project_list=
+|     +-Alias[id=3,name=x,relation=,type=Int]
+|     | +-Modulo
+|     |   +-AttributeReference[id=0,name=generate_series,alias=g,
+|     |   | relation=generate_series,type=Int]
+|     |   +-Literal[value=5,type=Int]
+|     +-Alias[id=4,name=y,relation=,type=Int]
+|       +-AttributeReference[id=0,name=generate_series,alias=g,
+|         relation=generate_series,type=Int]
++-output_attributes=
+  +-AttributeReference[id=5,name=x,relation=,type=Int]
+  +-AttributeReference[id=6,name=y,relation=,type=Int]
+[Physical Plan]
+TopLevelPlan
++-plan=Selection
+| +-input=Sort[is_ascending=[true],nulls_first=[false]]
+| | +-input=HashJoin
+| | | +-left=SharedSubplanReference[subplan_id=0]
+| | | | +-referenced_attributes=
+| | | | | +-AttributeReference[id=3,name=x,relation=,type=Int]
+| | | | | +-AttributeReference[id=4,name=y,relation=,type=Int]
+| | | | +-output_attributes=
+| | | |   +-AttributeReference[id=5,name=x,relation=,type=Int]
+| | | |   +-AttributeReference[id=6,name=y,relation=,type=Int]
+| | | +-right=Aggregate
+| | | | +-input=SharedSubplanReference[subplan_id=0]
+| | | | | +-referenced_attributes=
+| | | | | | +-AttributeReference[id=3,name=x,relation=,type=Int]
+| | | | | | +-AttributeReference[id=4,name=y,relation=,type=Int]
+| | | | | +-output_attributes=
+| | | | |   +-AttributeReference[id=7,name=x,relation=,type=Int]
+| | | | |   +-AttributeReference[id=8,name=y,relation=,type=Int]
+| | | | +-grouping_expressions=
+| | | | | +-AttributeReference[id=7,name=x,relation=,type=Int]
+| | | | +-aggregate_expressions=
+| | | |   +-Alias[id=9,name=,alias=$aggregate0,relation=$aggregate,type=Int NULL]
+| | | |     +-AggregateFunction[function=MAX]
+| | | |       +-AttributeReference[id=8,name=y,relation=,type=Int]
+| | | +-project_expressions=
+| | | | +-AttributeReference[id=5,name=x,relation=,type=Int]
+| | | | +-AttributeReference[id=6,name=y,relation=,type=Int]
+| | | +-left_join_attributes=
+| | | | +-AttributeReference[id=5,name=x,relation=,type=Int]
+| | | | +-AttributeReference[id=6,name=y,relation=,type=Int]
+| | | +-right_join_attributes=
+| | |   +-AttributeReference[id=7,name=x,relation=,type=Int]
+| | |   +-AttributeReference[id=9,name=,alias=$aggregate0,relation=$aggregate,
+| | |     type=Int NULL]
+| | +-sort_attributes=
+| |   +-AttributeReference[id=5,name=x,relation=,type=Int]
+| +-project_expressions=
+|   +-AttributeReference[id=5,name=x,relation=,type=Int]
+|   +-AttributeReference[id=6,name=y,relation=,type=Int]
++-shared_subplans=
+| +-Selection
+|   +-input=TableGenerator[function_name=generate_series,table_alias=g]
+|   | +-AttributeReference[id=0,name=generate_series,alias=g,
+|   |   relation=generate_series,type=Int]
+|   +-project_expressions=
+|     +-Alias[id=3,name=x,relation=,type=Int]
+|     | +-Modulo
+|     |   +-AttributeReference[id=0,name=generate_series,alias=g,
+|     |   | relation=generate_series,type=Int]
+|     |   +-Literal[value=5,type=Int]
+|     +-Alias[id=4,name=y,relation=,type=Int]
+|       +-AttributeReference[id=0,name=generate_series,alias=g,
+|         relation=generate_series,type=Int]
++-output_attributes=
+  +-AttributeReference[id=5,name=x,relation=,type=Int]
+  +-AttributeReference[id=6,name=y,relation=,type=Int]

http://git-wip-us.apache.org/repos/asf/incubator-quickstep/blob/c5460f40/query_optimizer/tests/resolver/Select.test
----------------------------------------------------------------------
diff --git a/query_optimizer/tests/resolver/Select.test b/query_optimizer/tests/resolver/Select.test
index 00ff18a..9897934 100644
--- a/query_optimizer/tests/resolver/Select.test
+++ b/query_optimizer/tests/resolver/Select.test
@@ -1723,11 +1723,14 @@ select subquery_col from subquery
 TopLevelPlan
 +-plan=Project
 | +-input=SharedSubplanReference[subplan_id=0]
+| | +-referenced_attributes=
+| | | +-AttributeReference[id=0,name=subquery_col,relation=subquery,
+| | |   type=Int NULL]
 | | +-output_attributes=
-| |   +-AttributeReference[id=0,name=subquery_col,relation=subquery,
+| |   +-AttributeReference[id=6,name=subquery_col,relation=subquery,
 | |     type=Int NULL]
 | +-project_list=
-|   +-AttributeReference[id=0,name=subquery_col,relation=subquery,type=Int NULL]
+|   +-AttributeReference[id=6,name=subquery_col,relation=subquery,type=Int NULL]
 +-shared_subplans=
 | +-Project
 |   +-input=TableReference[relation_name=Test,relation_alias=test]
@@ -1742,7 +1745,7 @@ TopLevelPlan
 |     +-Alias[id=0,name=subquery_col,relation=subquery,type=Int NULL]
 |       +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
 +-output_attributes=
-  +-AttributeReference[id=0,name=subquery_col,relation=subquery,type=Int NULL]
+  +-AttributeReference[id=6,name=subquery_col,relation=subquery,type=Int NULL]
 ==
 
 # Multiple WITH queries.
@@ -1755,14 +1758,18 @@ TopLevelPlan
 +-plan=Project
 | +-input=MultiwayCartesianJoin
 | | +-SharedSubplanReference[subplan_id=2]
+| | | +-referenced_attributes=
+| | | | +-AttributeReference[id=10,name=int_col,relation=subquery3,type=Int NULL]
 | | | +-output_attributes=
-| | |   +-AttributeReference[id=7,name=int_col,relation=subquery3,type=Int NULL]
+| | |   +-AttributeReference[id=11,name=int_col,relation=subquery3,type=Int NULL]
 | | +-SharedSubplanReference[subplan_id=1]
+| |   +-referenced_attributes=
+| |   | +-AttributeReference[id=7,name=int_col,relation=subquery2,type=Int NULL]
 | |   +-output_attributes=
-| |     +-AttributeReference[id=6,name=int_col,relation=subquery2,type=Int NULL]
+| |     +-AttributeReference[id=12,name=int_col,relation=subquery2,type=Int NULL]
 | +-project_list=
-|   +-AttributeReference[id=7,name=int_col,relation=subquery3,type=Int NULL]
-|   +-AttributeReference[id=6,name=int_col,relation=subquery2,type=Int NULL]
+|   +-AttributeReference[id=11,name=int_col,relation=subquery3,type=Int NULL]
+|   +-AttributeReference[id=12,name=int_col,relation=subquery2,type=Int NULL]
 +-shared_subplans=
 | +-Project
 | | +-input=TableReference[relation_name=Test,relation_alias=test]
@@ -1777,31 +1784,38 @@ TopLevelPlan
 | |   +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
 | +-Project
 | | +-input=SharedSubplanReference[subplan_id=0]
+| | | +-referenced_attributes=
+| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
 | | | +-output_attributes=
-| | |   +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
+| | |   +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
 | | +-project_list=
-| |   +-Alias[id=6,name=int_col,relation=subquery2,type=Int NULL]
+| |   +-Alias[id=7,name=int_col,relation=subquery2,type=Int NULL]
 | |     +-Add
-| |       +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
+| |       +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
 | |       +-Literal[value=5,type=Int]
 | +-Project
 |   +-input=MultiwayCartesianJoin
 |   | +-SharedSubplanReference[subplan_id=1]
+|   | | +-referenced_attributes=
+|   | | | +-AttributeReference[id=7,name=int_col,relation=subquery2,
+|   | | |   type=Int NULL]
 |   | | +-output_attributes=
-|   | |   +-AttributeReference[id=6,name=int_col,relation=subquery2,
+|   | |   +-AttributeReference[id=8,name=int_col,relation=subquery2,
 |   | |     type=Int NULL]
 |   | +-SharedSubplanReference[subplan_id=0]
+|   |   +-referenced_attributes=
+|   |   | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
 |   |   +-output_attributes=
-|   |     +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
+|   |     +-AttributeReference[id=9,name=int_col,relation=test,type=Int NULL]
 |   +-project_list=
-|     +-Alias[id=7,name=int_col,relation=subquery3,type=Int NULL]
+|     +-Alias[id=10,name=int_col,relation=subquery3,type=Int NULL]
 |       +-Add
-|         +-AttributeReference[id=6,name=int_col,relation=subquery2,
+|         +-AttributeReference[id=8,name=int_col,relation=subquery2,
 |         | type=Int NULL]
 |         +-Literal[value=6,type=Int]
 +-output_attributes=
-  +-AttributeReference[id=7,name=int_col,relation=subquery3,type=Int NULL]
-  +-AttributeReference[id=6,name=int_col,relation=subquery2,type=Int NULL]
+  +-AttributeReference[id=11,name=int_col,relation=subquery3,type=Int NULL]
+  +-AttributeReference[id=12,name=int_col,relation=subquery2,type=Int NULL]
 ==
 
 with subquery(int_col_alias, long_col_alias) as (select int_col, long_col from test)
@@ -1810,11 +1824,14 @@ select int_col_alias from subquery
 TopLevelPlan
 +-plan=Project
 | +-input=SharedSubplanReference[subplan_id=0]
+| | +-referenced_attributes=
+| | | +-AttributeReference[id=6,name=int_col_alias,relation=,type=Int NULL]
+| | | +-AttributeReference[id=7,name=long_col_alias,relation=,type=Long]
 | | +-output_attributes=
-| |   +-AttributeReference[id=6,name=int_col_alias,relation=,type=Int NULL]
-| |   +-AttributeReference[id=7,name=long_col_alias,relation=,type=Long]
+| |   +-AttributeReference[id=8,name=int_col_alias,relation=,type=Int NULL]
+| |   +-AttributeReference[id=9,name=long_col_alias,relation=,type=Long]
 | +-project_list=
-|   +-AttributeReference[id=6,name=int_col_alias,relation=,type=Int NULL]
+|   +-AttributeReference[id=8,name=int_col_alias,relation=,type=Int NULL]
 +-shared_subplans=
 | +-Project
 |   +-input=Project
@@ -1835,7 +1852,7 @@ TopLevelPlan
 |     +-Alias[id=7,name=long_col_alias,relation=,type=Long]
 |       +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
 +-output_attributes=
-  +-AttributeReference[id=6,name=int_col_alias,relation=,type=Int NULL]
+  +-AttributeReference[id=8,name=int_col_alias,relation=,type=Int NULL]
 ==
 
 with subquery as (select int_col, long_col from test)
@@ -1845,16 +1862,19 @@ TopLevelPlan
 +-plan=Project
 | +-input=Project
 | | +-input=SharedSubplanReference[subplan_id=0]
+| | | +-referenced_attributes=
+| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
+| | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
 | | | +-output_attributes=
-| | |   +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
-| | |   +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
+| | |   +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
+| | |   +-AttributeReference[id=7,name=long_col,relation=test,type=Long]
 | | +-project_list=
-| |   +-Alias[id=6,name=int_col_alias,relation=,type=Int NULL]
-| |   | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
-| |   +-Alias[id=7,name=long_col_alias,relation=,type=Long]
-| |     +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
+| |   +-Alias[id=8,name=int_col_alias,relation=,type=Int NULL]
+| |   | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
+| |   +-Alias[id=9,name=long_col_alias,relation=,type=Long]
+| |     +-AttributeReference[id=7,name=long_col,relation=test,type=Long]
 | +-project_list=
-|   +-AttributeReference[id=6,name=int_col_alias,relation=,type=Int NULL]
+|   +-AttributeReference[id=8,name=int_col_alias,relation=,type=Int NULL]
 +-shared_subplans=
 | +-Project
 |   +-input=TableReference[relation_name=Test,relation_alias=test]
@@ -1869,7 +1889,7 @@ TopLevelPlan
 |     +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
 |     +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
 +-output_attributes=
-  +-AttributeReference[id=6,name=int_col_alias,relation=,type=Int NULL]
+  +-AttributeReference[id=8,name=int_col_alias,relation=,type=Int NULL]
 ==
 
 with subquery as (select int_col int_col_alias, long_col from test)
@@ -1878,12 +1898,16 @@ select int_col_alias from subquery
 TopLevelPlan
 +-plan=Project
 | +-input=SharedSubplanReference[subplan_id=0]
+| | +-referenced_attributes=
+| | | +-AttributeReference[id=0,name=int_col_alias,relation=subquery,
+| | | | type=Int NULL]
+| | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
 | | +-output_attributes=
-| |   +-AttributeReference[id=0,name=int_col_alias,relation=subquery,
+| |   +-AttributeReference[id=6,name=int_col_alias,relation=subquery,
 | |   | type=Int NULL]
-| |   +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
+| |   +-AttributeReference[id=7,name=long_col,relation=test,type=Long]
 | +-project_list=
-|   +-AttributeReference[id=0,name=int_col_alias,relation=subquery,type=Int NULL]
+|   +-AttributeReference[id=6,name=int_col_alias,relation=subquery,type=Int NULL]
 +-shared_subplans=
 | +-Project
 |   +-input=TableReference[relation_name=Test,relation_alias=test]
@@ -1899,7 +1923,7 @@ TopLevelPlan
 |     | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
 |     +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
 +-output_attributes=
-  +-AttributeReference[id=0,name=int_col_alias,relation=subquery,type=Int NULL]
+  +-AttributeReference[id=6,name=int_col_alias,relation=subquery,type=Int NULL]
 ==
 
 # A WITH query cannot reference a subsequent WITH query.
@@ -1936,10 +1960,12 @@ select 1 from test
 TopLevelPlan
 +-plan=Project
 | +-input=SharedSubplanReference[subplan_id=0]
+| | +-referenced_attributes=
+| | | +-AttributeReference[id=6,name=,alias=1,relation=test,type=Int]
 | | +-output_attributes=
-| |   +-AttributeReference[id=6,name=,alias=1,relation=test,type=Int]
+| |   +-AttributeReference[id=7,name=,alias=1,relation=test,type=Int]
 | +-project_list=
-|   +-Alias[id=7,name=,alias=1,relation=,type=Int]
+|   +-Alias[id=8,name=,alias=1,relation=,type=Int]
 |     +-Literal[value=1,type=Int]
 +-shared_subplans=
 | +-Project
@@ -1955,7 +1981,7 @@ TopLevelPlan
 |     +-Alias[id=6,name=,alias=1,relation=test,type=Int]
 |       +-Literal[value=1,type=Int]
 +-output_attributes=
-  +-AttributeReference[id=7,name=,alias=1,relation=,type=Int]
+  +-AttributeReference[id=8,name=,alias=1,relation=,type=Int]
 ==
 
 # Identifies with special characters.
@@ -2937,3 +2963,88 @@ TopLevelPlan
 +-output_attributes=
   +-AttributeReference[id=11,name=,alias=(x+SubqueryExpression),relation=,
     type=Long NULL]
+==
+
+# Same shared subplan referenced multiple times.
+WITH t(x, y) AS (
+  SELECT i % 5, i
+  FROM generate_series(1, 20) AS g(i)
+)
+SELECT *
+FROM t
+WHERE t.y = (
+  SELECT MAX(y)
+  FROM t t1
+  WHERE t.x = t1.x
+)
+ORDER BY x;
+--
+TopLevelPlan
++-plan=Project
+| +-input=Sort[is_ascending=[true],nulls_first=[false]]
+| | +-input=Filter
+| | | +-input=SharedSubplanReference[subplan_id=0]
+| | | | +-referenced_attributes=
+| | | | | +-AttributeReference[id=3,name=x,relation=,type=Int]
+| | | | | +-AttributeReference[id=4,name=y,relation=,type=Int]
+| | | | +-output_attributes=
+| | | |   +-AttributeReference[id=5,name=x,relation=,type=Int]
+| | | |   +-AttributeReference[id=6,name=y,relation=,type=Int]
+| | | +-filter_predicate=Equal
+| | |   +-AttributeReference[id=6,name=y,relation=,type=Int]
+| | |   +-SubqueryExpression
+| | |     +-subquery=Project
+| | |       +-input=Aggregate
+| | |       | +-input=Filter
+| | |       | | +-input=SharedSubplanReference[subplan_id=0]
+| | |       | | | +-referenced_attributes=
+| | |       | | | | +-AttributeReference[id=3,name=x,relation=,type=Int]
+| | |       | | | | +-AttributeReference[id=4,name=y,relation=,type=Int]
+| | |       | | | +-output_attributes=
+| | |       | | |   +-AttributeReference[id=7,name=x,relation=,type=Int]
+| | |       | | |   +-AttributeReference[id=8,name=y,relation=,type=Int]
+| | |       | | +-filter_predicate=Equal
+| | |       | |   +-AttributeReference[id=5,name=x,relation=,type=Int,
+| | |       | |   | is_outer_reference=true]
+| | |       | |   +-AttributeReference[id=7,name=x,relation=,type=Int]
+| | |       | +-grouping_expressions=
+| | |       | | +-[]
+| | |       | +-aggregate_expressions=
+| | |       |   +-Alias[id=9,name=,alias=$aggregate0,relation=$aggregate,
+| | |       |     type=Int NULL]
+| | |       |     +-AggregateFunction[function=MAX]
+| | |       |       +-AttributeReference[id=8,name=y,relation=,type=Int]
+| | |       +-project_list=
+| | |         +-Alias[id=9,name=,alias=MAX(y),relation=,type=Int NULL]
+| | |           +-AttributeReference[id=9,name=,alias=$aggregate0,
+| | |             relation=$aggregate,type=Int NULL]
+| | +-sort_expressions=
+| |   +-AttributeReference[id=5,name=x,relation=,type=Int]
+| +-project_list=
+|   +-AttributeReference[id=5,name=x,relation=,type=Int]
+|   +-AttributeReference[id=6,name=y,relation=,type=Int]
++-shared_subplans=
+| +-Project
+|   +-input=Project
+|   | +-input=Project
+|   | | +-input=TableGenerator[function_name=generate_series,table_alias=g]
+|   | | | +-AttributeReference[id=0,name=generate_series,alias=g,
+|   | | |   relation=generate_series,type=Int]
+|   | | +-project_list=
+|   | |   +-Alias[id=1,name=i,relation=,type=Int]
+|   | |     +-AttributeReference[id=0,name=generate_series,alias=g,
+|   | |       relation=generate_series,type=Int]
+|   | +-project_list=
+|   |   +-Alias[id=2,name=,alias=(i%5),relation=t,type=Int]
+|   |   | +-Modulo
+|   |   |   +-AttributeReference[id=1,name=i,relation=,type=Int]
+|   |   |   +-Literal[value=5,type=Int]
+|   |   +-AttributeReference[id=1,name=i,relation=,type=Int]
+|   +-project_list=
+|     +-Alias[id=3,name=x,relation=,type=Int]
+|     | +-AttributeReference[id=2,name=,alias=(i%5),relation=t,type=Int]
+|     +-Alias[id=4,name=y,relation=,type=Int]
+|       +-AttributeReference[id=1,name=i,relation=,type=Int]
++-output_attributes=
+  +-AttributeReference[id=5,name=x,relation=,type=Int]
+  +-AttributeReference[id=6,name=y,relation=,type=Int]