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

Commit4aa0e64

Browse files
author
Thomas G. Lockhart
committed
First tests using JOIN syntax.
1 parent348ab94 commit4aa0e64

File tree

3 files changed

+182
-0
lines changed

3 files changed

+182
-0
lines changed

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

Lines changed: 94 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,94 @@
1+
QUERY: CREATE TABLE JOIN_TBL (
2+
i integer,
3+
j integer,
4+
x text
5+
);
6+
QUERY: CREATE TABLE JOIN2_TBL (
7+
i integer,
8+
k integer
9+
);
10+
QUERY: INSERT INTO JOIN_TBL VALUES (1, 3, 'one');
11+
QUERY: INSERT INTO JOIN_TBL VALUES (2, 2, 'two');
12+
QUERY: INSERT INTO JOIN_TBL VALUES (3, 1, 'three');
13+
QUERY: INSERT INTO JOIN_TBL VALUES (4, 0, 'four');
14+
QUERY: INSERT INTO JOIN2_TBL VALUES (1, -1);
15+
QUERY: INSERT INTO JOIN2_TBL VALUES (2, 2);
16+
QUERY: INSERT INTO JOIN2_TBL VALUES (3, -3);
17+
QUERY: INSERT INTO JOIN2_TBL VALUES (2, 4);
18+
QUERY: SELECT '' AS "xxx", *
19+
FROM JOIN_TBL CROSS JOIN JOIN2_TBL;
20+
xxx|i|j|x |i| k
21+
---+-+-+-----+-+--
22+
|1|3|one |1|-1
23+
|2|2|two |1|-1
24+
|3|1|three|1|-1
25+
|4|0|four |1|-1
26+
|1|3|one |2| 2
27+
|2|2|two |2| 2
28+
|3|1|three|2| 2
29+
|4|0|four |2| 2
30+
|1|3|one |3|-3
31+
|2|2|two |3|-3
32+
|3|1|three|3|-3
33+
|4|0|four |3|-3
34+
|1|3|one |2| 4
35+
|2|2|two |2| 4
36+
|3|1|three|2| 4
37+
|4|0|four |2| 4
38+
(16 rows)
39+
40+
QUERY: SELECT '' AS "xxx", *
41+
FROM JOIN_TBL NATURAL JOIN JOIN2_TBL;
42+
ERROR: JOIN expressions are not yet implemented
43+
QUERY: SELECT '' AS "xxx", *
44+
FROM JOIN_TBL INNER JOIN JOIN2_TBL USING (i);
45+
ERROR: JOIN expressions are not yet implemented
46+
QUERY: SELECT '' AS "xxx", *
47+
FROM JOIN_TBL JOIN JOIN2_TBL ON (JOIN_TBL.i = JOIN2_TBL.i);
48+
ERROR: JOIN expressions are not yet implemented
49+
QUERY: SELECT '' AS "xxx", *
50+
FROM JOIN_TBL JOIN JOIN2_TBL ON (JOIN_TBL.i = JOIN2_TBL.k);
51+
ERROR: JOIN expressions are not yet implemented
52+
QUERY: SELECT '' AS "xxx", *
53+
FROM JOIN_TBL CROSS JOIN JOIN2_TBL;
54+
xxx|i|j|x |i| k
55+
---+-+-+-----+-+--
56+
|1|3|one |1|-1
57+
|2|2|two |1|-1
58+
|3|1|three|1|-1
59+
|4|0|four |1|-1
60+
|1|3|one |2| 2
61+
|2|2|two |2| 2
62+
|3|1|three|2| 2
63+
|4|0|four |2| 2
64+
|1|3|one |3|-3
65+
|2|2|two |3|-3
66+
|3|1|three|3|-3
67+
|4|0|four |3|-3
68+
|1|3|one |2| 4
69+
|2|2|two |2| 4
70+
|3|1|three|2| 4
71+
|4|0|four |2| 4
72+
(16 rows)
73+
74+
QUERY: SELECT '' AS "xxx", *
75+
FROM JOIN_TBL JOIN JOIN2_TBL ON (JOIN_TBL.i <= JOIN2_TBL.k);
76+
ERROR: JOIN expressions are not yet implemented
77+
QUERY: SELECT '' AS "xxx", *
78+
FROM JOIN_TBL OUTER JOIN JOIN2_TBL USING (i);
79+
NOTICE: OUTER JOIN not yet implemented
80+
ERROR: JOIN expressions are not yet implemented
81+
QUERY: SELECT '' AS "xxx", *
82+
FROM JOIN_TBL LEFT OUTER JOIN JOIN2_TBL USING (i);
83+
NOTICE: LEFT OUTER JOIN not yet implemented
84+
ERROR: JOIN expressions are not yet implemented
85+
QUERY: SELECT '' AS "xxx", *
86+
FROM JOIN_TBL RIGHT OUTER JOIN JOIN2_TBL USING (i);
87+
NOTICE: RIGHT OUTER JOIN not yet implemented
88+
ERROR: JOIN expressions are not yet implemented
89+
QUERY: SELECT '' AS "xxx", *
90+
FROM JOIN_TBL FULL OUTER JOIN JOIN2_TBL USING (i);
91+
NOTICE: FULL OUTER JOIN not yet implemented
92+
ERROR: JOIN expressions are not yet implemented
93+
QUERY: DROP TABLE JOIN_TBL;
94+
QUERY: DROP TABLE JOIN2_TBL;

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

