8000 Update "join syntax" test for new capabilities. · postgrespro/postgres@92c8437 · GitHub
[go: up one dir, main page]
Skip to content
< 10000 /div>

Commit 92c8437

Browse files
author
Thomas G. Lockhart
committed
Update "join syntax" test for new capabilities.
1 parent ebd697a commit 92c8437

File tree

2 files changed

+312
-76
lines changed

2 files changed

+312
-76
lines changed

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

Lines changed: 225 additions & 46 deletions
Original file line numberDiff line numberDiff line change
@@ -2,12 +2,12 @@
22
-- JOIN
33
-- Test join clauses
44
--
5-
CREATE TABLE JOIN1_TBL (
5+
CREATE TABLE J1_TBL (
66
i integer,
77
j integer,
88
t text
99
);
10-
CREATE TABLE JOIN2_TBL (
10+
CREATE TABLE J2_TBL (
1111
i integer,
1212
k integer
1313
);
@@ -20,21 +20,99 @@ CREATE TABLE JOIN4_TBL (
2020
k integer,
2121
z integer
2222
);
23-
INSERT INTO JOIN1_TBL VALUES (1, 3, 'one');
24-
INSERT INTO JOIN1_TBL VALUES (2, 2, 'two');
25-
INSERT INTO JOIN1_TBL VALUES (3, 1, 'three');
26-
INSERT INTO JOIN1_TBL VALUES (4, 0, 'four');
27-
INSERT INTO JOIN2_TBL VALUES (1, -1);
28-
INSERT INTO JOIN2_TBL VALUES (2, 2);
29-
INSERT INTO JOIN2_TBL VALUES (3, -3);
30-
INSERT INTO JOIN2_TBL VALUES (2, 4);
23+
INSERT INTO J1_TBL VALUES (1, 3, 'one');
24+
INSERT INTO J1_TBL VALUES (2, 2, 'two');
25+
INSERT INTO J1_TBL VALUES (3, 1, 'three');
26+
INSERT INTO J1_TBL VALUES (4, 0, 'four');
27+
INSERT INTO J2_TBL VALUES (1, -1);
28+
INSERT INTO J2_TBL VALUES (2, 2);
29+
INSERT INTO J2_TBL VALUES (3, -3);
30+
INSERT INTO J2_TBL VALUES (2, 4);
31+
--
32+
-- CORRELATION NAMES
33+
-- Make sure that table/column aliases are supported
34+
-- before diving into more complex join syntax.
35+
--
36+
SELECT '' AS "xxx", *
37+
FROM J1_TBL AS tx;
38+
xxx | i | j | t
39+
-----+---+---+-------
40+
| 1 | 3 | one
41+
| 2 | 2 | two
42+
| 3 | 1 | three
43+
| 4 | 0 | four
44+
(4 rows)
45+
46+
SELECT '' AS "xxx", *
47+
FROM J1_TBL tx;
48+
xxx | i | j | t
49+
-----+---+---+-------
50+
| 1 | 3 | one
51+
| 2 | 2 | two
52+
| 3 | 1 | three
53+
| 4 | 0 | four
54+
(4 rows)
55+
56+
SELECT '' AS "xxx", *
57+
FROM J1_TBL AS t1 (a, b, c);
58+
xxx | a | b | c
59+
-----+---+---+-------
60+
| 1 | 3 | one
61+
| 2 | 2 | two
62+
| 3 | 1 | three
63+
| 4 | 0 | four
64+
(4 rows)
65+
66+
SELECT '' AS "xxx", *
67+
FROM J1_TBL t1 (a, b, c);
68+
xxx | a | b | c
69+
-----+---+---+-------
70+
| 1 | 3 | one
71+
| 2 | 2 | two
72+
| 3 | 1 | three
73+
| 4 | 0 | four
74+
(4 rows)
75+
76+
SELECT '' AS "xxx", *
77+
FROM J1_TBL t1 (a, b, c), J2_TBL t2 (d, e);
78+
xxx | a | b | c | d | e
79+
-----+---+---+-------+---+----
80+
| 1 | 3 | one | 1 | -1
81+
| 2 | 2 | two | 1 | -1
82+
| 3 | 1 | three | 1 | -1
83+
| 4 | 0 | four | 1 | -1
84+
| 1 | 3 | one | 2 | 2
85+
| 2 | 2 | two | 2 | 2
86+
| 3 | 1 | three | 2 | 2
87+
| 4 | 0 | four | 2 | 2
88+
| 1 | 3 | one | 3 | -3
89+
| 2 | 2 | two | 3 | -3
90+
| 3 | 1 | three | 3 | -3
91+
| 4 | 0 | four | 3 | -3
92+
| 1 | 3 | one | 2 | 4
93+
| 2 | 2 | two | 2 | 4
94+
| 3 | 1 | three | 2 | 4
95+
| 4 | 0 | four | 2 | 4
96+
(16 rows)
97+
98+
SELECT '' AS "xxx", t1.a, t2.e
99+
FROM J1_TBL t1 (a, b, c), J2_TBL t2 (d, e)
100+
WHERE t1.a = t2.d;
101+
xxx | a | e
102+
-----+---+----
103+
| 1 | -1
104+
| 2 | 2
105+
| 2 | 4
106+
| 3 | -3
107+
(4 rows)
108+
31109
--
32110
-- CROSS JOIN
33111
-- Qualifications are not allowed on cross joins,
34112
-- which degenerate into a standard unqualified inner join.
35113
--
36114
SELECT '' AS "xxx", *
37-
FROM JOIN1_TBL CROSS JOIN JOIN2_TBL;
115+
FROM J1_TBL CROSS JOIN J2_TBL;
38116
xxx | i | j | t | i | k
39117
-----+---+---+-------+---+----
40118
| 1 | 3 | one | 1 | -1
@@ -55,15 +133,41 @@ SELECT '' AS "xxx", *
55133
| 4 | 0 | four | 2 | 4
56134
(16 rows)
57135

136+
-- ambiguous column
58137
SELECT '' AS "xxx", i, k, t
59-
FROM JOIN1_TBL CROSS JOIN JOIN2_TBL;
138+
FROM J1_TBL CROSS JOIN J2_TBL;
60139
ERROR: Column 'i' is ambiguous
140+
-- resolve previous ambiguity by specifying the table name
141+
SELECT '' AS "xxx", t1.i, k, t
142+
FROM J1_TBL t1 CROSS JOIN J2_TBL t2;
143+
xxx | i | k | t
144+
-----+---+----+-------
145+
| 1 | -1 | one
146+
| 2 | -1 | two
147+
| 3 | -1 | three
148+
| 4 | -1 | four
149+
| 1 | 2 | one
150+
| 2 | 2 | two
151+
| 3 | 2 | three
152+
| 4 | 2 | four
153+
| 1 | -3 | one
154+
| 2 | -3 | two
155+
| 3 | -3 | three
156+
| 4 | -3 | four
157+
| 1 | 4 | one
158+
| 2 | 4 | two
159+
| 3 | 4 | three
160+
| 4 | 4 | four
161+
(16 rows)
162+
61163
SELECT '' AS "xxx", ii, tt, kk
62-
FROM JOIN1_TBL CROSS JOIN JOIN2_TBL AS JT (ii, jj, tt, ii2, kk);
63-
ERROR: parser: parse error at or near "("
64-
SELECT '' AS "xxx", jt.ii, jt.jj, jt.kk
65-
FROM JOIN1_TBL CROSS JOIN JOIN2_TBL AS JT (ii, jj, tt, ii2, kk);
66-
ERROR: parser: parse error at or near "("
164+
FROM (J1_TBL CROSS JOIN J2_TBL)
165+
AS tx (ii, jj, tt, ii2, kk);
166+
ERROR: JOIN table aliases are not supported
167+
SELECT '' AS "xxx", tx.ii, tx.jj, tx.kk
168+
FROM (J1_TBL t1 (a, b, c) CROSS JOIN J2_TBL t2 (d, e))
169+
AS tx (ii, jj, tt, ii2, kk);
170+
ERROR: JOIN table aliases are not supported
67171
--
68172
--
69173
-- Inner joins (equi-joins)
@@ -74,29 +178,108 @@ ERROR: parser: parse error at or near "("
74178
-- The USING syntax changes the shape of the resulting table
75179
-- by including a column in the USING clause only once in the result.
76180
--
77-
-- Inner equi-join on all columns with the same name
78-
SELECT '' AS "xxx", *
79-
FROM JOIN1_TBL NATURAL JOIN JOIN2_TBL;
80-
ERROR: JOIN expressions are not yet implemented
81181
-- Inner equi-join on specified column
82182
SELECT '' AS "xxx", *
83-
FROM JOIN1_TBL INNER JOIN JOIN2_TBL USING (i);
84-
ERROR: JOIN expressions are not yet implemented
183+
FROM J1_TBL INNER JOIN J2_TBL USING (i);
184+
xxx | i | j | t | k
185+
-----+---+---+-------+----
186+
| 1 | 3 | one | -1
187+
| 2 | 2 | two | 2
188+
| 2 | 2 | two | 4
189+
| 3 | 1 | three | -3
190+
(4 rows)
191+
85192
-- Same as above, slightly different syntax
86193
SELECT '' AS "xxx", *
87-
FROM JOIN1_TBL JOIN JOIN2_TBL USING (i);
88-
ERROR: JOIN expressions are not yet implemented
194+
FROM J1_TBL JOIN J2_TBL USING (i);
195+
xxx | i | j | t | k
196+
-----+---+---+-------+----
197+
| 1 | 3 | one | -1
198+
| 2 | 2 | two | 2
199+
| 2 | 2 | two | 4
200+
| 3 | 1 | three | -3
201+
(4 rows)
202+
203+
SELECT '' AS "xxx", *
204+
FROM J1_TBL t1 (a, b, c) JOIN J2_TBL t2 (a, d) USING (a);
205+
xxx | a | b | c | d
206+
-----+---+---+-------+----
207+
| 1 | 3 | one | -1
208+
| 2 | 2 | two | 2
209+
| 2 | 2 | two | 4
210+
| 3 | 1 | three | -3
211+
(4 rows)
212+
213+
SELECT '' AS "xxx", *
214+
FROM J1_TBL t1 (a, b, c) JOIN J2_TBL t2 (a, b) USING (b);
215+
xxx | b | a | c | a
216+
-----+---+---+-----+---
217+
| 2 | 2 | two | 2
218+
(1 row)
219+
220+
--
221+
-- NATURAL JOIN
222+
-- Inner equi-join on all columns with the same name
223+
--
224+
SELECT '' AS "xxx", *
225+
FROM J1_TBL NATURAL JOIN J2_TBL;
226+
xxx | i | j | t | k
227+
-----+---+---+-------+----
228+
| 1 | 3 | one | -1
229+
| 2 | 2 | two | 2
230+
| 2 | 2 | two | 4
231+
| 3 | 1 | three | -3
232+
(4 rows)
233+
234+
SELECT '' AS "xxx", *
235+
FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (a, d);
236+
xxx | a | b | c | d
237+
-----+---+---+-------+----
238+
| 1 | 3 | one | -1
239+
| 2 | 2 | two | 2
240+
| 2 | 2 | two | 4
241+
| 3 | 1 | three | -3
242+
(4 rows)
243+
244+
SELECT '' AS "xxx", *
245+
FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (d, a);
246+
xxx | a | b | c | d
247+
-----+---+---+------+---
248+
| 2 | 2 | two | 2
249+
| 4 | 0 | four | 2
250+
(2 rows)
251+
252+
SELECT '' AS "xxx", *
253+
FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (d, a);
254+
xxx | a | b | c | d
255+
-----+---+---+------+---
256+
| 2 | 2 | two | 2
257+
| 4 | 0 | four | 2
258+
(2 rows)
259+
260+
-- mismatch number of columns
261+
-- currently, Postgres will fill in with underlying names
262+
SELECT '' AS "xxx", *
263+
FROM J1_TBL t1 (a, b) NATURAL JOIN J2_TBL t2 (a);
264+
xxx | a | b | t | k
265+
-----+---+---+-------+----
266+
| 1 | 3 | one | -1
267+
| 2 | 2 | two | 2
268+
| 2 | 2 | two | 4
269+
| 3 | 1 | three | -3
270+
(4 rows)
271+
89272
--
90273
-- Inner joins (equi-joins)
91274
--
92275
SELECT '' AS "xxx", *
93-
FROM JOIN1_TBL JOIN JOIN2_TBL ON (JOIN1_TBL.i = JOIN2_TBL.i);
94-
ERROR: JOIN expressions are not yet implemented
276+
FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i = J2_TBL.i);
277+
ERROR: transformExpr: does not know how to transform node 501 (internal error)
95278
SELECT '' AS "xxx", *
96-
FROM JOIN1_TBL JOIN JOIN2_TBL ON (JOIN1_TBL.i = JOIN2_TBL.k);
97-
ERROR: JOIN expressions are not yet implemented
279+
FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i = J2_TBL.k);
280+
ERROR: transformExpr: does not know how to transform node 501 (internal error)
98281
SELECT '' AS "xxx", *
99-
FROM JOIN1_TBL CROSS JOIN JOIN2_TBL;
282+
FROM J1_TBL CROSS JOIN J2_TBL;
100283
xxx | i | j | t | i | k
101284
-----+---+---+-------+---+----
102285
| 1 | 3 | one | 1 | -1
@@ -121,32 +304,28 @@ SELECT '' AS "xxx", *
121304
-- Non-equi-joins
122305
--
123306
SELECT '' AS "xxx", *
124-
FROM JOIN1_TBL JOIN JOIN2_TBL ON (JOIN1_TBL.i <= JOIN2_TBL.k);
125-
ERROR: JOIN expressions are not yet implemented
307+
FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i <= J2_TBL.k);
308+
ERROR: transformExpr: does not know how to transform node 501 (internal error)
126309
--
127310
-- Outer joins
128311
--
129312
SELECT '' AS "xxx", *
130-
FROM JOIN1_TBL OUTER JOIN JOIN2_TBL USING (i);
131-
NOTICE: OUTER JOIN not yet implemented
132-
ERROR: JOIN expressions are not yet implemented
313+
FROM J1_TBL OUTER JOIN J2_TBL USING (i);
314+
ERROR: OUTER JOIN is not yet supported
133315
SELECT '' AS "xxx", *
134-
FROM JOIN1_TBL LEFT OUTER JOIN JOIN2_TBL USING (i);
135-
NOTICE: LEFT OUTER JOIN not yet implemented
136-
ERROR: JOIN expressions are not yet implemented
316+
FROM J1_TBL LEFT OUTER JOIN J2_TBL USING (i);
317+
ERROR: OUTER JOIN is not yet supported
137318
SELECT '' AS "xxx", *
138-
FROM JOIN1_TBL RIGHT OUTER JOIN JOIN2_TBL USING (i);
139-
NOTICE: RIGHT OUTER JOIN not yet implemented
140-
ERROR: JOIN expressions are not yet implemented
319+
FROM J1_TBL RIGHT OUTER JOIN J2_TBL USING (i);
320+
ERROR: OUTER JOIN is not yet supported
141321
SELECT '' AS "xxx", *
142-
FROM JOIN1_TBL FULL OUTER JOIN JOIN2_TBL USING (i);
143-
NOTICE: FULL OUTER JOIN not yet implemented
144-
ERROR: JOIN expressions are not yet implemented
322+
FROM J1_TBL FULL OUTER JOIN J2_TBL USING (i);
323+
ERROR: OUTER JOIN is not yet supported
145324
--
146325
-- More complicated constructs
147326
--
148327
--
149328
-- Clean up
150329
--
151-
DROP TABLE JOIN1_TBL;
152-
DROP TABLE JOIN2_TBL;
330+
DROP TABLE J1_TBL;
331+
DROP TABLE J2_TBL;

0 commit comments

Comments
 (0)
0