gatorsmile commented on a change in pull request #24782: [SPARK-27934][SQL][TEST] Port case.sql URL: https://github.com/apache/spark/pull/24782#discussion_r292319203
########## File path: sql/core/src/test/resources/sql-tests/results/pgSQL/case.sql.out ########## @@ -0,0 +1,425 @@ +-- Automatically generated by SQLQueryTestSuite +-- Number of queries: 37 + + +-- !query 0 +set spark.sql.crossJoin.enabled=true +-- !query 0 schema +struct<key:string,value:string> +-- !query 0 output +spark.sql.crossJoin.enabled true + + +-- !query 1 +CREATE TABLE CASE_TBL ( + i integer, + f double +) USING parquet +-- !query 1 schema +struct<> +-- !query 1 output + + + +-- !query 2 +CREATE TABLE CASE2_TBL ( + i integer, + j integer +) USING parquet +-- !query 2 schema +struct<> +-- !query 2 output + + + +-- !query 3 +INSERT INTO CASE_TBL VALUES (1, 10.1) +-- !query 3 schema +struct<> +-- !query 3 output + + + +-- !query 4 +INSERT INTO CASE_TBL VALUES (2, 20.2) +-- !query 4 schema +struct<> +-- !query 4 output + + + +-- !query 5 +INSERT INTO CASE_TBL VALUES (3, -30.3) +-- !query 5 schema +struct<> +-- !query 5 output + + + +-- !query 6 +INSERT INTO CASE_TBL VALUES (4, NULL) +-- !query 6 schema +struct<> +-- !query 6 output + + + +-- !query 7 +INSERT INTO CASE2_TBL VALUES (1, -1) +-- !query 7 schema +struct<> +-- !query 7 output + + + +-- !query 8 +INSERT INTO CASE2_TBL VALUES (2, -2) +-- !query 8 schema +struct<> +-- !query 8 output + + + +-- !query 9 +INSERT INTO CASE2_TBL VALUES (3, -3) +-- !query 9 schema +struct<> +-- !query 9 output + + + +-- !query 10 +INSERT INTO CASE2_TBL VALUES (2, -4) +-- !query 10 schema +struct<> +-- !query 10 output + + + +-- !query 11 +INSERT INTO CASE2_TBL VALUES (1, NULL) +-- !query 11 schema +struct<> +-- !query 11 output + + + +-- !query 12 +INSERT INTO CASE2_TBL VALUES (NULL, -6) +-- !query 12 schema +struct<> +-- !query 12 output + + + +-- !query 13 +SELECT '3' AS `One`, + CASE + WHEN 1 < 2 THEN 3 + END AS `Simple WHEN` +-- !query 13 schema +struct<One:string,Simple WHEN:int> +-- !query 13 output +3 3 + + +-- !query 14 +SELECT '<NULL>' AS `One`, + CASE + WHEN 1 > 2 THEN 3 + END AS `Simple default` +-- !query 14 schema +struct<One:string,Simple default:int> +-- !query 14 output +<NULL> NULL + + +-- !query 15 +SELECT '3' AS `One`, + CASE + WHEN 1 < 2 THEN 3 + ELSE 4 + END AS `Simple ELSE` +-- !query 15 schema +struct<One:string,Simple ELSE:int> +-- !query 15 output +3 3 + + +-- !query 16 +SELECT '4' AS `One`, + CASE + WHEN 1 > 2 THEN 3 + ELSE 4 + END AS `ELSE default` +-- !query 16 schema +struct<One:string,ELSE default:int> +-- !query 16 output +4 4 + + +-- !query 17 +SELECT '6' AS `One`, + CASE + WHEN 1 > 2 THEN 3 + WHEN 4 < 5 THEN 6 + ELSE 7 + END AS `Two WHEN with default` +-- !query 17 schema +struct<One:string,Two WHEN with default:int> +-- !query 17 output +6 6 + + +-- !query 18 +SELECT '7' AS `None`, + CASE WHEN rand() < 0 THEN 1 + END AS `NULL on no matches` +-- !query 18 schema +struct<None:string,NULL on no matches:int> +-- !query 18 output +7 NULL + + +-- !query 19 +SELECT CASE WHEN 1=0 THEN 1/0 WHEN 1=1 THEN 1 ELSE 2/0 END +-- !query 19 schema +struct<CASE WHEN (1 = 0) THEN (CAST(1 AS DOUBLE) / CAST(0 AS DOUBLE)) WHEN (1 = 1) THEN CAST(1 AS DOUBLE) ELSE (CAST(2 AS DOUBLE) / CAST(0 AS DOUBLE)) END:double> +-- !query 19 output +1.0 + + +-- !query 20 +SELECT CASE 1 WHEN 0 THEN 1/0 WHEN 1 THEN 1 ELSE 2/0 END +-- !query 20 schema +struct<CASE WHEN (1 = 0) THEN (CAST(1 AS DOUBLE) / CAST(0 AS DOUBLE)) WHEN (1 = 1) THEN CAST(1 AS DOUBLE) ELSE (CAST(2 AS DOUBLE) / CAST(0 AS DOUBLE)) END:double> +-- !query 20 output +1.0 + + +-- !query 21 +SELECT CASE WHEN i > 100 THEN 1/0 ELSE 0 END FROM case_tbl Review comment: Your comment https://github.com/apache/spark/pull/24782/files#r289867687 should be moved to here. ---------------------------------------------------------------- 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]
