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 7ffe21e3b774e4a55717f8bcddeebed2cc2b3b93 Author: David Kimura <[email protected]> AuthorDate: Mon Oct 3 13:46:22 2022 -0700 [ORCA] Fix window frame translator related FIXMEs (#14191) - Fix up accidental swapped window frame options Window frame options were flipped accidentally in commit ebf9763c7882. The only reason we got away with it is because we also flipped them in CTranslatorDXLToPlStmt::TranslateDXLWindow(). Flip them back. - [ORCA] Add support for window EXCLUDE, RANGE, GROUPS option Round trip EXCLUDE, RANGE, GROUPS info. ORCA doesn't need to do anything with them. --- .../gpopt/translate/CTranslatorDXLToPlStmt.cpp | 58 +++-- .../gpopt/translate/CTranslatorQueryToDXL.cpp | 7 +- .../gpopt/translate/CTranslatorScalarToDXL.cpp | 78 +++--- .../dxl/minidump/WindowFrameExcludeCurrentRow.mdp | 237 +++++++++++++++++++ .../data/dxl/minidump/WindowFrameExcludeGroup.mdp | 231 ++++++++++++++++++ .../data/dxl/minidump/WindowFrameExcludeTies.mdp | 231 ++++++++++++++++++ .../gporca/data/dxl/minidump/WindowFrameGroups.mdp | 207 ++++++++++++++++ .../WindowFrameRangePrecedingAndFollowing.mdp | 263 +++++++++++++++++++++ .../data/dxl/parse_tests/q45-WindowWithFraming.xml | 2 +- .../libgpopt/include/gpopt/base/CWindowFrame.h | 55 ++++- .../gporca/libgpopt/src/base/CWindowFrame.cpp | 27 ++- .../src/translate/CTranslatorDXLToExpr.cpp | 12 +- .../src/translate/CTranslatorExprToDXL.cpp | 12 +- .../naucrates/dxl/operators/CDXLWindowFrame.h | 51 +++- .../dxl/parser/CParseHandlerWindowFrame.h | 15 ++ .../include/naucrates/dxl/xml/dxltokens.h | 7 + .../src/operators/CDXLOperatorFactory.cpp | 6 + .../libnaucrates/src/operators/CDXLWindowFrame.cpp | 31 ++- .../src/parser/CParseHandlerWindowFrame.cpp | 12 +- .../gporca/libnaucrates/src/xml/dxltokens.cpp | 7 + .../src/unittest/gpopt/minidump/CWindowTest.cpp | 5 + .../gpopt/translate/CTranslatorScalarToDXL.h | 2 + src/test/regress/expected/gporca_optimizer.out | 4 - src/test/regress/expected/window.out | 2 + src/test/regress/expected/window_optimizer.out | 74 +++++- src/test/regress/sql/window.sql | 2 + 26 files changed, 1536 insertions(+), 102 deletions(-) diff --git a/src/backend/gpopt/translate/CTranslatorDXLToPlStmt.cpp b/src/backend/gpopt/translate/CTranslatorDXLToPlStmt.cpp index d95df69ab8..efef8d4ff3 100644 --- a/src/backend/gpopt/translate/CTranslatorDXLToPlStmt.cpp +++ b/src/backend/gpopt/translate/CTranslatorDXLToPlStmt.cpp @@ -2958,16 +2958,28 @@ CTranslatorDXLToPlStmt::TranslateDXLWindow( { window->frameOptions |= FRAMEOPTION_ROWS; } + else if (EdxlfsGroups == window_frame->ParseDXLFrameSpec()) + { + window->frameOptions |= FRAMEOPTION_GROUPS; + } else { window->frameOptions |= FRAMEOPTION_RANGE; } - if (window_frame->ParseFrameExclusionStrategy() != EdxlfesNulls) + if (window_frame->ParseFrameExclusionStrategy() == + EdxlfesCurrentRow) + { + window->frameOptions |= FRAMEOPTION_EXCLUDE_CURRENT_ROW; + } + else if (window_frame->ParseFrameExclusionStrategy() == + EdxlfesGroup) + { + window->frameOptions |= FRAMEOPTION_EXCLUDE_GROUP; + } + else if (window_frame->ParseFrameExclusionStrategy() == EdxlfesTies) { - GPOS_RAISE(gpdxl::ExmaDXL, - gpdxl::ExmiQuery2DXLUnsupportedFeature, - GPOS_WSZ_LIT("EXCLUDE clause in window frame")); + window->frameOptions |= FRAMEOPTION_EXCLUDE_TIES; } // translate the CDXLNodes representing the leading and trailing edge @@ -2992,35 +3004,35 @@ CTranslatorDXLToPlStmt::TranslateDXLWindow( ->ParseDXLFrameBoundary(); if (lead_boundary_type == EdxlfbUnboundedPreceding) { - window->frameOptions |= FRAMEOPTION_END_UNBOUNDED_PRECEDING; + window->frameOptions |= FRAMEOPTION_START_UNBOUNDED_PRECEDING; } if (lead_boundary_type == EdxlfbBoundedPreceding) { - window->frameOptions |= FRAMEOPTION_END_OFFSET_PRECEDING; + window->frameOptions |= FRAMEOPTION_START_OFFSET_PRECEDING; } if (lead_boundary_type == EdxlfbCurrentRow) { - window->frameOptions |= FRAMEOPTION_END_CURRENT_ROW; + window->frameOptions |= FRAMEOPTION_START_CURRENT_ROW; } if (lead_boundary_type == EdxlfbBoundedFollowing) { - window->frameOptions |= FRAMEOPTION_END_OFFSET_FOLLOWING; + window->frameOptions |= FRAMEOPTION_START_OFFSET_FOLLOWING; } if (lead_boundary_type == EdxlfbUnboundedFollowing) { - window->frameOptions |= FRAMEOPTION_END_UNBOUNDED_FOLLOWING; + window->frameOptions |= FRAMEOPTION_START_UNBOUNDED_FOLLOWING; } if (lead_boundary_type == EdxlfbDelayedBoundedPreceding) { - window->frameOptions |= FRAMEOPTION_END_OFFSET_PRECEDING; + window->frameOptions |= FRAMEOPTION_START_OFFSET_PRECEDING; } if (lead_boundary_type == EdxlfbDelayedBoundedFollowing) { - window->frameOptions |= FRAMEOPTION_END_OFFSET_FOLLOWING; + window->frameOptions |= FRAMEOPTION_START_OFFSET_FOLLOWING; } if (0 != win_frame_leading_dxlnode->Arity()) { - window->endOffset = + window->startOffset = (Node *) m_translator_dxl_to_scalar->TranslateDXLToScalar( (*win_frame_leading_dxlnode)[0], &colid_var_mapping); } @@ -3034,39 +3046,45 @@ CTranslatorDXLToPlStmt::TranslateDXLWindow( ->ParseDXLFrameBoundary(); if (trail_boundary_type == EdxlfbUnboundedPreceding) { - window->frameOptions |= FRAMEOPTION_START_UNBOUNDED_PRECEDING; + window->frameOptions |= FRAMEOPTION_END_UNBOUNDED_PRECEDING; } if (trail_boundary_type == EdxlfbBoundedPreceding) { - window->frameOptions |= FRAMEOPTION_START_OFFSET_PRECEDING; + window->frameOptions |= FRAMEOPTION_END_OFFSET_PRECEDING; } if (trail_boundary_type == EdxlfbCurrentRow) { - window->frameOptions |= FRAMEOPTION_START_CURRENT_ROW; + window->frameOptions |= FRAMEOPTION_END_CURRENT_ROW; } if (trail_boundary_type == EdxlfbBoundedFollowing) { - window->frameOptions |= FRAMEOPTION_START_OFFSET_FOLLOWING; + window->frameOptions |= FRAMEOPTION_END_OFFSET_FOLLOWING; } if (trail_boundary_type == EdxlfbUnboundedFollowing) { - window->frameOptions |= FRAMEOPTION_START_UNBOUNDED_FOLLOWING; + window->frameOptions |= FRAMEOPTION_END_UNBOUNDED_FOLLOWING; } if (trail_boundary_type == EdxlfbDelayedBoundedPreceding) { - window->frameOptions |= FRAMEOPTION_START_OFFSET_PRECEDING; + window->frameOptions |= FRAMEOPTION_END_OFFSET_PRECEDING; } if (trail_boundary_type == EdxlfbDelayedBoundedFollowing) { - window->frameOptions |= FRAMEOPTION_START_OFFSET_FOLLOWING; + window->frameOptions |= FRAMEOPTION_END_OFFSET_FOLLOWING; } if (0 != win_frame_trailing_dxlnode->Arity()) { - window->startOffset = + window->endOffset = (Node *) m_translator_dxl_to_scalar->TranslateDXLToScalar( (*win_frame_trailing_dxlnode)[0], &colid_var_mapping); } + window->startInRangeFunc = window_frame->PdxlnStartInRangeFunc(); + window->endInRangeFunc = window_frame->PdxlnEndInRangeFunc(); + window->inRangeColl = window_frame->PdxlnInRangeColl(); + window->inRangeAsc = window_frame->PdxlnInRangeAsc(); + window->inRangeNullsFirst = window_frame->PdxlnInRangeNullsFirst(); + // cleanup child_contexts->Release(); } diff --git a/src/backend/gpopt/translate/CTranslatorQueryToDXL.cpp b/src/backend/gpopt/translate/CTranslatorQueryToDXL.cpp index c4dbd9dad7..3045ed6d35 100644 --- a/src/backend/gpopt/translate/CTranslatorQueryToDXL.cpp +++ b/src/backend/gpopt/translate/CTranslatorQueryToDXL.cpp @@ -1512,7 +1512,8 @@ CTranslatorQueryToDXL::CreateWindowFramForLeadLag(BOOL is_lead_func, return GPOS_NEW(m_mp) CDXLWindowFrame( EdxlfsRow, // frame specification EdxlfesNulls, // frame exclusion strategy is set to exclude NULLs in GPDB - dxl_lead_edge, dxl_trail_edge); + dxl_lead_edge, dxl_trail_edge, InvalidOid, InvalidOid, InvalidOid, + false, false); } @@ -1644,7 +1645,9 @@ CTranslatorQueryToDXL::TranslateWindowSpecToDXL( window_frame = m_scalar_translator->TranslateWindowFrameToDXL( wc->frameOptions, wc->startOffset, wc->endOffset, - m_var_to_colid_map, project_list_dxlnode_node); + wc->startInRangeFunc, wc->endInRangeFunc, wc->inRangeColl, + wc->inRangeAsc, wc->inRangeNullsFirst, m_var_to_colid_map, + project_list_dxlnode_node); CDXLWindowSpec *window_spec_dxlnode = GPOS_NEW(m_mp) CDXLWindowSpec( m_mp, part_columns, mdname, sort_col_list_dxl, window_frame); diff --git a/src/backend/gpopt/translate/CTranslatorScalarToDXL.cpp b/src/backend/gpopt/translate/CTranslatorScalarToDXL.cpp index c2eb60c3e8..69cc5a75d5 100644 --- a/src/backend/gpopt/translate/CTranslatorScalarToDXL.cpp +++ b/src/backend/gpopt/translate/CTranslatorScalarToDXL.cpp @@ -1513,18 +1513,12 @@ CTranslatorScalarToDXL::TranslateAggrefToDXL( CDXLWindowFrame * CTranslatorScalarToDXL::TranslateWindowFrameToDXL( int frame_options, const Node *start_offset, const Node *end_offset, + Oid start_in_range_func, Oid end_in_range_func, Oid in_range_coll, + bool in_range_asc, bool in_range_nulls_first, const CMappingVarColId *var_colid_mapping, CDXLNode *new_scalar_proj_list) { EdxlFrameSpec frame_spec; - // GPDB_12_MERGE_FIXME: there's no reason ORCA would care about this, other - // than that it doesn't roundtrip this piece of info. - if ((frame_options & FRAMEOPTION_EXCLUSION)) - { - GPOS_RAISE(gpdxl::ExmaDXL, gpdxl::ExmiQuery2DXLUnsupportedFeature, - GPOS_WSZ_LIT("window frame EXCLUDE")); - } - if ((frame_options & FRAMEOPTION_ROWS) != 0) { frame_spec = EdxlfsRow; @@ -1532,25 +1526,10 @@ CTranslatorScalarToDXL::TranslateWindowFrameToDXL( else if ((frame_options & FRAMEOPTION_RANGE) != 0) { frame_spec = EdxlfsRange; - // GPDB_12_MERGE_FIXME: as soon as we can pass WindowClause::startInRangeFunc - // and friends to ORCA and get them back, we can support stuff like - // RANGE 1 PRECEDING - if ((frame_options & - (FRAMEOPTION_START_OFFSET | FRAMEOPTION_END_OFFSET))) - { - GPOS_RAISE( - gpdxl::ExmaDXL, gpdxl::ExmiQuery2DXLUnsupportedFeature, - GPOS_WSZ_LIT( - "window frame RANGE with OFFSET PRECEDING or FOLLOWING")); - } } else if ((frame_options & FRAMEOPTION_GROUPS) != 0) { - // GPDB_12_MERGE_FIXME: there's no reason the optimizer would care too - // much about this. As long as we recognize and roundtrip this, I think - // the executor will take care of it - GPOS_RAISE(gpdxl::ExmaDXL, gpdxl::ExmiQuery2DXLUnsupportedFeature, - GPOS_WSZ_LIT("window frame GROUPS mode")); + frame_spec = EdxlfsGroups; } else { @@ -1558,30 +1537,24 @@ CTranslatorScalarToDXL::TranslateWindowFrameToDXL( GPOS_WSZ_LIT("window frame option")); } - - // GPDB_12_MERGE_FIXME: the following window frame options are flipped (i.e. - // the START_ and END_ ones are swapped accidently in commit - // ebf9763c78826819). The only reason we got away with it is because we also - // flipped them in CTranslatorDXLToPlStmt::TranslateDXLWindow(). Flip them - // back after the merge. EdxlFrameBoundary leading_boundary; - if ((frame_options & FRAMEOPTION_END_UNBOUNDED_PRECEDING) != 0) + if ((frame_options & FRAMEOPTION_START_UNBOUNDED_PRECEDING) != 0) { leading_boundary = EdxlfbUnboundedPreceding; } - else if ((frame_options & FRAMEOPTION_END_OFFSET_PRECEDING) != 0) + else if ((frame_options & FRAMEOPTION_START_OFFSET_PRECEDING) != 0) { leading_boundary = EdxlfbBoundedPreceding; } - else if ((frame_options & FRAMEOPTION_END_CURRENT_ROW) != 0) + else if ((frame_options & FRAMEOPTION_START_CURRENT_ROW) != 0) { leading_boundary = EdxlfbCurrentRow; } - else if ((frame_options & FRAMEOPTION_END_OFFSET_FOLLOWING) != 0) + else if ((frame_options & FRAMEOPTION_START_OFFSET_FOLLOWING) != 0) { leading_boundary = EdxlfbBoundedFollowing; } - else if ((frame_options & FRAMEOPTION_END_UNBOUNDED_FOLLOWING) != 0) + else if ((frame_options & FRAMEOPTION_START_UNBOUNDED_FOLLOWING) != 0) { leading_boundary = EdxlfbUnboundedFollowing; } @@ -1592,23 +1565,23 @@ CTranslatorScalarToDXL::TranslateWindowFrameToDXL( } EdxlFrameBoundary trailing_boundary; - if ((frame_options & FRAMEOPTION_START_UNBOUNDED_PRECEDING) != 0) + if ((frame_options & FRAMEOPTION_END_UNBOUNDED_PRECEDING) != 0) { trailing_boundary = EdxlfbUnboundedPreceding; } - else if ((frame_options & FRAMEOPTION_START_OFFSET_PRECEDING) != 0) + else if ((frame_options & FRAMEOPTION_END_OFFSET_PRECEDING) != 0) { trailing_boundary = EdxlfbBoundedPreceding; } - else if ((frame_options & FRAMEOPTION_START_CURRENT_ROW) != 0) + else if ((frame_options & FRAMEOPTION_END_CURRENT_ROW) != 0) { trailing_boundary = EdxlfbCurrentRow; } - else if ((frame_options & FRAMEOPTION_START_OFFSET_FOLLOWING) != 0) + else if ((frame_options & FRAMEOPTION_END_OFFSET_FOLLOWING) != 0) { trailing_boundary = EdxlfbBoundedFollowing; } - else if ((frame_options & FRAMEOPTION_START_UNBOUNDED_FOLLOWING) != 0) + else if ((frame_options & FRAMEOPTION_END_UNBOUNDED_FOLLOWING) != 0) { trailing_boundary = EdxlfbUnboundedFollowing; } @@ -1621,6 +1594,18 @@ CTranslatorScalarToDXL::TranslateWindowFrameToDXL( // We don't support non-default EXCLUDE [CURRENT ROW | GROUP | TIES | // NO OTHERS] options. EdxlFrameExclusionStrategy strategy = EdxlfesNulls; + if ((frame_options & FRAMEOPTION_EXCLUDE_CURRENT_ROW) != 0) + { + strategy = EdxlfesCurrentRow; + } + else if ((frame_options & FRAMEOPTION_EXCLUDE_GROUP) != 0) + { + strategy = EdxlfesGroup; + } + else if ((frame_options & FRAMEOPTION_EXCLUDE_TIES) != 0) + { + strategy = EdxlfesTies; + } CDXLNode *lead_edge = GPOS_NEW(m_mp) CDXLNode(m_mp, GPOS_NEW(m_mp) CDXLScalarWindowFrameEdge( @@ -1630,20 +1615,21 @@ CTranslatorScalarToDXL::TranslateWindowFrameToDXL( m_mp, false /* fLeading */, trailing_boundary)); // translate the lead and trail value - if (nullptr != end_offset) + if (nullptr != start_offset) { lead_edge->AddChild(TranslateWindowFrameEdgeToDXL( - end_offset, var_colid_mapping, new_scalar_proj_list)); + start_offset, var_colid_mapping, new_scalar_proj_list)); } - if (nullptr != start_offset) + if (nullptr != end_offset) { trail_edge->AddChild(TranslateWindowFrameEdgeToDXL( - start_offset, var_colid_mapping, new_scalar_proj_list)); + end_offset, var_colid_mapping, new_scalar_proj_list)); } - CDXLWindowFrame *window_frame_dxl = GPOS_NEW(m_mp) - CDXLWindowFrame(frame_spec, strategy, lead_edge, trail_edge); + CDXLWindowFrame *window_frame_dxl = GPOS_NEW(m_mp) CDXLWindowFrame( + frame_spec, strategy, lead_edge, trail_edge, start_in_range_func, + end_in_range_func, in_range_coll, in_range_asc, in_range_nulls_first); return window_frame_dxl; } diff --git a/src/backend/gporca/data/dxl/minidump/WindowFrameExcludeCurrentRow.mdp b/src/backend/gporca/data/dxl/minidump/WindowFrameExcludeCurrentRow.mdp new file mode 100644 index 0000000000..07e5be0a96 --- /dev/null +++ b/src/backend/gporca/data/dxl/minidump/WindowFrameExcludeCurrentRow.mdp @@ -0,0 +1,237 @@ +<?xml version="1.0" encoding="UTF-8"?> +<dxl:DXLMessage xmlns:dxl="http://greenplum.com/dxl/2010/12/";> + <dxl:Comment><![CDATA[ + Objective: This is a basic test that ORCA can generate a plan for a query + containing a window frame ROWS with EXCLUDE CUURENT ROW option. + + EXPLAIN SELECT SUM(a) OVER (ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW), a + FROM (values (1), (2), (3), (4), (5), (6)) v(a); + QUERY PLAN + ----------------------------------------------------------------------- + WindowAgg (cost=0.00..0.00 rows=6 width=12) + Order By: column1 + -> Sort (cost=0.00..0.00 rows=6 width=4) + Sort Key: column1 + -> Values Scan on "Values" (cost=0.00..0.00 rows=6 width=4) + 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,103003,103014,103022,103026,103027,103029,103033,103038,103040,104002,104003,104004,104005,105000,106000"/> + </dxl:OptimizerConfig> + <dxl:Metadata SystemIds="0.GPDB"> + <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.20.1.0" Name="Int8" IsRedistributable="true" IsHashable="true" IsMergeJoinable="true" IsComposite="false" IsTextRelated="false" IsFixedLength="true" Length="8" PassByValue="true"> + <dxl:DistrOpfamily Mdid="0.1977.1.0"/> + <dxl:LegacyDistrOpfamily Mdid="0.7100.1.0"/> + <dxl:EqualityOp Mdid="0.410.1.0"/> + <dxl:InequalityOp Mdid="0.411.1.0"/> + <dxl:LessThanOp Mdid="0.412.1.0"/> + <dxl:LessThanEqualsOp Mdid="0.414.1.0"/> + <dxl:GreaterThanOp Mdid="0.413.1.0"/> + <dxl:GreaterThanEqualsOp Mdid="0.415.1.0"/> + <dxl:ComparisonOp Mdid="0.351.1.0"/> + <dxl:ArrayType Mdid="0.1016.1.0"/> + <dxl:MinAgg Mdid="0.2131.1.0"/> + <dxl:MaxAgg Mdid="0.2115.1.0"/> + <dxl:AvgAgg Mdid="0.2100.1.0"/> + <dxl:SumAgg Mdid="0.2107.1.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:GPDBAgg Mdid="0.2108.1.0" Name="sum" IsSplittable="true" HashAggCapable="true"> + <dxl:ResultType Mdid="0.20.1.0"/> + <dxl:IntermediateResultType Mdid="0.20.1.0"/> + </dxl:GPDBAgg> + <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:Metadata> + <dxl:Query> + <dxl:OutputColumns> + <dxl:Ident ColId="7" ColName="sum" TypeMdid="0.20.1.0"/> + <dxl:Ident ColId="1" ColName="a" TypeMdid="0.23.1.0"/> + </dxl:OutputColumns> + <dxl:CTEList/> + <dxl:LogicalWindow> + <dxl:WindowSpecList> + <dxl:WindowSpec PartitionColumns=""> + <dxl:SortingColumnList> + <dxl:SortingColumn ColId="1" SortOperatorMdid="0.97.1.0" SortOperatorName="<" SortNullsFirst="false"/> + </dxl:SortingColumnList> + <dxl:WindowFrame FrameSpec="Row" ExclusionStrategy="CurrentRow" StartInRange="0" EndInRange="0" InRangeColl="0" InRangeAsc="false" InRangeNullsFirst="false"> + <dxl:TrailingEdge TrailingBoundary="BoundedFollowing"> + <dxl:ConstValue TypeMdid="0.20.1.0" Value="1"/> + </dxl:TrailingEdge> + <dxl:LeadingEdge LeadingBoundary="BoundedPreceding"> + <dxl:ConstValue TypeMdid="0.20.1.0" Value="1"/> + </dxl:LeadingEdge> + </dxl:WindowFrame> + </dxl:WindowSpec> + </dxl:WindowSpecList> + <dxl:ProjList> + <dxl:ProjElem ColId="7" Alias="sum"> + <dxl:WindowFunc Mdid="0.2108.1.0" TypeMdid="0.20.1.0" Distinct="false" WindowStarArg="false" WindowSimpleAgg="true" WindowStrategy="Immediate" WinSpecPos="0"> + <dxl:Ident ColId="1" ColName="column1" TypeMdid="0.23.1.0"/> + </dxl:WindowFunc> + </dxl:ProjElem> + <dxl:ProjElem ColId="1" Alias="a"> + <dxl:Ident ColId="1" ColName="column1" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:LogicalConstTable> + <dxl:Columns> + <dxl:Column ColId="1" Attno="1" ColName="column1" TypeMdid="0.23.1.0"/> + </dxl:Columns> + <dxl:ConstTuple> + <dxl:Datum TypeMdid="0.23.1.0" Value="1"/> + </dxl:ConstTuple> + <dxl:ConstTuple> + <dxl:Datum TypeMdid="0.23.1.0" Value="2"/> + </dxl:ConstTuple> + <dxl:ConstTuple> + <dxl:Datum TypeMdid="0.23.1.0" Value="3"/> + </dxl:ConstTuple> + <dxl:ConstTuple> + <dxl:Datum TypeMdid="0.23.1.0" Value="4"/> + </dxl:ConstTuple> + <dxl:ConstTuple> + <dxl:Datum TypeMdid="0.23.1.0" Value="5"/> + </dxl:ConstTuple> + <dxl:ConstTuple> + <dxl:Datum TypeMdid="0.23.1.0" Value="6"/> + </dxl:ConstTuple> + </dxl:LogicalConstTable> + </dxl:LogicalWindow> + </dxl:Query> + <dxl:Plan Id="0" SpaceSize="0"> + <dxl:Window PartitionColumns=""> + <dxl:Properties> + <dxl:Cost StartupCost="0" TotalCost="0.000400" Rows="6.000000" Width="12"/> + </dxl:Properties> + <dxl:ProjList> + <dxl:ProjElem ColId="1" Alias="sum"> + <dxl:WindowFunc Mdid="0.2108.1.0" TypeMdid="0.20.1.0" Distinct="false" WindowStarArg="false" WindowSimpleAgg="true" WindowStrategy="Immediate" WinSpecPos="0"> + <dxl:Ident ColId="0" ColName="column1" TypeMdid="0.23.1.0"/> + </dxl:WindowFunc> + </dxl:ProjElem> + <dxl:ProjElem ColId="0" Alias="a"> + <dxl:Ident ColId="0" ColName="column1" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:Filter/> + <dxl:Sort SortDiscardDuplicates="false"> + <dxl:Properties> + <dxl:Cost StartupCost="0" TotalCost="0.000376" Rows="6.000000" Width="4"/> + </dxl:Properties> + <dxl:ProjList> + <dxl:ProjElem ColId="0" Alias="column1"> + <dxl:Ident ColId="0" ColName="column1" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:Filter/> + <dxl:SortingColumnList> + <dxl:SortingColumn ColId="0" SortOperatorMdid="0.97.1.0" SortOperatorName="<" SortNullsFirst="false"/> + </dxl:SortingColumnList> + <dxl:LimitCount/> + <dxl:LimitOffset/> + <dxl:Values> + <dxl:Properties> + <dxl:Cost StartupCost="0" TotalCost="0.000024" Rows="6.000000" Width="4"/> + </dxl:Properties> + <dxl:ProjList> + <dxl:ProjElem ColId="0" Alias="column1"> + <dxl:Ident ColId="0" ColName="column1" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:ValuesList> + <dxl:ConstValue TypeMdid="0.23.1.0" Value="1"/> + </dxl:ValuesList> + <dxl:ValuesList> + <dxl:ConstValue TypeMdid="0.23.1.0" Value="2"/> + </dxl:ValuesList> + <dxl:ValuesList> + <dxl:ConstValue TypeMdid="0.23.1.0" Value="3"/> + </dxl:ValuesList> + <dxl:ValuesList> + <dxl:ConstValue TypeMdid="0.23.1.0" Value="4"/> + </dxl:ValuesList> + <dxl:ValuesList> + <dxl:ConstValue TypeMdid="0.23.1.0" Value="5"/> + </dxl:ValuesList> + <dxl:ValuesList> + <dxl:ConstValue TypeMdid="0.23.1.0" Value="6"/> + </dxl:ValuesList> + </dxl:Values> + </dxl:Sort> + <dxl:WindowKeyList> + <dxl:WindowKey> + <dxl:SortingColumnList> + <dxl:SortingColumn ColId="0" SortOperatorMdid="0.97.1.0" SortOperatorName="<" SortNullsFirst="false"/> + </dxl:SortingColumnList> + <dxl:WindowFrame FrameSpec="Row" ExclusionStrategy="CurrentRow" StartInRange="0" EndInRange="0" InRangeColl="0" InRangeAsc="false" InRangeNullsFirst="false"> + <dxl:TrailingEdge TrailingBoundary="BoundedFollowing"> + <dxl:ConstValue TypeMdid="0.20.1.0" Value="1"/> + </dxl:TrailingEdge> + <dxl:LeadingEdge LeadingBoundary="BoundedPreceding"> + <dxl:ConstValue TypeMdid="0.20.1.0" Value="1"/> + </dxl:LeadingEdge> + </dxl:WindowFrame> + </dxl:WindowKey> + </dxl:WindowKeyList> + </dxl:Window> + </dxl:Plan> + </dxl:Thread> +</dxl:DXLMessage> diff --git a/src/backend/gporca/data/dxl/minidump/WindowFrameExcludeGroup.mdp b/src/backend/gporca/data/dxl/minidump/WindowFrameExcludeGroup.mdp new file mode 100644 index 0000000000..0b0eb61621 --- /dev/null +++ b/src/backend/gporca/data/dxl/minidump/WindowFrameExcludeGroup.mdp @@ -0,0 +1,231 @@ +<?xml version="1.0" encoding="UTF-8"?> +<dxl:DXLMessage xmlns:dxl="http://greenplum.com/dxl/2010/12/";> + <dxl:Comment><![CDATA[ + Objective: This is a basic test that ORCA can generate a plan for a query + containing a window frame ROWS with EXCLUDE GROUP option. + + EXPLAIN SELECT SUM(a) OVER (ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE GROUP), a + FROM (values (1), (2), (3), (4), (5), (6)) v(a); + QUERY PLAN + --------------------------------------- + WindowAgg + Order By: column1 + -> Sort + Sort Key: column1 + -> Values Scan on "Values" + 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,103003,103014,103022,103026,103027,103029,103033,103038,103040,104002,104003,104004,104005,105000,106000"/> + </dxl:OptimizerConfig> + <dxl:Metadata SystemIds="0.GPDB"> + <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.20.1.0" Name="Int8" IsRedistributable="true" IsHashable="true" IsMergeJoinable="true" IsComposite="false" IsTextRelated="false" IsFixedLength="true" Length="8" PassByValue="true"> + <dxl:DistrOpfamily Mdid="0.1977.1.0"/> + <dxl:LegacyDistrOpfamily Mdid="0.7100.1.0"/> + <dxl:EqualityOp Mdid="0.410.1.0"/> + <dxl:InequalityOp Mdid="0.411.1.0"/> + <dxl:LessThanOp Mdid="0.412.1.0"/> + <dxl:LessThanEqualsOp Mdid="0.414.1.0"/> + <dxl:GreaterThanOp Mdid="0.413.1.0"/> + <dxl:GreaterThanEqualsOp Mdid="0.415.1.0"/> + <dxl:ComparisonOp Mdid="0.351.1.0"/> + <dxl:ArrayType Mdid="0.1016.1.0"/> + <dxl:MinAgg Mdid="0.2131.1.0"/> + <dxl:MaxAgg Mdid="0.2115.1.0"/> + <dxl:AvgAgg Mdid="0.2100.1.0"/> + <dxl:SumAgg Mdid="0.2107.1.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:GPDBAgg Mdid="0.2108.1.0" Name="sum" IsSplittable="true" HashAggCapable="true"> + <dxl:ResultType Mdid="0.20.1.0"/> + <dxl:IntermediateResultType Mdid="0.20.1.0"/> + </dxl:GPDBAgg> + <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:Metadata> + <dxl:Query> + <dxl:OutputColumns> + <dxl:Ident ColId="6" ColName="sum" TypeMdid="0.20.1.0"/> + <dxl:Ident ColId="1" ColName="a" TypeMdid="0.23.1.0"/> + </dxl:OutputColumns> + <dxl:CTEList/> + <dxl:LogicalWindow> + <dxl:WindowSpecList> + <dxl:WindowSpec PartitionColumns=""> + <dxl:SortingColumnList> + <dxl:SortingColumn ColId="1" SortOperatorMdid="0.97.1.0" SortOperatorName="<" SortNullsFirst="false"/> + </dxl:SortingColumnList> + <dxl:WindowFrame FrameSpec="Row" ExclusionStrategy="Group" StartInRange="0" EndInRange="0" InRangeColl="0" InRangeAsc="false" InRangeNullsFirst="false"> + <dxl:TrailingEdge TrailingBoundary="BoundedFollowing"> + <dxl:ConstValue TypeMdid="0.20.1.0" Value="1"/> + </dxl:TrailingEdge> + <dxl:LeadingEdge LeadingBoundary="BoundedPreceding"> + <dxl:ConstValue TypeMdid="0.20.1.0" Value="1"/> + </dxl:LeadingEdge> + </dxl:WindowFrame> + </dxl:WindowSpec> + </dxl:WindowSpecList> + <dxl:ProjList> + <dxl:ProjElem ColId="6" Alias="sum"> + <dxl:WindowFunc Mdid="0.2108.1.0" TypeMdid="0.20.1.0" Distinct="false" WindowStarArg="false" WindowSimpleAgg="true" WindowStrategy="Immediate" WinSpecPos="0"> + <dxl:Ident ColId="1" ColName="column1" TypeMdid="0.23.1.0"/> + </dxl:WindowFunc> + </dxl:ProjElem> + <dxl:ProjElem ColId="1" Alias="a"> + <dxl:Ident ColId="1" ColName="column1" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:LogicalConstTable> + <dxl:Columns> + <dxl:Column ColId="1" Attno="1" ColName="column1" TypeMdid="0.23.1.0"/> + </dxl:Columns> + <dxl:ConstTuple> + <dxl:Datum TypeMdid="0.23.1.0" Value="1"/> + </dxl:ConstTuple> + <dxl:ConstTuple> + <dxl:Datum TypeMdid="0.23.1.0" Value="2"/> + </dxl:ConstTuple> + <dxl:ConstTuple> + <dxl:Datum TypeMdid="0.23.1.0" Value="2"/> + </dxl:ConstTuple> + <dxl:ConstTuple> + <dxl:Datum TypeMdid="0.23.1.0" Value="2"/> + </dxl:ConstTuple> + <dxl:ConstTuple> + <dxl:Datum TypeMdid="0.23.1.0" Value="3"/> + </dxl:ConstTuple> + </dxl:LogicalConstTable> + </dxl:LogicalWindow> + </dxl:Query> + <dxl:Plan Id="0" SpaceSize="0"> + <dxl:Window PartitionColumns=""> + <dxl:Properties> + <dxl:Cost StartupCost="0" TotalCost="0.000303" Rows="5.000000" Width="12"/> + </dxl:Properties> + <dxl:ProjList> + <dxl:ProjElem ColId="1" Alias="sum"> + <dxl:WindowFunc Mdid="0.2108.1.0" TypeMdid="0.20.1.0" Distinct="false" WindowStarArg="false" WindowSimpleAgg="true" WindowStrategy="Immediate" WinSpecPos="0"> + <dxl:Ident ColId="0" ColName="column1" TypeMdid="0.23.1.0"/> + </dxl:WindowFunc> + </dxl:ProjElem> + <dxl:ProjElem ColId="0" Alias="a"> + <dxl:Ident ColId="0" ColName="column1" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:Filter/> + <dxl:Sort SortDiscardDuplicates="false"> + <dxl:Properties> + <dxl:Cost StartupCost="0" TotalCost="0.000283" Rows="5.000000" Width="4"/> + </dxl:Properties> + <dxl:ProjList> + <dxl:ProjElem ColId="0" Alias="column1"> + <dxl:Ident ColId="0" ColName="column1" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:Filter/> + <dxl:SortingColumnList> + <dxl:SortingColumn ColId="0" SortOperatorMdid="0.97.1.0" SortOperatorName="<" SortNullsFirst="false"/> + </dxl:SortingColumnList> + <dxl:LimitCount/> + <dxl:LimitOffset/> + <dxl:Values> + <dxl:Properties> + <dxl:Cost StartupCost="0" TotalCost="0.000020" Rows="5.000000" Width="4"/> + </dxl:Properties> + <dxl:ProjList> + <dxl:ProjElem ColId="0" Alias="column1"> + <dxl:Ident ColId="0" ColName="column1" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:ValuesList> + <dxl:ConstValue TypeMdid="0.23.1.0" Value="1"/> + </dxl:ValuesList> + <dxl:ValuesList> + <dxl:ConstValue TypeMdid="0.23.1.0" Value="2"/> + </dxl:ValuesList> + <dxl:ValuesList> + <dxl:ConstValue TypeMdid="0.23.1.0" Value="2"/> + </dxl:ValuesList> + <dxl:ValuesList> + <dxl:ConstValue TypeMdid="0.23.1.0" Value="2"/> + </dxl:ValuesList> + <dxl:ValuesList> + <dxl:ConstValue TypeMdid="0.23.1.0" Value="3"/> + </dxl:ValuesList> + </dxl:Values> + </dxl:Sort> + <dxl:WindowKeyList> + <dxl:WindowKey> + <dxl:SortingColumnList> + <dxl:SortingColumn ColId="0" SortOperatorMdid="0.97.1.0" SortOperatorName="<" SortNullsFirst="false"/> + </dxl:SortingColumnList> + <dxl:WindowFrame FrameSpec="Row" ExclusionStrategy="Group" StartInRange="0" EndInRange="0" InRangeColl="0" InRangeAsc="false" InRangeNullsFirst="false"> + <dxl:TrailingEdge TrailingBoundary="BoundedFollowing"> + <dxl:ConstValue TypeMdid="0.20.1.0" Value="1"/> + </dxl:TrailingEdge> + <dxl:LeadingEdge LeadingBoundary="BoundedPreceding"> + <dxl:ConstValue TypeMdid="0.20.1.0" Value="1"/> + </dxl:LeadingEdge> + </dxl:WindowFrame> + </dxl:WindowKey> + </dxl:WindowKeyList> + </dxl:Window> + </dxl:Plan> + </dxl:Thread> +</dxl:DXLMessage> diff --git a/src/backend/gporca/data/dxl/minidump/WindowFrameExcludeTies.mdp b/src/backend/gporca/data/dxl/minidump/WindowFrameExcludeTies.mdp new file mode 100644 index 0000000000..aa1ad22110 --- /dev/null +++ b/src/backend/gporca/data/dxl/minidump/WindowFrameExcludeTies.mdp @@ -0,0 +1,231 @@ +<?xml version="1.0" encoding="UTF-8"?> +<dxl:DXLMessage xmlns:dxl="http://greenplum.com/dxl/2010/12/";> + <dxl:Comment><![CDATA[ + Objective: This is a basic test that ORCA can generate a plan for a query + containing a window frame ROWS with EXCLUDE TIES option. + + EXPLAIN SELECT SUM(a) OVER (ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE TIES), a + FROM (values (1), (2), (3), (4), (5), (6)) v(a); + QUERY PLAN + --------------------------------------- + WindowAgg + Order By: column1 + -> Sort + Sort Key: column1 + -> Values Scan on "Values" + 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,103003,103014,103022,103026,103027,103029,103033,103038,103040,104002,104003,104004,104005,105000,106000"/> + </dxl:OptimizerConfig> + <dxl:Metadata SystemIds="0.GPDB"> + <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.20.1.0" Name="Int8" IsRedistributable="true" IsHashable="true" IsMergeJoinable="true" IsComposite="false" IsTextRelated="false" IsFixedLength="true" Length="8" PassByValue="true"> + <dxl:DistrOpfamily Mdid="0.1977.1.0"/> + <dxl:LegacyDistrOpfamily Mdid="0.7100.1.0"/> + <dxl:EqualityOp Mdid="0.410.1.0"/> + <dxl:InequalityOp Mdid="0.411.1.0"/> + <dxl:LessThanOp Mdid="0.412.1.0"/> + <dxl:LessThanEqualsOp Mdid="0.414.1.0"/> + <dxl:GreaterThanOp Mdid="0.413.1.0"/> + <dxl:GreaterThanEqualsOp Mdid="0.415.1.0"/> + <dxl:ComparisonOp Mdid="0.351.1.0"/> + <dxl:ArrayType Mdid="0.1016.1.0"/> + <dxl:MinAgg Mdid="0.2131.1.0"/> + <dxl:MaxAgg Mdid="0.2115.1.0"/> + <dxl:AvgAgg Mdid="0.2100.1.0"/> + <dxl:SumAgg Mdid="0.2107.1.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:GPDBAgg Mdid="0.2108.1.0" Name="sum" IsSplittable="true" HashAggCapable="true"> + <dxl:ResultType Mdid="0.20.1.0"/> + <dxl:IntermediateResultType Mdid="0.20.1.0"/> + </dxl:GPDBAgg> + <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:Metadata> + <dxl:Query> + <dxl:OutputColumns> + <dxl:Ident ColId="6" ColName="sum" TypeMdid="0.20.1.0"/> + <dxl:Ident ColId="1" ColName="a" TypeMdid="0.23.1.0"/> + </dxl:OutputColumns> + <dxl:CTEList/> + <dxl:LogicalWindow> + <dxl:WindowSpecList> + <dxl:WindowSpec PartitionColumns=""> + <dxl:SortingColumnList> + <dxl:SortingColumn ColId="1" SortOperatorMdid="0.97.1.0" SortOperatorName="<" SortNullsFirst="false"/> + </dxl:SortingColumnList> + <dxl:WindowFrame FrameSpec="Row" ExclusionStrategy="Ties" StartInRange="0" EndInRange="0" InRangeColl="0" InRangeAsc="false" InRangeNullsFirst="false"> + <dxl:TrailingEdge TrailingBoundary="BoundedFollowing"> + <dxl:ConstValue TypeMdid="0.20.1.0" Value="1"/> + </dxl:TrailingEdge> + <dxl:LeadingEdge LeadingBoundary="BoundedPreceding"> + <dxl:ConstValue TypeMdid="0.20.1.0" Value="1"/> + </dxl:LeadingEdge> + </dxl:WindowFrame> + </dxl:WindowSpec> + </dxl:WindowSpecList> + <dxl:ProjList> + <dxl:ProjElem ColId="6" Alias="sum"> + <dxl:WindowFunc Mdid="0.2108.1.0" TypeMdid="0.20.1.0" Distinct="false" WindowStarArg="false" WindowSimpleAgg="true" WindowStrategy="Immediate" WinSpecPos="0"> + <dxl:Ident ColId="1" ColName="column1" TypeMdid="0.23.1.0"/> + </dxl:WindowFunc> + </dxl:ProjElem> + <dxl:ProjElem ColId="1" Alias="a"> + <dxl:Ident ColId="1" ColName="column1" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:LogicalConstTable> + <dxl:Columns> + <dxl:Column ColId="1" Attno="1" ColName="column1" TypeMdid="0.23.1.0"/> + </dxl:Columns> + <dxl:ConstTuple> + <dxl:Datum TypeMdid="0.23.1.0" Value="1"/> + </dxl:ConstTuple> + <dxl:ConstTuple> + <dxl:Datum TypeMdid="0.23.1.0" Value="2"/> + </dxl:ConstTuple> + <dxl:ConstTuple> + <dxl:Datum TypeMdid="0.23.1.0" Value="2"/> + </dxl:ConstTuple> + <dxl:ConstTuple> + <dxl:Datum TypeMdid="0.23.1.0" Value="2"/> + </dxl:ConstTuple> + <dxl:ConstTuple> + <dxl:Datum TypeMdid="0.23.1.0" Value="3"/> + </dxl:ConstTuple> + </dxl:LogicalConstTable> + </dxl:LogicalWindow> + </dxl:Query> + <dxl:Plan Id="0" SpaceSize="0"> + <dxl:Window PartitionColumns=""> + <dxl:Properties> + <dxl:Cost StartupCost="0" TotalCost="0.000303" Rows="5.000000" Width="12"/> + </dxl:Properties> + <dxl:ProjList> + <dxl:ProjElem ColId="1" Alias="sum"> + <dxl:WindowFunc Mdid="0.2108.1.0" TypeMdid="0.20.1.0" Distinct="false" WindowStarArg="false" WindowSimpleAgg="true" WindowStrategy="Immediate" WinSpecPos="0"> + <dxl:Ident ColId="0" ColName="column1" TypeMdid="0.23.1.0"/> + </dxl:WindowFunc> + </dxl:ProjElem> + <dxl:ProjElem ColId="0" Alias="a"> + <dxl:Ident ColId="0" ColName="column1" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:Filter/> + <dxl:Sort SortDiscardDuplicates="false"> + <dxl:Properties> + <dxl:Cost StartupCost="0" TotalCost="0.000283" Rows="5.000000" Width="4"/> + </dxl:Properties> + <dxl:ProjList> + <dxl:ProjElem ColId="0" Alias="column1"> + <dxl:Ident ColId="0" ColName="column1" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:Filter/> + <dxl:SortingColumnList> + <dxl:SortingColumn ColId="0" SortOperatorMdid="0.97.1.0" SortOperatorName="<" SortNullsFirst="false"/> + </dxl:SortingColumnList> + <dxl:LimitCount/> + <dxl:LimitOffset/> + <dxl:Values> + <dxl:Properties> + <dxl:Cost StartupCost="0" TotalCost="0.000020" Rows="5.000000" Width="4"/> + </dxl:Properties> + <dxl:ProjList> + <dxl:ProjElem ColId="0" Alias="column1"> + <dxl:Ident ColId="0" ColName="column1" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:ValuesList> + <dxl:ConstValue TypeMdid="0.23.1.0" Value="1"/> + </dxl:ValuesList> + <dxl:ValuesList> + <dxl:ConstValue TypeMdid="0.23.1.0" Value="2"/> + </dxl:ValuesList> + <dxl:ValuesList> + <dxl:ConstValue TypeMdid="0.23.1.0" Value="2"/> + </dxl:ValuesList> + <dxl:ValuesList> + <dxl:ConstValue TypeMdid="0.23.1.0" Value="2"/> + </dxl:ValuesList> + <dxl:ValuesList> + <dxl:ConstValue TypeMdid="0.23.1.0" Value="3"/> + </dxl:ValuesList> + </dxl:Values> + </dxl:Sort> + <dxl:WindowKeyList> + <dxl:WindowKey> + <dxl:SortingColumnList> + <dxl:SortingColumn ColId="0" SortOperatorMdid="0.97.1.0" SortOperatorName="<" SortNullsFirst="false"/> + </dxl:SortingColumnList> + <dxl:WindowFrame FrameSpec="Row" ExclusionStrategy="Ties" StartInRange="0" EndInRange="0" InRangeColl="0" InRangeAsc="false" InRangeNullsFirst="false"> + <dxl:TrailingEdge TrailingBoundary="BoundedFollowing"> + <dxl:ConstValue TypeMdid="0.20.1.0" Value="1"/> + </dxl:TrailingEdge> + <dxl:LeadingEdge LeadingBoundary="BoundedPreceding"> + <dxl:ConstValue TypeMdid="0.20.1.0" Value="1"/> + </dxl:LeadingEdge> + </dxl:WindowFrame> + </dxl:WindowKey> + </dxl:WindowKeyList> + </dxl:Window> + </dxl:Plan> + </dxl:Thread> +</dxl:DXLMessage> diff --git a/src/backend/gporca/data/dxl/minidump/WindowFrameGroups.mdp b/src/backend/gporca/data/dxl/minidump/WindowFrameGroups.mdp new file mode 100644 index 0000000000..c4cdde6aca --- /dev/null +++ b/src/backend/gporca/data/dxl/minidump/WindowFrameGroups.mdp @@ -0,0 +1,207 @@ +<?xml version="1.0" encoding="UTF-8"?> +<dxl:DXLMessage xmlns:dxl="http://greenplum.com/dxl/2010/12/";> + <dxl:Comment><![CDATA[ + Objective: This is a basic test that ORCA can generate a plan for a query + containing a window frame GROUPS options. + + EXPLAIN SELECT i, SUM(i) OVER (ORDER BY i GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_rows FROM generate_series(1, 10) i; + QUERY PLAN + ---------------------------------------------- + WindowAgg + Order By: generate_series + -> Sort + Sort Key: generate_series + -> Function Scan on generate_series + 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,103003,103014,103022,103026,103027,103029,103033,103038,103040,104002,104003,104004,104005,105000,106000"/> + </dxl:OptimizerConfig> + <dxl:Metadata SystemIds="0.GPDB"> + <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.20.1.0" Name="Int8" IsRedistributable="true" IsHashable="true" IsMergeJoinable="true" IsComposite="false" IsTextRelated="false" IsFixedLength="true" Length="8" PassByValue="true"> + <dxl:DistrOpfamily Mdid="0.1977.1.0"/> + <dxl:LegacyDistrOpfamily Mdid="0.7100.1.0"/> + <dxl:EqualityOp Mdid="0.410.1.0"/> + <dxl:InequalityOp Mdid="0.411.1.0"/> + <dxl:LessThanOp Mdid="0.412.1.0"/> + <dxl:LessThanEqualsOp Mdid="0.414.1.0"/> + <dxl:GreaterThanOp Mdid="0.413.1.0"/> + <dxl:GreaterThanEqualsOp Mdid="0.415.1.0"/> + <dxl:ComparisonOp Mdid="0.351.1.0"/> + <dxl:ArrayType Mdid="0.1016.1.0"/> + <dxl:MinAgg Mdid="0.2131.1.0"/> + <dxl:MaxAgg Mdid="0.2115.1.0"/> + <dxl:AvgAgg Mdid="0.2100.1.0"/> + <dxl:SumAgg Mdid="0.2107.1.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:GPDBFunc Mdid="0.1067.1.0" Name="generate_series" ReturnsSet="true" Stability="Immutable" DataAccess="NoSQL" IsStrict="true" IsNDVPreserving="false" IsAllowedForPS="false"> + <dxl:ResultType Mdid="0.23.1.0"/> + </dxl:GPDBFunc> + <dxl:GPDBAgg Mdid="0.2108.1.0" Name="sum" IsSplittable="true" HashAggCapable="true"> + <dxl:ResultType Mdid="0.20.1.0"/> + <dxl:IntermediateResultType Mdid="0.20.1.0"/> + </dxl:GPDBAgg> + <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:Metadata> + <dxl:Query> + <dxl:OutputColumns> + <dxl:Ident ColId="1" ColName="i" TypeMdid="0.23.1.0"/> + <dxl:Ident ColId="2" ColName="sum_rows" TypeMdid="0.20.1.0"/> + </dxl:OutputColumns> + <dxl:CTEList/> + <dxl:LogicalWindow> + <dxl:WindowSpecList> + <dxl:WindowSpec PartitionColumns=""> + <dxl:SortingColumnList> + <dxl:SortingColumn ColId="1" SortOperatorMdid="0.97.1.0" SortOperatorName="<" SortNullsFirst="false"/> + </dxl:SortingColumnList> + <dxl:WindowFrame FrameSpec="Groups" ExclusionStrategy="Nulls" StartInRange="0" EndInRange="0" InRangeColl="0" InRangeAsc="false" InRangeNullsFirst="false"> + <dxl:TrailingEdge TrailingBoundary="BoundedFollowing"> + <dxl:ConstValue TypeMdid="0.20.1.0" Value="1"/> + </dxl:TrailingEdge> + <dxl:LeadingEdge LeadingBoundary="BoundedPreceding"> + <dxl:ConstValue TypeMdid="0.20.1.0" Value="1"/> + </dxl:LeadingEdge> + </dxl:WindowFrame> + </dxl:WindowSpec> + </dxl:WindowSpecList> + <dxl:ProjList> + <dxl:ProjElem ColId="1" Alias="i"> + <dxl:Ident ColId="1" ColName="generate_series" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + <dxl:ProjElem ColId="2" Alias="sum_rows"> + <dxl:WindowFunc Mdid="0.2108.1.0" TypeMdid="0.20.1.0" Distinct="false" WindowStarArg="false" WindowSimpleAgg="true" WindowStrategy="Immediate" WinSpecPos="0"> + <dxl:Ident ColId="1" ColName="generate_series" TypeMdid="0.23.1.0"/> + </dxl:WindowFunc> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:LogicalTVF FuncId="0.1067.1.0" Name="generate_series" TypeMdid="0.23.1.0"> + <dxl:Columns> + <dxl:Column ColId="1" Attno="1" ColName="generate_series" TypeMdid="0.23.1.0"/> + </dxl:Columns> + <dxl:ConstValue TypeMdid="0.23.1.0" Value="1"/> + <dxl:ConstValue TypeMdid="0.23.1.0" Value="10"/> + </dxl:LogicalTVF> + </dxl:LogicalWindow> + </dxl:Query> + <dxl:Plan Id="0" SpaceSize="0"> + <dxl:Window PartitionColumns=""> + <dxl:Properties> + <dxl:Cost StartupCost="0" TotalCost="0.234024" Rows="1000.000000" Width="12"/> + </dxl:Properties> + <dxl:ProjList> + <dxl:ProjElem ColId="0" Alias="i"> + <dxl:Ident ColId="0" ColName="generate_series" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + <dxl:ProjElem ColId="1" Alias="sum_rows"> + <dxl:WindowFunc Mdid="0.2108.1.0" TypeMdid="0.20.1.0" Distinct="false" WindowStarArg="false" WindowSimpleAgg="true" WindowStrategy="Immediate" WinSpecPos="0"> + <dxl:Ident ColId="0" ColName="generate_series" TypeMdid="0.23.1.0"/> + </dxl:WindowFunc> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:Filter/> + <dxl:Sort SortDiscardDuplicates="false"> + <dxl:Properties> + <dxl:Cost StartupCost="0" TotalCost="0.230024" Rows="1000.000000" Width="4"/> + </dxl:Properties> + <dxl:ProjList> + <dxl:ProjElem ColId="0" Alias="generate_series"> + <dxl:Ident ColId="0" ColName="generate_series" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:Filter/> + <dxl:SortingColumnList> + <dxl:SortingColumn ColId="0" SortOperatorMdid="0.97.1.0" SortOperatorName="<" SortNullsFirst="false"/> + </dxl:SortingColumnList> + <dxl:LimitCount/> + <dxl:LimitOffset/> + <dxl:TableValuedFunction FuncId="0.1067.1.0" Name="generate_series" TypeMdid="0.23.1.0"> + <dxl:Properties> + <dxl:Cost StartupCost="0" TotalCost="0.004000" Rows="1000.000000" Width="4"/> + </dxl:Properties> + <dxl:ProjList> + <dxl:ProjElem ColId="0" Alias="generate_series"> + <dxl:Ident ColId="0" ColName="generate_series" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:ConstValue TypeMdid="0.23.1.0" Value="1"/> + <dxl:ConstValue TypeMdid="0.23.1.0" Value="10"/> + </dxl:TableValuedFunction> + </dxl:Sort> + <dxl:WindowKeyList> + <dxl:WindowKey> + <dxl:SortingColumnList> + <dxl:SortingColumn ColId="0" SortOperatorMdid="0.97.1.0" SortOperatorName="<" SortNullsFirst="false"/> + </dxl:SortingColumnList> + <dxl:WindowFrame FrameSpec="Groups" ExclusionStrategy="Nulls" StartInRange="0" EndInRange="0" InRangeColl="0" InRangeAsc="false" InRangeNullsFirst="false"> + <dxl:TrailingEdge TrailingBoundary="BoundedFollowing"> + <dxl:ConstValue TypeMdid="0.20.1.0" Value="1"/> + </dxl:TrailingEdge> + <dxl:LeadingEdge LeadingBoundary="BoundedPreceding"> + <dxl:ConstValue TypeMdid="0.20.1.0" Value="1"/> + </dxl:LeadingEdge> + </dxl:WindowFrame> + </dxl:WindowKey> + </dxl:WindowKeyList> + </dxl:Window> + </dxl:Plan> + </dxl:Thread> +</dxl:DXLMessage> diff --git a/src/backend/gporca/data/dxl/minidump/WindowFrameRangePrecedingAndFollowing.mdp b/src/backend/gporca/data/dxl/minidump/WindowFrameRangePrecedingAndFollowing.mdp new file mode 100644 index 0000000000..913ab53986 --- /dev/null +++ b/src/backend/gporca/data/dxl/minidump/WindowFrameRangePrecedingAndFollowing.mdp @@ -0,0 +1,263 @@ +<?xml version="1.0" encoding="UTF-8"?> +<dxl:DXLMessage xmlns:dxl="http://greenplum.com/dxl/2010/12/";> + <dxl:Comment><![CDATA[ + Objective: This is a basic test that ORCA can generate a plan for a query + containing a window frame RANGE with PRECEEDING and FOLLOWING options. + + EXPLAIN SELECT SUM(a) OVER (ORDER BY b RANGE BETWEEN 1 PRECEDING AND 2 FOLLOWING), a, b + FROM (values (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6)) v(a, b); + QUERY PLAN + ----------------------------------------------------------------------- + WindowAgg (cost=0.00..0.00 rows=6 width=16) + Order By: column2 + -> Sort (cost=0.00..0.00 rows=6 width=8) + Sort Key: column2 + -> Values Scan on "Values" (cost=0.00..0.00 rows=6 width=8) + 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,103003,103014,103022,103026,103027,103029,103033,103038,103040,104002,104003,104004,104005,105000,106000"/> + </dxl:OptimizerConfig> + <dxl:Metadata SystemIds="0.GPDB"> + <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.20.1.0" Name="Int8" IsRedistributable="true" IsHashable="true" IsMergeJoinable="true" IsComposite="false" IsTextRelated="false" IsFixedLength="true" Length="8" PassByValue="true"> + <dxl:DistrOpfamily Mdid="0.1977.1.0"/> + <dxl:LegacyDistrOpfamily Mdid="0.7100.1.0"/> + <dxl:EqualityOp Mdid="0.410.1.0"/> + <dxl:InequalityOp Mdid="0.411.1.0"/> + <dxl:LessThanOp Mdid="0.412.1.0"/> + <dxl:LessThanEqualsOp Mdid="0.414.1.0"/> + <dxl:GreaterThanOp Mdid="0.413.1.0"/> + <dxl:GreaterThanEqualsOp Mdid="0.415.1.0"/> + <dxl:ComparisonOp Mdid="0.351.1.0"/> + <dxl:ArrayType Mdid="0.1016.1.0"/> + <dxl:MinAgg Mdid="0.2131.1.0"/> + <dxl:MaxAgg Mdid="0.2115.1.0"/> + <dxl:AvgAgg Mdid="0.2100.1.0"/> + <dxl:SumAgg Mdid="0.2107.1.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:GPDBAgg Mdid="0.2108.1.0" Name="sum" IsSplittable="true" HashAggCapable="true"> + <dxl:ResultType Mdid="0.20.1.0"/> + <dxl:IntermediateResultType Mdid="0.20.1.0"/> + </dxl:GPDBAgg> + <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:Metadata> + <dxl:Query> + <dxl:OutputColumns> + <dxl:Ident ColId="13" ColName="sum" TypeMdid="0.20.1.0"/> + <dxl:Ident ColId="1" ColName="a" TypeMdid="0.23.1.0"/> + <dxl:Ident ColId="2" ColName="b" TypeMdid="0.23.1.0"/> + </dxl:OutputColumns> + <dxl:CTEList/> + <dxl:LogicalWindow> + <dxl:WindowSpecList> + <dxl:WindowSpec PartitionColumns=""> + <dxl:SortingColumnList> + <dxl:SortingColumn ColId="2" SortOperatorMdid="0.97.1.0" SortOperatorName="<" SortNullsFirst="false"/> + </dxl:SortingColumnList> + <dxl:WindowFrame FrameSpec="Range" ExclusionStrategy="Nulls" StartInRange="4128" EndInRange="4128" InRangeColl="0" InRangeAsc="true" InRangeNullsFirst="false"> + <dxl:TrailingEdge TrailingBoundary="BoundedFollowing"> + <dxl:ConstValue TypeMdid="0.23.1.0" Value="2"/> + </dxl:TrailingEdge> + <dxl:LeadingEdge LeadingBoundary="BoundedPreceding"> + <dxl:ConstValue TypeMdid="0.23.1.0" Value="1"/> + </dxl:LeadingEdge> + </dxl:WindowFrame> + </dxl:WindowSpec> + </dxl:WindowSpecList> + <dxl:ProjList> + <dxl:ProjElem ColId="13" Alias="sum"> + <dxl:WindowFunc Mdid="0.2108.1.0" TypeMdid="0.20.1.0" Distinct="false" WindowStarArg="false" WindowSimpleAgg="true" WindowStrategy="Immediate" WinSpecPos="0"> + <dxl:Ident ColId="1" ColName="column1" TypeMdid="0.23.1.0"/> + </dxl:WindowFunc> + </dxl:ProjElem> + <dxl:ProjElem ColId="1" Alias="a"> + <dxl:Ident ColId="1" ColName="column1" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + <dxl:ProjElem ColId="2" Alias="b"> + <dxl:Ident ColId="2" ColName="column2" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:LogicalConstTable> + <dxl:Columns> + <dxl:Column ColId="1" Attno="1" ColName="column1" TypeMdid="0.23.1.0"/> + <dxl:Column ColId="2" Attno="2" ColName="column2" TypeMdid="0.23.1.0"/> + </dxl:Columns> + <dxl:ConstTuple> + <dxl:Datum TypeMdid="0.23.1.0" Value="1"/> + <dxl:Datum TypeMdid="0.23.1.0" Value="1"/> + </dxl:ConstTuple> + <dxl:ConstTuple> + <dxl:Datum TypeMdid="0.23.1.0" Value="2"/> + <dxl:Datum TypeMdid="0.23.1.0" Value="2"/> + </dxl:ConstTuple> + <dxl:ConstTuple> + <dxl:Datum TypeMdid="0.23.1.0" Value="3"/> + <dxl:Datum TypeMdid="0.23.1.0" Value="3"/> + </dxl:ConstTuple> + <dxl:ConstTuple> + <dxl:Datum TypeMdid="0.23.1.0" Value="4"/> + <dxl:Datum TypeMdid="0.23.1.0" Value="4"/> + </dxl:ConstTuple> + <dxl:ConstTuple> + <dxl:Datum TypeMdid="0.23.1.0" Value="5"/> + <dxl:Datum TypeMdid="0.23.1.0" Value="5"/> + </dxl:ConstTuple> + <dxl:ConstTuple> + <dxl:Datum TypeMdid="0.23.1.0" Value="6"/> + <dxl:Datum TypeMdid="0.23.1.0" Value="6"/> + </dxl:ConstTuple> + </dxl:LogicalConstTable> + </dxl:LogicalWindow> + </dxl:Query> + <dxl:Plan Id="0" SpaceSize="0"> + <dxl:Window PartitionColumns=""> + <dxl:Properties> + <dxl:Cost StartupCost="0" TotalCost="0.000800" Rows="6.000000" Width="16"/> + </dxl:Properties> + <dxl:ProjList> + <dxl:ProjElem ColId="2" Alias="sum"> + <dxl:WindowFunc Mdid="0.2108.1.0" TypeMdid="0.20.1.0" Distinct="false" WindowStarArg="false" WindowSimpleAgg="true" WindowStrategy="Immediate" WinSpecPos="0"> + <dxl:Ident ColId="0" ColName="column1" TypeMdid="0.23.1.0"/> + </dxl:WindowFunc> + </dxl:ProjElem> + <dxl:ProjElem ColId="0" Alias="a"> + <dxl:Ident ColId="0" ColName="column1" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + <dxl:ProjElem ColId="1" Alias="b"> + <dxl:Ident ColId="1" ColName="column2" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:Filter/> + <dxl:Sort SortDiscardDuplicates="false"> + <dxl:Properties> + <dxl:Cost StartupCost="0" TotalCost="0.000752" Rows="6.000000" Width="8"/> + </dxl:Properties> + <dxl:ProjList> + <dxl:ProjElem ColId="0" Alias="column1"> + <dxl:Ident ColId="0" ColName="column1" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + <dxl:ProjElem ColId="1" Alias="column2"> + <dxl:Ident ColId="1" ColName="column2" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:Filter/> + <dxl:SortingColumnList> + <dxl:SortingColumn ColId="1" SortOperatorMdid="0.97.1.0" SortOperatorName="<" SortNullsFirst="false"/> + </dxl:SortingColumnList> + <dxl:LimitCount/> + <dxl:LimitOffset/> + <dxl:Values> + <dxl:Properties> + <dxl:Cost StartupCost="0" TotalCost="0.000048" Rows="6.000000" Width="8"/> + </dxl:Properties> + <dxl:ProjList> + <dxl:ProjElem ColId="0" Alias="column1"> + <dxl:Ident ColId="0" ColName="column1" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + <dxl:ProjElem ColId="1" Alias="column2"> + <dxl:Ident ColId="1" ColName="column2" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:ValuesList> + <dxl:ConstValue TypeMdid="0.23.1.0" Value="1"/> + <dxl:ConstValue TypeMdid="0.23.1.0" Value="1"/> + </dxl:ValuesList> + <dxl:ValuesList> + <dxl:ConstValue TypeMdid="0.23.1.0" Value="2"/> + <dxl:ConstValue TypeMdid="0.23.1.0" Value="2"/> + </dxl:ValuesList> + <dxl:ValuesList> + <dxl:ConstValue TypeMdid="0.23.1.0" Value="3"/> + <dxl:ConstValue TypeMdid="0.23.1.0" Value="3"/> + </dxl:ValuesList> + <dxl:ValuesList> + <dxl:ConstValue TypeMdid="0.23.1.0" Value="4"/> + <dxl:ConstValue TypeMdid="0.23.1.0" Value="4"/> + </dxl:ValuesList> + <dxl:ValuesList> + <dxl:ConstValue TypeMdid="0.23.1.0" Value="5"/> + <dxl:ConstValue TypeMdid="0.23.1.0" Value="5"/> + </dxl:ValuesList> + <dxl:ValuesList> + <dxl:ConstValue TypeMdid="0.23.1.0" Value="6"/> + <dxl:ConstValue TypeMdid="0.23.1.0" Value="6"/> + </dxl:ValuesList> + </dxl:Values> + </dxl:Sort> + <dxl:WindowKeyList> + <dxl:WindowKey> + <dxl:SortingColumnList> + <dxl:SortingColumn ColId="1" SortOperatorMdid="0.97.1.0" SortOperatorName="<" SortNullsFirst="false"/> + </dxl:SortingColumnList> + <dxl:WindowFrame FrameSpec="Range" ExclusionStrategy="Nulls" StartInRange="4128" EndInRange="4128" InRangeColl="0" InRangeAsc="true" InRangeNullsFirst="false"> + <dxl:TrailingEdge TrailingBoundary="BoundedFollowing"> + <dxl:ConstValue TypeMdid="0.23.1.0" Value="2"/> + </dxl:TrailingEdge> + <dxl:LeadingEdge LeadingBoundary="BoundedPreceding"> + <dxl:ConstValue TypeMdid="0.23.1.0" Value="1"/> + </dxl:LeadingEdge> + </dxl:WindowFrame> + </dxl:WindowKey> + </dxl:WindowKeyList> + </dxl:Window> + </dxl:Plan> + </dxl:Thread> +</dxl:DXLMessage> diff --git a/src/backend/gporca/data/dxl/parse_tests/q45-WindowWithFraming.xml b/src/backend/gporca/data/dxl/parse_tests/q45-WindowWithFraming.xml index c6c3b49029..5376316e4a 100644 --- a/src/backend/gporca/data/dxl/parse_tests/q45-WindowWithFraming.xml +++ b/src/backend/gporca/data/dxl/parse_tests/q45-WindowWithFraming.xml @@ -118,7 +118,7 @@ <dxl:SortingColumnList> <dxl:SortingColumn ColId="1" SortOperatorMdid="0.97.1.0" SortOperatorName="<" SortNullsFirst="false"/> </dxl:SortingColumnList> - <dxl:WindowFrame FrameSpec="Range" ExclusionStrategy="Nulls"> + <dxl:WindowFrame FrameSpec="Range" ExclusionStrategy="Nulls" StartInRange="0" EndInRange="0" InRangeColl="0" InRangeAsc="false" InRangeNullsFirst="false"> <dxl:TrailingEdge TrailingBoundary="BoundedPreceding"> <dxl:ConstValue TypeMdid="0.23.1.0" Value="10"/> </dxl:TrailingEdge> diff --git a/src/backend/gporca/libgpopt/include/gpopt/base/CWindowFrame.h b/src/backend/gporca/libgpopt/include/gpopt/base/CWindowFrame.h index ca8932b31e..c4e9ce7f89 100644 --- a/src/backend/gporca/libgpopt/include/gpopt/base/CWindowFrame.h +++ b/src/backend/gporca/libgpopt/include/gpopt/base/CWindowFrame.h @@ -40,8 +40,9 @@ public: // specification method enum EFrameSpec { - EfsRows, // frame is specified using Rows construct - EfsRange, // frame is specified using Range construct + EfsRows, // frame is specified using Rows construct + EfsRange, // frame is specified using Range construct + EfsGroups, // frame is specified using Groups construct EfsSentinel }; @@ -97,6 +98,21 @@ private: // singelton empty frame -- used with any unspecified window function frame static const CWindowFrame m_wfEmpty; + // in_range function for startOffset + OID m_start_in_range_func; + + // in_range function for endOffset + OID m_end_in_range_func; + + // collation for in_range tests + OID m_in_range_coll; + + // use ASC sort order for in_range tests + BOOL m_in_range_asc; + + // nulls sort first for in_range tests + BOOL m_in_range_nulls_first; + // private dummy ctor used to create empty frame CWindowFrame(); @@ -106,7 +122,10 @@ public: // ctor CWindowFrame(CMemoryPool *mp, EFrameSpec efs, EFrameBoundary efbLeading, EFrameBoundary efbTrailing, CExpression *pexprLeading, - CExpression *pexprTrailing, EFrameExclusionStrategy efes); + CExpression *pexprTrailing, EFrameExclusionStrategy efes, + OID start_in_range_func, OID end_in_range_func, + OID in_range_coll, bool in_range_asc, + bool in_range_nulls_first); // dtor ~CWindowFrame() override; @@ -198,6 +217,36 @@ public: return &m_wfEmpty; } + OID + StartInRangeFunc() const + { + return m_start_in_range_func; + } + + OID + EndInRangeFunc() const + { + return m_end_in_range_func; + } + + OID + InRangeColl() const + { + return m_in_range_coll; + } + + BOOL + InRangeAsc() const + { + return m_in_range_asc; + } + + BOOL + InRangeNullsFirst() const + { + return m_in_range_nulls_first; + } + }; // class CWindowFrame } // namespace gpopt diff --git a/src/backend/gporca/libgpopt/src/base/CWindowFrame.cpp b/src/backend/gporca/libgpopt/src/base/CWindowFrame.cpp index 6e495395f0..231e270586 100644 --- a/src/backend/gporca/libgpopt/src/base/CWindowFrame.cpp +++ b/src/backend/gporca/libgpopt/src/base/CWindowFrame.cpp @@ -20,7 +20,7 @@ using namespace gpopt; FORCE_GENERATE_DBGSTR(CWindowFrame); // string encoding of frame specification -const CHAR rgszFrameSpec[][10] = {"Rows", "Range"}; +const CHAR rgszFrameSpec[][10] = {"Rows", "Range", "Groups"}; GPOS_CPL_ASSERT(CWindowFrame::EfsSentinel == GPOS_ARRAY_SIZE(rgszFrameSpec)); // string encoding of frame boundary @@ -51,18 +51,23 @@ const CWindowFrame CWindowFrame::m_wfEmpty; // Ctor // //--------------------------------------------------------------------------- -CWindowFrame::CWindowFrame(CMemoryPool *mp, EFrameSpec efs, - EFrameBoundary efbLeading, - EFrameBoundary efbTrailing, - CExpression *pexprLeading, - CExpression *pexprTrailing, - EFrameExclusionStrategy efes) +CWindowFrame::CWindowFrame( + CMemoryPool *mp, EFrameSpec efs, EFrameBoundary efbLeading, + EFrameBoundary efbTrailing, CExpression *pexprLeading, + CExpression *pexprTrailing, EFrameExclusionStrategy efes, + OID start_in_range_func, OID end_in_range_func, OID in_range_coll, + bool in_range_asc, bool in_range_nulls_first) : m_efs(efs), m_efbLeading(efbLeading), m_efbTrailing(efbTrailing), m_pexprLeading(pexprLeading), m_pexprTrailing(pexprTrailing), - m_efes(efes) + m_efes(efes), + m_start_in_range_func(start_in_range_func), + m_end_in_range_func(end_in_range_func), + m_in_range_coll(in_range_coll), + m_in_range_asc(in_range_asc), + m_in_range_nulls_first(in_range_nulls_first) { GPOS_ASSERT_IMP(EfbBoundedPreceding == m_efbLeading || EfbBoundedFollowing == m_efbLeading, @@ -199,8 +204,10 @@ CWindowFrame::PwfCopyWithRemappedColumns(CMemoryPool *mp, mp, colref_mapping, must_exist); } - return GPOS_NEW(mp) CWindowFrame(mp, m_efs, m_efbLeading, m_efbTrailing, - pexprLeading, pexprTrailing, m_efes); + return GPOS_NEW(mp) CWindowFrame( + mp, m_efs, m_efbLeading, m_efbTrailing, pexprLeading, pexprTrailing, + m_efes, m_start_in_range_func, m_end_in_range_func, m_in_range_coll, + m_in_range_asc, m_in_range_nulls_first); } //--------------------------------------------------------------------------- diff --git a/src/backend/gporca/libgpopt/src/translate/CTranslatorDXLToExpr.cpp b/src/backend/gporca/libgpopt/src/translate/CTranslatorDXLToExpr.cpp index ab60da9abc..6ae7b555be 100644 --- a/src/backend/gporca/libgpopt/src/translate/CTranslatorDXLToExpr.cpp +++ b/src/backend/gporca/libgpopt/src/translate/CTranslatorDXLToExpr.cpp @@ -1908,9 +1908,17 @@ CTranslatorDXLToExpr::Pwf(const CDXLWindowFrame *window_frame) { efs = CWindowFrame::EfsRange; } + else if (EdxlfsGroups == window_frame->ParseDXLFrameSpec()) + { + efs = CWindowFrame::EfsGroups; + } - CWindowFrame *pwf = GPOS_NEW(m_mp) - CWindowFrame(m_mp, efs, efbLead, efbTrail, pexprLead, pexprTrail, efes); + CWindowFrame *pwf = GPOS_NEW(m_mp) CWindowFrame( + m_mp, efs, efbLead, efbTrail, pexprLead, pexprTrail, efes, + window_frame->PdxlnStartInRangeFunc(), + window_frame->PdxlnEndInRangeFunc(), window_frame->PdxlnInRangeColl(), + window_frame->PdxlnInRangeAsc(), + window_frame->PdxlnInRangeNullsFirst()); return pwf; } diff --git a/src/backend/gporca/libgpopt/src/translate/CTranslatorExprToDXL.cpp b/src/backend/gporca/libgpopt/src/translate/CTranslatorExprToDXL.cpp index 27da8c6fca..82aa9f00c5 100644 --- a/src/backend/gporca/libgpopt/src/translate/CTranslatorExprToDXL.cpp +++ b/src/backend/gporca/libgpopt/src/translate/CTranslatorExprToDXL.cpp @@ -6176,8 +6176,10 @@ CTranslatorExprToDXL::GetWindowFrame(CWindowFrame *pwf) } // mappings for frame info in expression and dxl worlds - const ULONG rgulSpecMapping[][2] = {{CWindowFrame::EfsRows, EdxlfsRow}, - {CWindowFrame::EfsRange, EdxlfsRange}}; + const ULONG rgulSpecMapping[][2] = { + {CWindowFrame::EfsRows, EdxlfsRow}, + {CWindowFrame::EfsRange, EdxlfsRange}, + {CWindowFrame::EfsGroups, EdxlfsGroups}}; const ULONG rgulBoundaryMapping[][2] = { {CWindowFrame::EfbUnboundedPreceding, EdxlfbUnboundedPreceding}, @@ -6223,8 +6225,10 @@ CTranslatorExprToDXL::GetWindowFrame(CWindowFrame *pwf) pdxlnTrailing->AddChild(PdxlnScalar(pwf->PexprTrailing())); } - return GPOS_NEW(m_mp) CDXLWindowFrame(edxlfs, frame_exc_strategy, - pdxlnLeading, pdxlnTrailing); + return GPOS_NEW(m_mp) CDXLWindowFrame( + edxlfs, frame_exc_strategy, pdxlnLeading, pdxlnTrailing, + pwf->StartInRangeFunc(), pwf->EndInRangeFunc(), pwf->InRangeColl(), + pwf->InRangeAsc(), pwf->InRangeNullsFirst()); } diff --git a/src/backend/gporca/libnaucrates/include/naucrates/dxl/operators/CDXLWindowFrame.h b/src/backend/gporca/libnaucrates/include/naucrates/dxl/operators/CDXLWindowFrame.h index 01fde849b8..6fd40e8254 100644 --- a/src/backend/gporca/libnaucrates/include/naucrates/dxl/operators/CDXLWindowFrame.h +++ b/src/backend/gporca/libnaucrates/include/naucrates/dxl/operators/CDXLWindowFrame.h @@ -26,6 +26,7 @@ enum EdxlFrameSpec { EdxlfsRow = 0, EdxlfsRange, + EdxlfsGroups, EdxlfsSentinel }; @@ -62,13 +63,31 @@ private: // scalar value representing the boundary trailing CDXLNode *m_dxlnode_trailing; + // in_range function for startOffset + OID m_start_in_range_func; + + // in_range function for endOffset + OID m_end_in_range_func; + + // collation for in_range tests + OID m_in_range_coll; + + // use ASC sort order for in_range tests + BOOL m_in_range_asc; + + // nulls sort first for in_range tests + BOOL m_in_range_nulls_first; + public: CDXLWindowFrame(const CDXLWindowFrame &) = delete; // ctor CDXLWindowFrame(EdxlFrameSpec edxlfs, EdxlFrameExclusionStrategy frame_exc_strategy, - CDXLNode *dxlnode_leading, CDXLNode *dxlnode_trailing); + CDXLNode *dxlnode_leading, CDXLNode *dxlnode_trailing, + OID start_in_range_func, OID end_in_range_func, + OID in_range_coll, bool in_range_asc, + bool in_range_nulls_first); //dtor ~CDXLWindowFrame() override; @@ -100,6 +119,36 @@ public: return m_dxlnode_leading; } + OID + PdxlnStartInRangeFunc() const + { + return m_start_in_range_func; + } + + OID + PdxlnEndInRangeFunc() const + { + return m_end_in_range_func; + } + + OID + PdxlnInRangeColl() const + { + return m_in_range_coll; + } + + BOOL + PdxlnInRangeAsc() const + { + return m_in_range_asc; + } + + BOOL + PdxlnInRangeNullsFirst() const + { + return m_in_range_nulls_first; + } + // return the string representation of the exclusion strategy static const CWStringConst *PstrES(EdxlFrameExclusionStrategy edxles); diff --git a/src/backend/gporca/libnaucrates/include/naucrates/dxl/parser/CParseHandlerWindowFrame.h b/src/backend/gporca/libnaucrates/include/naucrates/dxl/parser/CParseHandlerWindowFrame.h index d837afb44a..e476e46ceb 100644 --- a/src/backend/gporca/libnaucrates/include/naucrates/dxl/parser/CParseHandlerWindowFrame.h +++ b/src/backend/gporca/libnaucrates/include/naucrates/dxl/parser/CParseHandlerWindowFrame.h @@ -42,6 +42,21 @@ private: // frame exclusion strategy EdxlFrameExclusionStrategy m_dxl_frame_exclusion_strategy; + // in_range function for startOffset + OID m_start_in_range_func; + + // in_range function for endOffset + OID m_end_in_range_func; + + // collation for in_range tests + OID m_in_range_coll; + + // use ASC sort order for in_range tests + bool m_in_range_asc; + + // nulls sort first for in_range tests + bool m_in_range_nulls_first; + // window frame generated by the parse handler CDXLWindowFrame *m_window_frame; diff --git a/src/backend/gporca/libnaucrates/include/naucrates/dxl/xml/dxltokens.h b/src/backend/gporca/libnaucrates/include/naucrates/dxl/xml/dxltokens.h index 64847c207a..37b0c277a6 100644 --- a/src/backend/gporca/libnaucrates/include/naucrates/dxl/xml/dxltokens.h +++ b/src/backend/gporca/libnaucrates/include/naucrates/dxl/xml/dxltokens.h @@ -239,6 +239,7 @@ enum Edxltoken EdxltokenWindowFrameSpec, EdxltokenWindowFSRow, EdxltokenWindowFSRange, + EdxltokenWindowFSGroups, EdxltokenWindowLeadingBoundary, EdxltokenWindowTrailingBoundary, @@ -257,6 +258,12 @@ enum Edxltoken EdxltokenWindowESGroup, EdxltokenWindowESTies, + EdxltokenWindowStartInRangeOid, + EdxltokenWindowEndInRangeOid, + EdxltokenWindowInRangeColl, + EdxltokenWindowInRangeAsc, + EdxltokenWindowInRangeNullsFirst, + EdxltokenWindowrefOid, EdxltokenWindowrefDistinct, EdxltokenWindowrefStarArg, diff --git a/src/backend/gporca/libnaucrates/src/operators/CDXLOperatorFactory.cpp b/src/backend/gporca/libnaucrates/src/operators/CDXLOperatorFactory.cpp index 13da90d729..443b4fde2d 100644 --- a/src/backend/gporca/libnaucrates/src/operators/CDXLOperatorFactory.cpp +++ b/src/backend/gporca/libnaucrates/src/operators/CDXLOperatorFactory.cpp @@ -1229,6 +1229,12 @@ CDXLOperatorFactory::ParseDXLFrameSpec(const Attributes &attrs) { frame_spec = EdxlfsRange; } + else if (0 == XMLString::compareString( + CDXLTokens::XmlstrToken(EdxltokenWindowFSGroups), + frame_spec_xml)) + { + frame_spec = EdxlfsGroups; + } else { // turn Xerces exception in optimizer exception diff --git a/src/backend/gporca/libnaucrates/src/operators/CDXLWindowFrame.cpp b/src/backend/gporca/libnaucrates/src/operators/CDXLWindowFrame.cpp index 26ca820297..9c5579d0d9 100644 --- a/src/backend/gporca/libnaucrates/src/operators/CDXLWindowFrame.cpp +++ b/src/backend/gporca/libnaucrates/src/operators/CDXLWindowFrame.cpp @@ -32,11 +32,19 @@ using namespace gpdxl; CDXLWindowFrame::CDXLWindowFrame(EdxlFrameSpec edxlfs, EdxlFrameExclusionStrategy frame_exc_strategy, CDXLNode *dxlnode_leading, - CDXLNode *dxlnode_trailing) + CDXLNode *dxlnode_trailing, + OID start_in_range_func, OID end_in_range_func, + OID in_range_coll, bool in_range_asc, + bool in_range_nulls_first) : m_dxl_win_frame_spec(edxlfs), m_dxl_frame_exclusion_strategy(frame_exc_strategy), m_dxlnode_leading(dxlnode_leading), - m_dxlnode_trailing(dxlnode_trailing) + m_dxlnode_trailing(dxlnode_trailing), + m_start_in_range_func(start_in_range_func), + m_end_in_range_func(end_in_range_func), + m_in_range_coll(in_range_coll), + m_in_range_asc(in_range_asc), + m_in_range_nulls_first(in_range_nulls_first) { GPOS_ASSERT(EdxlfsSentinel > m_dxl_win_frame_spec); GPOS_ASSERT(EdxlfesSentinel > m_dxl_frame_exclusion_strategy); @@ -112,6 +120,10 @@ CDXLWindowFrame::PstrFS(EdxlFrameSpec edxlfs) { return CDXLTokens::GetDXLTokenStr(EdxltokenWindowFSRow); } + else if (EdxlfsGroups == edxlfs) + { + return CDXLTokens::GetDXLTokenStr(EdxltokenWindowFSGroups); + } return CDXLTokens::GetDXLTokenStr(EdxltokenWindowFSRange); } @@ -140,6 +152,21 @@ CDXLWindowFrame::SerializeToDXL(CXMLSerializer *xml_serializer) const CDXLTokens::GetDXLTokenStr(EdxltokenWindowExclusionStrategy), PstrES(m_dxl_frame_exclusion_strategy)); + xml_serializer->AddAttribute( + CDXLTokens::GetDXLTokenStr(EdxltokenWindowStartInRangeOid), + m_start_in_range_func); + xml_serializer->AddAttribute( + CDXLTokens::GetDXLTokenStr(EdxltokenWindowEndInRangeOid), + m_end_in_range_func); + xml_serializer->AddAttribute( + CDXLTokens::GetDXLTokenStr(EdxltokenWindowInRangeColl), + m_in_range_coll); + xml_serializer->AddAttribute( + CDXLTokens::GetDXLTokenStr(EdxltokenWindowInRangeAsc), m_in_range_asc); + xml_serializer->AddAttribute( + CDXLTokens::GetDXLTokenStr(EdxltokenWindowInRangeNullsFirst), + m_in_range_nulls_first); + // add the values representing the window boundary m_dxlnode_trailing->SerializeToDXL(xml_serializer); m_dxlnode_leading->SerializeToDXL(xml_serializer); diff --git a/src/backend/gporca/libnaucrates/src/parser/CParseHandlerWindowFrame.cpp b/src/backend/gporca/libnaucrates/src/parser/CParseHandlerWindowFrame.cpp index f266be9248..17fd45a651 100644 --- a/src/backend/gporca/libnaucrates/src/parser/CParseHandlerWindowFrame.cpp +++ b/src/backend/gporca/libnaucrates/src/parser/CParseHandlerWindowFrame.cpp @@ -34,6 +34,11 @@ CParseHandlerWindowFrame::CParseHandlerWindowFrame( : CParseHandlerBase(mp, parse_handler_mgr, parse_handler_root), m_dxl_win_frame_spec(EdxlfsSentinel), m_dxl_frame_exclusion_strategy(EdxlfesSentinel), + m_start_in_range_func(0), + m_end_in_range_func(0), + m_in_range_coll(0), + m_in_range_asc(false), + m_in_range_nulls_first(false), m_window_frame(nullptr) { } @@ -129,9 +134,10 @@ CParseHandlerWindowFrame::EndElement(const XMLCh *const, // element_uri, CDXLNode *dxlnode_leading = leading_val_parse_handler_base->CreateDXLNode(); dxlnode_leading->AddRef(); - m_window_frame = GPOS_NEW(m_mp) - CDXLWindowFrame(m_dxl_win_frame_spec, m_dxl_frame_exclusion_strategy, - dxlnode_leading, dxlnode_trailing); + m_window_frame = GPOS_NEW(m_mp) CDXLWindowFrame( + m_dxl_win_frame_spec, m_dxl_frame_exclusion_strategy, dxlnode_leading, + dxlnode_trailing, m_start_in_range_func, m_end_in_range_func, + m_in_range_coll, m_in_range_asc, m_in_range_nulls_first); // deactivate handler m_parse_handler_mgr->DeactivateHandler(); diff --git a/src/backend/gporca/libnaucrates/src/xml/dxltokens.cpp b/src/backend/gporca/libnaucrates/src/xml/dxltokens.cpp index 6839ed93af..00a7127eda 100644 --- a/src/backend/gporca/libnaucrates/src/xml/dxltokens.cpp +++ b/src/backend/gporca/libnaucrates/src/xml/dxltokens.cpp @@ -318,6 +318,13 @@ CDXLTokens::Init(CMemoryPool *mp) {EdxltokenScalarWindowFrameTrailingEdge, GPOS_WSZ_LIT("TrailingEdge")}, {EdxltokenWindowFSRow, GPOS_WSZ_LIT("Row")}, {EdxltokenWindowFSRange, GPOS_WSZ_LIT("Range")}, + {EdxltokenWindowFSGroups, GPOS_WSZ_LIT("Groups")}, + + {EdxltokenWindowStartInRangeOid, GPOS_WSZ_LIT("StartInRange")}, + {EdxltokenWindowEndInRangeOid, GPOS_WSZ_LIT("EndInRange")}, + {EdxltokenWindowInRangeColl, GPOS_WSZ_LIT("InRangeColl")}, + {EdxltokenWindowInRangeAsc, GPOS_WSZ_LIT("InRangeAsc")}, + {EdxltokenWindowInRangeNullsFirst, GPOS_WSZ_LIT("InRangeNullsFirst")}, {EdxltokenWindowExclusionStrategy, GPOS_WSZ_LIT("ExclusionStrategy")}, {EdxltokenWindowESNone, GPOS_WSZ_LIT("None")}, diff --git a/src/backend/gporca/server/src/unittest/gpopt/minidump/CWindowTest.cpp b/src/backend/gporca/server/src/unittest/gpopt/minidump/CWindowTest.cpp index b67b6d90e9..74006550a5 100644 --- a/src/backend/gporca/server/src/unittest/gpopt/minidump/CWindowTest.cpp +++ b/src/backend/gporca/server/src/unittest/gpopt/minidump/CWindowTest.cpp @@ -33,6 +33,11 @@ CWindowTest::EresUnittest() "../data/dxl/minidump/Preds-Over-WinFunc4.mdp", "../data/dxl/minidump/Preds-Over-WinFunc5.mdp", "../data/dxl/minidump/window-count-gpdb6.mdp", + "../data/dxl/minidump/WindowFrameExcludeGroup.mdp", + "../data/dxl/minidump/WindowFrameExcludeTies.mdp", + "../data/dxl/minidump/WindowFrameExcludeCurrentRow.mdp", + "../data/dxl/minidump/WindowFrameRangePrecedingAndFollowing.mdp", + "../data/dxl/minidump/WindowFrameGroups.mdp", }; return CTestUtils::EresUnittest_RunTestsWithoutAdditionalTraceFlags( diff --git a/src/include/gpopt/translate/CTranslatorScalarToDXL.h b/src/include/gpopt/translate/CTranslatorScalarToDXL.h index 866de5e491..4020102c4d 100644 --- a/src/include/gpopt/translate/CTranslatorScalarToDXL.h +++ b/src/include/gpopt/translate/CTranslatorScalarToDXL.h @@ -279,6 +279,8 @@ public: // create a DXL WindowFrame node from a GPDB expression CDXLWindowFrame *TranslateWindowFrameToDXL( int frame_options, const Node *start_offset, const Node *end_offset, + Oid start_in_range_func, Oid end_in_range_func, Oid in_range_coll, + bool in_range_asc, bool in_range_nulls_first, const CMappingVarColId *var_colid_mapping, CDXLNode *new_scalar_proj_list); diff --git a/src/test/regress/expected/gporca_optimizer.out b/src/test/regress/expected/gporca_optimizer.out index 4747f1604f..65b2858493 100644 --- a/src/test/regress/expected/gporca_optimizer.out +++ b/src/test/regress/expected/gporca_optimizer.out @@ -6308,8 +6308,6 @@ select row_number() over(order by foo.a+ bar.a)/count(*) from orca.foo inner joi (400 rows) select count(*) over(partition by b order by a range between 1 preceding and (select count(*) from orca.bar) following) from orca.foo; -INFO: GPORCA failed to produce a plan, falling back to planner -DETAIL: Feature not supported: window frame RANGE with OFFSET PRECEDING or FOLLOWING count ------- 16 @@ -6400,8 +6398,6 @@ select a+1, rank() over(partition by b+1 order by a+1) from orca.foo order by 1, (40 rows) select a , sum(a) over (order by a range 1 preceding) from orca.r order by 1,2; -INFO: GPORCA failed to produce a plan, falling back to planner -DETAIL: Feature not supported: window frame RANGE with OFFSET PRECEDING or FOLLOWING a | sum ----+----- 1 | 1 diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out index de7238b5f1..31a33926ff 100644 --- a/src/test/regress/expected/window.out +++ b/src/test/regress/expected/window.out @@ -5,6 +5,7 @@ set enable_incremental_sort=on; -- -- WINDOW FUNCTIONS -- +SET optimizer_trace_fallback=on; CREATE TEMPORARY TABLE empsalary ( depname varchar, empno bigint, @@ -4264,3 +4265,4 @@ SELECT * FROM pg_temp.f(2); {5} (5 rows) +RESET optimizer_trace_fallback; diff --git a/src/test/regress/expected/window_optimizer.out b/src/test/regress/expected/window_optimizer.out index 0f25e18f71..78dd907220 100644 --- a/src/test/regress/expected/window_optimizer.out +++ b/src/test/regress/expected/window_optimizer.out @@ -5,6 +5,7 @@ set enable_incremental_sort=on; -- -- WINDOW FUNCTIONS -- +SET optimizer_trace_fallback=on; CREATE TEMPORARY TABLE empsalary ( depname varchar, empno bigint, @@ -1211,6 +1212,8 @@ SELECT * FROM v_window; (10 rows) SELECT pg_get_viewdef('v_window'); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation pg_get_viewdef --------------------------------------------------------------------------------------- SELECT i.i, + @@ -1238,6 +1241,8 @@ SELECT * FROM v_window; (10 rows) SELECT pg_get_viewdef('v_window'); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation pg_get_viewdef ----------------------------------------------------------------------------------------------------------- SELECT i.i, + @@ -1264,6 +1269,8 @@ SELECT * FROM v_window; (10 rows) SELECT pg_get_viewdef('v_window'); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation pg_get_viewdef ----------------------------------------------------------------------------------------------------- SELECT i.i, + @@ -1290,6 +1297,8 @@ SELECT * FROM v_window; (10 rows) SELECT pg_get_viewdef('v_window'); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation pg_get_viewdef ---------------------------------------------------------------------------------------------------- SELECT i.i, + @@ -1316,6 +1325,8 @@ SELECT * FROM v_window; (10 rows) SELECT pg_get_viewdef('v_window'); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation pg_get_viewdef --------------------------------------------------------------------------------------- SELECT i.i, + @@ -1341,6 +1352,8 @@ SELECT * FROM v_window; (10 rows) SELECT pg_get_viewdef('v_window'); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation pg_get_viewdef ----------------------------------------------------------------------------------------- SELECT i.i, + @@ -1353,6 +1366,8 @@ CREATE TEMP VIEW v_window AS SELECT i, min(i) over (order by i range between '1 day' preceding and '10 days' following) as min_i FROM generate_series(now(), now()+'100 days'::interval, '1 hour') i; SELECT pg_get_viewdef('v_window'); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation pg_get_viewdef --------------------------------------------------------------------------------------------------------------------------- SELECT i.i, + @@ -3003,6 +3018,8 @@ WINDOW w AS (ORDER BY x groups between 1 preceding and 1 following); -- with UNION SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 UNION ALL SELECT * FROM tenk2)s LIMIT 0; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation count ------- (0 rows) @@ -3027,10 +3044,10 @@ from t1 where f1 = f2; Partition By: f1 Order By: f2 -> Sort - Sort Key: f1 + Sort Key: f1, f2 -> Seq Scan on t1 Filter: (f1 = f2) - Optimizer: Postgres query optimizer + Optimizer: Pivotal Optimizer (GPORCA) (9 rows) select f1, sum(f1) over (partition by f1 order by f2 @@ -3077,10 +3094,10 @@ from t1 where f1 = f2; Partition By: f1 Order By: f2 -> Sort - Sort Key: f1 + Sort Key: f1, f2 -> Seq Scan on t1 Filter: (f1 = f2) - Optimizer: Postgres query optimizer + Optimizer: Pivotal Optimizer (GPORCA) (9 rows) select f1, sum(f1) over (partition by f1 order by f2 @@ -3181,6 +3198,8 @@ SELECT sum(salary), row_number() OVER (ORDER BY depname), sum( ) FILTER (WHERE depname <> 'sales') OVER (ORDER BY depname DESC) AS "filtered_sum", depname FROM empsalary GROUP BY depname; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Aggregate functions with FILTER sum | row_number | filtered_sum | depname -------+------------+--------------+----------- 25100 | 1 | 22600 | develop @@ -3488,6 +3507,22 @@ FROM (VALUES ) AS t(p, i, v) WINDOW wnd AS (PARTITION BY P ORDER BY i ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) ORDER BY p, i; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL row | nstrict | nstrict_init | strict | strict_init ----------+-----------------------------------------------+-------------------------------------------------+-----------+---------------- 1,1:NULL | +NULL | MI+NULL | | MI @@ -3522,6 +3557,14 @@ FROM (VALUES ) AS t(p, i, f, v) WINDOW wnd AS (PARTITION BY p ORDER BY i ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) ORDER BY p, i; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Aggregate functions with FILTER +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL row | nstrict_filt | nstrict_init_filt | strict_filt | strict_init_filt ----------+--------------+-------------------+-------------+------------------ 1,1:NULL | +NULL | MI+NULL | | MI @@ -3549,6 +3592,12 @@ FROM (VALUES ) AS t(i, v) WINDOW wnd AS (ORDER BY i ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) ORDER BY i; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL row | inverse | noinverse -----+---------------+----------- 1:a | a | a @@ -3569,6 +3618,14 @@ FROM (VALUES ) AS t(i, v) WINDOW wnd AS (ORDER BY i ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) ORDER BY i; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Aggregate functions with FILTER +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL row | inverse | noinverse -----+---------------+----------- 1:a | a | a @@ -3734,6 +3791,14 @@ JOIN sum_following ON sum_following.i = vs.i WINDOW fwd AS ( ORDER BY vs.i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: SIRV functions +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: SIRV functions eq1 | eq2 | eq3 -----+-----+----- t | t | t @@ -4265,3 +4330,4 @@ SELECT * FROM pg_temp.f(2); {5} (5 rows) +RESET optimizer_trace_fallback; diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql index 6fd72f478e..0605874f7e 100644 --- a/src/test/regress/sql/window.sql +++ b/src/test/regress/sql/window.sql @@ -6,6 +6,7 @@ set enable_incremental_sort=on; -- -- WINDOW FUNCTIONS -- +SET optimizer_trace_fallback=on; CREATE TEMPORARY TABLE empsalary ( depname varchar, @@ -1462,3 +1463,4 @@ $$ LANGUAGE SQL STABLE; EXPLAIN (costs off) SELECT * FROM pg_temp.f(2); SELECT * FROM pg_temp.f(2); +RESET optimizer_trace_fallback; --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
