--
-- 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 '' 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;
-- 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;
-stdin-:: Fatal: Table metadata loading
-stdin-:: Fatal: ydb/library/yql/providers/yt/gateway/file/yql_yt_file_services.cpp:44: Table not found: plato.case_tbl
-- 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 *
FROM CASE_TBL a, CASE2_TBL b
WHERE COALESCE(a.f, b.i, b.j) = 2;
-stdin-:: Error: Type annotation
-stdin-::1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem
SELECT *
^
-stdin-::1:1: Error: Duplicated member: i
SELECT *
^
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;
SELECT *
FROM CASE_TBL a, CASE2_TBL b
WHERE COALESCE(f,b.i) = 2;
-stdin-:: Error: Type annotation
-stdin-::1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem
SELECT *
^
-stdin-::1:1: Error: Duplicated member: i
SELECT *
^
-- Tests for constant subexpression simplification
explain (costs off)
SELECT * FROM CASE_TBL WHERE NULLIF(1, 2) = 2;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
-- Tests for constant subexpression simplification
^
explain (costs off)
SELECT * FROM CASE_TBL WHERE NULLIF(1, 1) IS NOT NULL;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
explain (costs off)
^
explain (costs off)
SELECT * FROM CASE_TBL WHERE NULLIF(1, null) = 2;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
explain (costs off)
^
--
-- Examples of updates involving tables
--
UPDATE CASE_TBL
SET i = CASE WHEN i >= 3 THEN (- i)
ELSE (2 * i) END;
-stdin-:: Fatal: Pre type annotation
-stdin-:: Fatal: tools/enum_parser/enum_serialization_runtime/enum_runtime.cpp:70: Key 'pg_update' not found in enum NYql::EYtSettingType. Valid options are: 'initial', 'infer_scheme', 'force_infer_schema', 'do_not_fail_on_invalid_schema', 'direct_read', 'view', 'mode', 'scheme', 'weak_concat', 'anonymous', 'with_qb', 'inline', 'sample', 'joinLabel', 'ignore_non_existing', 'warn_non_existing', 'xlock', 'unordered', 'nonUnique', 'userschema', 'usercolumns', 'statcolumns', 'syscolumns', 'ignoretypev3', 'memUsage', 'itemsCount', 'rowFactor', 'ordered', 'keyFilter', 'keyFilter2', 'take', 'skip', 'limit', 'sortLimitBy', 'sortBy', 'reduceBy', 'reduceFilterBy', 'forceTransform', 'weakFields', 'sharded', 'combineChunks', 'jobCount', 'joinReduce', 'firstAsPrimary', 'flow', 'keepSorted', 'keySwitch', 'uniqueBy', 'opHash', 'mapOutputType', 'reduceInputType', 'noDq', 'split', 'compression_codec', 'erasure_codec', 'expiration', 'replication_factor', 'user_attrs', 'media', 'primary_medium', 'keep_meta', 'monotonic_keys', 'mutationid'.
SELECT * FROM CASE_TBL;
UPDATE CASE_TBL
SET i = CASE WHEN i >= 2 THEN (2 * i)
ELSE (3 * i) END;
-stdin-:: Fatal: Pre type annotation
-stdin-:: Fatal: tools/enum_parser/enum_serialization_runtime/enum_runtime.cpp:70: Key 'pg_update' not found in enum NYql::EYtSettingType. Valid options are: 'initial', 'infer_scheme', 'force_infer_schema', 'do_not_fail_on_invalid_schema', 'direct_read', 'view', 'mode', 'scheme', 'weak_concat', 'anonymous', 'with_qb', 'inline', 'sample', 'joinLabel', 'ignore_non_existing', 'warn_non_existing', 'xlock', 'unordered', 'nonUnique', 'userschema', 'usercolumns', 'statcolumns', 'syscolumns', 'ignoretypev3', 'memUsage', 'itemsCount', 'rowFactor', 'ordered', 'keyFilter', 'keyFilter2', 'take', 'skip', 'limit', 'sortLimitBy', 'sortBy', 'reduceBy', 'reduceFilterBy', 'forceTransform', 'weakFields', 'sharded', 'combineChunks', 'jobCount', 'joinReduce', 'firstAsPrimary', 'flow', 'keepSorted', 'keySwitch', 'uniqueBy', 'opHash', 'mapOutputType', 'reduceInputType', 'noDq', 'split', 'compression_codec', 'erasure_codec', 'expiration', 'replication_factor', 'user_attrs', 'media', 'primary_medium', 'keep_meta', 'monotonic_keys', 'mutationid'.
SELECT * FROM CASE_TBL;
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;
-stdin-:: Fatal: Pre type annotation
-stdin-:: Fatal: tools/enum_parser/enum_serialization_runtime/enum_runtime.cpp:70: Key 'pg_update' not found in enum NYql::EYtSettingType. Valid options are: 'initial', 'infer_scheme', 'force_infer_schema', 'do_not_fail_on_invalid_schema', 'direct_read', 'view', 'mode', 'scheme', 'weak_concat', 'anonymous', 'with_qb', 'inline', 'sample', 'joinLabel', 'ignore_non_existing', 'warn_non_existing', 'xlock', 'unordered', 'nonUnique', 'userschema', 'usercolumns', 'statcolumns', 'syscolumns', 'ignoretypev3', 'memUsage', 'itemsCount', 'rowFactor', 'ordered', 'keyFilter', 'keyFilter2', 'take', 'skip', 'limit', 'sortLimitBy', 'sortBy', 'reduceBy', 'reduceFilterBy', 'forceTransform', 'weakFields', 'sharded', 'combineChunks', 'jobCount', 'joinReduce', 'firstAsPrimary', 'flow', 'keepSorted', 'keySwitch', 'uniqueBy', 'opHash', 'mapOutputType', 'reduceInputType', 'noDq', 'split', 'compression_codec', 'erasure_codec', 'expiration', 'replication_factor', 'user_attrs', 'media', 'primary_medium', 'keep_meta', 'monotonic_keys', 'mutationid'.
SELECT * FROM CASE_TBL;
--
-- 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;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 261
CREATE FUNCTION vol(text) returns text as
^
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;
-stdin-:: Error: Type annotation
-stdin-::1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem, At function: PgResultItem
SELECT CASE
^
-stdin-::1:8: Error: At function: If, At function: Or, At function: Coalesce, At function: FromPg, At function: PgOp
SELECT CASE
^
-stdin-::2:4: Error: At function: If, At function: Or
(CASE vol('bar')
^
-stdin-::2:4: Error: At function: Coalesce, At function: FromPg, At function: PgOp
(CASE vol('bar')
^
-stdin-::2:9: Error: At function: PgCall
(CASE vol('bar')
^
-stdin-::2:9: Error: No such proc: vol
(CASE vol('bar')
^
-stdin-::2:4: Error: At function: Coalesce, At function: FromPg, At function: PgOp
(CASE vol('bar')
^
-stdin-::4:10: Error: At function: PgCall
WHEN vol(null) THEN 'null input'
^
-stdin-::4:10: Error: No such proc: vol
WHEN vol(null) THEN 'null input'
^
-- In this case, we can't inline the SQL function without confusing things.
CREATE DOMAIN foodomain AS text;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 272
-- In this case, we can't inline the SQL function without confusing things.
^
CREATE FUNCTION volfoo(text) returns foodomain as
'begin return $1::foodomain; end' language plpgsql volatile;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 261
CREATE FUNCTION volfoo(text) returns foodomain as
^
CREATE FUNCTION inline_eq(foodomain, foodomain) returns boolean as
'SELECT CASE $2::text WHEN $1::text THEN true ELSE false END' language sql;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 261
CREATE FUNCTION inline_eq(foodomain, foodomain) returns boolean as
^
CREATE OPERATOR = (procedure = inline_eq,
leftarg = foodomain, rightarg = foodomain);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 255
CREATE OPERATOR = (procedure = inline_eq,
^
SELECT CASE volfoo('bar') WHEN 'foo'::foodomain THEN 'is foo' ELSE 'is not foo' END;
-stdin-:: Error: Parse Sql
-stdin-::1:39: Error: Unknown type: foodomain
SELECT CASE volfoo('bar') WHEN 'foo'::foodomain THEN 'is foo' ELSE 'is not foo' END;
^
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[];
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 272
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;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 261
CREATE FUNCTION make_ad(int,int) returns arrdomain as
^
CREATE FUNCTION ad_eq(arrdomain, arrdomain) returns boolean as
'begin return array_eq($1, $2); end' language plpgsql;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 261
CREATE FUNCTION ad_eq(arrdomain, arrdomain) returns boolean as
^
CREATE OPERATOR = (procedure = ad_eq,
leftarg = arrdomain, rightarg = arrdomain);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 255
CREATE OPERATOR = (procedure = ad_eq,
^
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;
-stdin-:: Error: Parse Sql
-stdin-::2:20: Error: Unknown type: arrdomain
WHEN array[2,4]::arrdomain THEN 'wrong'
^
ROLLBACK;
-- Test interaction of CASE with ArrayCoerceExpr (bug #15471)
BEGIN;
CREATE TYPE casetestenum AS ENUM ('e', 'f', 'g');
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 315
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;
-stdin-:: Error: Parse Sql
-stdin-::3:67: Error: Unknown type: casetestenum
WHEN 'foo' THEN ARRAY['a', 'b', 'c', 'd'] || enum_range(NULL::casetestenum)::text[]
^
ROLLBACK;
--
-- Clean up
--
DROP TABLE CASE_TBL;
DROP TABLE CASE2_TBL;