spark-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From lix...@apache.org
Subject spark git commit: [SPARK-22904][SQL] Add tests for decimal operations and string casts
Date Wed, 27 Dec 2017 15:53:20 GMT
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 <marcogaido91@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 <marcogaido91@gmail.com>
Authored: Wed Dec 27 23:53:10 2017 +0800
Committer: gatorsmile <gatorsmile@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-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org


Mime
View raw message