This is an automated email from the ASF dual-hosted git repository. dongjoon pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/master by this push: new 019efaa [SPARK-28029][SQL][TEST] Port int2.sql 019efaa is described below commit 019efaa3751307c757eb8e5823aa0e79b30082d8 Author: Yuming Wang <yumw...@ebay.com> AuthorDate: Tue Jul 9 08:49:31 2019 -0700 [SPARK-28029][SQL][TEST] Port int2.sql ## What changes were proposed in this pull request? This PR is to port int2.sql from PostgreSQL regression tests. https://github.com/postgres/postgres/blob/REL_12_BETA2/src/test/regress/sql/int2.sql The expected results can be found in the link: https://github.com/postgres/postgres/blob/REL_12_BETA2/src/test/regress/expected/int2.out When porting the test cases, found two PostgreSQL specific features that do not exist in Spark SQL: [SPARK-28023](https://issues.apache.org/jira/browse/SPARK-28023): Trim the string when cast string type to other types [SPARK-28027](https://issues.apache.org/jira/browse/SPARK-28027): Add bitwise shift left/right operators Also, found a bug: [SPARK-28024](https://issues.apache.org/jira/browse/SPARK-28024): Incorrect value when out of range Also, found three inconsistent behavior: [SPARK-27923](https://issues.apache.org/jira/browse/SPARK-27923): Invalid input syntax for smallint throws exception at PostgreSQL [SPARK-28028](https://issues.apache.org/jira/browse/SPARK-28028): Cast numeric to integral type need round [SPARK-2659](https://issues.apache.org/jira/browse/SPARK-2659): HiveQL: Division operator should always perform fractional division, for example: ```sql select 1/2; ``` ## How was this patch tested? N/A Closes #24853 from wangyum/SPARK-28029. Authored-by: Yuming Wang <yumw...@ebay.com> Signed-off-by: Dongjoon Hyun <dh...@apple.com> --- .../test/resources/sql-tests/inputs/pgSQL/int2.sql | 129 ++++++++ .../resources/sql-tests/results/pgSQL/int2.sql.out | 363 +++++++++++++++++++++ 2 files changed, 492 insertions(+) diff --git a/sql/core/src/test/resources/sql-tests/inputs/pgSQL/int2.sql b/sql/core/src/test/resources/sql-tests/inputs/pgSQL/int2.sql new file mode 100644 index 0000000..61f350d --- /dev/null +++ b/sql/core/src/test/resources/sql-tests/inputs/pgSQL/int2.sql @@ -0,0 +1,129 @@ +-- +-- Portions Copyright (c) 1996-2019, PostgreSQL Global Development Group +-- +-- +-- INT2 +-- https://github.com/postgres/postgres/blob/REL_12_BETA2/src/test/regress/sql/int2.sql + +CREATE TABLE INT2_TBL(f1 smallint) USING parquet; + +-- [SPARK-28023] Trim the string when cast string type to other types +INSERT INTO INT2_TBL VALUES (trim('0 ')); + +INSERT INTO INT2_TBL VALUES (trim(' 1234 ')); + +INSERT INTO INT2_TBL VALUES (trim(' -1234')); + +-- [SPARK-27923] Invalid input syntax for type short throws exception at PostgreSQL +-- INSERT INTO INT2_TBL VALUES ('34.5'); + +-- largest and smallest values +INSERT INTO INT2_TBL VALUES ('32767'); + +INSERT INTO INT2_TBL VALUES ('-32767'); + +-- bad input values -- should give errors +-- INSERT INTO INT2_TBL VALUES ('100000'); +-- INSERT INTO INT2_TBL VALUES ('asdf'); +-- INSERT INTO INT2_TBL VALUES (' '); +-- INSERT INTO INT2_TBL VALUES ('- 1234'); +-- INSERT INTO INT2_TBL VALUES ('4 444'); +-- INSERT INTO INT2_TBL VALUES ('123 dt'); +-- INSERT INTO INT2_TBL VALUES (''); + + +SELECT '' AS five, * FROM INT2_TBL; + +SELECT '' AS four, i.* FROM INT2_TBL i WHERE i.f1 <> smallint('0'); + +SELECT '' AS four, i.* FROM INT2_TBL i WHERE i.f1 <> int('0'); + +SELECT '' AS one, i.* FROM INT2_TBL i WHERE i.f1 = smallint('0'); + +SELECT '' AS one, i.* FROM INT2_TBL i WHERE i.f1 = int('0'); + +SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 < smallint('0'); + +SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 < int('0'); + +SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 <= smallint('0'); + +SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 <= int('0'); + +SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 > smallint('0'); + +SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 > int('0'); + +SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 >= smallint('0'); + +SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 >= int('0'); + +-- positive odds +SELECT '' AS one, i.* FROM INT2_TBL i WHERE (i.f1 % smallint('2')) = smallint('1'); + +-- any evens +SELECT '' AS three, i.* FROM INT2_TBL i WHERE (i.f1 % int('2')) = smallint('0'); + +-- [SPARK-28024] Incorrect value when out of range +-- SELECT '' AS five, i.f1, i.f1 * smallint('2') AS x FROM INT2_TBL i; + +SELECT '' AS five, i.f1, i.f1 * smallint('2') AS x FROM INT2_TBL i +WHERE abs(f1) < 16384; + +SELECT '' AS five, i.f1, i.f1 * int('2') AS x FROM INT2_TBL i; + +-- [SPARK-28024] Incorrect value when out of range +-- SELECT '' AS five, i.f1, i.f1 + smallint('2') AS x FROM INT2_TBL i; + +SELECT '' AS five, i.f1, i.f1 + smallint('2') AS x FROM INT2_TBL i +WHERE f1 < 32766; + +SELECT '' AS five, i.f1, i.f1 + int('2') AS x FROM INT2_TBL i; + +-- [SPARK-28024] Incorrect value when out of range +-- SELECT '' AS five, i.f1, i.f1 - smallint('2') AS x FROM INT2_TBL i; + +SELECT '' AS five, i.f1, i.f1 - smallint('2') AS x FROM INT2_TBL i +WHERE f1 > -32767; + +SELECT '' AS five, i.f1, i.f1 - int('2') AS x FROM INT2_TBL i; + +-- PostgreSQL `/` is the same with Spark `div` since SPARK-2659. +SELECT '' AS five, i.f1, i.f1 div smallint('2') AS x FROM INT2_TBL i; + +-- PostgreSQL `/` is the same with Spark `div` since SPARK-2659. +SELECT '' AS five, i.f1, i.f1 div int('2') AS x FROM INT2_TBL i; + +-- corner cases +SELECT string(shiftleft(smallint(-1), 15)); +SELECT string(smallint(shiftleft(smallint(-1), 15))+1); + +-- check sane handling of INT16_MIN overflow cases +-- [SPARK-28024] Incorrect numeric values when out of range +-- SELECT smallint((-32768)) * smallint(-1); +-- SELECT smallint(-32768) / smallint(-1); +SELECT smallint(-32768) % smallint(-1); + +-- [SPARK-28028] Cast numeric to integral type need round +-- check rounding when casting from float +SELECT x, smallint(x) AS int2_value +FROM (VALUES float(-2.5), + float(-1.5), + float(-0.5), + float(0.0), + float(0.5), + float(1.5), + float(2.5)) t(x); + +-- [SPARK-28028] Cast numeric to integral type need round +-- check rounding when casting from numeric +SELECT x, smallint(x) AS int2_value +FROM (VALUES cast(-2.5 as decimal(38, 18)), + cast(-1.5 as decimal(38, 18)), + cast(-0.5 as decimal(38, 18)), + cast(-0.0 as decimal(38, 18)), + cast(0.5 as decimal(38, 18)), + cast(1.5 as decimal(38, 18)), + cast(2.5 as decimal(38, 18))) t(x); + +DROP TABLE INT2_TBL; diff --git a/sql/core/src/test/resources/sql-tests/results/pgSQL/int2.sql.out b/sql/core/src/test/resources/sql-tests/results/pgSQL/int2.sql.out new file mode 100644 index 0000000..6b9246f --- /dev/null +++ b/sql/core/src/test/resources/sql-tests/results/pgSQL/int2.sql.out @@ -0,0 +1,363 @@ +-- Automatically generated by SQLQueryTestSuite +-- Number of queries: 35 + + +-- !query 0 +CREATE TABLE INT2_TBL(f1 smallint) USING parquet +-- !query 0 schema +struct<> +-- !query 0 output + + + +-- !query 1 +INSERT INTO INT2_TBL VALUES (trim('0 ')) +-- !query 1 schema +struct<> +-- !query 1 output + + + +-- !query 2 +INSERT INTO INT2_TBL VALUES (trim(' 1234 ')) +-- !query 2 schema +struct<> +-- !query 2 output + + + +-- !query 3 +INSERT INTO INT2_TBL VALUES (trim(' -1234')) +-- !query 3 schema +struct<> +-- !query 3 output + + + +-- !query 4 +INSERT INTO INT2_TBL VALUES ('32767') +-- !query 4 schema +struct<> +-- !query 4 output + + + +-- !query 5 +INSERT INTO INT2_TBL VALUES ('-32767') +-- !query 5 schema +struct<> +-- !query 5 output + + + +-- !query 6 +SELECT '' AS five, * FROM INT2_TBL +-- !query 6 schema +struct<five:string,f1:smallint> +-- !query 6 output +-1234 + -32767 + 0 + 1234 + 32767 + + +-- !query 7 +SELECT '' AS four, i.* FROM INT2_TBL i WHERE i.f1 <> smallint('0') +-- !query 7 schema +struct<four:string,f1:smallint> +-- !query 7 output +-1234 + -32767 + 1234 + 32767 + + +-- !query 8 +SELECT '' AS four, i.* FROM INT2_TBL i WHERE i.f1 <> int('0') +-- !query 8 schema +struct<four:string,f1:smallint> +-- !query 8 output +-1234 + -32767 + 1234 + 32767 + + +-- !query 9 +SELECT '' AS one, i.* FROM INT2_TBL i WHERE i.f1 = smallint('0') +-- !query 9 schema +struct<one:string,f1:smallint> +-- !query 9 output +0 + + +-- !query 10 +SELECT '' AS one, i.* FROM INT2_TBL i WHERE i.f1 = int('0') +-- !query 10 schema +struct<one:string,f1:smallint> +-- !query 10 output +0 + + +-- !query 11 +SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 < smallint('0') +-- !query 11 schema +struct<two:string,f1:smallint> +-- !query 11 output +-1234 + -32767 + + +-- !query 12 +SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 < int('0') +-- !query 12 schema +struct<two:string,f1:smallint> +-- !query 12 output +-1234 + -32767 + + +-- !query 13 +SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 <= smallint('0') +-- !query 13 schema +struct<three:string,f1:smallint> +-- !query 13 output +-1234 + -32767 + 0 + + +-- !query 14 +SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 <= int('0') +-- !query 14 schema +struct<three:string,f1:smallint> +-- !query 14 output +-1234 + -32767 + 0 + + +-- !query 15 +SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 > smallint('0') +-- !query 15 schema +struct<two:string,f1:smallint> +-- !query 15 output +1234 + 32767 + + +-- !query 16 +SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 > int('0') +-- !query 16 schema +struct<two:string,f1:smallint> +-- !query 16 output +1234 + 32767 + + +-- !query 17 +SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 >= smallint('0') +-- !query 17 schema +struct<three:string,f1:smallint> +-- !query 17 output +0 + 1234 + 32767 + + +-- !query 18 +SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 >= int('0') +-- !query 18 schema +struct<three:string,f1:smallint> +-- !query 18 output +0 + 1234 + 32767 + + +-- !query 19 +SELECT '' AS one, i.* FROM INT2_TBL i WHERE (i.f1 % smallint('2')) = smallint('1') +-- !query 19 schema +struct<one:string,f1:smallint> +-- !query 19 output +32767 + + +-- !query 20 +SELECT '' AS three, i.* FROM INT2_TBL i WHERE (i.f1 % int('2')) = smallint('0') +-- !query 20 schema +struct<three:string,f1:smallint> +-- !query 20 output +-1234 + 0 + 1234 + + +-- !query 21 +SELECT '' AS five, i.f1, i.f1 * smallint('2') AS x FROM INT2_TBL i +WHERE abs(f1) < 16384 +-- !query 21 schema +struct<five:string,f1:smallint,x:smallint> +-- !query 21 output +-1234 -2468 + 0 0 + 1234 2468 + + +-- !query 22 +SELECT '' AS five, i.f1, i.f1 * int('2') AS x FROM INT2_TBL i +-- !query 22 schema +struct<five:string,f1:smallint,x:int> +-- !query 22 output +-1234 -2468 + -32767 -65534 + 0 0 + 1234 2468 + 32767 65534 + + +-- !query 23 +SELECT '' AS five, i.f1, i.f1 + smallint('2') AS x FROM INT2_TBL i +WHERE f1 < 32766 +-- !query 23 schema +struct<five:string,f1:smallint,x:smallint> +-- !query 23 output +-1234 -1232 + -32767 -32765 + 0 2 + 1234 1236 + + +-- !query 24 +SELECT '' AS five, i.f1, i.f1 + int('2') AS x FROM INT2_TBL i +-- !query 24 schema +struct<five:string,f1:smallint,x:int> +-- !query 24 output +-1234 -1232 + -32767 -32765 + 0 2 + 1234 1236 + 32767 32769 + + +-- !query 25 +SELECT '' AS five, i.f1, i.f1 - smallint('2') AS x FROM INT2_TBL i +WHERE f1 > -32767 +-- !query 25 schema +struct<five:string,f1:smallint,x:smallint> +-- !query 25 output +-1234 -1236 + 0 -2 + 1234 1232 + 32767 32765 + + +-- !query 26 +SELECT '' AS five, i.f1, i.f1 - int('2') AS x FROM INT2_TBL i +-- !query 26 schema +struct<five:string,f1:smallint,x:int> +-- !query 26 output +-1234 -1236 + -32767 -32769 + 0 -2 + 1234 1232 + 32767 32765 + + +-- !query 27 +SELECT '' AS five, i.f1, i.f1 div smallint('2') AS x FROM INT2_TBL i +-- !query 27 schema +struct<five:string,f1:smallint,x:smallint> +-- !query 27 output +-1234 -617 + -32767 -16383 + 0 0 + 1234 617 + 32767 16383 + + +-- !query 28 +SELECT '' AS five, i.f1, i.f1 div int('2') AS x FROM INT2_TBL i +-- !query 28 schema +struct<five:string,f1:smallint,x:int> +-- !query 28 output +-1234 -617 + -32767 -16383 + 0 0 + 1234 617 + 32767 16383 + + +-- !query 29 +SELECT string(shiftleft(smallint(-1), 15)) +-- !query 29 schema +struct<CAST(shiftleft(CAST(CAST(-1 AS SMALLINT) AS INT), 15) AS STRING):string> +-- !query 29 output +-32768 + + +-- !query 30 +SELECT string(smallint(shiftleft(smallint(-1), 15))+1) +-- !query 30 schema +struct<CAST((CAST(CAST(shiftleft(CAST(CAST(-1 AS SMALLINT) AS INT), 15) AS SMALLINT) AS INT) + 1) AS STRING):string> +-- !query 30 output +-32767 + + +-- !query 31 +SELECT smallint(-32768) % smallint(-1) +-- !query 31 schema +struct<(CAST(-32768 AS SMALLINT) % CAST(-1 AS SMALLINT)):smallint> +-- !query 31 output +0 + + +-- !query 32 +SELECT x, smallint(x) AS int2_value +FROM (VALUES float(-2.5), + float(-1.5), + float(-0.5), + float(0.0), + float(0.5), + float(1.5), + float(2.5)) t(x) +-- !query 32 schema +struct<x:float,int2_value:smallint> +-- !query 32 output +-0.5 0 +-1.5 -1 +-2.5 -2 +0.0 0 +0.5 0 +1.5 1 +2.5 2 + + +-- !query 33 +SELECT x, smallint(x) AS int2_value +FROM (VALUES cast(-2.5 as decimal(38, 18)), + cast(-1.5 as decimal(38, 18)), + cast(-0.5 as decimal(38, 18)), + cast(-0.0 as decimal(38, 18)), + cast(0.5 as decimal(38, 18)), + cast(1.5 as decimal(38, 18)), + cast(2.5 as decimal(38, 18))) t(x) +-- !query 33 schema +struct<x:decimal(38,18),int2_value:smallint> +-- !query 33 output +-0.5 0 +-1.5 -1 +-2.5 -2 +0 0 +0.5 0 +1.5 1 +2.5 2 + + +-- !query 34 +DROP TABLE INT2_TBL +-- !query 34 schema +struct<> +-- !query 34 output + --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@spark.apache.org For additional commands, e-mail: commits-h...@spark.apache.org