Making CASE error handling less surprising

Every so often we get a complaint like [1] about how a CASE should have
prevented a run-time error and didn't, because constant-folding tried
to evaluate a subexpression that would not have been entered at run-time.

It struck me that it would not be hard to improve this situation a great
deal.  If, within a CASE subexpression that isn't certain to be executed
at runtime, we refuse to pre-evaluate *any* function (essentially, treat
them all as volatile), then we should largely get the semantics that
users expect.  There's some potential for query slowdown if a CASE
contains a constant subexpression that we formerly reduced at plan time
and now do not, but that doesn't seem to me to be a very big deal.

Attached is a draft patch that handles CASE and COALESCE this way.

This is not a complete fix, because if you write a sub-SELECT the
contents of the sub-SELECT are not processed by the outer query's
eval_const_expressions pass; instead, we look at it within the
sub-SELECT itself, and in that context there's no apparent reason
to avoid const-folding.  So
   CASE WHEN x < 0 THEN (SELECT 1/0) END
fails even if x is never less than zero.  I don't see any great way
to avoid that, and I'm not particularly concerned about it anyhow;
usually the point of a sub-SELECT like this is to be decoupled from
outer query evaluation, so that the behavior should not be that
surprising.

One interesting point is that the join regression test contains a
number of uses of "coalesce(int8-variable, int4-constant)" which is
treated a little differently than before: we no longer constant-fold
the int4 constant to int8.  That causes the run-time cost of the
expression to be estimated slightly higher, which changes plans in
a couple of these tests; and in any case the EXPLAIN output looks
different since it shows the runtime coercion explicitly.  To avoid
those changes I made all these examples quote the constants, so that
the parser resolves them as int8 out of the gate.  (Perhaps it'd be
okay to just accept the changes, but I didn't feel like trying to
analyze in detail what each test case had been meant to prove.)

Also, I didn't touch the docs yet.  Sections 4.2.14 and 9.18.1
contain some weasel wording that could be backed off, but in light
of the sub-SELECT exception we can't just remove the issue
altogether I think.  Not quite sure how to word it.

Thoughts?

            regards, tom lane

[1] https://www.postgresql.org/message-id/16549-4991fbf36fcec234%40postgresql.org

diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index e04b144072..8a41dce235 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -61,6 +61,15 @@ typedef struct
     AggClauseCosts *costs;
 } get_agg_clause_costs_context;

+typedef enum
+{
+    /* Ordering is important here! */
+    ece_eval_nothing,            /* be unconditionally safe */
+    ece_eval_immutable,            /* eval immutable functions */
+    ece_eval_stable,            /* eval stable functions too */
+    ece_eval_volatile            /* eval volatile functions too */
+} ece_level;
+
 typedef struct
 {
     ParamListInfo boundParams;
@@ -68,6 +77,7 @@ typedef struct
     List       *active_fns;
     Node       *case_val;
     bool        estimate;
+    ece_level    eval_level;
 } eval_const_expressions_context;

 typedef struct
@@ -119,6 +129,8 @@ static Node *eval_const_expressions_mutator(Node *node,
 static bool contain_non_const_walker(Node *node, void *context);
 static bool ece_function_is_safe(Oid funcid,
                                  eval_const_expressions_context *context);
+static bool ece_provolatile_is_safe(char provolatile,
+                                    eval_const_expressions_context *context);
 static Node *apply_const_relabel(Node *arg, Oid rtype,
                                  int32 rtypmod, Oid rcollid,
                                  CoercionForm rformat, int rlocation);
@@ -2264,6 +2276,7 @@ eval_const_expressions(PlannerInfo *root, Node *node)
     context.active_fns = NIL;    /* nothing being recursively simplified */
     context.case_val = NULL;    /* no CASE being examined */
     context.estimate = false;    /* safe transformations only */
+    context.eval_level = ece_eval_immutable;    /* eval immutable functions */
     return eval_const_expressions_mutator(node, &context);
 }

@@ -2280,8 +2293,11 @@ eval_const_expressions(PlannerInfo *root, Node *node)
  *      available by the caller of planner(), even if the Param isn't marked
  *      constant.  This effectively means that we plan using the first supplied
  *      value of the Param.
- * 2. Fold stable, as well as immutable, functions to constants.
+ * 2. Fold stable, as well as immutable, functions to constants.  The risk
+ *      that the result might change from planning time to execution time is
+ *      worth taking, as we otherwise couldn't get an estimate at all.
  * 3. Reduce PlaceHolderVar nodes to their contained expressions.
+ * 4. Ignore domain constraints, assuming that CoerceToDomain will succeed.
  *--------------------
  */
 Node *
@@ -2295,6 +2311,7 @@ estimate_expression_value(PlannerInfo *root, Node *node)
     context.active_fns = NIL;    /* nothing being recursively simplified */
     context.case_val = NULL;    /* no CASE being examined */
     context.estimate = true;    /* unsafe transformations OK */
+    context.eval_level = ece_eval_stable;    /* eval stable functions */
     return eval_const_expressions_mutator(node, &context);
 }

@@ -2960,8 +2977,9 @@ eval_const_expressions_mutator(Node *node,
                  * CaseTestExpr placeholder nodes, so that we have the
                  * opportunity to reduce constant test conditions.  For
                  * example this allows
-                 *        CASE 0 WHEN 0 THEN 1 ELSE 1/0 END
-                 * to reduce to 1 rather than drawing a divide-by-0 error.
+                 *        CASE 0 WHEN 0 THEN 1 ELSE 0 END
+                 * to reduce to just 1.
+                 *
                  * Note that when the test expression is constant, we don't
                  * have to include it in the resulting CASE; for example
                  *        CASE 0 WHEN x THEN y ELSE z END
@@ -2973,10 +2991,20 @@ eval_const_expressions_mutator(Node *node,
                  * expression when executing the CASE, since any contained
                  * CaseTestExprs that might have referred to it will have been
                  * replaced by the constant.
+                 *
+                 * An additional consideration is that the user might be
+                 * expecting the CASE to prevent run-time errors, such as
+                 *        CASE 0 WHEN 0 THEN 1 ELSE 1/0 END
+                 * Since division is immutable, we'd ordinarily simplify the
+                 * division and hence draw the divide-by-zero error at plan
+                 * time.  To avoid that, reduce eval_level to ece_eval_nothing
+                 * whenever we are considering a test condition or result
+                 * value that will not certainly be evaluated at run-time.
                  *----------
                  */
                 CaseExpr   *caseexpr = (CaseExpr *) node;
                 CaseExpr   *newcase;
+                ece_level    save_eval_level = context->eval_level;
                 Node       *save_case_val;
                 Node       *newarg;
                 List       *newargs;
@@ -3027,6 +3055,15 @@ eval_const_expressions_mutator(Node *node,
                         const_true_cond = true;
                     }

+                    /*
+                     * Unless the test condition is constant TRUE, we can't be
+                     * sure the result value will be evaluated, so back off
+                     * the evaluation safety level.  This change will also
+                     * apply to subsequent test conditions and result values.
+                     */
+                    if (!const_true_cond)
+                        context->eval_level = ece_eval_nothing;
+
                     /* Simplify this alternative's result value */
                     caseresult = eval_const_expressions_mutator((Node *) oldcasewhen->result,
                                                                 context);
@@ -3058,6 +3095,7 @@ eval_const_expressions_mutator(Node *node,
                                                                context);

                 context->case_val = save_case_val;
+                context->eval_level = save_eval_level;

                 /*
                  * If no non-FALSE alternatives, CASE reduces to the default
@@ -3113,6 +3151,7 @@ eval_const_expressions_mutator(Node *node,
             {
                 CoalesceExpr *coalesceexpr = (CoalesceExpr *) node;
                 CoalesceExpr *newcoalesce;
+                ece_level    save_eval_level = context->eval_level;
                 List       *newargs;
                 ListCell   *arg;

@@ -3137,13 +3176,25 @@ eval_const_expressions_mutator(Node *node,
                         if (((Const *) e)->constisnull)
                             continue;    /* drop null constant */
                         if (newargs == NIL)
+                        {
+                            context->eval_level = save_eval_level;
                             return e;    /* first expr */
+                        }
                         newargs = lappend(newargs, e);
                         break;
                     }
                     newargs = lappend(newargs, e);
+
+                    /*
+                     * Arguments following a non-constant argument may or may
+                     * not get evaluated at run-time, so don't risk doing any
+                     * not-100%-safe computations within them.
+                     */
+                    context->eval_level = ece_eval_nothing;
                 }

