This is an automated email from the ASF dual-hosted git repository.
MisterRaindrop pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry-pxf.git
The following commit(s) were added to refs/heads/main by this push:
new 2789f811 fix: support filter pushdown for numeric columns with integer
constants
2789f811 is described below
commit 2789f811bb94ff155cc1b5076cda63a07ee73b34
Author: liuxiaoyu <[email protected]>
AuthorDate: Sun Apr 19 19:40:19 2026 +0800
fix: support filter pushdown for numeric columns with integer constants
When a numeric column is compared with an integer constant (e.g.,
biz_dt = 20260331), PostgreSQL wraps the constant in an implicit
int4 -> numeric type cast FuncExpr. The filter extraction code in
both the FDW path (fdw/pxf_filter.c) and the external table path
(external-table/src/pxffilters.c) only recognized plain Const
nodes, so these predicates were silently dropped and the query
fell back to a full scan on the remote source.
Before matching the Var+Const pattern, run eval_const_expressions()
on any operand that is neither a Var nor a Const. This folds the
implicit cast into a plain Const, after which the existing filter
serialization path can push the predicate down unchanged.
Fix is applied symmetrically in OpExprToPxfFilter (FDW) and
opexpr_to_pxffilter (external table). Neither function mutates
the original plan tree; only the local leftop/rightop pointers
are redirected at the simplified Const.
Test coverage:
* Extend FilterPushDownTest and FDW_FilterPushDownTest regression
suites with c3 = 5, c3 < 5, c3 > 1, c3 <= 2, c3 >= 5, c3 <> 5,
and c3 BETWEEN 1 AND 5, covering the full set of supported
scalar operators when the constant type differs from the column
type.
* Synchronize the int-const block across both test segments of
FilterPushDownTest.sql and its expected output.
---
external-table/src/pxffilters.c | 21 ++++
fdw/pxf_filter.c | 20 ++++
regression/expected/FDW_FilterPushDownTest.out | 138 ++++++++++++++++++++++++-
regression/expected/FilterPushDownTest.out | 138 ++++++++++++++++++++++++-
regression/sql/FDW_FilterPushDownTest.sql | 18 ++++
regression/sql/FilterPushDownTest.sql | 18 ++++
6 files changed, 349 insertions(+), 4 deletions(-)
diff --git a/external-table/src/pxffilters.c b/external-table/src/pxffilters.c
index 3ef0f062..7f8a21a4 100644
--- a/external-table/src/pxffilters.c
+++ b/external-table/src/pxffilters.c
@@ -26,6 +26,7 @@
#include "catalog/pg_operator.h"
#include "optimizer/clauses.h"
+#include "optimizer/optimizer.h"
#include "parser/parse_expr.h"
#include "utils/builtins.h"
#include "utils/guc.h"
@@ -775,6 +776,26 @@ opexpr_to_pxffilter(OpExpr *expr, PxfFilterDesc * filter)
}
}
+ /*
+ * Try to evaluate constant expressions in operands.
+ * This handles implicit type casts where PostgreSQL wraps a constant
+ * in a FuncExpr (e.g., int4 -> numeric cast when comparing a numeric
+ * column with an integer constant). Evaluating these gives us back a
+ * plain Const node that we can serialize for filter pushdown.
+ */
+ if (!IsA(leftop, Var) && !IsA(leftop, Const))
+ {
+ Node *simplified = eval_const_expressions(NULL, leftop);
+ if (IsA(simplified, Const))
+ leftop = simplified;
+ }
+ if (!IsA(rightop, Var) && !IsA(rightop, Const))
+ {
+ Node *simplified = eval_const_expressions(NULL, rightop);
+ if (IsA(simplified, Const))
+ rightop = simplified;
+ }
+
/* arguments must be VAR and CONST */
if (IsA(leftop, Var) && IsA(rightop, Const))
{
diff --git a/fdw/pxf_filter.c b/fdw/pxf_filter.c
index b57575cf..1e7ec464 100644
--- a/fdw/pxf_filter.c
+++ b/fdw/pxf_filter.c
@@ -24,6 +24,7 @@
#include "catalog/pg_operator.h"
#include "optimizer/clauses.h"
+#include "optimizer/optimizer.h"
#include "parser/parse_expr.h"
#include "utils/builtins.h"
#include "utils/guc.h"
@@ -852,6 +853,25 @@ OpExprToPxfFilter(OpExpr *expr, PxfFilterDesc *filter)
}
}
+ /*
+ * Try to evaluate constant expressions in operands.
+ * This handles implicit type casts where PostgreSQL wraps a constant
+ * in a FuncExpr (e.g., int4 -> numeric cast when comparing a numeric
+ * column with an integer constant). Evaluating these gives us back a
+ * plain Const node that we can serialize for filter pushdown.
+ */
+ if (!IsA(leftop, Var) && !IsA(leftop, Const))
+ {
+ Node *simplified = eval_const_expressions(NULL, leftop);
+ if (IsA(simplified, Const))
+ leftop = simplified;
+ }
+ if (!IsA(rightop, Var) && !IsA(rightop, Const))
+ {
+ Node *simplified = eval_const_expressions(NULL, rightop);
+ if (IsA(simplified, Const))
+ rightop = simplified;
+ }
/* arguments must be VAR and CONST */
if (IsA(leftop, Var) && IsA(rightop, Const))
diff --git a/regression/expected/FDW_FilterPushDownTest.out
b/regression/expected/FDW_FilterPushDownTest.out
index 172ede4d..15da050b 100644
--- a/regression/expected/FDW_FilterPushDownTest.out
+++ b/regression/expected/FDW_FilterPushDownTest.out
@@ -486,9 +486,76 @@ SELECT * FROM test_filter WHERE c3 IS NOT NULL ORDER BY
t0, a1;
| 0 | t | 0.01 | AA | AA | a3o9
(9 rows)
+-- test numeric predicates with integer constant (cross-type pushdown)
+SELECT * FROM test_filter WHERE c3 = 5 ORDER BY t0, a1;
+ t0 | a1 | b2 | c3 | d4 | e5 | filtervalue
+----+----+----+----+----+----+-------------
+(0 rows)
+
+SELECT * FROM test_filter WHERE c3 < 5 ORDER BY t0, a1;
+ t0 | a1 | b2 | c3 | d4 | e5 | filtervalue
+----+----+----+------+----+----+----------------
+ B | | f | 1.11 | BB | BB | a3c1700s1d5o1
+ C | 2 | | 2.21 | CC | CC | a3c1700s1d5o1
+ E | 4 | t | 4.41 | | EE | a3c1700s1d5o1
+ | 0 | t | 0.01 | AA | AA | a3c1700s1d5o1
+(4 rows)
+
+SELECT * FROM test_filter WHERE c3 > 1 ORDER BY t0, a1;
+ t0 | a1 | b2 | c3 | d4 | e5 | filtervalue
+----+----+----+------+----+----+----------------
+ B | | f | 1.11 | BB | BB | a3c1700s1d1o2
+ C | 2 | | 2.21 | CC | CC | a3c1700s1d1o2
+ E | 4 | t | 4.41 | | EE | a3c1700s1d1o2
+ F | 5 | f | 5.51 | FF | | a3c1700s1d1o2
+ G | 6 | t | 6.61 | GG | GG | a3c1700s1d1o2
+ H | 7 | f | 7.71 | HH | HH | a3c1700s1d1o2
+ I | 8 | t | 8.81 | II | II | a3c1700s1d1o2
+ J | 9 | f | 9.91 | JJ | JJ | a3c1700s1d1o2
+(8 rows)
+
+SELECT * FROM test_filter WHERE c3 <= 2 ORDER BY t0, a1;
+ t0 | a1 | b2 | c3 | d4 | e5 | filtervalue
+----+----+----+------+----+----+----------------
+ B | | f | 1.11 | BB | BB | a3c1700s1d2o3
+ | 0 | t | 0.01 | AA | AA | a3c1700s1d2o3
+(2 rows)
+
+SELECT * FROM test_filter WHERE c3 >= 5 ORDER BY t0, a1;
+ t0 | a1 | b2 | c3 | d4 | e5 | filtervalue
+----+----+----+------+----+----+----------------
+ F | 5 | f | 5.51 | FF | | a3c1700s1d5o4
+ G | 6 | t | 6.61 | GG | GG | a3c1700s1d5o4
+ H | 7 | f | 7.71 | HH | HH | a3c1700s1d5o4
+ I | 8 | t | 8.81 | II | II | a3c1700s1d5o4
+ J | 9 | f | 9.91 | JJ | JJ | a3c1700s1d5o4
+(5 rows)
+
+SELECT * FROM test_filter WHERE c3 <> 5 ORDER BY t0, a1;
+ t0 | a1 | b2 | c3 | d4 | e5 | filtervalue
+----+----+----+------+----+----+----------------
+ B | | f | 1.11 | BB | BB | a3c1700s1d5o6
+ C | 2 | | 2.21 | CC | CC | a3c1700s1d5o6
+ E | 4 | t | 4.41 | | EE | a3c1700s1d5o6
+ F | 5 | f | 5.51 | FF | | a3c1700s1d5o6
+ G | 6 | t | 6.61 | GG | GG | a3c1700s1d5o6
+ H | 7 | f | 7.71 | HH | HH | a3c1700s1d5o6
+ I | 8 | t | 8.81 | II | II | a3c1700s1d5o6
+ J | 9 | f | 9.91 | JJ | JJ | a3c1700s1d5o6
+ | 0 | t | 0.01 | AA | AA | a3c1700s1d5o6
+(9 rows)
+
+SELECT * FROM test_filter WHERE c3 BETWEEN 1 AND 5 ORDER BY t0, a1;
+ t0 | a1 | b2 | c3 | d4 | e5 | filtervalue
+----+----+----+------+----+----+------------------------------------
+ B | | f | 1.11 | BB | BB | a3c1700s1d1o4a3c1700s1d5o3l0
+ C | 2 | | 2.21 | CC | CC | a3c1700s1d1o4a3c1700s1d5o3l0
+ E | 4 | t | 4.41 | | EE | a3c1700s1d1o4a3c1700s1d5o3l0
+(3 rows)
+
-- test char predicates
SELECT * FROM test_filter WHERE d4 = 'BB' ORDER BY t0, a1;
- t0 | a1 | b2 | c3 | d4 | e5 | filtervalue
+ t0 | a1 | b2 | c3 | d4 | e5 | filtervalue
----+----+----+------+----+----+----------------
B | | f | 1.11 | BB | BB | a4c1042s2dBBo5
(1 row)
@@ -1222,9 +1289,76 @@ SELECT * FROM test_filter WHERE c3 IS NOT NULL ORDER BY
t0, a1;
| 0 | t | 0.01 | AA | AA | a3o9
(9 rows)
+-- test numeric predicates with integer constant (cross-type pushdown)
+SELECT * FROM test_filter WHERE c3 = 5 ORDER BY t0, a1;
+ t0 | a1 | b2 | c3 | d4 | e5 | filtervalue
+----+----+----+----+----+----+-------------
+(0 rows)
+
+SELECT * FROM test_filter WHERE c3 < 5 ORDER BY t0, a1;
+ t0 | a1 | b2 | c3 | d4 | e5 | filtervalue
+----+----+----+------+----+----+----------------
+ B | | f | 1.11 | BB | BB | a3c1700s1d5o1
+ C | 2 | | 2.21 | CC | CC | a3c1700s1d5o1
+ E | 4 | t | 4.41 | | EE | a3c1700s1d5o1
+ | 0 | t | 0.01 | AA | AA | a3c1700s1d5o1
+(4 rows)
+
+SELECT * FROM test_filter WHERE c3 > 1 ORDER BY t0, a1;
+ t0 | a1 | b2 | c3 | d4 | e5 | filtervalue
+----+----+----+------+----+----+----------------
+ B | | f | 1.11 | BB | BB | a3c1700s1d1o2
+ C | 2 | | 2.21 | CC | CC | a3c1700s1d1o2
+ E | 4 | t | 4.41 | | EE | a3c1700s1d1o2
+ F | 5 | f | 5.51 | FF | | a3c1700s1d1o2
+ G | 6 | t | 6.61 | GG | GG | a3c1700s1d1o2
+ H | 7 | f | 7.71 | HH | HH | a3c1700s1d1o2
+ I | 8 | t | 8.81 | II | II | a3c1700s1d1o2
+ J | 9 | f | 9.91 | JJ | JJ | a3c1700s1d1o2
+(8 rows)
+
+SELECT * FROM test_filter WHERE c3 <= 2 ORDER BY t0, a1;
+ t0 | a1 | b2 | c3 | d4 | e5 | filtervalue
+----+----+----+------+----+----+----------------
+ B | | f | 1.11 | BB | BB | a3c1700s1d2o3
+ | 0 | t | 0.01 | AA | AA | a3c1700s1d2o3
+(2 rows)
+
+SELECT * FROM test_filter WHERE c3 >= 5 ORDER BY t0, a1;
+ t0 | a1 | b2 | c3 | d4 | e5 | filtervalue
+----+----+----+------+----+----+----------------
+ F | 5 | f | 5.51 | FF | | a3c1700s1d5o4
+ G | 6 | t | 6.61 | GG | GG | a3c1700s1d5o4
+ H | 7 | f | 7.71 | HH | HH | a3c1700s1d5o4
+ I | 8 | t | 8.81 | II | II | a3c1700s1d5o4
+ J | 9 | f | 9.91 | JJ | JJ | a3c1700s1d5o4
+(5 rows)
+
+SELECT * FROM test_filter WHERE c3 <> 5 ORDER BY t0, a1;
+ t0 | a1 | b2 | c3 | d4 | e5 | filtervalue
+----+----+----+------+----+----+----------------
+ B | | f | 1.11 | BB | BB | a3c1700s1d5o6
+ C | 2 | | 2.21 | CC | CC | a3c1700s1d5o6
+ E | 4 | t | 4.41 | | EE | a3c1700s1d5o6
+ F | 5 | f | 5.51 | FF | | a3c1700s1d5o6
+ G | 6 | t | 6.61 | GG | GG | a3c1700s1d5o6
+ H | 7 | f | 7.71 | HH | HH | a3c1700s1d5o6
+ I | 8 | t | 8.81 | II | II | a3c1700s1d5o6
+ J | 9 | f | 9.91 | JJ | JJ | a3c1700s1d5o6
+ | 0 | t | 0.01 | AA | AA | a3c1700s1d5o6
+(9 rows)
+
+SELECT * FROM test_filter WHERE c3 BETWEEN 1 AND 5 ORDER BY t0, a1;
+ t0 | a1 | b2 | c3 | d4 | e5 | filtervalue
+----+----+----+------+----+----+------------------------------------
+ B | | f | 1.11 | BB | BB | a3c1700s1d1o4a3c1700s1d5o3l0
+ C | 2 | | 2.21 | CC | CC | a3c1700s1d1o4a3c1700s1d5o3l0
+ E | 4 | t | 4.41 | | EE | a3c1700s1d1o4a3c1700s1d5o3l0
+(3 rows)
+
-- test char predicates
SELECT * FROM test_filter WHERE d4 = 'BB' ORDER BY t0, a1;
- t0 | a1 | b2 | c3 | d4 | e5 | filtervalue
+ t0 | a1 | b2 | c3 | d4 | e5 | filtervalue
----+----+----+------+----+----+----------------
B | | f | 1.11 | BB | BB | a4c1042s2dBBo5
(1 row)
diff --git a/regression/expected/FilterPushDownTest.out
b/regression/expected/FilterPushDownTest.out
index 1d98bd4e..f24d814b 100644
--- a/regression/expected/FilterPushDownTest.out
+++ b/regression/expected/FilterPushDownTest.out
@@ -483,9 +483,76 @@ SELECT * FROM test_filter WHERE c3 IS NOT NULL ORDER BY
t0, a1;
| 0 | t | 0.01 | AA | AA | a3o9
(9 rows)
+-- test numeric predicates with integer constant (cross-type pushdown)
+SELECT * FROM test_filter WHERE c3 = 5 ORDER BY t0, a1;
+ t0 | a1 | b2 | c3 | d4 | e5 | filtervalue
+----+----+----+----+----+----+-------------
+(0 rows)
+
+SELECT * FROM test_filter WHERE c3 < 5 ORDER BY t0, a1;
+ t0 | a1 | b2 | c3 | d4 | e5 | filtervalue
+----+----+----+------+----+----+----------------
+ B | | f | 1.11 | BB | BB | a3c1700s1d5o1
+ C | 2 | | 2.21 | CC | CC | a3c1700s1d5o1
+ E | 4 | t | 4.41 | | EE | a3c1700s1d5o1
+ | 0 | t | 0.01 | AA | AA | a3c1700s1d5o1
+(4 rows)
+
+SELECT * FROM test_filter WHERE c3 > 1 ORDER BY t0, a1;
+ t0 | a1 | b2 | c3 | d4 | e5 | filtervalue
+----+----+----+------+----+----+----------------
+ B | | f | 1.11 | BB | BB | a3c1700s1d1o2
+ C | 2 | | 2.21 | CC | CC | a3c1700s1d1o2
+ E | 4 | t | 4.41 | | EE | a3c1700s1d1o2
+ F | 5 | f | 5.51 | FF | | a3c1700s1d1o2
+ G | 6 | t | 6.61 | GG | GG | a3c1700s1d1o2
+ H | 7 | f | 7.71 | HH | HH | a3c1700s1d1o2
+ I | 8 | t | 8.81 | II | II | a3c1700s1d1o2
+ J | 9 | f | 9.91 | JJ | JJ | a3c1700s1d1o2
+(8 rows)
+
+SELECT * FROM test_filter WHERE c3 <= 2 ORDER BY t0, a1;
+ t0 | a1 | b2 | c3 | d4 | e5 | filtervalue
+----+----+----+------+----+----+----------------
+ B | | f | 1.11 | BB | BB | a3c1700s1d2o3
+ | 0 | t | 0.01 | AA | AA | a3c1700s1d2o3
+(2 rows)
+
+SELECT * FROM test_filter WHERE c3 >= 5 ORDER BY t0, a1;
+ t0 | a1 | b2 | c3 | d4 | e5 | filtervalue
+----+----+----+------+----+----+----------------
+ F | 5 | f | 5.51 | FF | | a3c1700s1d5o4
+ G | 6 | t | 6.61 | GG | GG | a3c1700s1d5o4
+ H | 7 | f | 7.71 | HH | HH | a3c1700s1d5o4
+ I | 8 | t | 8.81 | II | II | a3c1700s1d5o4
+ J | 9 | f | 9.91 | JJ | JJ | a3c1700s1d5o4
+(5 rows)
+
+SELECT * FROM test_filter WHERE c3 <> 5 ORDER BY t0, a1;
+ t0 | a1 | b2 | c3 | d4 | e5 | filtervalue
+----+----+----+------+----+----+----------------
+ B | | f | 1.11 | BB | BB | a3c1700s1d5o6
+ C | 2 | | 2.21 | CC | CC | a3c1700s1d5o6
+ E | 4 | t | 4.41 | | EE | a3c1700s1d5o6
+ F | 5 | f | 5.51 | FF | | a3c1700s1d5o6
+ G | 6 | t | 6.61 | GG | GG | a3c1700s1d5o6
+ H | 7 | f | 7.71 | HH | HH | a3c1700s1d5o6
+ I | 8 | t | 8.81 | II | II | a3c1700s1d5o6
+ J | 9 | f | 9.91 | JJ | JJ | a3c1700s1d5o6
+ | 0 | t | 0.01 | AA | AA | a3c1700s1d5o6
+(9 rows)
+
+SELECT * FROM test_filter WHERE c3 BETWEEN 1 AND 5 ORDER BY t0, a1;
+ t0 | a1 | b2 | c3 | d4 | e5 | filtervalue
+----+----+----+------+----+----+------------------------------------
+ B | | f | 1.11 | BB | BB | a3c1700s1d1o4a3c1700s1d5o3l0
+ C | 2 | | 2.21 | CC | CC | a3c1700s1d1o4a3c1700s1d5o3l0
+ E | 4 | t | 4.41 | | EE | a3c1700s1d1o4a3c1700s1d5o3l0
+(3 rows)
+
-- test char predicates
SELECT * FROM test_filter WHERE d4 = 'BB' ORDER BY t0, a1;
- t0 | a1 | b2 | c3 | d4 | e5 | filtervalue
+ t0 | a1 | b2 | c3 | d4 | e5 | filtervalue
----+----+----+------+----+----+----------------
B | | f | 1.11 | BB | BB | a4c1042s2dBBo5
(1 row)
@@ -1219,9 +1286,76 @@ SELECT * FROM test_filter WHERE c3 IS NOT NULL ORDER BY
t0, a1;
| 0 | t | 0.01 | AA | AA | a3o8l2
(9 rows)
+-- test numeric predicates with integer constant (cross-type pushdown)
+SELECT * FROM test_filter WHERE c3 = 5 ORDER BY t0, a1;
+ t0 | a1 | b2 | c3 | d4 | e5 | filtervalue
+----+----+----+----+----+----+-------------
+(0 rows)
+
+SELECT * FROM test_filter WHERE c3 < 5 ORDER BY t0, a1;
+ t0 | a1 | b2 | c3 | d4 | e5 | filtervalue
+----+----+----+------+----+----+----------------
+ B | | f | 1.11 | BB | BB | a3c1700s1d5o1
+ C | 2 | | 2.21 | CC | CC | a3c1700s1d5o1
+ E | 4 | t | 4.41 | | EE | a3c1700s1d5o1
+ | 0 | t | 0.01 | AA | AA | a3c1700s1d5o1
+(4 rows)
+
+SELECT * FROM test_filter WHERE c3 > 1 ORDER BY t0, a1;
+ t0 | a1 | b2 | c3 | d4 | e5 | filtervalue
+----+----+----+------+----+----+----------------
+ B | | f | 1.11 | BB | BB | a3c1700s1d1o2
+ C | 2 | | 2.21 | CC | CC | a3c1700s1d1o2
+ E | 4 | t | 4.41 | | EE | a3c1700s1d1o2
+ F | 5 | f | 5.51 | FF | | a3c1700s1d1o2
+ G | 6 | t | 6.61 | GG | GG | a3c1700s1d1o2
+ H | 7 | f | 7.71 | HH | HH | a3c1700s1d1o2
+ I | 8 | t | 8.81 | II | II | a3c1700s1d1o2
+ J | 9 | f | 9.91 | JJ | JJ | a3c1700s1d1o2
+(8 rows)
+
+SELECT * FROM test_filter WHERE c3 <= 2 ORDER BY t0, a1;
+ t0 | a1 | b2 | c3 | d4 | e5 | filtervalue
+----+----+----+------+----+----+----------------
+ B | | f | 1.11 | BB | BB | a3c1700s1d2o3
+ | 0 | t | 0.01 | AA | AA | a3c1700s1d2o3
+(2 rows)
+
+SELECT * FROM test_filter WHERE c3 >= 5 ORDER BY t0, a1;
+ t0 | a1 | b2 | c3 | d4 | e5 | filtervalue
+----+----+----+------+----+----+----------------
+ F | 5 | f | 5.51 | FF | | a3c1700s1d5o4
+ G | 6 | t | 6.61 | GG | GG | a3c1700s1d5o4
+ H | 7 | f | 7.71 | HH | HH | a3c1700s1d5o4
+ I | 8 | t | 8.81 | II | II | a3c1700s1d5o4
+ J | 9 | f | 9.91 | JJ | JJ | a3c1700s1d5o4
+(5 rows)
+
+SELECT * FROM test_filter WHERE c3 <> 5 ORDER BY t0, a1;
+ t0 | a1 | b2 | c3 | d4 | e5 | filtervalue
+----+----+----+------+----+----+----------------
+ B | | f | 1.11 | BB | BB | a3c1700s1d5o6
+ C | 2 | | 2.21 | CC | CC | a3c1700s1d5o6
+ E | 4 | t | 4.41 | | EE | a3c1700s1d5o6
+ F | 5 | f | 5.51 | FF | | a3c1700s1d5o6
+ G | 6 | t | 6.61 | GG | GG | a3c1700s1d5o6
+ H | 7 | f | 7.71 | HH | HH | a3c1700s1d5o6
+ I | 8 | t | 8.81 | II | II | a3c1700s1d5o6
+ J | 9 | f | 9.91 | JJ | JJ | a3c1700s1d5o6
+ | 0 | t | 0.01 | AA | AA | a3c1700s1d5o6
+(9 rows)
+
+SELECT * FROM test_filter WHERE c3 BETWEEN 1 AND 5 ORDER BY t0, a1;
+ t0 | a1 | b2 | c3 | d4 | e5 | filtervalue
+----+----+----+------+----+----+------------------------------------
+ B | | f | 1.11 | BB | BB | a3c1700s1d1o4a3c1700s1d5o3l0
+ C | 2 | | 2.21 | CC | CC | a3c1700s1d1o4a3c1700s1d5o3l0
+ E | 4 | t | 4.41 | | EE | a3c1700s1d1o4a3c1700s1d5o3l0
+(3 rows)
+
-- test char predicates
SELECT * FROM test_filter WHERE d4 = 'BB' ORDER BY t0, a1;
- t0 | a1 | b2 | c3 | d4 | e5 | filtervalue
+ t0 | a1 | b2 | c3 | d4 | e5 | filtervalue
----+----+----+------+----+----+----------------
B | | f | 1.11 | BB | BB | a4c1042s2dBBo5
(1 row)
diff --git a/regression/sql/FDW_FilterPushDownTest.sql
b/regression/sql/FDW_FilterPushDownTest.sql
index 7135658c..8b184b06 100644
--- a/regression/sql/FDW_FilterPushDownTest.sql
+++ b/regression/sql/FDW_FilterPushDownTest.sql
@@ -84,6 +84,15 @@ SELECT * FROM test_filter WHERE c3 NOT BETWEEN 1.11 AND
4.41 ORDER BY t0, a1;
SELECT * FROM test_filter WHERE c3 IS NULL ORDER BY t0, a1;
SELECT * FROM test_filter WHERE c3 IS NOT NULL ORDER BY t0, a1;
+-- test numeric predicates with integer constant (cross-type pushdown)
+SELECT * FROM test_filter WHERE c3 = 5 ORDER BY t0, a1;
+SELECT * FROM test_filter WHERE c3 < 5 ORDER BY t0, a1;
+SELECT * FROM test_filter WHERE c3 > 1 ORDER BY t0, a1;
+SELECT * FROM test_filter WHERE c3 <= 2 ORDER BY t0, a1;
+SELECT * FROM test_filter WHERE c3 >= 5 ORDER BY t0, a1;
+SELECT * FROM test_filter WHERE c3 <> 5 ORDER BY t0, a1;
+SELECT * FROM test_filter WHERE c3 BETWEEN 1 AND 5 ORDER BY t0, a1;
+
-- test char predicates
SELECT * FROM test_filter WHERE d4 = 'BB' ORDER BY t0, a1;
SELECT * FROM test_filter WHERE d4 = 'BB ' ORDER BY t0, a1;
@@ -178,6 +187,15 @@ SELECT * FROM test_filter WHERE c3 NOT BETWEEN 1.11 AND
4.41 ORDER BY t0, a1;
SELECT * FROM test_filter WHERE c3 IS NULL ORDER BY t0, a1;
SELECT * FROM test_filter WHERE c3 IS NOT NULL ORDER BY t0, a1;
+-- test numeric predicates with integer constant (cross-type pushdown)
+SELECT * FROM test_filter WHERE c3 = 5 ORDER BY t0, a1;
+SELECT * FROM test_filter WHERE c3 < 5 ORDER BY t0, a1;
+SELECT * FROM test_filter WHERE c3 > 1 ORDER BY t0, a1;
+SELECT * FROM test_filter WHERE c3 <= 2 ORDER BY t0, a1;
+SELECT * FROM test_filter WHERE c3 >= 5 ORDER BY t0, a1;
+SELECT * FROM test_filter WHERE c3 <> 5 ORDER BY t0, a1;
+SELECT * FROM test_filter WHERE c3 BETWEEN 1 AND 5 ORDER BY t0, a1;
+
-- test char predicates
SELECT * FROM test_filter WHERE d4 = 'BB' ORDER BY t0, a1;
SELECT * FROM test_filter WHERE d4 = 'BB ' ORDER BY t0, a1;
diff --git a/regression/sql/FilterPushDownTest.sql
b/regression/sql/FilterPushDownTest.sql
index 4697ef9f..2d5d6fc0 100644
--- a/regression/sql/FilterPushDownTest.sql
+++ b/regression/sql/FilterPushDownTest.sql
@@ -80,6 +80,15 @@ SELECT * FROM test_filter WHERE c3 NOT BETWEEN 1.11 AND
4.41 ORDER BY t0, a1;
SELECT * FROM test_filter WHERE c3 IS NULL ORDER BY t0, a1;
SELECT * FROM test_filter WHERE c3 IS NOT NULL ORDER BY t0, a1;
+-- test numeric predicates with integer constant (cross-type pushdown)
+SELECT * FROM test_filter WHERE c3 = 5 ORDER BY t0, a1;
+SELECT * FROM test_filter WHERE c3 < 5 ORDER BY t0, a1;
+SELECT * FROM test_filter WHERE c3 > 1 ORDER BY t0, a1;
+SELECT * FROM test_filter WHERE c3 <= 2 ORDER BY t0, a1;
+SELECT * FROM test_filter WHERE c3 >= 5 ORDER BY t0, a1;
+SELECT * FROM test_filter WHERE c3 <> 5 ORDER BY t0, a1;
+SELECT * FROM test_filter WHERE c3 BETWEEN 1 AND 5 ORDER BY t0, a1;
+
-- test char predicates
SELECT * FROM test_filter WHERE d4 = 'BB' ORDER BY t0, a1;
SELECT * FROM test_filter WHERE d4 = 'BB ' ORDER BY t0, a1;
@@ -174,6 +183,15 @@ SELECT * FROM test_filter WHERE c3 NOT BETWEEN 1.11 AND
4.41 ORDER BY t0, a1;
SELECT * FROM test_filter WHERE c3 IS NULL ORDER BY t0, a1;
SELECT * FROM test_filter WHERE c3 IS NOT NULL ORDER BY t0, a1;
+-- test numeric predicates with integer constant (cross-type pushdown)
+SELECT * FROM test_filter WHERE c3 = 5 ORDER BY t0, a1;
+SELECT * FROM test_filter WHERE c3 < 5 ORDER BY t0, a1;
+SELECT * FROM test_filter WHERE c3 > 1 ORDER BY t0, a1;
+SELECT * FROM test_filter WHERE c3 <= 2 ORDER BY t0, a1;
+SELECT * FROM test_filter WHERE c3 >= 5 ORDER BY t0, a1;
+SELECT * FROM test_filter WHERE c3 <> 5 ORDER BY t0, a1;
+SELECT * FROM test_filter WHERE c3 BETWEEN 1 AND 5 ORDER BY t0, a1;
+
-- test char predicates
SELECT * FROM test_filter WHERE d4 = 'BB' ORDER BY t0, a1;
SELECT * FROM test_filter WHERE d4 = 'BB ' ORDER BY t0, a1;
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
