|
||||
0001 -- 0002 -- Portions Copyright (c) 1996-2019, PostgreSQL Global Development Group 0003 -- 0004 -- 0005 -- CASE 0006 -- https://github.com/postgres/postgres/blob/REL_12_BETA2/src/test/regress/sql/case.sql 0007 -- Test the CASE statement 0008 -- 0009 CREATE TABLE CASE_TBL ( 0010 i integer, 0011 f double 0012 ) USING parquet; 0013 0014 CREATE TABLE CASE2_TBL ( 0015 i integer, 0016 j integer 0017 ) USING parquet; 0018 0019 INSERT INTO CASE_TBL VALUES (1, 10.1); 0020 INSERT INTO CASE_TBL VALUES (2, 20.2); 0021 INSERT INTO CASE_TBL VALUES (3, -30.3); 0022 INSERT INTO CASE_TBL VALUES (4, NULL); 0023 0024 INSERT INTO CASE2_TBL VALUES (1, -1); 0025 INSERT INTO CASE2_TBL VALUES (2, -2); 0026 INSERT INTO CASE2_TBL VALUES (3, -3); 0027 INSERT INTO CASE2_TBL VALUES (2, -4); 0028 INSERT INTO CASE2_TBL VALUES (1, NULL); 0029 INSERT INTO CASE2_TBL VALUES (NULL, -6); 0030 0031 -- 0032 -- Simplest examples without tables 0033 -- 0034 0035 SELECT '3' AS `One`, 0036 CASE 0037 WHEN 1 < 2 THEN 3 0038 END AS `Simple WHEN`; 0039 0040 SELECT '<NULL>' AS `One`, 0041 CASE 0042 WHEN 1 > 2 THEN 3 0043 END AS `Simple default`; 0044 0045 SELECT '3' AS `One`, 0046 CASE 0047 WHEN 1 < 2 THEN 3 0048 ELSE 4 0049 END AS `Simple ELSE`; 0050 0051 SELECT '4' AS `One`, 0052 CASE 0053 WHEN 1 > 2 THEN 3 0054 ELSE 4 0055 END AS `ELSE default`; 0056 0057 SELECT '6' AS `One`, 0058 CASE 0059 WHEN 1 > 2 THEN 3 0060 WHEN 4 < 5 THEN 6 0061 ELSE 7 0062 END AS `Two WHEN with default`; 0063 0064 SELECT '7' AS `None`, 0065 CASE WHEN rand() < 0 THEN 1 0066 END AS `NULL on no matches`; 0067 0068 -- Constant-expression folding shouldn't evaluate unreachable subexpressions 0069 SELECT CASE WHEN 1=0 THEN 1/0 WHEN 1=1 THEN 1 ELSE 2/0 END; 0070 SELECT CASE 1 WHEN 0 THEN 1/0 WHEN 1 THEN 1 ELSE 2/0 END; 0071 0072 -- [SPARK-27923] PostgreSQL throws an exception but Spark SQL is NULL 0073 -- However we do not currently suppress folding of potentially 0074 -- reachable subexpressions 0075 SELECT CASE WHEN i > 100 THEN 1/0 ELSE 0 END FROM case_tbl; 0076 0077 -- Test for cases involving untyped literals in test expression 0078 SELECT CASE 'a' WHEN 'a' THEN 1 ELSE 2 END; 0079 0080 -- 0081 -- Examples of targets involving tables 0082 -- 0083 0084 SELECT '' AS `Five`, 0085 CASE 0086 WHEN i >= 3 THEN i 0087 END AS `>= 3 or Null` 0088 FROM CASE_TBL; 0089 0090 SELECT '' AS `Five`, 0091 CASE WHEN i >= 3 THEN (i + i) 0092 ELSE i 0093 END AS `Simplest Math` 0094 FROM CASE_TBL; 0095 0096 SELECT '' AS `Five`, i AS `Value`, 0097 CASE WHEN (i < 0) THEN 'small' 0098 WHEN (i = 0) THEN 'zero' 0099 WHEN (i = 1) THEN 'one' 0100 WHEN (i = 2) THEN 'two' 0101 ELSE 'big' 0102 END AS `Category` 0103 FROM CASE_TBL; 0104 0105 SELECT '' AS `Five`, 0106 CASE WHEN ((i < 0) or (i < 0)) THEN 'small' 0107 WHEN ((i = 0) or (i = 0)) THEN 'zero' 0108 WHEN ((i = 1) or (i = 1)) THEN 'one' 0109 WHEN ((i = 2) or (i = 2)) THEN 'two' 0110 ELSE 'big' 0111 END AS `Category` 0112 FROM CASE_TBL; 0113 0114 -- 0115 -- Examples of qualifications involving tables 0116 -- 0117 0118 -- 0119 -- NULLIF() and COALESCE() 0120 -- Shorthand forms for typical CASE constructs 0121 -- defined in the SQL standard. 0122 -- 0123 0124 SELECT * FROM CASE_TBL WHERE COALESCE(f,i) = 4; 0125 0126 SELECT * FROM CASE_TBL WHERE NULLIF(f,i) = 2; 0127 0128 SELECT COALESCE(a.f, b.i, b.j) 0129 FROM CASE_TBL a, CASE2_TBL b; 0130 0131 SELECT * 0132 FROM CASE_TBL a, CASE2_TBL b 0133 WHERE COALESCE(a.f, b.i, b.j) = 2; 0134 0135 SELECT '' AS Five, NULLIF(a.i,b.i) AS `NULLIF(a.i,b.i)`, 0136 NULLIF(b.i, 4) AS `NULLIF(b.i,4)` 0137 FROM CASE_TBL a, CASE2_TBL b; 0138 0139 SELECT '' AS `Two`, * 0140 FROM CASE_TBL a, CASE2_TBL b 0141 WHERE COALESCE(f,b.i) = 2; 0142 0143 -- We don't support update now. 0144 -- 0145 -- Examples of updates involving tables 0146 -- 0147 0148 -- UPDATE CASE_TBL 0149 -- SET i = CASE WHEN i >= 3 THEN (- i) 0150 -- ELSE (2 * i) END; 0151 0152 -- SELECT * FROM CASE_TBL; 0153 0154 -- UPDATE CASE_TBL 0155 -- SET i = CASE WHEN i >= 2 THEN (2 * i) 0156 -- ELSE (3 * i) END; 0157 0158 -- SELECT * FROM CASE_TBL; 0159 0160 -- UPDATE CASE_TBL 0161 -- SET i = CASE WHEN b.i >= 2 THEN (2 * j) 0162 -- ELSE (3 * j) END 0163 -- FROM CASE2_TBL b 0164 -- WHERE j = -CASE_TBL.i; 0165 0166 -- SELECT * FROM CASE_TBL; 0167 0168 -- 0169 -- Nested CASE expressions 0170 -- 0171 0172 -- This test exercises a bug caused by aliasing econtext->caseValue_isNull 0173 -- with the isNull argument of the inner CASE's CaseExpr evaluation. After 0174 -- evaluating the vol(null) expression in the inner CASE's second WHEN-clause, 0175 -- the isNull flag for the case test value incorrectly became true, causing 0176 -- the third WHEN-clause not to match. The volatile function calls are needed 0177 -- to prevent constant-folding in the planner, which would hide the bug. 0178 0179 -- Wrap this in a single transaction so the transient '=' operator doesn't 0180 -- cause problems in concurrent sessions 0181 -- BEGIN; 0182 0183 -- CREATE FUNCTION vol(text) returns text as 0184 -- 'begin return $1; end' language plpgsql volatile; 0185 0186 SELECT CASE 0187 (CASE vol('bar') 0188 WHEN 'foo' THEN 'it was foo!' 0189 WHEN vol(null) THEN 'null input' 0190 WHEN 'bar' THEN 'it was bar!' END 0191 ) 0192 WHEN 'it was foo!' THEN 'foo recognized' 0193 WHEN 'it was bar!' THEN 'bar recognized' 0194 ELSE 'unrecognized' END; 0195 0196 -- We don't support the features below: 0197 -- 1. CREATE DOMAIN ... 0198 -- 2. CREATE OPERATOR ... 0199 -- 3. CREATE TYPE ... 0200 0201 -- In this case, we can't inline the SQL function without confusing things. 0202 -- CREATE DOMAIN foodomain AS text; 0203 0204 -- CREATE FUNCTION volfoo(text) returns foodomain as 0205 -- 'begin return $1::foodomain; end' language plpgsql volatile; 0206 0207 -- CREATE FUNCTION inline_eq(foodomain, foodomain) returns boolean as 0208 -- 'SELECT CASE $2::text WHEN $1::text THEN true ELSE false END' language sql; 0209 0210 -- CREATE OPERATOR = (procedure = inline_eq, 0211 -- leftarg = foodomain, rightarg = foodomain); 0212 0213 -- SELECT CASE volfoo('bar') WHEN 'foo'::foodomain THEN 'is foo' ELSE 'is not foo' END; 0214 0215 -- ROLLBACK; 0216 0217 -- Test multiple evaluation of a CASE arg that is a read/write object (#14472) 0218 -- Wrap this in a single transaction so the transient '=' operator doesn't 0219 -- cause problems in concurrent sessions 0220 -- BEGIN; 0221 0222 -- CREATE DOMAIN arrdomain AS int[]; 0223 0224 -- CREATE FUNCTION make_ad(int,int) returns arrdomain as 0225 -- 'declare x arrdomain; 0226 -- begin 0227 -- x := array[$1,$2]; 0228 -- return x; 0229 -- end' language plpgsql volatile; 0230 0231 -- CREATE FUNCTION ad_eq(arrdomain, arrdomain) returns boolean as 0232 -- 'begin return array_eq($1, $2); end' language plpgsql; 0233 0234 -- CREATE OPERATOR = (procedure = ad_eq, 0235 -- leftarg = arrdomain, rightarg = arrdomain); 0236 0237 -- SELECT CASE make_ad(1,2) 0238 -- WHEN array[2,4]::arrdomain THEN 'wrong' 0239 -- WHEN array[2,5]::arrdomain THEN 'still wrong' 0240 -- WHEN array[1,2]::arrdomain THEN 'right' 0241 -- END; 0242 0243 -- ROLLBACK; 0244 0245 -- Test interaction of CASE with ArrayCoerceExpr (bug #15471) 0246 -- BEGIN; 0247 0248 -- CREATE TYPE casetestenum AS ENUM ('e', 'f', 'g'); 0249 0250 -- SELECT 0251 -- CASE 'foo'::text 0252 -- WHEN 'foo' THEN ARRAY['a', 'b', 'c', 'd'] || enum_range(NULL::casetestenum)::text[] 0253 -- ELSE ARRAY['x', 'y'] 0254 -- END; 0255 0256 -- ROLLBACK; 0257 0258 -- 0259 -- Clean up 0260 -- 0261 0262 DROP TABLE CASE_TBL; 0263 DROP TABLE CASE2_TBL;
[ Source navigation ] | [ Diff markup ] | [ Identifier search ] | [ general search ] |
This page was automatically generated by the 2.1.0 LXR engine. The LXR team |