You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cloudberry.apache.org by ma...@apache.org on 2025/02/21 09:45:34 UTC

(cloudberry) 19/30: Fix query hang / fallback if involving CTE of replicated

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

maxyang pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry.git

commit cc4e8f3c2d45b7d38be7773a4529258469402788
Author: Jingyu Wang <wj...@vmware.com>
AuthorDate: Thu Jan 4 16:34:57 2024 -0800

    Fix query hang / fallback if involving CTE of replicated
    
    **Motivation**
    
    This subject has been revisited on several occasions, as seen in the merged PR
    13833, PR 14896, and closed PR 13728. However, sporadically, we still encounter
    unexpected fallbacks. The hang in the CTE query is a result of a mismatch
    between the number of CTE producers and consumers. This mismatch can occur when
    there are either more producers than consumers or more consumers than producers,
    leading to unconsumed producers or starved consumers that cause the query to
    hang.
    
    Historically, we have addressed query hangs by resorting to intentional
    fallbacks. While ORCA falling back on the planner is not an ideal solution, it
    is preferable to the query not completing. The intentional fallback was
    implemented in PR 13833 by restricting the optimization of potential
    alternatives that could result in a producer/consumer number mismatch.
    
    In this PR, we reevaluate several test cases previously examined, aiming to find
    a solution that avoids hanging or falling back.
    
    **RCA**
    
    The hang in a CTE query commonly arises when the CTE is of a replicated nature,
    either tainted or strict. Such occurrences are less frequent if the CTE is hash
    or randomly distributed. In the latter case, both producer and consumer
    naturally execute on all segments. However, given a replicated input, producers
    and consumers can choose to execute on either all segments and then deduplicate,
    or, just on one segment. This flexibility results in potential mismatch between
    the numbers of producers and consumers. This PR tackles the problem by sending
    appropriate distribution requests on both the producer and consumer. Note that a
    query hang is usually not a concern with universal input, despite the presence
    of duplicate tuples. Universal producer guarantees universal data availability,
    and the consumer execution operates based on necessity. This avoids any mismatch
    between producer and consumer numbers.
    
    **Implementation**
    
    Historically, we initiate two sets of distribution requests from the physical
    sequence operator to its two children. In the first request, we specify both
    children to be `Non-Singleton`, aiming for the producer and consumer to execute
    across all segments. In the second request, we request `Any` on the producer,
    allowing it to retain its inherent distribution property. We align the
    consumer's request accordingly. Specifically, if the producer is universal, the
    consumer's locality can be `Any`. If the producer is replicated, we forego the
    request, which occasionally results in fallbacks due to missing plan alternative
    that satisfies all the required properties. In all the other scenarios, we
    request `Non-Singleton`, mirroring the first request to spread out data
    consumption. Note if the producer is a singleton, we always request the consumer
    to be a matching singleton, either on the coordinator or one segment.
    
    As mentioned earlier, query hang arises when dealing with replicated CTEs. In
    response, we modify the first distribution request to be `Non-Singleton
    Non-Replicated`. This request exclusively handles non-replicated input, where
    both the producer and consumer naturally execute across all segments. For
    replicated input, a hash filter is applied to enforce the producer into a random
    distribution, allowing the consumer to still execute across all segments. In the
    second request, we maintain the request for the producer to be `Any` to preserve
    its original property. If the original property is replicated, we request the
    consumer to be replicated as well. This avoids abandoning the optimization
    context.
    
    It's important to clarify that the terms "producer" and "consumer" in the above
    requests pertain to the producer and consumer **sides** of the physical sequence
    operator. The requests don't directly impact the actual producer, i.e., the
    shared scan, or the actual consumer, i.e., consumption of the shared scan.
    Therefore, the updated requests don't eliminate the possibility of a number
    mismatch between the producer and consumer. However, it does significantly
    minimize the likelihood. This is because the plan with the minimal cost is
    unlikely to incorporate motions that alter the locality of the actual producer
    or consumer from that is requested on their respective sides in an asymmetric
    manner that would cause number mismatch.
    
    Interestingly, the physical sequence operator receives a third distribution
    request originating from the CTE consumer. This bottom-up request stands out as
    an exception in ORCA's top-down optimization framework. The methodology is
    documented in this [VLDB paper, section
    7.1](https://www.vldb.org/pvldb/vol8/p1704-elhelw.pdf), with the goal of
    minimizing unnecessary motions, hash redistributionin particular. It was
    implemented by PR 11904. What occurs is the casting of the consumer's derived
    property (post partial optimization) as a request back onto the producer.
    
    Currently, this request does not specifically address a replicated CTE input.
    However, based on the analysis presented earlier, it becomes evident that this
    bottom-up request should not override the producer's duplicate sensitivity.
    Specifically, if the CTE starts as replicated but is then converted to random by
    the hash filter, we should not allow a duplicate-insensitive request to be sent
    to the producer, as it may lead to duplicate hazards and query hang. In this PR,
    we assess the derived duplicate sensitivity of the producer and consumer,
    preventing mismatching requests.
    
    **Tests**
    
    This PR reverts commit b350160, which solved the query hang by intentional fall
    back, while retaining the tests introduced by that commit. Now we no longer
    encounter query hang or fallback.
    
    The MDP test, NLJ-Rewindability-CTAS.mdp, reveals a plan diff in which the
    duplicate sensitive random motion is now pushed down the physical filter. This
    is a conservative maneuver used to handle the scenario where a CTE is inlined by
    a filter. In this context, one CTE is referenced within another CTE, and the
    results of the first CTE contribute to the filtering criteria in the second CTE.
    While the physical filter is unaware of whether it references an inlined CTE,
    the above restriction on replicated distribution eliminates the possibility of
    costly query hangs. Since duplicate sensitive random motion is a non physical
    motion, we don't expect the performance impact to be significant.
---
 .../data/dxl/minidump/NLJ-Rewindability-CTAS.mdp   | 443 +++++++++++----------
 .../dxl/minidump/TaintedReplicatedTablesCTE.mdp    |   2 +-
 .../data/dxl/minidump/cte-duplicate-columns-2.mdp  |   2 +-
 .../libgpopt/include/gpopt/base/CReqdPropPlan.h    |   5 +-
 .../libgpopt/src/base/COptimizationContext.cpp     |   3 +-
 .../gporca/libgpopt/src/base/CReqdPropPlan.cpp     |  67 +++-
 .../libgpopt/src/operators/CPhysicalFilter.cpp     |   2 +-
 .../libgpopt/src/operators/CPhysicalMotion.cpp     |   6 +-
 .../libgpopt/src/operators/CPhysicalSequence.cpp   |  95 +++--
 src/test/regress/expected/bfv_cte.out              | 165 ++++++++
 src/test/regress/expected/bfv_cte_optimizer.out    | 178 +++++++++
 src/test/regress/sql/bfv_cte.sql                   |  83 ++++
 12 files changed, 779 insertions(+), 272 deletions(-)

diff --git a/src/backend/gporca/data/dxl/minidump/NLJ-Rewindability-CTAS.mdp b/src/backend/gporca/data/dxl/minidump/NLJ-Rewindability-CTAS.mdp
index 7b58c03424..65c0b77d16 100644
--- a/src/backend/gporca/data/dxl/minidump/NLJ-Rewindability-CTAS.mdp
+++ b/src/backend/gporca/data/dxl/minidump/NLJ-Rewindability-CTAS.mdp
@@ -443,10 +443,10 @@
         </dxl:LogicalCTEAnchor>
       </dxl:LogicalCTAS>
     </dxl:Query>
-    <dxl:Plan Id="0" SpaceSize="88">
+    <dxl:Plan Id="0" SpaceSize="212">
       <dxl:PhysicalCTAS Name="fake ctas rel" IsTemporary="true" StorageType="Heap" DistributionPolicy="Random" InsertColumns="20" VarTypeModList="-1">
         <dxl:Properties>
-          <dxl:Cost StartupCost="0" TotalCost="1324033.549169" Rows="1.000000" Width="8"/>
+          <dxl:Cost StartupCost="0" TotalCost="1324033.549187" Rows="1.000000" Width="8"/>
         </dxl:Properties>
         <dxl:DistrOpclasses/>
         <dxl:Columns>
@@ -458,76 +458,37 @@
             <dxl:Ident ColId="20" ColName="coalesce" TypeMdid="0.701.1.0"/>
           </dxl:ProjElem>
         </dxl:ProjList>
-        <dxl:Result>
+        <dxl:RandomMotion InputSegments="0,1,2" OutputSegments="0,1,2">
           <dxl:Properties>
-            <dxl:Cost StartupCost="0" TotalCost="1324033.533544" Rows="1.000000" Width="12"/>
+            <dxl:Cost StartupCost="0" TotalCost="1324033.533562" Rows="1.000000" Width="12"/>
           </dxl:Properties>
           <dxl:ProjList>
             <dxl:ProjElem ColId="20" Alias="coalesce">
               <dxl:Ident ColId="20" ColName="coalesce" TypeMdid="0.701.1.0"/>
             </dxl:ProjElem>
             <dxl:ProjElem ColId="38" Alias="ColRef_0038">
-              <dxl:ConstValue TypeMdid="0.23.1.0" Value="1"/>
+              <dxl:Ident ColId="38" ColName="ColRef_0038" TypeMdid="0.23.1.0"/>
             </dxl:ProjElem>
           </dxl:ProjList>
           <dxl:Filter/>
-          <dxl:OneTimeFilter/>
-          <dxl:Sequence>
+          <dxl:SortingColumnList/>
+          <dxl:Result>
             <dxl:Properties>
-              <dxl:Cost StartupCost="0" TotalCost="1324033.533540" Rows="1.000000" Width="8"/>
+              <dxl:Cost StartupCost="0" TotalCost="1324033.533550" Rows="1.000000" Width="12"/>
             </dxl:Properties>
             <dxl:ProjList>
               <dxl:ProjElem ColId="20" Alias="coalesce">
                 <dxl:Ident ColId="20" ColName="coalesce" TypeMdid="0.701.1.0"/>
               </dxl:ProjElem>
+              <dxl:ProjElem ColId="38" Alias="ColRef_0038">
+                <dxl:ConstValue TypeMdid="0.23.1.0" Value="1"/>
+              </dxl:ProjElem>
             </dxl:ProjList>
-            <dxl:CTEProducer CTEId="0" Columns="1,2">
-              <dxl:Properties>
-                <dxl:Cost StartupCost="0" TotalCost="0.000118" Rows="1.000000" Width="1"/>
-              </dxl:Properties>
-              <dxl:ProjList>
-                <dxl:ProjElem ColId="1" Alias="val">
-                  <dxl:Ident ColId="1" ColName="val" TypeMdid="0.25.1.0"/>
-                </dxl:ProjElem>
-                <dxl:ProjElem ColId="2" Alias="wt">
-                  <dxl:Ident ColId="2" ColName="wt" TypeMdid="0.701.1.0"/>
-                </dxl:ProjElem>
-              </dxl:ProjList>
-              <dxl:Result>
-                <dxl:Properties>
-                  <dxl:Cost StartupCost="0" TotalCost="0.000117" Rows="1.000000" Width="16"/>
-                </dxl:Properties>
-                <dxl:ProjList>
-                  <dxl:ProjElem ColId="1" Alias="val">
-                    <dxl:FuncExpr FuncId="0.2331.1.0" FuncRetSet="true" TypeMdid="0.25.1.0" FuncVariadic="false">
-                      <dxl:ConstValue TypeMdid="0.1009.1.0" Value="AAAAqAEAAAAAAAAAGQAAABEAAAABAAAAAAAACXJlcnVtAAAAAAAACXZlbGl0&#10;AAAAAAAABXAAAAAAAAAFcQAAAAAAAAVyAAAAAAAABXMAAAAAAAAFdAAAAAAA&#10;AAZ1bQAAAAAABXYAAAAAAAAFcQAAAAAAAAV3AAAAAAAABWUAAAAAAAAFcgAA&#10;AAAAAAV0AAAAAAAABXkAAAAAAAAFdQAAAAAAAAVpAAAA"/>
-                    </dxl:FuncExpr>
-                  </dxl:ProjElem>
-                  <dxl:ProjElem ColId="2" Alias="wt">
-                    <dxl:FuncExpr FuncId="0.2331.1.0" FuncRetSet="true" TypeMdid="0.701.1.0" FuncVariadic="false">
-                      <dxl:ConstValue TypeMdid="0.1022.1.0" Value="AAAAoAEAAAAAAAAAvQIAABEAAAABAAAAAAAAAAAA8D8AAAAAAADwPwAAAAAA&#10;APA/AAAAAAAA8D8AAAAAAADwPwAAAAAAAPA/AAAAAAAA8D8AAAAAAADwPwAA&#10;AAAAAPA/AAAAAAAA8D8AAAAAAADwPwAAAAAAAPA/AAAAAAAA8D8AAAAAAADw&#10;PwAAAAAAAPA/AAAAAAAA8D8AAAAAAADwPw=="/>
-                    </dxl:FuncExpr>
-                  </dxl:ProjElem>
-                </dxl:ProjList>
-                <dxl:Filter/>
-                <dxl:OneTimeFilter/>
-                <dxl:Result>
-                  <dxl:Properties>
-                    <dxl:Cost StartupCost="0" TotalCost="0.000001" Rows="1.000000" Width="1"/>
-                  </dxl:Properties>
-                  <dxl:ProjList>
-                    <dxl:ProjElem ColId="0" Alias="">
-                      <dxl:ConstValue TypeMdid="0.16.1.0" Value="true"/>
-                    </dxl:ProjElem>
-                  </dxl:ProjList>
-                  <dxl:Filter/>
-                  <dxl:OneTimeFilter/>
-                </dxl:Result>
-              </dxl:Result>
-            </dxl:CTEProducer>
+            <dxl:Filter/>
+            <dxl:OneTimeFilter/>
             <dxl:RandomMotion InputSegments="0" OutputSegments="0,1,2" DuplicateSensitive="true">
               <dxl:Properties>
-                <dxl:Cost StartupCost="0" TotalCost="1324033.533420" Rows="1.000000" Width="8"/>
+                <dxl:Cost StartupCost="0" TotalCost="1324033.533546" Rows="1.000000" Width="8"/>
               </dxl:Properties>
               <dxl:ProjList>
                 <dxl:ProjElem ColId="20" Alias="coalesce">
@@ -536,108 +497,139 @@
               </dxl:ProjList>
               <dxl:Filter/>
               <dxl:SortingColumnList/>
-              <dxl:Result>
+              <dxl:Sequence>
                 <dxl:Properties>
-                  <dxl:Cost StartupCost="0" TotalCost="1324033.533399" Rows="1.000000" Width="8"/>
+                  <dxl:Cost StartupCost="0" TotalCost="1324033.533525" Rows="1.000000" Width="8"/>
                 </dxl:Properties>
                 <dxl:ProjList>
                   <dxl:ProjElem ColId="20" Alias="coalesce">
-                    <dxl:Coalesce TypeMdid="0.701.1.0">
-                      <dxl:OpExpr OperatorName="/" OperatorMdid="0.593.1.0" OperatorType="0.701.1.0">
-                        <dxl:Ident ColId="18" ColName="sum" TypeMdid="0.701.1.0"/>
-                        <dxl:NullIf OperatorMdid="0.670.1.0" TypeMdid="0.701.1.0">
-                          <dxl:Ident ColId="19" ColName="sum" TypeMdid="0.701.1.0"/>
-                          <dxl:ConstValue TypeMdid="0.701.1.0" Value="AAAAAAAAAAA=" DoubleValue="0.000000"/>
-                        </dxl:NullIf>
-                      </dxl:OpExpr>
-                      <dxl:ConstValue TypeMdid="0.701.1.0" Value="AAAAAAAAAAA=" DoubleValue="0.000000"/>
-                    </dxl:Coalesce>
+                    <dxl:Ident ColId="20" ColName="coalesce" TypeMdid="0.701.1.0"/>
                   </dxl:ProjElem>
                 </dxl:ProjList>
-                <dxl:Filter/>
-                <dxl:OneTimeFilter/>
-                <dxl:Aggregate AggregationStrategy="Plain" StreamSafe="false">
+                <dxl:CTEProducer CTEId="0" Columns="1,2">
                   <dxl:Properties>
-                    <dxl:Cost StartupCost="0" TotalCost="1324033.533391" Rows="1.000000" Width="16"/>
+                    <dxl:Cost StartupCost="0" TotalCost="0.000118" Rows="1.000000" Width="1"/>
                   </dxl:Properties>
-                  <dxl:GroupingColumns/>
                   <dxl:ProjList>
-                    <dxl:ProjElem ColId="18" Alias="sum">
-                      <dxl:AggFunc AggMdid="0.2111.1.0" AggDistinct="false" AggStage="Normal" AggKind="n" AggArgTypes="">
-                        <dxl:ValuesList ParamType="aggargs">
-                          <dxl:OpExpr OperatorName="*" OperatorMdid="0.594.1.0" OperatorType="0.701.1.0">
-                            <dxl:If TypeMdid="0.701.1.0">
-                              <dxl:Not>
-                                <dxl:IsDistinctFrom OperatorMdid="0.98.1.0">
-                                  <dxl:Ident ColId="9" ColName="val" TypeMdid="0.25.1.0"/>
-                                  <dxl:Ident ColId="12" ColName="val" TypeMdid="0.25.1.0"/>
-                                </dxl:IsDistinctFrom>
-                              </dxl:Not>
-                              <dxl:Ident ColId="10" ColName="wt" TypeMdid="0.701.1.0"/>
-                              <dxl:ConstValue TypeMdid="0.701.1.0" Value="AAAAAAAAAAA=" DoubleValue="0.000000"/>
-                            </dxl:If>
-                            <dxl:ConstValue TypeMdid="0.701.1.0" Value="AAAAAAAAAEA=" DoubleValue="2.000000"/>
-                          </dxl:OpExpr>
-                        </dxl:ValuesList>
-                        <dxl:ValuesList ParamType="aggdirectargs"/>
-                        <dxl:ValuesList ParamType="aggorder"/>
-                        <dxl:ValuesList ParamType="aggdistinct"/>
-                      </dxl:AggFunc>
+                    <dxl:ProjElem ColId="1" Alias="val">
+                      <dxl:Ident ColId="1" ColName="val" TypeMdid="0.25.1.0"/>
                     </dxl:ProjElem>
-                    <dxl:ProjElem ColId="19" Alias="sum">
-                      <dxl:AggFunc AggMdid="0.2111.1.0" AggDistinct="false" AggStage="Normal" AggKind="n" AggArgTypes="">
-                        <dxl:ValuesList ParamType="aggargs">
-                          <dxl:OpExpr OperatorName="*" OperatorMdid="0.594.1.0" OperatorType="0.701.1.0">
-                            <dxl:Cast TypeMdid="0.701.1.0" FuncId="0.316.1.0">
-                              <dxl:If TypeMdid="0.23.1.0">
-                                <dxl:Not>
-                                  <dxl:IsDistinctFrom OperatorMdid="0.98.1.0">
-                                    <dxl:Ident ColId="9" ColName="val" TypeMdid="0.25.1.0"/>
-                                    <dxl:Ident ColId="12" ColName="val" TypeMdid="0.25.1.0"/>
-                                  </dxl:IsDistinctFrom>
-                                </dxl:Not>
-                                <dxl:ConstValue TypeMdid="0.23.1.0" Value="2"/>
-                                <dxl:ConstValue TypeMdid="0.23.1.0" Value="1"/>
-                              </dxl:If>
-                            </dxl:Cast>
-                            <dxl:Coalesce TypeMdid="0.701.1.0">
-                              <dxl:Ident ColId="10" ColName="wt" TypeMdid="0.701.1.0"/>
-                              <dxl:Ident ColId="14" ColName="w" TypeMdid="0.701.1.0"/>
-                            </dxl:Coalesce>
-                          </dxl:OpExpr>
-                        </dxl:ValuesList>
-                        <dxl:ValuesList ParamType="aggdirectargs"/>
-                        <dxl:ValuesList ParamType="aggorder"/>
-                        <dxl:ValuesList ParamType="aggdistinct"/>
-                      </dxl:AggFunc>
+                    <dxl:ProjElem ColId="2" Alias="wt">
+                      <dxl:Ident ColId="2" ColName="wt" TypeMdid="0.701.1.0"/>
                     </dxl:ProjElem>
                   </dxl:ProjList>
-                  <dxl:Filter/>
-                  <dxl:NestedLoopJoin JoinType="Inner" IndexNestedLoopJoin="false" OuterRefAsParam="false">
+                  <dxl:Result>
                     <dxl:Properties>
-                      <dxl:Cost StartupCost="0" TotalCost="1324033.533305" Rows="3.000000" Width="32"/>
+                      <dxl:Cost StartupCost="0" TotalCost="0.000117" Rows="1.000000" Width="16"/>
                     </dxl:Properties>
                     <dxl:ProjList>
-                      <dxl:ProjElem ColId="9" Alias="val">
-                        <dxl:Ident ColId="9" ColName="val" TypeMdid="0.25.1.0"/>
+                      <dxl:ProjElem ColId="1" Alias="val">
+                        <dxl:FuncExpr FuncId="0.2331.1.0" FuncRetSet="true" TypeMdid="0.25.1.0" FuncVariadic="false">
+                          <dxl:ConstValue TypeMdid="0.1009.1.0" Value="AAAAqAEAAAAAAAAAGQAAABEAAAABAAAAAAAACXJlcnVtAAAAAAAACXZlbGl0&#xA;AAAAAAAABXAAAAAAAAAFcQAAAAAAAAVyAAAAAAAABXMAAAAAAAAFdAAAAAAA&#xA;AAZ1bQAAAAAABXYAAAAAAAAFcQAAAAAAAAV3AAAAAAAABWUAAAAAAAAFcgAA&#xA;AAAAAAV0AAAAAAAABXkAAAAAAAAFdQAAAAAAAAVpAAAA"/>
+                        </dxl:FuncExpr>
                       </dxl:ProjElem>
-                      <dxl:ProjElem ColId="10" Alias="wt">
-                        <dxl:Ident ColId="10" ColName="wt" TypeMdid="0.701.1.0"/>
+                      <dxl:ProjElem ColId="2" Alias="wt">
+                        <dxl:FuncExpr FuncId="0.2331.1.0" FuncRetSet="true" TypeMdid="0.701.1.0" FuncVariadic="false">
+                          <dxl:ConstValue TypeMdid="0.1022.1.0" Value="AAAAoAEAAAAAAAAAvQIAABEAAAABAAAAAAAAAAAA8D8AAAAAAADwPwAAAAAA&#xA;APA/AAAAAAAA8D8AAAAAAADwPwAAAAAAAPA/AAAAAAAA8D8AAAAAAADwPwAA&#xA;AAAAAPA/AAAAAAAA8D8AAAAAAADwPwAAAAAAAPA/AAAAAAAA8D8AAAAAAADw&#xA;PwAAAAAAAPA/AAAAAAAA8D8AAAAAAADwPw=="/>
+                        </dxl:FuncExpr>
                       </dxl:ProjElem>
-                      <dxl:ProjElem ColId="12" Alias="val">
-                        <dxl:Ident ColId="12" ColName="val" TypeMdid="0.25.1.0"/>
+                    </dxl:ProjList>
+                    <dxl:Filter/>
+                    <dxl:OneTimeFilter/>
+                    <dxl:Result>
+                      <dxl:Properties>
+                        <dxl:Cost StartupCost="0" TotalCost="0.000001" Rows="1.000000" Width="1"/>
+                      </dxl:Properties>
+                      <dxl:ProjList>
+                        <dxl:ProjElem ColId="0" Alias="">
+                          <dxl:ConstValue TypeMdid="0.16.1.0" Value="true"/>
+                        </dxl:ProjElem>
+                      </dxl:ProjList>
+                      <dxl:Filter/>
+                      <dxl:OneTimeFilter/>
+                    </dxl:Result>
+                  </dxl:Result>
+                </dxl:CTEProducer>
+                <dxl:Result>
+                  <dxl:Properties>
+                    <dxl:Cost StartupCost="0" TotalCost="1324033.533399" Rows="1.000000" Width="8"/>
+                  </dxl:Properties>
+                  <dxl:ProjList>
+                    <dxl:ProjElem ColId="20" Alias="coalesce">
+                      <dxl:Coalesce TypeMdid="0.701.1.0">
+                        <dxl:OpExpr OperatorName="/" OperatorMdid="0.593.1.0" OperatorType="0.701.1.0">
+                          <dxl:Ident ColId="18" ColName="sum" TypeMdid="0.701.1.0"/>
+                          <dxl:NullIf OperatorMdid="0.670.1.0" TypeMdid="0.701.1.0">
+                            <dxl:Ident ColId="19" ColName="sum" TypeMdid="0.701.1.0"/>
+                            <dxl:ConstValue TypeMdid="0.701.1.0" Value="AAAAAAAAAAA=" DoubleValue="0.000000"/>
+                          </dxl:NullIf>
+                        </dxl:OpExpr>
+                        <dxl:ConstValue TypeMdid="0.701.1.0" Value="AAAAAAAAAAA=" DoubleValue="0.000000"/>
+                      </dxl:Coalesce>
+                    </dxl:ProjElem>
+                  </dxl:ProjList>
+                  <dxl:Filter/>
+                  <dxl:OneTimeFilter/>
+                  <dxl:Aggregate AggregationStrategy="Plain" StreamSafe="false">
+                    <dxl:Properties>
+                      <dxl:Cost StartupCost="0" TotalCost="1324033.533391" Rows="1.000000" Width="16"/>
+                    </dxl:Properties>
+                    <dxl:GroupingColumns/>
+                    <dxl:ProjList>
+                      <dxl:ProjElem ColId="18" Alias="sum">
+                        <dxl:AggFunc AggMdid="0.2111.1.0" AggDistinct="false" AggStage="Normal" AggKind="n" AggArgTypes="">
+                          <dxl:ValuesList ParamType="aggargs">
+                            <dxl:OpExpr OperatorName="*" OperatorMdid="0.594.1.0" OperatorType="0.701.1.0">
+                              <dxl:If TypeMdid="0.701.1.0">
+                                <dxl:Not>
+                                  <dxl:IsDistinctFrom OperatorMdid="0.98.1.0">
+                                    <dxl:Ident ColId="9" ColName="val" TypeMdid="0.25.1.0"/>
+                                    <dxl:Ident ColId="12" ColName="val" TypeMdid="0.25.1.0"/>
+                                  </dxl:IsDistinctFrom>
+                                </dxl:Not>
+                                <dxl:Ident ColId="10" ColName="wt" TypeMdid="0.701.1.0"/>
+                                <dxl:ConstValue TypeMdid="0.701.1.0" Value="AAAAAAAAAAA=" DoubleValue="0.000000"/>
+                              </dxl:If>
+                              <dxl:ConstValue TypeMdid="0.701.1.0" Value="AAAAAAAAAEA=" DoubleValue="2.000000"/>
+                            </dxl:OpExpr>
+                          </dxl:ValuesList>
+                          <dxl:ValuesList ParamType="aggdirectargs"/>
+                          <dxl:ValuesList ParamType="aggorder"/>
+                          <dxl:ValuesList ParamType="aggdistinct"/>
+                        </dxl:AggFunc>
                       </dxl:ProjElem>
-                      <dxl:ProjElem ColId="14" Alias="w">
-                        <dxl:Ident ColId="14" ColName="w" TypeMdid="0.701.1.0"/>
+                      <dxl:ProjElem ColId="19" Alias="sum">
+                        <dxl:AggFunc AggMdid="0.2111.1.0" AggDistinct="false" AggStage="Normal" AggKind="n" AggArgTypes="">
+                          <dxl:ValuesList ParamType="aggargs">
+                            <dxl:OpExpr OperatorName="*" OperatorMdid="0.594.1.0" OperatorType="0.701.1.0">
+                              <dxl:Cast TypeMdid="0.701.1.0" FuncId="0.316.1.0">
+                                <dxl:If TypeMdid="0.23.1.0">
+                                  <dxl:Not>
+                                    <dxl:IsDistinctFrom OperatorMdid="0.98.1.0">
+                                      <dxl:Ident ColId="9" ColName="val" TypeMdid="0.25.1.0"/>
+                                      <dxl:Ident ColId="12" ColName="val" TypeMdid="0.25.1.0"/>
+                                    </dxl:IsDistinctFrom>
+                                  </dxl:Not>
+                                  <dxl:ConstValue TypeMdid="0.23.1.0" Value="2"/>
+                                  <dxl:ConstValue TypeMdid="0.23.1.0" Value="1"/>
+                                </dxl:If>
+                              </dxl:Cast>
+                              <dxl:Coalesce TypeMdid="0.701.1.0">
+                                <dxl:Ident ColId="10" ColName="wt" TypeMdid="0.701.1.0"/>
+                                <dxl:Ident ColId="14" ColName="w" TypeMdid="0.701.1.0"/>
+                              </dxl:Coalesce>
+                            </dxl:OpExpr>
+                          </dxl:ValuesList>
+                          <dxl:ValuesList ParamType="aggdirectargs"/>
+                          <dxl:ValuesList ParamType="aggorder"/>
+                          <dxl:ValuesList ParamType="aggdistinct"/>
+                        </dxl:AggFunc>
                       </dxl:ProjElem>
                     </dxl:ProjList>
                     <dxl:Filter/>
-                    <dxl:JoinFilter>
-                      <dxl:ConstValue TypeMdid="0.16.1.0" Value="true"/>
-                    </dxl:JoinFilter>
-                    <dxl:MergeJoin JoinType="Full" UniqueOuter="false">
+                    <dxl:NestedLoopJoin JoinType="Inner" IndexNestedLoopJoin="false" OuterRefAsParam="false">
                       <dxl:Properties>
-                        <dxl:Cost StartupCost="0" TotalCost="431.001032" Rows="3.000000" Width="24"/>
+                        <dxl:Cost StartupCost="0" TotalCost="1324033.533305" Rows="3.000000" Width="32"/>
                       </dxl:Properties>
                       <dxl:ProjList>
                         <dxl:ProjElem ColId="9" Alias="val">
@@ -649,18 +641,17 @@
                         <dxl:ProjElem ColId="12" Alias="val">
                           <dxl:Ident ColId="12" ColName="val" TypeMdid="0.25.1.0"/>
                         </dxl:ProjElem>
+                        <dxl:ProjElem ColId="14" Alias="w">
+                          <dxl:Ident ColId="14" ColName="w" TypeMdid="0.701.1.0"/>
+                        </dxl:ProjElem>
                       </dxl:ProjList>
                       <dxl:Filter/>
-                      <dxl:JoinFilter/>
-                      <dxl:MergeCondList>
-                        <dxl:Comparison ComparisonOperator="=" OperatorMdid="0.98.1.0">
-                          <dxl:Ident ColId="9" ColName="val" TypeMdid="0.25.1.0"/>
-                          <dxl:Ident ColId="12" ColName="val" TypeMdid="0.25.1.0"/>
-                        </dxl:Comparison>
-                      </dxl:MergeCondList>
-                      <dxl:Sort SortDiscardDuplicates="false">
+                      <dxl:JoinFilter>
+                        <dxl:ConstValue TypeMdid="0.16.1.0" Value="true"/>
+                      </dxl:JoinFilter>
+                      <dxl:MergeJoin JoinType="Full" UniqueOuter="false">
                         <dxl:Properties>
-                          <dxl:Cost StartupCost="0" TotalCost="431.000220" Rows="1.000000" Width="16"/>
+                          <dxl:Cost StartupCost="0" TotalCost="431.001032" Rows="3.000000" Width="24"/>
                         </dxl:Properties>
                         <dxl:ProjList>
                           <dxl:ProjElem ColId="9" Alias="val">
@@ -669,16 +660,21 @@
                           <dxl:ProjElem ColId="10" Alias="wt">
                             <dxl:Ident ColId="10" ColName="wt" TypeMdid="0.701.1.0"/>
                           </dxl:ProjElem>
+                          <dxl:ProjElem ColId="12" Alias="val">
+                            <dxl:Ident ColId="12" ColName="val" TypeMdid="0.25.1.0"/>
+                          </dxl:ProjElem>
                         </dxl:ProjList>
                         <dxl:Filter/>
-                        <dxl:SortingColumnList>
-                          <dxl:SortingColumn ColId="9" SortOperatorMdid="0.664.1.0" SortOperatorName="&lt;" SortNullsFirst="false"/>
-                        </dxl:SortingColumnList>
-                        <dxl:LimitCount/>
-                        <dxl:LimitOffset/>
-                        <dxl:CTEConsumer CTEId="0" Columns="9,10">
+                        <dxl:JoinFilter/>
+                        <dxl:MergeCondList>
+                          <dxl:Comparison ComparisonOperator="=" OperatorMdid="0.98.1.0">
+                            <dxl:Ident ColId="9" ColName="val" TypeMdid="0.25.1.0"/>
+                            <dxl:Ident ColId="12" ColName="val" TypeMdid="0.25.1.0"/>
+                          </dxl:Comparison>
+                        </dxl:MergeCondList>
+                        <dxl:Sort SortDiscardDuplicates="false">
                           <dxl:Properties>
-                            <dxl:Cost StartupCost="0" TotalCost="431.000039" Rows="1.000000" Width="16"/>
+                            <dxl:Cost StartupCost="0" TotalCost="431.000220" Rows="1.000000" Width="16"/>
                           </dxl:Properties>
                           <dxl:ProjList>
                             <dxl:ProjElem ColId="9" Alias="val">
@@ -688,100 +684,119 @@
                               <dxl:Ident ColId="10" ColName="wt" TypeMdid="0.701.1.0"/>
                             </dxl:ProjElem>
                           </dxl:ProjList>
-                        </dxl:CTEConsumer>
-                      </dxl:Sort>
-                      <dxl:Sort SortDiscardDuplicates="false">
-                        <dxl:Properties>
-                          <dxl:Cost StartupCost="0" TotalCost="0.000200" Rows="1.000000" Width="8"/>
-                        </dxl:Properties>
-                        <dxl:ProjList>
-                          <dxl:ProjElem ColId="12" Alias="val">
-                            <dxl:Ident ColId="12" ColName="val" TypeMdid="0.25.1.0"/>
-                          </dxl:ProjElem>
-                        </dxl:ProjList>
-                        <dxl:Filter/>
-                        <dxl:SortingColumnList>
-                          <dxl:SortingColumn ColId="12" SortOperatorMdid="0.664.1.0" SortOperatorName="&lt;" SortNullsFirst="false"/>
-                        </dxl:SortingColumnList>
-                        <dxl:LimitCount/>
-                        <dxl:LimitOffset/>
-                        <dxl:Result>
+                          <dxl:Filter/>
+                          <dxl:SortingColumnList>
+                            <dxl:SortingColumn ColId="9" SortOperatorMdid="0.664.1.0" SortOperatorName="&lt;" SortNullsFirst="false"/>
+                          </dxl:SortingColumnList>
+                          <dxl:LimitCount/>
+                          <dxl:LimitOffset/>
+                          <dxl:CTEConsumer CTEId="0" Columns="9,10">
+                            <dxl:Properties>
+                              <dxl:Cost StartupCost="0" TotalCost="431.000039" Rows="1.000000" Width="16"/>
+                            </dxl:Properties>
+                            <dxl:ProjList>
+                              <dxl:ProjElem ColId="9" Alias="val">
+                                <dxl:Ident ColId="9" ColName="val" TypeMdid="0.25.1.0"/>
+                              </dxl:ProjElem>
+                              <dxl:ProjElem ColId="10" Alias="wt">
+                                <dxl:Ident ColId="10" ColName="wt" TypeMdid="0.701.1.0"/>
+                              </dxl:ProjElem>
+                            </dxl:ProjList>
+                          </dxl:CTEConsumer>
+                        </dxl:Sort>
+                        <dxl:Sort SortDiscardDuplicates="false">
                           <dxl:Properties>
-                            <dxl:Cost StartupCost="0" TotalCost="0.000109" Rows="1.000000" Width="8"/>
+                            <dxl:Cost StartupCost="0" TotalCost="0.000200" Rows="1.000000" Width="8"/>
                           </dxl:Properties>
                           <dxl:ProjList>
                             <dxl:ProjElem ColId="12" Alias="val">
-                              <dxl:FuncExpr FuncId="0.2331.1.0" FuncRetSet="true" TypeMdid="0.25.1.0" FuncVariadic="false">
-                                <dxl:ConstValue TypeMdid="0.1009.1.0" Value="AAAAZAEAAAAAAAAAGQAAAAkAAAABAAAAAAAACG9kaW8AAAAKYmVhdGFlAAAA&#10;AAAFYQAAAAAAAAViAAAAAAAABWMAAAAAAAAFZAAAAAAAAAVlAAAAAAAABWYA&#10;AAAAAAAFZwAAAA=="/>
-                              </dxl:FuncExpr>
+                              <dxl:Ident ColId="12" ColName="val" TypeMdid="0.25.1.0"/>
                             </dxl:ProjElem>
                           </dxl:ProjList>
                           <dxl:Filter/>
-                          <dxl:OneTimeFilter/>
+                          <dxl:SortingColumnList>
+                            <dxl:SortingColumn ColId="12" SortOperatorMdid="0.664.1.0" SortOperatorName="&lt;" SortNullsFirst="false"/>
+                          </dxl:SortingColumnList>
+                          <dxl:LimitCount/>
+                          <dxl:LimitOffset/>
                           <dxl:Result>
                             <dxl:Properties>
-                              <dxl:Cost StartupCost="0" TotalCost="0.000001" Rows="1.000000" Width="1"/>
+                              <dxl:Cost StartupCost="0" TotalCost="0.000109" Rows="1.000000" Width="8"/>
                             </dxl:Properties>
                             <dxl:ProjList>
-                              <dxl:ProjElem ColId="13" Alias="">
-                                <dxl:ConstValue TypeMdid="0.16.1.0" Value="true"/>
+                              <dxl:ProjElem ColId="12" Alias="val">
+                                <dxl:FuncExpr FuncId="0.2331.1.0" FuncRetSet="true" TypeMdid="0.25.1.0" FuncVariadic="false">
+                                  <dxl:ConstValue TypeMdid="0.1009.1.0" Value="AAAAZAEAAAAAAAAAGQAAAAkAAAABAAAAAAAACG9kaW8AAAAKYmVhdGFlAAAA&#xA;AAAFYQAAAAAAAAViAAAAAAAABWMAAAAAAAAFZAAAAAAAAAVlAAAAAAAABWYA&#xA;AAAAAAAFZwAAAA=="/>
+                                </dxl:FuncExpr>
                               </dxl:ProjElem>
                             </dxl:ProjList>
                             <dxl:Filter/>
                             <dxl:OneTimeFilter/>
+                            <dxl:Result>
+                              <dxl:Properties>
+                                <dxl:Cost StartupCost="0" TotalCost="0.000001" Rows="1.000000" Width="1"/>
+                              </dxl:Properties>
+                              <dxl:ProjList>
+                                <dxl:ProjElem ColId="13" Alias="">
+                                  <dxl:ConstValue TypeMdid="0.16.1.0" Value="true"/>
+                                </dxl:ProjElem>
+                              </dxl:ProjList>
+                              <dxl:Filter/>
+                              <dxl:OneTimeFilter/>
+                            </dxl:Result>
                           </dxl:Result>
-                        </dxl:Result>
-                      </dxl:Sort>
-                    </dxl:MergeJoin>
-                    <dxl:Materialize Eager="false">
-                      <dxl:Properties>
-                        <dxl:Cost StartupCost="0" TotalCost="431.000028" Rows="1.000000" Width="8"/>
-                      </dxl:Properties>
-                      <dxl:ProjList>
-                        <dxl:ProjElem ColId="14" Alias="w">
-                          <dxl:Ident ColId="14" ColName="w" TypeMdid="0.701.1.0"/>
-                        </dxl:ProjElem>
-                      </dxl:ProjList>
-                      <dxl:Filter/>
-                      <dxl:Aggregate AggregationStrategy="Plain" StreamSafe="false">
+                        </dxl:Sort>
+                      </dxl:MergeJoin>
+                      <dxl:Materialize Eager="false">
                         <dxl:Properties>
-                          <dxl:Cost StartupCost="0" TotalCost="431.000020" Rows="1.000000" Width="8"/>
+                          <dxl:Cost StartupCost="0" TotalCost="431.000028" Rows="1.000000" Width="8"/>
                         </dxl:Properties>
-                        <dxl:GroupingColumns/>
                         <dxl:ProjList>
                           <dxl:ProjElem ColId="14" Alias="w">
-                            <dxl:AggFunc AggMdid="0.2105.1.0" AggDistinct="false" AggStage="Normal" AggKind="n" AggArgTypes="701">
-                              <dxl:ValuesList ParamType="aggargs">
-                                <dxl:Ident ColId="16" ColName="wt" TypeMdid="0.701.1.0"/>
-                              </dxl:ValuesList>
-                              <dxl:ValuesList ParamType="aggdirectargs"/>
-                              <dxl:ValuesList ParamType="aggorder"/>
-                              <dxl:ValuesList ParamType="aggdistinct"/>
-                            </dxl:AggFunc>
+                            <dxl:Ident ColId="14" ColName="w" TypeMdid="0.701.1.0"/>
                           </dxl:ProjElem>
                         </dxl:ProjList>
                         <dxl:Filter/>
-                        <dxl:CTEConsumer CTEId="0" Columns="15,16">
+                        <dxl:Aggregate AggregationStrategy="Plain" StreamSafe="false">
                           <dxl:Properties>
-                            <dxl:Cost StartupCost="0" TotalCost="431.000019" Rows="1.000000" Width="8"/>
+                            <dxl:Cost StartupCost="0" TotalCost="431.000020" Rows="1.000000" Width="8"/>
                           </dxl:Properties>
+                          <dxl:GroupingColumns/>
                           <dxl:ProjList>
-                            <dxl:ProjElem ColId="15" Alias="val">
-                              <dxl:Ident ColId="15" ColName="val" TypeMdid="0.25.1.0"/>
-                            </dxl:ProjElem>
-                            <dxl:ProjElem ColId="16" Alias="wt">
-                              <dxl:Ident ColId="16" ColName="wt" TypeMdid="0.701.1.0"/>
+                            <dxl:ProjElem ColId="14" Alias="w">
+                              <dxl:AggFunc AggMdid="0.2105.1.0" AggDistinct="false" AggStage="Normal" AggKind="n" AggArgTypes="701">
+                                <dxl:ValuesList ParamType="aggargs">
+                                  <dxl:Ident ColId="16" ColName="wt" TypeMdid="0.701.1.0"/>
+                                </dxl:ValuesList>
+                                <dxl:ValuesList ParamType="aggdirectargs"/>
+                                <dxl:ValuesList ParamType="aggorder"/>
+                                <dxl:ValuesList ParamType="aggdistinct"/>
+                              </dxl:AggFunc>
                             </dxl:ProjElem>
                           </dxl:ProjList>
-                        </dxl:CTEConsumer>
-                      </dxl:Aggregate>
-                    </dxl:Materialize>
-                  </dxl:NestedLoopJoin>
-                </dxl:Aggregate>
-              </dxl:Result>
+                          <dxl:Filter/>
+                          <dxl:CTEConsumer CTEId="0" Columns="15,16">
+                            <dxl:Properties>
+                              <dxl:Cost StartupCost="0" TotalCost="431.000019" Rows="1.000000" Width="8"/>
+                            </dxl:Properties>
+                            <dxl:ProjList>
+                              <dxl:ProjElem ColId="15" Alias="val">
+                                <dxl:Ident ColId="15" ColName="val" TypeMdid="0.25.1.0"/>
+                              </dxl:ProjElem>
+                              <dxl:ProjElem ColId="16" Alias="wt">
+                                <dxl:Ident ColId="16" ColName="wt" TypeMdid="0.701.1.0"/>
+                              </dxl:ProjElem>
+                            </dxl:ProjList>
+                          </dxl:CTEConsumer>
+                        </dxl:Aggregate>
+                      </dxl:Materialize>
+                    </dxl:NestedLoopJoin>
+                  </dxl:Aggregate>
+                </dxl:Result>
+              </dxl:Sequence>
             </dxl:RandomMotion>
-          </dxl:Sequence>
-        </dxl:Result>
+          </dxl:Result>
+        </dxl:RandomMotion>
       </dxl:PhysicalCTAS>
     </dxl:Plan>
   </dxl:Thread>
diff --git a/src/backend/gporca/data/dxl/minidump/TaintedReplicatedTablesCTE.mdp b/src/backend/gporca/data/dxl/minidump/TaintedReplicatedTablesCTE.mdp
index e7f605dd4d..55d07b2a11 100644
--- a/src/backend/gporca/data/dxl/minidump/TaintedReplicatedTablesCTE.mdp
+++ b/src/backend/gporca/data/dxl/minidump/TaintedReplicatedTablesCTE.mdp
@@ -350,7 +350,7 @@
         </dxl:LogicalCTEAnchor>
       </dxl:LogicalCTEAnchor>
     </dxl:Query>
-    <dxl:Plan Id="0" SpaceSize="70">
+    <dxl:Plan Id="0" SpaceSize="82">
       <dxl:Sequence>
         <dxl:Properties>
           <dxl:Cost StartupCost="0" TotalCost="1293.001221" Rows="2.000000" Width="24"/>
diff --git a/src/backend/gporca/data/dxl/minidump/cte-duplicate-columns-2.mdp b/src/backend/gporca/data/dxl/minidump/cte-duplicate-columns-2.mdp
index e3bf7fb602..6f375b8e47 100644
--- a/src/backend/gporca/data/dxl/minidump/cte-duplicate-columns-2.mdp
+++ b/src/backend/gporca/data/dxl/minidump/cte-duplicate-columns-2.mdp
@@ -235,7 +235,7 @@ where x1.b = x2.b;
         </dxl:LogicalJoin>
       </dxl:LogicalCTEAnchor>
     </dxl:Query>
-    <dxl:Plan Id="0" SpaceSize="20">
+    <dxl:Plan Id="0" SpaceSize="28">
       <dxl:Sequence>
         <dxl:Properties>
           <dxl:Cost StartupCost="0" TotalCost="1293.001043" Rows="1.000000" Width="24"/>
diff --git a/src/backend/gporca/libgpopt/include/gpopt/base/CReqdPropPlan.h b/src/backend/gporca/libgpopt/include/gpopt/base/CReqdPropPlan.h
index 2fea31f6cd..f9e683ca61 100644
--- a/src/backend/gporca/libgpopt/include/gpopt/base/CReqdPropPlan.h
+++ b/src/backend/gporca/libgpopt/include/gpopt/base/CReqdPropPlan.h
@@ -185,8 +185,9 @@ public:
 
 	// map input required and derived plan properties into new required plan properties
 	static CReqdPropPlan *PrppRemapForCTE(CMemoryPool *mp,
-										  CReqdPropPlan *prppInput,
-										  CDrvdPropPlan *pdpplanInput,
+										  CReqdPropPlan *prppProducer,
+										  CDrvdPropPlan *pdpplanProducer,
+										  CDrvdPropPlan *pdpplanConsumer,
 										  UlongToColRefMap *colref_mapping);
 
 	// print function
diff --git a/src/backend/gporca/libgpopt/src/base/COptimizationContext.cpp b/src/backend/gporca/libgpopt/src/base/COptimizationContext.cpp
index 603a834584..954dd96a8c 100644
--- a/src/backend/gporca/libgpopt/src/base/COptimizationContext.cpp
+++ b/src/backend/gporca/libgpopt/src/base/COptimizationContext.cpp
@@ -417,7 +417,8 @@ COptimizationContext::PrppCTEProducer(CMemoryPool *mp,
 			mp, popProducer->UlCTEId(), pcrsInnerOutput,
 			popProducer->Pdrgpcr());
 	CReqdPropPlan *prppProducer = CReqdPropPlan::PrppRemapForCTE(
-		mp, pocProducer->Prpp(), pccConsumer->Pdpplan(), colref_mapping);
+		mp, pocProducer->Prpp(), pccProducer->Pdpplan(), pccConsumer->Pdpplan(),
+		colref_mapping);
 	colref_mapping->Release();
 
 	if (prppProducer->Equals(pocProducer->Prpp()))
