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

Commit4335a37

Browse files
author
Thomas G. Lockhart
committed
Add many new test cases.
1 parentdfdb2e5 commit4335a37

File tree

2 files changed

+278
-80
lines changed

2 files changed

+278
-80
lines changed

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

Lines changed: 160 additions & 53 deletions
Original file line numberDiff line numberDiff line change
@@ -1,94 +1,201 @@
1-
QUERY: SELECT '' AS "One",
1+
QUERY: CREATE TABLE CASE_TBL (
2+
i integer,
3+
f double precision
4+
);
5+
QUERY: CREATE TABLE CASE2_TBL (
6+
i integer,
7+
j integer
8+
);
9+
QUERY: INSERT INTO CASE_TBL VALUES (1, 10.1);
10+
QUERY: INSERT INTO CASE_TBL VALUES (2, 20.2);
11+
QUERY: INSERT INTO CASE_TBL VALUES (3, -30.3);
12+
QUERY: INSERT INTO CASE_TBL VALUES (4, NULL);
13+
QUERY: INSERT INTO CASE2_TBL VALUES (1, -1);
14+
QUERY: INSERT INTO CASE2_TBL VALUES (2, -2);
15+
QUERY: INSERT INTO CASE2_TBL VALUES (3, -3);
16+
QUERY: INSERT INTO CASE2_TBL VALUES (2, -4);
17+
QUERY: INSERT INTO CASE2_TBL VALUES (1, NULL);
18+
QUERY: INSERT INTO CASE2_TBL VALUES (NULL, -6);
19+
QUERY: SELECT '3' AS "One",
220
CASE
321
WHEN 1 < 2 THEN 3
4-
END AS "One only = 3";
5-
One|One only = 3
6-
---+------------
7-
| 3
22+
END AS "Simple WHEN";
23+
One|Simple WHEN
24+
---+-----------
25+
3| 3
826
(1 row)
927

10-
QUERY: SELECT '' AS "One",
28+
QUERY: SELECT '<NULL>' AS "One",
1129
CASE
1230
WHEN 1 > 2 THEN 3
13-
END AS "One only = Null";
14-
One|One only = Null
15-
---+---------------
16-
|
31+
END AS "Simple default";
32+
One |Simple default
33+
------+--------------
34+
<NULL>|
1735
(1 row)
1836

19-
QUERY: SELECT '' AS "One",
37+
QUERY: SELECT '3' AS "One",
2038
CASE
2139
WHEN 1 < 2 THEN 3
2240
ELSE 4
23-
END AS "One with default = 3";
24-
One|One with default = 3
25-
---+--------------------
26-
| 3
41+
END AS "Simple ELSE";
42+
One|Simple ELSE
43+
---+-----------
44+
3| 3
2745
(1 row)
2846

29-
QUERY: SELECT '' AS "One",
47+
QUERY: SELECT '4' AS "One",
3048
CASE
3149
WHEN 1 > 2 THEN 3
3250
ELSE 4
33-
END AS "One withdefault = 4";
34-
One|One withdefault = 4
35-
---+--------------------
36-
| 4
51+
END AS "ELSEdefault";
52+
One|ELSEdefault
53+
---+------------
54+
4| 4
3755
(1 row)
3856

39-
QUERY: SELECT '' AS "One",
57+
QUERY: SELECT '6' AS "One",
4058
CASE
4159
WHEN 1 > 2 THEN 3
4260
WHEN 4 < 5 THEN 6
4361
ELSE 7
44-
END AS "Two with default = 6";
45-
One|Two with default = 6
46-
---+--------------------
47-
| 6
62+
END AS "TwoWHENwith default";
63+
One|TwoWHENwith default
64+
---+---------------------
65+
6| 6
4866
(1 row)
4967

5068
QUERY: SELECT '' AS "Five",
5169
CASE
52-
WHENf1 >= 0 THENf1
70+
WHENi >= 0 THENi
5371
END AS ">= 0 or Null"
54-
FROMINT4_TBL;
72+
FROMCASE_TBL;
5573
Five|>= 0 or Null
5674
----+------------
57-
| 0
58-
| 123456
59-
|
60-
| 2147483647
61-
|
62-
(5 rows)
75+
| 1
76+
| 2
77+
| 3
78+
| 4
79+
(4 rows)
6380

