Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit7c5d8c1

Browse files
committed
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 changethe output order of these. Independent of the follow-up patchesgetting committed, nailing down the order in these specific tests atworst seems harmless.Author: Thomas MunroDiscussion:https://postgr.es/m/CAEepm=1D4-tP7j7UAgT_j4ZX2j4Ehe1qgZQWFKBMb8F76UW5Rg@mail.gmail.com
1 parent8f93bd8 commit7c5d8c1

File tree

4 files changed

+62
-56
lines changed

4 files changed

+62
-56
lines changed

‎src/test/regress/expected/join.out

Lines changed: 49 additions & 45 deletions
Original file line numberDiff line numberDiff line change
@@ -4493,80 +4493,84 @@ select count(*) from tenk1 a,
44934493
explain (costs off)
44944494
select * from int8_tbl a,
44954495
int8_tbl x left join lateral (select a.q1 from int4_tbl y) ss(z)
4496-
on x.q2 = ss.z;
4497-
QUERY PLAN
4498-
------------------------------------------
4499-
Nested Loop
4500-
-> Seq Scan on int8_tbl a
4501-
-> Hash Right Join
4502-
Hash Cond: ((a.q1) = x.q2)
4503-
-> Seq Scan on int4_tbl y
4504-
-> Hash
4505-
-> Seq Scan on int8_tbl x
4506-
(7 rows)
4496+
on x.q2 = ss.z
4497+
order by a.q1, a.q2, x.q1, x.q2, ss.z;
4498+
QUERY PLAN
4499+
------------------------------------------------
4500+
Sort
4501+
Sort Key: a.q1, a.q2, x.q1, x.q2, (a.q1)
4502+
-> Nested Loop
4503+
-> Seq Scan on int8_tbl a
4504+
-> Hash Right Join
4505+
Hash Cond: ((a.q1) = x.q2)
4506+
-> Seq Scan on int4_tbl y
4507+
-> Hash
4508+
-> Seq Scan on int8_tbl x
4509+
(9 rows)
45074510

