Hi Denis, Rustam
Thank you both for the careful testing. I reproduced all five cases on
v5, and the attached v6 fixes them.
All five have the same cause. v5 applied the NULL/FALSE folding not only
to quals but also, when root != NULL, to CASE WHEN conditions and to the
argument of IS [NOT] TRUE. That doesn't change the value of the
enclosing expression, but it does change its structure. The planner then
compares that structure against expressions from the catalogs that were
simplified without the folding:
- ON CONFLICT: the CASE collapses to the Var "a", which is matched
against the index's plain columns, and the index has none.
- pruning and partitionwise aggregate: rel->partexprs is built with root
== NULL.
- extended statistics: the stats expression is simplified with root and
becomes the bare column "a", so it gets matched by attnum.
- constraint exclusion: partition_qual comes from expression_planner()
without root.
Rustam, you're right that a fix in partprune.c alone wouldn't be enough.
I also don't think fixing it where rel->partexprs and partition_qual are
built is the right direction, because the problem isn't limited to
partitioning. Index expressions, statistics expressions and ON CONFLICT
inference all depend on the same thing: a scalar expression has to look
the same however it was simplified. We would have to apply the folding
consistently to every catalog expression. We can't do that, because a
partition key is checked for being constant when it is defined, and that
is exactly the dump/upgrade breakage from the previous round.
So v6 restricts the folding to positions where the result is the truth
value of a qual:
- WHERE/JOIN/HAVING quals and the other places that use
eval_const_expressions_qual(), as before, looking through AND and OR only.
- "expr IS TRUE" and "expr IS NOT TRUE" in those positions. They are
replaced by constant FALSE and TRUE only when expr reduces entirely to
FALSE. Their argument is never partially rewritten.
- aggregate and window function FILTER clauses, as before. An Aggref or
WindowFunc cannot appear in an index expression, a partition key or a
statistics expression, so these can't be matched against catalog
expressions.
CASE WHEN conditions and IS [NOT] TRUE outside a qual are no longer
folded. Folding CASE WHEN was requested earlier in the thread. I don't
see a way to do it without these regressions, so I've dropped it.
v6 adds regression tests for all five of your cases (predicate.sql,
insert_conflict.sql, stats_ext.sql). Each of them fails on v5, and with
v6 the plans and estimates match master
