summaryrefslogtreecommitdiff
path: root/src/test
diff options
context:
space:
mode:
Diffstat (limited to 'src/test')
-rw-r--r--src/test/regress/expected/generated_virtual.out48
-rw-r--r--src/test/regress/expected/predicate.out33
-rw-r--r--src/test/regress/sql/generated_virtual.sql11
-rw-r--r--src/test/regress/sql/predicate.sql20
4 files changed, 84 insertions, 28 deletions
diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
index dde325e46c6..249e68be654 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -1509,10 +1509,11 @@ create table gtest32 (
a int primary key,
b int generated always as (a * 2),
c int generated always as (10 + 10),
- d int generated always as (coalesce(a, 100)),
- e int
+ d int generated always as (coalesce(f, 100)),
+ e int,
+ f int
);
-insert into gtest32 values (1), (2);
+insert into gtest32 (a, f) values (1, 1), (2, 2);
analyze gtest32;
-- Ensure that nullingrel bits are propagated into the generation expressions
explain (costs off)
@@ -1591,46 +1592,47 @@ where coalesce(t2.b, 1) = 2 or t1.a is null;
-- Ensure that the generation expressions are wrapped into PHVs if needed
explain (verbose, costs off)
select t2.* from gtest32 t1 left join gtest32 t2 on false;
- QUERY PLAN
----------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------
Nested Loop Left Join
- Output: t2.a, (t2.a * 2), (20), (COALESCE(t2.a, 100)), t2.e
+ Output: t2.a, (t2.a * 2), (20), (COALESCE(t2.f, 100)), t2.e, t2.f
Join Filter: false
-> Seq Scan on generated_virtual_tests.gtest32 t1
- Output: t1.a, t1.b, t1.c, t1.d, t1.e
+ Output: t1.a, t1.b, t1.c, t1.d, t1.e, t1.f
-> Result
- Output: t2.a, t2.e, 20, COALESCE(t2.a, 100)
+ Output: t2.a, t2.e, t2.f, 20, COALESCE(t2.f, 100)
Replaces: Scan on t2
One-Time Filter: false
(9 rows)
select t2.* from gtest32 t1 left join gtest32 t2 on false;
- a | b | c | d | e
----+---+---+---+---
- | | | |
- | | | |
+ a | b | c | d | e | f
+---+---+---+---+---+---
+ | | | | |
+ | | | | |
(2 rows)
explain (verbose, costs off)
-select * from gtest32 t group by grouping sets (a, b, c, d, e) having c = 20;
- QUERY PLAN
------------------------------------------------------
+select * from gtest32 t group by grouping sets (a, b, c, d, e, f) having c = 20;
+ QUERY PLAN
+--------------------------------------------------------
HashAggregate
- Output: a, ((a * 2)), (20), (COALESCE(a, 100)), e
+ Output: a, ((a * 2)), (20), (COALESCE(f, 100)), e, f
Hash Key: t.a
Hash Key: (t.a * 2)
Hash Key: 20
- Hash Key: COALESCE(t.a, 100)
+ Hash Key: COALESCE(t.f, 100)
Hash Key: t.e
+ Hash Key: t.f
Filter: ((20) = 20)
-> Seq Scan on generated_virtual_tests.gtest32 t
- Output: a, (a * 2), 20, COALESCE(a, 100), e
-(10 rows)
+ Output: a, (a * 2), 20, COALESCE(f, 100), e, f
+(11 rows)
-select * from gtest32 t group by grouping sets (a, b, c, d, e) having c = 20;
- a | b | c | d | e
----+---+----+---+---
- | | 20 | |
+select * from gtest32 t group by grouping sets (a, b, c, d, e, f) having c = 20;
+ a | b | c | d | e | f
+---+---+----+---+---+---
+ | | 20 | | |
(1 row)
-- Ensure that the virtual generated columns in ALTER COLUMN TYPE USING expression are expanded
diff --git a/src/test/regress/expected/predicate.out b/src/test/regress/expected/predicate.out
index 94c343fe030..520b46cd321 100644
--- a/src/test/regress/expected/predicate.out
+++ b/src/test/regress/expected/predicate.out
@@ -484,3 +484,36 @@ SELECT * FROM pred_tab WHERE a < 3 AND b IS NOT NULL AND c IS NOT NULL;
(2 rows)
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;
+ QUERY PLAN
+--------------------------------
+ Seq Scan on pred_tab
+ Filter: (COALESCE(b, a) > 1)
+(2 rows)
+
+-- Ensure that argument "b*a" is dropped
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(b, a, b*a) > 1;
+ QUERY PLAN
+--------------------------------
+ Seq Scan on pred_tab
+ Filter: (COALESCE(b, a) > 1)
+(2 rows)
+
+-- Ensure that the entire COALESCE expression is replaced by "a"
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(a, b) > 1;
+ QUERY PLAN
+----------------------
+ Seq Scan on pred_tab
+ Filter: (a > 1)
+(2 rows)
+
+DROP TABLE pred_tab;
diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql
index 2911439776c..81152b39a79 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -817,11 +817,12 @@ create table gtest32 (
a int primary key,
b int generated always as (a * 2),
c int generated always as (10 + 10),
- d int generated always as (coalesce(a, 100)),
- e int
+ d int generated always as (coalesce(f, 100)),
+ e int,
+ f int
);
-insert into gtest32 values (1), (2);
+insert into gtest32 (a, f) values (1, 1), (2, 2);
analyze gtest32;
-- Ensure that nullingrel bits are propagated into the generation expressions
@@ -859,8 +860,8 @@ select t2.* from gtest32 t1 left join gtest32 t2 on false;
select t2.* from gtest32 t1 left join gtest32 t2 on false;
explain (verbose, costs off)
-select * from gtest32 t group by grouping sets (a, b, c, d, e) having c = 20;
-select * from gtest32 t group by grouping sets (a, b, c, d, e) having c = 20;
+select * from gtest32 t group by grouping sets (a, b, c, d, e, f) having c = 20;
+select * from gtest32 t group by grouping sets (a, b, c, d, e, f) having c = 20;
-- Ensure that the virtual generated columns in ALTER COLUMN TYPE USING expression are expanded
alter table gtest32 alter column e type bigint using b;
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;
generated by cgit v1.2.3 (git 2.46.0) at 2025-12-30 18:15:20 +0000