diff --git a/src/backend/gporca/libgpopt/src/base/CReqdPropPlan.cpp b/src/backend/gporca/libgpopt/src/base/CReqdPropPlan.cpp
index df19aecf59..f0cfa78c6a 100644
--- a/src/backend/gporca/libgpopt/src/base/CReqdPropPlan.cpp
+++ b/src/backend/gporca/libgpopt/src/base/CReqdPropPlan.cpp
@@ -526,20 +526,29 @@ CReqdPropPlan::FEqualForCostBounding(const CReqdPropPlan *prppFst,
 //
 //---------------------------------------------------------------------------
 CReqdPropPlan *
-CReqdPropPlan::PrppRemapForCTE(CMemoryPool *mp, CReqdPropPlan *prppInput,
-							   CDrvdPropPlan *pdpplanInput,
+CReqdPropPlan::PrppRemapForCTE(CMemoryPool *mp, CReqdPropPlan *prppProducer,
+							   CDrvdPropPlan *pdpplanProducer,
+							   CDrvdPropPlan *pdpplanConsumer,
 							   UlongToColRefMap *colref_mapping)
 {
 	GPOS_ASSERT(nullptr != colref_mapping);
-	GPOS_ASSERT(nullptr != prppInput);
-	GPOS_ASSERT(nullptr != pdpplanInput);
+	GPOS_ASSERT(nullptr != prppProducer);
+	GPOS_ASSERT(nullptr != pdpplanProducer);
+	GPOS_ASSERT(nullptr != pdpplanConsumer);
 
 	// Remap derived sort order to a required sort order.
-	COrderSpec *pos = pdpplanInput->Pos()->PosCopyWithRemappedColumns(
+	COrderSpec *pos = pdpplanConsumer->Pos()->PosCopyWithRemappedColumns(
 		mp, colref_mapping, false /*must_exist*/);
-	CEnfdOrder *peo = GPOS_NEW(mp) CEnfdOrder(pos, prppInput->Peo()->Eom());
-
-	// Remap derived distribution only if it can be used as required distribution.
+	CEnfdOrder *peo = GPOS_NEW(mp) CEnfdOrder(pos, prppProducer->Peo()->Eom());
+
+	// Remap derived distribution only if:
+	// (1) it can be used as required distribution, and
+	// (2) either the producer's derived distribution spec isn't duplicate
+	// sensitive, or, the consumer's derived distribution spec is duplicate
+	// sensitive. This is to ensure we don't accidentally raise a duplicate
+	// insensitive request when it's supposed to be duplicate sensitive,
+	// such as when the input is replicated.
+	//
 	// Also, fix distribution specs with equivalent columns, since those may come
 	// from different consumers and NOT be equivalent in the producer.
 	// For example:
@@ -548,35 +557,49 @@ CReqdPropPlan::PrppRemapForCTE(CMemoryPool *mp, CReqdPropPlan *prppInput,
 	// On the query side, columns x1.a and x2.b are equivalent, but we should NOT
 	// treat columns a and b of the producer as equivalent.
 
-	CDistributionSpec *pdsDerived = pdpplanInput->Pds();
+	CDistributionSpec *pdsDerivedProducer = pdpplanProducer->Pds();
+	CDistributionSpec *pdsDerivedConsumer = pdpplanConsumer->Pds();
+
+	BOOL producer_duplicate_sensitive =
+		CDistributionSpec::EdtRandom == pdsDerivedProducer->Edt()
+			? CDistributionSpecRandom::PdsConvert(pdsDerivedProducer)
+				  ->IsDuplicateSensitive()
+			: false;
+	BOOL consumer_duplicate_sensitive =
+		CDistributionSpec::EdtRandom == pdsDerivedConsumer->Edt()
+			? CDistributionSpecRandom::PdsConvert(pdsDerivedConsumer)
+				  ->IsDuplicateSensitive()
+			: false;
 	CEnfdDistribution *ped = nullptr;
-	if (pdsDerived->FRequirable())
+	if (pdsDerivedConsumer->FRequirable() &&
+		(!producer_duplicate_sensitive || consumer_duplicate_sensitive))
 	{
-		CDistributionSpec *pdsNoEquiv = pdsDerived->StripEquivColumns(mp);
+		CDistributionSpec *pdsNoEquiv =
+			pdsDerivedConsumer->StripEquivColumns(mp);
 		CDistributionSpec *pds = pdsNoEquiv->PdsCopyWithRemappedColumns(
 			mp, colref_mapping, false /*must_exist*/);
-		ped = GPOS_NEW(mp) CEnfdDistribution(pds, prppInput->Ped()->Edm());
+		ped = GPOS_NEW(mp) CEnfdDistribution(pds, prppProducer->Ped()->Edm());
 		pdsNoEquiv->Release();
 	}
 	else
 	{
-		prppInput->Ped()->AddRef();
-		ped = prppInput->Ped();
+		prppProducer->Ped()->AddRef();
+		ped = prppProducer->Ped();
 	}
 
 	// other properties are copied from input
 
-	prppInput->PcrsRequired()->AddRef();
-	CColRefSet *pcrsRequired = prppInput->PcrsRequired();
+	prppProducer->PcrsRequired()->AddRef();
+	CColRefSet *pcrsRequired = prppProducer->PcrsRequired();
 
-	prppInput->Per()->AddRef();
-	CEnfdRewindability *per = prppInput->Per();
+	prppProducer->Per()->AddRef();
+	CEnfdRewindability *per = prppProducer->Per();
 
-	prppInput->Pepp()->AddRef();
-	CEnfdPartitionPropagation *pepp = prppInput->Pepp();
+	prppProducer->Pepp()->AddRef();
+	CEnfdPartitionPropagation *pepp = prppProducer->Pepp();
 
-	prppInput->Pcter()->AddRef();
-	CCTEReq *pcter = prppInput->Pcter();
+	prppProducer->Pcter()->AddRef();
+	CCTEReq *pcter = prppProducer->Pcter();
 
 	return GPOS_NEW(mp) CReqdPropPlan(pcrsRequired, peo, ped, per, pepp, pcter);
 }
diff --git a/src/backend/gporca/libgpopt/src/operators/CPhysicalFilter.cpp b/src/backend/gporca/libgpopt/src/operators/CPhysicalFilter.cpp
index 4865236e0e..58eae30313 100644
--- a/src/backend/gporca/libgpopt/src/operators/CPhysicalFilter.cpp
+++ b/src/backend/gporca/libgpopt/src/operators/CPhysicalFilter.cpp
@@ -129,7 +129,7 @@ CPhysicalFilter::PdsRequired(CMemoryPool *mp, CExpressionHandle &exprhdl,
 		!CDistributionSpecNonSingleton::PdsConvert(pdsRequired)
 			 ->FAllowReplicated())
 	{
-		// this situation arises when we have Filter instead inlined CTE,
+		// this situation arises when we have Filter inside inlined CTE,
 		// in this case, we need to push down non-singleton with not allowed replicated through Filter
 		pdsRequired->AddRef();
 		return pdsRequired;
diff --git a/src/backend/gporca/libgpopt/src/operators/CPhysicalMotion.cpp b/src/backend/gporca/libgpopt/src/operators/CPhysicalMotion.cpp
index 602ae36115..8aab252965 100644
--- a/src/backend/gporca/libgpopt/src/operators/CPhysicalMotion.cpp
+++ b/src/backend/gporca/libgpopt/src/operators/CPhysicalMotion.cpp
@@ -83,6 +83,7 @@ CPhysicalMotion::PdsRequired(CMemoryPool *mp,
 {
 	GPOS_ASSERT(0 == child_index);
 
+	// scenario 1:
 	// if the required distribution is EdtStrictRandom, it indicates
 	// that this motion operator was enforced into the same
 	// group as that of CPhysicalComputeScalar just below CPhysicalDML(Insert)
@@ -131,10 +132,11 @@ CPhysicalMotion::PdsRequired(CMemoryPool *mp,
 	//	      +--CScalarProjectList
 	//	         +--CScalarProjectElement "ColRef_0008"
 	//	            +--CScalarConst (1)
+	// scenario 2:
+	// CTE consumer's derived property is EdtStrictRandom. It's
+	// sent back to the physical sequence.
 	if (CDistributionSpec::EdtStrictRandom == pdsRequired->Edt())
 	{
-		GPOS_ASSERT(COperator::EopPhysicalMotionRandom == Eopid());
-		GPOS_ASSERT(CDistributionSpec::EdtStrictRandom == Pds()->Edt());
 		return GPOS_NEW(mp) CDistributionSpecRandom();
 	}
 
diff --git a/src/backend/gporca/libgpopt/src/operators/CPhysicalSequence.cpp b/src/backend/gporca/libgpopt/src/operators/CPhysicalSequence.cpp
index 1657bcf480..5dd12b8711 100644
--- a/src/backend/gporca/libgpopt/src/operators/CPhysicalSequence.cpp
+++ b/src/backend/gporca/libgpopt/src/operators/CPhysicalSequence.cpp
@@ -16,6 +16,7 @@
 #include "gpopt/base/CCTEReq.h"
 #include "gpopt/base/CDistributionSpecAny.h"
 #include "gpopt/base/CDistributionSpecNonSingleton.h"
+#include "gpopt/base/CDistributionSpecReplicated.h"
 #include "gpopt/base/CDistributionSpecSingleton.h"
 #include "gpopt/base/COptCtxt.h"
 #include "gpopt/operators/CExpressionHandle.h"
@@ -35,15 +36,18 @@ CPhysicalSequence::CPhysicalSequence(CMemoryPool *mp)
 {
 	// Sequence generates two distribution requests for its children:
 	// (1) If incoming distribution from above is Singleton, pass it through
-	//		to all children, otherwise request Non-Singleton on all children
+	//		to all children, otherwise request Non-Singleton (Non-Replicated)
+	//		on all children
 	//
 	// (2)	Optimize first child with Any distribution requirement, and compute
 	//		distribution request on other children based on derived distribution
 	//		of first child:
 	//			* If distribution of first child is a Singleton, request Singleton
-	//				on all children
-	//			* If distribution of first child is a Non-Singleton, request
-	//				Non-Singleton on all children
+	//				on the second child
+	//			* If distribution of first child is Replicated, request Replicated
+	//				on the second child
+	//			* Otherwise, request Non-Singleton (Non-Replicated) on the second
+	//				child
 
 	SetDistrRequests(2);
 
@@ -188,65 +192,100 @@ CPhysicalSequence::PdsRequired(CMemoryPool *mp,
 	GPOS_ASSERT(child_index < exprhdl.Arity());
 	GPOS_ASSERT(ulOptReq < UlDistrRequests());
 
+	// The following distribution requests ensure matching number of
+	// producers and consumers. Any mismatch, either more producers
+	// than consumers, or more consumers than producers, will cause
+	// the query to hang
+
+	// 1st request
 	if (0 == ulOptReq)
 	{
-		if (CDistributionSpec::EdtSingleton == pdsRequired->Edt() ||
-			CDistributionSpec::EdtStrictSingleton == pdsRequired->Edt())
+		// If the incoming request is a singleton, request singleton
+		// on ALL the children. No need to check strict singleton,
+		// cause strict singleton is a derived only spec.
+		if (CDistributionSpec::EdtSingleton == pdsRequired->Edt())
 		{
-			// incoming request is a singleton, request singleton on all children
 			CDistributionSpecSingleton *pdss =
 				CDistributionSpecSingleton::PdssConvert(pdsRequired);
 			return GPOS_NEW(mp) CDistributionSpecSingleton(pdss->Est());
 		}
 
-		// incoming request is a non-singleton, request non-singleton on all children
-		return GPOS_NEW(mp) CDistributionSpecNonSingleton();
+		// Otherwise, request non-singleton (excluding replicated)
+		// on ALL the children.
+
+		// If the input is hashed or randomly distributed, no motion
+		// will be enforced. Both the producer and the consumer are
+		// executed on all the segments. That is, no mismatch between
+		// the number of producers and consumers.
+
+		// If the input is replicated, a hash filter will be applied
+		// to the input. The hash filter resolves the duplicate hazard,
+		// and now the producer is randomly distributed. The consumer
+		// will continue to be executed on all the segments. Again, no
+		// mismatch between the number of producers and consumers.
+		return GPOS_NEW(mp)
+			CDistributionSpecNonSingleton(false /* fAllowReplicated */);
 	}
+
+	// 2nd request
 	GPOS_ASSERT(1 == ulOptReq);
 
 	if (0 == child_index)
 	{
-		// no distribution requirement on first child
+		// Request Any on the first child
 		return GPOS_NEW(mp) CDistributionSpecAny(this->Eopid());
 	}
 
-	// get derived plan properties of first child
+	// Get derived plan properties of first child
 	CDrvdPropPlan *pdpplan = CDrvdPropPlan::Pdpplan((*pdrgpdpCtxt)[0]);
+	// Because we request Any on the first child, no motion could
+	// have been enforced. The derived spec of the first child is
+	// what it delivers in the first place.
 	CDistributionSpec *pds = pdpplan->Pds();
 
+	// If the first child is singleton, request singleton on the
+	// second child. This request is identical to the 1st request
+	// (ulOptReq = 0), where we request singleton on ALL the
+	// children, and would eventually be deduplicated.
 	if (pds->FSingletonOrStrictSingleton())
 	{
-		// first child is singleton, request singleton distribution on second child
 		CDistributionSpecSingleton *pdss =
 			CDistributionSpecSingleton::PdssConvert(pds);
 		return GPOS_NEW(mp) CDistributionSpecSingleton(pdss->Est());
 	}
 
+	// If the first child is universal, request Any on the second
+	// child. This is because if the producer is universally
+	// available, no matter what the locality of the consumer
+	// is, the consumer would be able to access the shared data
 	if (CDistributionSpec::EdtUniversal == pds->Edt())
 	{
-		// first child is universal, impose no requirements on second child
 		return GPOS_NEW(mp) CDistributionSpecAny(this->Eopid());
 	}
 
-	// If required distribution is singleton on master (sequence is top operation) or
-	// non-singleton with not allowed replicated (there is another top-sequence under this sequence)
-	// then we should not allow replicated distribution, to avoid potential hang issues,
-	// which may accured when ORCA is translating expression to DXL and sets
-	// one segment for input array if strict or tainted replicated distribution detected,
-	// and Redistribute from all segments to one segments appears.
-
-	if ((CDistributionSpec::EdtSingleton == pdsRequired->Edt() &&
-		 CDistributionSpecSingleton::PdssConvert(pdsRequired)->FOnMaster()) ||
-		(CDistributionSpec::EdtNonSingleton == pdsRequired->Edt() &&
-		 !CDistributionSpecNonSingleton::PdsConvert(pdsRequired)
-			  ->FAllowReplicated()))
+	// If the first child is replicated, request replicated on the
+	// second child. This way, the producer is only executed on one
+	// segment, and so is the consumer. The plan would look like:
+	// Gather Motion 1:1  (slice1; segments: 1)
+	//   ->  Sequence
+	//         ->  Shared Scan (share slice:id 1:1)
+	//               ->  Seq Scan on rep
+	//         ->  Broadcast Motion 3:1  (slice2; segments: 3)
+	//             ...
+	//             ...
+	//             ->  Gather Motion 1:1  (sliceN; segments: 1)
+	//                   ->  Shared Scan (share slice:id N:1)
+	if (CDistributionSpec::EdtStrictReplicated == pds->Edt() ||
+		CDistributionSpec::EdtTaintedReplicated == pds->Edt())
 	{
 		return GPOS_NEW(mp)
-			CDistributionSpecNonSingleton(false /* fAllowReplicated */);
+			CDistributionSpecReplicated(CDistributionSpec::EdtReplicated);
 	}
 
-	// first child is non-singleton, request a non-singleton distribution on second child
-	return GPOS_NEW(mp) CDistributionSpecNonSingleton();
+	// Request non-singleton (excluding replicated) on the second
+	// child
+	return GPOS_NEW(mp)
+		CDistributionSpecNonSingleton(false /* fAllowReplicated */);
 }
 
 
diff --git a/src/test/regress/expected/bfv_cte.out b/src/test/regress/expected/bfv_cte.out
index 925d6d1e49..b050796d4a 100644
--- a/src/test/regress/expected/bfv_cte.out
+++ b/src/test/regress/expected/bfv_cte.out
@@ -487,6 +487,7 @@ select wait_until_query_output_to_file('/tmp/bfv_cte.out');
 -- s/slice=2\): initialized xslice state/slice={2|4}): initialized xslice state/g
 -- m/slice=4\): initialized xslice state/
 -- s/slice=4\): initialized xslice state/slice={2|4}): initialized xslice state/g
+-- s/One-Time Filter: \(gp_execution_segment\(\) =\d+\)/One-Time Filter: \(gp_execution_segment\(\) =#\)/
 -- end_matchsubs
 -- Filter out irrelevant LOG messages from segments other than seg2.
 \! cat /tmp/bfv_cte.out | grep -P '^(?!LOG)|^(LOG.*seg2)' | grep -vP 'LOG.*fault|decreased xslice state refcount'
@@ -521,3 +522,167 @@ RESET enable_parallel;
 DROP TABLE sisc_t1;
 DROP TABLE sisc_t2;
 \! rm /tmp/bfv_cte.out
+-- The following tests ensures queries involving CTE don't hang in ORCA or fall back.
+-- Query hang is caused by a mismatch between the number of CTE producers and
+-- consumers. Either there are more producers than consumers, or more consumers than
+-- producers, the unconsumed producers or the starved consumers would cause the query
+-- to hang. Query fall back, on the other hand, is due to a missing plan that satisfies
+-- all the required plan properties. The issues stated above are fixed by sending
+-- appropriate distribution requests based on how data is distributed to begin with,
+-- to ensure the number of CTE producers and consumers match. For each of the tests,
+-- we use explain to verify the plan and the actual query to verify the query doesn't
+-- hang.
+-- This test involves a tainted-replicated CTE and two consumer 
+-- To ensure there's no duplicate hazard, the cost model chooses a plan that gathers
+-- the CTE onto the coordinator. Both the producer and the consumer executes on the
+-- coordiator. There's 1 producer matching 1 consumer.
+drop table if exists rep;
+NOTICE:  table "rep" does not exist, skipping
+create table rep (i character varying(10)) distributed replicated;
+explain (analyze off, costs off, verbose off)
+with cte1 as ( select *,row_number() over ( partition by i) as rank_desc from rep),
+cte2 as ( select 'col1' tblnm,count(*) diffcnt from ( select * from cte1) x)
+select * from ( select 'col1' tblnm from cte1) a left join cte2 c on a.tblnm=c.tblnm;
+                                                       QUERY PLAN                                                       
+-----------------------------------------------------------------
+ Hash Left Join
+   Hash Cond: (('col1'::text) = ('col1'::text))
+   ->  Gather Motion 1:1  (slice1; segments: 1)
+         ->  Subquery Scan on cte1
+               ->  WindowAgg
+                     Partition By: rep.i
+                     ->  Sort
+                           Sort Key: rep.i
+                           ->  Seq Scan on rep
+   ->  Hash
+         ->  Aggregate
+               ->  Gather Motion 1:1  (slice2; segments: 1)
+                     ->  Subquery Scan on cte1_1
+                           ->  WindowAgg
+                                 Partition By: rep_1.i
+                                 ->  Sort
+                                       Sort Key: rep_1.i
+                                       ->  Seq Scan on rep rep_1
+ Optimizer: Postgres-based planner
+(19 rows)
+
+with cte1 as ( select *,row_number() over ( partition by i) as rank_desc from rep),
+     cte2 as ( select 'col1' tblnm,count(*) diffcnt from ( select * from cte1) x)
+select * from ( select 'col1' tblnm from cte1) a left join cte2 c on a.tblnm=c.tblnm;
+ tblnm | tblnm | diffcnt 
+-------+-------+---------
+(0 rows)
+
+drop table rep;
+-- This test involves a tainted-replicated CTE and two consumers
+-- To ensure there's no duplicate hazard, the cost model chooses a plan that executes
+-- the producer and both consumers on one segment, before gathering the data onto
+-- the coordinator and return. There's 1 producer matching 1 consumer for each consumer.
+drop table if exists rep1, rep2;
+NOTICE:  table "rep1" does not exist, skipping
+NOTICE:  table "rep2" does not exist, skipping
+create table rep1 (id bigserial not null, isc varchar(15) not null,iscd varchar(15) null) distributed replicated;
+create table rep2 (id numeric null, rc varchar(255) null,ri numeric null) distributed replicated;
+insert into rep1 (isc,iscd) values ('cmn_bin_yes', 'cmn_bin_yes');
+insert into rep2 (id,rc,ri) values (113551,'cmn_bin_yes',101991), (113552,'cmn_bin_no',101991), (113553,'cmn_bin_err',101991), (113554,'cmn_bin_null',101991);
+explain (analyze off, costs off, verbose off)
+with
+    t1 as (select * from rep1),
+    t2 as (select id, rc from rep2 where ri = 101991)
+select p.*from t1 p join t2 r on p.isc = r.rc join t2 r1 on p.iscd = r1.rc;
+                                            QUERY PLAN                                             
+------------------------------------------------------------------
+ Gather Motion 1:1  (slice1; segments: 1)
+   ->  Hash Join
+         Hash Cond: ((rep1.iscd)::text = (r1.rc)::text)
+         ->  Hash Join
+               Hash Cond: ((rep1.isc)::text = (r.rc)::text)
+               ->  Seq Scan on rep1
+               ->  Hash
+                     ->  Subquery Scan on r
+                           ->  Seq Scan on rep2
+                                 Filter: (ri = '101991'::numeric)
+         ->  Hash
+               ->  Subquery Scan on r1
+                     ->  Seq Scan on rep2 rep2_1
+                           Filter: (ri = '101991'::numeric)
+ Optimizer: Postgres-based planner
+(15 rows)
+
+with
+    t1 as (select * from rep1),
+    t2 as (select id, rc from rep2 where ri = 101991)
+select p.*from t1 p join t2 r on p.isc = r.rc join t2 r1 on p.iscd = r1.rc limit 1;
+ id |     isc     |    iscd     
+----+-------------+-------------
+  1 | cmn_bin_yes | cmn_bin_yes
+(1 row)
+
+drop table rep1, rep2;
+-- This test involves a strictly replicated CTE and two consumers
+-- To ensure there's no duplicate hazard, the cost model chooses a plan that executes
+-- the producer and both consumers on one segment by placing a one-time segment filter
+-- on the producer side. The coordinator gathers data from all three segments, even 
+-- though only one segment has the tuples from the join due to the one-time filter. 
+-- There's 1 producer matching 1 consumer for each consumer.
+drop table if exists t1, t2, rep;
+NOTICE:  table "t1" does not exist, skipping
+NOTICE:  table "t2" does not exist, skipping
+NOTICE:  table "rep" does not exist, skipping
+create table t1 (a int, b int);
+create table t2 (a int, b int);
+create table rep (a int, b int) distributed replicated;
+insert into t1 select 1, generate_series(1,10);
+insert into t2 select 1, generate_series(1,20);
+insert into rep select 1, 1;
+explain (analyze off, costs off, verbose off)
+with t1_cte as (select b from t1),
+rep_cte as (select a from rep)
+select
+case when (t2.b in (1,2)) then (select rep_cte.a from rep_cte)
+when (t2.b in (1,2)) then (select rep_cte.a from rep_cte)
+end as rep_cte_a
+from t1_cte join t2 on t1_cte.b = t2.b;
+                                                QUERY PLAN                                                
+------------------------------------------------------------------
+ Gather Motion 3:1  (slice1; segments: 3)
+   InitPlan 1 (returns $0)  (slice4)
+     ->  Gather Motion 1:1  (slice5; segments: 1)
+           ->  Seq Scan on rep
+   InitPlan 2 (returns $1)  (slice6)
+     ->  Gather Motion 1:1  (slice7; segments: 1)
+           ->  Seq Scan on rep rep_1
+   ->  Hash Join
+         Hash Cond: (t1.b = t2.b)
+         ->  Redistribute Motion 3:3  (slice2; segments: 3)
+               Hash Key: t1.b
+               ->  Seq Scan on t1
+         ->  Hash
+               ->  Redistribute Motion 3:3  (slice3; segments: 3)
+                     Hash Key: t2.b
+                     ->  Seq Scan on t2
+ Optimizer: Postgres-based planner
+(17 rows)
+
+with t1_cte as (select b from t1),
+rep_cte as (select a from rep)
+select
+case when (t2.b in (1,2)) then (select rep_cte.a from rep_cte)
+when (t2.b in (1,2)) then (select rep_cte.a from rep_cte)
+end as rep_cte_a
+from t1_cte join t2 on t1_cte.b = t2.b;
+ rep_cte_a 
+-----------
+         1
+          
+          
+          
+          
+         1
+          
+          
+          
+          
+(10 rows)
+
+drop table t1, t2, rep;
diff --git a/src/test/regress/expected/bfv_cte_optimizer.out b/src/test/regress/expected/bfv_cte_optimizer.out
index a178d208dd..dc7c1d75e1 100644
--- a/src/test/regress/expected/bfv_cte_optimizer.out
+++ b/src/test/regress/expected/bfv_cte_optimizer.out
@@ -489,6 +489,7 @@ select wait_until_query_output_to_file('/tmp/bfv_cte.out');
 -- s/slice=2\): initialized xslice state/slice={2|4}): initialized xslice state/g
 -- m/slice=4\): initialized xslice state/
 -- s/slice=4\): initialized xslice state/slice={2|4}): initialized xslice state/g
