You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@spark.apache.org by do...@apache.org on 2019/07/09 15:50:02 UTC
[spark] branch master updated: [SPARK-28029][SQL][TEST] Port
int2.sql
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 <yu...@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 <yu...@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-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org