Repository: spark Updated Branches: refs/heads/master b8bfce51a -> 774715d5c
[SPARK-22904][SQL] Add tests for decimal operations and string casts ## What changes were proposed in this pull request? Test coverage for arithmetic operations leading to: 1. Precision loss 2. Overflow Moreover, tests for casting bad string to other input types and for using bad string as operators of some functions. ## How was this patch tested? added tests Author: Marco Gaido <marcogaid...@gmail.com> Closes #20084 from mgaido91/SPARK-22904. Project: http://git-wip-us.apache.org/repos/asf/spark/repo Commit: http://git-wip-us.apache.org/repos/asf/spark/commit/774715d5 Tree: http://git-wip-us.apache.org/repos/asf/spark/tree/774715d5 Diff: http://git-wip-us.apache.org/repos/asf/spark/diff/774715d5 Branch: refs/heads/master Commit: 774715d5c73ab6d410208fa1675cd11166e03165 Parents: b8bfce5 Author: Marco Gaido <marcogaid...@gmail.com> Authored: Wed Dec 27 23:53:10 2017 +0800 Committer: gatorsmile <gatorsm...@gmail.com> Committed: Wed Dec 27 23:53:10 2017 +0800 ---------------------------------------------------------------------- .../native/decimalArithmeticOperations.sql | 33 +++ .../native/stringCastAndExpressions.sql | 57 ++++ .../native/decimalArithmeticOperations.sql.out | 82 ++++++ .../native/stringCastAndExpressions.sql.out | 261 +++++++++++++++++++ 4 files changed, 433 insertions(+) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/spark/blob/774715d5/sql/core/src/test/resources/sql-tests/inputs/typeCoercion/native/decimalArithmeticOperations.sql ---------------------------------------------------------------------- diff --git a/sql/core/src/test/resources/sql-tests/inputs/typeCoercion/native/decimalArithmeticOperations.sql b/sql/core/src/test/resources/sql-tests/inputs/typeCoercion/native/decimalArithmeticOperations.sql new file mode 100644 index 0000000..c8e108a --- /dev/null +++ b/sql/core/src/test/resources/sql-tests/inputs/typeCoercion/native/decimalArithmeticOperations.sql @@ -0,0 +1,33 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one or more +-- contributor license agreements. See the NOTICE file distributed with +-- this work for additional information regarding copyright ownership. +-- The ASF licenses this file to You under the Apache License, Version 2.0 +-- (the "License"); you may not use this file except in compliance with +-- the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +CREATE TEMPORARY VIEW t AS SELECT 1.0 as a, 0.0 as b; + +-- division, remainder and pmod by 0 return NULL +select a / b from t; +select a % b from t; +select pmod(a, b) from t; + +-- arithmetic operations causing an overflow return NULL +select (5e36 + 0.1) + 5e36; +select (-4e36 - 0.1) - 7e36; +select 12345678901234567890.0 * 12345678901234567890.0; +select 1e35 / 0.1; + +-- arithmetic operations causing a precision loss return NULL +select 123456789123456789.1234567890 * 1.123456789123456789; +select 0.001 / 9876543210987654321098765432109876543.2 http://git-wip-us.apache.org/repos/asf/spark/blob/774715d5/sql/core/src/test/resources/sql-tests/inputs/typeCoercion/native/stringCastAndExpressions.sql ---------------------------------------------------------------------- diff --git a/sql/core/src/test/resources/sql-tests/inputs/typeCoercion/native/stringCastAndExpressions.sql b/sql/core/src/test/resources/sql-tests/inputs/typeCoercion/native/stringCastAndExpressions.sql new file mode 100644 index 0000000..f17adb5 --- /dev/null +++ b/sql/core/src/test/resources/sql-tests/inputs/typeCoercion/native/stringCastAndExpressions.sql @@ -0,0 +1,57 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one or more +-- contributor license agreements. See the NOTICE file distributed with +-- this work for additional information regarding copyright ownership. +-- The ASF licenses this file to You under the Apache License, Version 2.0 +-- (the "License"); you may not use this file except in compliance with +-- the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +CREATE TEMPORARY VIEW t AS SELECT 'aa' as a; + +-- casting to data types which are unable to represent the string input returns NULL +select cast(a as byte) from t; +select cast(a as short) from t; +select cast(a as int) from t; +select cast(a as long) from t; +select cast(a as float) from t; +select cast(a as double) from t; +select cast(a as decimal) from t; +select cast(a as boolean) from t; +select cast(a as timestamp) from t; +select cast(a as date) from t; +-- casting to binary works correctly +select cast(a as binary) from t; +-- casting to array, struct or map throws exception +select cast(a as array<string>) from t; +select cast(a as struct<s:string>) from t; +select cast(a as map<string, string>) from t; + +-- all timestamp/date expressions return NULL if bad input strings are provided +select to_timestamp(a) from t; +select to_timestamp('2018-01-01', a) from t; +select to_unix_timestamp(a) from t; +select to_unix_timestamp('2018-01-01', a) from t; +select unix_timestamp(a) from t; +select unix_timestamp('2018-01-01', a) from t; +select from_unixtime(a) from t; +select from_unixtime('2018-01-01', a) from t; +select next_day(a, 'MO') from t; +select next_day('2018-01-01', a) from t; +select trunc(a, 'MM') from t; +select trunc('2018-01-01', a) from t; + +-- some functions return NULL if bad input is provided +select unhex('-123'); +select sha2(a, a) from t; +select get_json_object(a, a) from t; +select json_tuple(a, a) from t; +select from_json(a, 'a INT') from t; http://git-wip-us.apache.org/repos/asf/spark/blob/774715d5/sql/core/src/test/resources/sql-tests/results/typeCoercion/native/decimalArithmeticOperations.sql.out ---------------------------------------------------------------------- diff --git a/sql/core/src/test/resources/sql-tests/results/typeCoercion/native/decimalArithmeticOperations.sql.out b/sql/core/src/test/resources/sql-tests/results/typeCoercion/native/decimalArithmeticOperations.sql.out new file mode 100644 index 0000000..ce02f6a --- /dev/null +++ b/sql/core/src/test/resources/sql-tests/results/typeCoercion/native/decimalArithmeticOperations.sql.out @@ -0,0 +1,82 @@ +-- Automatically generated by SQLQueryTestSuite +-- Number of queries: 10 + + +-- !query 0 +CREATE TEMPORARY VIEW t AS SELECT 1.0 as a, 0.0 as b +-- !query 0 schema +struct<> +-- !query 0 output + + + +-- !query 1 +select a / b from t +-- !query 1 schema +struct<(CAST(a AS DECIMAL(2,1)) / CAST(b AS DECIMAL(2,1))):decimal(8,6)> +-- !query 1 output +NULL + + +-- !query 2 +select a % b from t +-- !query 2 schema +struct<(CAST(a AS DECIMAL(2,1)) % CAST(b AS DECIMAL(2,1))):decimal(1,1)> +-- !query 2 output +NULL + + +-- !query 3 +select pmod(a, b) from t +-- !query 3 schema +struct<pmod(CAST(a AS DECIMAL(2,1)), CAST(b AS DECIMAL(2,1))):decimal(1,1)> +-- !query 3 output +NULL + + +-- !query 4 +select (5e36 + 0.1) + 5e36 +-- !query 4 schema +struct<(CAST((CAST(5E+36 AS DECIMAL(38,1)) + CAST(0.1 AS DECIMAL(38,1))) AS DECIMAL(38,1)) + CAST(5E+36 AS DECIMAL(38,1))):decimal(38,1)> +-- !query 4 output +NULL + + +-- !query 5 +select (-4e36 - 0.1) - 7e36 +-- !query 5 schema +struct<(CAST((CAST(-4E+36 AS DECIMAL(38,1)) - CAST(0.1 AS DECIMAL(38,1))) AS DECIMAL(38,1)) - CAST(7E+36 AS DECIMAL(38,1))):decimal(38,1)> +-- !query 5 output +NULL + + +-- !query 6 +select 12345678901234567890.0 * 12345678901234567890.0 +-- !query 6 schema +struct<(12345678901234567890.0 * 12345678901234567890.0):decimal(38,2)> +-- !query 6 output +NULL + + +-- !query 7 +select 1e35 / 0.1 +-- !query 7 schema +struct<(CAST(1E+35 AS DECIMAL(37,1)) / CAST(0.1 AS DECIMAL(37,1))):decimal(38,3)> +-- !query 7 output +NULL + + +-- !query 8 +select 123456789123456789.1234567890 * 1.123456789123456789 +-- !query 8 schema +struct<(CAST(123456789123456789.1234567890 AS DECIMAL(36,18)) * CAST(1.123456789123456789 AS DECIMAL(36,18))):decimal(38,28)> +-- !query 8 output +NULL + + +-- !query 9 +select 0.001 / 9876543210987654321098765432109876543.2 +-- !query 9 schema +struct<(CAST(0.001 AS DECIMAL(38,3)) / CAST(9876543210987654321098765432109876543.2 AS DECIMAL(38,3))):decimal(38,37)> +-- !query 9 output +NULL http://git-wip-us.apache.org/repos/asf/spark/blob/774715d5/sql/core/src/test/resources/sql-tests/results/typeCoercion/native/stringCastAndExpressions.sql.out ---------------------------------------------------------------------- diff --git a/sql/core/src/test/resources/sql-tests/results/typeCoercion/native/stringCastAndExpressions.sql.out b/sql/core/src/test/resources/sql-tests/results/typeCoercion/native/stringCastAndExpressions.sql.out new file mode 100644 index 0000000..8ed2820 --- /dev/null +++ b/sql/core/src/test/resources/sql-tests/results/typeCoercion/native/stringCastAndExpressions.sql.out @@ -0,0 +1,261 @@ +-- Automatically generated by SQLQueryTestSuite +-- Number of queries: 32 + + +-- !query 0 +CREATE TEMPORARY VIEW t AS SELECT 'aa' as a +-- !query 0 schema +struct<> +-- !query 0 output + + + +-- !query 1 +select cast(a as byte) from t +-- !query 1 schema +struct<a:tinyint> +-- !query 1 output +NULL + + +-- !query 2 +select cast(a as short) from t +-- !query 2 schema +struct<a:smallint> +-- !query 2 output +NULL + + +-- !query 3 +select cast(a as int) from t +-- !query 3 schema +struct<a:int> +-- !query 3 output +NULL + + +-- !query 4 +select cast(a as long) from t +-- !query 4 schema +struct<a:bigint> +-- !query 4 output +NULL + + +-- !query 5 +select cast(a as float) from t +-- !query 5 schema +struct<a:float> +-- !query 5 output +NULL + + +-- !query 6 +select cast(a as double) from t +-- !query 6 schema +struct<a:double> +-- !query 6 output +NULL + + +-- !query 7 +select cast(a as decimal) from t +-- !query 7 schema +struct<a:decimal(10,0)> +-- !query 7 output +NULL + + +-- !query 8 +select cast(a as boolean) from t +-- !query 8 schema +struct<a:boolean> +-- !query 8 output +NULL + + +-- !query 9 +select cast(a as timestamp) from t +-- !query 9 schema +struct<a:timestamp> +-- !query 9 output +NULL + + +-- !query 10 +select cast(a as date) from t +-- !query 10 schema +struct<a:date> +-- !query 10 output +NULL + + +-- !query 11 +select cast(a as binary) from t +-- !query 11 schema +struct<a:binary> +-- !query 11 output +aa + + +-- !query 12 +select cast(a as array<string>) from t +-- !query 12 schema +struct<> +-- !query 12 output +org.apache.spark.sql.AnalysisException +cannot resolve 't.`a`' due to data type mismatch: cannot cast string to array<string>; line 1 pos 7 + + +-- !query 13 +select cast(a as struct<s:string>) from t +-- !query 13 schema +struct<> +-- !query 13 output +org.apache.spark.sql.AnalysisException +cannot resolve 't.`a`' due to data type mismatch: cannot cast string to struct<s:string>; line 1 pos 7 + + +-- !query 14 +select cast(a as map<string, string>) from t +-- !query 14 schema +struct<> +-- !query 14 output +org.apache.spark.sql.AnalysisException +cannot resolve 't.`a`' due to data type mismatch: cannot cast string to map<string,string>; line 1 pos 7 + + +-- !query 15 +select to_timestamp(a) from t +-- !query 15 schema +struct<to_timestamp(t.`a`):timestamp> +-- !query 15 output +NULL + + +-- !query 16 +select to_timestamp('2018-01-01', a) from t +-- !query 16 schema +struct<to_timestamp('2018-01-01', t.`a`):timestamp> +-- !query 16 output +NULL + + +-- !query 17 +select to_unix_timestamp(a) from t +-- !query 17 schema +struct<to_unix_timestamp(a, yyyy-MM-dd HH:mm:ss):bigint> +-- !query 17 output +NULL + + +-- !query 18 +select to_unix_timestamp('2018-01-01', a) from t +-- !query 18 schema +struct<to_unix_timestamp(2018-01-01, a):bigint> +-- !query 18 output +NULL + + +-- !query 19 +select unix_timestamp(a) from t +-- !query 19 schema +struct<unix_timestamp(a, yyyy-MM-dd HH:mm:ss):bigint> +-- !query 19 output +NULL + + +-- !query 20 +select unix_timestamp('2018-01-01', a) from t +-- !query 20 schema +struct<unix_timestamp(2018-01-01, a):bigint> +-- !query 20 output +NULL + + +-- !query 21 +select from_unixtime(a) from t +-- !query 21 schema +struct<from_unixtime(CAST(a AS BIGINT), yyyy-MM-dd HH:mm:ss):string> +-- !query 21 output +NULL + + +-- !query 22 +select from_unixtime('2018-01-01', a) from t +-- !query 22 schema +struct<from_unixtime(CAST(2018-01-01 AS BIGINT), a):string> +-- !query 22 output +NULL + + +-- !query 23 +select next_day(a, 'MO') from t +-- !query 23 schema +struct<next_day(CAST(a AS DATE), MO):date> +-- !query 23 output +NULL + + +-- !query 24 +select next_day('2018-01-01', a) from t +-- !query 24 schema +struct<next_day(CAST(2018-01-01 AS DATE), a):date> +-- !query 24 output +NULL + + +-- !query 25 +select trunc(a, 'MM') from t +-- !query 25 schema +struct<trunc(CAST(a AS DATE), MM):date> +-- !query 25 output +NULL + + +-- !query 26 +select trunc('2018-01-01', a) from t +-- !query 26 schema +struct<trunc(CAST(2018-01-01 AS DATE), a):date> +-- !query 26 output +NULL + + +-- !query 27 +select unhex('-123') +-- !query 27 schema +struct<unhex(-123):binary> +-- !query 27 output +NULL + + +-- !query 28 +select sha2(a, a) from t +-- !query 28 schema +struct<sha2(CAST(a AS BINARY), CAST(a AS INT)):string> +-- !query 28 output +NULL + + +-- !query 29 +select get_json_object(a, a) from t +-- !query 29 schema +struct<get_json_object(a, a):string> +-- !query 29 output +NULL + + +-- !query 30 +select json_tuple(a, a) from t +-- !query 30 schema +struct<c0:string> +-- !query 30 output +NULL + + +-- !query 31 +select from_json(a, 'a INT') from t +-- !query 31 schema +struct<jsontostructs(a):struct<a:int>> +-- !query 31 output +NULL --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@spark.apache.org For additional commands, e-mail: commits-h...@spark.apache.org