45084511
select * from int8_tbl a,
45094512
int8_tbl x left join lateral (select a.q1 from int4_tbl y) ss(z)
4510-
on x.q2 = ss.z;
4513+
on x.q2 = ss.z
4514+
order by a.q1, a.q2, x.q1, x.q2, ss.z;
45114515
q1 | q2 | q1 | q2 | z
45124516
------------------+-------------------+------------------+-------------------+------------------
4517+
123 | 456 | 123 | 456 |
4518+
123 | 456 | 123 | 4567890123456789 |
4519+
123 | 456 | 4567890123456789 | -4567890123456789 |
45134520
123 | 456 | 4567890123456789 | 123 | 123
45144521
123 | 456 | 4567890123456789 | 123 | 123
45154522
123 | 456 | 4567890123456789 | 123 | 123
45164523
123 | 456 | 4567890123456789 | 123 | 123
45174524
123 | 456 | 4567890123456789 | 123 | 123
45184525
123 | 456 | 4567890123456789 | 4567890123456789 |
4519-
123 |456 |123 | 4567890123456789 |
4520-
123 | 456| 123 | 456 |
4521-
123 | 456 | 4567890123456789 | -4567890123456789 |
4526+
123 |4567890123456789 | 123 | 456 |
4527+
123 |4567890123456789| 123 |4567890123456789 |
4528+
123 |4567890123456789 | 4567890123456789 | -4567890123456789 |
45224529
123 | 4567890123456789 | 4567890123456789 | 123 | 123
45234530
123 | 4567890123456789 | 4567890123456789 | 123 | 123
45244531
123 | 4567890123456789 | 4567890123456789 | 123 | 123
45254532
123 | 4567890123456789 | 4567890123456789 | 123 | 123
45264533
123 | 4567890123456789 | 4567890123456789 | 123 | 123
45274534
123 | 4567890123456789 | 4567890123456789 | 4567890123456789 |
4528-
123 | 4567890123456789 | 123 | 4567890123456789 |
4529-
123 | 4567890123456789 | 123 | 456 |
4530-
123 | 4567890123456789 | 4567890123456789 | -4567890123456789 |
4531-
4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 4567890123456789
4535+
4567890123456789 | -4567890123456789 | 123 | 456 |
4536+
4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | 4567890123456789
4537+
4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | 4567890123456789
4538+
4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | 4567890123456789
4539+
4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | 4567890123456789
4540+
4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | 4567890123456789
4541+
4567890123456789 | -4567890123456789 | 4567890123456789 | -4567890123456789 |
4542+
4567890123456789 | -4567890123456789 | 4567890123456789 | 123 |
4543+
4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
4544+
4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
4545+
4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
4546+
4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
4547+
4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
4548+
4567890123456789 | 123 | 123 | 456 |
45324549
4567890123456789 | 123 | 123 | 4567890123456789 | 4567890123456789
4533-
4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 4567890123456789
45344550
4567890123456789 | 123 | 123 | 4567890123456789 | 4567890123456789
4535-
4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 4567890123456789
45364551
4567890123456789 | 123 | 123 | 4567890123456789 | 4567890123456789
4537-
4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 4567890123456789
45384552
4567890123456789 | 123 | 123 | 4567890123456789 | 4567890123456789
4539-
4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 4567890123456789
45404553
4567890123456789 | 123 | 123 | 4567890123456789 | 4567890123456789
4541-
4567890123456789 | 123 | 4567890123456789 | 123 |
4542-
4567890123456789 | 123 | 123 | 456 |
45434554
4567890123456789 | 123 | 4567890123456789 | -4567890123456789 |
4544-
4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
4555+
4567890123456789 | 123 | 4567890123456789 | 123 |
4556+
4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 4567890123456789
4557+
4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 4567890123456789
4558+
4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 4567890123456789
4559+
4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 4567890123456789
4560+
4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 4567890123456789
4561+
4567890123456789 | 4567890123456789 | 123 | 456 |
45454562
4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789
4546-
4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
45474563
4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789
4548-
4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
45494564
4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789
4550-
4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
45514565
4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789
4552-
4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
45534566
4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789
4554-
4567890123456789 | 4567890123456789 | 4567890123456789 | 123 |
4555-
4567890123456789 | 4567890123456789 | 123 | 456 |
45564567
4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789 |
4557-
4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
4558-
4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | 4567890123456789
4559-
4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
4560-
4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | 4567890123456789
4561-
4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
4562-
4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | 4567890123456789
4563-
4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
4564-
4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | 4567890123456789
4565-
4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
4566-
4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | 4567890123456789
4567-
4567890123456789 | -4567890123456789 | 4567890123456789 | 123 |
4568-
4567890123456789 | -4567890123456789 | 123 | 456 |
4569-
4567890123456789 | -4567890123456789 | 4567890123456789 | -4567890123456789 |
4568+
4567890123456789 | 4567890123456789 | 4567890123456789 | 123 |
4569+
4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
4570+
4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
4571+
4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
4572+
4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
4573+
4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
45704574
(57 rows)
45714575

45724576
-- lateral reference to a join alias variable

‎src/test/regress/expected/rowsecurity.out

Lines changed: 7 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -448,28 +448,28 @@ CREATE POLICY p2 ON category
448448
ALTER TABLE category ENABLE ROW LEVEL SECURITY;
449449
-- cannot delete PK referenced by invisible FK
450450
SET SESSION AUTHORIZATION regress_rls_bob;
451-
SELECT * FROM document d FULL OUTER JOIN category c on d.cid = c.cid;
451+
SELECT * FROM document d FULL OUTER JOIN category c on d.cid = c.cid ORDER BY d.did, c.cid;
452452
did | cid | dlevel | dauthor | dtitle | cid | cname
453453
-----+-----+--------+-----------------+--------------------+-----+------------
454-
2 | 11 | 2 | regress_rls_bob | my second novel | 11 | novel
455454
1 | 11 | 1 | regress_rls_bob | my first novel | 11 | novel
456-
| | | | | 33 | technology
457-
5 | 44 | 2 | regress_rls_bob | my second manga | |
458-
4 | 44 | 1 | regress_rls_bob | my first manga | |
455+
2 | 11 | 2 | regress_rls_bob | my second novel | 11 | novel
459456
3 | 22 | 2 | regress_rls_bob | my science fiction | |
457+
4 | 44 | 1 | regress_rls_bob | my first manga | |
458+
5 | 44 | 2 | regress_rls_bob | my second manga | |
459+
| | | | | 33 | technology
460460
(6 rows)
461461

