From 7c5d8c16e12e56c1043ff4a28e07a306a15c2b85 Mon Sep 17 00:00:00 2001 From: Andres Freund Date: Wed, 8 Feb 2017 16:58:21 -0800 Subject: [PATCH] Add explicit ORDER BY to a few tests that exercise hash-join code. A proposed patch, also by Thomas and in the same thread, would change the output order of these. Independent of the follow-up patches getting committed, nailing down the order in these specific tests at worst seems harmless. Author: Thomas Munro Discussion: https://postgr.es/m/CAEepm=1D4-tP7j7UAgT_j4ZX2j4Ehe1qgZQWFKBMb8F76UW5Rg@mail.gmail.com --- src/test/regress/expected/join.out | 94 ++++++++++++----------- src/test/regress/expected/rowsecurity.out | 14 ++-- src/test/regress/sql/join.sql | 6 +- src/test/regress/sql/rowsecurity.sql | 4 +- 4 files changed, 62 insertions(+), 56 deletions(-) diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index c3bb4fe767..4992048170 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -4493,80 +4493,84 @@ select count(*) from tenk1 a, explain (costs off) select * from int8_tbl a, int8_tbl x left join lateral (select a.q1 from int4_tbl y) ss(z) - on x.q2 = ss.z; - QUERY PLAN ------------------------------------------- - Nested Loop - -> Seq Scan on int8_tbl a - -> Hash Right Join - Hash Cond: ((a.q1) = x.q2) - -> Seq Scan on int4_tbl y - -> Hash - -> Seq Scan on int8_tbl x -(7 rows) + on x.q2 = ss.z + order by a.q1, a.q2, x.q1, x.q2, ss.z; + QUERY PLAN +------------------------------------------------ + Sort + Sort Key: a.q1, a.q2, x.q1, x.q2, (a.q1) + -> Nested Loop + -> Seq Scan on int8_tbl a + -> Hash Right Join + Hash Cond: ((a.q1) = x.q2) + -> Seq Scan on int4_tbl y + -> Hash + -> Seq Scan on int8_tbl x +(9 rows) select * from int8_tbl a, int8_tbl x left join lateral (select a.q1 from int4_tbl y) ss(z) - on x.q2 = ss.z; + on x.q2 = ss.z + order by a.q1, a.q2, x.q1, x.q2, ss.z; q1 | q2 | q1 | q2 | z ------------------+-------------------+------------------+-------------------+------------------ + 123 | 456 | 123 | 456 | + 123 | 456 | 123 | 4567890123456789 | + 123 | 456 | 4567890123456789 | -4567890123456789 | 123 | 456 | 4567890123456789 | 123 | 123 123 | 456 | 4567890123456789 | 123 | 123 123 | 456 | 4567890123456789 | 123 | 123 123 | 456 | 4567890123456789 | 123 | 123 123 | 456 | 4567890123456789 | 123 | 123 123 | 456 | 4567890123456789 | 4567890123456789 | - 123 | 456 | 123 | 4567890123456789 | - 123 | 456 | 123 | 456 | - 123 | 456 | 4567890123456789 | -4567890123456789 | + 123 | 4567890123456789 | 123 | 456 | + 123 | 4567890123456789 | 123 | 4567890123456789 | + 123 | 4567890123456789 | 4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 123 | 123 123 | 4567890123456789 | 4567890123456789 | 123 | 123 123 | 4567890123456789 | 4567890123456789 | 123 | 123 123 | 4567890123456789 | 4567890123456789 | 123 | 123 123 | 4567890123456789 | 4567890123456789 | 123 | 123 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 | - 123 | 4567890123456789 | 123 | 4567890123456789 | - 123 | 4567890123456789 | 123 | 456 | - 123 | 4567890123456789 | 4567890123456789 | -4567890123456789 | - 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 + 4567890123456789 | -4567890123456789 | 123 | 456 | + 4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | 4567890123456789 + 4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | 4567890123456789 + 4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | 4567890123456789 + 4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | 4567890123456789 + 4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | 4567890123456789 + 4567890123456789 | -4567890123456789 | 4567890123456789 | -4567890123456789 | + 4567890123456789 | -4567890123456789 | 4567890123456789 | 123 | + 4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 + 4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 + 4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 + 4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 + 4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 + 4567890123456789 | 123 | 123 | 456 | 4567890123456789 | 123 | 123 | 4567890123456789 | 4567890123456789 - 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 4567890123456789 | 123 | 123 | 4567890123456789 | 4567890123456789 - 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 4567890123456789 | 123 | 123 | 4567890123456789 | 4567890123456789 - 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 4567890123456789 | 123 | 123 | 4567890123456789 | 4567890123456789 - 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 4567890123456789 | 123 | 123 | 4567890123456789 | 4567890123456789 - 4567890123456789 | 123 | 4567890123456789 | 123 | - 4567890123456789 | 123 | 123 | 456 | 4567890123456789 | 123 | 4567890123456789 | -4567890123456789 | - 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 + 4567890123456789 | 123 | 4567890123456789 | 123 | + 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 + 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 + 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 + 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 + 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 + 4567890123456789 | 4567890123456789 | 123 | 456 | 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 - 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 - 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 - 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 - 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 - 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 | - 4567890123456789 | 4567890123456789 | 123 | 456 | 4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789 | - 4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 - 4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | 4567890123456789 - 4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 - 4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | 4567890123456789 - 4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 - 4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | 4567890123456789 - 4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 - 4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | 4567890123456789 - 4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 - 4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | 4567890123456789 - 4567890123456789 | -4567890123456789 | 4567890123456789 | 123 | - 4567890123456789 | -4567890123456789 | 123 | 456 | - 4567890123456789 | -4567890123456789 | 4567890123456789 | -4567890123456789 | + 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 | + 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 + 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 + 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 + 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 + 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 (57 rows) -- lateral reference to a join alias variable diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out index 25407bf9dd..7bf29368d0 100644 --- a/src/test/regress/expected/rowsecurity.out +++ b/src/test/regress/expected/rowsecurity.out @@ -448,15 +448,15 @@ CREATE POLICY p2 ON category ALTER TABLE category ENABLE ROW LEVEL SECURITY; -- cannot delete PK referenced by invisible FK SET SESSION AUTHORIZATION regress_rls_bob; -SELECT * FROM document d FULL OUTER JOIN category c on d.cid = c.cid; +SELECT * FROM document d FULL OUTER JOIN category c on d.cid = c.cid ORDER BY d.did, c.cid; did | cid | dlevel | dauthor | dtitle | cid | cname -----+-----+--------+-----------------+--------------------+-----+------------ - 2 | 11 | 2 | regress_rls_bob | my second novel | 11 | novel 1 | 11 | 1 | regress_rls_bob | my first novel | 11 | novel - | | | | | 33 | technology - 5 | 44 | 2 | regress_rls_bob | my second manga | | - 4 | 44 | 1 | regress_rls_bob | my first manga | | + 2 | 11 | 2 | regress_rls_bob | my second novel | 11 | novel 3 | 22 | 2 | regress_rls_bob | my science fiction | | + 4 | 44 | 1 | regress_rls_bob | my first manga | | + 5 | 44 | 2 | regress_rls_bob | my second manga | | + | | | | | 33 | technology (6 rows) DELETE FROM category WHERE cid = 33; -- fails with FK violation @@ -464,12 +464,12 @@ ERROR: update or delete on table "category" violates foreign key constraint "do DETAIL: Key is still referenced from table "document". -- can insert FK referencing invisible PK SET SESSION AUTHORIZATION regress_rls_carol; -SELECT * FROM document d FULL OUTER JOIN category c on d.cid = c.cid; +SELECT * FROM document d FULL OUTER JOIN category c on d.cid = c.cid ORDER BY d.did, c.cid; did | cid | dlevel | dauthor | dtitle | cid | cname -----+-----+--------+-------------------+-----------------------+-----+----------------- 6 | 22 | 1 | regress_rls_carol | great science fiction | 22 | science fiction - 8 | 44 | 1 | regress_rls_carol | great manga | 44 | manga 7 | 33 | 2 | regress_rls_carol | great technology book | | + 8 | 44 | 1 | regress_rls_carol | great manga | 44 | manga (3 rows) INSERT INTO document VALUES (11, 33, 1, current_user, 'hoge'); diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index bf18a8f6c4..cca1a53c15 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -1537,10 +1537,12 @@ select count(*) from tenk1 a, explain (costs off) select * from int8_tbl a, int8_tbl x left join lateral (select a.q1 from int4_tbl y) ss(z) - on x.q2 = ss.z; + on x.q2 = ss.z + order by a.q1, a.q2, x.q1, x.q2, ss.z; select * from int8_tbl a, int8_tbl x left join lateral (select a.q1 from int4_tbl y) ss(z) - on x.q2 = ss.z; + on x.q2 = ss.z + order by a.q1, a.q2, x.q1, x.q2, ss.z; -- lateral reference to a join alias variable select * from (select f1/2 as x from int4_tbl) ss1 join int4_tbl i4 on x = f1, diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql index 5e2f4ef884..1b6896e57c 100644 --- a/src/test/regress/sql/rowsecurity.sql +++ b/src/test/regress/sql/rowsecurity.sql @@ -178,12 +178,12 @@ ALTER TABLE category ENABLE ROW LEVEL SECURITY; -- cannot delete PK referenced by invisible FK SET SESSION AUTHORIZATION regress_rls_bob; -SELECT * FROM document d FULL OUTER JOIN category c on d.cid = c.cid; +SELECT * FROM document d FULL OUTER JOIN category c on d.cid = c.cid ORDER BY d.did, c.cid; DELETE FROM category WHERE cid = 33; -- fails with FK violation -- can insert FK referencing invisible PK SET SESSION AUTHORIZATION regress_rls_carol; -SELECT * FROM document d FULL OUTER JOIN category c on d.cid = c.cid; +SELECT * FROM document d FULL OUTER JOIN category c on d.cid = c.cid ORDER BY d.did, c.cid; INSERT INTO document VALUES (11, 33, 1, current_user, 'hoge'); -- UNIQUE or PRIMARY KEY constraint violation DOES reveal presence of row -- 2.40.0