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 544c11e876c6df41a1d84a8b050bebe76968d733 Author: Ekta Khanna <[email protected]> AuthorDate: Mon Oct 17 18:15:41 2022 -0700 Fix query preprocessor for nested Select-Project-NaryJoin Prior to this commit, for nested Select-Project-NAryJoin, the CollapseSelectAndReplaceColref called from PexprTransposeSelectAndProject would replace all the nested combinations with Select. But since the Projection is later added inside PexprTransposeSelectAndProject, only the topmost combination would get transposed, which would lead to dropping of some projected columns and later crash when deriving stats for these dropped columns. Below is an example of the query tree: ``` Nested Select-Project-NaryJoin Input: +--CLogicalSelect (x = 'meh') +--CLogicalProject (col1...n, expr as x) +-- CLogicalNAryJoin +--CLogicalSelect (y = 'xxx') +--CLogicalProject (col1...n, expr as y) +-- CLogicalNAryJoin Current Output of PexprTransposeSelectAndProject step: +--CLogicalProject (col1..n, expr as x) +--CLogicalSelect (expr = 'meh') +-- CLogicalNAryJoin +--CLogicalSelect (expr = 'xxx') +-- CLogicalNAryJoin Expected Output of PexprTransposeSelectAndProject step: +--CLogicalProject (col1..n, expr as x) +--CLogicalSelect (expr = 'meh') +-- CLogicalNAryJoin +--CLogicalProject (col1..n, expr as y) +--CLogicalSelect (expr = 'xxx') +-- CLogicalNAryJoin ``` As part of this PR, once the Logical Select is generated the function would recurse into PexprTransposeSelectAndProject to ensure that none of the ProjectElements are dropped. --- .../dxl/minidump/NestedJoinWithCastedColumn.mdp | 947 +++++++++++++++++++++ .../gpopt/operators/CExpressionPreprocessor.h | 5 + .../src/operators/CExpressionPreprocessor.cpp | 23 +- src/backend/gporca/server/CMakeLists.txt | 2 +- 4 files changed, 971 insertions(+), 6 deletions(-) diff --git a/src/backend/gporca/data/dxl/minidump/NestedJoinWithCastedColumn.mdp b/src/backend/gporca/data/dxl/minidump/NestedJoinWithCastedColumn.mdp new file mode 100644 index 0000000000..250d39d8ad --- /dev/null +++ b/src/backend/gporca/data/dxl/minidump/NestedJoinWithCastedColumn.mdp @@ -0,0 +1,947 @@ +<?xml version="1.0" encoding="UTF-8"?> +<dxl:DXLMessage xmlns:dxl="http://greenplum.com/dxl/2010/12/";> + <dxl:Comment><![CDATA[ + Objective: Ensure that nested join on a with a cast'd column doesn't + drop the projected column + while doing a predicate push down as part of the + PexprTransposeSelectAndProject preprocessing step. + + Setup: + CREATE TABLE t1(join_id int, select_id int); + CREATE TABLE t2(join_id int, select_id int); + CREATE TABLE t3(select_id int); + CREATE TABLE t5(id int, id1 int, name varchar, value double precision); + INSERT INTO t1 SELECT i,i from generate_series(1,10)i; + INSERT INTO t2 SELECT i,i from generate_series(1,10)i; + INSERT INTO t3 SELECT i from generate_series(1,10)i; + + EXPLAIN SELECT * FROM + ( + SELECT 'raw' AS name1, value::text AS l + FROM + (SELECT t2.select_id, 'something' AS name + FROM t1 JOIN t2 on t1.join_id = t2.join_id + UNION + SELECT t3.select_id, 'something_else' AS name + FROM t3 + )tt1 + JOIN t5 ON id1::text = tt1.select_id::text AND tt1.name::text = t5.name::text + )tt3 WHERE name1::text = 'raw'; + QUERY PLAN + ------------------------------------------------------------------------------------------------------------------------------------------ + Result (cost=0.00..1724.00 rows=1 width=16) + -> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1724.00 rows=1 width=8) + -> Hash Join (cost=0.00..1724.00 rows=1 width=8) + Hash Cond: (((t5.id1)::text = (t2.select_id)::text) AND ((t5.name)::text = ('something'::text))) + -> Redistribute Motion 3:3 (slice2; segments: 3) (cost=0.00..431.00 rows=1 width=20) + Hash Key: (t5.id1)::text + -> Seq Scan on t5 (cost=0.00..431.00 rows=1 width=20) + Filter: (name = ANY ('{something,something_else}'::character varying[])) + -> Hash (cost=1293.00..1293.00 rows=1 width=12) + -> Redistribute Motion 3:3 (slice3; segments: 3) (cost=0.00..1293.00 rows=1 width=12) + Hash Key: (t2.select_id)::text + -> GroupAggregate (cost=0.00..1293.00 rows=1 width=12) + Group Key: t2.select_id, ('something'::text) + -> Sort (cost=0.00..1293.00 rows=1 width=12) + Sort Key: t2.select_id, ('something'::text) + -> Redistribute Motion 3:3 (slice4; segments: 3) (cost=0.00..1293.00 rows=1 width=12) + Hash Key: t2.select_id, ('something'::text) + -> Append (cost=0.00..1293.00 rows=1 width=12) + -> Hash Join (cost=0.00..862.00 rows=1 width=4) + Hash Cond: (t2.join_id = t1.join_id) + -> Seq Scan on t2 (cost=0.00..431.00 rows=1 width=8) + -> Hash (cost=431.00..431.00 rows=1 width=4) + -> Seq Scan on t1 (cost=0.00..431.00 rows=1 width=4) + Filter: (('something'::text = ANY ('{something,something_else}'::text[])) AND ('raw'::text = 'raw'::text)) + -> Result (cost=0.00..431.00 rows=1 width=12) + Filter: (('something_else'::text) = ANY ('{something,something_else}'::text[])) + -> Seq Scan on t3 (cost=0.00..431.00 rows=1 width=4) + Filter: ('raw'::text = 'raw'::text) + Optimizer: Pivotal Optimizer (GPORCA) + ]]> + </dxl:Comment> + <dxl:Thread Id="0"> + <dxl:OptimizerConfig> + <dxl:EnumeratorConfig Id="0" PlanSamples="0" CostThreshold="0"/> + <dxl:StatisticsConfig DampingFactorFilter="0.750000" DampingFactorJoin="0.000000" DampingFactorGroupBy="0.750000" MaxStatsBuckets="100"/> + <dxl:CTEConfig CTEInliningCutoff="0"/> + <dxl:WindowOids RowNumber="3100" Rank="3101"/> + <dxl:CostModelConfig CostModelType="1" SegmentsForCosting="3"> + <dxl:CostParams> + <dxl:CostParam Name="NLJFactor" Value="1024.000000" LowerBound="1023.500000" UpperBound="1024.500000"/> + </dxl:CostParams> + </dxl:CostModelConfig> + <dxl:Hint MinNumOfPartsToRequireSortOnInsert="2147483647" JoinArityForAssociativityCommutativity="18" ArrayExpansionThreshold="20" JoinOrderDynamicProgThreshold="10" BroadcastThreshold="100000" EnforceConstraintsOnDML="false" PushGroupByBelowSetopThreshold="10" XformBindThreshold="0"/> + <dxl:TraceFlags Value="102001,102002,102003,102043,102074,102120,102144,103001,103014,103022,103026,103027,103029,103033,103038,103040,104002,104003,104004,104005,105000,106000"/> + </dxl:OptimizerConfig> + <dxl:Metadata SystemIds="0.GPDB"> + <dxl:ColumnStatistics Mdid="1.139329.1.0.1" Name="id1" Width="4.000000" NullFreq="0.000000" NdvRemain="0.000000" FreqRemain="0.000000" ColStatsMissing="true"/> + <dxl:ColumnStatistics Mdid="1.139329.1.0.0" Name="id" Width="4.000000" NullFreq="0.000000" NdvRemain="0.000000" FreqRemain="0.000000" ColStatsMissing="true"/> + <dxl:Type Mdid="0.16.1.0" Name="bool" IsRedistributable="true" IsHashable="true" IsMergeJoinable="true" IsComposite="false" IsTextRelated="false" IsFixedLength="true" Length="1" PassByValue="true"> + <dxl:DistrOpfamily Mdid="0.2222.1.0"/> + <dxl:LegacyDistrOpfamily Mdid="0.7124.1.0"/> + <dxl:EqualityOp Mdid="0.91.1.0"/> + <dxl:InequalityOp Mdid="0.85.1.0"/> + <dxl:LessThanOp Mdid="0.58.1.0"/> + <dxl:LessThanEqualsOp Mdid="0.1694.1.0"/> + <dxl:GreaterThanOp Mdid="0.59.1.0"/> + <dxl:GreaterThanEqualsOp Mdid="0.1695.1.0"/> + <dxl:ComparisonOp Mdid="0.1693.1.0"/> + <dxl:ArrayType Mdid="0.1000.1.0"/> + <dxl:MinAgg Mdid="0.0.0.0"/> + <dxl:MaxAgg Mdid="0.0.0.0"/> + <dxl:AvgAgg Mdid="0.0.0.0"/> + <dxl:SumAgg Mdid="0.0.0.0"/> + <dxl:CountAgg Mdid="0.2147.1.0"/> + </dxl:Type> + <dxl:Type Mdid="0.1043.1.0" Name="varchar" IsRedistributable="true" IsHashable="true" IsMergeJoinable="true" IsComposite="false" IsTextRelated="true" IsFixedLength="false" Length="-1" PassByValue="false"> + <dxl:DistrOpfamily Mdid="0.1995.1.0"/> + <dxl:LegacyDistrOpfamily Mdid="0.7105.1.0"/> + <dxl:EqualityOp Mdid="0.98.1.0"/> + <dxl:InequalityOp Mdid="0.531.1.0"/> + <dxl:LessThanOp Mdid="0.664.1.0"/> + <dxl:LessThanEqualsOp Mdid="0.665.1.0"/> + <dxl:GreaterThanOp Mdid="0.666.1.0"/> + <dxl:GreaterThanEqualsOp Mdid="0.667.1.0"/> + <dxl:ComparisonOp Mdid="0.360.1.0"/> + <dxl:ArrayType Mdid="0.1015.1.0"/> + <dxl:MinAgg Mdid="0.0.0.0"/> + <dxl:MaxAgg Mdid="0.0.0.0"/> + <dxl:AvgAgg Mdid="0.0.0.0"/> + <dxl:SumAgg Mdid="0.0.0.0"/> + <dxl:CountAgg Mdid="0.2147.1.0"/> + </dxl:Type> + <dxl:Type Mdid="0.23.1.0" Name="int4" IsRedistributable="true" IsHashable="true" IsMergeJoinable="true" IsComposite="false" IsTextRelated="false" IsFixedLength="true" Length="4" PassByValue="true"> + <dxl:DistrOpfamily Mdid="0.1977.1.0"/> + <dxl:LegacyDistrOpfamily Mdid="0.7100.1.0"/> + <dxl:EqualityOp Mdid="0.96.1.0"/> + <dxl:InequalityOp Mdid="0.518.1.0"/> + <dxl:LessThanOp Mdid="0.97.1.0"/> + <dxl:LessThanEqualsOp Mdid="0.523.1.0"/> + <dxl:GreaterThanOp Mdid="0.521.1.0"/> + <dxl:GreaterThanEqualsOp Mdid="0.525.1.0"/> + <dxl:ComparisonOp Mdid="0.351.1.0"/> + <dxl:ArrayType Mdid="0.1007.1.0"/> + <dxl:MinAgg Mdid="0.2132.1.0"/> + <dxl:MaxAgg Mdid="0.2116.1.0"/> + <dxl:AvgAgg Mdid="0.2101.1.0"/> + <dxl:SumAgg Mdid="0.2108.1.0"/> + <dxl:CountAgg Mdid="0.2147.1.0"/> + </dxl:Type> + <dxl:MDCast Mdid="3.25.1.0;1043.1.0" Name="varchar" BinaryCoercible="true" SourceTypeId="0.25.1.0" DestinationTypeId="0.1043.1.0" CastFuncId="0.0.0.0" CoercePathType="0"/> + <dxl:GPDBScalarOp Mdid="0.664.1.0" Name="<" ComparisonType="LT" ReturnsNullOnNullInput="true" IsNDVPreserving="false"> + <dxl:LeftType Mdid="0.25.1.0"/> + <dxl:RightType Mdid="0.25.1.0"/> + <dxl:ResultType Mdid="0.16.1.0"/> + <dxl:OpFunc Mdid="0.740.1.0"/> + <dxl:Commutator Mdid="0.666.1.0"/> + <dxl:InverseOp Mdid="0.667.1.0"/> + <dxl:Opfamilies> + <dxl:Opfamily Mdid="0.1994.1.0"/> + <dxl:Opfamily Mdid="0.4017.1.0"/> + <dxl:Opfamily Mdid="0.4056.1.0"/> + <dxl:Opfamily Mdid="0.10018.1.0"/> + </dxl:Opfamilies> + </dxl:GPDBScalarOp> + <dxl:Type Mdid="0.25.1.0" Name="text" IsRedistributable="true" IsHashable="true" IsMergeJoinable="true" IsComposite="false" IsTextRelated="true" IsFixedLength="false" Length="-1" PassByValue="false"> + <dxl:DistrOpfamily Mdid="0.1995.1.0"/> + <dxl:LegacyDistrOpfamily Mdid="0.7105.1.0"/> + <dxl:EqualityOp Mdid="0.98.1.0"/> + <dxl:InequalityOp Mdid="0.531.1.0"/> + <dxl:LessThanOp Mdid="0.664.1.0"/> + <dxl:LessThanEqualsOp Mdid="0.665.1.0"/> + <dxl:GreaterThanOp Mdid="0.666.1.0"/> + <dxl:GreaterThanEqualsOp Mdid="0.667.1.0"/> + <dxl:ComparisonOp Mdid="0.360.1.0"/> + <dxl:ArrayType Mdid="0.1009.1.0"/> + <dxl:MinAgg Mdid="0.2145.1.0"/> + <dxl:MaxAgg Mdid="0.2129.1.0"/> + <dxl:AvgAgg Mdid="0.0.0.0"/> + <dxl:SumAgg Mdid="0.0.0.0"/> + <dxl:CountAgg Mdid="0.2147.1.0"/> + </dxl:Type> + <dxl:GPDBScalarOp Mdid="0.666.1.0" Name=">" ComparisonType="GT" ReturnsNullOnNullInput="true" IsNDVPreserving="false"> + <dxl:LeftType Mdid="0.25.1.0"/> + <dxl:RightType Mdid="0.25.1.0"/> + <dxl:ResultType Mdid="0.16.1.0"/> + <dxl:OpFunc Mdid="0.742.1.0"/> + <dxl:Commutator Mdid="0.664.1.0"/> + <dxl:InverseOp Mdid="0.665.1.0"/> + <dxl:Opfamilies> + <dxl:Opfamily Mdid="0.1994.1.0"/> + <dxl:Opfamily Mdid="0.4017.1.0"/> + <dxl:Opfamily Mdid="0.4056.1.0"/> + <dxl:Opfamily Mdid="0.10018.1.0"/> + </dxl:Opfamilies> + </dxl:GPDBScalarOp> + <dxl:Type Mdid="0.26.1.0" Name="oid" IsRedistributable="true" IsHashable="true" IsMergeJoinable="true" IsComposite="false" IsTextRelated="false" IsFixedLength="true" Length="4" PassByValue="true"> + <dxl:DistrOpfamily Mdid="0.1990.1.0"/> + <dxl:LegacyDistrOpfamily Mdid="0.7109.1.0"/> + <dxl:EqualityOp Mdid="0.607.1.0"/> + <dxl:InequalityOp Mdid="0.608.1.0"/> + <dxl:LessThanOp Mdid="0.609.1.0"/> + <dxl:LessThanEqualsOp Mdid="0.611.1.0"/> + <dxl:GreaterThanOp Mdid="0.610.1.0"/> + <dxl:GreaterThanEqualsOp Mdid="0.612.1.0"/> + <dxl:ComparisonOp Mdid="0.356.1.0"/> + <dxl:ArrayType Mdid="0.1028.1.0"/> + <dxl:MinAgg Mdid="0.2134.1.0"/> + <dxl:MaxAgg Mdid="0.2118.1.0"/> + <dxl:AvgAgg Mdid="0.0.0.0"/> + <dxl:SumAgg Mdid="0.0.0.0"/> + <dxl:CountAgg Mdid="0.2147.1.0"/> + </dxl:Type> + <dxl:Type Mdid="0.27.1.0" Name="tid" IsRedistributable="true" IsHashable="true" IsMergeJoinable="true" IsComposite="false" IsTextRelated="false" IsFixedLength="true" Length="6" PassByValue="false"> + <dxl:DistrOpfamily Mdid="0.2227.1.0"/> + <dxl:LegacyDistrOpfamily Mdid="0.7110.1.0"/> + <dxl:EqualityOp Mdid="0.387.1.0"/> + <dxl:InequalityOp Mdid="0.402.1.0"/> + <dxl:LessThanOp Mdid="0.2799.1.0"/> + <dxl:LessThanEqualsOp Mdid="0.2801.1.0"/> + <dxl:GreaterThanOp Mdid="0.2800.1.0"/> + <dxl:GreaterThanEqualsOp Mdid="0.2802.1.0"/> + <dxl:ComparisonOp Mdid="0.2794.1.0"/> + <dxl:ArrayType Mdid="0.1010.1.0"/> + <dxl:MinAgg Mdid="0.2798.1.0"/> + <dxl:MaxAgg Mdid="0.2797.1.0"/> + <dxl:AvgAgg Mdid="0.0.0.0"/> + <dxl:SumAgg Mdid="0.0.0.0"/> + <dxl:CountAgg Mdid="0.2147.1.0"/> + </dxl:Type> + <dxl:Type Mdid="0.29.1.0" Name="cid" IsRedistributable="true" IsHashable="true" IsMergeJoinable="false" IsComposite="false" IsTextRelated="false" IsFixedLength="true" Length="4" PassByValue="true"> + <dxl:DistrOpfamily Mdid="0.2226.1.0"/> + <dxl:EqualityOp Mdid="0.385.1.0"/> + <dxl:InequalityOp Mdid="0.0.0.0"/> + <dxl:LessThanOp Mdid="0.0.0.0"/> + <dxl:LessThanEqualsOp Mdid="0.0.0.0"/> + <dxl:GreaterThanOp Mdid="0.0.0.0"/> + <dxl:GreaterThanEqualsOp Mdid="0.0.0.0"/> + <dxl:ComparisonOp Mdid="0.0.0.0"/> + <dxl:ArrayType Mdid="0.1012.1.0"/> + <dxl:MinAgg Mdid="0.0.0.0"/> + <dxl:MaxAgg Mdid="0.0.0.0"/> + <dxl:AvgAgg Mdid="0.0.0.0"/> + <dxl:SumAgg Mdid="0.0.0.0"/> + <dxl:CountAgg Mdid="0.2147.1.0"/> + </dxl:Type> + <dxl:Type Mdid="0.28.1.0" Name="xid" IsRedistributable="true" IsHashable="true" IsMergeJoinable="false" IsComposite="false" IsTextRelated="false" IsFixedLength="true" Length="4" PassByValue="true"> + <dxl:DistrOpfamily Mdid="0.2225.1.0"/> + <dxl:EqualityOp Mdid="0.352.1.0"/> + <dxl:InequalityOp Mdid="0.3315.1.0"/> + <dxl:LessThanOp Mdid="0.0.0.0"/> + <dxl:LessThanEqualsOp Mdid="0.0.0.0"/> + <dxl:GreaterThanOp Mdid="0.0.0.0"/> + <dxl:GreaterThanEqualsOp Mdid="0.0.0.0"/> + <dxl:ComparisonOp Mdid="0.0.0.0"/> + <dxl:ArrayType Mdid="0.1011.1.0"/> + <dxl:MinAgg Mdid="0.0.0.0"/> + <dxl:MaxAgg Mdid="0.0.0.0"/> + <dxl:AvgAgg Mdid="0.0.0.0"/> + <dxl:SumAgg Mdid="0.0.0.0"/> + <dxl:CountAgg Mdid="0.2147.1.0"/> + </dxl:Type> + <dxl:ColumnStatistics Mdid="1.139329.1.0.3" Name="value" Width="8.000000" NullFreq="0.000000" NdvRemain="0.000000" FreqRemain="0.000000" ColStatsMissing="true"/> + <dxl:ColumnStatistics Mdid="1.139329.1.0.2" Name="name" Width="8.000000" NullFreq="0.000000" NdvRemain="0.000000" FreqRemain="0.000000" ColStatsMissing="true"/> + <dxl:RelationStatistics Mdid="2.139320.1.0" Name="t1" Rows="0.000000" RelPages="0" RelAllVisible="0" EmptyRelation="true"/> + <dxl:MDCast Mdid="3.25.1.0;25.1.0" Name="text" BinaryCoercible="true" SourceTypeId="0.25.1.0" DestinationTypeId="0.25.1.0" CastFuncId="0.0.0.0" CoercePathType="0"/> + <dxl:Relation Mdid="0.139320.1.0" Name="t1" IsTemporary="false" StorageType="Heap" DistributionPolicy="Hash" DistributionColumns="0" Keys="8,2" NumberLeafPartitions="0"> + <dxl:Columns> + <dxl:Column Name="join_id" Attno="1" Mdid="0.23.1.0" Nullable="true" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="select_id" Attno="2" Mdid="0.23.1.0" Nullable="true" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="ctid" Attno="-1" Mdid="0.27.1.0" Nullable="false" ColWidth="6"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="xmin" Attno="-2" Mdid="0.28.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="cmin" Attno="-3" Mdid="0.29.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="xmax" Attno="-4" Mdid="0.28.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="cmax" Attno="-5" Mdid="0.29.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="tableoid" Attno="-6" Mdid="0.26.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="gp_segment_id" Attno="-7" Mdid="0.23.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + </dxl:Columns> + <dxl:IndexInfoList/> + <dxl:CheckConstraints/> + <dxl:DistrOpfamilies> + <dxl:DistrOpfamily Mdid="0.1977.1.0"/> + </dxl:DistrOpfamilies> + </dxl:Relation> + <dxl:RelationStatistics Mdid="2.139323.1.0" Name="t2" Rows="0.000000" RelPages="0" RelAllVisible="0" EmptyRelation="true"/> + <dxl:Relation Mdid="0.139323.1.0" Name="t2" IsTemporary="false" StorageType="Heap" DistributionPolicy="Hash" DistributionColumns="0" Keys="8,2" NumberLeafPartitions="0"> + <dxl:Columns> + <dxl:Column Name="join_id" Attno="1" Mdid="0.23.1.0" Nullable="true" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="select_id" Attno="2" Mdid="0.23.1.0" Nullable="true" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="ctid" Attno="-1" Mdid="0.27.1.0" Nullable="false" ColWidth="6"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="xmin" Attno="-2" Mdid="0.28.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="cmin" Attno="-3" Mdid="0.29.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="xmax" Attno="-4" Mdid="0.28.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="cmax" Attno="-5" Mdid="0.29.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="tableoid" Attno="-6" Mdid="0.26.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="gp_segment_id" Attno="-7" Mdid="0.23.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + </dxl:Columns> + <dxl:IndexInfoList/> + <dxl:CheckConstraints/> + <dxl:DistrOpfamilies> + <dxl:DistrOpfamily Mdid="0.1977.1.0"/> + </dxl:DistrOpfamilies> + </dxl:Relation> + <dxl:MDCast Mdid="3.1043.1.0;25.1.0" Name="text" BinaryCoercible="true" SourceTypeId="0.1043.1.0" DestinationTypeId="0.25.1.0" CastFuncId="0.0.0.0" CoercePathType="0"/> + <dxl:Type Mdid="0.701.1.0" Name="float8" IsRedistributable="true" IsHashable="true" IsMergeJoinable="true" IsComposite="false" IsTextRelated="false" IsFixedLength="true" Length="8" PassByValue="true"> + <dxl:DistrOpfamily Mdid="0.1971.1.0"/> + <dxl:LegacyDistrOpfamily Mdid="0.7102.1.0"/> + <dxl:EqualityOp Mdid="0.670.1.0"/> + <dxl:InequalityOp Mdid="0.671.1.0"/> + <dxl:LessThanOp Mdid="0.672.1.0"/> + <dxl:LessThanEqualsOp Mdid="0.673.1.0"/> + <dxl:GreaterThanOp Mdid="0.674.1.0"/> + <dxl:GreaterThanEqualsOp Mdid="0.675.1.0"/> + <dxl:ComparisonOp Mdid="0.355.1.0"/> + <dxl:ArrayType Mdid="0.1022.1.0"/> + <dxl:MinAgg Mdid="0.2136.1.0"/> + <dxl:MaxAgg Mdid="0.2120.1.0"/> + <dxl:AvgAgg Mdid="0.2105.1.0"/> + <dxl:SumAgg Mdid="0.2111.1.0"/> + <dxl:CountAgg Mdid="0.2147.1.0"/> + </dxl:Type> + <dxl:RelationStatistics Mdid="2.139326.1.0" Name="t3" Rows="0.000000" RelPages="0" RelAllVisible="0" EmptyRelation="true"/> + <dxl:Relation Mdid="0.139326.1.0" Name="t3" IsTemporary="false" StorageType="Heap" DistributionPolicy="Hash" DistributionColumns="0" Keys="7,1" NumberLeafPartitions="0"> + <dxl:Columns> + <dxl:Column Name="select_id" Attno="1" Mdid="0.23.1.0" Nullable="true" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="ctid" Attno="-1" Mdid="0.27.1.0" Nullable="false" ColWidth="6"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="xmin" Attno="-2" Mdid="0.28.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="cmin" Attno="-3" Mdid="0.29.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="xmax" Attno="-4" Mdid="0.28.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="cmax" Attno="-5" Mdid="0.29.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="tableoid" Attno="-6" Mdid="0.26.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="gp_segment_id" Attno="-7" Mdid="0.23.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + </dxl:Columns> + <dxl:IndexInfoList/> + <dxl:CheckConstraints/> + <dxl:DistrOpfamilies> + <dxl:DistrOpfamily Mdid="0.1977.1.0"/> + </dxl:DistrOpfamilies> + </dxl:Relation> + <dxl:RelationStatistics Mdid="2.139329.1.0" Name="t5" Rows="0.000000" RelPages="0" RelAllVisible="0" EmptyRelation="true"/> + <dxl:Relation Mdid="0.139329.1.0" Name="t5" IsTemporary="false" StorageType="Heap" DistributionPolicy="Hash" DistributionColumns="0" Keys="10,4" NumberLeafPartitions="0"> + <dxl:Columns> + <dxl:Column Name="id" Attno="1" Mdid="0.23.1.0" Nullable="true" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="id1" Attno="2" Mdid="0.23.1.0" Nullable="true" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="name" Attno="3" Mdid="0.1043.1.0" Nullable="true" ColWidth="8"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="value" Attno="4" Mdid="0.701.1.0" Nullable="true" ColWidth="8"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="ctid" Attno="-1" Mdid="0.27.1.0" Nullable="false" ColWidth="6"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="xmin" Attno="-2" Mdid="0.28.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="cmin" Attno="-3" Mdid="0.29.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="xmax" Attno="-4" Mdid="0.28.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="cmax" Attno="-5" Mdid="0.29.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="tableoid" Attno="-6" Mdid="0.26.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="gp_segment_id" Attno="-7" Mdid="0.23.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + </dxl:Columns> + <dxl:IndexInfoList/> + <dxl:CheckConstraints/> + <dxl:DistrOpfamilies> + <dxl:DistrOpfamily Mdid="0.1977.1.0"/> + </dxl:DistrOpfamilies> + </dxl:Relation> + <dxl:ColumnStatistics Mdid="1.139320.1.0.0" Name="join_id" Width="4.000000" NullFreq="0.000000" NdvRemain="0.000000" FreqRemain="0.000000" ColStatsMissing="true"/> + <dxl:ColumnStatistics Mdid="1.139323.1.0.1" Name="select_id" Width="4.000000" NullFreq="0.000000" NdvRemain="0.000000" FreqRemain="0.000000" ColStatsMissing="true"/> + <dxl:ColumnStatistics Mdid="1.139323.1.0.0" Name="join_id" Width="4.000000" NullFreq="0.000000" NdvRemain="0.000000" FreqRemain="0.000000" ColStatsMissing="true"/> + <dxl:MDCast Mdid="3.23.1.0;23.1.0" Name="int4" BinaryCoercible="true" SourceTypeId="0.23.1.0" DestinationTypeId="0.23.1.0" CastFuncId="0.0.0.0" CoercePathType="0"/> + <dxl:GPDBScalarOp Mdid="0.97.1.0" Name="<" ComparisonType="LT" ReturnsNullOnNullInput="true" IsNDVPreserving="false"> + <dxl:LeftType Mdid="0.23.1.0"/> + <dxl:RightType Mdid="0.23.1.0"/> + <dxl:ResultType Mdid="0.16.1.0"/> + <dxl:OpFunc Mdid="0.66.1.0"/> + <dxl:Commutator Mdid="0.521.1.0"/> + <dxl:InverseOp Mdid="0.525.1.0"/> + <dxl:Opfamilies> + <dxl:Opfamily Mdid="0.1976.1.0"/> + <dxl:Opfamily Mdid="0.4054.1.0"/> + <dxl:Opfamily Mdid="0.10009.1.0"/> + </dxl:Opfamilies> + </dxl:GPDBScalarOp> + <dxl:GPDBScalarOp Mdid="0.96.1.0" Name="=" ComparisonType="Eq" ReturnsNullOnNullInput="true" IsNDVPreserving="false"> + <dxl:LeftType Mdid="0.23.1.0"/> + <dxl:RightType Mdid="0.23.1.0"/> + <dxl:ResultType Mdid="0.16.1.0"/> + <dxl:OpFunc Mdid="0.65.1.0"/> + <dxl:Commutator Mdid="0.96.1.0"/> + <dxl:InverseOp Mdid="0.518.1.0"/> + <dxl:HashOpfamily Mdid="0.1977.1.0"/> + <dxl:LegacyHashOpfamily Mdid="0.7100.1.0"/> + <dxl:Opfamilies> + <dxl:Opfamily Mdid="0.1976.1.0"/> + <dxl:Opfamily Mdid="0.1977.1.0"/> + <dxl:Opfamily Mdid="0.4054.1.0"/> + <dxl:Opfamily Mdid="0.7100.1.0"/> + <dxl:Opfamily Mdid="0.10009.1.0"/> + </dxl:Opfamilies> + </dxl:GPDBScalarOp> + <dxl:GPDBScalarOp Mdid="0.98.1.0" Name="=" ComparisonType="Eq" ReturnsNullOnNullInput="true" IsNDVPreserving="false"> + <dxl:LeftType Mdid="0.25.1.0"/> + <dxl:RightType Mdid="0.25.1.0"/> + <dxl:ResultType Mdid="0.16.1.0"/> + <dxl:OpFunc Mdid="0.67.1.0"/> + <dxl:Commutator Mdid="0.98.1.0"/> + <dxl:InverseOp Mdid="0.531.1.0"/> + <dxl:HashOpfamily Mdid="0.1995.1.0"/> + <dxl:LegacyHashOpfamily Mdid="0.7105.1.0"/> + <dxl:Opfamilies> + <dxl:Opfamily Mdid="0.1994.1.0"/> + <dxl:Opfamily Mdid="0.1995.1.0"/> + <dxl:Opfamily Mdid="0.2095.1.0"/> + <dxl:Opfamily Mdid="0.2229.1.0"/> + <dxl:Opfamily Mdid="0.4017.1.0"/> + <dxl:Opfamily Mdid="0.4056.1.0"/> + <dxl:Opfamily Mdid="0.7105.1.0"/> + <dxl:Opfamily Mdid="0.10018.1.0"/> + <dxl:Opfamily Mdid="0.10022.1.0"/> + </dxl:Opfamilies> + </dxl:GPDBScalarOp> + <dxl:ColumnStatistics Mdid="1.139326.1.0.0" Name="select_id" Width="4.000000" NullFreq="0.000000" NdvRemain="0.000000" FreqRemain="0.000000" ColStatsMissing="true"/> + </dxl:Metadata> + <dxl:Query> + <dxl:OutputColumns> + <dxl:Ident ColId="40" ColName="name1" TypeMdid="0.25.1.0"/> + <dxl:Ident ColId="41" ColName="l" TypeMdid="0.25.1.0"/> + </dxl:OutputColumns> + <dxl:CTEList/> + <dxl:LogicalSelect> + <dxl:Comparison ComparisonOperator="=" OperatorMdid="0.98.1.0"> + <dxl:Ident ColId="40" ColName="name1" TypeMdid="0.25.1.0"/> + <dxl:ConstValue TypeMdid="0.25.1.0" Value="AAAAB3Jhdw==" LintValue="2602528368"/> + </dxl:Comparison> + <dxl:LogicalProject> + <dxl:ProjList> + <dxl:ProjElem ColId="40" Alias="name1"> + <dxl:ConstValue TypeMdid="0.25.1.0" Value="AAAAB3Jhdw==" LintValue="2602528368"/> + </dxl:ProjElem> + <dxl:ProjElem ColId="41" Alias="l"> + <dxl:CoerceViaIO TypeMdid="0.25.1.0" CoercionForm="1" Location="66"> + <dxl:Ident ColId="32" ColName="value" TypeMdid="0.701.1.0"/> + </dxl:CoerceViaIO> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:LogicalJoin JoinType="Inner"> + <dxl:Union InputColumns="11,19;20,28" CastAcrossInputs="false"> + <dxl:Columns> + <dxl:Column ColId="11" Attno="1" ColName="select_id" TypeMdid="0.23.1.0"/> + <dxl:Column ColId="19" Attno="2" ColName="name" TypeMdid="0.25.1.0"/> + </dxl:Columns> + <dxl:LogicalProject> + <dxl:ProjList> + <dxl:ProjElem ColId="19" Alias="name"> + <dxl:ConstValue TypeMdid="0.25.1.0" Value="AAAADXNvbWV0aGluZw==" LintValue="577565194"/> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:LogicalJoin JoinType="Inner"> + <dxl:LogicalGet> + <dxl:TableDescriptor Mdid="0.139320.1.0" TableName="t1" LockMode="1"> + <dxl:Columns> + <dxl:Column ColId="1" Attno="1" ColName="join_id" TypeMdid="0.23.1.0" ColWidth="4"/> + <dxl:Column ColId="2" Attno="2" ColName="select_id" TypeMdid="0.23.1.0" ColWidth="4"/> + <dxl:Column ColId="3" Attno="-1" ColName="ctid" TypeMdid="0.27.1.0" ColWidth="6"/> + <dxl:Column ColId="4" Attno="-2" ColName="xmin" TypeMdid="0.28.1.0" ColWidth="4"/> + <dxl:Column ColId="5" Attno="-3" ColName="cmin" TypeMdid="0.29.1.0" ColWidth="4"/> + <dxl:Column ColId="6" Attno="-4" ColName="xmax" TypeMdid="0.28.1.0" ColWidth="4"/> + <dxl:Column ColId="7" Attno="-5" ColName="cmax" TypeMdid="0.29.1.0" ColWidth="4"/> + <dxl:Column ColId="8" Attno="-6" ColName="tableoid" TypeMdid="0.26.1.0" ColWidth="4"/> + <dxl:Column ColId="9" Attno="-7" ColName="gp_segment_id" TypeMdid="0.23.1.0" ColWidth="4"/> + </dxl:Columns> + </dxl:TableDescriptor> + </dxl:LogicalGet> + <dxl:LogicalGet> + <dxl:TableDescriptor Mdid="0.139323.1.0" TableName="t2" LockMode="1"> + <dxl:Columns> + <dxl:Column ColId="10" Attno="1" ColName="join_id" TypeMdid="0.23.1.0" ColWidth="4"/> + <dxl:Column ColId="11" Attno="2" ColName="select_id" TypeMdid="0.23.1.0" ColWidth="4"/> + <dxl:Column ColId="12" Attno="-1" ColName="ctid" TypeMdid="0.27.1.0" ColWidth="6"/> + <dxl:Column ColId="13" Attno="-2" ColName="xmin" TypeMdid="0.28.1.0" ColWidth="4"/> + <dxl:Column ColId="14" Attno="-3" ColName="cmin" TypeMdid="0.29.1.0" ColWidth="4"/> + <dxl:Column ColId="15" Attno="-4" ColName="xmax" TypeMdid="0.28.1.0" ColWidth="4"/> + <dxl:Column ColId="16" Attno="-5" ColName="cmax" TypeMdid="0.29.1.0" ColWidth="4"/> + <dxl:Column ColId="17" Attno="-6" ColName="tableoid" TypeMdid="0.26.1.0" ColWidth="4"/> + <dxl:Column ColId="18" Attno="-7" ColName="gp_segment_id" TypeMdid="0.23.1.0" ColWidth="4"/> + </dxl:Columns> + </dxl:TableDescriptor> + </dxl:LogicalGet> + <dxl:Comparison ComparisonOperator="=" OperatorMdid="0.96.1.0"> + <dxl:Ident ColId="1" ColName="join_id" TypeMdid="0.23.1.0"/> + <dxl:Ident ColId="10" ColName="join_id" TypeMdid="0.23.1.0"/> + </dxl:Comparison> + </dxl:LogicalJoin> + </dxl:LogicalProject> + <dxl:LogicalProject> + <dxl:ProjList> + <dxl:ProjElem ColId="28" Alias="name"> + <dxl:ConstValue TypeMdid="0.25.1.0" Value="AAAAEnNvbWV0aGluZ19lbHNl" LintValue="3772348069"/> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:LogicalGet> + <dxl:TableDescriptor Mdid="0.139326.1.0" TableName="t3" LockMode="1"> + <dxl:Columns> + <dxl:Column ColId="20" Attno="1" ColName="select_id" TypeMdid="0.23.1.0" ColWidth="4"/> + <dxl:Column ColId="21" Attno="-1" ColName="ctid" TypeMdid="0.27.1.0" ColWidth="6"/> + <dxl:Column ColId="22" Attno="-2" ColName="xmin" TypeMdid="0.28.1.0" ColWidth="4"/> + <dxl:Column ColId="23" Attno="-3" ColName="cmin" TypeMdid="0.29.1.0" ColWidth="4"/> + <dxl:Column ColId="24" Attno="-4" ColName="xmax" TypeMdid="0.28.1.0" ColWidth="4"/> + <dxl:Column ColId="25" Attno="-5" ColName="cmax" TypeMdid="0.29.1.0" ColWidth="4"/> + <dxl:Column ColId="26" Attno="-6" ColName="tableoid" TypeMdid="0.26.1.0" ColWidth="4"/> + <dxl:Column ColId="27" Attno="-7" ColName="gp_segment_id" TypeMdid="0.23.1.0" ColWidth="4"/> + </dxl:Columns> + </dxl:TableDescriptor> + </dxl:LogicalGet> + </dxl:LogicalProject> + </dxl:Union> + <dxl:LogicalGet> + <dxl:TableDescriptor Mdid="0.139329.1.0" TableName="t5" LockMode="1"> + <dxl:Columns> + <dxl:Column ColId="29" Attno="1" ColName="id" TypeMdid="0.23.1.0" ColWidth="4"/> + <dxl:Column ColId="30" Attno="2" ColName="id1" TypeMdid="0.23.1.0" ColWidth="4"/> + <dxl:Column ColId="31" Attno="3" ColName="name" TypeMdid="0.1043.1.0" ColWidth="8"/> + <dxl:Column ColId="32" Attno="4" ColName="value" TypeMdid="0.701.1.0" ColWidth="8"/> + <dxl:Column ColId="33" Attno="-1" ColName="ctid" TypeMdid="0.27.1.0" ColWidth="6"/> + <dxl:Column ColId="34" Attno="-2" ColName="xmin" TypeMdid="0.28.1.0" ColWidth="4"/> + <dxl:Column ColId="35" Attno="-3" ColName="cmin" TypeMdid="0.29.1.0" ColWidth="4"/> + <dxl:Column ColId="36" Attno="-4" ColName="xmax" TypeMdid="0.28.1.0" ColWidth="4"/> + <dxl:Column ColId="37" Attno="-5" ColName="cmax" TypeMdid="0.29.1.0" ColWidth="4"/> + <dxl:Column ColId="38" Attno="-6" ColName="tableoid" TypeMdid="0.26.1.0" ColWidth="4"/> + <dxl:Column ColId="39" Attno="-7" ColName="gp_segment_id" TypeMdid="0.23.1.0" ColWidth="4"/> + </dxl:Columns> + </dxl:TableDescriptor> + </dxl:LogicalGet> + <dxl:And> + <dxl:Comparison ComparisonOperator="=" OperatorMdid="0.98.1.0"> + <dxl:CoerceViaIO TypeMdid="0.25.1.0" CoercionForm="1" Location="339"> + <dxl:Ident ColId="30" ColName="id1" TypeMdid="0.23.1.0"/> + </dxl:CoerceViaIO> + <dxl:CoerceViaIO TypeMdid="0.25.1.0" CoercionForm="1" Location="361"> + <dxl:Ident ColId="11" ColName="select_id" TypeMdid="0.23.1.0"/> + </dxl:CoerceViaIO> + </dxl:Comparison> + <dxl:Comparison ComparisonOperator="=" OperatorMdid="0.98.1.0"> + <dxl:Ident ColId="19" ColName="name" TypeMdid="0.25.1.0"/> + <dxl:Cast TypeMdid="0.25.1.0" FuncId="0.0.0.0"> + <dxl:Ident ColId="31" ColName="name" TypeMdid="0.1043.1.0"/> + </dxl:Cast> + </dxl:Comparison> + </dxl:And> + </dxl:LogicalJoin> + </dxl:LogicalProject> + </dxl:LogicalSelect> + </dxl:Query> + <dxl:Plan Id="0" SpaceSize="813000"> + <dxl:Result> + <dxl:Properties> + <dxl:Cost StartupCost="0" TotalCost="1724.001501" Rows="1.000000" Width="16"/> + </dxl:Properties> + <dxl:ProjList> + <dxl:ProjElem ColId="39" Alias="name1"> + <dxl:ConstValue TypeMdid="0.25.1.0" Value="AAAAB3Jhdw==" LintValue="2602528368"/> + </dxl:ProjElem> + <dxl:ProjElem ColId="40" Alias="l"> + <dxl:CoerceViaIO TypeMdid="0.25.1.0" CoercionForm="1" Location="66"> + <dxl:Ident ColId="31" ColName="value" TypeMdid="0.701.1.0"/> + </dxl:CoerceViaIO> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:Filter/> + <dxl:OneTimeFilter/> + <dxl:GatherMotion InputSegments="0,1,2" OutputSegments="-1"> + <dxl:Properties> + <dxl:Cost StartupCost="0" TotalCost="1724.001485" Rows="1.000000" Width="8"/> + </dxl:Properties> + <dxl:ProjList> + <dxl:ProjElem ColId="31" Alias="value"> + <dxl:Ident ColId="31" ColName="value" TypeMdid="0.701.1.0"/> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:Filter/> + <dxl:SortingColumnList/> + <dxl:HashJoin JoinType="Inner"> + <dxl:Properties> + <dxl:Cost StartupCost="0" TotalCost="1724.001455" Rows="1.000000" Width="8"/> + </dxl:Properties> + <dxl:ProjList> + <dxl:ProjElem ColId="31" Alias="value"> + <dxl:Ident ColId="31" ColName="value" TypeMdid="0.701.1.0"/> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:Filter/> + <dxl:JoinFilter/> + <dxl:HashCondList> + <dxl:Comparison ComparisonOperator="=" OperatorMdid="0.98.1.0"> + <dxl:CoerceViaIO TypeMdid="0.25.1.0" CoercionForm="1" Location="339"> + <dxl:Ident ColId="29" ColName="id1" TypeMdid="0.23.1.0"/> + </dxl:CoerceViaIO> + <dxl:CoerceViaIO TypeMdid="0.25.1.0" CoercionForm="1" Location="361"> + <dxl:Ident ColId="10" ColName="select_id" TypeMdid="0.23.1.0"/> + </dxl:CoerceViaIO> + </dxl:Comparison> + <dxl:Comparison ComparisonOperator="=" OperatorMdid="0.98.1.0"> + <dxl:Cast TypeMdid="0.25.1.0" FuncId="0.0.0.0"> + <dxl:Ident ColId="30" ColName="name" TypeMdid="0.1043.1.0"/> + </dxl:Cast> + <dxl:Ident ColId="18" ColName="name" TypeMdid="0.25.1.0"/> + </dxl:Comparison> + </dxl:HashCondList> + <dxl:RedistributeMotion InputSegments="0,1,2" OutputSegments="0,1,2"> + <dxl:Properties> + <dxl:Cost StartupCost="0" TotalCost="431.000054" Rows="1.000000" Width="20"/> + </dxl:Properties> + <dxl:ProjList> + <dxl:ProjElem ColId="29" Alias="id1"> + <dxl:Ident ColId="29" ColName="id1" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + <dxl:ProjElem ColId="30" Alias="name"> + <dxl:Ident ColId="30" ColName="name" TypeMdid="0.1043.1.0"/> + </dxl:ProjElem> + <dxl:ProjElem ColId="31" Alias="value"> + <dxl:Ident ColId="31" ColName="value" TypeMdid="0.701.1.0"/> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:Filter/> + <dxl:SortingColumnList/> + <dxl:HashExprList> + <dxl:HashExpr Opfamily="0.1995.1.0"> + <dxl:CoerceViaIO TypeMdid="0.25.1.0" CoercionForm="1" Location="339"> + <dxl:Ident ColId="29" ColName="id1" TypeMdid="0.23.1.0"/> + </dxl:CoerceViaIO> + </dxl:HashExpr> + </dxl:HashExprList> + <dxl:TableScan> + <dxl:Properties> + <dxl:Cost StartupCost="0" TotalCost="431.000010" Rows="1.000000" Width="20"/> + </dxl:Properties> + <dxl:ProjList> + <dxl:ProjElem ColId="29" Alias="id1"> + <dxl:Ident ColId="29" ColName="id1" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + <dxl:ProjElem ColId="30" Alias="name"> + <dxl:Ident ColId="30" ColName="name" TypeMdid="0.1043.1.0"/> + </dxl:ProjElem> + <dxl:ProjElem ColId="31" Alias="value"> + <dxl:Ident ColId="31" ColName="value" TypeMdid="0.701.1.0"/> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:Filter/> + <dxl:TableDescriptor Mdid="0.139329.1.0" TableName="t5" LockMode="1"> + <dxl:Columns> + <dxl:Column ColId="28" Attno="1" ColName="id" TypeMdid="0.23.1.0" ColWidth="4"/> + <dxl:Column ColId="29" Attno="2" ColName="id1" TypeMdid="0.23.1.0" ColWidth="4"/> + <dxl:Column ColId="30" Attno="3" ColName="name" TypeMdid="0.1043.1.0" ColWidth="8"/> + <dxl:Column ColId="31" Attno="4" ColName="value" TypeMdid="0.701.1.0" ColWidth="8"/> + <dxl:Column ColId="32" Attno="-1" ColName="ctid" TypeMdid="0.27.1.0" ColWidth="6"/> + <dxl:Column ColId="33" Attno="-2" ColName="xmin" TypeMdid="0.28.1.0" ColWidth="4"/> + <dxl:Column ColId="34" Attno="-3" ColName="cmin" TypeMdid="0.29.1.0" ColWidth="4"/> + <dxl:Column ColId="35" Attno="-4" ColName="xmax" TypeMdid="0.28.1.0" ColWidth="4"/> + <dxl:Column ColId="36" Attno="-5" ColName="cmax" TypeMdid="0.29.1.0" ColWidth="4"/> + <dxl:Column ColId="37" Attno="-6" ColName="tableoid" TypeMdid="0.26.1.0" ColWidth="4"/> + <dxl:Column ColId="38" Attno="-7" ColName="gp_segment_id" TypeMdid="0.23.1.0" ColWidth="4"/> + </dxl:Columns> + </dxl:TableDescriptor> + </dxl:TableScan> + </dxl:RedistributeMotion> + <dxl:RedistributeMotion InputSegments="0,1,2" OutputSegments="0,1,2"> + <dxl:Properties> + <dxl:Cost StartupCost="0" TotalCost="1293.000560" Rows="1.000000" Width="12"/> + </dxl:Properties> + <dxl:ProjList> + <dxl:ProjElem ColId="10" Alias="select_id"> + <dxl:Ident ColId="10" ColName="select_id" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + <dxl:ProjElem ColId="18" Alias="name"> + <dxl:Ident ColId="18" ColName="name" TypeMdid="0.25.1.0"/> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:Filter/> + <dxl:SortingColumnList/> + <dxl:HashExprList> + <dxl:HashExpr Opfamily="0.1995.1.0"> + <dxl:CoerceViaIO TypeMdid="0.25.1.0" CoercionForm="1" Location="361"> + <dxl:Ident ColId="10" ColName="select_id" TypeMdid="0.23.1.0"/> + </dxl:CoerceViaIO> + </dxl:HashExpr> + </dxl:HashExprList> + <dxl:Aggregate AggregationStrategy="Sorted" StreamSafe="false"> + <dxl:Properties> + <dxl:Cost StartupCost="0" TotalCost="1293.000522" Rows="1.000000" Width="12"/> + </dxl:Properties> + <dxl:GroupingColumns> + <dxl:GroupingColumn ColId="10"/> + <dxl:GroupingColumn ColId="18"/> + </dxl:GroupingColumns> + <dxl:ProjList> + <dxl:ProjElem ColId="10" Alias="select_id"> + <dxl:Ident ColId="10" ColName="select_id" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + <dxl:ProjElem ColId="18" Alias="name"> + <dxl:Ident ColId="18" ColName="name" TypeMdid="0.25.1.0"/> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:Filter/> + <dxl:Sort SortDiscardDuplicates="false"> + <dxl:Properties> + <dxl:Cost StartupCost="0" TotalCost="1293.000503" Rows="1.000000" Width="12"/> + </dxl:Properties> + <dxl:ProjList> + <dxl:ProjElem ColId="10" Alias="select_id"> + <dxl:Ident ColId="10" ColName="select_id" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + <dxl:ProjElem ColId="18" Alias="name"> + <dxl:Ident ColId="18" ColName="name" TypeMdid="0.25.1.0"/> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:Filter/> + <dxl:SortingColumnList> + <dxl:SortingColumn ColId="10" SortOperatorMdid="0.97.1.0" SortOperatorName="<" SortNullsFirst="false"/> + <dxl:SortingColumn ColId="18" SortOperatorMdid="0.664.1.0" SortOperatorName="<" SortNullsFirst="false"/> + </dxl:SortingColumnList> + <dxl:LimitCount/> + <dxl:LimitOffset/> + <dxl:RedistributeMotion InputSegments="0,1,2" OutputSegments="0,1,2"> + <dxl:Properties> + <dxl:Cost StartupCost="0" TotalCost="1293.000503" Rows="1.000000" Width="12"/> + </dxl:Properties> + <dxl:ProjList> + <dxl:ProjElem ColId="10" Alias="select_id"> + <dxl:Ident ColId="10" ColName="select_id" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + <dxl:ProjElem ColId="18" Alias="name"> + <dxl:Ident ColId="18" ColName="name" TypeMdid="0.25.1.0"/> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:Filter/> + <dxl:SortingColumnList/> + <dxl:HashExprList> + <dxl:HashExpr Opfamily="0.1977.1.0"> + <dxl:Ident ColId="10" ColName="select_id" TypeMdid="0.23.1.0"/> + </dxl:HashExpr> + <dxl:HashExpr Opfamily="0.1995.1.0"> + <dxl:Ident ColId="18" ColName="name" TypeMdid="0.25.1.0"/> + </dxl:HashExpr> + </dxl:HashExprList> + <dxl:Append IsTarget="false" IsZapped="false"> + <dxl:Properties> + <dxl:Cost StartupCost="0" TotalCost="1293.000484" Rows="1.000000" Width="12"/> + </dxl:Properties> + <dxl:ProjList> + <dxl:ProjElem ColId="10" Alias="select_id"> + <dxl:Ident ColId="10" ColName="select_id" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + <dxl:ProjElem ColId="18" Alias="name"> + <dxl:Ident ColId="18" ColName="name" TypeMdid="0.25.1.0"/> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:Filter/> + <dxl:Result> + <dxl:Properties> + <dxl:Cost StartupCost="0" TotalCost="862.000442" Rows="1.000000" Width="12"/> + </dxl:Properties> + <dxl:ProjList> + <dxl:ProjElem ColId="10" Alias="select_id"> + <dxl:Ident ColId="10" ColName="select_id" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + <dxl:ProjElem ColId="18" Alias="name"> + <dxl:ConstValue TypeMdid="0.25.1.0" Value="AAAADXNvbWV0aGluZw==" LintValue="577565194"/> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:Filter/> + <dxl:OneTimeFilter/> + <dxl:HashJoin JoinType="Inner"> + <dxl:Properties> + <dxl:Cost StartupCost="0" TotalCost="862.000438" Rows="1.000000" Width="4"/> + </dxl:Properties> + <dxl:ProjList> + <dxl:ProjElem ColId="10" Alias="select_id"> + <dxl:Ident ColId="10" ColName="select_id" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:Filter/> + <dxl:JoinFilter/> + <dxl:HashCondList> + <dxl:Comparison ComparisonOperator="=" OperatorMdid="0.96.1.0"> + <dxl:Ident ColId="9" ColName="join_id" TypeMdid="0.23.1.0"/> + <dxl:Ident ColId="0" ColName="join_id" TypeMdid="0.23.1.0"/> + </dxl:Comparison> + </dxl:HashCondList> + <dxl:TableScan> + <dxl:Properties> + <dxl:Cost StartupCost="0" TotalCost="431.000021" Rows="1.000000" Width="8"/> + </dxl:Properties> + <dxl:ProjList> + <dxl:ProjElem ColId="9" Alias="join_id"> + <dxl:Ident ColId="9" ColName="join_id" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + <dxl:ProjElem ColId="10" Alias="select_id"> + <dxl:Ident ColId="10" ColName="select_id" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:Filter/> + <dxl:TableDescriptor Mdid="0.139323.1.0" TableName="t2" LockMode="1"> + <dxl:Columns> + <dxl:Column ColId="9" Attno="1" ColName="join_id" TypeMdid="0.23.1.0" ColWidth="4"/> + <dxl:Column ColId="10" Attno="2" ColName="select_id" TypeMdid="0.23.1.0" ColWidth="4"/> + <dxl:Column ColId="11" Attno="-1" ColName="ctid" TypeMdid="0.27.1.0" ColWidth="6"/> + <dxl:Column ColId="12" Attno="-2" ColName="xmin" TypeMdid="0.28.1.0" ColWidth="4"/> + <dxl:Column ColId="13" Attno="-3" ColName="cmin" TypeMdid="0.29.1.0" ColWidth="4"/> + <dxl:Column ColId="14" Attno="-4" ColName="xmax" TypeMdid="0.28.1.0" ColWidth="4"/> + <dxl:Column ColId="15" Attno="-5" ColName="cmax" TypeMdid="0.29.1.0" ColWidth="4"/> + <dxl:Column ColId="16" Attno="-6" ColName="tableoid" TypeMdid="0.26.1.0" ColWidth="4"/> + <dxl:Column ColId="17" Attno="-7" ColName="gp_segment_id" TypeMdid="0.23.1.0" ColWidth="4"/> + </dxl:Columns> + </dxl:TableDescriptor> + </dxl:TableScan> + <dxl:TableScan> + <dxl:Properties> + <dxl:Cost StartupCost="0" TotalCost="431.000028" Rows="1.000000" Width="4"/> + </dxl:Properties> + <dxl:ProjList> + <dxl:ProjElem ColId="0" Alias="join_id"> + <dxl:Ident ColId="0" ColName="join_id" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:Filter> + <dxl:Comparison ComparisonOperator="=" OperatorMdid="0.98.1.0"> + <dxl:ConstValue TypeMdid="0.25.1.0" Value="AAAAB3Jhdw==" LintValue="2602528368"/> + <dxl:ConstValue TypeMdid="0.25.1.0" Value="AAAAB3Jhdw==" LintValue="2602528368"/> + </dxl:Comparison> + </dxl:Filter> + <dxl:TableDescriptor Mdid="0.139320.1.0" TableName="t1" LockMode="1"> + <dxl:Columns> + <dxl:Column ColId="0" Attno="1" ColName="join_id" TypeMdid="0.23.1.0" ColWidth="4"/> + <dxl:Column ColId="2" Attno="-1" ColName="ctid" TypeMdid="0.27.1.0" ColWidth="6"/> + <dxl:Column ColId="3" Attno="-2" ColName="xmin" TypeMdid="0.28.1.0" ColWidth="4"/> + <dxl:Column ColId="4" Attno="-3" ColName="cmin" TypeMdid="0.29.1.0" ColWidth="4"/> + <dxl:Column ColId="5" Attno="-4" ColName="xmax" TypeMdid="0.28.1.0" ColWidth="4"/> + <dxl:Column ColId="6" Attno="-5" ColName="cmax" TypeMdid="0.29.1.0" ColWidth="4"/> + <dxl:Column ColId="7" Attno="-6" ColName="tableoid" TypeMdid="0.26.1.0" ColWidth="4"/> + <dxl:Column ColId="8" Attno="-7" ColName="gp_segment_id" TypeMdid="0.23.1.0" ColWidth="4"/> + </dxl:Columns> + </dxl:TableDescriptor> + </dxl:TableScan> + </dxl:HashJoin> + </dxl:Result> + <dxl:Result> + <dxl:Properties> + <dxl:Cost StartupCost="0" TotalCost="431.000038" Rows="1.000000" Width="12"/> + </dxl:Properties> + <dxl:ProjList> + <dxl:ProjElem ColId="19" Alias="select_id"> + <dxl:Ident ColId="19" ColName="select_id" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + <dxl:ProjElem ColId="27" Alias="name"> + <dxl:ConstValue TypeMdid="0.25.1.0" Value="AAAAEnNvbWV0aGluZ19lbHNl" LintValue="3772348069"/> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:Filter/> + <dxl:OneTimeFilter/> + <dxl:TableScan> + <dxl:Properties> + <dxl:Cost StartupCost="0" TotalCost="431.000026" Rows="1.000000" Width="4"/> + </dxl:Properties> + <dxl:ProjList> + <dxl:ProjElem ColId="19" Alias="select_id"> + <dxl:Ident ColId="19" ColName="select_id" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:Filter> + <dxl:Comparison ComparisonOperator="=" OperatorMdid="0.98.1.0"> + <dxl:ConstValue TypeMdid="0.25.1.0" Value="AAAAB3Jhdw==" LintValue="2602528368"/> + <dxl:ConstValue TypeMdid="0.25.1.0" Value="AAAAB3Jhdw==" LintValue="2602528368"/> + </dxl:Comparison> + </dxl:Filter> + <dxl:TableDescriptor Mdid="0.139326.1.0" TableName="t3" LockMode="1"> + <dxl:Columns> + <dxl:Column ColId="19" Attno="1" ColName="select_id" TypeMdid="0.23.1.0" ColWidth="4"/> + <dxl:Column ColId="20" Attno="-1" ColName="ctid" TypeMdid="0.27.1.0" ColWidth="6"/> + <dxl:Column ColId="21" Attno="-2" ColName="xmin" TypeMdid="0.28.1.0" ColWidth="4"/> + <dxl:Column ColId="22" Attno="-3" ColName="cmin" TypeMdid="0.29.1.0" ColWidth="4"/> + <dxl:Column ColId="23" Attno="-4" ColName="xmax" TypeMdid="0.28.1.0" ColWidth="4"/> + <dxl:Column ColId="24" Attno="-5" ColName="cmax" TypeMdid="0.29.1.0" ColWidth="4"/> + <dxl:Column ColId="25" Attno="-6" ColName="tableoid" TypeMdid="0.26.1.0" ColWidth="4"/> + <dxl:Column ColId="26" Attno="-7" ColName="gp_segment_id" TypeMdid="0.23.1.0" ColWidth="4"/> + </dxl:Columns> + </dxl:TableDescriptor> + </dxl:TableScan> + </dxl:Result> + </dxl:Append> + </dxl:RedistributeMotion> + </dxl:Sort> + </dxl:Aggregate> + </dxl:RedistributeMotion> + </dxl:HashJoin> + </dxl:GatherMotion> + </dxl:Result> + </dxl:Plan> + </dxl:Thread> +</dxl:DXLMessage> diff --git a/src/backend/gporca/libgpopt/include/gpopt/operators/CExpressionPreprocessor.h b/src/backend/gporca/libgpopt/include/gpopt/operators/CExpressionPreprocessor.h index ff10ef81db..64b2f3c9a6 100644 --- a/src/backend/gporca/libgpopt/include/gpopt/operators/CExpressionPreprocessor.h +++ b/src/backend/gporca/libgpopt/include/gpopt/operators/CExpressionPreprocessor.h @@ -210,6 +210,11 @@ private: static CExpression *ConvertSplitUpdateToInPlaceUpdate(CMemoryPool *mp, CExpression *expr); + static CExpression *CollapseSelectAndReplaceColref(CMemoryPool *mp, + CExpression *expr, + CColRef *pcolref, + CExpression *pprojExpr); + public: CExpressionPreprocessor() = delete; diff --git a/src/backend/gporca/libgpopt/src/operators/CExpressionPreprocessor.cpp b/src/backend/gporca/libgpopt/src/operators/CExpressionPreprocessor.cpp index aa58c8d912..1f9f061365 100644 --- a/src/backend/gporca/libgpopt/src/operators/CExpressionPreprocessor.cpp +++ b/src/backend/gporca/libgpopt/src/operators/CExpressionPreprocessor.cpp @@ -2755,9 +2755,11 @@ CExpressionPreprocessor::PcnstrFromChildPartition( } // Collapse a select over a project and update column reference. -static CExpression * -CollapseSelectAndReplaceColref(CMemoryPool *mp, CExpression *pexpr, - CColRef *pcolref, CExpression *pprojExpr) +CExpression * +CExpressionPreprocessor::CollapseSelectAndReplaceColref(CMemoryPool *mp, + CExpression *pexpr, + CColRef *pcolref, + CExpression *pprojExpr) { // remove the logical project // @@ -2773,10 +2775,15 @@ CollapseSelectAndReplaceColref(CMemoryPool *mp, CExpression *pexpr, (*(*pexpr)[0])[0]->Pop()->Eopid() == COperator::EopLogicalNAryJoin) { (*(*pexpr)[0])[0]->AddRef(); - return GPOS_NEW(mp) + CExpression *pexprCollapsedSelect = GPOS_NEW(mp) CExpression(mp, GPOS_NEW(mp) CLogicalSelect(mp), (*(*pexpr)[0])[0], CollapseSelectAndReplaceColref(mp, (*pexpr)[1], pcolref, pprojExpr)); + + CExpression *pexprTransposed = + PexprTransposeSelectAndProject(mp, pexprCollapsedSelect); + pexprCollapsedSelect->Release(); + return pexprTransposed; } // replace reference @@ -2915,9 +2922,15 @@ CExpressionPreprocessor::PexprTransposeSelectAndProject(CMemoryPool *mp, // parts. // // NB: JoinOnViewWithMixOfPushableAndNonpushablePredicates.mdp + CExpression *prevpselectNew = pselectNew; pselectNew = CollapseSelectAndReplaceColref( - mp, pselectNew, CUtils::PNthProjectElement(pproject, ul)->Pcr(), + mp, prevpselectNew, + CUtils::PNthProjectElement(pproject, ul)->Pcr(), CUtils::PNthProjectElementExpr(pproject, ul)); + if (pexpr != prevpselectNew) + { + prevpselectNew->Release(); + } } pdrgpexpr->Append(pselectNew); diff --git a/src/backend/gporca/server/CMakeLists.txt b/src/backend/gporca/server/CMakeLists.txt index a5f77d49dc..67f7b4b15b 100644 --- a/src/backend/gporca/server/CMakeLists.txt +++ b/src/backend/gporca/server/CMakeLists.txt @@ -172,7 +172,7 @@ MultipleDampedPredJoinCardinality MultipleIndependentPredJoinCardinality MultiDi MultiDistKeyWithOtherPredsJoinCardinality NoDistKeyMultiPredJoinCardinality OneDistKeyMultiPredJoinCardinality JoinOnViewWithCastedColumn JoinOnViewWithCastedColumnAndSubqueryInPredicate JoinOnViewWithVolatileColumn JoinOnViewWithMixOfPushableAndNonpushablePredicates JoinOnViewWithSetReturningColumn OuterJoinOnViewWithCastedColumn JoinWithSubqueryProjectColumn -AggSubqCollapseFalseFilter; +AggSubqCollapseFalseFilter NestedJoinWithCastedColumn; CArrayCmpTest: ArrayConcat ArrayRef FoldedArrayCmp ArrayCmpAll --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