+-- s/One-Time Filter: \(gp_execution_segment\(\) =\d+\)/One-Time Filter: \(gp_execution_segment\(\) =#\)/
 -- end_matchsubs
 -- Filter out irrelevant LOG messages from segments other than seg2.
 \! cat /tmp/bfv_cte.out | grep -P '^(?!LOG)|^(LOG.*seg2)' | grep -vP 'LOG.*fault|decreased xslice state refcount'
@@ -523,3 +524,180 @@ RESET enable_parallel;
 DROP TABLE sisc_t1;
 DROP TABLE sisc_t2;
 \! rm /tmp/bfv_cte.out
+-- The following tests ensures queries involving CTE don't hang in ORCA or fall back.
+-- Query hang is caused by a mismatch between the number of CTE producers and
+-- consumers. Either there are more producers than consumers, or more consumers than
+-- producers, the unconsumed producers or the starved consumers would cause the query
+-- to hang. Query fall back, on the other hand, is due to a missing plan that satisfies
+-- all the required plan properties. The issues stated above are fixed by sending
+-- appropriate distribution requests based on how data is distributed to begin with,
+-- to ensure the number of CTE producers and consumers match. For each of the tests,
+-- we use explain to verify the plan and the actual query to verify the query doesn't
+-- hang.
+-- This test involves a tainted-replicated CTE and two consumer 
+-- To ensure there's no duplicate hazard, the cost model chooses a plan that gathers
+-- the CTE onto the coordinator. Both the producer and the consumer executes on the
+-- coordiator. There's 1 producer matching 1 consumer.
+drop table if exists rep;
+NOTICE:  table "rep" does not exist, skipping
+create table rep (i character varying(10)) distributed replicated;
+explain (analyze off, costs off, verbose off)
+with cte1 as ( select *,row_number() over ( partition by i) as rank_desc from rep),
+cte2 as ( select 'col1' tblnm,count(*) diffcnt from ( select * from cte1) x)
+select * from ( select 'col1' tblnm from cte1) a left join cte2 c on a.tblnm=c.tblnm;
+                                             QUERY PLAN                                             
+----------------------------------------------------------------
+ Sequence
+   ->  Shared Scan (share slice:id 0:0)
+         ->  Gather Motion 1:1  (slice1; segments: 1)
+               ->  WindowAgg
+                     Partition By: rep.i
+                     ->  Sort
+                           Sort Key: rep.i
+                           ->  Seq Scan on rep
+   ->  Hash Left Join
+         Hash Cond: (('col1'::text) = ('col1'::text))
+         ->  Result
+               ->  Shared Scan (share slice:id 0:0)
+         ->  Hash
+               ->  Result
+                     Filter: (('col1'::text) = 'col1'::text)
+                     ->  Aggregate
+                           ->  Shared Scan (share slice:id 0:0)
+ Optimizer: GPORCA
+(18 rows)
+
+with cte1 as ( select *,row_number() over ( partition by i) as rank_desc from rep),
+     cte2 as ( select 'col1' tblnm,count(*) diffcnt from ( select * from cte1) x)
+select * from ( select 'col1' tblnm from cte1) a left join cte2 c on a.tblnm=c.tblnm;
+ tblnm | tblnm | diffcnt 
+-------+-------+---------
+(0 rows)
+
+drop table rep;
+-- This test involves a tainted-replicated CTE and two consumers
+-- To ensure there's no duplicate hazard, the cost model chooses a plan that executes
+-- the producer and both consumers on one segment, before gathering the data onto
+-- the coordinator and return. There's 1 producer matching 1 consumer for each consumer.
+drop table if exists rep1, rep2;
+NOTICE:  table "rep1" does not exist, skipping
+NOTICE:  table "rep2" does not exist, skipping
+create table rep1 (id bigserial not null, isc varchar(15) not null,iscd varchar(15) null) distributed replicated;
+create table rep2 (id numeric null, rc varchar(255) null,ri numeric null) distributed replicated;
+insert into rep1 (isc,iscd) values ('cmn_bin_yes', 'cmn_bin_yes');
+insert into rep2 (id,rc,ri) values (113551,'cmn_bin_yes',101991), (113552,'cmn_bin_no',101991), (113553,'cmn_bin_err',101991), (113554,'cmn_bin_null',101991);
+explain (analyze off, costs off, verbose off)
+with
+    t1 as (select * from rep1),
+    t2 as (select id, rc from rep2 where ri = 101991)
+select p.*from t1 p join t2 r on p.isc = r.rc join t2 r1 on p.iscd = r1.rc;
+                                             QUERY PLAN                                             
+-----------------------------------------------------------------------------
+ Gather Motion 1:1  (slice1; segments: 1)
+   ->  Sequence
+         ->  Shared Scan (share slice:id 1:1)
+               ->  Seq Scan on rep2
+                     Filter: (ri = '101991'::numeric)
+         ->  Hash Join
+               Hash Cond: ((rep1.isc)::text = (share1_ref2.rc)::text)
+               ->  Hash Join
+                     Hash Cond: ((rep1.iscd)::text = (share1_ref3.rc)::text)
+                     ->  Seq Scan on rep1
+                     ->  Hash
+                           ->  Shared Scan (share slice:id 1:1)
+               ->  Hash
+                     ->  Shared Scan (share slice:id 1:1)
+ Optimizer: GPORCA
+(15 rows)
+
+with
+    t1 as (select * from rep1),
+    t2 as (select id, rc from rep2 where ri = 101991)
+select p.*from t1 p join t2 r on p.isc = r.rc join t2 r1 on p.iscd = r1.rc limit 1;
+ id |     isc     |    iscd     
+----+-------------+-------------
+  1 | cmn_bin_yes | cmn_bin_yes
+(1 row)
+
+drop table rep1, rep2;
+-- This test involves a strictly replicated CTE and two consumers
+-- To ensure there's no duplicate hazard, the cost model chooses a plan that executes
+-- the producer and both consumers on one segment by placing a one-time segment filter
+-- on the producer side. The coordinator gathers data from all three segments, even 
+-- though only one segment has the tuples from the join due to the one-time filter. 
+-- There's 1 producer matching 1 consumer for each consumer.
+drop table if exists t1, t2, rep;
+NOTICE:  table "t1" does not exist, skipping
+NOTICE:  table "t2" does not exist, skipping
+NOTICE:  table "rep" does not exist, skipping
+create table t1 (a int, b int);
+create table t2 (a int, b int);
+create table rep (a int, b int) distributed replicated;
+insert into t1 select 1, generate_series(1,10);
+insert into t2 select 1, generate_series(1,20);
+insert into rep select 1, 1;
+explain (analyze off, costs off, verbose off)
+with t1_cte as (select b from t1),
+rep_cte as (select a from rep)
+select
+case when (t2.b in (1,2)) then (select rep_cte.a from rep_cte)
+when (t2.b in (1,2)) then (select rep_cte.a from rep_cte)
+end as rep_cte_a
+from t1_cte join t2 on t1_cte.b = t2.b;
+                                                          QUERY PLAN                                                          
+------------------------------------------------------------------------------------------
+ Gather Motion 3:1  (slice1; segments: 3)
+   ->  Sequence
+         ->  Shared Scan (share slice:id 1:1)
+               ->  Result
+                     One-Time Filter: (gp_execution_segment() = 1)
+                     ->  Seq Scan on rep
+         ->  Nested Loop Left Join
+               Join Filter: true
+               ->  Nested Loop Left Join
+                     Join Filter: true
+                     ->  Hash Join
+                           Hash Cond: (t1.b = t2.b)
+                           ->  Seq Scan on t1
+                           ->  Hash
+                                 ->  Broadcast Motion 3:3  (slice6; segments: 3)
+                                       ->  Seq Scan on t2
+                     ->  Assert
+                           Assert Cond: ((row_number() OVER (?)) = 1)
+                           ->  Materialize
+                                 ->  Broadcast Motion 1:3  (slice4)
+                                       ->  WindowAgg
+                                             ->  Gather Motion 3:1  (slice5; segments: 3)
+                                                   ->  Shared Scan (share slice:id 5:1)
+               ->  Assert
+                     Assert Cond: ((row_number() OVER (?)) = 1)
+                     ->  Materialize
+                           ->  Broadcast Motion 1:3  (slice2)
+                                 ->  WindowAgg
+                                       ->  Gather Motion 3:1  (slice3; segments: 3)
+                                             ->  Shared Scan (share slice:id 3:1)
+ Optimizer: GPORCA
+(31 rows)
+
+with t1_cte as (select b from t1),
+rep_cte as (select a from rep)
+select
+case when (t2.b in (1,2)) then (select rep_cte.a from rep_cte)
+when (t2.b in (1,2)) then (select rep_cte.a from rep_cte)
+end as rep_cte_a
+from t1_cte join t2 on t1_cte.b = t2.b;
+ rep_cte_a 
+-----------
+         1
+         1
+          
+          
+          
+          
+          
+          
+          
+          
+(10 rows)
+
+drop table t1, t2, rep;
diff --git a/src/test/regress/sql/bfv_cte.sql b/src/test/regress/sql/bfv_cte.sql
index 02b1895e8a..537579784f 100644
--- a/src/test/regress/sql/bfv_cte.sql
+++ b/src/test/regress/sql/bfv_cte.sql
@@ -323,6 +323,7 @@ select wait_until_query_output_to_file('/tmp/bfv_cte.out');
 -- s/slice=2\): initialized xslice state/slice={2|4}): initialized xslice state/g
 -- m/slice=4\): initialized xslice state/
 -- s/slice=4\): initialized xslice state/slice={2|4}): initialized xslice state/g
