wangyum commented on a change in pull request #24782: [SPARK-27934][SQL][TEST] Port case.sql URL: https://github.com/apache/spark/pull/24782#discussion_r292078289
########## File path: sql/core/src/test/resources/sql-tests/inputs/pgSQL/case.sql ########## @@ -0,0 +1,267 @@ +-- +-- Portions Copyright (c) 1996-2019, PostgreSQL Global Development Group +-- +-- +-- CASE +-- https://github.com/postgres/postgres/blob/REL_12_BETA1/src/test/regress/sql/case.sql +-- Test the case statement +-- +-- There are 2 join condition is missing in this test case. we set spark.sql.crossJoin.enabled=true. +set spark.sql.crossJoin.enabled=true; +CREATE TABLE CASE_TBL ( + i integer, + f double +) USING parquet; + +CREATE TABLE CASE2_TBL ( + i integer, + j integer +) USING parquet; + +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`; + +-- [SPARK-27930] Add built-in Math Function: RANDOM +-- 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; + +-- [SPARK-27923] PostgreSQL throws an exception but Spark SQL is NULL +-- 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; + +-- 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 '' AS `Five`, + CASE + WHEN i >= 3 THEN i + END AS `>= 3 or Null` + FROM CASE_TBL; + +SELECT '' AS `Five`, + CASE WHEN i >= 3 THEN (i + i) + ELSE i + END AS `Simplest Math` + FROM CASE_TBL; + +SELECT '' AS `Five`, 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 '' AS `Five`, + 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; + +SELECT '' AS Five, 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 '' AS `Two`, * + FROM CASE_TBL a, CASE2_TBL b + WHERE COALESCE(f,b.i) = 2; + +-- We don't support update now. +-- +-- Examples of updates involving tables +-- + +-- UPDATE CASE_TBL +-- SET i = CASE WHEN i >= 3 THEN (- i) +-- ELSE (2 * i) END; + +-- SELECT * FROM CASE_TBL; + +-- UPDATE CASE_TBL +-- SET i = CASE WHEN i >= 2 THEN (2 * i) +-- ELSE (3 * i) END; + +-- 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; + +-- SELECT * FROM CASE_TBL; + +-- We don't support the features below: +-- 1. CREATE FUNCTION ... returns ... as ... +-- 2. CREATE DOMAIN ... +-- 3. CREATE OPERATOR ... +-- 4. CREATE TYPE ... +-- +-- 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') Review comment: Yes. Add `vol` to `SQLQueryTestSuite`. ---------------------------------------------------------------- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: [email protected] With regards, Apache Git Services --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
