aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/test/regress/expected/hash_func.out7
-rw-r--r--src/test/regress/expected/union.out357
-rw-r--r--src/test/regress/expected/with.out20
-rw-r--r--src/test/regress/sql/hash_func.sql6
-rw-r--r--src/test/regress/sql/union.sql92
-rw-r--r--src/test/regress/sql/with.sql18
6 files changed, 498 insertions, 2 deletions
diff --git a/src/test/regress/expected/hash_func.out b/src/test/regress/expected/hash_func.out
index e6e3410aaa1..e7d615fde59 100644
--- a/src/test/regress/expected/hash_func.out
+++ b/src/test/regress/expected/hash_func.out
@@ -177,6 +177,13 @@ WHERE hash_array(v)::bit(32) != hash_array_extended(v, 0)::bit(32)
-------+----------+-----------+-----------
(0 rows)
+-- array hashing with non-hashable element type
+SELECT v as value, hash_array(v)::bit(32) as standard
+FROM (VALUES ('{0}'::money[])) x(v);
+ERROR: could not identify a hash function for type money
+SELECT v as value, hash_array_extended(v, 0)::bit(32) as extended0
+FROM (VALUES ('{0}'::money[])) x(v);
+ERROR: could not identify an extended hash function for type money
SELECT v as value, hashbpchar(v)::bit(32) as standard,
hashbpcharextended(v, 0)::bit(32) as extended0,
hashbpcharextended(v, 1)::bit(32) as extended1
diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.out
index 6e72e92d801..22e1ff5c42d 100644
--- a/src/test/regress/expected/union.out
+++ b/src/test/regress/expected/union.out
@@ -345,10 +345,30 @@ ERROR: FOR NO KEY UPDATE is not allowed with UNION/INTERSECT/EXCEPT
1 | 2 | 3
(1 row)
--- exercise both hashed and sorted implementations of INTERSECT/EXCEPT
+-- exercise both hashed and sorted implementations of UNION/INTERSECT/EXCEPT
set enable_hashagg to on;
explain (costs off)
select count(*) from
+ ( select unique1 from tenk1 union select fivethous from tenk1 ) ss;
+ QUERY PLAN
+----------------------------------------------------------------
+ Aggregate
+ -> HashAggregate
+ Group Key: tenk1.unique1
+ -> Append
+ -> Index Only Scan using tenk1_unique1 on tenk1
+ -> Seq Scan on tenk1 tenk1_1
+(6 rows)
+
+select count(*) from
+ ( select unique1 from tenk1 union select fivethous from tenk1 ) ss;
+ count
+-------
+ 10000
+(1 row)
+
+explain (costs off)
+select count(*) from
( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
QUERY PLAN
------------------------------------------------------------------------------------
@@ -391,6 +411,27 @@ select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10;
set enable_hashagg to off;
explain (costs off)
select count(*) from
+ ( select unique1 from tenk1 union select fivethous from tenk1 ) ss;
+ QUERY PLAN
+----------------------------------------------------------------------
+ Aggregate
+ -> Unique
+ -> Sort
+ Sort Key: tenk1.unique1
+ -> Append
+ -> Index Only Scan using tenk1_unique1 on tenk1
+ -> Seq Scan on tenk1 tenk1_1
+(7 rows)
+
+select count(*) from
+ ( select unique1 from tenk1 union select fivethous from tenk1 ) ss;
+ count
+-------
+ 10000
+(1 row)
+
+explain (costs off)
+select count(*) from
( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
QUERY PLAN
------------------------------------------------------------------------------------------
@@ -435,6 +476,320 @@ select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10;
(1 row)
reset enable_hashagg;
+-- non-hashable type
+set enable_hashagg to on;
+explain (costs off)
+select x from (values (100::money), (200::money)) _(x) union select x from (values (100::money), (300::money)) _(x);
+ QUERY PLAN
+-----------------------------------------------
+ Unique
+ -> Sort
+ Sort Key: "*VALUES*".column1
+ -> Append
+ -> Values Scan on "*VALUES*"
+ -> Values Scan on "*VALUES*_1"
+(6 rows)
+
+set enable_hashagg to off;
+explain (costs off)
+select x from (values (100::money), (200::money)) _(x) union select x from (values (100::money), (300::money)) _(x);
+ QUERY PLAN
+-----------------------------------------------
+ Unique
+ -> Sort
+ Sort Key: "*VALUES*".column1
+ -> Append
+ -> Values Scan on "*VALUES*"
+ -> Values Scan on "*VALUES*_1"
+(6 rows)
+
+reset enable_hashagg;
+-- arrays
+set enable_hashagg to on;
+explain (costs off)
+select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4])) _(x);
+ QUERY PLAN
+-----------------------------------------
+ HashAggregate
+ Group Key: "*VALUES*".column1
+ -> Append
+ -> Values Scan on "*VALUES*"
+ -> Values Scan on "*VALUES*_1"
+(5 rows)
+
+select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4])) _(x);
+ x
+-------
+ {1,4}
+ {1,2}
+ {1,3}
+(3 rows)
+
+explain (costs off)
+select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4])) _(x);
+ QUERY PLAN
+-----------------------------------------------
+ HashSetOp Intersect
+ -> Append
+ -> Subquery Scan on "*SELECT* 1"
+ -> Values Scan on "*VALUES*"
+ -> Subquery Scan on "*SELECT* 2"
+ -> Values Scan on "*VALUES*_1"
+(6 rows)
+
+select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4])) _(x);
+ x
+-------
+ {1,2}
+(1 row)
+
+explain (costs off)
+select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4])) _(x);
+ QUERY PLAN
+-----------------------------------------------
+ HashSetOp Except
+ -> Append
+ -> Subquery Scan on "*SELECT* 1"
+ -> Values Scan on "*VALUES*"
+ -> Subquery Scan on "*SELECT* 2"
+ -> Values Scan on "*VALUES*_1"
+(6 rows)
+
+select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4])) _(x);
+ x
+-------
+ {1,3}
+(1 row)
+
+-- non-hashable type
+explain (costs off)
+select x from (values (array[100::money]), (array[200::money])) _(x) union select x from (values (array[100::money]), (array[300::money])) _(x);
+ QUERY PLAN
+-----------------------------------------------
+ Unique
+ -> Sort
+ Sort Key: "*VALUES*".column1
+ -> Append
+ -> Values Scan on "*VALUES*"
+ -> Values Scan on "*VALUES*_1"
+(6 rows)
+
+select x from (values (array[100::money]), (array[200::money])) _(x) union select x from (values (array[100::money]), (array[300::money])) _(x);
+ x
+-----------
+ {$100.00}
+ {$200.00}
+ {$300.00}
+(3 rows)
+
+set enable_hashagg to off;
+explain (costs off)
+select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4])) _(x);
+ QUERY PLAN
+-----------------------------------------------
+ Unique
+ -> Sort
+ Sort Key: "*VALUES*".column1
+ -> Append
+ -> Values Scan on "*VALUES*"
+ -> Values Scan on "*VALUES*_1"
+(6 rows)
+
+select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4])) _(x);
+ x
+-------
+ {1,2}
+ {1,3}
+ {1,4}
+(3 rows)
+
+explain (costs off)
+select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4])) _(x);
+ QUERY PLAN
+-----------------------------------------------------
+ SetOp Intersect
+ -> Sort
+ Sort Key: "*SELECT* 1".x
+ -> Append
+ -> Subquery Scan on "*SELECT* 1"
+ -> Values Scan on "*VALUES*"
+ -> Subquery Scan on "*SELECT* 2"
+ -> Values Scan on "*VALUES*_1"
+(8 rows)
+
+select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4])) _(x);
+ x
+-------
+ {1,2}
+(1 row)
+
+explain (costs off)
+select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4])) _(x);
+ QUERY PLAN
+-----------------------------------------------------
+ SetOp Except
+ -> Sort
+ Sort Key: "*SELECT* 1".x
+ -> Append
+ -> Subquery Scan on "*SELECT* 1"
+ -> Values Scan on "*VALUES*"
+ -> Subquery Scan on "*SELECT* 2"
+ -> Values Scan on "*VALUES*_1"
+(8 rows)
+
+select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4])) _(x);
+ x
+-------
+ {1,3}
+(1 row)
+
+reset enable_hashagg;
+-- records
+set enable_hashagg to on;
+-- currently no hashing support for record, so these will still run with sort plans:
+explain (costs off)
+select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x);
+ QUERY PLAN
+-----------------------------------------------
+ Unique
+ -> Sort
+ Sort Key: "*VALUES*".column1
+ -> Append
+ -> Values Scan on "*VALUES*"
+ -> Values Scan on "*VALUES*_1"
+(6 rows)
+
+select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x);
+ x
+-------
+ (1,2)
+ (1,3)
+ (1,4)
+(3 rows)
+
+explain (costs off)
+select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x);
+ QUERY PLAN
+-----------------------------------------------------
+ SetOp Intersect
+ -> Sort
+ Sort Key: "*SELECT* 1".x
+ -> Append
+ -> Subquery Scan on "*SELECT* 1"
+ -> Values Scan on "*VALUES*"
+ -> Subquery Scan on "*SELECT* 2"
+ -> Values Scan on "*VALUES*_1"
+(8 rows)
+
+select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x);
+ x
+-------
+ (1,2)
+(1 row)
+
+explain (costs off)
+select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x);
+ QUERY PLAN
+-----------------------------------------------------
+ SetOp Except
+ -> Sort
+ Sort Key: "*SELECT* 1".x
+ -> Append
+ -> Subquery Scan on "*SELECT* 1"
+ -> Values Scan on "*VALUES*"
+ -> Subquery Scan on "*SELECT* 2"
+ -> Values Scan on "*VALUES*_1"
+(8 rows)
+
+select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x);
+ x
+-------
+ (1,3)
+(1 row)
+
+-- non-hashable type
+explain (costs off)
+select x from (values (row(100::money)), (row(200::money))) _(x) union select x from (values (row(100::money)), (row(300::money))) _(x);
+ QUERY PLAN
+-----------------------------------------------
+ Unique
+ -> Sort
+ Sort Key: "*VALUES*".column1
+ -> Append
+ -> Values Scan on "*VALUES*"
+ -> Values Scan on "*VALUES*_1"
+(6 rows)
+
+select x from (values (row(100::money)), (row(200::money))) _(x) union select x from (values (row(100::money)), (row(300::money))) _(x);
+ x
+-----------
+ ($100.00)
+ ($200.00)
+ ($300.00)
+(3 rows)
+
+set enable_hashagg to off;
+explain (costs off)
+select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x);
+ QUERY PLAN
+-----------------------------------------------
+ Unique
+ -> Sort
+ Sort Key: "*VALUES*".column1
+ -> Append
+ -> Values Scan on "*VALUES*"
+ -> Values Scan on "*VALUES*_1"
+(6 rows)
+
+select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x);
+ x
+-------
+ (1,2)
+ (1,3)
+ (1,4)
+(3 rows)
+
+explain (costs off)
+select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x);
+ QUERY PLAN
+-----------------------------------------------------
+ SetOp Intersect
+ -> Sort
+ Sort Key: "*SELECT* 1".x
+ -> Append
+ -> Subquery Scan on "*SELECT* 1"
+ -> Values Scan on "*VALUES*"
+ -> Subquery Scan on "*SELECT* 2"
+ -> Values Scan on "*VALUES*_1"
+(8 rows)
+
+select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x);
+ x
+-------
+ (1,2)
+(1 row)
+
+explain (costs off)
+select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x);
+ QUERY PLAN
+-----------------------------------------------------
+ SetOp Except
+ -> Sort
+ Sort Key: "*SELECT* 1".x
+ -> Append
+ -> Subquery Scan on "*SELECT* 1"
+ -> Values Scan on "*VALUES*"
+ -> Subquery Scan on "*SELECT* 2"
+ -> Values Scan on "*VALUES*_1"
+(8 rows)
+
+select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x);
+ x
+-------
+ (1,3)
+(1 row)
+
+reset enable_hashagg;
--
-- Mixed types
--
diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out
index 457f3bf04fa..1f984a9fa48 100644
--- a/src/test/regress/expected/with.out
+++ b/src/test/regress/expected/with.out
@@ -49,6 +49,15 @@ SELECT * FROM t;
5
(5 rows)
+-- UNION DISTINCT requires hashable type
+WITH RECURSIVE t(n) AS (
+ VALUES (1::money)
+UNION
+ SELECT n+1::money FROM t WHERE n < 100::money
+)
+SELECT sum(n) FROM t;
+ERROR: could not implement recursive UNION
+DETAIL: All column datatypes must be hashable.
-- recursive view
CREATE RECURSIVE VIEW nums (n) AS
VALUES (1)
@@ -616,6 +625,17 @@ select * from search_graph;
2 | 3 | arc 2 -> 3 | f | {"(1,4)","(4,5)","(5,1)","(1,2)","(2,3)"}
(25 rows)
+-- UNION DISTINCT currently not supported here because row types not hashable
+with recursive search_graph(f, t, label, is_cycle, path) as (
+ select *, false, array[row(g.f, g.t)] from graph g
+ union distinct
+ select g.*, row(g.f, g.t) = any(path), path || row(g.f, g.t)
+ from graph g, search_graph sg
+ where g.f = sg.t and not is_cycle
+)
+select * from search_graph;
+ERROR: could not implement recursive UNION
+DETAIL: All column datatypes must be hashable.
-- ordering by the path column has same effect as SEARCH DEPTH FIRST
with recursive search_graph(f, t, label, is_cycle, path) as (
select *, false, array[row(g.f, g.t)] from graph g
diff --git a/src/test/regress/sql/hash_func.sql b/src/test/regress/sql/hash_func.sql
index a3e2decc2c4..de84e68ba31 100644
--- a/src/test/regress/sql/hash_func.sql
+++ b/src/test/regress/sql/hash_func.sql
@@ -130,6 +130,12 @@ FROM (VALUES ('{0}'::int4[]), ('{0,1,2,3,4}'), ('{17,18,19,20}'),
WHERE hash_array(v)::bit(32) != hash_array_extended(v, 0)::bit(32)
OR hash_array(v)::bit(32) = hash_array_extended(v, 1)::bit(32);
+-- array hashing with non-hashable element type
+SELECT v as value, hash_array(v)::bit(32) as standard
+FROM (VALUES ('{0}'::money[])) x(v);
+SELECT v as value, hash_array_extended(v, 0)::bit(32) as extended0
+FROM (VALUES ('{0}'::money[])) x(v);
+
SELECT v as value, hashbpchar(v)::bit(32) as standard,
hashbpcharextended(v, 0)::bit(32) as extended0,
hashbpcharextended(v, 1)::bit(32) as extended1
diff --git a/src/test/regress/sql/union.sql b/src/test/regress/sql/union.sql
index 5f4881d5943..6cee454a4cf 100644
--- a/src/test/regress/sql/union.sql
+++ b/src/test/regress/sql/union.sql
@@ -118,12 +118,18 @@ SELECT q1 FROM int8_tbl EXCEPT ALL SELECT q1 FROM int8_tbl FOR NO KEY UPDATE;
(SELECT 1,2,3 UNION SELECT 4,5,6) EXCEPT SELECT 4,5,6;
(SELECT 1,2,3 UNION SELECT 4,5,6 ORDER BY 1,2) EXCEPT SELECT 4,5,6;
--- exercise both hashed and sorted implementations of INTERSECT/EXCEPT
+-- exercise both hashed and sorted implementations of UNION/INTERSECT/EXCEPT
set enable_hashagg to on;
explain (costs off)
select count(*) from
+ ( select unique1 from tenk1 union select fivethous from tenk1 ) ss;
+select count(*) from
+ ( select unique1 from tenk1 union select fivethous from tenk1 ) ss;
+
+explain (costs off)
+select count(*) from
( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
select count(*) from
( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
@@ -136,6 +142,12 @@ set enable_hashagg to off;
explain (costs off)
select count(*) from
+ ( select unique1 from tenk1 union select fivethous from tenk1 ) ss;
+select count(*) from
+ ( select unique1 from tenk1 union select fivethous from tenk1 ) ss;
+
+explain (costs off)
+select count(*) from
( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
select count(*) from
( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
@@ -146,6 +158,84 @@ select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10;
reset enable_hashagg;
+-- non-hashable type
+set enable_hashagg to on;
+
+explain (costs off)
+select x from (values (100::money), (200::money)) _(x) union select x from (values (100::money), (300::money)) _(x);
+
+set enable_hashagg to off;
+
+explain (costs off)
+select x from (values (100::money), (200::money)) _(x) union select x from (values (100::money), (300::money)) _(x);
+
+reset enable_hashagg;
+
+-- arrays
+set enable_hashagg to on;
+
+explain (costs off)
+select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4])) _(x);
+select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4])) _(x);
+explain (costs off)
+select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4])) _(x);
+select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4])) _(x);
+explain (costs off)
+select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4])) _(x);
+select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4])) _(x);
+
+-- non-hashable type
+explain (costs off)
+select x from (values (array[100::money]), (array[200::money])) _(x) union select x from (values (array[100::money]), (array[300::money])) _(x);
+select x from (values (array[100::money]), (array[200::money])) _(x) union select x from (values (array[100::money]), (array[300::money])) _(x);
+
+set enable_hashagg to off;
+
+explain (costs off)
+select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4])) _(x);
+select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4])) _(x);
+explain (costs off)
+select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4])) _(x);
+select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4])) _(x);
+explain (costs off)
+select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4])) _(x);
+select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4])) _(x);
+
+reset enable_hashagg;
+
+-- records
+set enable_hashagg to on;
+
+-- currently no hashing support for record, so these will still run with sort plans:
+explain (costs off)
+select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x);
+select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x);
+explain (costs off)
+select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x);
+select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x);
+explain (costs off)
+select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x);
+select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x);
+
+-- non-hashable type
+explain (costs off)
+select x from (values (row(100::money)), (row(200::money))) _(x) union select x from (values (row(100::money)), (row(300::money))) _(x);
+select x from (values (row(100::money)), (row(200::money))) _(x) union select x from (values (row(100::money)), (row(300::money))) _(x);
+
+set enable_hashagg to off;
+
+explain (costs off)
+select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x);
+select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x);
+explain (costs off)
+select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x);
+select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x);
+explain (costs off)
+select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x);
+select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x);
+
+reset enable_hashagg;
+
--
-- Mixed types
--
diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql
index 2eea297a719..c6ce01a2d11 100644
--- a/src/test/regress/sql/with.sql
+++ b/src/test/regress/sql/with.sql
@@ -31,6 +31,14 @@ UNION ALL
)
SELECT * FROM t;
+-- UNION DISTINCT requires hashable type
+WITH RECURSIVE t(n) AS (
+ VALUES (1::money)
+UNION
+ SELECT n+1::money FROM t WHERE n < 100::money
+)
+SELECT sum(n) FROM t;
+
-- recursive view
CREATE RECURSIVE VIEW nums (n) AS
VALUES (1)
@@ -317,6 +325,16 @@ with recursive search_graph(f, t, label, is_cycle, path) as (
)
select * from search_graph;
+-- UNION DISTINCT currently not supported here because row types not hashable
+with recursive search_graph(f, t, label, is_cycle, path) as (
+ select *, false, array[row(g.f, g.t)] from graph g
+ union distinct
+ select g.*, row(g.f, g.t) = any(path), path || row(g.f, g.t)
+ from graph g, search_graph sg
+ where g.f = sg.t and not is_cycle
+)
+select * from search_graph;
+
-- ordering by the path column has same effect as SEARCH DEPTH FIRST
with recursive search_graph(f, t, label, is_cycle, path) as (
select *, false, array[row(g.f, g.t)] from graph g
generated by cgit v1.2.3 (git 2.25.1) at 2025-06-01 00:54:16 +0000