Tom Lane писал 2021-07-26 18:18:
Alexander Pyhalov <a.pyha...@postgrespro.ru> writes:
[ 0001-Allow-pushing-CASE-expression-to-foreign-server-v6.patch ]
This doesn't compile cleanly:
deparse.c: In function 'foreign_expr_walker.isra.4':
deparse.c:920:8: warning: 'collation' may be used uninitialized in
this function [-Wmaybe-uninitialized]
if (collation != outer_cxt->collation)
^
deparse.c:914:3: warning: 'state' may be used uninitialized in this
function [-Wmaybe-uninitialized]
switch (state)
^~~~~~
These uninitialized variables very likely explain the fact that it
fails
regression tests, both for me and for the cfbot. Even if this weren't
an
outright bug, we don't tolerate code that produces warnings on common
compilers.
regards, tom lane
Hi.
Of course, this is a patch issue. Don't understand how I overlooked
this.
Rebased on master and fixed it. Tests are passing here (but they also
passed for previous patch version).
What exact tests are failing?
--
Best regards,
Alexander Pyhalov,
Postgres Professional
From 9c9fa2e37fc62ddcd8dc6176306d74b7e219fd26 Mon Sep 17 00:00:00 2001
From: Alexander Pyhalov <a.pyha...@postgrespro.ru>
Date: Thu, 22 Jul 2021 11:42:16 +0300
Subject: [PATCH] Allow pushing CASE expression to foreign server
---
contrib/postgres_fdw/deparse.c | 150 ++++++++++++++
.../postgres_fdw/expected/postgres_fdw.out | 184 ++++++++++++++++++
contrib/postgres_fdw/sql/postgres_fdw.sql | 63 ++++++
3 files changed, 397 insertions(+)
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index 31919fda8c6..df1aaf8e713 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -186,6 +186,8 @@ static void appendFunctionName(Oid funcid, deparse_expr_cxt *context);
static Node *deparseSortGroupClause(Index ref, List *tlist, bool force_colno,
deparse_expr_cxt *context);
+static void deparseCaseExpr(CaseExpr *node, deparse_expr_cxt *context);
+
/*
* Helper functions
*/
@@ -627,6 +629,105 @@ foreign_expr_walker(Node *node,
state = FDW_COLLATE_NONE;
}
break;
+ case T_CaseExpr:
+ {
+ ListCell *lc;
+ foreign_loc_cxt tmp_cxt;
+ CaseExpr *ce = (CaseExpr *) node;
+
+ /*
+ * case arg expression collation doesn't affect result
+ * collation
+ */
+ tmp_cxt.collation = InvalidOid;
+ tmp_cxt.state = FDW_COLLATE_NONE;
+ if (ce->arg && !foreign_expr_walker((Node *) ce->arg, glob_cxt, &tmp_cxt))
+ return false;
+
+ /* Recurse to case clause subexpressions. */
+ foreach(lc, ce->args)
+ {
+ CaseWhen *cw = lfirst_node(CaseWhen, lc);
+ Node *whenExpr = (Node *) cw->expr;
+
+ /*
+ * The parser should have produced WHEN clauses of the
+ * form "CaseTestExpr = RHS", possibly with an implicit
+ * coercion inserted above the CaseTestExpr. However in an
+ * expression that's been through the optimizer, the WHEN
+ * clause could be almost anything (since the equality
+ * operator could have been expanded into an inline
+ * function). In this case forbid pushdown.
+ */
+
+ if (ce->arg)
+ {
+ List *whenExprArgs;
+
+ if (!IsA(whenExpr, OpExpr))
+ return false;
+
+ whenExprArgs = ((OpExpr *) whenExpr)->args;
+
+ if ((list_length(whenExprArgs) != 2) ||
+ !IsA(strip_implicit_coercions(linitial(whenExprArgs)), CaseTestExpr))
+ return false;
+ }
+
+ /*
+ * case when expression collation doesn't affect result
+ * collation
+ */
+ tmp_cxt.collation = InvalidOid;
+ tmp_cxt.state = FDW_COLLATE_NONE;
+ /* Recurse to case clause expression. */
+ if (!foreign_expr_walker((Node *) cw->expr,
+ glob_cxt, &tmp_cxt))
+ return false;
+
+ /* Recurse to result expression. */
+ if (!foreign_expr_walker((Node *) cw->result,
+ glob_cxt, &inner_cxt))
+ return false;
+ }
+
+ if (!foreign_expr_walker((Node *) ce->defresult, glob_cxt, &inner_cxt))
+ return false;
+
+ /*
+ * Collation rule is same as for function nodes.
+ */
+ collation = ce->casecollid;
+ if (collation == InvalidOid)
+ state = FDW_COLLATE_NONE;
+ else if (inner_cxt.state == FDW_COLLATE_SAFE &&
+ collation == inner_cxt.collation)
+ state = FDW_COLLATE_SAFE;
+ else if (collation == DEFAULT_COLLATION_OID)
+ state = FDW_COLLATE_NONE;
+ else
+ state = FDW_COLLATE_UNSAFE;
+ }
+ break;
+ case T_CaseTestExpr:
+ {
+ CaseTestExpr *c = (CaseTestExpr *) node;
+
+ /*
+ * Collation rule is same as for function nodes.
+ */
+ collation = c->collation;
+ if (collation == InvalidOid)
+ state = FDW_COLLATE_NONE;
+ else if (inner_cxt.state == FDW_COLLATE_SAFE &&
+ collation == inner_cxt.collation)
+ state = FDW_COLLATE_SAFE;
+ else if (collation == DEFAULT_COLLATION_OID)
+ state = FDW_COLLATE_NONE;
+ else
+ state = FDW_COLLATE_UNSAFE;
+ }
+ break;
case T_NullTest:
{
NullTest *nt = (NullTest *) node;
@@ -2462,6 +2563,9 @@ deparseExpr(Expr *node, deparse_expr_cxt *context)
case T_Aggref:
deparseAggref((Aggref *) node, context);
break;
+ case T_CaseExpr:
+ deparseCaseExpr((CaseExpr *) node, context);
+ break;
default:
elog(ERROR, "unsupported expression type for deparse: %d",
(int) nodeTag(node));
@@ -3007,6 +3111,52 @@ deparseNullTest(NullTest *node, deparse_expr_cxt *context)
}
}
+/*
+ * Deparse CASE expression
+ */
+static void
+deparseCaseExpr(CaseExpr *node, deparse_expr_cxt *context)
+{
+ StringInfo buf = context->buf;
+ ListCell *lc = NULL;
+
+ appendStringInfoString(buf, "(CASE");
+
+ /* If this is a CASE arg WHEN clause process arg first */
+ if (node->arg != NULL)
+ {
+ appendStringInfoString(buf, " ");
+ deparseExpr(node->arg, context);
+ }
+
+ /* Add each condition/result of the CASE clause */
+ foreach(lc, node->args)
+ {
+ CaseWhen *whenclause = (CaseWhen *) lfirst(lc);
+
+ /* WHEN */
+ appendStringInfoString(buf, " WHEN ");
+ if (node->arg == NULL) /* CASE WHEN */
+ deparseExpr(whenclause->expr, context);
+ else /* CASE arg WHEN */
+ deparseExpr(lsecond(((OpExpr *) whenclause->expr)->args), context);
+
+ /* THEN */
+ appendStringInfoString(buf, " THEN ");
+ deparseExpr(whenclause->result, context);
+ }
+
+ /* add ELSE if needed */
+ if (node->defresult != NULL)
+ {
+ appendStringInfoString(buf, " ELSE ");
+ deparseExpr(node->defresult, context);
+ }
+
+ /* append END */
+ appendStringInfoString(buf, " END)");
+}
+
/*
* Deparse ARRAY[...] construct.
*/
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index ed25e7a743f..9a966b73cc1 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -5561,6 +5561,190 @@ UPDATE ft2 d SET c2 = CASE WHEN random() >= 0 THEN d.c2 ELSE 0 END
UPDATE ft2 d SET c2 = CASE WHEN random() >= 0 THEN d.c2 ELSE 0 END
FROM ft2 AS t WHERE d.c1 = t.c1 AND d.c1 > 1000;
+-- Test CASE pushdown
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE ft2 d SET c2 = CASE WHEN c2 > 0 THEN c2 ELSE 0 END
+WHERE c1 > 1000;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------
+ Update on public.ft2 d
+ -> Foreign Update on public.ft2 d
+ Remote SQL: UPDATE "S 1"."T 1" SET c2 = (CASE WHEN (c2 > 0) THEN c2 ELSE 0 END) WHERE (("C 1" > 1000))
+(3 rows)
+
+UPDATE ft2 d SET c2 = CASE WHEN c2 > 0 THEN c2 ELSE 0 END
+WHERE c1 > 1000;
+-- CASE in WHERE clause
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT c1,c2,c3 FROM ft2 WHERE CASE WHEN c2 > 600 THEN c2 END > 500 ORDER BY c1;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan on public.ft2
+ Output: c1, c2, c3
+ Remote SQL: SELECT "C 1", c2, c3 FROM "S 1"."T 1" WHERE (((CASE WHEN (c2 > 600) THEN c2 ELSE NULL::integer END) > 500)) ORDER BY "C 1" ASC NULLS LAST
+(3 rows)
+
+SELECT c1,c2,c3 FROM ft2 WHERE CASE WHEN c2 > 600 THEN c2 END > 500 ORDER BY c1;
+ c1 | c2 | c3
+------+-----+--------------------
+ 1009 | 609 | 0000900009_update9
+ 1019 | 609 | 0001900019_update9
+(2 rows)
+
+-- Nested CASE
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT c1,c2,c3 FROM ft2 WHERE CASE CASE WHEN c2 > 0 THEN c2 END WHEN 100 THEN 601 WHEN c2 THEN c2 ELSE 0 END > 600 ORDER BY c1;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan on public.ft2
+ Output: c1, c2, c3
+ Remote SQL: SELECT "C 1", c2, c3 FROM "S 1"."T 1" WHERE (((CASE (CASE WHEN (c2 > 0) THEN c2 ELSE NULL::integer END) WHEN 100 THEN 601 WHEN c2 THEN c2 ELSE 0 END) > 600)) ORDER BY "C 1" ASC NULLS LAST
+(3 rows)
+
+SELECT c1,c2,c3 FROM ft2 WHERE CASE CASE WHEN c2 > 0 THEN c2 END WHEN 100 THEN 601 WHEN c2 THEN c2 ELSE 0 END > 600 ORDER BY c1;
+ c1 | c2 | c3
+------+-----+--------------------
+ 1009 | 609 | 0000900009_update9
+ 1010 | 100 | 0001000010
+ 1019 | 609 | 0001900019_update9
+ 1020 | 100 | 0002000020
+(4 rows)
+
+-- Aggregate function with CASE clause, both must be pushed down
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT sum(CASE WHEN mod(c1, 4) = 0 THEN 1 ELSE 2 END) FROM ft1;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------
+ Foreign Scan
+ Output: (sum(CASE WHEN (mod(c1, 4) = 0) THEN 1 ELSE 2 END))
+ Relations: Aggregate on (public.ft1)
+ Remote SQL: SELECT sum((CASE WHEN (mod("C 1", 4) = 0) THEN 1 ELSE 2 END)) FROM "S 1"."T 1"
+(4 rows)
+
+-- Same but with out ELSE clause
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT sum(CASE WHEN mod(c1, 4) = 0 THEN 1 END) FROM ft1;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------
+ Foreign Scan
+ Output: (sum(CASE WHEN (mod(c1, 4) = 0) THEN 1 ELSE NULL::integer END))
+ Relations: Aggregate on (public.ft1)
+ Remote SQL: SELECT sum((CASE WHEN (mod("C 1", 4) = 0) THEN 1 ELSE NULL::integer END)) FROM "S 1"."T 1"
+(4 rows)
+
+-- Different CASE form using a function in the condition
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM ft1 WHERE c1 > (CASE WHEN mod(c1, 4) = 0 THEN 1 ELSE 100 END);
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan on public.ft1
+ Output: c1, c2, c3, c4, c5, c6, c7, c8
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" > (CASE WHEN (mod("C 1", 4) = 0) THEN 1 ELSE 100 END)))
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM ft1 WHERE c1 > (CASE mod(c1, 4) WHEN 0 THEN 1 WHEN 2 THEN 50 ELSE 100 END);
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan on public.ft1
+ Output: c1, c2, c3, c4, c5, c6, c7, c8
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" > (CASE mod("C 1", 4) WHEN 0 THEN 1 WHEN 2 THEN 50 ELSE 100 END)))
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM ft1 WHERE c1 > (CASE WHEN mod(c1, 4) = 0 THEN 1 WHEN mod(c1, 6) = 0 THEN 50 ELSE 100 END);
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan on public.ft1
+ Output: c1, c2, c3, c4, c5, c6, c7, c8
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" > (CASE WHEN (mod("C 1", 4) = 0) THEN 1 WHEN (mod("C 1", 6) = 0) THEN 50 ELSE 100 END)))
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM ft1 WHERE substr(c3, 6) = (CASE WHEN mod(c1, 4) = 0 THEN '_trig_update' ELSE '_update' END);
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan on public.ft1
+ Output: c1, c2, c3, c4, c5, c6, c7, c8
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE ((substr(c3, 6) = (CASE WHEN (mod("C 1", 4) = 0) THEN '_trig_update'::text ELSE '_update'::text END)))
+(3 rows)
+
+-- CASE involved in a prepared statement
+PREPARE pre_case_select AS SELECT * FROM ft1 WHERE substr(c3, 6) = (CASE WHEN mod(c1, 4) = 0 THEN $1 ELSE $2 END);
+EXPLAIN (VERBOSE, COSTS OFF) EXECUTE pre_case_select('_trig_update', '_update');
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan on public.ft1
+ Output: c1, c2, c3, c4, c5, c6, c7, c8
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE ((substr(c3, 6) = (CASE WHEN (mod("C 1", 4) = 0) THEN '_trig_update'::text ELSE '_update'::text END)))
+(3 rows)
+
+DEALLOCATE pre_case_select;
+-- Test that CASE pushdown is not happening
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE ft2 d SET c2 = CASE WHEN random() >= 0 THEN c2 ELSE 0 END
+WHERE c1 > 1000;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------
+ Update on public.ft2 d
+ Remote SQL: UPDATE "S 1"."T 1" SET c2 = $2 WHERE ctid = $1
+ -> Foreign Scan on public.ft2 d
+ Output: CASE WHEN (random() >= '0'::double precision) THEN c2 ELSE 0 END, ctid, d.*
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8, ctid FROM "S 1"."T 1" WHERE (("C 1" > 1000)) FOR UPDATE
+(5 rows)
+
+UPDATE ft2 d SET c2 = CASE WHEN c2 > 0 THEN c2 ELSE 0 END
+WHERE c1 > 1000;
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM ft1 WHERE substr(c3, 6) COLLATE "C" = (CASE WHEN mod(c1, 4) = 0 THEN '_trig_update' COLLATE "C" ELSE '_update' COLLATE "C" END) COLLATE "C";
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan on public.ft1
+ Output: c1, c2, c3, c4, c5, c6, c7, c8
+ Filter: ((substr(ft1.c3, 6))::text = CASE WHEN (mod(ft1.c1, 4) = 0) THEN '_trig_update'::text COLLATE "C" ELSE '_update'::text COLLATE "C" END)
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
+(4 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM ft1 WHERE c3 = (CASE random() WHEN 0 THEN '_trig_update' ELSE '_update' END);
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------
+ Foreign Scan on public.ft1
+ Output: c1, c2, c3, c4, c5, c6, c7, c8
+ Filter: (ft1.c3 = CASE random() WHEN '0'::double precision THEN '_trig_update'::text ELSE '_update'::text END)
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
+(4 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM ft1 WHERE c3 = (CASE WHEN mod(c1, 4) = 0 THEN '_trig_update' ELSE '_update' COLLATE "C" END);
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan on public.ft1
+ Output: c1, c2, c3, c4, c5, c6, c7, c8
+ Filter: ((ft1.c3)::text = CASE WHEN (mod(ft1.c1, 4) = 0) THEN '_trig_update'::text ELSE '_update'::text COLLATE "C" END)
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
+(4 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM ft1 WHERE c3 = (CASE WHEN mod(c1, 4) = 0 THEN '_trig_update' COLLATE "C" ELSE '_update' END);
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan on public.ft1
+ Output: c1, c2, c3, c4, c5, c6, c7, c8
+ Filter: ((ft1.c3)::text = CASE WHEN (mod(ft1.c1, 4) = 0) THEN '_trig_update'::text COLLATE "C" ELSE '_update'::text END)
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
+(4 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM ft1 WHERE c2 = (CASE WHEN mod(c1, 4) = 0 THEN 1 ELSE random() END);
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------
+ Foreign Scan on public.ft1
+ Output: c1, c2, c3, c4, c5, c6, c7, c8
+ Filter: ((ft1.c2)::double precision = CASE WHEN (mod(ft1.c1, 4) = 0) THEN '1'::double precision ELSE random() END)
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
+(4 rows)
+
-- Test UPDATE/DELETE with WHERE or JOIN/ON conditions containing
-- user-defined operators/functions
ALTER SERVER loopback OPTIONS (DROP extensions);
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 02a6b15a13f..fb259a2f3af 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -1265,6 +1265,69 @@ UPDATE ft2 d SET c2 = CASE WHEN random() >= 0 THEN d.c2 ELSE 0 END
UPDATE ft2 d SET c2 = CASE WHEN random() >= 0 THEN d.c2 ELSE 0 END
FROM ft2 AS t WHERE d.c1 = t.c1 AND d.c1 > 1000;
+-- Test CASE pushdown
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE ft2 d SET c2 = CASE WHEN c2 > 0 THEN c2 ELSE 0 END
+WHERE c1 > 1000;
+
+UPDATE ft2 d SET c2 = CASE WHEN c2 > 0 THEN c2 ELSE 0 END
+WHERE c1 > 1000;
+
+-- CASE in WHERE clause
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT c1,c2,c3 FROM ft2 WHERE CASE WHEN c2 > 600 THEN c2 END > 500 ORDER BY c1;
+
+SELECT c1,c2,c3 FROM ft2 WHERE CASE WHEN c2 > 600 THEN c2 END > 500 ORDER BY c1;
+
+-- Nested CASE
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT c1,c2,c3 FROM ft2 WHERE CASE CASE WHEN c2 > 0 THEN c2 END WHEN 100 THEN 601 WHEN c2 THEN c2 ELSE 0 END > 600 ORDER BY c1;
+
+SELECT c1,c2,c3 FROM ft2 WHERE CASE CASE WHEN c2 > 0 THEN c2 END WHEN 100 THEN 601 WHEN c2 THEN c2 ELSE 0 END > 600 ORDER BY c1;
+
+-- Aggregate function with CASE clause, both must be pushed down
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT sum(CASE WHEN mod(c1, 4) = 0 THEN 1 ELSE 2 END) FROM ft1;
+-- Same but with out ELSE clause
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT sum(CASE WHEN mod(c1, 4) = 0 THEN 1 END) FROM ft1;
+-- Different CASE form using a function in the condition
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM ft1 WHERE c1 > (CASE WHEN mod(c1, 4) = 0 THEN 1 ELSE 100 END);
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM ft1 WHERE c1 > (CASE mod(c1, 4) WHEN 0 THEN 1 WHEN 2 THEN 50 ELSE 100 END);
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM ft1 WHERE c1 > (CASE WHEN mod(c1, 4) = 0 THEN 1 WHEN mod(c1, 6) = 0 THEN 50 ELSE 100 END);
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM ft1 WHERE substr(c3, 6) = (CASE WHEN mod(c1, 4) = 0 THEN '_trig_update' ELSE '_update' END);
+-- CASE involved in a prepared statement
+PREPARE pre_case_select AS SELECT * FROM ft1 WHERE substr(c3, 6) = (CASE WHEN mod(c1, 4) = 0 THEN $1 ELSE $2 END);
+EXPLAIN (VERBOSE, COSTS OFF) EXECUTE pre_case_select('_trig_update', '_update');
+DEALLOCATE pre_case_select;
+
+-- Test that CASE pushdown is not happening
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE ft2 d SET c2 = CASE WHEN random() >= 0 THEN c2 ELSE 0 END
+WHERE c1 > 1000;
+
+UPDATE ft2 d SET c2 = CASE WHEN c2 > 0 THEN c2 ELSE 0 END
+WHERE c1 > 1000;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM ft1 WHERE substr(c3, 6) COLLATE "C" = (CASE WHEN mod(c1, 4) = 0 THEN '_trig_update' COLLATE "C" ELSE '_update' COLLATE "C" END) COLLATE "C";
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM ft1 WHERE c3 = (CASE random() WHEN 0 THEN '_trig_update' ELSE '_update' END);
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM ft1 WHERE c3 = (CASE WHEN mod(c1, 4) = 0 THEN '_trig_update' ELSE '_update' COLLATE "C" END);
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM ft1 WHERE c3 = (CASE WHEN mod(c1, 4) = 0 THEN '_trig_update' COLLATE "C" ELSE '_update' END);
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM ft1 WHERE c2 = (CASE WHEN mod(c1, 4) = 0 THEN 1 ELSE random() END);
+
-- Test UPDATE/DELETE with WHERE or JOIN/ON conditions containing
-- user-defined operators/functions
ALTER SERVER loopback OPTIONS (DROP extensions);
--
2.25.1
