diff options
Diffstat (limited to 'src/test/regress/sql/predicate.sql')
| -rw-r--r-- | src/test/regress/sql/predicate.sql | 20 |
1 files changed, 20 insertions, 0 deletions
diff --git a/src/test/regress/sql/predicate.sql b/src/test/regress/sql/predicate.sql index 7d4fda1bc18..c3d1a81ada1 100644 --- a/src/test/regress/sql/predicate.sql +++ b/src/test/regress/sql/predicate.sql @@ -240,3 +240,23 @@ SELECT * FROM pred_tab WHERE a < 3 AND b IS NOT NULL AND c IS NOT NULL; SELECT * FROM pred_tab WHERE a < 3 AND b IS NOT NULL AND c IS NOT NULL; DROP TABLE pred_tab; + +-- +-- Test that COALESCE expressions in predicates are simplified using +-- non-nullable arguments. +-- +CREATE TABLE pred_tab (a int NOT NULL, b int); + +-- Ensure that constant NULL arguments are dropped +EXPLAIN (COSTS OFF) +SELECT * FROM pred_tab WHERE COALESCE(NULL, b, NULL, a) > 1; + +-- Ensure that argument "b*a" is dropped +EXPLAIN (COSTS OFF) +SELECT * FROM pred_tab WHERE COALESCE(b, a, b*a) > 1; + +-- Ensure that the entire COALESCE expression is replaced by "a" +EXPLAIN (COSTS OFF) +SELECT * FROM pred_tab WHERE COALESCE(a, b) > 1; + +DROP TABLE pred_tab; |