From 47e506fb8fe4dcbf1b70605de0b7fde7e0577c5a Mon Sep 17 00:00:00 2001 From: "Thomas G. Lockhart" Date: Fri, 4 Dec 1998 15:36:51 +0000 Subject: [PATCH] Include test for CASE expression. --- src/test/regress/expected/case.out | 94 ++++++++++++++++++++++++++++++ src/test/regress/sql/case.sql | 78 +++++++++++++++++++++++++ src/test/regress/sql/tests | 1 + 3 files changed, 173 insertions(+) create mode 100644 src/test/regress/expected/case.out create mode 100644 src/test/regress/sql/case.sql diff --git a/src/test/regress/expected/case.out b/src/test/regress/expected/case.out new file mode 100644 index 0000000000..5c714616e9 --- /dev/null +++ b/src/test/regress/expected/case.out @@ -0,0 +1,94 @@ +QUERY: SELECT '' AS "One", + CASE + WHEN 1 < 2 THEN 3 + END AS "One only = 3"; +One|One only = 3 +---+------------ + | 3 +(1 row) + +QUERY: SELECT '' AS "One", + CASE + WHEN 1 > 2 THEN 3 + END AS "One only = Null"; +One|One only = Null +---+--------------- + | +(1 row) + +QUERY: SELECT '' AS "One", + CASE + WHEN 1 < 2 THEN 3 + ELSE 4 + END AS "One with default = 3"; +One|One with default = 3 +---+-------------------- + | 3 +(1 row) + +QUERY: SELECT '' AS "One", + CASE + WHEN 1 > 2 THEN 3 + ELSE 4 + END AS "One with default = 4"; +One|One with default = 4 +---+-------------------- + | 4 +(1 row) + +QUERY: SELECT '' AS "One", + CASE + WHEN 1 > 2 THEN 3 + WHEN 4 < 5 THEN 6 + ELSE 7 + END AS "Two with default = 6"; +One|Two with default = 6 +---+-------------------- + | 6 +(1 row) + +QUERY: SELECT '' AS "Five", + CASE + WHEN f1 >= 0 THEN f1 + END AS ">= 0 or Null" + FROM INT4_TBL; +Five|>= 0 or Null +----+------------ + | 0 + | 123456 + | + | 2147483647 + | +(5 rows) + +QUERY: SELECT '' AS "Five", + CASE WHEN f1 >= 0 THEN (f1 - f1) + ELSE f1 + END AS "Simplest Math" + FROM INT4_TBL; +Five|Simplest Math +----+------------- + | 0 + | 0 + | -123456 + | 0 + | -2147483647 +(5 rows) + +QUERY: SELECT '' AS "Five", f1 AS "Value", + CASE WHEN (f1 < 0) THEN 'small' + WHEN (f1 = 0) THEN 'zero' + WHEN (f1 = 1) THEN 'one' + WHEN (f1 = 2) THEN 'two' + ELSE 'big' + END AS "Category" + FROM INT4_TBL; +Five| Value|Category +----+-----------+-------- + | 0|zero + | 123456|big + | -123456|small + | 2147483647|big + |-2147483647|small +(5 rows) + diff --git a/src/test/regress/sql/case.sql b/src/test/regress/sql/case.sql new file mode 100644 index 0000000000..6d4a6349b2 --- /dev/null +++ b/src/test/regress/sql/case.sql @@ -0,0 +1,78 @@ +-- +-- case.sql +-- +-- Test the case statement + +-- +-- Simplest examples without involving tables +-- + +SELECT '' AS "One", + CASE + WHEN 1 < 2 THEN 3 + END AS "One only = 3"; + +SELECT '' AS "One", + CASE + WHEN 1 > 2 THEN 3 + END AS "One only = Null"; + +SELECT '' AS "One", + CASE + WHEN 1 < 2 THEN 3 + ELSE 4 + END AS "One with default = 3"; + +SELECT '' AS "One", + CASE + WHEN 1 > 2 THEN 3 + ELSE 4 + END AS "One with default = 4"; + +SELECT '' AS "One", + CASE + WHEN 1 > 2 THEN 3 + WHEN 4 < 5 THEN 6 + ELSE 7 + END AS "Two with default = 6"; + +-- +-- Examples of targets involving tables +-- + +SELECT '' AS "Five", + CASE + WHEN f1 >= 0 THEN f1 + END AS ">= 0 or Null" + FROM INT4_TBL; + +SELECT '' AS "Five", + CASE WHEN f1 >= 0 THEN (f1 - f1) + ELSE f1 + END AS "Simplest Math" + FROM INT4_TBL; + +SELECT '' AS "Five", f1 AS "Value", + CASE WHEN (f1 < 0) THEN 'small' + WHEN (f1 = 0) THEN 'zero' + WHEN (f1 = 1) THEN 'one' + WHEN (f1 = 2) THEN 'two' + ELSE 'big' + END AS "Category" + FROM INT4_TBL; + +/* +SELECT '' AS "Five", + CASE WHEN ((f1 < 0) or (i < 0)) THEN 'small' + WHEN ((f1 = 0) or (i = 0)) THEN 'zero' + WHEN ((f1 = 1) or (i = 1)) THEN 'one' + WHEN ((f1 = 2) or (i = 2)) THEN 'two' + ELSE 'big' + END AS "Category" + FROM INT4_TBL; +*/ + +-- +-- Examples of qualifications involving tables +-- + diff --git a/src/test/regress/sql/tests b/src/test/regress/sql/tests index 1780d95be4..3c3d30b949 100644 --- a/src/test/regress/sql/tests +++ b/src/test/regress/sql/tests @@ -49,6 +49,7 @@ select_implicit select_having subselect union +case aggregates transactions random -- 2.40.0