1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
|
--
-- CASE
-- Test the case statement
--
CREATE TABLE CASE_TBL (
i integer,
f double precision
);
CREATE TABLE CASE2_TBL (
i integer,
j integer
);
INSERT INTO CASE_TBL VALUES (1, 10.1);
INSERT INTO CASE_TBL VALUES (2, 20.2);
INSERT INTO CASE_TBL VALUES (3, -30.3);
INSERT INTO CASE_TBL VALUES (4, NULL);
INSERT INTO CASE2_TBL VALUES (1, -1);
INSERT INTO CASE2_TBL VALUES (2, -2);
INSERT INTO CASE2_TBL VALUES (3, -3);
INSERT INTO CASE2_TBL VALUES (2, -4);
INSERT INTO CASE2_TBL VALUES (1, NULL);
INSERT INTO CASE2_TBL VALUES (NULL, -6);
--
-- Simplest examples without tables
--
SELECT '3' AS "One",
CASE
WHEN 1 < 2 THEN 3
END AS "Simple WHEN";
SELECT '<NULL>' AS "One",
CASE
WHEN 1 > 2 THEN 3
END AS "Simple default";
SELECT '3' AS "One",
CASE
WHEN 1 < 2 THEN 3
ELSE 4
END AS "Simple ELSE";
SELECT '4' AS "One",
CASE
WHEN 1 > 2 THEN 3
ELSE 4
END AS "ELSE default";
SELECT '6' AS "One",
CASE
WHEN 1 > 2 THEN 3
WHEN 4 < 5 THEN 6
ELSE 7
END AS "Two WHEN with default";
SELECT '7' AS "None",
CASE WHEN random() < 0 THEN 1
END AS "NULL on no matches";
-- Constant-expression folding shouldn't evaluate unreachable subexpressions
SELECT CASE WHEN 1=0 THEN 1/0 WHEN 1=1 THEN 1 ELSE 2/0 END;
SELECT CASE 1 WHEN 0 THEN 1/0 WHEN 1 THEN 1 ELSE 2/0 END;
-- Test for cases involving untyped literals in test expression
SELECT CASE 'a' WHEN 'a' THEN 1 ELSE 2 END;
--
-- Examples of targets involving tables
--
SELECT
CASE
WHEN i >= 3 THEN i
END AS ">= 3 or Null"
FROM CASE_TBL;
SELECT
CASE WHEN i >= 3 THEN (i + i)
ELSE i
END AS "Simplest Math"
FROM CASE_TBL;
SELECT i AS "Value",
CASE WHEN (i < 0) THEN 'small'
WHEN (i = 0) THEN 'zero'
WHEN (i = 1) THEN 'one'
WHEN (i = 2) THEN 'two'
ELSE 'big'
END AS "Category"
FROM CASE_TBL;
SELECT
CASE WHEN ((i < 0) or (i < 0)) THEN 'small'
WHEN ((i = 0) or (i = 0)) THEN 'zero'
WHEN ((i = 1) or (i = 1)) THEN 'one'
WHEN ((i = 2) or (i = 2)) THEN 'two'
ELSE 'big'
END AS "Category"
FROM CASE_TBL;
--
-- Examples of qualifications involving tables
--
--
-- NULLIF() and COALESCE()
-- Shorthand forms for typical CASE constructs
-- defined in the SQL standard.
--
SELECT * FROM CASE_TBL WHERE COALESCE(f,i) = 4;
SELECT * FROM CASE_TBL WHERE NULLIF(f,i) = 2;
SELECT COALESCE(a.f, b.i, b.j)
FROM CASE_TBL a, CASE2_TBL b;
SELECT NULLIF(a.i,b.i) AS "NULLIF(a.i,b.i)",
NULLIF(b.i, 4) AS "NULLIF(b.i,4)"
FROM CASE_TBL a, CASE2_TBL b;
--
-- Nested CASE expressions
--
-- This test exercises a bug caused by aliasing econtext->caseValue_isNull
-- with the isNull argument of the inner CASE's CaseExpr evaluation. After
-- evaluating the vol(null) expression in the inner CASE's second WHEN-clause,
-- the isNull flag for the case test value incorrectly became true, causing
-- the third WHEN-clause not to match. The volatile function calls are needed
-- to prevent constant-folding in the planner, which would hide the bug.
-- Wrap this in a single transaction so the transient '=' operator doesn't
-- cause problems in concurrent sessions
BEGIN;
ROLLBACK;
-- Test multiple evaluation of a CASE arg that is a read/write object (#14472)
-- Wrap this in a single transaction so the transient '=' operator doesn't
-- cause problems in concurrent sessions
BEGIN;
ROLLBACK;
-- Test interaction of CASE with ArrayCoerceExpr (bug #15471)
BEGIN;
ROLLBACK;
--
-- Clean up
--
DROP TABLE CASE_TBL;
DROP TABLE CASE2_TBL;
|
