aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
authorAndres Freund <andres@anarazel.de>2017-02-08 16:58:21 -0800
committerAndres Freund <andres@anarazel.de>2017-02-08 16:58:21 -0800
commit7c5d8c16e12e56c1043ff4a28e07a306a15c2b85 (patch)
tree26d8586f60614ad0dee4b09459d5b5c51954dca1 /src
parent8f93bd8512466c9b6c4dbc1e5efd0f72b8e2be9a (diff)
downloadpostgresql-7c5d8c16e12e56c1043ff4a28e07a306a15c2b85.tar.gz
postgresql-7c5d8c16e12e56c1043ff4a28e07a306a15c2b85.zip
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
Diffstat (limited to 'src')
-rw-r--r--src/test/regress/expected/join.out94
-rw-r--r--src/test/regress/expected/rowsecurity.out14
-rw-r--r--src/test/regress/sql/join.sql6
-rw-r--r--src/test/regress/sql/rowsecurity.sql4
4 files changed, 62 insertions, 56 deletions
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index c3bb4fe767f..49920481707 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 25407bf9ddd..7bf29368d0f 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 bf18a8f6c42..cca1a53c15f 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 5e2f4ef8848..1b6896e57c8 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
generated by cgit v1.2.3 (git 2.25.1) at 2025-06-02 07:30:09 +0000