From 6efca23cc039b6a0b25d2ebf4a22ab7363d17fcf Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Fri, 11 Aug 2017 16:52:12 -0400 Subject: [PATCH] Add regression tests exercising the non-hashed code paths in nodeSetop.c. Perusal of the code coverage report shows that the existing regression test cases for INTERSECT and EXCEPT seemingly all prefer the SETOP_HASHED implementation. Add some test cases in which we force use of the SETOP_SORTED mode. --- src/test/regress/expected/union.out | 90 +++++++++++++++++++++++++++++ src/test/regress/sql/union.sql | 28 +++++++++ 2 files changed, 118 insertions(+) diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.out index 5c4edd1c16..ee26b163f7 100644 --- a/src/test/regress/expected/union.out +++ b/src/test/regress/expected/union.out @@ -345,6 +345,96 @@ 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 +set enable_hashagg to on; +explain (costs off) +select count(*) from + ( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss; + QUERY PLAN +------------------------------------------------------------------------------------ + Aggregate + -> Subquery Scan on ss + -> HashSetOp Intersect + -> Append + -> Subquery Scan on "*SELECT* 2" + -> Seq Scan on tenk1 + -> Subquery Scan on "*SELECT* 1" + -> Index Only Scan using tenk1_unique1 on tenk1 tenk1_1 +(8 rows) + +select count(*) from + ( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss; + count +------- + 5000 +(1 row) + +explain (costs off) +select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10; + QUERY PLAN +------------------------------------------------------------------------ + HashSetOp Except + -> Append + -> Subquery Scan on "*SELECT* 1" + -> Index Only Scan using tenk1_unique1 on tenk1 + -> Subquery Scan on "*SELECT* 2" + -> Index Only Scan using tenk1_unique2 on tenk1 tenk1_1 + Filter: (unique2 <> 10) +(7 rows) + +select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10; + unique1 +--------- + 10 +(1 row) + +set enable_hashagg to off; +explain (costs off) +select count(*) from + ( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss; + QUERY PLAN +------------------------------------------------------------------------------------------ + Aggregate + -> Subquery Scan on ss + -> SetOp Intersect + -> Sort + Sort Key: "*SELECT* 2".fivethous + -> Append + -> Subquery Scan on "*SELECT* 2" + -> Seq Scan on tenk1 + -> Subquery Scan on "*SELECT* 1" + -> Index Only Scan using tenk1_unique1 on tenk1 tenk1_1 +(10 rows) + +select count(*) from + ( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss; + count +------- + 5000 +(1 row) + +explain (costs off) +select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10; + QUERY PLAN +------------------------------------------------------------------------------ + SetOp Except + -> Sort + Sort Key: "*SELECT* 1".unique1 + -> Append + -> Subquery Scan on "*SELECT* 1" + -> Index Only Scan using tenk1_unique1 on tenk1 + -> Subquery Scan on "*SELECT* 2" + -> Index Only Scan using tenk1_unique2 on tenk1 tenk1_1 + Filter: (unique2 <> 10) +(9 rows) + +select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10; + unique1 +--------- + 10 +(1 row) + +reset enable_hashagg; -- -- Mixed types -- diff --git a/src/test/regress/sql/union.sql b/src/test/regress/sql/union.sql index 5e0eff2ca3..c0317cccb4 100644 --- a/src/test/regress/sql/union.sql +++ b/src/test/regress/sql/union.sql @@ -118,6 +118,34 @@ 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 + +set enable_hashagg to on; + +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; + +explain (costs off) +select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10; +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 intersect select fivethous from tenk1 ) ss; +select count(*) from + ( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss; + +explain (costs off) +select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10; +select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10; + +reset enable_hashagg; + -- -- Mixed types -- -- 2.40.0