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 ebccd1f8806d6bedd6e4f127505b1e62f4a8789f Author: David Kimura <[email protected]> AuthorDate: Mon Apr 24 12:10:00 2023 -0700 [ORCA] Support boolean static partition pruning (#15348) After Postgres 12 merge into GPDB, GPDB absorbed the underlying Postgres partition implementation. In the process, ORCA lost static partition pruning on boolean partitioned tables. In order to support that feature, ORCA constraint framework needs to support boolean types as interval constraints for constraint contradiction. In SQL standard, boolean type includes NULL and UNKNOWN. Some special care was needed to handle UNKNOWN type as it relates to an interval constraint. Since it is unknown, it can be anywhere on the spectrum (-inf, inf), hence that is the range we assign it. In the process of supporting this feature, a related issue was discovered in upstream Postgres [1]. This commit also enables partition pruning with general boolean compare. It is important to note that general comparison handles NULL different than boolean test. For example, this evaluates to TRUE: SELECT true IS NOT null; However, this evaluates NULL: SELECT true<>null; This has vastly different effects on static partition pruning. First step to enabling this general boolean compare is to create interval constraints from boolean scalar idents (see PciIntervalFromScalarIdent). Second step is to update the interval range to support domain of boolean types (see CConstraintInterval::PciUnbounded). In order to illustrate that issue, consider the following table: CREATE TABLE booltab (a bool) partition by list(a) ( partition part_t values(true), partition part_f values(false), partition default def); The default partition constraint is defined in the catalog. And in ORCA world is represented as an expression like: +--CScalarBoolOp (EboolopNot) +--CScalarBoolOp (EboolopAnd) |--CScalarBoolOp (EboolopNot) | +--CScalarNullTest | +--CScalarIdent "a" (0) +--CScalarArrayCmp Any (=) |--CScalarIdent "a" (0) +--CScalarArray: {(0) (1)} If we track the constraint derivation at each level without adjusting the constraint interval for boolean, then the derivation looks like: +--CScalarBoolOp (EboolopNot) {"a" (0), ranges: (-inf, 0) (0, 1) (1, inf) [NULL] } +--CScalarBoolOp (EboolopAnd) {"a" (0), ranges: [0, 0] [1, 1] } |--CScalarBoolOp (EboolopNot) {"a" (0), ranges: (-inf, inf) } | +--CScalarNullTest {"a" (0), ranges: [NULL] } | +--CScalarIdent "a" (0) +--CScalarArrayCmp Any (=) {"a" (0), ranges: [0, 0] [1, 1] } |--CScalarIdent "a" (0) +--CScalarArray: {(0) (1)} When the predicate is a=false, then the constraint is: +--CScalarBoolOp (EboolopNot) {"a" (0), ranges: (-inf, 1) (1, inf) } +--CScalarIdent "a" (1) {"a" (0), ranges: [1, 1] [NULL] } The conjunction of table constraint: {"a" (0), ranges: (-inf, 0) (0, 1) (1, inf) [NULL] } And predicate constraint: {"a" (0), ranges: (-inf, 1) (1, inf) } Produces result constraint: {"a" (0), ranges: (-inf, 0) (1, inf) } The problem with that result constraint is that it is not considered a contradiction because there is a range, albeit outside the range of boolean [0,1]. As a result, the default partition is incorrectly not pruned. There are 2 different ways to solve this: 1) Fix up contradiction detection to special case boolean intervals 2) Fix up boolean interval to map to the (-inf, inf) domain space One problem with the 2nd approach is that other systems (e.g. direct dispatch) use the constraint system and rely on finite values (i.e. 0, 1). That would mean fixing up those areas in the code as well to handle the full spectrum (-inf, inf) correctly. Instead, this commit implements the 1st approach. It also requires updating the EboolopNot operator to keep the domain [0, 1]. In this case the above default partition and predicate constraint derivation looks like: +--CScalarBoolOp (EboolopNot) {"a" (0), ranges: [NULL] } +--CScalarBoolOp (EboolopAnd) {"a" (0), ranges: [0, 0] [1, 1] } |--CScalarBoolOp (EboolopNot) {"a" (0), ranges: [0, 0] [1, 1] } | +--CScalarNullTest {"a" (0), ranges: [NULL] } | +--CScalarIdent "a" (0) +--CScalarArrayCmp Any (=) {"a" (0), ranges: [0, 0] [1, 1] } |--CScalarIdent "a" (0) +--CScalarArray: {(0) (1)} +--CScalarBoolOp (EboolopNot) {"a" (0), ranges: [0, 0] } +--CScalarIdent "a" (1) {"a" (0), ranges: [1, 1] [NULL] } Then the conjunction of the two produces: {"a" (0), ranges: } And the table is correctly pruned. [1] https://www.postgresql.org/message-id/flat/CAHnPFjQ5qxs6J_p+g8=ww7gqvfn71_je+tygj0s7rdrci1u...@mail.gmail.com --- .../dxl/minidump/CTEMisAlignedProducerConsumer.mdp | 24 ++ .../data/dxl/minidump/PartTbl-SPE-Boolean1.mdp | 365 ++++++++++++++++ .../data/dxl/minidump/PartTbl-SPE-Boolean2.mdp | 461 ++++++++++++++++++++ .../include/gpopt/base/CConstraintInterval.h | 9 + .../gporca/libgpopt/include/gpopt/base/CUtils.h | 3 + .../libgpopt/src/base/CConstraintInterval.cpp | 162 ++++++- .../libgpopt/src/base/CDefaultComparator.cpp | 1 + src/backend/gporca/libgpopt/src/base/CUtils.cpp | 8 + src/backend/gporca/server/CMakeLists.txt | 3 + .../gpopt/translate/CTranslatorExprToDXLTest.cpp | 10 +- .../regress/expected/partition_prune_optimizer.out | 14 +- src/test/regress/expected/partition_pruning.out | 480 +++++++++++++++++++++ .../expected/partition_pruning_optimizer.out | 470 ++++++++++++++++++++ .../expected/qp_targeted_dispatch_optimizer.out | 2 +- src/test/regress/sql/partition_pruning.sql | 102 +++++ 15 files changed, 2095 insertions(+), 19 deletions(-) diff --git a/src/backend/gporca/data/dxl/minidump/CTEMisAlignedProducerConsumer.mdp b/src/backend/gporca/data/dxl/minidump/CTEMisAlignedProducerConsumer.mdp index c9542903b5..56c2fa14a6 100644 --- a/src/backend/gporca/data/dxl/minidump/CTEMisAlignedProducerConsumer.mdp +++ b/src/backend/gporca/data/dxl/minidump/CTEMisAlignedProducerConsumer.mdp @@ -148,6 +148,30 @@ SELECT 1 FROM (SELECT * FROM foo) t2 FULL JOIN (SELECT 1) t1 ON true; <dxl:ColumnStatistics Mdid="1.16385.1.0.4" Name="xmax" Width="4.000000" NullFreq="0.000000" NdvRemain="0.000000" FreqRemain="0.000000" ColStatsMissing="true"/> <dxl:ColumnStatistics Mdid="1.16385.1.0.7" Name="gp_segment_id" Width="4.000000" NullFreq="0.000000" NdvRemain="0.000000" FreqRemain="0.000000" ColStatsMissing="true"/> <dxl:ColumnStatistics Mdid="1.16385.1.0.6" Name="tableoid" Width="4.000000" NullFreq="0.000000" NdvRemain="0.000000" FreqRemain="0.000000" ColStatsMissing="true"/> + <dxl:GPDBScalarOp Mdid="0.1695.1.0" Name=">=" ComparisonType="GEq" ReturnsNullOnNullInput="true" IsNDVPreserving="false"> + <dxl:LeftType Mdid="0.16.1.0"/> + <dxl:RightType Mdid="0.16.1.0"/> + <dxl:ResultType Mdid="0.16.1.0"/> + <dxl:OpFunc Mdid="0.1692.1.0"/> + <dxl:Commutator Mdid="0.1694.1.0"/> + <dxl:InverseOp Mdid="0.58.1.0"/> + <dxl:Opfamilies> + <dxl:Opfamily Mdid="0.424.1.0"/> + <dxl:Opfamily Mdid="0.10002.1.0"/> + </dxl:Opfamilies> + </dxl:GPDBScalarOp> + <dxl:GPDBScalarOp Mdid="0.1694.1.0" Name="<=" ComparisonType="LEq" ReturnsNullOnNullInput="true" IsNDVPreserving="false"> + <dxl:LeftType Mdid="0.16.1.0"/> + <dxl:RightType Mdid="0.16.1.0"/> + <dxl:ResultType Mdid="0.16.1.0"/> + <dxl:OpFunc Mdid="0.1691.1.0"/> + <dxl:Commutator Mdid="0.1695.1.0"/> + <dxl:InverseOp Mdid="0.59.1.0"/> + <dxl:Opfamilies> + <dxl:Opfamily Mdid="0.424.1.0"/> + <dxl:Opfamily Mdid="0.10002.1.0"/> + </dxl:Opfamilies> + </dxl:GPDBScalarOp> </dxl:Metadata> <dxl:Query> <dxl:OutputColumns> diff --git a/src/backend/gporca/data/dxl/minidump/PartTbl-SPE-Boolean1.mdp b/src/backend/gporca/data/dxl/minidump/PartTbl-SPE-Boolean1.mdp new file mode 100644 index 0000000000..e7f02e3cae --- /dev/null +++ b/src/backend/gporca/data/dxl/minidump/PartTbl-SPE-Boolean1.mdp @@ -0,0 +1,365 @@ +<?xml version="1.0" encoding="UTF-8"?> +<dxl:DXLMessage xmlns:dxl="http://greenplum.com/dxl/2010/12/";> + <dxl:Comment><![CDATA[ + Objective: Perform static partition elimination (SPE) on a table partitioned + by a boolean column. In below setup containing 2 leaf tables and predicate + IS TRUE, only 1 table should be scanned. + + CREATE TABLE boolpart(a int, b bool) PARTITION BY LIST(b) ( + PARTITION l1 values(true), PARTITION l2 values(false)); + INSERT INTO boolpart values (1,true); + INSERT INTO boolpart values (2, false); + + EXPLAIN SELECT * FROM boolpart WHERE b IS TRUE; + ]]> + </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 JoinArityForAssociativityCommutativity="18" ArrayExpansionThreshold="20" JoinOrderDynamicProgThreshold="10" BroadcastThreshold="100000" EnforceConstraintsOnDML="false" PushGroupByBelowSetopThreshold="10" XformBindThreshold="0" SkewFactor="0"/> + <dxl:TraceFlags Value="102001,102002,102003,102043,102074,102120,102144,103001,103014,103022,103026,103027,103029,103033,103038,103040,104002,104003,104004,104005,105000,106000"/> + </dxl:OptimizerConfig> + <dxl:Metadata SystemIds="0.GPDB"> + <dxl: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.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:Relation Mdid="6.112430.1.0" Name="boolpart_1_prt_l2" IsTemporary="false" StorageType="Heap" DistributionPolicy="Hash" DistributionColumns="0" Keys="8,2"> + <dxl:Columns> + <dxl:Column Name="a" Attno="1" Mdid="0.23.1.0" Nullable="true" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="b" Attno="2" Mdid="0.16.1.0" Nullable="true" ColWidth="1"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="ctid" Attno="-1" Mdid="0.27.1.0" Nullable="false" ColWidth="6"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="xmin" Attno="-2" Mdid="0.28.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="cmin" Attno="-3" Mdid="0.29.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="xmax" Attno="-4" Mdid="0.28.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="cmax" Attno="-5" Mdid="0.29.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="tableoid" Attno="-6" Mdid="0.26.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="gp_segment_id" Attno="-7" Mdid="0.23.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + </dxl:Columns> + <dxl:IndexInfoList/> + <dxl:CheckConstraints/> + <dxl:DistrOpfamilies> + <dxl:DistrOpfamily Mdid="0.1977.1.0"/> + </dxl:DistrOpfamilies> + <dxl:PartConstraint> + <dxl:And> + <dxl:IsNotNull> + <dxl:Ident ColId="2" ColName="b" TypeMdid="0.16.1.0"/> + </dxl:IsNotNull> + <dxl:Comparison ComparisonOperator="=" OperatorMdid="0.91.1.0"> + <dxl:Ident ColId="2" ColName="b" TypeMdid="0.16.1.0"/> + <dxl:ConstValue TypeMdid="0.16.1.0" Value="false"/> + </dxl:Comparison> + </dxl:And> + </dxl:PartConstraint> + </dxl:Relation> + <dxl:Type Mdid="0.26.1.0" Name="oid" IsRedistributable="true" IsHashable="true" IsMergeJoinable="true" IsComposite="false" IsTextRelated="false" IsFixedLength="true" Length="4" PassByValue="true"> + <dxl:DistrOpfamily Mdid="0.1990.1.0"/> + <dxl:LegacyDistrOpfamily Mdid="0.7109.1.0"/> + <dxl:EqualityOp Mdid="0.607.1.0"/> + <dxl:InequalityOp Mdid="0.608.1.0"/> + <dxl:LessThanOp Mdid="0.609.1.0"/> + <dxl:LessThanEqualsOp Mdid="0.611.1.0"/> + <dxl:GreaterThanOp Mdid="0.610.1.0"/> + <dxl:GreaterThanEqualsOp Mdid="0.612.1.0"/> + <dxl:ComparisonOp Mdid="0.356.1.0"/> + <dxl:ArrayType Mdid="0.1028.1.0"/> + <dxl:MinAgg Mdid="0.2134.1.0"/> + <dxl:MaxAgg Mdid="0.2118.1.0"/> + <dxl:AvgAgg Mdid="0.0.0.0"/> + <dxl:SumAgg Mdid="0.0.0.0"/> + <dxl:CountAgg Mdid="0.2147.1.0"/> + </dxl:Type> + <dxl:Type Mdid="0.27.1.0" Name="tid" IsRedistributable="true" IsHashable="true" IsMergeJoinable="true" IsComposite="false" IsTextRelated="false" IsFixedLength="true" Length="6" PassByValue="false"> + <dxl:DistrOpfamily Mdid="0.2227.1.0"/> + <dxl:LegacyDistrOpfamily Mdid="0.7110.1.0"/> + <dxl:EqualityOp Mdid="0.387.1.0"/> + <dxl:InequalityOp Mdid="0.402.1.0"/> + <dxl:LessThanOp Mdid="0.2799.1.0"/> + <dxl:LessThanEqualsOp Mdid="0.2801.1.0"/> + <dxl:GreaterThanOp Mdid="0.2800.1.0"/> + <dxl:GreaterThanEqualsOp Mdid="0.2802.1.0"/> + <dxl:ComparisonOp Mdid="0.2794.1.0"/> + <dxl:ArrayType Mdid="0.1010.1.0"/> + <dxl:MinAgg Mdid="0.2798.1.0"/> + <dxl:MaxAgg Mdid="0.2797.1.0"/> + <dxl:AvgAgg Mdid="0.0.0.0"/> + <dxl:SumAgg Mdid="0.0.0.0"/> + <dxl:CountAgg Mdid="0.2147.1.0"/> + </dxl:Type> + <dxl:RelationStatistics Mdid="2.112424.1.0" Name="boolpart" Rows="0.000000" RelPages="0" RelAllVisible="0" EmptyRelation="true"/> + <dxl:Type Mdid="0.29.1.0" Name="cid" IsRedistributable="true" IsHashable="true" IsMergeJoinable="false" IsComposite="false" IsTextRelated="false" IsFixedLength="true" Length="4" PassByValue="true"> + <dxl:DistrOpfamily Mdid="0.2226.1.0"/> + <dxl:EqualityOp Mdid="0.385.1.0"/> + <dxl:InequalityOp Mdid="0.0.0.0"/> + <dxl:LessThanOp Mdid="0.0.0.0"/> + <dxl:LessThanEqualsOp Mdid="0.0.0.0"/> + <dxl:GreaterThanOp Mdid="0.0.0.0"/> + <dxl:GreaterThanEqualsOp Mdid="0.0.0.0"/> + <dxl:ComparisonOp Mdid="0.0.0.0"/> + <dxl:ArrayType Mdid="0.1012.1.0"/> + <dxl:MinAgg Mdid="0.0.0.0"/> + <dxl:MaxAgg Mdid="0.0.0.0"/> + <dxl:AvgAgg Mdid="0.0.0.0"/> + <dxl:SumAgg Mdid="0.0.0.0"/> + <dxl:CountAgg Mdid="0.2147.1.0"/> + </dxl:Type> + <dxl:Type Mdid="0.28.1.0" Name="xid" IsRedistributable="true" IsHashable="true" IsMergeJoinable="false" IsComposite="false" IsTextRelated="false" IsFixedLength="true" Length="4" PassByValue="true"> + <dxl:DistrOpfamily Mdid="0.2225.1.0"/> + <dxl:EqualityOp Mdid="0.352.1.0"/> + <dxl:InequalityOp Mdid="0.3315.1.0"/> + <dxl:LessThanOp Mdid="0.0.0.0"/> + <dxl:LessThanEqualsOp Mdid="0.0.0.0"/> + <dxl:GreaterThanOp Mdid="0.0.0.0"/> + <dxl:GreaterThanEqualsOp Mdid="0.0.0.0"/> + <dxl:ComparisonOp Mdid="0.0.0.0"/> + <dxl:ArrayType Mdid="0.1011.1.0"/> + <dxl:MinAgg Mdid="0.0.0.0"/> + <dxl:MaxAgg Mdid="0.0.0.0"/> + <dxl:AvgAgg Mdid="0.0.0.0"/> + <dxl:SumAgg Mdid="0.0.0.0"/> + <dxl:CountAgg Mdid="0.2147.1.0"/> + </dxl:Type> + <dxl:Relation Mdid="6.112424.1.0" Name="boolpart" IsTemporary="false" StorageType="Heap" DistributionPolicy="Hash" DistributionColumns="0" Keys="7,8,2" PartitionColumns="1" PartitionTypes="l"> + <dxl:Columns> + <dxl:Column Name="a" Attno="1" Mdid="0.23.1.0" Nullable="true" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="b" Attno="2" Mdid="0.16.1.0" Nullable="true" ColWidth="1"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="ctid" Attno="-1" Mdid="0.27.1.0" Nullable="false" ColWidth="6"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="xmin" Attno="-2" Mdid="0.28.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="cmin" Attno="-3" Mdid="0.29.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="xmax" Attno="-4" Mdid="0.28.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="cmax" Attno="-5" Mdid="0.29.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="tableoid" Attno="-6" Mdid="0.26.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="gp_segment_id" Attno="-7" Mdid="0.23.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + </dxl:Columns> + <dxl:IndexInfoList/> + <dxl:CheckConstraints/> + <dxl:DistrOpfamilies> + <dxl:DistrOpfamily Mdid="0.1977.1.0"/> + </dxl:DistrOpfamilies> + <dxl:Partitions> + <dxl:Partition Mdid="6.112430.1.0"/> + <dxl:Partition Mdid="6.112427.1.0"/> + </dxl:Partitions> + </dxl:Relation> + <dxl:Relation Mdid="6.112427.1.0" Name="boolpart_1_prt_l1" IsTemporary="false" StorageType="Heap" DistributionPolicy="Hash" DistributionColumns="0" Keys="8,2"> + <dxl:Columns> + <dxl:Column Name="a" Attno="1" Mdid="0.23.1.0" Nullable="true" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="b" Attno="2" Mdid="0.16.1.0" Nullable="true" ColWidth="1"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="ctid" Attno="-1" Mdid="0.27.1.0" Nullable="false" ColWidth="6"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="xmin" Attno="-2" Mdid="0.28.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="cmin" Attno="-3" Mdid="0.29.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="xmax" Attno="-4" Mdid="0.28.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="cmax" Attno="-5" Mdid="0.29.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="tableoid" Attno="-6" Mdid="0.26.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="gp_segment_id" Attno="-7" Mdid="0.23.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + </dxl:Columns> + <dxl:IndexInfoList/> + <dxl:CheckConstraints/> + <dxl:DistrOpfamilies> + <dxl:DistrOpfamily Mdid="0.1977.1.0"/> + </dxl:DistrOpfamilies> + <dxl:PartConstraint> + <dxl:And> + <dxl:IsNotNull> + <dxl:Ident ColId="2" ColName="b" TypeMdid="0.16.1.0"/> + </dxl:IsNotNull> + <dxl:Comparison ComparisonOperator="=" OperatorMdid="0.91.1.0"> + <dxl:Ident ColId="2" ColName="b" TypeMdid="0.16.1.0"/> + <dxl:ConstValue TypeMdid="0.16.1.0" Value="true"/> + </dxl:Comparison> + </dxl:And> + </dxl:PartConstraint> + </dxl:Relation> + <dxl:ColumnStatistics Mdid="1.112424.1.0.1" Name="b" Width="1.000000" NullFreq="0.000000" NdvRemain="0.000000" FreqRemain="0.000000" ColStatsMissing="true"/> + <dxl:ColumnStatistics Mdid="1.112424.1.0.0" Name="a" Width="4.000000" NullFreq="0.000000" NdvRemain="0.000000" FreqRemain="0.000000" ColStatsMissing="true"/> + <dxl:GPDBScalarOp Mdid="0.91.1.0" Name="=" ComparisonType="Eq" ReturnsNullOnNullInput="true" IsNDVPreserving="false"> + <dxl:LeftType Mdid="0.16.1.0"/> + <dxl:RightType Mdid="0.16.1.0"/> + <dxl:ResultType Mdid="0.16.1.0"/> + <dxl:OpFunc Mdid="0.60.1.0"/> + <dxl:Commutator Mdid="0.91.1.0"/> + <dxl:InverseOp Mdid="0.85.1.0"/> + <dxl:HashOpfamily Mdid="0.2222.1.0"/> + <dxl:LegacyHashOpfamily Mdid="0.7124.1.0"/> + <dxl:Opfamilies> + <dxl:Opfamily Mdid="0.424.1.0"/> + <dxl:Opfamily Mdid="0.2222.1.0"/> + <dxl:Opfamily Mdid="0.7124.1.0"/> + <dxl:Opfamily Mdid="0.10002.1.0"/> + </dxl:Opfamilies> + </dxl:GPDBScalarOp> + <dxl:RelationExtendedStatistics Mdid="10.112424.1.0" Name="boolpart"/> + </dxl:Metadata> + <dxl:Query> + <dxl:OutputColumns> + <dxl:Ident ColId="1" ColName="a" TypeMdid="0.23.1.0"/> + <dxl:Ident ColId="2" ColName="b" TypeMdid="0.16.1.0"/> + </dxl:OutputColumns> + <dxl:CTEList/> + <dxl:LogicalSelect> + <dxl:IsTrue> + <dxl:Ident ColId="2" ColName="b" TypeMdid="0.16.1.0"/> + </dxl:IsTrue> + <dxl:LogicalGet> + <dxl:TableDescriptor Mdid="6.112424.1.0" TableName="boolpart" LockMode="1"> + <dxl:Columns> + <dxl:Column ColId="1" Attno="1" ColName="a" TypeMdid="0.23.1.0" ColWidth="4"/> + <dxl:Column ColId="2" Attno="2" ColName="b" TypeMdid="0.16.1.0" ColWidth="1"/> + <dxl:Column ColId="3" Attno="-1" ColName="ctid" TypeMdid="0.27.1.0" ColWidth="6"/> + <dxl:Column ColId="4" Attno="-2" ColName="xmin" TypeMdid="0.28.1.0" ColWidth="4"/> + <dxl:Column ColId="5" Attno="-3" ColName="cmin" TypeMdid="0.29.1.0" ColWidth="4"/> + <dxl:Column ColId="6" Attno="-4" ColName="xmax" TypeMdid="0.28.1.0" ColWidth="4"/> + <dxl:Column ColId="7" Attno="-5" ColName="cmax" TypeMdid="0.29.1.0" ColWidth="4"/> + <dxl:Column ColId="8" Attno="-6" ColName="tableoid" TypeMdid="0.26.1.0" ColWidth="4"/> + <dxl:Column ColId="9" Attno="-7" ColName="gp_segment_id" TypeMdid="0.23.1.0" ColWidth="4"/> + </dxl:Columns> + </dxl:TableDescriptor> + </dxl:LogicalGet> + </dxl:LogicalSelect> + </dxl:Query> + <dxl:Plan Id="0" SpaceSize="1"> + <dxl:GatherMotion InputSegments="0,1,2" OutputSegments="-1"> + <dxl:Properties> + <dxl:Cost StartupCost="0" TotalCost="431.000039" Rows="1.000000" Width="5"/> + </dxl:Properties> + <dxl:ProjList> + <dxl:ProjElem ColId="0" Alias="a"> + <dxl:Ident ColId="0" ColName="a" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + <dxl:ProjElem ColId="1" Alias="b"> + <dxl:Ident ColId="1" ColName="b" TypeMdid="0.16.1.0"/> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:Filter/> + <dxl:SortingColumnList/> + <dxl:DynamicTableScan SelectorIds=""> + <dxl:Properties> + <dxl:Cost StartupCost="0" TotalCost="431.000020" Rows="1.000000" Width="5"/> + </dxl:Properties> + <dxl:ProjList> + <dxl:ProjElem ColId="0" Alias="a"> + <dxl:Ident ColId="0" ColName="a" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + <dxl:ProjElem ColId="1" Alias="b"> + <dxl:Ident ColId="1" ColName="b" TypeMdid="0.16.1.0"/> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:Filter> + <dxl:IsTrue> + <dxl:Ident ColId="1" ColName="b" TypeMdid="0.16.1.0"/> + </dxl:IsTrue> + </dxl:Filter> + <dxl:Partitions> + <dxl:Partition Mdid="6.112427.1.0"/> + </dxl:Partitions> + <dxl:TableDescriptor Mdid="6.112424.1.0" TableName="boolpart" LockMode="1"> + <dxl:Columns> + <dxl:Column ColId="0" Attno="1" ColName="a" TypeMdid="0.23.1.0" ColWidth="4"/> + <dxl:Column ColId="1" Attno="2" ColName="b" TypeMdid="0.16.1.0" ColWidth="1"/> + <dxl:Column ColId="2" Attno="-1" ColName="ctid" TypeMdid="0.27.1.0" ColWidth="6"/> + <dxl:Column ColId="3" Attno="-2" ColName="xmin" TypeMdid="0.28.1.0" ColWidth="4"/> + <dxl:Column ColId="4" Attno="-3" ColName="cmin" TypeMdid="0.29.1.0" ColWidth="4"/> + <dxl:Column ColId="5" Attno="-4" ColName="xmax" TypeMdid="0.28.1.0" ColWidth="4"/> + <dxl:Column ColId="6" Attno="-5" ColName="cmax" TypeMdid="0.29.1.0" ColWidth="4"/> + <dxl:Column ColId="7" Attno="-6" ColName="tableoid" TypeMdid="0.26.1.0" ColWidth="4"/> + <dxl:Column ColId="8" Attno="-7" ColName="gp_segment_id" TypeMdid="0.23.1.0" ColWidth="4"/> + </dxl:Columns> + </dxl:TableDescriptor> + </dxl:DynamicTableScan> + </dxl:GatherMotion> + </dxl:Plan> + </dxl:Thread> +</dxl:DXLMessage> diff --git a/src/backend/gporca/data/dxl/minidump/PartTbl-SPE-Boolean2.mdp b/src/backend/gporca/data/dxl/minidump/PartTbl-SPE-Boolean2.mdp new file mode 100644 index 0000000000..49c1f8c646 --- /dev/null +++ b/src/backend/gporca/data/dxl/minidump/PartTbl-SPE-Boolean2.mdp @@ -0,0 +1,461 @@ +<?xml version="1.0" encoding="UTF-8"?> +<dxl:DXLMessage xmlns:dxl="http://greenplum.com/dxl/2010/12/";> + <dxl:Comment><![CDATA[ + Objective: Perform static partition elimination (SPE) on a table partitioned + by a boolean column. In below setup containing 3 leaf tables and predicate + IS TRUE OR IS FALSE, then 2 table should be scanned. + + CREATE TABLE boolpart(a int, b bool) PARTITION BY LIST(b) ( + PARTITION l1 values(true), PARTITION l2 values(false), DEFAULT PARTITION def); + INSERT INTO boolpart values (1,true); + INSERT INTO boolpart values (2, false); + + EXPLAIN SELECT * FROM boolpart WHERE b IS true or b IS false; + ]]> + </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 JoinArityForAssociativityCommutativity="18" ArrayExpansionThreshold="20" JoinOrderDynamicProgThreshold="10" BroadcastThreshold="100000" EnforceConstraintsOnDML="false" PushGroupByBelowSetopThreshold="10" XformBindThreshold="0" SkewFactor="0"/> + <dxl:TraceFlags Value="102001,102002,102003,102043,102074,102120,102144,103001,103014,103022,103026,103027,103029,103033,103038,103040,104002,104003,104004,104005,105000,106000"/> + </dxl:OptimizerConfig> + <dxl:Metadata SystemIds="0.GPDB"> + <dxl: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.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:Type Mdid="0.26.1.0" Name="oid" IsRedistributable="true" IsHashable="true" IsMergeJoinable="true" IsComposite="false" IsTextRelated="false" IsFixedLength="true" Length="4" PassByValue="true"> + <dxl:DistrOpfamily Mdid="0.1990.1.0"/> + <dxl:LegacyDistrOpfamily Mdid="0.7109.1.0"/> + <dxl:EqualityOp Mdid="0.607.1.0"/> + <dxl:InequalityOp Mdid="0.608.1.0"/> + <dxl:LessThanOp Mdid="0.609.1.0"/> + <dxl:LessThanEqualsOp Mdid="0.611.1.0"/> + <dxl:GreaterThanOp Mdid="0.610.1.0"/> + <dxl:GreaterThanEqualsOp Mdid="0.612.1.0"/> + <dxl:ComparisonOp Mdid="0.356.1.0"/> + <dxl:ArrayType Mdid="0.1028.1.0"/> + <dxl:MinAgg Mdid="0.2134.1.0"/> + <dxl:MaxAgg Mdid="0.2118.1.0"/> + <dxl:AvgAgg Mdid="0.0.0.0"/> + <dxl:SumAgg Mdid="0.0.0.0"/> + <dxl:CountAgg Mdid="0.2147.1.0"/> + </dxl:Type> + <dxl:Type Mdid="0.27.1.0" Name="tid" IsRedistributable="true" IsHashable="true" IsMergeJoinable="true" IsComposite="false" IsTextRelated="false" IsFixedLength="true" Length="6" PassByValue="false"> + <dxl:DistrOpfamily Mdid="0.2227.1.0"/> + <dxl:LegacyDistrOpfamily Mdid="0.7110.1.0"/> + <dxl:EqualityOp Mdid="0.387.1.0"/> + <dxl:InequalityOp Mdid="0.402.1.0"/> + <dxl:LessThanOp Mdid="0.2799.1.0"/> + <dxl:LessThanEqualsOp Mdid="0.2801.1.0"/> + <dxl:GreaterThanOp Mdid="0.2800.1.0"/> + <dxl:GreaterThanEqualsOp Mdid="0.2802.1.0"/> + <dxl:ComparisonOp Mdid="0.2794.1.0"/> + <dxl:ArrayType Mdid="0.1010.1.0"/> + <dxl:MinAgg Mdid="0.2798.1.0"/> + <dxl:MaxAgg Mdid="0.2797.1.0"/> + <dxl:AvgAgg Mdid="0.0.0.0"/> + <dxl:SumAgg Mdid="0.0.0.0"/> + <dxl:CountAgg Mdid="0.2147.1.0"/> + </dxl:Type> + <dxl:Type Mdid="0.29.1.0" Name="cid" IsRedistributable="true" IsHashable="true" IsMergeJoinable="false" IsComposite="false" IsTextRelated="false" IsFixedLength="true" Length="4" PassByValue="true"> + <dxl:DistrOpfamily Mdid="0.2226.1.0"/> + <dxl:EqualityOp Mdid="0.385.1.0"/> + <dxl:InequalityOp Mdid="0.0.0.0"/> + <dxl:LessThanOp Mdid="0.0.0.0"/> + <dxl:LessThanEqualsOp Mdid="0.0.0.0"/> + <dxl:GreaterThanOp Mdid="0.0.0.0"/> + <dxl:GreaterThanEqualsOp Mdid="0.0.0.0"/> + <dxl:ComparisonOp Mdid="0.0.0.0"/> + <dxl:ArrayType Mdid="0.1012.1.0"/> + <dxl:MinAgg Mdid="0.0.0.0"/> + <dxl:MaxAgg Mdid="0.0.0.0"/> + <dxl:AvgAgg Mdid="0.0.0.0"/> + <dxl:SumAgg Mdid="0.0.0.0"/> + <dxl:CountAgg Mdid="0.2147.1.0"/> + </dxl:Type> + <dxl:Type Mdid="0.28.1.0" Name="xid" IsRedistributable="true" IsHashable="true" IsMergeJoinable="false" IsComposite="false" IsTextRelated="false" IsFixedLength="true" Length="4" PassByValue="true"> + <dxl:DistrOpfamily Mdid="0.2225.1.0"/> + <dxl:EqualityOp Mdid="0.352.1.0"/> + <dxl:InequalityOp Mdid="0.3315.1.0"/> + <dxl:LessThanOp Mdid="0.0.0.0"/> + <dxl:LessThanEqualsOp Mdid="0.0.0.0"/> + <dxl:GreaterThanOp Mdid="0.0.0.0"/> + <dxl:GreaterThanEqualsOp Mdid="0.0.0.0"/> + <dxl:ComparisonOp Mdid="0.0.0.0"/> + <dxl:ArrayType Mdid="0.1011.1.0"/> + <dxl:MinAgg Mdid="0.0.0.0"/> + <dxl:MaxAgg Mdid="0.0.0.0"/> + <dxl:AvgAgg Mdid="0.0.0.0"/> + <dxl:SumAgg Mdid="0.0.0.0"/> + <dxl:CountAgg Mdid="0.2147.1.0"/> + </dxl:Type> + <dxl:RelationExtendedStatistics Mdid="10.112486.1.0" Name="boolpart"/> + <dxl:ColumnStatistics Mdid="1.112486.1.0.1" Name="b" Width="1.000000" NullFreq="0.000000" NdvRemain="0.000000" FreqRemain="0.000000" ColStatsMissing="true"/> + <dxl:ColumnStatistics Mdid="1.112486.1.0.0" Name="a" Width="4.000000" NullFreq="0.000000" NdvRemain="0.000000" FreqRemain="0.000000" ColStatsMissing="true"/> + <dxl:RelationStatistics Mdid="2.112486.1.0" Name="boolpart" Rows="0.000000" RelPages="0" RelAllVisible="0" EmptyRelation="true"/> + <dxl:Relation Mdid="6.112486.1.0" Name="boolpart" IsTemporary="false" StorageType="Heap" DistributionPolicy="Hash" DistributionColumns="0" Keys="7,8,2" PartitionColumns="1" PartitionTypes="l"> + <dxl:Columns> + <dxl:Column Name="a" Attno="1" Mdid="0.23.1.0" Nullable="true" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="b" Attno="2" Mdid="0.16.1.0" Nullable="true" ColWidth="1"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="ctid" Attno="-1" Mdid="0.27.1.0" Nullable="false" ColWidth="6"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="xmin" Attno="-2" Mdid="0.28.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="cmin" Attno="-3" Mdid="0.29.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="xmax" Attno="-4" Mdid="0.28.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="cmax" Attno="-5" Mdid="0.29.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="tableoid" Attno="-6" Mdid="0.26.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="gp_segment_id" Attno="-7" Mdid="0.23.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + </dxl:Columns> + <dxl:IndexInfoList/> + <dxl:CheckConstraints/> + <dxl:DistrOpfamilies> + <dxl:DistrOpfamily Mdid="0.1977.1.0"/> + </dxl:DistrOpfamilies> + <dxl:Partitions> + <dxl:Partition Mdid="6.112495.1.0"/> + <dxl:Partition Mdid="6.112492.1.0"/> + <dxl:Partition Mdid="6.112489.1.0"/> + </dxl:Partitions> + </dxl:Relation> + <dxl:GPDBScalarOp Mdid="0.58.1.0" Name="<" ComparisonType="LT" ReturnsNullOnNullInput="true" IsNDVPreserving="false"> + <dxl:LeftType Mdid="0.16.1.0"/> + <dxl:RightType Mdid="0.16.1.0"/> + <dxl:ResultType Mdid="0.16.1.0"/> + <dxl:OpFunc Mdid="0.56.1.0"/> + <dxl:Commutator Mdid="0.59.1.0"/> + <dxl:InverseOp Mdid="0.1695.1.0"/> + <dxl:Opfamilies> + <dxl:Opfamily Mdid="0.424.1.0"/> + <dxl:Opfamily Mdid="0.10002.1.0"/> + </dxl:Opfamilies> + </dxl:GPDBScalarOp> + <dxl:GPDBScalarOp Mdid="0.59.1.0" Name=">" ComparisonType="GT" ReturnsNullOnNullInput="true" IsNDVPreserving="false"> + <dxl:LeftType Mdid="0.16.1.0"/> + <dxl:RightType Mdid="0.16.1.0"/> + <dxl:ResultType Mdid="0.16.1.0"/> + <dxl:OpFunc Mdid="0.57.1.0"/> + <dxl:Commutator Mdid="0.58.1.0"/> + <dxl:InverseOp Mdid="0.1694.1.0"/> + <dxl:Opfamilies> + <dxl:Opfamily Mdid="0.424.1.0"/> + <dxl:Opfamily Mdid="0.10002.1.0"/> + </dxl:Opfamilies> + </dxl:GPDBScalarOp> + <dxl:GPDBScalarOp Mdid="0.85.1.0" Name="<>" ComparisonType="NEq" ReturnsNullOnNullInput="true" IsNDVPreserving="false"> + <dxl:LeftType Mdid="0.16.1.0"/> + <dxl:RightType Mdid="0.16.1.0"/> + <dxl:ResultType Mdid="0.16.1.0"/> + <dxl:OpFunc Mdid="0.84.1.0"/> + <dxl:Commutator Mdid="0.85.1.0"/> + <dxl:InverseOp Mdid="0.91.1.0"/> + </dxl:GPDBScalarOp> + <dxl:Relation Mdid="6.112492.1.0" Name="boolpart_1_prt_l1" IsTemporary="false" StorageType="Heap" DistributionPolicy="Hash" DistributionColumns="0" Keys="8,2"> + <dxl:Columns> + <dxl:Column Name="a" Attno="1" Mdid="0.23.1.0" Nullable="true" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="b" Attno="2" Mdid="0.16.1.0" Nullable="true" ColWidth="1"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="ctid" Attno="-1" Mdid="0.27.1.0" Nullable="false" ColWidth="6"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="xmin" Attno="-2" Mdid="0.28.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="cmin" Attno="-3" Mdid="0.29.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="xmax" Attno="-4" Mdid="0.28.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="cmax" Attno="-5" Mdid="0.29.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="tableoid" Attno="-6" Mdid="0.26.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="gp_segment_id" Attno="-7" Mdid="0.23.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + </dxl:Columns> + <dxl:IndexInfoList/> + <dxl:CheckConstraints/> + <dxl:DistrOpfamilies> + <dxl:DistrOpfamily Mdid="0.1977.1.0"/> + </dxl:DistrOpfamilies> + <dxl:PartConstraint> + <dxl:And> + <dxl:IsNotNull> + <dxl:Ident ColId="2" ColName="b" TypeMdid="0.16.1.0"/> + </dxl:IsNotNull> + <dxl:Comparison ComparisonOperator="=" OperatorMdid="0.91.1.0"> + <dxl:Ident ColId="2" ColName="b" TypeMdid="0.16.1.0"/> + <dxl:ConstValue TypeMdid="0.16.1.0" Value="true"/> + </dxl:Comparison> + </dxl:And> + </dxl:PartConstraint> + </dxl:Relation> + <dxl:GPDBScalarOp Mdid="0.91.1.0" Name="=" ComparisonType="Eq" ReturnsNullOnNullInput="true" IsNDVPreserving="false"> + <dxl:LeftType Mdid="0.16.1.0"/> + <dxl:RightType Mdid="0.16.1.0"/> + <dxl:ResultType Mdid="0.16.1.0"/> + <dxl:OpFunc Mdid="0.60.1.0"/> + <dxl:Commutator Mdid="0.91.1.0"/> + <dxl:InverseOp Mdid="0.85.1.0"/> + <dxl:HashOpfamily Mdid="0.2222.1.0"/> + <dxl:LegacyHashOpfamily Mdid="0.7124.1.0"/> + <dxl:Opfamilies> + <dxl:Opfamily Mdid="0.424.1.0"/> + <dxl:Opfamily Mdid="0.2222.1.0"/> + <dxl:Opfamily Mdid="0.7124.1.0"/> + <dxl:Opfamily Mdid="0.10002.1.0"/> + </dxl:Opfamilies> + </dxl:GPDBScalarOp> + <dxl:Relation Mdid="6.112495.1.0" Name="boolpart_1_prt_l2" IsTemporary="false" StorageType="Heap" DistributionPolicy="Hash" DistributionColumns="0" Keys="8,2"> + <dxl:Columns> + <dxl:Column Name="a" Attno="1" Mdid="0.23.1.0" Nullable="true" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="b" Attno="2" Mdid="0.16.1.0" Nullable="true" ColWidth="1"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="ctid" Attno="-1" Mdid="0.27.1.0" Nullable="false" ColWidth="6"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="xmin" Attno="-2" Mdid="0.28.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="cmin" Attno="-3" Mdid="0.29.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="xmax" Attno="-4" Mdid="0.28.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="cmax" Attno="-5" Mdid="0.29.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="tableoid" Attno="-6" Mdid="0.26.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="gp_segment_id" Attno="-7" Mdid="0.23.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + </dxl:Columns> + <dxl:IndexInfoList/> + <dxl:CheckConstraints/> + <dxl:DistrOpfamilies> + <dxl:DistrOpfamily Mdid="0.1977.1.0"/> + </dxl:DistrOpfamilies> + <dxl:PartConstraint> + <dxl:And> + <dxl:IsNotNull> + <dxl:Ident ColId="2" ColName="b" TypeMdid="0.16.1.0"/> + </dxl:IsNotNull> + <dxl:Comparison ComparisonOperator="=" OperatorMdid="0.91.1.0"> + <dxl:Ident ColId="2" ColName="b" TypeMdid="0.16.1.0"/> + <dxl:ConstValue TypeMdid="0.16.1.0" Value="false"/> + </dxl:Comparison> + </dxl:And> + </dxl:PartConstraint> + </dxl:Relation> + <dxl:Relation Mdid="6.112489.1.0" Name="boolpart_1_prt_def" IsTemporary="false" StorageType="Heap" DistributionPolicy="Hash" DistributionColumns="0" Keys="8,2"> + <dxl:Columns> + <dxl:Column Name="a" Attno="1" Mdid="0.23.1.0" Nullable="true" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="b" Attno="2" Mdid="0.16.1.0" Nullable="true" ColWidth="1"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="ctid" Attno="-1" Mdid="0.27.1.0" Nullable="false" ColWidth="6"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="xmin" Attno="-2" Mdid="0.28.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="cmin" Attno="-3" Mdid="0.29.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="xmax" Attno="-4" Mdid="0.28.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="cmax" Attno="-5" Mdid="0.29.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="tableoid" Attno="-6" Mdid="0.26.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="gp_segment_id" Attno="-7" Mdid="0.23.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + </dxl:Columns> + <dxl:IndexInfoList/> + <dxl:CheckConstraints/> + <dxl:DistrOpfamilies> + <dxl:DistrOpfamily Mdid="0.1977.1.0"/> + </dxl:DistrOpfamilies> + <dxl:PartConstraint> + <dxl:Not> + <dxl:And> + <dxl:IsNotNull> + <dxl:Ident ColId="2" ColName="b" TypeMdid="0.16.1.0"/> + </dxl:IsNotNull> + <dxl:ArrayComp OperatorName="=" OperatorMdid="0.91.1.0" OperatorType="Any"> + <dxl:Ident ColId="2" ColName="b" TypeMdid="0.16.1.0"/> + <dxl:Array ArrayType="0.1000.1.0" ElementType="0.16.1.0" MultiDimensional="false"> + <dxl:ConstValue TypeMdid="0.16.1.0" Value="false"/> + <dxl:ConstValue TypeMdid="0.16.1.0" Value="true"/> + </dxl:Array> + </dxl:ArrayComp> + </dxl:And> + </dxl:Not> + </dxl:PartConstraint> + </dxl:Relation> + </dxl:Metadata> + <dxl:Query> + <dxl:OutputColumns> + <dxl:Ident ColId="1" ColName="a" TypeMdid="0.23.1.0"/> + <dxl:Ident ColId="2" ColName="b" TypeMdid="0.16.1.0"/> + </dxl:OutputColumns> + <dxl:CTEList/> + <dxl:LogicalSelect> + <dxl:Or> + <dxl:IsTrue> + <dxl:Ident ColId="2" ColName="b" TypeMdid="0.16.1.0"/> + </dxl:IsTrue> + <dxl:IsFalse> + <dxl:Ident ColId="2" ColName="b" TypeMdid="0.16.1.0"/> + </dxl:IsFalse> + </dxl:Or> + <dxl:LogicalGet> + <dxl:TableDescriptor Mdid="6.112486.1.0" TableName="boolpart" LockMode="1"> + <dxl:Columns> + <dxl:Column ColId="1" Attno="1" ColName="a" TypeMdid="0.23.1.0" ColWidth="4"/> + <dxl:Column ColId="2" Attno="2" ColName="b" TypeMdid="0.16.1.0" ColWidth="1"/> + <dxl:Column ColId="3" Attno="-1" ColName="ctid" TypeMdid="0.27.1.0" ColWidth="6"/> + <dxl:Column ColId="4" Attno="-2" ColName="xmin" TypeMdid="0.28.1.0" ColWidth="4"/> + <dxl:Column ColId="5" Attno="-3" ColName="cmin" TypeMdid="0.29.1.0" ColWidth="4"/> + <dxl:Column ColId="6" Attno="-4" ColName="xmax" TypeMdid="0.28.1.0" ColWidth="4"/> + <dxl:Column ColId="7" Attno="-5" ColName="cmax" TypeMdid="0.29.1.0" ColWidth="4"/> + <dxl:Column ColId="8" Attno="-6" ColName="tableoid" TypeMdid="0.26.1.0" ColWidth="4"/> + <dxl:Column ColId="9" Attno="-7" ColName="gp_segment_id" TypeMdid="0.23.1.0" ColWidth="4"/> + </dxl:Columns> + </dxl:TableDescriptor> + </dxl:LogicalGet> + </dxl:LogicalSelect> + </dxl:Query> + <dxl:Plan Id="0" SpaceSize="1"> + <dxl:GatherMotion InputSegments="0,1,2" OutputSegments="-1"> + <dxl:Properties> + <dxl:Cost StartupCost="0" TotalCost="431.000039" Rows="1.000000" Width="5"/> + </dxl:Properties> + <dxl:ProjList> + <dxl:ProjElem ColId="0" Alias="a"> + <dxl:Ident ColId="0" ColName="a" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + <dxl:ProjElem ColId="1" Alias="b"> + <dxl:Ident ColId="1" ColName="b" TypeMdid="0.16.1.0"/> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:Filter/> + <dxl:SortingColumnList/> + <dxl:DynamicTableScan SelectorIds=""> + <dxl:Properties> + <dxl:Cost StartupCost="0" TotalCost="431.000020" Rows="1.000000" Width="5"/> + </dxl:Properties> + <dxl:ProjList> + <dxl:ProjElem ColId="0" Alias="a"> + <dxl:Ident ColId="0" ColName="a" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + <dxl:ProjElem ColId="1" Alias="b"> + <dxl:Ident ColId="1" ColName="b" TypeMdid="0.16.1.0"/> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:Filter> + <dxl:Or> + <dxl:IsTrue> + <dxl:Ident ColId="1" ColName="b" TypeMdid="0.16.1.0"/> + </dxl:IsTrue> + <dxl:IsFalse> + <dxl:Ident ColId="1" ColName="b" TypeMdid="0.16.1.0"/> + </dxl:IsFalse> + </dxl:Or> + </dxl:Filter> + <dxl:Partitions> + <dxl:Partition Mdid="6.112495.1.0"/> + <dxl:Partition Mdid="6.112492.1.0"/> + </dxl:Partitions> + <dxl:TableDescriptor Mdid="6.112486.1.0" TableName="boolpart" LockMode="1"> + <dxl:Columns> + <dxl:Column ColId="0" Attno="1" ColName="a" TypeMdid="0.23.1.0" ColWidth="4"/> + <dxl:Column ColId="1" Attno="2" ColName="b" TypeMdid="0.16.1.0" ColWidth="1"/> + <dxl:Column ColId="2" Attno="-1" ColName="ctid" TypeMdid="0.27.1.0" ColWidth="6"/> + <dxl:Column ColId="3" Attno="-2" ColName="xmin" TypeMdid="0.28.1.0" ColWidth="4"/> + <dxl:Column ColId="4" Attno="-3" ColName="cmin" TypeMdid="0.29.1.0" ColWidth="4"/> + <dxl:Column ColId="5" Attno="-4" ColName="xmax" TypeMdid="0.28.1.0" ColWidth="4"/> + <dxl:Column ColId="6" Attno="-5" ColName="cmax" TypeMdid="0.29.1.0" ColWidth="4"/> + <dxl:Column ColId="7" Attno="-6" ColName="tableoid" TypeMdid="0.26.1.0" ColWidth="4"/> + <dxl:Column ColId="8" Attno="-7" ColName="gp_segment_id" TypeMdid="0.23.1.0" ColWidth="4"/> + </dxl:Columns> + </dxl:TableDescriptor> + </dxl:DynamicTableScan> + </dxl:GatherMotion> + </dxl:Plan> + </dxl:Thread> +</dxl:DXLMessage> diff --git a/src/backend/gporca/libgpopt/include/gpopt/base/CConstraintInterval.h b/src/backend/gporca/libgpopt/include/gpopt/base/CConstraintInterval.h index 54c2595fce..e71f572a18 100644 --- a/src/backend/gporca/libgpopt/include/gpopt/base/CConstraintInterval.h +++ b/src/backend/gporca/libgpopt/include/gpopt/base/CConstraintInterval.h @@ -113,6 +113,15 @@ private: static CConstraintInterval *PciIntervalFromScalarNullTest( CMemoryPool *mp, CExpression *pexpr, CColRef *colref); + // create interval from scalar boolean test + static CConstraintInterval *PciIntervalFromScalarBooleanTest( + CMemoryPool *mp, CExpression *pexpr, CColRef *colref); + + // create interval from bool scalar ident + static CConstraintInterval *PciIntervalFromScalarIdent(CMemoryPool *mp, + CColRef *colref, + BOOL infer_nulls_as); + // creates a range like [x,x] where x is a constant static CRangeArray *PciRangeFromColConstCmp(CMemoryPool *mp, IMDType::ECmpType cmp_type, diff --git a/src/backend/gporca/libgpopt/include/gpopt/base/CUtils.h b/src/backend/gporca/libgpopt/include/gpopt/base/CUtils.h index 3d5ec8c1c1..3735b31079 100644 --- a/src/backend/gporca/libgpopt/include/gpopt/base/CUtils.h +++ b/src/backend/gporca/libgpopt/include/gpopt/base/CUtils.h @@ -655,6 +655,9 @@ public: static BOOL FScalarBoolOp(CExpression *pexpr, CScalarBoolOp::EBoolOperator eboolop); + // check if expression is scalar bool test op + static BOOL FScalarBooleanTest(CExpression *pexpr); + // check if expression is scalar null test static BOOL FScalarNullTest(CExpression *pexpr); diff --git a/src/backend/gporca/libgpopt/src/base/CConstraintInterval.cpp b/src/backend/gporca/libgpopt/src/base/CConstraintInterval.cpp index 3c48dc405c..70a6ab5d1d 100644 --- a/src/backend/gporca/libgpopt/src/base/CConstraintInterval.cpp +++ b/src/backend/gporca/libgpopt/src/base/CConstraintInterval.cpp @@ -23,9 +23,12 @@ #include "gpopt/base/CUtils.h" #include "gpopt/operators/CPredicateUtils.h" #include "gpopt/operators/CScalarArray.h" +#include "gpopt/operators/CScalarBooleanTest.h" #include "gpopt/operators/CScalarIdent.h" #include "gpopt/operators/CScalarIsDistinctFrom.h" +#include "naucrates/base/IDatumBool.h" #include "naucrates/md/IMDScalarOp.h" +#include "naucrates/md/IMDTypeBool.h" using namespace gpopt; @@ -75,7 +78,27 @@ CConstraintInterval::~CConstraintInterval() BOOL CConstraintInterval::FContradiction() const { - return (!m_fIncludesNull && 0 == m_pdrgprng->Size()); + if (!m_fIncludesNull && 0 == m_pdrgprng->Size()) + { + return true; + } + + // Constraint on boolean column is special case because only 2 values exist + // in the domain space [0,1]. If both ends are exclude then the constraint + // is a contradiction. + if (m_pcr->RetrieveType()->GetDatumType() == IMDType::EtiBool && + m_pdrgprng->Size() == 1 && !m_fIncludesNull) + { + if ((*m_pdrgprng)[0]->EriLeft() == CRange::EriExcluded && + (*m_pdrgprng)[0]->EriRight() == CRange::EriExcluded && + (*m_pdrgprng)[0]->PdatumLeft() != nullptr && + (*m_pdrgprng)[0]->PdatumRight() != nullptr) + { + return true; + } + } + + return false; } //--------------------------------------------------------------------------- @@ -174,10 +197,16 @@ CConstraintInterval::PciIntervalFromScalarExpr( case COperator::EopScalarNullTest: pci = PciIntervalFromScalarNullTest(mp, pexpr, colref); break; + case COperator::EopScalarBooleanTest: + pci = PciIntervalFromScalarBooleanTest(mp, pexpr, colref); + break; case COperator::EopScalarBoolOp: pci = PciIntervalFromScalarBoolOp(mp, pexpr, colref, infer_nulls_as, access_method); break; + case COperator::EopScalarIdent: + pci = PciIntervalFromScalarIdent(mp, colref, infer_nulls_as); + break; case COperator::EopScalarCmp: pci = PciIntervalFromScalarCmp(mp, pexpr, colref, infer_nulls_as, access_method); @@ -405,6 +434,84 @@ CConstraintInterval::PciIntervalFromScalarNullTest(CMemoryPool *mp, return nullptr; } +//--------------------------------------------------------------------------- +// @function: +// CConstraintInterval::PciIntervalFromScalarBooleanTest +// +// @doc: +// Create interval from scalar null test +// +//--------------------------------------------------------------------------- +CConstraintInterval * +CConstraintInterval::PciIntervalFromScalarBooleanTest(CMemoryPool *mp, + CExpression *pexpr, + CColRef *colref) +{ + GPOS_ASSERT(nullptr != pexpr); + GPOS_ASSERT(CUtils::FScalarBooleanTest(pexpr)); + + CScalarBooleanTest *pop = CScalarBooleanTest::PopConvert(pexpr->Pop()); + GPOS_ASSERT(nullptr != pop); + + CRangeArray *pdrngprng = GPOS_NEW(mp) CRangeArray(mp); + CMDAccessor *md_accessor = COptCtxt::PoctxtFromTLS()->Pmda(); + const IMDTypeBool *pmdtypebool = md_accessor->PtMDType<IMDTypeBool>(); + const IComparator *pcomp = COptCtxt::PoctxtFromTLS()->Pcomp(); + bool fIncludesNull = false; + switch (pop->Ebt()) + { + case CScalarBooleanTest::EbtIsTrue: + case CScalarBooleanTest::EbtIsNotFalse: + { + if (pop->Ebt() == CScalarBooleanTest::EbtIsNotFalse) + { + fIncludesNull = true; + } + + IDatumBool *datum = + pmdtypebool->CreateBoolDatum(mp, true, false /*is_null*/); + CRange *prange = + GPOS_NEW(mp) CRange(pcomp, IMDType::EcmptEq, datum); + pdrngprng->Append(prange); + break; + } + case CScalarBooleanTest::EbtIsNotTrue: + case CScalarBooleanTest::EbtIsFalse: + { + if (pop->Ebt() == CScalarBooleanTest::EbtIsNotTrue) + { + fIncludesNull = true; + } + + IDatumBool *datum = + pmdtypebool->CreateBoolDatum(mp, false, false /*is_null*/); + CRange *prange = + GPOS_NEW(mp) CRange(pcomp, IMDType::EcmptEq, datum); + pdrngprng->Append(prange); + break; + } + case CScalarBooleanTest::EbtIsUnknown: + case CScalarBooleanTest::EbtIsNotUnknown: + { + IDatumBool *datum = + pmdtypebool->CreateBoolDatum(mp, false, false /*is_null*/); + CRange *prange = + GPOS_NEW(mp) CRange(pcomp, IMDType::EcmptOther, datum); + pdrngprng->Append(prange); + fIncludesNull = true; + break; + } + default: + { + GPOS_ASSERT(false && "Unknown boolean test type"); + break; + } + } + + return GPOS_NEW(mp) + CConstraintInterval(mp, colref, pdrngprng, fIncludesNull); +} + //--------------------------------------------------------------------------- // @function: @@ -617,6 +724,33 @@ CConstraintInterval::PciIntervalFromScalarBoolOp( } } +//--------------------------------------------------------------------------- +// @function: +// CConstraintInterval::PciIntervalFromScalarIdent +// +// @doc: +// Create interval from boolean scalar ident +// +//--------------------------------------------------------------------------- +CConstraintInterval * +CConstraintInterval::PciIntervalFromScalarIdent(CMemoryPool *mp, + CColRef *colref, + BOOL infer_nulls_as) +{ + GPOS_ASSERT(colref->RetrieveType()->GetDatumType() == IMDType::EtiBool); + + CRangeArray *pdrngprng = GPOS_NEW(mp) CRangeArray(mp); + CMDAccessor *md_accessor = COptCtxt::PoctxtFromTLS()->Pmda(); + const IMDTypeBool *pmdtypebool = md_accessor->PtMDType<IMDTypeBool>(); + IDatumBool *datum = + pmdtypebool->CreateBoolDatum(mp, true, false /*is_null*/); + pdrngprng->Append(GPOS_NEW(mp) CRange(COptCtxt::PoctxtFromTLS()->Pcomp(), + IMDType::EcmptEq, datum)); + + return GPOS_NEW(mp) CConstraintInterval(mp, colref, pdrngprng, + infer_nulls_as /*fIncludesNull*/); +} + //--------------------------------------------------------------------------- // @function: // CConstraintInterval::PciIntervalFromScalarBoolOr @@ -1282,12 +1416,30 @@ CConstraintInterval::PciUnbounded(CMemoryPool *mp, const CColRef *colref, } mdid->AddRef(); - CRange *prange = GPOS_NEW(mp) CRange( - mdid, COptCtxt::PoctxtFromTLS()->Pcomp(), nullptr /*ppointLeft*/, - CRange::EriExcluded, nullptr /*ppointRight*/, CRange::EriExcluded); CRangeArray *pdrgprng = GPOS_NEW(mp) CRangeArray(mp); - pdrgprng->Append(prange); + if (colref->RetrieveType()->GetDatumType() == IMDType::EtiBool) + { + // valid boolean constraint values must map in the range [0, 1] + CMDAccessor *md_accessor = COptCtxt::PoctxtFromTLS()->Pmda(); + const IMDTypeBool *pmdtypebool = md_accessor->PtMDType<IMDTypeBool>(); + CRange *prange = GPOS_NEW(mp) + CRange(mdid, COptCtxt::PoctxtFromTLS()->Pcomp(), + pmdtypebool->CreateBoolDatum( + mp, false, false /*is_null*/) /*ppointLeft*/, + CRange::EriIncluded, + pmdtypebool->CreateBoolDatum( + mp, true, false /*is_null*/) /*ppointRight*/, + CRange::EriIncluded); + pdrgprng->Append(prange); + } + else + { + CRange *prange = GPOS_NEW(mp) CRange( + mdid, COptCtxt::PoctxtFromTLS()->Pcomp(), nullptr /*ppointLeft*/, + CRange::EriExcluded, nullptr /*ppointRight*/, CRange::EriExcluded); + pdrgprng->Append(prange); + } return GPOS_NEW(mp) CConstraintInterval(mp, colref, pdrgprng, fIncludesNull); diff --git a/src/backend/gporca/libgpopt/src/base/CDefaultComparator.cpp b/src/backend/gporca/libgpopt/src/base/CDefaultComparator.cpp index fc33632dbd..285ac191a7 100644 --- a/src/backend/gporca/libgpopt/src/base/CDefaultComparator.cpp +++ b/src/backend/gporca/libgpopt/src/base/CDefaultComparator.cpp @@ -115,6 +115,7 @@ CDefaultComparator::FUseInternalEvaluator(const IDatum *datum1, // double value if (mdid1->Equals(datum2->MDId()) && datum1->StatsAreComparable(datum2) && (CMDIdGPDB::m_mdid_date.Equals(mdid1) || + CMDIdGPDB::m_mdid_bool.Equals(mdid1) || CMDIdGPDB::m_mdid_time.Equals(mdid1) || CMDIdGPDB::m_mdid_timestamp.Equals(mdid1) || CMDIdGPDB::m_mdid_float4.Equals(mdid1) || diff --git a/src/backend/gporca/libgpopt/src/base/CUtils.cpp b/src/backend/gporca/libgpopt/src/base/CUtils.cpp index 6552a44940..e8735b3a3c 100644 --- a/src/backend/gporca/libgpopt/src/base/CUtils.cpp +++ b/src/backend/gporca/libgpopt/src/base/CUtils.cpp @@ -3027,6 +3027,13 @@ CUtils::FScalarBoolOp(CExpression *pexpr, CScalarBoolOp::EBoolOperator eboolop) eboolop == CScalarBoolOp::PopConvert(pop)->Eboolop(); } +// check if given expression is a boolean test +BOOL +CUtils::FScalarBooleanTest(CExpression *pexpr) +{ + return (COperator::EopScalarBooleanTest == pexpr->Pop()->Eopid()); +} + // check if given expression is a scalar null test BOOL CUtils::FScalarNullTest(CExpression *pexpr) @@ -3168,6 +3175,7 @@ CUtils::FConstrainableType(IMDId *mdid_type) { // also allow date/time/timestamp/float4/float8 return (CMDIdGPDB::m_mdid_date.Equals(mdid_type) || + CMDIdGPDB::m_mdid_bool.Equals(mdid_type) || CMDIdGPDB::m_mdid_time.Equals(mdid_type) || CMDIdGPDB::m_mdid_timestamp.Equals(mdid_type) || CMDIdGPDB::m_mdid_timeTz.Equals(mdid_type) || diff --git a/src/backend/gporca/server/CMakeLists.txt b/src/backend/gporca/server/CMakeLists.txt index 2d78e2020d..244ae4fcd3 100644 --- a/src/backend/gporca/server/CMakeLists.txt +++ b/src/backend/gporca/server/CMakeLists.txt @@ -243,6 +243,9 @@ PartTbl-SPE-DynamicTableScan-Range-Cost5 PartTbl-SPE-DynamicTableScan-List-Cost1 PartTbl-SPE-DynamicTableScan-List-Cost2 PartTbl-SPE-DynamicTableScan-List-Cost3 PartTbl-SPE-DynamicTableScan-List-Cost4 PartTbl-SPE-DynamicTableScan-List-Cost5; +CPartTblSPEBoolTest: +PartTbl-SPE-Boolean1 PartTbl-SPE-Boolean2; + CSetop1Test: ValueScanWithDuplicateAndSelfComparison PushGbBelowNaryUnionAll PushGbBelowNaryUnion-1 PushGbBelowNaryUnion-2 MS-UnionAll-1 diff --git a/src/backend/gporca/server/src/unittest/gpopt/translate/CTranslatorExprToDXLTest.cpp b/src/backend/gporca/server/src/unittest/gpopt/translate/CTranslatorExprToDXLTest.cpp index b44eede46b..190eaf65a8 100644 --- a/src/backend/gporca/server/src/unittest/gpopt/translate/CTranslatorExprToDXLTest.cpp +++ b/src/backend/gporca/server/src/unittest/gpopt/translate/CTranslatorExprToDXLTest.cpp @@ -167,12 +167,10 @@ const CTestUtils::STestCase rgtc[] = { "../data/dxl/expressiontests/DynamicGetNLJoinPartKeyPlan.xml"}, {"../data/dxl/expressiontests/DynamicGetNLJoinOtherKeyQuery.xml", "../data/dxl/expressiontests/DynamicGetNLJoinOtherKeyPlan.xml"}, - // GPDB_12_MERGE_FIXME: Re-enable once ORCA supports constraint derivation on - // bool columns - // {"../data/dxl/expressiontests/DynamicGetBooleanQuery.xml", - // "../data/dxl/expressiontests/DynamicGetBooleanPlan.xml"}, - // {"../data/dxl/expressiontests/DynamicGetBooleanNotQuery.xml", - // "../data/dxl/expressiontests/DynamicGetBooleanNotPlan.xml"}, + {"../data/dxl/expressiontests/DynamicGetBooleanQuery.xml", + "../data/dxl/expressiontests/DynamicGetBooleanPlan.xml"}, + {"../data/dxl/expressiontests/DynamicGetBooleanNotQuery.xml", + "../data/dxl/expressiontests/DynamicGetBooleanNotPlan.xml"}, {"../data/dxl/expressiontests/DynamicGetMultiJoinQuery.xml", "../data/dxl/expressiontests/DynamicGetMultiJoinPlan.xml"}, {"../data/dxl/expressiontests/CoalesceQuery.xml", diff --git a/src/test/regress/expected/partition_prune_optimizer.out b/src/test/regress/expected/partition_prune_optimizer.out index f7d6a479f4..cc433eff42 100644 --- a/src/test/regress/expected/partition_prune_optimizer.out +++ b/src/test/regress/expected/partition_prune_optimizer.out @@ -1190,7 +1190,7 @@ explain (costs off) select * from boolpart where a = false; ------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on boolpart - Number of partitions to scan: 3 (out of 3) + Number of partitions to scan: 1 (out of 3) Filter: (NOT a) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -1200,7 +1200,7 @@ explain (costs off) select * from boolpart where not a = false; ------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on boolpart - Number of partitions to scan: 3 (out of 3) + Number of partitions to scan: 1 (out of 3) Filter: a Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -1220,17 +1220,17 @@ explain (costs off) select * from boolpart where a is not true; ------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on boolpart - Number of partitions to scan: 3 (out of 3) + Number of partitions to scan: 2 (out of 3) Filter: (a IS NOT TRUE) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) explain (costs off) select * from boolpart where a is not true and a is not false; - QUERY PLAN --------------------------------------------------------- - Gather Motion 3:1 (slice1; segments: 3) + QUERY PLAN +--------------------------------------- + Gather Motion 1:1 (slice1; segments: 1) -> Dynamic Seq Scan on boolpart - Number of partitions to scan: 3 (out of 3) + Number of partitions to scan: 1 (out of 3) Filter: ((a IS NOT TRUE) AND (a IS NOT FALSE)) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) diff --git a/src/test/regress/expected/partition_pruning.out b/src/test/regress/expected/partition_pruning.out index 91cb2ea69c..b2c44dd8b1 100644 --- a/src/test/regress/expected/partition_pruning.out +++ b/src/test/regress/expected/partition_pruning.out @@ -3011,4 +3011,484 @@ select get_selected_parts('explain analyze select * from bar where j is distinct [0, 0] (1 row) +-- Table partitioned by boolean column +CREATE TABLE pt_bool_tab +( + col1 int, + col2 bool +) +distributed by (col1) +partition by list(col2) +( + partition part1 values(true), + partition part2 values(false) +); +INSERT INTO pt_bool_tab SELECT i, true FROM generate_series(1,3)i; +INSERT INTO pt_bool_tab SELECT i, false FROM generate_series(1,2)i; +EXPLAIN SELECT * FROM pt_bool_tab WHERE col2 IS true; + QUERY PLAN +----------------------------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000000969.00 rows=46750 width=5) + -> Seq Scan on pt_bool_tab_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + Filter: (col2 IS TRUE) + Optimizer: Postgres query optimizer +(4 rows) + +SELECT * FROM pt_bool_tab WHERE col2 IS true; + col1 | col2 +------+------ + 1 | t + 2 | t + 3 | t +(3 rows) + +EXPLAIN SELECT * FROM pt_bool_tab WHERE col2 IS false; + QUERY PLAN +----------------------------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000000969.00 rows=46750 width=5) + -> Seq Scan on pt_bool_tab_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + Filter: (col2 IS FALSE) + Optimizer: Postgres query optimizer +(4 rows) + +SELECT * FROM pt_bool_tab WHERE col2 IS false; + col1 | col2 +------+------ + 2 | f + 1 | f +(2 rows) + +EXPLAIN SELECT * FROM pt_bool_tab WHERE col2 IS NULL; + QUERY PLAN +------------------------------------------ + Result (cost=0.00..0.00 rows=0 width=0) + One-Time Filter: false + Optimizer: Postgres query optimizer +(3 rows) + +SELECT * FROM pt_bool_tab WHERE col2 IS NULL; + col1 | col2 +------+------ +(0 rows) + +EXPLAIN SELECT * FROM pt_bool_tab WHERE col2 IS unknown; + QUERY PLAN +-------------------------------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..20000000694.14 rows=187 width=5) + -> Append (cost=10000000000.00..20000000691.65 rows=62 width=5) + -> Seq Scan on pt_bool_tab_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=31 width=5) + Filter: (col2 IS UNKNOWN) + -> Seq Scan on pt_bool_tab_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=31 width=5) + Filter: (col2 IS UNKNOWN) + Optimizer: Postgres query optimizer +(7 rows) + +SELECT * FROM pt_bool_tab WHERE col2 IS unknown; + col1 | col2 +------+------ +(0 rows) + +EXPLAIN SELECT * FROM pt_bool_tab WHERE col2 IS NOT true; + QUERY PLAN +----------------------------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000000969.00 rows=46750 width=5) + -> Seq Scan on pt_bool_tab_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + Filter: (col2 IS NOT TRUE) + Optimizer: Postgres query optimizer +(4 rows) + +SELECT * FROM pt_bool_tab WHERE col2 IS NOT true; + col1 | col2 +------+------ + 2 | f + 1 | f +(2 rows) + +EXPLAIN SELECT * FROM pt_bool_tab WHERE col2 IS NOT false; + QUERY PLAN +----------------------------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000000969.00 rows=46750 width=5) + -> Seq Scan on pt_bool_tab_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + Filter: (col2 IS NOT FALSE) + Optimizer: Postgres query optimizer +(4 rows) + +SELECT * FROM pt_bool_tab WHERE col2 IS NOT false; + col1 | col2 +------+------ + 2 | t + 3 | t + 1 | t +(3 rows) + +EXPLAIN SELECT * FROM pt_bool_tab WHERE col2 IS NOT unknown; + QUERY PLAN +----------------------------------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..20000003493.53 rows=186813 width=5) + -> Append (cost=10000000000.00..20000001002.69 rows=62271 width=5) + -> Seq Scan on pt_bool_tab_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=31136 width=5) + Filter: (col2 IS NOT UNKNOWN) + -> Seq Scan on pt_bool_tab_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=31136 width=5) + Filter: (col2 IS NOT UNKNOWN) + Optimizer: Postgres query optimizer +(7 rows) + +SELECT * FROM pt_bool_tab WHERE col2 IS NOT unknown; + col1 | col2 +------+------ + 1 | f + 1 | t + 2 | f + 2 | t + 3 | t +(5 rows) + +EXPLAIN SELECT * FROM pt_bool_tab WHERE col2 IS NOT NULL; + QUERY PLAN +----------------------------------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..20000003493.53 rows=186813 width=5) + -> Append (cost=10000000000.00..20000001002.69 rows=62271 width=5) + -> Seq Scan on pt_bool_tab_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=31136 width=5) + Filter: (col2 IS NOT NULL) + -> Seq Scan on pt_bool_tab_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=31136 width=5) + Filter: (col2 IS NOT NULL) + Optimizer: Postgres query optimizer +(7 rows) + +SELECT * FROM pt_bool_tab WHERE col2 IS NOT NULL; + col1 | col2 +------+------ + 1 | f + 1 | t + 2 | f + 2 | t + 3 | t +(5 rows) + +CREATE TABLE pt_bool_tab_df +( + col1 int, + col2 bool +) +distributed by (col1) +partition by list(col2) +( + partition part1 values(true), + partition part2 values(false), + default partition def +); +INSERT INTO pt_bool_tab_df SELECT i, true FROM generate_series(1,3)i; +INSERT INTO pt_bool_tab_df SELECT i, false FROM generate_series(1,2)i; +INSERT INTO pt_bool_tab_df SELECT i, NULL FROM generate_series(1,1)i; +EXPLAIN SELECT * FROM pt_bool_tab_df WHERE col2 IS true; + QUERY PLAN +-------------------------------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000000969.00 rows=46750 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + Filter: (col2 IS TRUE) + Optimizer: Postgres query optimizer +(4 rows) + +SELECT * FROM pt_bool_tab_df WHERE col2 IS true; + col1 | col2 +------+------ + 1 | t + 2 | t + 3 | t +(3 rows) + +EXPLAIN SELECT * FROM pt_bool_tab_df WHERE col2 IS false; + QUERY PLAN +-------------------------------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000000969.00 rows=46750 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + Filter: (col2 IS FALSE) + Optimizer: Postgres query optimizer +(4 rows) + +SELECT * FROM pt_bool_tab_df WHERE col2 IS false; + col1 | col2 +------+------ + 2 | f + 1 | f +(2 rows) + +EXPLAIN SELECT * FROM pt_bool_tab_df WHERE col2 IS NULL; + QUERY PLAN +--------------------------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000000346.91 rows=94 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_def (cost=10000000000.00..10000000345.67 rows=31 width=5) + Filter: (col2 IS NULL) + Optimizer: Postgres query optimizer +(4 rows) + +SELECT * FROM pt_bool_tab_df WHERE col2 IS NULL; + col1 | col2 +------+------ + 1 | +(1 row) + +EXPLAIN SELECT * FROM pt_bool_tab_df WHERE col2 IS unknown; + QUERY PLAN +----------------------------------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..30000001041.21 rows=280 width=5) + -> Append (cost=10000000000.00..30000001037.47 rows=94 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=31 width=5) + Filter: (col2 IS UNKNOWN) + -> Seq Scan on pt_bool_tab_df_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=31 width=5) + Filter: (col2 IS UNKNOWN) + -> Seq Scan on pt_bool_tab_df_1_prt_def (cost=10000000000.00..10000000345.67 rows=31 width=5) + Filter: (col2 IS UNKNOWN) + Optimizer: Postgres query optimizer +(9 rows) + +SELECT * FROM pt_bool_tab_df WHERE col2 IS unknown; + col1 | col2 +------+------ + 1 | +(1 row) + +EXPLAIN SELECT * FROM pt_bool_tab_df WHERE col2 IS NOT true; + QUERY PLAN +-------------------------------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000000969.00 rows=46750 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + Filter: (col2 IS NOT TRUE) + Optimizer: Postgres query optimizer +(4 rows) + +SELECT * FROM pt_bool_tab_df WHERE col2 IS NOT true; + col1 | col2 +------+------ + 2 | f + 1 | f +(2 rows) + +EXPLAIN SELECT * FROM pt_bool_tab_df WHERE col2 IS NOT false; + QUERY PLAN +-------------------------------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000000969.00 rows=46750 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + Filter: (col2 IS NOT FALSE) + Optimizer: Postgres query optimizer +(4 rows) + +SELECT * FROM pt_bool_tab_df WHERE col2 IS NOT false; + col1 | col2 +------+------ + 2 | t + 3 | t + 1 | t +(3 rows) + +EXPLAIN SELECT * FROM pt_bool_tab_df WHERE col2 IS NOT unknown; + QUERY PLAN +-------------------------------------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..30000005240.29 rows=280220 width=5) + -> Append (cost=10000000000.00..30000001504.03 rows=93406 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=31136 width=5) + Filter: (col2 IS NOT UNKNOWN) + -> Seq Scan on pt_bool_tab_df_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=31136 width=5) + Filter: (col2 IS NOT UNKNOWN) + -> Seq Scan on pt_bool_tab_df_1_prt_def (cost=10000000000.00..10000000345.67 rows=31136 width=5) + Filter: (col2 IS NOT UNKNOWN) + Optimizer: Postgres query optimizer +(9 rows) + +SELECT * FROM pt_bool_tab_df WHERE col2 IS NOT unknown; + col1 | col2 +------+------ + 2 | f + 2 | t + 3 | t + 1 | f + 1 | t +(5 rows) + +EXPLAIN SELECT * FROM pt_bool_tab_df WHERE col2 IS NOT NULL; + QUERY PLAN +-------------------------------------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..30000005240.29 rows=280220 width=5) + -> Append (cost=10000000000.00..30000001504.03 rows=93406 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=31136 width=5) + Filter: (col2 IS NOT NULL) + -> Seq Scan on pt_bool_tab_df_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=31136 width=5) + Filter: (col2 IS NOT NULL) + -> Seq Scan on pt_bool_tab_df_1_prt_def (cost=10000000000.00..10000000345.67 rows=31136 width=5) + Filter: (col2 IS NOT NULL) + Optimizer: Postgres query optimizer +(9 rows) + +SELECT * FROM pt_bool_tab_df WHERE col2 IS NOT NULL; + col1 | col2 +------+------ + 2 | f + 2 | t + 3 | t + 1 | f + 1 | t +(5 rows) + +CREATE TABLE pt_bool_tab_null +( + col1 int, + col2 bool +) +distributed by (col1) +partition by list(col2) +( + partition part1 values(true), + partition part2 values(false), + partition part3 values(null) +); +INSERT INTO pt_bool_tab_null SELECT i, true FROM generate_series(1,3)i; +INSERT INTO pt_bool_tab_null SELECT i, false FROM generate_series(1,2)i; +INSERT INTO pt_bool_tab_null SELECT i, NULL FROM generate_series(1,1)i; +EXPLAIN SELECT * FROM pt_bool_tab_null WHERE col2 IS true; + QUERY PLAN +---------------------------------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000000969.00 rows=46750 width=5) + -> Seq Scan on pt_bool_tab_null_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + Filter: (col2 IS TRUE) + Optimizer: Postgres query optimizer +(4 rows) + +SELECT * FROM pt_bool_tab_null WHERE col2 IS true; + col1 | col2 +------+------ + 1 | t + 2 | t + 3 | t +(3 rows) + +EXPLAIN SELECT * FROM pt_bool_tab_null WHERE col2 IS false; + QUERY PLAN +---------------------------------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000000969.00 rows=46750 width=5) + -> Seq Scan on pt_bool_tab_null_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + Filter: (col2 IS FALSE) + Optimizer: Postgres query optimizer +(4 rows) + +SELECT * FROM pt_bool_tab_null WHERE col2 IS false; + col1 | col2 +------+------ + 1 | f + 2 | f +(2 rows) + +EXPLAIN SELECT * FROM pt_bool_tab_null WHERE col2 IS NULL; + QUERY PLAN +------------------------------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000000346.91 rows=94 width=5) + -> Seq Scan on pt_bool_tab_null_1_prt_part3 (cost=10000000000.00..10000000345.67 rows=31 width=5) + Filter: (col2 IS NULL) + Optimizer: Postgres query optimizer +(4 rows) + +SELECT * FROM pt_bool_tab_null WHERE col2 IS NULL; + col1 | col2 +------+------ + 1 | +(1 row) + +EXPLAIN SELECT * FROM pt_bool_tab_null WHERE col2 IS unknown; + QUERY PLAN +------------------------------------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..30000001041.21 rows=280 width=5) + -> Append (cost=10000000000.00..30000001037.47 rows=94 width=5) + -> Seq Scan on pt_bool_tab_null_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=31 width=5) + Filter: (col2 IS UNKNOWN) + -> Seq Scan on pt_bool_tab_null_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=31 width=5) + Filter: (col2 IS UNKNOWN) + -> Seq Scan on pt_bool_tab_null_1_prt_part3 (cost=10000000000.00..10000000345.67 rows=31 width=5) + Filter: (col2 IS UNKNOWN) + Optimizer: Postgres query optimizer +(9 rows) + +SELECT * FROM pt_bool_tab_null WHERE col2 IS unknown; + col1 | col2 +------+------ + 1 | +(1 row) + +EXPLAIN SELECT * FROM pt_bool_tab_null WHERE col2 IS NOT true; + QUERY PLAN +---------------------------------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000000969.00 rows=46750 width=5) + -> Seq Scan on pt_bool_tab_null_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + Filter: (col2 IS NOT TRUE) + Optimizer: Postgres query optimizer +(4 rows) + +SELECT * FROM pt_bool_tab_null WHERE col2 IS NOT true; + col1 | col2 +------+------ + 1 | f + 2 | f +(2 rows) + +EXPLAIN SELECT * FROM pt_bool_tab_null WHERE col2 IS NOT false; + QUERY PLAN +---------------------------------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000000969.00 rows=46750 width=5) + -> Seq Scan on pt_bool_tab_null_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + Filter: (col2 IS NOT FALSE) + Optimizer: Postgres query optimizer +(4 rows) + +SELECT * FROM pt_bool_tab_null WHERE col2 IS NOT false; + col1 | col2 +------+------ + 2 | t + 3 | t + 1 | t +(3 rows) + +EXPLAIN SELECT * FROM pt_bool_tab_null WHERE col2 IS NOT unknown; + QUERY PLAN +---------------------------------------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..30000005240.29 rows=280220 width=5) + -> Append (cost=10000000000.00..30000001504.03 rows=93406 width=5) + -> Seq Scan on pt_bool_tab_null_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=31136 width=5) + Filter: (col2 IS NOT UNKNOWN) + -> Seq Scan on pt_bool_tab_null_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=31136 width=5) + Filter: (col2 IS NOT UNKNOWN) + -> Seq Scan on pt_bool_tab_null_1_prt_part3 (cost=10000000000.00..10000000345.67 rows=31136 width=5) + Filter: (col2 IS NOT UNKNOWN) + Optimizer: Postgres query optimizer +(9 rows) + +SELECT * FROM pt_bool_tab_null WHERE col2 IS NOT unknown; + col1 | col2 +------+------ + 2 | f + 2 | t + 3 | t + 1 | f + 1 | t +(5 rows) + +EXPLAIN SELECT * FROM pt_bool_tab_null WHERE col2 IS NOT NULL; + QUERY PLAN +---------------------------------------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..20000003493.53 rows=186813 width=5) + -> Append (cost=10000000000.00..20000001002.69 rows=62271 width=5) + -> Seq Scan on pt_bool_tab_null_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=31136 width=5) + Filter: (col2 IS NOT NULL) + -> Seq Scan on pt_bool_tab_null_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=31136 width=5) + Filter: (col2 IS NOT NULL) + Optimizer: Postgres query optimizer +(7 rows) + +SELECT * FROM pt_bool_tab_null WHERE col2 IS NOT NULL; + col1 | col2 +------+------ + 2 | f + 2 | t + 3 | t + 1 | f + 1 | t +(5 rows) + RESET ALL; diff --git a/src/test/regress/expected/partition_pruning_optimizer.out b/src/test/regress/expected/partition_pruning_optimizer.out index af85d9c619..a0d6ece014 100644 --- a/src/test/regress/expected/partition_pruning_optimizer.out +++ b/src/test/regress/expected/partition_pruning_optimizer.out @@ -2507,4 +2507,474 @@ select get_selected_parts('explain analyze select * from bar where j is distinct [0, 0] (1 row) +-- Table partitioned by boolean column +CREATE TABLE pt_bool_tab +( + col1 int, + col2 bool +) +distributed by (col1) +partition by list(col2) +( + partition part1 values(true), + partition part2 values(false) +); +INSERT INTO pt_bool_tab SELECT i, true FROM generate_series(1,3)i; +INSERT INTO pt_bool_tab SELECT i, false FROM generate_series(1,2)i; +EXPLAIN SELECT * FROM pt_bool_tab WHERE col2 IS true; + QUERY PLAN +------------------------------------------------------------------------------ + Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5) + -> Dynamic Seq Scan on pt_bool_tab (cost=0.00..431.00 rows=1 width=5) + Number of partitions to scan: 1 (out of 2) + Filter: (col2 IS TRUE) + Optimizer: Pivotal Optimizer (GPORCA) +(5 rows) + +SELECT * FROM pt_bool_tab WHERE col2 IS true; + col1 | col2 +------+------ + 1 | t + 2 | t + 3 | t +(3 rows) + +EXPLAIN SELECT * FROM pt_bool_tab WHERE col2 IS false; + QUERY PLAN +------------------------------------------------------------------------------ + Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5) + -> Dynamic Seq Scan on pt_bool_tab (cost=0.00..431.00 rows=1 width=5) + Number of partitions to scan: 1 (out of 2) + Filter: (col2 IS FALSE) + Optimizer: Pivotal Optimizer (GPORCA) +(5 rows) + +SELECT * FROM pt_bool_tab WHERE col2 IS false; + col1 | col2 +------+------ + 2 | f + 1 | f +(2 rows) + +EXPLAIN SELECT * FROM pt_bool_tab WHERE col2 IS NULL; + QUERY PLAN +------------------------------------------ + Result (cost=0.00..0.00 rows=0 width=5) + One-Time Filter: false + Optimizer: Pivotal Optimizer (GPORCA) +(3 rows) + +SELECT * FROM pt_bool_tab WHERE col2 IS NULL; + col1 | col2 +------+------ +(0 rows) + +EXPLAIN SELECT * FROM pt_bool_tab WHERE col2 IS unknown; + QUERY PLAN +------------------------------------------------------------------------------ + Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5) + -> Dynamic Seq Scan on pt_bool_tab (cost=0.00..431.00 rows=1 width=5) + Number of partitions to scan: 2 (out of 2) + Filter: (col2 IS UNKNOWN) + Optimizer: Pivotal Optimizer (GPORCA) +(5 rows) + +SELECT * FROM pt_bool_tab WHERE col2 IS unknown; + col1 | col2 +------+------ +(0 rows) + +EXPLAIN SELECT * FROM pt_bool_tab WHERE col2 IS NOT true; + QUERY PLAN +------------------------------------------------------------------------------ + Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5) + -> Dynamic Seq Scan on pt_bool_tab (cost=0.00..431.00 rows=1 width=5) + Number of partitions to scan: 1 (out of 2) + Filter: (col2 IS NOT TRUE) + Optimizer: Pivotal Optimizer (GPORCA) +(5 rows) + +SELECT * FROM pt_bool_tab WHERE col2 IS NOT true; + col1 | col2 +------+------ + 2 | f + 1 | f +(2 rows) + +EXPLAIN SELECT * FROM pt_bool_tab WHERE col2 IS NOT false; + QUERY PLAN +------------------------------------------------------------------------------ + Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5) + -> Dynamic Seq Scan on pt_bool_tab (cost=0.00..431.00 rows=1 width=5) + Number of partitions to scan: 1 (out of 2) + Filter: (col2 IS NOT FALSE) + Optimizer: Pivotal Optimizer (GPORCA) +(5 rows) + +SELECT * FROM pt_bool_tab WHERE col2 IS NOT false; + col1 | col2 +------+------ + 2 | t + 3 | t + 1 | t +(3 rows) + +EXPLAIN SELECT * FROM pt_bool_tab WHERE col2 IS NOT unknown; + QUERY PLAN +------------------------------------------------------------------------------ + Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5) + -> Dynamic Seq Scan on pt_bool_tab (cost=0.00..431.00 rows=1 width=5) + Number of partitions to scan: 2 (out of 2) + Filter: (col2 IS NOT UNKNOWN) + Optimizer: Pivotal Optimizer (GPORCA) +(5 rows) + +SELECT * FROM pt_bool_tab WHERE col2 IS NOT unknown; + col1 | col2 +------+------ + 1 | f + 1 | t + 2 | f + 2 | t + 3 | t +(5 rows) + +EXPLAIN SELECT * FROM pt_bool_tab WHERE col2 IS NOT NULL; + QUERY PLAN +------------------------------------------------------------------------------ + Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5) + -> Dynamic Seq Scan on pt_bool_tab (cost=0.00..431.00 rows=1 width=5) + Number of partitions to scan: 2 (out of 2) + Filter: (NOT (col2 IS NULL)) + Optimizer: Pivotal Optimizer (GPORCA) +(5 rows) + +SELECT * FROM pt_bool_tab WHERE col2 IS NOT NULL; + col1 | col2 +------+------ + 1 | f + 1 | t + 2 | f + 2 | t + 3 | t +(5 rows) + +CREATE TABLE pt_bool_tab_df +( + col1 int, + col2 bool +) +distributed by (col1) +partition by list(col2) +( + partition part1 values(true), + partition part2 values(false), + default partition def +); +INSERT INTO pt_bool_tab_df SELECT i, true FROM generate_series(1,3)i; +INSERT INTO pt_bool_tab_df SELECT i, false FROM generate_series(1,2)i; +INSERT INTO pt_bool_tab_df SELECT i, NULL FROM generate_series(1,1)i; +EXPLAIN SELECT * FROM pt_bool_tab_df WHERE col2 IS true; + QUERY PLAN +------------------------------------------------------------------------------ + Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5) + -> Dynamic Seq Scan on pt_bool_tab_df (cost=0.00..431.00 rows=1 width=5) + Number of partitions to scan: 1 (out of 3) + Filter: (col2 IS TRUE) + Optimizer: Pivotal Optimizer (GPORCA) +(5 rows) + +SELECT * FROM pt_bool_tab_df WHERE col2 IS true; + col1 | col2 +------+------ + 1 | t + 2 | t + 3 | t +(3 rows) + +EXPLAIN SELECT * FROM pt_bool_tab_df WHERE col2 IS false; + QUERY PLAN +------------------------------------------------------------------------------ + Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5) + -> Dynamic Seq Scan on pt_bool_tab_df (cost=0.00..431.00 rows=1 width=5) + Number of partitions to scan: 1 (out of 3) + Filter: (col2 IS FALSE) + Optimizer: Pivotal Optimizer (GPORCA) +(5 rows) + +SELECT * FROM pt_bool_tab_df WHERE col2 IS false; + col1 | col2 +------+------ + 2 | f + 1 | f +(2 rows) + +EXPLAIN SELECT * FROM pt_bool_tab_df WHERE col2 IS NULL; + QUERY PLAN +------------------------------------------------------------------------------ + Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5) + -> Dynamic Seq Scan on pt_bool_tab_df (cost=0.00..431.00 rows=1 width=5) + Number of partitions to scan: 1 (out of 3) + Filter: (col2 IS NULL) + Optimizer: Pivotal Optimizer (GPORCA) +(5 rows) + +SELECT * FROM pt_bool_tab_df WHERE col2 IS NULL; + col1 | col2 +------+------ + 1 | +(1 row) + +EXPLAIN SELECT * FROM pt_bool_tab_df WHERE col2 IS unknown; + QUERY PLAN +------------------------------------------------------------------------------ + Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5) + -> Dynamic Seq Scan on pt_bool_tab_df (cost=0.00..431.00 rows=1 width=5) + Number of partitions to scan: 3 (out of 3) + Filter: (col2 IS UNKNOWN) + Optimizer: Pivotal Optimizer (GPORCA) +(5 rows) + +SELECT * FROM pt_bool_tab_df WHERE col2 IS unknown; + col1 | col2 +------+------ + 1 | +(1 row) + +EXPLAIN SELECT * FROM pt_bool_tab_df WHERE col2 IS NOT true; + QUERY PLAN +------------------------------------------------------------------------------ + Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5) + -> Dynamic Seq Scan on pt_bool_tab_df (cost=0.00..431.00 rows=1 width=5) + Number of partitions to scan: 2 (out of 3) + Filter: (col2 IS NOT TRUE) + Optimizer: Pivotal Optimizer (GPORCA) +(5 rows) + +SELECT * FROM pt_bool_tab_df WHERE col2 IS NOT true; + col1 | col2 +------+------ + 2 | f + 1 | f + 1 | +(3 rows) + +EXPLAIN SELECT * FROM pt_bool_tab_df WHERE col2 IS NOT false; + QUERY PLAN +------------------------------------------------------------------------------ + Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5) + -> Dynamic Seq Scan on pt_bool_tab_df (cost=0.00..431.00 rows=1 width=5) + Number of partitions to scan: 2 (out of 3) + Filter: (col2 IS NOT FALSE) + Optimizer: Pivotal Optimizer (GPORCA) +(5 rows) + +SELECT * FROM pt_bool_tab_df WHERE col2 IS NOT false; + col1 | col2 +------+------ + 2 | t + 3 | t + 1 | t + 1 | +(4 rows) + +EXPLAIN SELECT * FROM pt_bool_tab_df WHERE col2 IS NOT unknown; + QUERY PLAN +------------------------------------------------------------------------------ + Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5) + -> Dynamic Seq Scan on pt_bool_tab_df (cost=0.00..431.00 rows=1 width=5) + Number of partitions to scan: 3 (out of 3) + Filter: (col2 IS NOT UNKNOWN) + Optimizer: Pivotal Optimizer (GPORCA) +(5 rows) + +SELECT * FROM pt_bool_tab_df WHERE col2 IS NOT unknown; + col1 | col2 +------+------ + 2 | f + 2 | t + 3 | t + 1 | f + 1 | t +(5 rows) + +EXPLAIN SELECT * FROM pt_bool_tab_df WHERE col2 IS NOT NULL; + QUERY PLAN +------------------------------------------------------------------------------ + Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5) + -> Dynamic Seq Scan on pt_bool_tab_df (cost=0.00..431.00 rows=1 width=5) + Number of partitions to scan: 3 (out of 3) + Filter: (NOT (col2 IS NULL)) + Optimizer: Pivotal Optimizer (GPORCA) +(5 rows) + +SELECT * FROM pt_bool_tab_df WHERE col2 IS NOT NULL; + col1 | col2 +------+------ + 2 | f + 2 | t + 3 | t + 1 | f + 1 | t +(5 rows) + +CREATE TABLE pt_bool_tab_null +( + col1 int, + col2 bool +) +distributed by (col1) +partition by list(col2) +( + partition part1 values(true), + partition part2 values(false), + partition part3 values(null) +); +INSERT INTO pt_bool_tab_null SELECT i, true FROM generate_series(1,3)i; +INSERT INTO pt_bool_tab_null SELECT i, false FROM generate_series(1,2)i; +INSERT INTO pt_bool_tab_null SELECT i, NULL FROM generate_series(1,1)i; +EXPLAIN SELECT * FROM pt_bool_tab_null WHERE col2 IS true; + QUERY PLAN +-------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5) + -> Dynamic Seq Scan on pt_bool_tab_null (cost=0.00..431.00 rows=1 width=5) + Number of partitions to scan: 1 (out of 3) + Filter: (col2 IS TRUE) + Optimizer: Pivotal Optimizer (GPORCA) +(5 rows) + +SELECT * FROM pt_bool_tab_null WHERE col2 IS true; + col1 | col2 +------+------ + 1 | t + 2 | t + 3 | t +(3 rows) + +EXPLAIN SELECT * FROM pt_bool_tab_null WHERE col2 IS false; + QUERY PLAN +-------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5) + -> Dynamic Seq Scan on pt_bool_tab_null (cost=0.00..431.00 rows=1 width=5) + Number of partitions to scan: 1 (out of 3) + Filter: (col2 IS FALSE) + Optimizer: Pivotal Optimizer (GPORCA) +(5 rows) + +SELECT * FROM pt_bool_tab_null WHERE col2 IS false; + col1 | col2 +------+------ + 1 | f + 2 | f +(2 rows) + +EXPLAIN SELECT * FROM pt_bool_tab_null WHERE col2 IS NULL; + QUERY PLAN +-------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5) + -> Dynamic Seq Scan on pt_bool_tab_null (cost=0.00..431.00 rows=1 width=5) + Number of partitions to scan: 1 (out of 3) + Filter: (col2 IS NULL) + Optimizer: Pivotal Optimizer (GPORCA) +(5 rows) + +SELECT * FROM pt_bool_tab_null WHERE col2 IS NULL; + col1 | col2 +------+------ + 1 | +(1 row) + +EXPLAIN SELECT * FROM pt_bool_tab_null WHERE col2 IS unknown; + QUERY PLAN +-------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5) + -> Dynamic Seq Scan on pt_bool_tab_null (cost=0.00..431.00 rows=1 width=5) + Number of partitions to scan: 3 (out of 3) + Filter: (col2 IS UNKNOWN) + Optimizer: Pivotal Optimizer (GPORCA) +(5 rows) + +SELECT * FROM pt_bool_tab_null WHERE col2 IS unknown; + col1 | col2 +------+------ + 1 | +(1 row) + +EXPLAIN SELECT * FROM pt_bool_tab_null WHERE col2 IS NOT true; + QUERY PLAN +-------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5) + -> Dynamic Seq Scan on pt_bool_tab_null (cost=0.00..431.00 rows=1 width=5) + Number of partitions to scan: 2 (out of 3) + Filter: (col2 IS NOT TRUE) + Optimizer: Pivotal Optimizer (GPORCA) +(5 rows) + +SELECT * FROM pt_bool_tab_null WHERE col2 IS NOT true; + col1 | col2 +------+------ + 1 | f + 2 | f + 1 | +(3 rows) + +EXPLAIN SELECT * FROM pt_bool_tab_null WHERE col2 IS NOT false; + QUERY PLAN +-------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5) + -> Dynamic Seq Scan on pt_bool_tab_null (cost=0.00..431.00 rows=1 width=5) + Number of partitions to scan: 2 (out of 3) + Filter: (col2 IS NOT FALSE) + Optimizer: Pivotal Optimizer (GPORCA) +(5 rows) + +SELECT * FROM pt_bool_tab_null WHERE col2 IS NOT false; + col1 | col2 +------+------ + 2 | t + 3 | t + 1 | t + 1 | +(4 rows) + +EXPLAIN SELECT * FROM pt_bool_tab_null WHERE col2 IS NOT unknown; + QUERY PLAN +-------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5) + -> Dynamic Seq Scan on pt_bool_tab_null (cost=0.00..431.00 rows=1 width=5) + Number of partitions to scan: 3 (out of 3) + Filter: (col2 IS NOT UNKNOWN) + Optimizer: Pivotal Optimizer (GPORCA) +(5 rows) + +SELECT * FROM pt_bool_tab_null WHERE col2 IS NOT unknown; + col1 | col2 +------+------ + 2 | f + 2 | t + 3 | t + 1 | f + 1 | t +(5 rows) + +EXPLAIN SELECT * FROM pt_bool_tab_null WHERE col2 IS NOT NULL; + QUERY PLAN +-------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5) + -> Dynamic Seq Scan on pt_bool_tab_null (cost=0.00..431.00 rows=1 width=5) + Number of partitions to scan: 2 (out of 3) + Filter: (NOT (col2 IS NULL)) + Optimizer: Pivotal Optimizer (GPORCA) +(5 rows) + +SELECT * FROM pt_bool_tab_null WHERE col2 IS NOT NULL; + col1 | col2 +------+------ + 2 | f + 2 | t + 3 | t + 1 | f + 1 | t +(5 rows) + RESET ALL; diff --git a/src/test/regress/expected/qp_targeted_dispatch_optimizer.out b/src/test/regress/expected/qp_targeted_dispatch_optimizer.out index abf4565f51..330476105d 100644 --- a/src/test/regress/expected/qp_targeted_dispatch_optimizer.out +++ b/src/test/regress/expected/qp_targeted_dispatch_optimizer.out @@ -183,7 +183,7 @@ INFO: (slice 0) Dispatch command to ALL contents: 0 1 2 INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 select * from boolean where boo='t' and b=2; -INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 +INFO: (slice 1) Dispatch command to SINGLE content boo | b -----+--- t | 2 diff --git a/src/test/regress/sql/partition_pruning.sql b/src/test/regress/sql/partition_pruning.sql index 8a5197c63b..51517b3c55 100644 --- a/src/test/regress/sql/partition_pruning.sql +++ b/src/test/regress/sql/partition_pruning.sql @@ -895,4 +895,106 @@ select get_selected_parts('explain analyze select * from bar where j is distinct -- 8 parts: NULL is shared with others on p1. So, all 8 parts. select get_selected_parts('explain analyze select * from bar where j is distinct from NULL;'); + +-- Table partitioned by boolean column +CREATE TABLE pt_bool_tab +( + col1 int, + col2 bool +) +distributed by (col1) +partition by list(col2) +( + partition part1 values(true), + partition part2 values(false) +); + +INSERT INTO pt_bool_tab SELECT i, true FROM generate_series(1,3)i; +INSERT INTO pt_bool_tab SELECT i, false FROM generate_series(1,2)i; + +EXPLAIN SELECT * FROM pt_bool_tab WHERE col2 IS true; +SELECT * FROM pt_bool_tab WHERE col2 IS true; +EXPLAIN SELECT * FROM pt_bool_tab WHERE col2 IS false; +SELECT * FROM pt_bool_tab WHERE col2 IS false; +EXPLAIN SELECT * FROM pt_bool_tab WHERE col2 IS NULL; +SELECT * FROM pt_bool_tab WHERE col2 IS NULL; +EXPLAIN SELECT * FROM pt_bool_tab WHERE col2 IS unknown; +SELECT * FROM pt_bool_tab WHERE col2 IS unknown; +EXPLAIN SELECT * FROM pt_bool_tab WHERE col2 IS NOT true; +SELECT * FROM pt_bool_tab WHERE col2 IS NOT true; +EXPLAIN SELECT * FROM pt_bool_tab WHERE col2 IS NOT false; +SELECT * FROM pt_bool_tab WHERE col2 IS NOT false; +EXPLAIN SELECT * FROM pt_bool_tab WHERE col2 IS NOT unknown; +SELECT * FROM pt_bool_tab WHERE col2 IS NOT unknown; +EXPLAIN SELECT * FROM pt_bool_tab WHERE col2 IS NOT NULL; +SELECT * FROM pt_bool_tab WHERE col2 IS NOT NULL; + +CREATE TABLE pt_bool_tab_df +( + col1 int, + col2 bool +) +distributed by (col1) +partition by list(col2) +( + partition part1 values(true), + partition part2 values(false), + default partition def +); + +INSERT INTO pt_bool_tab_df SELECT i, true FROM generate_series(1,3)i; +INSERT INTO pt_bool_tab_df SELECT i, false FROM generate_series(1,2)i; +INSERT INTO pt_bool_tab_df SELECT i, NULL FROM generate_series(1,1)i; + +EXPLAIN SELECT * FROM pt_bool_tab_df WHERE col2 IS true; +SELECT * FROM pt_bool_tab_df WHERE col2 IS true; +EXPLAIN SELECT * FROM pt_bool_tab_df WHERE col2 IS false; +SELECT * FROM pt_bool_tab_df WHERE col2 IS false; +EXPLAIN SELECT * FROM pt_bool_tab_df WHERE col2 IS NULL; +SELECT * FROM pt_bool_tab_df WHERE col2 IS NULL; +EXPLAIN SELECT * FROM pt_bool_tab_df WHERE col2 IS unknown; +SELECT * FROM pt_bool_tab_df WHERE col2 IS unknown; +EXPLAIN SELECT * FROM pt_bool_tab_df WHERE col2 IS NOT true; +SELECT * FROM pt_bool_tab_df WHERE col2 IS NOT true; +EXPLAIN SELECT * FROM pt_bool_tab_df WHERE col2 IS NOT false; +SELECT * FROM pt_bool_tab_df WHERE col2 IS NOT false; +EXPLAIN SELECT * FROM pt_bool_tab_df WHERE col2 IS NOT unknown; +SELECT * FROM pt_bool_tab_df WHERE col2 IS NOT unknown; +EXPLAIN SELECT * FROM pt_bool_tab_df WHERE col2 IS NOT NULL; +SELECT * FROM pt_bool_tab_df WHERE col2 IS NOT NULL; + + +CREATE TABLE pt_bool_tab_null +( + col1 int, + col2 bool +) +distributed by (col1) +partition by list(col2) +( + partition part1 values(true), + partition part2 values(false), + partition part3 values(null) +); +INSERT INTO pt_bool_tab_null SELECT i, true FROM generate_series(1,3)i; +INSERT INTO pt_bool_tab_null SELECT i, false FROM generate_series(1,2)i; +INSERT INTO pt_bool_tab_null SELECT i, NULL FROM generate_series(1,1)i; + +EXPLAIN SELECT * FROM pt_bool_tab_null WHERE col2 IS true; +SELECT * FROM pt_bool_tab_null WHERE col2 IS true; +EXPLAIN SELECT * FROM pt_bool_tab_null WHERE col2 IS false; +SELECT * FROM pt_bool_tab_null WHERE col2 IS false; +EXPLAIN SELECT * FROM pt_bool_tab_null WHERE col2 IS NULL; +SELECT * FROM pt_bool_tab_null WHERE col2 IS NULL; +EXPLAIN SELECT * FROM pt_bool_tab_null WHERE col2 IS unknown; +SELECT * FROM pt_bool_tab_null WHERE col2 IS unknown; +EXPLAIN SELECT * FROM pt_bool_tab_null WHERE col2 IS NOT true; +SELECT * FROM pt_bool_tab_null WHERE col2 IS NOT true; +EXPLAIN SELECT * FROM pt_bool_tab_null WHERE col2 IS NOT false; +SELECT * FROM pt_bool_tab_null WHERE col2 IS NOT false; +EXPLAIN SELECT * FROM pt_bool_tab_null WHERE col2 IS NOT unknown; +SELECT * FROM pt_bool_tab_null WHERE col2 IS NOT unknown; +EXPLAIN SELECT * FROM pt_bool_tab_null WHERE col2 IS NOT NULL; +SELECT * FROM pt_bool_tab_null WHERE col2 IS NOT NULL; + RESET ALL; --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
