This is an automated email from the ASF dual-hosted git repository. lixiao 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 929d313 [SPARK-28059][SQL][TEST] Port int4.sql 929d313 is described below commit 929d3135687226aa7b24edeeb0ff8e62cd087fae Author: Yuming Wang <yumw...@ebay.com> AuthorDate: Sat Jun 22 23:59:30 2019 -0700 [SPARK-28059][SQL][TEST] Port int4.sql ## What changes were proposed in this pull request? This PR is to port int4.sql from PostgreSQL regression tests. https://github.com/postgres/postgres/blob/REL_12_BETA1/src/test/regress/sql/int4.sql The expected results can be found in the link: https://github.com/postgres/postgres/blob/REL_12_BETA1/src/test/regress/expected/int4.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 four inconsistent behavior: [SPARK-27923](https://issues.apache.org/jira/browse/SPARK-27923): Invalid input syntax for integer: "34.5" at PostgreSQL [SPARK-28027](https://issues.apache.org/jira/browse/SPARK-28027) Our operator `!` and `!!` has different meanings [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 #24877 from wangyum/SPARK-28059. Authored-by: Yuming Wang <yumw...@ebay.com> Signed-off-by: gatorsmile <gatorsm...@gmail.com> --- .../test/resources/sql-tests/inputs/pgSQL/int4.sql | 178 +++++++ .../resources/sql-tests/results/pgSQL/int4.sql.out | 530 +++++++++++++++++++++ 2 files changed, 708 insertions(+) diff --git a/sql/core/src/test/resources/sql-tests/inputs/pgSQL/int4.sql b/sql/core/src/test/resources/sql-tests/inputs/pgSQL/int4.sql new file mode 100644 index 0000000..89cac00 --- /dev/null +++ b/sql/core/src/test/resources/sql-tests/inputs/pgSQL/int4.sql @@ -0,0 +1,178 @@ +-- +-- Portions Copyright (c) 1996-2019, PostgreSQL Global Development Group +-- +-- +-- INT4 +-- https://github.com/postgres/postgres/blob/REL_12_BETA1/src/test/regress/sql/int4.sql +-- + +CREATE TABLE INT4_TBL(f1 int) USING parquet; + +-- [SPARK-28023] Trim the string when cast string type to other types +INSERT INTO INT4_TBL VALUES (trim(' 0 ')); + +INSERT INTO INT4_TBL VALUES (trim('123456 ')); + +INSERT INTO INT4_TBL VALUES (trim(' -123456')); + +-- [SPARK-27923] Invalid input syntax for integer: "34.5" at PostgreSQL +-- INSERT INTO INT4_TBL(f1) VALUES ('34.5'); + +-- largest and smallest values +INSERT INTO INT4_TBL VALUES ('2147483647'); + +INSERT INTO INT4_TBL VALUES ('-2147483647'); + +-- [SPARK-27923] Spark SQL insert these bad inputs to NULL +-- bad input values +-- INSERT INTO INT4_TBL(f1) VALUES ('1000000000000'); +-- INSERT INTO INT4_TBL(f1) VALUES ('asdf'); +-- INSERT INTO INT4_TBL(f1) VALUES (' '); +-- INSERT INTO INT4_TBL(f1) VALUES (' asdf '); +-- INSERT INTO INT4_TBL(f1) VALUES ('- 1234'); +-- INSERT INTO INT4_TBL(f1) VALUES ('123 5'); +-- INSERT INTO INT4_TBL(f1) VALUES (''); + + +SELECT '' AS five, * FROM INT4_TBL; + +SELECT '' AS four, i.* FROM INT4_TBL i WHERE i.f1 <> smallint('0'); + +SELECT '' AS four, i.* FROM INT4_TBL i WHERE i.f1 <> int('0'); + +SELECT '' AS one, i.* FROM INT4_TBL i WHERE i.f1 = smallint('0'); + +SELECT '' AS one, i.* FROM INT4_TBL i WHERE i.f1 = int('0'); + +SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 < smallint('0'); + +SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 < int('0'); + +SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 <= smallint('0'); + +SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 <= int('0'); + +SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 > smallint('0'); + +SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 > int('0'); + +SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 >= smallint('0'); + +SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 >= int('0'); + +-- positive odds +SELECT '' AS one, i.* FROM INT4_TBL i WHERE (i.f1 % smallint('2')) = smallint('1'); + +-- any evens +SELECT '' AS three, i.* FROM INT4_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 INT4_TBL i; + +SELECT '' AS five, i.f1, i.f1 * smallint('2') AS x FROM INT4_TBL i +WHERE abs(f1) < 1073741824; + +-- [SPARK-28024] Incorrect value when out of range +SELECT '' AS five, i.f1, i.f1 * int('2') AS x FROM INT4_TBL i; + +SELECT '' AS five, i.f1, i.f1 * int('2') AS x FROM INT4_TBL i +WHERE abs(f1) < 1073741824; + +-- [SPARK-28024] Incorrect value when out of range +SELECT '' AS five, i.f1, i.f1 + smallint('2') AS x FROM INT4_TBL i; + +SELECT '' AS five, i.f1, i.f1 + smallint('2') AS x FROM INT4_TBL i +WHERE f1 < 2147483646; + +-- [SPARK-28024] Incorrect value when out of range +SELECT '' AS five, i.f1, i.f1 + int('2') AS x FROM INT4_TBL i; + +SELECT '' AS five, i.f1, i.f1 + int('2') AS x FROM INT4_TBL i +WHERE f1 < 2147483646; + +-- [SPARK-28024] Incorrect value when out of range +SELECT '' AS five, i.f1, i.f1 - smallint('2') AS x FROM INT4_TBL i; + +SELECT '' AS five, i.f1, i.f1 - smallint('2') AS x FROM INT4_TBL i +WHERE f1 > -2147483647; + +-- [SPARK-28024] Incorrect value when out of range +SELECT '' AS five, i.f1, i.f1 - int('2') AS x FROM INT4_TBL i; + +SELECT '' AS five, i.f1, i.f1 - int('2') AS x FROM INT4_TBL i +WHERE f1 > -2147483647; + +SELECT '' AS five, i.f1, i.f1 / smallint('2') AS x FROM INT4_TBL i; + +SELECT '' AS five, i.f1, i.f1 / int('2') AS x FROM INT4_TBL i; + +-- +-- more complex expressions +-- + +-- variations on unary minus parsing +SELECT -2+3 AS one; + +SELECT 4-2 AS two; + +SELECT 2- -1 AS three; + +SELECT 2 - -2 AS four; + +SELECT smallint('2') * smallint('2') = smallint('16') / smallint('4') AS true; + +SELECT int('2') * smallint('2') = smallint('16') / int('4') AS true; + +SELECT smallint('2') * int('2') = int('16') / smallint('4') AS true; + +SELECT int('1000') < int('999') AS false; + +-- [SPARK-28027] Our ! and !! has different meanings +-- SELECT 4! AS twenty_four; + +-- SELECT !!3 AS six; + +SELECT 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 AS ten; + +-- [SPARK-2659] HiveQL: Division operator should always perform fractional division +SELECT 2 + 2 / 2 AS three; + +SELECT (2 + 2) / 2 AS two; + +-- [SPARK-28027] Add bitwise shift left/right operators +-- corner case +SELECT string(shiftleft(int(-1), 31)); +SELECT string(int(shiftleft(int(-1), 31))+1); + +-- [SPARK-28024] Incorrect numeric values when out of range +-- check sane handling of INT_MIN overflow cases +-- SELECT (-2147483648)::int4 * (-1)::int4; +-- SELECT (-2147483648)::int4 / (-1)::int4; +SELECT int(-2147483648) % int(-1); +-- SELECT (-2147483648)::int4 * (-1)::int2; +-- SELECT (-2147483648)::int4 / (-1)::int2; +SELECT int(-2147483648) % smallint(-1); + +-- [SPARK-28028] Cast numeric to integral type need round +-- check rounding when casting from float +SELECT x, int(x) AS int4_value +FROM (VALUES double(-2.5), + double(-1.5), + double(-0.5), + double(0.0), + double(0.5), + double(1.5), + double(2.5)) t(x); + +-- [SPARK-28028] Cast numeric to integral type need round +-- check rounding when casting from numeric +SELECT x, int(x) AS int4_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 INT4_TBL; diff --git a/sql/core/src/test/resources/sql-tests/results/pgSQL/int4.sql.out b/sql/core/src/test/resources/sql-tests/results/pgSQL/int4.sql.out new file mode 100644 index 0000000..9c17e9a --- /dev/null +++ b/sql/core/src/test/resources/sql-tests/results/pgSQL/int4.sql.out @@ -0,0 +1,530 @@ +-- Automatically generated by SQLQueryTestSuite +-- Number of queries: 53 + + +-- !query 0 +CREATE TABLE INT4_TBL(f1 int) USING parquet +-- !query 0 schema +struct<> +-- !query 0 output + + + +-- !query 1 +INSERT INTO INT4_TBL VALUES (trim(' 0 ')) +-- !query 1 schema +struct<> +-- !query 1 output + + + +-- !query 2 +INSERT INTO INT4_TBL VALUES (trim('123456 ')) +-- !query 2 schema +struct<> +-- !query 2 output + + + +-- !query 3 +INSERT INTO INT4_TBL VALUES (trim(' -123456')) +-- !query 3 schema +struct<> +-- !query 3 output + + + +-- !query 4 +INSERT INTO INT4_TBL VALUES ('2147483647') +-- !query 4 schema +struct<> +-- !query 4 output + + + +-- !query 5 +INSERT INTO INT4_TBL VALUES ('-2147483647') +-- !query 5 schema +struct<> +-- !query 5 output + + + +-- !query 6 +SELECT '' AS five, * FROM INT4_TBL +-- !query 6 schema +struct<five:string,f1:int> +-- !query 6 output +-123456 + -2147483647 + 0 + 123456 + 2147483647 + + +-- !query 7 +SELECT '' AS four, i.* FROM INT4_TBL i WHERE i.f1 <> smallint('0') +-- !query 7 schema +struct<four:string,f1:int> +-- !query 7 output +-123456 + -2147483647 + 123456 + 2147483647 + + +-- !query 8 +SELECT '' AS four, i.* FROM INT4_TBL i WHERE i.f1 <> int('0') +-- !query 8 schema +struct<four:string,f1:int> +-- !query 8 output +-123456 + -2147483647 + 123456 + 2147483647 + + +-- !query 9 +SELECT '' AS one, i.* FROM INT4_TBL i WHERE i.f1 = smallint('0') +-- !query 9 schema +struct<one:string,f1:int> +-- !query 9 output +0 + + +-- !query 10 +SELECT '' AS one, i.* FROM INT4_TBL i WHERE i.f1 = int('0') +-- !query 10 schema +struct<one:string,f1:int> +-- !query 10 output +0 + + +-- !query 11 +SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 < smallint('0') +-- !query 11 schema +struct<two:string,f1:int> +-- !query 11 output +-123456 + -2147483647 + + +-- !query 12 +SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 < int('0') +-- !query 12 schema +struct<two:string,f1:int> +-- !query 12 output +-123456 + -2147483647 + + +-- !query 13 +SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 <= smallint('0') +-- !query 13 schema +struct<three:string,f1:int> +-- !query 13 output +-123456 + -2147483647 + 0 + + +-- !query 14 +SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 <= int('0') +-- !query 14 schema +struct<three:string,f1:int> +-- !query 14 output +-123456 + -2147483647 + 0 + + +-- !query 15 +SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 > smallint('0') +-- !query 15 schema +struct<two:string,f1:int> +-- !query 15 output +123456 + 2147483647 + + +-- !query 16 +SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 > int('0') +-- !query 16 schema +struct<two:string,f1:int> +-- !query 16 output +123456 + 2147483647 + + +-- !query 17 +SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 >= smallint('0') +-- !query 17 schema +struct<three:string,f1:int> +-- !query 17 output +0 + 123456 + 2147483647 + + +-- !query 18 +SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 >= int('0') +-- !query 18 schema +struct<three:string,f1:int> +-- !query 18 output +0 + 123456 + 2147483647 + + +-- !query 19 +SELECT '' AS one, i.* FROM INT4_TBL i WHERE (i.f1 % smallint('2')) = smallint('1') +-- !query 19 schema +struct<one:string,f1:int> +-- !query 19 output +2147483647 + + +-- !query 20 +SELECT '' AS three, i.* FROM INT4_TBL i WHERE (i.f1 % int('2')) = smallint('0') +-- !query 20 schema +struct<three:string,f1:int> +-- !query 20 output +-123456 + 0 + 123456 + + +-- !query 21 +SELECT '' AS five, i.f1, i.f1 * smallint('2') AS x FROM INT4_TBL i +-- !query 21 schema +struct<five:string,f1:int,x:int> +-- !query 21 output +-123456 -246912 + -2147483647 2 + 0 0 + 123456 246912 + 2147483647 -2 + + +-- !query 22 +SELECT '' AS five, i.f1, i.f1 * smallint('2') AS x FROM INT4_TBL i +WHERE abs(f1) < 1073741824 +-- !query 22 schema +struct<five:string,f1:int,x:int> +-- !query 22 output +-123456 -246912 + 0 0 + 123456 246912 + + +-- !query 23 +SELECT '' AS five, i.f1, i.f1 * int('2') AS x FROM INT4_TBL i +-- !query 23 schema +struct<five:string,f1:int,x:int> +-- !query 23 output +-123456 -246912 + -2147483647 2 + 0 0 + 123456 246912 + 2147483647 -2 + + +-- !query 24 +SELECT '' AS five, i.f1, i.f1 * int('2') AS x FROM INT4_TBL i +WHERE abs(f1) < 1073741824 +-- !query 24 schema +struct<five:string,f1:int,x:int> +-- !query 24 output +-123456 -246912 + 0 0 + 123456 246912 + + +-- !query 25 +SELECT '' AS five, i.f1, i.f1 + smallint('2') AS x FROM INT4_TBL i +-- !query 25 schema +struct<five:string,f1:int,x:int> +-- !query 25 output +-123456 -123454 + -2147483647 -2147483645 + 0 2 + 123456 123458 + 2147483647 -2147483647 + + +-- !query 26 +SELECT '' AS five, i.f1, i.f1 + smallint('2') AS x FROM INT4_TBL i +WHERE f1 < 2147483646 +-- !query 26 schema +struct<five:string,f1:int,x:int> +-- !query 26 output +-123456 -123454 + -2147483647 -2147483645 + 0 2 + 123456 123458 + + +-- !query 27 +SELECT '' AS five, i.f1, i.f1 + int('2') AS x FROM INT4_TBL i +-- !query 27 schema +struct<five:string,f1:int,x:int> +-- !query 27 output +-123456 -123454 + -2147483647 -2147483645 + 0 2 + 123456 123458 + 2147483647 -2147483647 + + +-- !query 28 +SELECT '' AS five, i.f1, i.f1 + int('2') AS x FROM INT4_TBL i +WHERE f1 < 2147483646 +-- !query 28 schema +struct<five:string,f1:int,x:int> +-- !query 28 output +-123456 -123454 + -2147483647 -2147483645 + 0 2 + 123456 123458 + + +-- !query 29 +SELECT '' AS five, i.f1, i.f1 - smallint('2') AS x FROM INT4_TBL i +-- !query 29 schema +struct<five:string,f1:int,x:int> +-- !query 29 output +-123456 -123458 + -2147483647 2147483647 + 0 -2 + 123456 123454 + 2147483647 2147483645 + + +-- !query 30 +SELECT '' AS five, i.f1, i.f1 - smallint('2') AS x FROM INT4_TBL i +WHERE f1 > -2147483647 +-- !query 30 schema +struct<five:string,f1:int,x:int> +-- !query 30 output +-123456 -123458 + 0 -2 + 123456 123454 + 2147483647 2147483645 + + +-- !query 31 +SELECT '' AS five, i.f1, i.f1 - int('2') AS x FROM INT4_TBL i +-- !query 31 schema +struct<five:string,f1:int,x:int> +-- !query 31 output +-123456 -123458 + -2147483647 2147483647 + 0 -2 + 123456 123454 + 2147483647 2147483645 + + +-- !query 32 +SELECT '' AS five, i.f1, i.f1 - int('2') AS x FROM INT4_TBL i +WHERE f1 > -2147483647 +-- !query 32 schema +struct<five:string,f1:int,x:int> +-- !query 32 output +-123456 -123458 + 0 -2 + 123456 123454 + 2147483647 2147483645 + + +-- !query 33 +SELECT '' AS five, i.f1, i.f1 / smallint('2') AS x FROM INT4_TBL i +-- !query 33 schema +struct<five:string,f1:int,x:double> +-- !query 33 output +-123456 -61728.0 + -2147483647 -1.0737418235E9 + 0 0.0 + 123456 61728.0 + 2147483647 1.0737418235E9 + + +-- !query 34 +SELECT '' AS five, i.f1, i.f1 / int('2') AS x FROM INT4_TBL i +-- !query 34 schema +struct<five:string,f1:int,x:double> +-- !query 34 output +-123456 -61728.0 + -2147483647 -1.0737418235E9 + 0 0.0 + 123456 61728.0 + 2147483647 1.0737418235E9 + + +-- !query 35 +SELECT -2+3 AS one +-- !query 35 schema +struct<one:int> +-- !query 35 output +1 + + +-- !query 36 +SELECT 4-2 AS two +-- !query 36 schema +struct<two:int> +-- !query 36 output +2 + + +-- !query 37 +SELECT 2- -1 AS three +-- !query 37 schema +struct<three:int> +-- !query 37 output +3 + + +-- !query 38 +SELECT 2 - -2 AS four +-- !query 38 schema +struct<four:int> +-- !query 38 output +4 + + +-- !query 39 +SELECT smallint('2') * smallint('2') = smallint('16') / smallint('4') AS true +-- !query 39 schema +struct<true:boolean> +-- !query 39 output +true + + +-- !query 40 +SELECT int('2') * smallint('2') = smallint('16') / int('4') AS true +-- !query 40 schema +struct<true:boolean> +-- !query 40 output +true + + +-- !query 41 +SELECT smallint('2') * int('2') = int('16') / smallint('4') AS true +-- !query 41 schema +struct<true:boolean> +-- !query 41 output +true + + +-- !query 42 +SELECT int('1000') < int('999') AS false +-- !query 42 schema +struct<false:boolean> +-- !query 42 output +false + + +-- !query 43 +SELECT 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 AS ten +-- !query 43 schema +struct<ten:int> +-- !query 43 output +10 + + +-- !query 44 +SELECT 2 + 2 / 2 AS three +-- !query 44 schema +struct<three:double> +-- !query 44 output +3.0 + + +-- !query 45 +SELECT (2 + 2) / 2 AS two +-- !query 45 schema +struct<two:double> +-- !query 45 output +2.0 + + +-- !query 46 +SELECT string(shiftleft(int(-1), 31)) +-- !query 46 schema +struct<CAST(shiftleft(CAST(-1 AS INT), 31) AS STRING):string> +-- !query 46 output +-2147483648 + + +-- !query 47 +SELECT string(int(shiftleft(int(-1), 31))+1) +-- !query 47 schema +struct<CAST((CAST(shiftleft(CAST(-1 AS INT), 31) AS INT) + 1) AS STRING):string> +-- !query 47 output +-2147483647 + + +-- !query 48 +SELECT int(-2147483648) % int(-1) +-- !query 48 schema +struct<(CAST(-2147483648 AS INT) % CAST(-1 AS INT)):int> +-- !query 48 output +0 + + +-- !query 49 +SELECT int(-2147483648) % smallint(-1) +-- !query 49 schema +struct<(CAST(-2147483648 AS INT) % CAST(CAST(-1 AS SMALLINT) AS INT)):int> +-- !query 49 output +0 + + +-- !query 50 +SELECT x, int(x) AS int4_value +FROM (VALUES double(-2.5), + double(-1.5), + double(-0.5), + double(0.0), + double(0.5), + double(1.5), + double(2.5)) t(x) +-- !query 50 schema +struct<x:double,int4_value:int> +-- !query 50 output +-0.5 0 +-1.5 -1 +-2.5 -2 +0.0 0 +0.5 0 +1.5 1 +2.5 2 + + +-- !query 51 +SELECT x, int(x) AS int4_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 51 schema +struct<x:decimal(38,18),int4_value:int> +-- !query 51 output +-0.5 0 +-1.5 -1 +-2.5 -2 +0 0 +0.5 0 +1.5 1 +2.5 2 + + +-- !query 52 +DROP TABLE INT4_TBL +-- !query 52 schema +struct<> +-- !query 52 output + --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@spark.apache.org For additional commands, e-mail: commits-h...@spark.apache.org