Re: Unexpected (wrong?) result querying boolean partitioned table with NULL partition

On Wed, 12 Apr 2023 at 22:13, David Kimura <david.g.kimura@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