Lines changed: 87 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,87 @@
1+
--
2+
-- join.sql
3+
--
4+
-- Test join clauses
5+
--
6+
7+
CREATETABLEJOIN_TBL (
8+
iinteger,
9+
jinteger,
10+
xtext
11+
);
12+
13+
CREATETABLEJOIN2_TBL (
14+
iinteger,
15+
kinteger
16+
);
17+
18+
INSERT INTO JOIN_TBLVALUES (1,3,'one');
19+
INSERT INTO JOIN_TBLVALUES (2,2,'two');
20+
INSERT INTO JOIN_TBLVALUES (3,1,'three');
21+
INSERT INTO JOIN_TBLVALUES (4,0,'four');
22+
23+
INSERT INTO JOIN2_TBLVALUES (1,-1);
24+
INSERT INTO JOIN2_TBLVALUES (2,2);
25+
INSERT INTO JOIN2_TBLVALUES (3,-3);
26+
INSERT INTO JOIN2_TBLVALUES (2,4);
27+
28+
29+
--
30+
-- Inner joins (equi-joins)
31+
--
32+
33+
SELECT''AS"xxx",*
34+
FROM JOIN_TBLCROSS JOIN JOIN2_TBL;
35+
36+
SELECT''AS"xxx",*
37+
FROM JOIN_TBLNATURAL JOIN JOIN2_TBL;
38+
39+
SELECT''AS"xxx",*
40+
FROM JOIN_TBLINNER JOIN JOIN2_TBL USING (i);
41+
42+
SELECT''AS"xxx",*
43+
FROM JOIN_TBLJOIN JOIN2_TBLON (JOIN_TBL.i=JOIN2_TBL.i);
44+
45+
SELECT''AS"xxx",*
46+
FROM JOIN_TBLJOIN JOIN2_TBLON (JOIN_TBL.i=JOIN2_TBL.k);
47+
48+
SELECT''AS"xxx",*
49+
FROM JOIN_TBLCROSS JOIN JOIN2_TBL;
50+
51+
52+
--
53+
-- Non-equi-joins
54+
--
55+
56+
SELECT''AS"xxx",*
57+
FROM JOIN_TBLJOIN JOIN2_TBLON (JOIN_TBL.i<=JOIN2_TBL.k);
58+
59+
60+
--
61+
-- Outer joins
62+
--
63+
64+
SELECT''AS"xxx",*
65+
FROM JOIN_TBL OUTERJOIN JOIN2_TBL USING (i);
66+
67+
SELECT''AS"xxx",*
68+
FROM JOIN_TBLLEFT OUTER JOIN JOIN2_TBL USING (i);
69+
70+
SELECT''AS"xxx",*
71+
FROM JOIN_TBLRIGHT OUTER JOIN JOIN2_TBL USING (i);
72+
73+
SELECT''AS"xxx",*
74+
FROM JOIN_TBL FULL OUTERJOIN JOIN2_TBL USING (i);
75+
76+
77+
--
78+
-- More complicated constructs
79+
--
80+
81+
--
82+
-- Clean up
83+
--
84+
85+
DROPTABLE JOIN_TBL;
86+
DROPTABLE JOIN2_TBL;
87+

‎src/test/regress/sql/tests

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -50,6 +50,7 @@ select_having
5050
subselect
5151
union
5252
case
53+
join
5354
aggregates
5455
transactions
5556
random

0 commit comments

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