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

Commitca45412

Browse files
author
Thomas G. Lockhart
committed
Add new subselect test.
1 parentca38b10 commitca45412

File tree

3 files changed

+214
-0
lines changed

3 files changed

+214
-0
lines changed
Lines changed: 142 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,142 @@
1+
QUERY: SELECT 1 AS one WHERE 1 IN (SELECT 1);
2+
one
3+
---
4+
1
5+
(1 row)
6+
7+
QUERY: SELECT 1 AS zero WHERE 1 NOT IN (SELECT 1);
8+
zero
9+
----
10+
(0 rows)
11+
12+
QUERY: SELECT 1 AS zero WHERE 1 IN (SELECT 2);
13+
zero
14+
----
15+
(0 rows)
16+
17+
QUERY: CREATE TABLE SUBSELECT_TBL (
18+
f1 integer,
19+
f2 integer,
20+
f3 float
21+
);
22+
QUERY: INSERT INTO SUBSELECT_TBL VALUES (1, 2, 3);
23+
QUERY: INSERT INTO SUBSELECT_TBL VALUES (2, 3, 4);
24+
QUERY: INSERT INTO SUBSELECT_TBL VALUES (3, 4, 5);
25+
QUERY: INSERT INTO SUBSELECT_TBL VALUES (1, 1, 1);
26+
QUERY: INSERT INTO SUBSELECT_TBL VALUES (2, 2, 2);
27+
QUERY: INSERT INTO SUBSELECT_TBL VALUES (3, 3, 3);
28+
QUERY: INSERT INTO SUBSELECT_TBL VALUES (6, 7, 8);
29+
QUERY: INSERT INTO SUBSELECT_TBL VALUES (8, 9, NULL);
30+
QUERY: SELECT '' AS eight, * FROM SUBSELECT_TBL;
31+
eight|f1|f2|f3
32+
-----+--+--+--
33+
| 1| 2| 3
34+
| 2| 3| 4
35+
| 3| 4| 5
36+
| 1| 1| 1
37+
| 2| 2| 2
38+
| 3| 3| 3
39+
| 6| 7| 8
40+
| 8| 9|
41+
(8 rows)
42+
43+
QUERY: SELECT '' AS two, f1 AS "Constant Select" FROM SUBSELECT_TBL
44+
WHERE f1 IN (SELECT 1);
45+
two|Constant Select
46+
---+---------------
47+
| 1
48+
| 1
49+
(2 rows)
50+
51+
QUERY: SELECT '' AS six, f1 AS "Uncorrelated Field" FROM SUBSELECT_TBL
52+
WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL);
53+
six|Uncorrelated Field
54+
---+------------------
55+
| 1
56+
| 2
57+
| 3
58+
| 1
59+
| 2
60+
| 3
61+
(6 rows)
62+
63+
QUERY: SELECT '' AS six, f1 AS "Uncorrelated Field" FROM SUBSELECT_TBL
64+
WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL WHERE
65+
f2 IN (SELECT f1 FROM SUBSELECT_TBL));
66+
six|Uncorrelated Field
67+
---+------------------
68+
| 1
69+
| 2
70+
| 3
71+
| 1
72+
| 2
73+
| 3
74+
(6 rows)
75+
76+
QUERY: SELECT '' AS six, f1 AS "Correlated Field", f3 AS "Second Field"
77+
FROM SUBSELECT_TBL
78+
WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL WHERE f2 = f1);
79+
six|Correlated Field|Second Field
80+
---+----------------+------------
81+
| 1| 3
82+
| 2| 4
83+
| 3| 5
84+
| 1| 1
85+
| 2| 2
86+
| 3| 3
87+
(6 rows)
88+
89+
QUERY: SELECT '' AS six, f1 AS "Correlated Field", f3 AS "Second Field"
90+
FROM SUBSELECT_TBL
91+
WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL WHERE CAST(f2 AS float) = f3);
92+
six|Correlated Field|Second Field
93+
---+----------------+------------
94+
| 1| 3
95+
| 2| 4
96+
| 3| 5
97+
| 1| 1
98+
| 2| 2
99+
| 3| 3
100+
(6 rows)
101+
102+
QUERY: SELECT '' AS six, f1 AS "Correlated Field", f3 AS "Second Field"
103+
FROM SUBSELECT_TBL
104+
WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL WHERE f2 = CAST(f3 AS integer));
105+
ERROR: dtoi4: unable to convert null
106+
QUERY: SELECT '' AS five, f1 AS "Correlated Field"
107+
FROM SUBSELECT_TBL
108+
WHERE (f1, f2) IN (SELECT f2, CAST(f3 AS int4) FROM SUBSELECT_TBL WHERE f3 IS NOT NULL);
109+
five|Correlated Field
110+
----+----------------
111+
| 2
112+
| 3
113+
| 1
114+
| 2
115+
| 3
116+
(5 rows)
117+
118+
QUERY: SELECT '' AS three, f1 AS "Correlated Field"
119+
FROM SUBSELECT_TBL
120+
WHERE (f1, f2) NOT IN (SELECT f2, CAST(f3 AS int4) FROM SUBSELECT_TBL WHERE f3 IS NOT NULL);
121+
three|Correlated Field
122+
-----+----------------
123+
| 1
124+
| 6
125+
| 8
126+
(3 rows)
127+
128+
QUERY: SELECT '' AS eight, ss.f1 AS "Correlated Field", ss.f3 AS "Second Field"
129+
FROM SUBSELECT_TBL ss
130+
WHERE f1 NOT IN (SELECT f1 FROM INT4_TBL WHERE f1 != ss.f1);
131+
eight|Correlated Field|Second Field
132+
-----+----------------+------------
133+
| 1| 3
134+
| 2| 4
135+
| 3| 5
136+
| 1| 1
137+
| 2| 2
138+
| 3| 3
139+
| 6| 8
140+
| 8|
141+
(8 rows)
142+

