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

Commit86bc1da

Browse files
committed
Update regress test for CASE to enable tests involving joins.
1 parentec1f5f7 commit86bc1da

File tree

2 files changed

+105
-65
lines changed

2 files changed

+105
-65
lines changed

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

Lines changed: 99 additions & 45 deletions
Original file line numberDiff line numberDiff line change
@@ -67,28 +67,28 @@ One|Two WHEN with default
6767

6868
QUERY: SELECT '' AS "Five",
6969
CASE
70-
WHEN i >=0 THEN i
71-
END AS ">=0 or Null"
70+
WHEN i >=3 THEN i
71+
END AS ">=3 or Null"
7272
FROM CASE_TBL;
73-
Five|>=0 or Null
73+
Five|>=3 or Null
7474
----+------------
75-
|1
76-
|2
75+
|
76+
|
7777
| 3
7878
| 4
7979
(4 rows)
8080

8181
QUERY: SELECT '' AS "Five",
82-
CASE WHEN i >=0 THEN (i- i)
82+
CASE WHEN i >=3 THEN (i+ i)
8383
ELSE i
8484
END AS "Simplest Math"
8585
FROM CASE_TBL;
8686
Five|Simplest Math
8787
----+-------------
88-
|0
89-
|0
90-
|0
91-
|0
88+
|1
89+
|2
90+
|6
91+
|8
9292
(4 rows)
9393

9494
QUERY: SELECT '' AS "Five", i AS "Value",
@@ -107,17 +107,23 @@ Five|Value|Category
107107
| 4|big
108108
(4 rows)
109109

110-
QUERY: /*
111-
SELECT '' AS "Five",
110+
QUERY: SELECT '' AS "Five",
112111
CASE WHEN ((i < 0) or (i < 0)) THEN 'small'
113112
WHEN ((i = 0) or (i = 0)) THEN 'zero'
114113
WHEN ((i = 1) or (i = 1)) THEN 'one'
115114
WHEN ((i = 2) or (i = 2)) THEN 'two'
116115
ELSE 'big'
117116
END AS "Category"
118117
FROM CASE_TBL;
119-
*/
120-
SELECT * FROM CASE_TBL WHERE COALESCE(f,i) = 4;
118+
Five|Category
119+
----+--------
120+
|one
121+
|two
122+
|big
123+
|big
124+
(4 rows)
125+
126+
QUERY: SELECT * FROM CASE_TBL WHERE COALESCE(f,i) = 4;
121127
i|f
122128
-+-
123129
4|
@@ -128,33 +134,77 @@ i|f
128134
-+-
129135
(0 rows)
130136

131-
QUERY: /*
132-
This crashes the backend at the moment...
133-
- thomas 1998-12-12
134-
SELECT COALESCE(a.i, a.f, b.i, b.j)
137+
QUERY: SELECT COALESCE(a.f, b.i, b.j)
135138
FROM CASE_TBL a, CASE2_TBL b;
136-
*/
137-
SELECT *
139+
case
140+
-----
141+
10.1
142+
20.2
143+
-30.3
144+
1
145+
10.1
146+
20.2
147+
-30.3
148+
2
149+
10.1
150+
20.2
151+
-30.3
152+
3
153+
10.1
154+
20.2
155+
-30.3
156+
2
157+
10.1
158+
20.2
159+
-30.3
160+
1
161+
10.1
162+
20.2
163+
-30.3
164+
-6
165+
(24 rows)
166+
167+
QUERY: SELECT *
138168
FROM CASE_TBL a, CASE2_TBL b
139-
WHERE COALESCE(a.i, a.f, b.i, b.j) =4;
169+
WHERE COALESCE(a.f, b.i, b.j) =2;
140170
i|f|i| j
141171
-+-+-+--
142-
4| |1|-1
143172
4| |2|-2
144-
4| |3|-3
145173
4| |2|-4
146-
4| |1|
147-
4| | |-6
148-
(6 rows)
149-
150-
QUERY: /*
151-
This crashes the backend at the moment...
152-
- thomas 1998-12-12
153-
SELECT '' AS Five, NULLIF(a.i,b.i) AS "NULLIF(a.i,b.i)",
174+
(2 rows)
175+
176+
QUERY: SELECT '' AS Five, NULLIF(a.i,b.i) AS "NULLIF(a.i,b.i)",
154177
NULLIF(b.i, 4) AS "NULLIF(b.i,4)"
155178
FROM CASE_TBL a, CASE2_TBL b;
156-
*/
157-
SELECT '' AS "Two", *
179+
five|NULLIF(a.i,b.i)|NULLIF(b.i,4)
180+
----+---------------+-------------
181+
| | 1
182+
| 2| 1
183+
| 3| 1
184+
| 4| 1
185+
| 1| 2
186+
| | 2
187+
| 3| 2
188+
| 4| 2
189+
| 1| 3
190+
| 2| 3
191+
| | 3
192+
| 4| 3
193+
| 1| 2
194+
| | 2
195+
| 3| 2
196+
| 4| 2
197+
| | 1
198+
| 2| 1
199+
| 3| 1
200+
| 4| 1
201+
| 1|
202+
| 2|
203+
| 3|
204+
| 4|
205+
(24 rows)
206+
207+
QUERY: SELECT '' AS "Two", *
158208
FROM CASE_TBL a, CASE2_TBL b
159209
WHERE COALESCE(f,b.i) = 2;
160210
Two|i|f|i| j
@@ -164,13 +214,13 @@ Two|i|f|i| j
164214
(2 rows)
165215

