I wrote a new patch to add a lot more tests around hash-based plans.
This is intended to apply separately from the other patch, and the other
patch would then "flip" some of the test cases.
On 2020-11-13 20:51, Tom Lane wrote:
* The new test in with.sql claims to be testing row hashing, but
it's not very apparent that any such thing actually happens. Maybe
EXPLAIN the query, as well as execute it, to confirm that a
hash-based plan is used.
The recursive union requires hashing, but this is not visible in the
plan. You only get an error if there is no hashing support for a type.
I have added a test for this.
For the non-recursive union, I have added more tests that show this in
the plans.
* Is it worth devising a test case in which hashing is not possible
because one of the columns isn't hashable? I have mixed feelings
about this because the set of suitable column types may decrease
to empty over time, making it hard to maintain the test case.
I used the money type for now. If someone adds hash support for that,
we'll change it. I don't think this will change too rapidly, though.
--
Peter Eisentraut
2ndQuadrant, an EDB company
https://www.2ndquadrant.com/
From dface8b1a8a5714001b2f6cd7f949d2f78047306 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Tue, 17 Nov 2020 13:54:52 +0100
Subject: [PATCH] Add more tests for hashing and hash-based plans
- Test hashing of an array of a non-hashable element type.
- Test UNION [DISTINCT] with hash- and sort-based plans. (Previously,
only INTERSECT and EXCEPT where tested there.)
- Test UNION [DISTINCT] with a non-hashable column type. This
currently reverts to a sort-based plan even if enable_hashagg is on.
- Test UNION/INTERSECT/EXCEPT hash- and sort-based plans with arrays
as column types. Also test an array with a non-hashable element
type.
- Test UNION/INTERSECT/EXCEPT similarly with row types as column
types. Currently, this uses only sort-based plans because there is
no hashing support for row types.
- Add a test case that shows that recursive queries using UNION
[DISTINCT] require hashable column types.
- Add a currently failing test that uses UNION DISTINCT in a
cycle-detection use case using row types as column types.
Discussion:
https://www.postgresql.org/message-id/flat/38eccd35-4e2d-6767-1b3c-dada1eac3124%402ndquadrant.com
---
src/test/regress/expected/hash_func.out | 7 +
src/test/regress/expected/union.out | 357 +++++++++++++++++++++++-
src/test/regress/expected/with.out | 20 ++
src/test/regress/sql/hash_func.sql | 6 +
src/test/regress/sql/union.sql | 92 +++++-
src/test/regress/sql/with.sql | 18 ++
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 e6e3410aaa..e7d615fde5 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 6e72e92d80..22e1ff5c42 100644
--- a/src/test/regress/expected/union.out
+++ b/src/test/regress/expected/union.out
@@ -345,8 +345,28 @@ 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;
@@ -389,6 +409,27 @@ select unique1 from tenk1 except select unique2 from tenk1
where unique2 != 10;
(1 row)
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;
@@ -434,6 +475,320 @@ select unique1 from tenk1 except select unique2 from
tenk1 where unique2 != 10;
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 457f3bf04f..1f984a9fa4 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 a3e2decc2c..de84e68ba3 100644
--- a/src/test/regress/sql/hash_func.sql
+++ b/src/test/regress/sql/hash_func.sql
@@ -130,6 +130,12 @@
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 5f4881d594..6cee454a4c 100644
--- a/src/test/regress/sql/union.sql
+++ b/src/test/regress/sql/union.sql
@@ -118,10 +118,16 @@
(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;
@@ -134,6 +140,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;
@@ -146,6 +158,84 @@
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 2eea297a71..c6ce01a2d1 100644
--- a/src/test/regress/sql/with.sql
+++ b/src/test/regress/sql/with.sql
@@ -31,6 +31,14 @@
)
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 @@ CREATE TEMPORARY TABLE tree(
)
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
--
2.29.2