‎src/test/regress/sql/subselect.sql

Lines changed: 71 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,71 @@
1+
--
2+
-- Subselects
3+
--
4+
5+
SELECT1AS oneWHERE1IN (SELECT1);
6+
7+
SELECT1AS zeroWHERE1 NOTIN (SELECT1);
8+
9+
SELECT1AS zeroWHERE1IN (SELECT2);
10+
11+
-- Set up some simple test tables
12+
13+
CREATETABLESUBSELECT_TBL (
14+
f1integer,
15+
f2integer,
16+
f3 float
17+
);
18+
19+
INSERT INTO SUBSELECT_TBLVALUES (1,2,3);
20+
INSERT INTO SUBSELECT_TBLVALUES (2,3,4);
21+
INSERT INTO SUBSELECT_TBLVALUES (3,4,5);
22+
INSERT INTO SUBSELECT_TBLVALUES (1,1,1);
23+
INSERT INTO SUBSELECT_TBLVALUES (2,2,2);
24+
INSERT INTO SUBSELECT_TBLVALUES (3,3,3);
25+
INSERT INTO SUBSELECT_TBLVALUES (6,7,8);
26+
INSERT INTO SUBSELECT_TBLVALUES (8,9,NULL);
27+
28+
SELECT''AS eight,*FROM SUBSELECT_TBL;
29+
30+
-- Uncorrelated subselects
31+
32+
SELECT''AS two, f1AS"Constant Select"FROM SUBSELECT_TBL
33+
WHERE f1IN (SELECT1);
34+
35+
SELECT''AS six, f1AS"Uncorrelated Field"FROM SUBSELECT_TBL
36+
WHERE f1IN (SELECT f2FROM SUBSELECT_TBL);
37+
38+
SELECT''AS six, f1AS"Uncorrelated Field"FROM SUBSELECT_TBL
39+
WHERE f1IN (SELECT f2FROM SUBSELECT_TBLWHERE
40+
f2IN (SELECT f1FROM SUBSELECT_TBL));
41+
42+
-- Correlated subselects
43+
44+
SELECT''AS six, f1AS"Correlated Field", f3AS"Second Field"
45+
FROM SUBSELECT_TBL
46+
WHERE f1IN (SELECT f2FROM SUBSELECT_TBLWHERE f2= f1);
47+
48+
SELECT''AS six, f1AS"Correlated Field", f3AS"Second Field"
49+
FROM SUBSELECT_TBL
50+
WHERE f1IN (SELECT f2FROM SUBSELECT_TBLWHERE CAST(f2AS float)= f3);
51+
52+
SELECT''AS six, f1AS"Correlated Field", f3AS"Second Field"
53+
FROM SUBSELECT_TBL
54+
WHERE f1IN (SELECT f2FROM SUBSELECT_TBLWHERE f2= CAST(f3ASinteger));
55+
56+
SELECT''AS five, f1AS"Correlated Field"
57+
FROM SUBSELECT_TBL
58+
WHERE (f1, f2)IN (SELECT f2, CAST(f3AS int4)FROM SUBSELECT_TBLWHERE f3IS NOT NULL);
59+
60+
SELECT''AS three, f1AS"Correlated Field"
61+
FROM SUBSELECT_TBL
62+
WHERE (f1, f2) NOTIN (SELECT f2, CAST(f3AS int4)FROM SUBSELECT_TBLWHERE f3IS NOT NULL);
63+
64+
--
65+
-- Use some existing tables in the regression test
66+
--
67+
68+
SELECT''AS eight,ss.f1AS"Correlated Field",ss.f3AS"Second Field"
69+
FROM SUBSELECT_TBL ss
70+
WHERE f1 NOTIN (SELECT f1FROM INT4_TBLWHERE f1!=ss.f1);
71+

‎src/test/regress/sql/tests

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -48,6 +48,7 @@ select
4848
select_into
4949
select_distinct
5050
select_distinct_on
51+
subselect
5152
aggregates
5253
transactions
5354
random

0 commit comments

Comments
 (0)

[8]ページ先頭

©2009-2025 Movatter.jp