Preserve typmod and collation when reducing an expression to its input eval_const_expressions() can reduce a COALESCE, CASE, or NULLIF expression to one of its inputs. The input, however, does not necessarily expose the same typmod and collation as the expression it replaces. In particular, an explicit COLLATE clause on one input determines the collation of the whole expression, while the other inputs keep their own collations. If the expression was reduced to one of those other inputs, anything that relies on exprCollation(), such as sorting or grouping on the expression, would use the wrong collation and could produce wrong results.
For CASE and NULLIF this is a long-standing oversight. For COALESCE it has become much easier to hit since commits 931766aae and 10c4fe074, which reduce a COALESCE with a single surviving argument, or with a leading non-nullable argument, to that argument. To fix, apply applyRelabelType() to the replacement in these cases, so that it exposes the type, typmod, and collation of the expression it replaces. Back-patch to v19 only. The CASE and NULLIF cases exist in older branches too, but they are not back-patched further, given the lack of field reports and the fact that the fix visibly changes the typmod exposed by a reduced CASE expression. Reported-by: Shihao Zhong <[email protected]> Author: Shihao Zhong <[email protected]> Author: Richard Guo <[email protected]> Discussion: https://postgr.es/m/CAGRkXqR7VXacmRP41UBaM5u4aseeCH5ECVmQgn_YS=L7=xd...@mail.gmail.com Backpatch-through: 19 Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/d8ce0050a6cff3efefa5fce8abe3c6df7bd492ce Modified Files -------------- src/backend/optimizer/util/clauses.c | 48 ++++++++++++++++++-------- src/test/regress/expected/collate.icu.utf8.out | 25 ++++++++++++++ src/test/regress/sql/collate.icu.utf8.sql | 13 +++++++ 3 files changed, 71 insertions(+), 15 deletions(-)
