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

Commit3097788

Browse files
committed
subselect regress test was kind of silly; it claimed to test correlation
cases but actually did no such thing. Make it test some more cases thanbefore (including things that didn't work in 6.5).
1 parent5c63975 commit3097788

File tree

2 files changed

+63
-41
lines changed

2 files changed

+63
-41
lines changed

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

Lines changed: 44 additions & 28 deletions
Original file line numberDiff line numberDiff line change
@@ -78,40 +78,60 @@ SELECT '' AS six, f1 AS "Uncorrelated Field" FROM SUBSELECT_TBL
7878
| 3
7979
(6 rows)
8080

81-
-- Correlated subselects
82-
SELECT '' AS six, f1 AS "Correlated Field", f3 AS "Second Field"
81+
SELECT '' AS three, f1, f2
8382
FROM SUBSELECT_TBL
84-
WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL WHERE f2 = f1);
83+
WHERE (f1, f2) NOT IN (SELECT f2, CAST(f3 AS int4) FROM SUBSELECT_TBL
84+
WHERE f3 IS NOT NULL);
85+
three | f1 | f2
86+
-------+----+----
87+
| 1 | 2
88+
| 6 | 7
89+
| 8 | 9
90+
(3 rows)
91+
92+
-- Correlated subselects
93+
SELECT '' AS six, f1 AS "Correlated Field", f2 AS "Second Field"
94+
FROM SUBSELECT_TBL upper
95+
WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL WHERE f1 = upper.f1);
8596
six | Correlated Field | Second Field
8697
-----+------------------+--------------
87-
| 1 |3
88-
| 2 |4
89-
| 3 |5
98+
| 1 |2
99+
| 2 |3
100+
| 3 |4
90101
| 1 | 1
91102
| 2 | 2
92103
| 3 | 3
93104
(6 rows)
94105

95106
SELECT '' AS six, f1 AS "Correlated Field", f3 AS "Second Field"
96-
FROM SUBSELECT_TBL
97-
WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL WHERE CAST(f2 AS float) = f3);
107+
FROM SUBSELECT_TBL upper
108+
WHERE f1 IN
109+
(SELECT f2 FROM SUBSELECT_TBL WHERE CAST(upper.f2 AS float) = f3);
98110
six | Correlated Field | Second Field
99111
-----+------------------+--------------
100-
| 1 | 3
101112
| 2 | 4
102113
| 3 | 5
103114
| 1 | 1
104115
| 2 | 2
105116
| 3 | 3
106-
(6 rows)
117+
(5 rows)
107118

108119
SELECT '' AS six, f1 AS "Correlated Field", f3 AS "Second Field"
109-
FROM SUBSELECT_TBL
110-
WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL WHERE f2 = CAST(f3 AS integer));
111-
ERROR: dtoi4: unable to convert null
120+
FROM SUBSELECT_TBL upper
121+
WHERE f3 IN (SELECT upper.f1 + f2 FROM SUBSELECT_TBL
122+
WHERE f2 = CAST(f3 AS integer));
123+
six | Correlated Field | Second Field
124+
-----+------------------+--------------
125+
| 1 | 3
126+
| 2 | 4
127+
| 3 | 5
128+
| 6 | 8
129+
(4 rows)
130+
112131
SELECT '' AS five, f1 AS "Correlated Field"
113132
FROM SUBSELECT_TBL
114-
WHERE (f1, f2) IN (SELECT f2, CAST(f3 AS int4) FROM SUBSELECT_TBL WHERE f3 IS NOT NULL);
133+
WHERE (f1, f2) IN (SELECT f2, CAST(f3 AS int4) FROM SUBSELECT_TBL
134+
WHERE f3 IS NOT NULL);
115135
five | Correlated Field
116136
------+------------------
117137
| 2
@@ -121,31 +141,27 @@ SELECT '' AS five, f1 AS "Correlated Field"
121141
| 3
122142
(5 rows)
123143