462462
DELETE FROM category WHERE cid = 33; -- fails with FK violation
463463
ERROR: update or delete on table "category" violates foreign key constraint "document_cid_fkey" on table "document"
464464
DETAIL: Key is still referenced from table "document".
465465
-- can insert FK referencing invisible PK
466466
SET SESSION AUTHORIZATION regress_rls_carol;
467-
SELECT * FROM document d FULL OUTER JOIN category c on d.cid = c.cid;
467+
SELECT * FROM document d FULL OUTER JOIN category c on d.cid = c.cid ORDER BY d.did, c.cid;
468468
did | cid | dlevel | dauthor | dtitle | cid | cname
469469
-----+-----+--------+-------------------+-----------------------+-----+-----------------
470470
6 | 22 | 1 | regress_rls_carol | great science fiction | 22 | science fiction
471-
8 | 44 | 1 | regress_rls_carol | great manga | 44 | manga
472471
7 | 33 | 2 | regress_rls_carol | great technology book | |
472+
8 | 44 | 1 | regress_rls_carol | great manga | 44 | manga
473473
(3 rows)
474474

475475
INSERT INTO document VALUES (11, 33, 1, current_user, 'hoge');

‎src/test/regress/sql/join.sql

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1537,10 +1537,12 @@ select count(*) from tenk1 a,
15371537
explain (costs off)
15381538
select*from int8_tbl a,
15391539
int8_tbl xleft join lateral (selecta.q1from int4_tbl y) ss(z)
1540-
onx.q2=ss.z;
1540+
onx.q2=ss.z
1541+
order bya.q1,a.q2,x.q1,x.q2,ss.z;
15411542
select*from int8_tbl a,
15421543
int8_tbl xleft join lateral (selecta.q1from int4_tbl y) ss(z)
1543-
onx.q2=ss.z;
1544+
onx.q2=ss.z
1545+
order bya.q1,a.q2,x.q1,x.q2,ss.z;
15441546

15451547
-- lateral reference to a join alias variable
15461548
select*from (select f1/2as xfrom int4_tbl) ss1join int4_tbl i4on x= f1,

‎src/test/regress/sql/rowsecurity.sql

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -178,12 +178,12 @@ ALTER TABLE category ENABLE ROW LEVEL SECURITY;
178178

179179
-- cannot delete PK referenced by invisible FK
180180
SET SESSION AUTHORIZATION regress_rls_bob;
181-
SELECT*FROM document d FULL OUTERJOIN category cond.cid=c.cid;
181+
SELECT*FROM document d FULL OUTERJOIN category cond.cid=c.cidORDER BYd.did,c.cid;
182182
DELETEFROM categoryWHERE cid=33;-- fails with FK violation
183183

184184
-- can insert FK referencing invisible PK
185185
SET SESSION AUTHORIZATION regress_rls_carol;
186-
SELECT*FROM document d FULL OUTERJOIN category cond.cid=c.cid;
186+
SELECT*FROM document d FULL OUTERJOIN category cond.cid=c.cidORDER BYd.did,c.cid;
187187
INSERT INTO documentVALUES (11,33,1,current_user,'hoge');
188188

189189
-- UNIQUE or PRIMARY KEY constraint violation DOES reveal presence of row

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp