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