+                context->eval_level = save_eval_level;
+
                 /*
                  * If all the arguments were constant null, the result is just
                  * null
@@ -3163,13 +3214,12 @@ eval_const_expressions_mutator(Node *node,
         case T_SQLValueFunction:
             {
                 /*
-                 * All variants of SQLValueFunction are stable, so if we are
-                 * estimating the expression's value, we should evaluate the
-                 * current function value.  Otherwise just copy.
+                 * All variants of SQLValueFunction are stable, so evaluate if
+                 * we are evaluating stable functions.  Otherwise just copy.
                  */
                 SQLValueFunction *svf = (SQLValueFunction *) node;

-                if (context->estimate)
+                if (context->eval_level >= ece_eval_stable)
                     return (Node *) evaluate_expr((Expr *) svf,
                                                   svf->type,
                                                   svf->typmod,
@@ -3565,20 +3615,28 @@ contain_non_const_walker(Node *node, void *context)
 static bool
 ece_function_is_safe(Oid funcid, eval_const_expressions_context *context)
 {
-    char        provolatile = func_volatile(funcid);
+    return ece_provolatile_is_safe(func_volatile(funcid), context);
+}

-    /*
-     * Ordinarily we are only allowed to simplify immutable functions. But for
-     * purposes of estimation, we consider it okay to simplify functions that
-     * are merely stable; the risk that the result might change from planning
-     * time to execution time is worth taking in preference to not being able
-     * to estimate the value at all.
-     */
+/*
+ * Same, when we have the provolatile value directly at hand
+ */
+static bool
+ece_provolatile_is_safe(char provolatile,
+                        eval_const_expressions_context *context)
+{
+    ece_level    f_level;
+
+    /* Must map the provolatile letter codes to an ordered enum */
     if (provolatile == PROVOLATILE_IMMUTABLE)
-        return true;
-    if (context->estimate && provolatile == PROVOLATILE_STABLE)
-        return true;
-    return false;
+        f_level = ece_eval_immutable;
+    else if (provolatile == PROVOLATILE_STABLE)
+        f_level = ece_eval_stable;
+    else
+        f_level = ece_eval_volatile;
+
+    /* Now, does eval_level allow evaluation of this function? */
+    return (context->eval_level >= f_level);
 }

 /*
@@ -4238,9 +4296,8 @@ recheck_cast_function_args(List *args, Oid result_type, HeapTuple func_tuple)
  * evaluate_function: try to pre-evaluate a function call
  *
  * We can do this if the function is strict and has any constant-null inputs
- * (just return a null constant), or if the function is immutable and has all
- * constant inputs (call it and return the result as a Const node).  In
- * estimation mode we are willing to pre-evaluate stable functions too.
+ * (just return a null constant), or if the function is safe to evaluate and
+ * has all constant inputs (call it and return the result as a Const node).
  *
  * Returns a simplified expression if successful, or NULL if cannot
  * simplify the function.
@@ -4293,7 +4350,7 @@ evaluate_function(Oid funcid, Oid result_type, int32 result_typmod,
      * If the function is strict and has a constant-NULL input, it will never
      * be called at all, so we can replace the call by a NULL constant, even
      * if there are other inputs that aren't constant, and even if the
-     * function is not otherwise immutable.
+     * function is not otherwise safe to evaluate.
      */
     if (funcform->proisstrict && has_null_input)
         return (Expr *) makeNullConst(result_type, result_typmod,
@@ -4308,17 +4365,9 @@ evaluate_function(Oid funcid, Oid result_type, int32 result_typmod,
         return NULL;

     /*
-     * Ordinarily we are only allowed to simplify immutable functions. But for
-     * purposes of estimation, we consider it okay to simplify functions that
-     * are merely stable; the risk that the result might change from planning
-     * time to execution time is worth taking in preference to not being able
-     * to estimate the value at all.
+     * Are we permitted to evaluate functions of this volatility level?
      */
-    if (funcform->provolatile == PROVOLATILE_IMMUTABLE)
-         /* okay */ ;
-    else if (context->estimate && funcform->provolatile == PROVOLATILE_STABLE)
-         /* okay */ ;
-    else
+    if (!ece_provolatile_is_safe(funcform->provolatile, context))
         return NULL;

     /*
diff --git a/src/test/regress/expected/case.out b/src/test/regress/expected/case.out
index c0c8acf035..3326ebd5be 100644
--- a/src/test/regress/expected/case.out
+++ b/src/test/regress/expected/case.out
@@ -93,9 +93,17 @@ SELECT CASE 1 WHEN 0 THEN 1/0 WHEN 1 THEN 1 ELSE 2/0 END;
     1
 (1 row)

--- However we do not currently suppress folding of potentially
--- reachable subexpressions
 SELECT CASE WHEN i > 100 THEN 1/0 ELSE 0 END FROM case_tbl;
+ case
+------
+    0
+    0
+    0
+    0
+(4 rows)
+
+-- However, that guarantee doesn't extend into sub-selects
+SELECT CASE WHEN i > 100 THEN (select 1/0) ELSE 0 END FROM case_tbl;
 ERROR:  division by zero
 -- Test for cases involving untyped literals in test expression
 SELECT CASE 'a' WHEN 'a' THEN 1 ELSE 2 END;
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index a46b1573bd..6a411008dd 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -2860,9 +2860,9 @@ ON sub1.key1 = sub2.key3;
 EXPLAIN (COSTS OFF)
 SELECT qq, unique1
   FROM
-  ( SELECT COALESCE(q1, 0) AS qq FROM int8_tbl a ) AS ss1
+  ( SELECT COALESCE(q1, '0') AS qq FROM int8_tbl a ) AS ss1
   FULL OUTER JOIN
-  ( SELECT COALESCE(q2, -1) AS qq FROM int8_tbl b ) AS ss2
+  ( SELECT COALESCE(q2, '-1') AS qq FROM int8_tbl b ) AS ss2
   USING (qq)
   INNER JOIN tenk1 c ON qq = unique2;
                                                QUERY PLAN
@@ -2879,9 +2879,9 @@ SELECT qq, unique1

 SELECT qq, unique1
   FROM
-  ( SELECT COALESCE(q1, 0) AS qq FROM int8_tbl a ) AS ss1
+  ( SELECT COALESCE(q1, '0') AS qq FROM int8_tbl a ) AS ss1
   FULL OUTER JOIN
-  ( SELECT COALESCE(q2, -1) AS qq FROM int8_tbl b ) AS ss2
+  ( SELECT COALESCE(q2, '-1') AS qq FROM int8_tbl b ) AS ss2
   USING (qq)
   INNER JOIN tenk1 c ON qq = unique2;
  qq  | unique1
@@ -4299,14 +4299,14 @@ explain (costs off)
 --
 -- test that quals attached to an outer join have correct semantics,
 -- specifically that they don't re-use expressions computed below the join;
--- we force a mergejoin so that coalesce(b.q1, 1) appears as a join input
+-- we force a mergejoin so that coalesce(b.q1, '1') appears as a join input
 --
 set enable_hashjoin to off;
 set enable_nestloop to off;
 explain (verbose, costs off)
   select a.q2, b.q1
-    from int8_tbl a left join int8_tbl b on a.q2 = coalesce(b.q1, 1)
-    where coalesce(b.q1, 1) > 0;
+    from int8_tbl a left join int8_tbl b on a.q2 = coalesce(b.q1, '1')
+    where coalesce(b.q1, '1') > 0;
                        QUERY PLAN
 ---------------------------------------------------------
  Merge Left Join
@@ -4326,8 +4326,8 @@ explain (verbose, costs off)
 (14 rows)

 select a.q2, b.q1
-  from int8_tbl a left join int8_tbl b on a.q2 = coalesce(b.q1, 1)
-  where coalesce(b.q1, 1) > 0;
+  from int8_tbl a left join int8_tbl b on a.q2 = coalesce(b.q1, '1')
+  where coalesce(b.q1, '1') > 0;
         q2         |        q1
 -------------------+------------------
  -4567890123456789 |
@@ -5077,7 +5077,7 @@ select * from (values(1)) x(lb),
 (5 rows)

 select * from
-  int8_tbl x left join (select q1,coalesce(q2,0) q2 from int8_tbl) y on x.q2 = y.q1,
+  int8_tbl x left join (select q1,coalesce(q2,'0') q2 from int8_tbl) y on x.q2 = y.q1,
   lateral (values(x.q1,y.q1,y.q2)) v(xq1,yq1,yq2);
         q1        |        q2         |        q1        |        q2         |       xq1        |       yq1        |
    yq2         

------------------+-------------------+------------------+-------------------+------------------+------------------+-------------------
@@ -5094,7 +5094,7 @@ select * from
 (10 rows)

 select * from
-  int8_tbl x left join (select q1,coalesce(q2,0) q2 from int8_tbl) y on x.q2 = y.q1,
+  int8_tbl x left join (select q1,coalesce(q2,'0') q2 from int8_tbl) y on x.q2 = y.q1,
   lateral (select x.q1,y.q1,y.q2) v(xq1,yq1,yq2);
         q1        |        q2         |        q1        |        q2         |       xq1        |       yq1        |
    yq2         

------------------+-------------------+------------------+-------------------+------------------+------------------+-------------------
@@ -5111,7 +5111,7 @@ select * from
 (10 rows)

 select x.* from
-  int8_tbl x left join (select q1,coalesce(q2,0) q2 from int8_tbl) y on x.q2 = y.q1,
+  int8_tbl x left join (select q1,coalesce(q2,'0') q2 from int8_tbl) y on x.q2 = y.q1,
   lateral (select x.q1,y.q1,y.q2) v(xq1,yq1,yq2);
         q1        |        q2
 ------------------+-------------------
@@ -5128,7 +5128,7 @@ select x.* from
 (10 rows)

 select v.* from
-  (int8_tbl x left join (select q1,coalesce(q2,0) q2 from int8_tbl) y on x.q2 = y.q1)
+  (int8_tbl x left join (select q1,coalesce(q2,'0') q2 from int8_tbl) y on x.q2 = y.q1)
   left join int4_tbl z on z.f1 = x.q2,
   lateral (select x.q1,y.q1 union all select x.q2,y.q2) v(vx,vy);
         vx         |        vy
@@ -5156,7 +5156,7 @@ select v.* from
 (20 rows)

 select v.* from
-  (int8_tbl x left join (select q1,(select coalesce(q2,0)) q2 from int8_tbl) y on x.q2 = y.q1)
+  (int8_tbl x left join (select q1,(select coalesce(q2,'0')) q2 from int8_tbl) y on x.q2 = y.q1)
   left join int4_tbl z on z.f1 = x.q2,
   lateral (select x.q1,y.q1 union all select x.q2,y.q2) v(vx,vy);
         vx         |        vy
@@ -5184,7 +5184,7 @@ select v.* from
 (20 rows)

 select v.* from
-  (int8_tbl x left join (select q1,(select coalesce(q2,0)) q2 from int8_tbl) y on x.q2 = y.q1)
+  (int8_tbl x left join (select q1,(select coalesce(q2,'0')) q2 from int8_tbl) y on x.q2 = y.q1)
   left join int4_tbl z on z.f1 = x.q2,
   lateral (select x.q1,y.q1 from onerow union all select x.q2,y.q2 from onerow) v(vx,vy);
         vx         |        vy
@@ -5246,7 +5246,7 @@ select * from
 explain (verbose, costs off)
 select * from
   int8_tbl a left join
-  lateral (select *, coalesce(a.q2, 42) as x from int8_tbl b) ss on a.q2 = ss.q1;
+  lateral (select *, coalesce(a.q2, '42') as x from int8_tbl b) ss on a.q2 = ss.q1;
                             QUERY PLAN
 ------------------------------------------------------------------
  Nested Loop Left Join
@@ -5260,7 +5260,7 @@ select * from

 select * from
   int8_tbl a left join
-  lateral (select *, coalesce(a.q2, 42) as x from int8_tbl b) ss on a.q2 = ss.q1;
+  lateral (select *, coalesce(a.q2, '42') as x from int8_tbl b) ss on a.q2 = ss.q1;
         q1        |        q2         |        q1        |        q2         |        x
 ------------------+-------------------+------------------+-------------------+------------------
               123 |               456 |                  |                   |
@@ -5462,7 +5462,7 @@ select * from
 explain (verbose, costs off)
 select * from
   int8_tbl c left join (
-    int8_tbl a left join (select q1, coalesce(q2,42) as x from int8_tbl b) ss1
+    int8_tbl a left join (select q1, coalesce(q2,'42') as x from int8_tbl b) ss1
       on a.q2 = ss1.q1
     cross join
     lateral (select q1, coalesce(ss1.x,q2) as y from int8_tbl d) ss2
diff --git a/src/test/regress/sql/case.sql b/src/test/regress/sql/case.sql
index 17436c524a..cfad0b815c 100644
--- a/src/test/regress/sql/case.sql
+++ b/src/test/regress/sql/case.sql
@@ -66,11 +66,11 @@ SELECT '7' AS "None",
 -- Constant-expression folding shouldn't evaluate unreachable subexpressions
 SELECT CASE WHEN 1=0 THEN 1/0 WHEN 1=1 THEN 1 ELSE 2/0 END;
 SELECT CASE 1 WHEN 0 THEN 1/0 WHEN 1 THEN 1 ELSE 2/0 END;
-
--- However we do not currently suppress folding of potentially
--- reachable subexpressions
 SELECT CASE WHEN i > 100 THEN 1/0 ELSE 0 END FROM case_tbl;

+-- However, that guarantee doesn't extend into sub-selects
+SELECT CASE WHEN i > 100 THEN (select 1/0) ELSE 0 END FROM case_tbl;
+
 -- Test for cases involving untyped literals in test expression
 SELECT CASE 'a' WHEN 'a' THEN 1 ELSE 2 END;

diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 1403e0ffe7..5c868a3dae 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -811,17 +811,17 @@ ON sub1.key1 = sub2.key3;
 EXPLAIN (COSTS OFF)
 SELECT qq, unique1
   FROM
-  ( SELECT COALESCE(q1, 0) AS qq FROM int8_tbl a ) AS ss1
+  ( SELECT COALESCE(q1, '0') AS qq FROM int8_tbl a ) AS ss1
   FULL OUTER JOIN
-  ( SELECT COALESCE(q2, -1) AS qq FROM int8_tbl b ) AS ss2
+  ( SELECT COALESCE(q2, '-1') AS qq FROM int8_tbl b ) AS ss2
   USING (qq)
   INNER JOIN tenk1 c ON qq = unique2;

 SELECT qq, unique1
   FROM
-  ( SELECT COALESCE(q1, 0) AS qq FROM int8_tbl a ) AS ss1
+  ( SELECT COALESCE(q1, '0') AS qq FROM int8_tbl a ) AS ss1
   FULL OUTER JOIN
-  ( SELECT COALESCE(q2, -1) AS qq FROM int8_tbl b ) AS ss2
+  ( SELECT COALESCE(q2, '-1') AS qq FROM int8_tbl b ) AS ss2
   USING (qq)
   INNER JOIN tenk1 c ON qq = unique2;

@@ -1455,7 +1455,7 @@ explain (costs off)
 --
 -- test that quals attached to an outer join have correct semantics,
 -- specifically that they don't re-use expressions computed below the join;
--- we force a mergejoin so that coalesce(b.q1, 1) appears as a join input
+-- we force a mergejoin so that coalesce(b.q1, '1') appears as a join input
 --

 set enable_hashjoin to off;
@@ -1463,11 +1463,11 @@ set enable_nestloop to off;

 explain (verbose, costs off)
   select a.q2, b.q1
-    from int8_tbl a left join int8_tbl b on a.q2 = coalesce(b.q1, 1)
-    where coalesce(b.q1, 1) > 0;
+    from int8_tbl a left join int8_tbl b on a.q2 = coalesce(b.q1, '1')
+    where coalesce(b.q1, '1') > 0;
 select a.q2, b.q1
-  from int8_tbl a left join int8_tbl b on a.q2 = coalesce(b.q1, 1)
-  where coalesce(b.q1, 1) > 0;
+  from int8_tbl a left join int8_tbl b on a.q2 = coalesce(b.q1, '1')
+  where coalesce(b.q1, '1') > 0;

 reset enable_hashjoin;
 reset enable_nestloop;
@@ -1766,24 +1766,24 @@ select * from (values(1)) x(lb),
 select * from (values(1)) x(lb),
   lateral (select lb from int4_tbl) y(lbcopy);
 select * from
-  int8_tbl x left join (select q1,coalesce(q2,0) q2 from int8_tbl) y on x.q2 = y.q1,
+  int8_tbl x left join (select q1,coalesce(q2,'0') q2 from int8_tbl) y on x.q2 = y.q1,
   lateral (values(x.q1,y.q1,y.q2)) v(xq1,yq1,yq2);
 select * from
-  int8_tbl x left join (select q1,coalesce(q2,0) q2 from int8_tbl) y on x.q2 = y.q1,
+  int8_tbl x left join (select q1,coalesce(q2,'0') q2 from int8_tbl) y on x.q2 = y.q1,
   lateral (select x.q1,y.q1,y.q2) v(xq1,yq1,yq2);
 select x.* from
-  int8_tbl x left join (select q1,coalesce(q2,0) q2 from int8_tbl) y on x.q2 = y.q1,
+  int8_tbl x left join (select q1,coalesce(q2,'0') q2 from int8_tbl) y on x.q2 = y.q1,
   lateral (select x.q1,y.q1,y.q2) v(xq1,yq1,yq2);
 select v.* from
-  (int8_tbl x left join (select q1,coalesce(q2,0) q2 from int8_tbl) y on x.q2 = y.q1)
+  (int8_tbl x left join (select q1,coalesce(q2,'0') q2 from int8_tbl) y on x.q2 = y.q1)
   left join int4_tbl z on z.f1 = x.q2,
   lateral (select x.q1,y.q1 union all select x.q2,y.q2) v(vx,vy);
 select v.* from
-  (int8_tbl x left join (select q1,(select coalesce(q2,0)) q2 from int8_tbl) y on x.q2 = y.q1)
+  (int8_tbl x left join (select q1,(select coalesce(q2,'0')) q2 from int8_tbl) y on x.q2 = y.q1)
   left join int4_tbl z on z.f1 = x.q2,
   lateral (select x.q1,y.q1 union all select x.q2,y.q2) v(vx,vy);
 select v.* from
-  (int8_tbl x left join (select q1,(select coalesce(q2,0)) q2 from int8_tbl) y on x.q2 = y.q1)
+  (int8_tbl x left join (select q1,(select coalesce(q2,'0')) q2 from int8_tbl) y on x.q2 = y.q1)
   left join int4_tbl z on z.f1 = x.q2,
   lateral (select x.q1,y.q1 from onerow union all select x.q2,y.q2 from onerow) v(vx,vy);

@@ -1797,10 +1797,10 @@ select * from
 explain (verbose, costs off)
 select * from
   int8_tbl a left join
-  lateral (select *, coalesce(a.q2, 42) as x from int8_tbl b) ss on a.q2 = ss.q1;
+  lateral (select *, coalesce(a.q2, '42') as x from int8_tbl b) ss on a.q2 = ss.q1;
 select * from
   int8_tbl a left join
-  lateral (select *, coalesce(a.q2, 42) as x from int8_tbl b) ss on a.q2 = ss.q1;
+  lateral (select *, coalesce(a.q2, '42') as x from int8_tbl b) ss on a.q2 = ss.q1;

 -- lateral can result in join conditions appearing below their
 -- real semantic level
@@ -1841,7 +1841,7 @@ select * from
 explain (verbose, costs off)
 select * from
   int8_tbl c left join (
-    int8_tbl a left join (select q1, coalesce(q2,42) as x from int8_tbl b) ss1
+    int8_tbl a left join (select q1, coalesce(q2,'42') as x from int8_tbl b) ss1
       on a.q2 = ss1.q1
     cross join
     lateral (select q1, coalesce(ss1.x,q2) as y from int8_tbl d) ss2