6481
QUERY: SELECT '' AS "Five",
65-
CASE WHENf1 >= 0 THEN (f1 -f1)
66-
ELSEf1
82+
CASE WHENi >= 0 THEN (i -i)
83+
ELSEi
6784
END AS "Simplest Math"
68-
FROMINT4_TBL;
85+
FROMCASE_TBL;
6986
Five|Simplest Math
7087
----+-------------
7188
| 0
7289
| 0
73-
| -123456
7490
| 0
75-
| -2147483647
76-
(5 rows)
91+
| 0
92+
(4 rows)
93+
94+
QUERY: SELECT '' AS "Five", i AS "Value",
95+
CASE WHEN (i < 0) THEN 'small'
96+
WHEN (i = 0) THEN 'zero'
97+
WHEN (i = 1) THEN 'one'
98+
WHEN (i = 2) THEN 'two'
99+
ELSE 'big'
100+
END AS "Category"
101+
FROM CASE_TBL;
102+
Five|Value|Category
103+
----+-----+--------
104+
| 1|one
105+
| 2|two
106+
| 3|big
107+
| 4|big
108+
(4 rows)
77109

78-
QUERY: SELECT '' AS "Five", f1 AS "Value",
79-
CASE WHEN (f1 < 0) THEN 'small'
80-
WHEN (f1 = 0) THEN 'zero'
81-
WHEN (f1 = 1) THEN 'one'
82-
WHEN (f1 = 2) THEN 'two'
110+
QUERY: /*
111+
SELECT '' AS "Five",
112+
CASE WHEN ((i < 0) or (i < 0)) THEN 'small'
113+
WHEN ((i = 0) or (i = 0)) THEN 'zero'
114+
WHEN ((i = 1) or (i = 1)) THEN 'one'
115+
WHEN ((i = 2) or (i = 2)) THEN 'two'
83116
ELSE 'big'
84117
END AS "Category"
85-
FROM INT4_TBL;
86-
Five| Value|Category
87-
----+-----------+--------
88-
| 0|zero
89-
| 123456|big
90-
| -123456|small
91-
| 2147483647|big
92-
|-2147483647|small
93-
(5 rows)
118+
FROM CASE_TBL;
119+
*/
120+
SELECT * FROM CASE_TBL WHERE COALESCE(f,i) = 4;
121+
i|f
122+
-+-
123+
4|
124+
(1 row)
125+
126+
QUERY: SELECT * FROM CASE_TBL WHERE NULLIF(f,i) = 2;
127+
i|f
128+
-+-
129+
(0 rows)
130+
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)
135+
FROM CASE_TBL a, CASE2_TBL b;
136+
*/
137+
SELECT *
138+
FROM CASE_TBL a, CASE2_TBL b
139+
WHERE COALESCE(a.i, a.f, b.i, b.j) = 4;
140+
i|f|i| j
141+
-+-+-+--
142+
4| |1|-1
143+
4| |2|-2
144+
4| |3|-3
145+
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)",
154+
NULLIF(b.i, 4) AS "NULLIF(b.i,4)"
155+
FROM CASE_TBL a, CASE2_TBL b;
156+
*/
157+
SELECT '' AS "Two", *
158+
FROM CASE_TBL a, CASE2_TBL b
159+
WHERE COALESCE(f,b.i) = 2;
160+
Two|i|f|i| j
161+
---+-+-+-+--
162+
|4| |2|-2
163+
|4| |2|-4
164+
(2 rows)
165+
166+
QUERY: UPDATE CASE_TBL
167+
SET i = CASE WHEN i >= 0 THEN (- i)
168+
ELSE (2 * i) END;
169+
QUERY: SELECT * FROM CASE_TBL;
170+
i| f
171+
--+-----
172+
-1| 10.1
173+
-2| 20.2
174+
-3|-30.3
175+
-4|
176+
(4 rows)
177+
178+
QUERY: UPDATE CASE_TBL
179+
SET i = CASE WHEN i >= 2 THEN (2 * i)
180+
ELSE (3 * i) END;
181+
QUERY: SELECT * FROM CASE_TBL;
182+
i| f
183+
---+-----
184+
-3| 10.1
185+
-6| 20.2
186+
-9|-30.3
187+
-12|
188+
(4 rows)
94189

190+
QUERY: /*
191+
This crashes the backend at the moment...
192+
- thomas 1998-12-12
193+
UPDATE CASE_TBL
194+
SET i = CASE WHEN b.i >= 2 THEN (2 * j)
195+
ELSE (3 * j) END
196+
FROM CASE2_TBL b
197+
WHERE j = -CASE_TBL.i;
198+
SELECT * FROM CASE_TBL;
199+
*/
200+
DROP TABLE CASE_TBL;
201+
QUERY: DROP TABLE CASE2_TBL;

0 commit comments

Comments
 (0)

[8]ページ先頭

©2009-2025 Movatter.jp