124-
SELECT '' AS three, f1 AS "Correlated Field"
125-
FROM SUBSELECT_TBL
126-
WHERE (f1, f2) NOT IN (SELECT f2, CAST(f3 AS int4) FROM SUBSELECT_TBL WHERE f3 IS NOT NULL);
127-
three | Correlated Field
128-
-------+------------------
129-
| 1
130-
| 6
131-
| 8
132-
(3 rows)
133-
134144
--
135145
-- Use some existing tables in the regression test
136146
--
137147
SELECT '' AS eight, ss.f1 AS "Correlated Field", ss.f3 AS "Second Field"
138148
FROM SUBSELECT_TBL ss
139-
WHERE f1 NOT IN (SELECT f1 FROM INT4_TBL WHERE f1 != ss.f1);
149+
WHERE f1 NOT IN (SELECT f1+1 FROM INT4_TBL WHERE f1 != ss.f1);
140150
eight | Correlated Field | Second Field
141151
-------+------------------+--------------
142-
| 1 | 3
143152
| 2 | 4
144153
| 3 | 5
145-
| 1 | 1
146154
| 2 | 2
147155
| 3 | 3
148156
| 6 | 8
149157
| 8 |
150-
(8 rows)
158+
(6 rows)
159+
160+
select q1, float8(count(*)) / (select count(*) from int8_tbl)
161+
from int8_tbl group by q1;
162+
q1 | ?column?
163+
------------------+----------
164+
123 | 0.4
165+
4567890123456789 | 0.6
166+
(2 rows)
151167

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

Lines changed: 19 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -39,33 +39,39 @@ SELECT '' AS six, f1 AS "Uncorrelated Field" FROM SUBSELECT_TBL
3939
WHERE f1IN (SELECT f2FROM SUBSELECT_TBLWHERE
4040
f2IN (SELECT f1FROM SUBSELECT_TBL));
4141

42+
SELECT''AS three, f1, f2
43+
FROM SUBSELECT_TBL
44+
WHERE (f1, f2) NOTIN (SELECT f2, CAST(f3AS int4)FROM SUBSELECT_TBL
45+
WHERE f3IS NOT NULL);
46+
4247
-- Correlated subselects
4348

44-
SELECT''AS six, f1AS"Correlated Field",f3AS"Second Field"
45-
FROM SUBSELECT_TBL
46-
WHERE f1IN (SELECT f2FROM SUBSELECT_TBLWHEREf2= f1);
49+
SELECT''AS six, f1AS"Correlated Field",f2AS"Second Field"
50+
FROM SUBSELECT_TBLupper
51+
WHERE f1IN (SELECT f2FROM SUBSELECT_TBLWHEREf1=upper.f1);
4752

4853
SELECT''AS six, f1AS"Correlated Field", f3AS"Second Field"
49-
FROM SUBSELECT_TBL
50-
WHERE f1IN (SELECT f2FROM SUBSELECT_TBLWHERE CAST(f2AS float)= f3);
54+
FROM SUBSELECT_TBLupper
55+
WHERE f1IN
56+
(SELECT f2FROM SUBSELECT_TBLWHERE CAST(upper.f2AS float)= f3);
5157

5258
SELECT''AS six, f1AS"Correlated Field", f3AS"Second Field"
53-
FROM SUBSELECT_TBL
54-
WHERE f1IN (SELECT f2FROM SUBSELECT_TBLWHERE f2= CAST(f3ASinteger));
59+
FROM SUBSELECT_TBLupper
60+
WHERE f3IN (SELECTupper.f1+ f2FROM SUBSELECT_TBL
61+
WHERE f2= CAST(f3ASinteger));
5562

5663
SELECT''AS five, f1AS"Correlated Field"
5764
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);
65+
WHERE (f1, f2)IN (SELECT f2, CAST(f3AS int4)FROM SUBSELECT_TBL
66+
WHERE f3IS NOT NULL);
6367

6468
--
6569
-- Use some existing tables in the regression test
6670
--
6771

6872
SELECT''AS eight,ss.f1AS"Correlated Field",ss.f3AS"Second Field"
6973
FROM SUBSELECT_TBL ss
70-
WHERE f1 NOTIN (SELECT f1FROM INT4_TBLWHERE f1!=ss.f1);
74+
WHERE f1 NOTIN (SELECT f1+1FROM INT4_TBLWHERE f1!=ss.f1);
7175

76+
select q1, float8(count(*))/ (selectcount(*)from int8_tbl)
77+
from int8_tblgroup by q1;

0 commit comments

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