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_r292078525
########## 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 Review comment: OK. Rewrite it to `rand()`. ---------------------------------------------------------------- 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]