+-- s/One-Time Filter: \(gp_execution_segment\(\) =\d+\)/One-Time Filter: \(gp_execution_segment\(\) =#\)/
 -- end_matchsubs
 -- Filter out irrelevant LOG messages from segments other than seg2.
 \! cat /tmp/bfv_cte.out | grep -P '^(?!LOG)|^(LOG.*seg2)' | grep -vP 'LOG.*fault|decreased xslice state refcount'
@@ -334,3 +335,85 @@ RESET enable_parallel;
 DROP TABLE sisc_t1;
 DROP TABLE sisc_t2;
 \! rm /tmp/bfv_cte.out
+
+-- The following tests ensures queries involving CTE don't hang in ORCA or fall back.
+-- Query hang is caused by a mismatch between the number of CTE producers and
+-- consumers. Either there are more producers than consumers, or more consumers than
+-- producers, the unconsumed producers or the starved consumers would cause the query
+-- to hang. Query fall back, on the other hand, is due to a missing plan that satisfies
+-- all the required plan properties. The issues stated above are fixed by sending
+-- appropriate distribution requests based on how data is distributed to begin with,
+-- to ensure the number of CTE producers and consumers match. For each of the tests,
+-- we use explain to verify the plan and the actual query to verify the query doesn't
+-- hang.
+
+-- This test involves a tainted-replicated CTE and two consumer 
+-- To ensure there's no duplicate hazard, the cost model chooses a plan that gathers
+-- the CTE onto the coordinator. Both the producer and the consumer executes on the
+-- coordiator. There's 1 producer matching 1 consumer.
+drop table if exists rep;
+create table rep (i character varying(10)) distributed replicated;
+
+explain (analyze off, costs off, verbose off)
+with cte1 as ( select *,row_number() over ( partition by i) as rank_desc from rep),
+cte2 as ( select 'col1' tblnm,count(*) diffcnt from ( select * from cte1) x)
+select * from ( select 'col1' tblnm from cte1) a left join cte2 c on a.tblnm=c.tblnm;
+
+with cte1 as ( select *,row_number() over ( partition by i) as rank_desc from rep),
+     cte2 as ( select 'col1' tblnm,count(*) diffcnt from ( select * from cte1) x)
+select * from ( select 'col1' tblnm from cte1) a left join cte2 c on a.tblnm=c.tblnm;
+drop table rep;
+
+-- This test involves a tainted-replicated CTE and two consumers
+-- To ensure there's no duplicate hazard, the cost model chooses a plan that executes
+-- the producer and both consumers on one segment, before gathering the data onto
+-- the coordinator and return. There's 1 producer matching 1 consumer for each consumer.
+drop table if exists rep1, rep2;
+create table rep1 (id bigserial not null, isc varchar(15) not null,iscd varchar(15) null) distributed replicated;
+create table rep2 (id numeric null, rc varchar(255) null,ri numeric null) distributed replicated;
+insert into rep1 (isc,iscd) values ('cmn_bin_yes', 'cmn_bin_yes');
+insert into rep2 (id,rc,ri) values (113551,'cmn_bin_yes',101991), (113552,'cmn_bin_no',101991), (113553,'cmn_bin_err',101991), (113554,'cmn_bin_null',101991);
+explain (analyze off, costs off, verbose off)
+with
+    t1 as (select * from rep1),
+    t2 as (select id, rc from rep2 where ri = 101991)
+select p.*from t1 p join t2 r on p.isc = r.rc join t2 r1 on p.iscd = r1.rc;
+
+with
+    t1 as (select * from rep1),
+    t2 as (select id, rc from rep2 where ri = 101991)
+select p.*from t1 p join t2 r on p.isc = r.rc join t2 r1 on p.iscd = r1.rc limit 1;
+drop table rep1, rep2;
+
+-- This test involves a strictly replicated CTE and two consumers
+-- To ensure there's no duplicate hazard, the cost model chooses a plan that executes
+-- the producer and both consumers on one segment by placing a one-time segment filter
+-- on the producer side. The coordinator gathers data from all three segments, even 
+-- though only one segment has the tuples from the join due to the one-time filter. 
+-- There's 1 producer matching 1 consumer for each consumer.
+drop table if exists t1, t2, rep;
+create table t1 (a int, b int);
+create table t2 (a int, b int);
+create table rep (a int, b int) distributed replicated;
+insert into t1 select 1, generate_series(1,10);
+insert into t2 select 1, generate_series(1,20);
+insert into rep select 1, 1;
+
+explain (analyze off, costs off, verbose off)
+with t1_cte as (select b from t1),
+rep_cte as (select a from rep)
+select
+case when (t2.b in (1,2)) then (select rep_cte.a from rep_cte)
+when (t2.b in (1,2)) then (select rep_cte.a from rep_cte)
+end as rep_cte_a
+from t1_cte join t2 on t1_cte.b = t2.b;
+
+with t1_cte as (select b from t1),
+rep_cte as (select a from rep)
+select
+case when (t2.b in (1,2)) then (select rep_cte.a from rep_cte)
+when (t2.b in (1,2)) then (select rep_cte.a from rep_cte)
+end as rep_cte_a
+from t1_cte join t2 on t1_cte.b = t2.b;
+drop table t1, t2, rep;
+


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org