diff options
Diffstat (limited to 'src/test/regress/expected/case.out')
| -rw-r--r-- | src/test/regress/expected/case.out | 419 |
1 files changed, 419 insertions, 0 deletions
diff --git a/src/test/regress/expected/case.out b/src/test/regress/expected/case.out new file mode 100644 index 0000000..f5136c1 --- /dev/null +++ b/src/test/regress/expected/case.out @@ -0,0 +1,419 @@ +-- +-- 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"; + One | Simple WHEN +-----+------------- + 3 | 3 +(1 row) + +SELECT '<NULL>' AS "One", + CASE + WHEN 1 > 2 THEN 3 + END AS "Simple default"; + One | Simple default +--------+---------------- + <NULL> | +(1 row) + +SELECT '3' AS "One", + CASE + WHEN 1 < 2 THEN 3 + ELSE 4 + END AS "Simple ELSE"; + One | Simple ELSE +-----+------------- + 3 | 3 +(1 row) + +SELECT '4' AS "One", + CASE + WHEN 1 > 2 THEN 3 + ELSE 4 + END AS "ELSE default"; + One | ELSE default +-----+-------------- + 4 | 4 +(1 row) + +SELECT '6' AS "One", + CASE + WHEN 1 > 2 THEN 3 + WHEN 4 < 5 THEN 6 + ELSE 7 + END AS "Two WHEN with default"; + One | Two WHEN with default +-----+----------------------- + 6 | 6 +(1 row) + +SELECT '7' AS "None", + CASE WHEN random() < 0 THEN 1 + END AS "NULL on no matches"; + None | NULL on no matches +------+-------------------- + 7 | +(1 row) + +-- 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; + case +------ + 1 +(1 row) + +SELECT CASE 1 WHEN 0 THEN 1/0 WHEN 1 THEN 1 ELSE 2/0 END; + case +------ + 1 +(1 row) + +-- However we do not currently suppress folding of potentially +-- reachable subexpressions +SELECT CASE WHEN i > 100 THEN 1/0 ELSE 0 END FROM case_tbl; +ERROR: division by zero +-- Test for cases involving untyped literals in test expression +SELECT CASE 'a' WHEN 'a' THEN 1 ELSE 2 END; + case +------ + 1 +(1 row) + +-- +-- Examples of targets involving tables +-- +SELECT + CASE + WHEN i >= 3 THEN i + END AS ">= 3 or Null" + FROM CASE_TBL; + >= 3 or Null +-------------- + + + 3 + 4 +(4 rows) + +SELECT + CASE WHEN i >= 3 THEN (i + i) + ELSE i + END AS "Simplest Math" + FROM CASE_TBL; + Simplest Math +--------------- + 1 + 2 + 6 + 8 +(4 rows) + +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; + Value | Category +-------+---------- + 1 | one + 2 | two + 3 | big + 4 | big +(4 rows) + +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; + Category +---------- + one + two + big + big +(4 rows) + +-- +-- 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; + i | f +---+--- + 4 | +(1 row) + +SELECT * FROM CASE_TBL WHERE NULLIF(f,i) = 2; + i | f +---+--- +(0 rows) + +SELECT COALESCE(a.f, b.i, b.j) + FROM CASE_TBL a, CASE2_TBL b; + coalesce +---------- + 10.1 + 20.2 + -30.3 + 1 + 10.1 + 20.2 + -30.3 + 2 + 10.1 + 20.2 + -30.3 + 3 + 10.1 + 20.2 + -30.3 + 2 + 10.1 + 20.2 + -30.3 + 1 + 10.1 + 20.2 + -30.3 + -6 +(24 rows) + +SELECT * + FROM CASE_TBL a, CASE2_TBL b + WHERE COALESCE(a.f, b.i, b.j) = 2; + i | f | i | j +---+---+---+---- + 4 | | 2 | -2 + 4 | | 2 | -4 +(2 rows) + +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; + NULLIF(a.i,b.i) | NULLIF(b.i,4) +-----------------+--------------- + | 1 + 2 | 1 + 3 | 1 + 4 | 1 + 1 | 2 + | 2 + 3 | 2 + 4 | 2 + 1 | 3 + 2 | 3 + | 3 + 4 | 3 + 1 | 2 + | 2 + 3 | 2 + 4 | 2 + | 1 + 2 | 1 + 3 | 1 + 4 | 1 + 1 | + 2 | + 3 | + 4 | +(24 rows) + +SELECT * + FROM CASE_TBL a, CASE2_TBL b + WHERE COALESCE(f,b.i) = 2; + i | f | i | j +---+---+---+---- + 4 | | 2 | -2 + 4 | | 2 | -4 +(2 rows) + +-- Tests for constant subexpression simplification +explain (costs off) +SELECT * FROM CASE_TBL WHERE NULLIF(1, 2) = 2; + QUERY PLAN +-------------------------- + Result + One-Time Filter: false +(2 rows) + +explain (costs off) +SELECT * FROM CASE_TBL WHERE NULLIF(1, 1) IS NOT NULL; + QUERY PLAN +-------------------------- + Result + One-Time Filter: false +(2 rows) + +explain (costs off) +SELECT * FROM CASE_TBL WHERE NULLIF(1, null) = 2; + QUERY PLAN +-------------------------- + Result + One-Time Filter: false +(2 rows) + +-- +-- Examples of updates involving tables +-- +UPDATE CASE_TBL + SET i = CASE WHEN i >= 3 THEN (- i) + ELSE (2 * i) END; +SELECT * FROM CASE_TBL; + i | f +----+------- + 2 | 10.1 + 4 | 20.2 + -3 | -30.3 + -4 | +(4 rows) + +UPDATE CASE_TBL + SET i = CASE WHEN i >= 2 THEN (2 * i) + ELSE (3 * i) END; +SELECT * FROM CASE_TBL; + i | f +-----+------- + 4 | 10.1 + 8 | 20.2 + -9 | -30.3 + -12 | +(4 rows) + +UPDATE CASE_TBL + SET i = CASE WHEN b.i >= 2 THEN (2 * j) + ELSE (3 * j) END + FROM CASE2_TBL b + WHERE j = -CASE_TBL.i; +SELECT * FROM CASE_TBL; + i | f +-----+------- + 8 | 20.2 + -9 | -30.3 + -12 | + -8 | 10.1 +(4 rows) + +-- +-- 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; +CREATE FUNCTION vol(text) returns text as + 'begin return $1; end' language plpgsql volatile; +SELECT CASE + (CASE vol('bar') + WHEN 'foo' THEN 'it was foo!' + WHEN vol(null) THEN 'null input' + WHEN 'bar' THEN 'it was bar!' END + ) + WHEN 'it was foo!' THEN 'foo recognized' + WHEN 'it was bar!' THEN 'bar recognized' + ELSE 'unrecognized' END; + case +---------------- + bar recognized +(1 row) + +-- In this case, we can't inline the SQL function without confusing things. +CREATE DOMAIN foodomain AS text; +CREATE FUNCTION volfoo(text) returns foodomain as + 'begin return $1::foodomain; end' language plpgsql volatile; +CREATE FUNCTION inline_eq(foodomain, foodomain) returns boolean as + 'SELECT CASE $2::text WHEN $1::text THEN true ELSE false END' language sql; +CREATE OPERATOR = (procedure = inline_eq, + leftarg = foodomain, rightarg = foodomain); +SELECT CASE volfoo('bar') WHEN 'foo'::foodomain THEN 'is foo' ELSE 'is not foo' END; + case +------------ + is not foo +(1 row) + +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; +CREATE DOMAIN arrdomain AS int[]; +CREATE FUNCTION make_ad(int,int) returns arrdomain as + 'declare x arrdomain; + begin + x := array[$1,$2]; + return x; + end' language plpgsql volatile; +CREATE FUNCTION ad_eq(arrdomain, arrdomain) returns boolean as + 'begin return array_eq($1, $2); end' language plpgsql; +CREATE OPERATOR = (procedure = ad_eq, + leftarg = arrdomain, rightarg = arrdomain); +SELECT CASE make_ad(1,2) + WHEN array[2,4]::arrdomain THEN 'wrong' + WHEN array[2,5]::arrdomain THEN 'still wrong' + WHEN array[1,2]::arrdomain THEN 'right' + END; + case +------- + right +(1 row) + +ROLLBACK; +-- Test interaction of CASE with ArrayCoerceExpr (bug #15471) +BEGIN; +CREATE TYPE casetestenum AS ENUM ('e', 'f', 'g'); +SELECT + CASE 'foo'::text + WHEN 'foo' THEN ARRAY['a', 'b', 'c', 'd'] || enum_range(NULL::casetestenum)::text[] + ELSE ARRAY['x', 'y'] + END; + array +----------------- + {a,b,c,d,e,f,g} +(1 row) + +ROLLBACK; +-- +-- Clean up +-- +DROP TABLE CASE_TBL; +DROP TABLE CASE2_TBL; |