Fix planner's nullability/strictness logic for ScalarArrayOpExpr. find_nonnullable_rels and find_nonnullable_vars mistakenly treated a ScalarArrayOpExpr that could return FALSE as strict, but that's okay only at top level of a qual expression; further down, we've got to insist on a guaranteed-NULL result. The result was that we could draw mistaken conclusions about whether outer joins can be simplified, if the decision hinged on a non-top-level ScalarArrayOpExpr with a potentially-empty array argument.
I believe this error dates to commit 72a070a36, which taught find_nonnullable_rels to descend into non-top-level parts of qual expressions. is_strict_saop (added earlier by 72153c058) already had enough intelligence to do the case correctly, but it wasn't passed the proper flag, ie "top_level" needs to be passed for "falseOK". e006a24ad copied that mistake into find_nonnullable_vars. Later, over-eager refactoring in commit 2f153ddfd broke contain_nonstrict_functions' handling of ScalarArrayOpExpr by treating it as though it were no different from an OpExpr. It is, because we must also prove the array is non-empty before concluding that the expression is strict. This could result in misclassifying an expression as strict when it is not, leading to assorted planning mistakes such as inlining a SQL function that shouldn't be inlined. We can almost fix this by just re-adding the previous handling of ScalarArrayOpExpr in that function, but doing only that would lead to also calling check_functions_in_node() and thus redundantly checking the operator's strictness. Avoid that by turning the if-series into an else-if chain, as it arguably should have been all along. The reason these errors have escaped detection for decades is that they are exposed only in arcane corner cases. ScalarArrayOpExpr with an empty array isn't typical usage, and even when that's possible several other conditions apply before the planner can reach a mistaken conclusion. While it's possible to build test cases demonstrating these mistakes, I (tgl) judged them too indirect and special-purpose to justify consuming regression test cycles forevermore. Author: Ayush Tiwari <[email protected]> Reviewed-by: Tom Lane <[email protected]> Discussion: https://postgr.es/m/cajtyswv3vqrjmst-gv1nsxeef-zonjvjpys910abaiumij4...@mail.gmail.com Discussion: https://postgr.es/m/cajtyswwclgmz0f8_qpp_liq-fc7-geifscdqoq3xgerhpps...@mail.gmail.com Backpatch-through: 14 Branch ------ REL_18_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/277122036c3382c5ab47034a180fde1176728c43 Modified Files -------------- src/backend/optimizer/util/clauses.c | 74 ++++++++++++++++++++---------------- 1 file changed, 41 insertions(+), 33 deletions(-)
