On Wed, 12 Apr 2023 at 22:13, David Kimura <david.g.kim...@gmail.com> wrote: > Is it fair to assume that, given the same data, a partitioned table should > return the same results as a non-partitioned table?
Yes, and also the same as when enable_partition_pruning is set to off.
> CREATE TABLE boolpart (a bool) PARTITION BY LIST (a);
> CREATE TABLE boolpart_default PARTITION OF boolpart default;
> CREATE TABLE boolpart_t PARTITION OF boolpart FOR VALUES IN ('true');
> CREATE TABLE boolpart_f PARTITION OF boolpart FOR VALUES IN ('false');
> INSERT INTO boolpart VALUES (true), (false), (null);
>
> EXPLAIN SELECT * FROM boolpart WHERE a IS NOT true;
> QUERY PLAN
> -----------------------------------------------------------------------
> Seq Scan on boolpart_f boolpart (cost=0.00..38.10 rows=1405 width=1)
> Filter: (a IS NOT TRUE)
> (2 rows)
>
> SELECT * FROM boolpart WHERE a IS NOT true;
> a
> ---
> f
> (1 row)
>
> Compare that to the result of a non-partitioned table:
>
> CREATE TABLE booltab (a bool);
> INSERT INTO booltab VALUES (true), (false), (null);
>
> EXPLAIN SELECT * FROM booltab WHERE a IS NOT true;
> QUERY PLAN
> -----------------------------------------------------------
> Seq Scan on booltab (cost=0.00..38.10 rows=1405 width=1)
> Filter: (a IS NOT TRUE)
> (2 rows)
>
> SELECT * FROM booltab WHERE a IS NOT true;
> a
> ---
> f
Ouch. That's certainly not correct.
> I think the issue has to do with assumptions made about boolean test IS NOT
> inequality logic which is different from inequality of other operators.
> Specifically, "true IS NOT NULL" is not the same as "true<>NULL".
Yeah, that's wrong.
> One idea is to use the negation operator for IS_NOT_(true|false) (i.e.
> BooleanNotEqualOperator instead of BooleanEqualOperator). But besides
> presumably being a more expensive operation, not equal is not part of the
> btree
> opfamily for bool_ops. So, seems like that won't really fit into the current
> partition pruning framework.
There's already code to effectively handle <> operators. Just the
PartClauseInfo.op_is_ne needs to be set to true.
get_matching_list_bounds() then handles that by taking the inverse of
the partitions matching the equality operator.
Effectively, I think that's the attached patch.
There seems to be a bunch of tests checking this already, all of them
assuming the incorrect plans.
David
fix_partprune_handling_of_NOT_TRUE_and_NOT_FALSE_boolean_quals.patch
Description: Binary data