166216
QUERY: UPDATE CASE_TBL
167-
SET i = CASE WHEN i >=0 THEN (- i)
217+
SET i = CASE WHEN i >=3 THEN (- i)
168218
ELSE (2 * i) END;
169219
QUERY: SELECT * FROM CASE_TBL;
170220
i| f
171221
--+-----
172-
-1| 10.1
173-
-2| 20.2
222+
2| 10.1
223+
4| 20.2
174224
-3|-30.3
175225
-4|
176226
(4 rows)
@@ -181,21 +231,25 @@ QUERY: UPDATE CASE_TBL
181231
QUERY: SELECT * FROM CASE_TBL;
182232
i| f
183233
---+-----
184-
-3| 10.1
185-
-6| 20.2
234+
4| 10.1
235+
8| 20.2
186236
-9|-30.3
187237
-12|
188238
(4 rows)
189239

190-
QUERY: /*
191-
This crashes the backend at the moment...
192-
- thomas 1998-12-12
193-
UPDATE CASE_TBL
240+
QUERY: UPDATE CASE_TBL
194241
SET i = CASE WHEN b.i >= 2 THEN (2 * j)
195242
ELSE (3 * j) END
196243
FROM CASE2_TBL b
197244
WHERE j = -CASE_TBL.i;
198-
SELECT * FROM CASE_TBL;
199-
*/
200-
DROP TABLE CASE_TBL;
245+
QUERY: SELECT * FROM CASE_TBL;
246+
i| f
247+
---+-----
248+
8| 20.2
249+
-9|-30.3
250+
-12|
251+
-8| 10.1
252+
(4 rows)
253+
254+
QUERY: DROP TABLE CASE_TBL;
201255
QUERY: DROP TABLE CASE2_TBL;

‎src/test/regress/sql/case.sql‎

Lines changed: 6 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -65,12 +65,12 @@ SELECT '6' AS "One",
6565

6666
SELECT''AS"Five",
6767
CASE
68-
WHEN i>=0 THEN i
69-
ENDAS">=0 or Null"
68+
WHEN i>=3 THEN i
69+
ENDAS">=3 or Null"
7070
FROM CASE_TBL;
7171

7272
SELECT''AS"Five",
73-
CASE WHEN i>=0 THEN (i- i)
73+
CASE WHEN i>=3 THEN (i+ i)
7474
ELSE i
7575
ENDAS"Simplest Math"
7676
FROM CASE_TBL;
@@ -84,7 +84,6 @@ SELECT '' AS "Five", i AS "Value",
8484
ENDAS"Category"
8585
FROM CASE_TBL;
8686

87-
/*
8887
SELECT''AS"Five",
8988
CASE WHEN ((i<0)or (i<0)) THEN'small'
9089
WHEN ((i=0)or (i=0)) THEN'zero'
@@ -93,7 +92,6 @@ SELECT '' AS "Five",
9392
ELSE'big'
9493
ENDAS"Category"
9594
FROM CASE_TBL;
96-
*/
9795

9896
--
9997
-- Examples of qualifications involving tables
@@ -109,24 +107,16 @@ SELECT * FROM CASE_TBL WHERE COALESCE(f,i) = 4;
109107

110108
SELECT*FROM CASE_TBLWHERE NULLIF(f,i)=2;
111109

112-
/*
113-
This crashes the backend at the moment...
114-
- thomas 1998-12-12
115-
SELECT COALESCE(a.i, a.f, b.i, b.j)
110+
SELECT COALESCE(a.f,b.i,b.j)
116111
FROM CASE_TBL a, CASE2_TBL b;
117-
*/
118112

119113
SELECT*
120114
FROM CASE_TBL a, CASE2_TBL b
121-
WHERE COALESCE(a.i,a.f,b.i,b.j)=4;
115+
WHERE COALESCE(a.f,b.i,b.j)=2;
122116

123-
/*
124-
This crashes the backend at the moment...
125-
- thomas 1998-12-12
126117
SELECT''AS Five, NULLIF(a.i,b.i)AS"NULLIF(a.i,b.i)",
127118
NULLIF(b.i,4)AS"NULLIF(b.i,4)"
128119
FROM CASE_TBL a, CASE2_TBL b;
129-
*/
130120

131121
SELECT''AS"Two",*
132122
FROM CASE_TBL a, CASE2_TBL b
@@ -137,7 +127,7 @@ SELECT '' AS "Two", *
137127
--
138128

139129
UPDATE CASE_TBL
140-
SET i= CASE WHEN i>=0 THEN (- i)
130+
SET i= CASE WHEN i>=3 THEN (- i)
141131
ELSE (2* i) END;
142132

143133
SELECT*FROM CASE_TBL;
@@ -148,17 +138,13 @@ UPDATE CASE_TBL
148138

149139
SELECT*FROM CASE_TBL;
150140

151-
/*
152-
This crashes the backend at the moment...
153-
- thomas 1998-12-12
154141
UPDATE CASE_TBL
155142
SET i= CASE WHENb.i>=2 THEN (2* j)
156143
ELSE (3* j) END
157144
FROM CASE2_TBL b
158145
WHERE j=-CASE_TBL.i;
159146

160147
SELECT*FROM CASE_TBL;
161-
*/
162148

163149
--
164150
-- Clean up

0 commit comments

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