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/10 03:33:57 UTC
[spark] branch master updated: [SPARK-28136][SQL][TEST] Port
int8.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 1b23267 [SPARK-28136][SQL][TEST] Port int8.sql
1b23267 is described below
commit 1b232671a8210ed7de5e7e3ce5aecc140db847f2
Author: Yuming Wang <yu...@ebay.com>
AuthorDate: Tue Jul 9 20:33:35 2019 -0700
[SPARK-28136][SQL][TEST] Port int8.sql
## What changes were proposed in this pull request?
This PR is to port int8.sql from PostgreSQL regression tests. https://github.com/postgres/postgres/blob/REL_12_BETA2/src/test/regress/sql/int8.sql
The expected results can be found in the link: https://github.com/postgres/postgres/blob/REL_12_BETA2/src/test/regress/expected/int8.out
When porting the test cases, found two PostgreSQL specific features that do not exist in Spark SQL:
[SPARK-28137](https://issues.apache.org/jira/browse/SPARK-28137): Missing Data Type Formatting Functions
[SPARK-28027](https://issues.apache.org/jira/browse/SPARK-28027): Missing some mathematical operators
Also, found three inconsistent behavior:
[SPARK-26218](https://issues.apache.org/jira/browse/SPARK-28024): Throw exception on overflow for integers
[SPARK-27923](https://issues.apache.org/jira/browse/SPARK-27923): Spark SQL insert bad inputs to NULL
[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 #24933 from wangyum/SPARK-28136.
Authored-by: Yuming Wang <yu...@ebay.com>
Signed-off-by: Dongjoon Hyun <dh...@apple.com>
---
.../test/resources/sql-tests/inputs/pgSQL/int8.sql | 239 ++++++
.../resources/sql-tests/results/pgSQL/int8.sql.out | 855 +++++++++++++++++++++
2 files changed, 1094 insertions(+)
diff --git a/sql/core/src/test/resources/sql-tests/inputs/pgSQL/int8.sql b/sql/core/src/test/resources/sql-tests/inputs/pgSQL/int8.sql
new file mode 100644
index 0000000..31eef6f
--- /dev/null
+++ b/sql/core/src/test/resources/sql-tests/inputs/pgSQL/int8.sql
@@ -0,0 +1,239 @@
+--
+-- Portions Copyright (c) 1996-2019, PostgreSQL Global Development Group
+--
+--
+-- INT8
+-- Test int8 64-bit integers.
+-- https://github.com/postgres/postgres/blob/REL_12_BETA2/src/test/regress/sql/int8.sql
+--
+CREATE TABLE INT8_TBL(q1 bigint, q2 bigint) USING parquet;
+
+INSERT INTO INT8_TBL VALUES(trim(' 123 '),trim(' 456'));
+INSERT INTO INT8_TBL VALUES(trim('123 '),'4567890123456789');
+INSERT INTO INT8_TBL VALUES('4567890123456789','123');
+INSERT INTO INT8_TBL VALUES(+4567890123456789,'4567890123456789');
+INSERT INTO INT8_TBL VALUES('+4567890123456789','-4567890123456789');
+
+-- [SPARK-27923] Spark SQL insert there bad inputs to NULL
+-- bad inputs
+-- INSERT INTO INT8_TBL(q1) VALUES (' ');
+-- INSERT INTO INT8_TBL(q1) VALUES ('xxx');
+-- INSERT INTO INT8_TBL(q1) VALUES ('3908203590239580293850293850329485');
+-- INSERT INTO INT8_TBL(q1) VALUES ('-1204982019841029840928340329840934');
+-- INSERT INTO INT8_TBL(q1) VALUES ('- 123');
+-- INSERT INTO INT8_TBL(q1) VALUES (' 345 5');
+-- INSERT INTO INT8_TBL(q1) VALUES ('');
+
+SELECT * FROM INT8_TBL;
+
+-- int8/int8 cmp
+SELECT * FROM INT8_TBL WHERE q2 = 4567890123456789;
+SELECT * FROM INT8_TBL WHERE q2 <> 4567890123456789;
+SELECT * FROM INT8_TBL WHERE q2 < 4567890123456789;
+SELECT * FROM INT8_TBL WHERE q2 > 4567890123456789;
+SELECT * FROM INT8_TBL WHERE q2 <= 4567890123456789;
+SELECT * FROM INT8_TBL WHERE q2 >= 4567890123456789;
+
+-- int8/int4 cmp
+SELECT * FROM INT8_TBL WHERE q2 = 456;
+SELECT * FROM INT8_TBL WHERE q2 <> 456;
+SELECT * FROM INT8_TBL WHERE q2 < 456;
+SELECT * FROM INT8_TBL WHERE q2 > 456;
+SELECT * FROM INT8_TBL WHERE q2 <= 456;
+SELECT * FROM INT8_TBL WHERE q2 >= 456;
+
+-- int4/int8 cmp
+SELECT * FROM INT8_TBL WHERE 123 = q1;
+SELECT * FROM INT8_TBL WHERE 123 <> q1;
+SELECT * FROM INT8_TBL WHERE 123 < q1;
+SELECT * FROM INT8_TBL WHERE 123 > q1;
+SELECT * FROM INT8_TBL WHERE 123 <= q1;
+SELECT * FROM INT8_TBL WHERE 123 >= q1;
+
+-- int8/int2 cmp
+SELECT * FROM INT8_TBL WHERE q2 = smallint('456');
+SELECT * FROM INT8_TBL WHERE q2 <> smallint('456');
+SELECT * FROM INT8_TBL WHERE q2 < smallint('456');
+SELECT * FROM INT8_TBL WHERE q2 > smallint('456');
+SELECT * FROM INT8_TBL WHERE q2 <= smallint('456');
+SELECT * FROM INT8_TBL WHERE q2 >= smallint('456');
+
+-- int2/int8 cmp
+SELECT * FROM INT8_TBL WHERE smallint('123') = q1;
+SELECT * FROM INT8_TBL WHERE smallint('123') <> q1;
+SELECT * FROM INT8_TBL WHERE smallint('123') < q1;
+SELECT * FROM INT8_TBL WHERE smallint('123') > q1;
+SELECT * FROM INT8_TBL WHERE smallint('123') <= q1;
+SELECT * FROM INT8_TBL WHERE smallint('123') >= q1;
+
+
+SELECT '' AS five, q1 AS plus, -q1 AS minus FROM INT8_TBL;
+
+SELECT '' AS five, q1, q2, q1 + q2 AS plus FROM INT8_TBL;
+SELECT '' AS five, q1, q2, q1 - q2 AS minus FROM INT8_TBL;
+SELECT '' AS three, q1, q2, q1 * q2 AS multiply FROM INT8_TBL;
+SELECT '' AS three, q1, q2, q1 * q2 AS multiply FROM INT8_TBL
+ WHERE q1 < 1000 or (q2 > 0 and q2 < 1000);
+SELECT '' AS five, q1, q2, q1 / q2 AS divide, q1 % q2 AS mod FROM INT8_TBL;
+
+SELECT '' AS five, q1, double(q1) FROM INT8_TBL;
+SELECT '' AS five, q2, double(q2) FROM INT8_TBL;
+
+SELECT 37 + q1 AS plus4 FROM INT8_TBL;
+SELECT 37 - q1 AS minus4 FROM INT8_TBL;
+SELECT '' AS five, 2 * q1 AS `twice int4` FROM INT8_TBL;
+SELECT '' AS five, q1 * 2 AS `twice int4` FROM INT8_TBL;
+
+-- [SPARK-2659] HiveQL: Division operator should always perform fractional division
+-- int8 op int4
+SELECT q1 + int(42) AS `8plus4`, q1 - int(42) AS `8minus4`, q1 * int(42) AS `8mul4`, q1 / int(42) AS `8div4` FROM INT8_TBL;
+-- int4 op int8
+SELECT int(246) + q1 AS `4plus8`, int(246) - q1 AS `4minus8`, int(246) * q1 AS `4mul8`, int(246) / q1 AS `4div8` FROM INT8_TBL;
+
+-- int8 op int2
+SELECT q1 + smallint(42) AS `8plus2`, q1 - smallint(42) AS `8minus2`, q1 * smallint(42) AS `8mul2`, q1 / smallint(42) AS `8div2` FROM INT8_TBL;
+-- int2 op int8
+SELECT smallint(246) + q1 AS `2plus8`, smallint(246) - q1 AS `2minus8`, smallint(246) * q1 AS `2mul8`, smallint(246) / q1 AS `2div8` FROM INT8_TBL;
+
+SELECT q2, abs(q2) FROM INT8_TBL;
+SELECT min(q1), min(q2) FROM INT8_TBL;
+SELECT max(q1), max(q2) FROM INT8_TBL;
+
+-- [SPARK-28137] Missing Data Type Formatting Functions
+-- TO_CHAR()
+--
+-- SELECT '' AS to_char_1, to_char(q1, '9G999G999G999G999G999'), to_char(q2, '9,999,999,999,999,999')
+-- FROM INT8_TBL;
+
+-- SELECT '' AS to_char_2, to_char(q1, '9G999G999G999G999G999D999G999'), to_char(q2, '9,999,999,999,999,999.999,999')
+-- FROM INT8_TBL;
+
+-- SELECT '' AS to_char_3, to_char( (q1 * -1), '9999999999999999PR'), to_char( (q2 * -1), '9999999999999999.999PR')
+-- FROM INT8_TBL;
+
+-- SELECT '' AS to_char_4, to_char( (q1 * -1), '9999999999999999S'), to_char( (q2 * -1), 'S9999999999999999')
+-- FROM INT8_TBL;
+
+-- SELECT '' AS to_char_5, to_char(q2, 'MI9999999999999999') FROM INT8_TBL;
+-- SELECT '' AS to_char_6, to_char(q2, 'FMS9999999999999999') FROM INT8_TBL;
+-- SELECT '' AS to_char_7, to_char(q2, 'FM9999999999999999THPR') FROM INT8_TBL;
+-- SELECT '' AS to_char_8, to_char(q2, 'SG9999999999999999th') FROM INT8_TBL;
+-- SELECT '' AS to_char_9, to_char(q2, '0999999999999999') FROM INT8_TBL;
+-- SELECT '' AS to_char_10, to_char(q2, 'S0999999999999999') FROM INT8_TBL;
+-- SELECT '' AS to_char_11, to_char(q2, 'FM0999999999999999') FROM INT8_TBL;
+-- SELECT '' AS to_char_12, to_char(q2, 'FM9999999999999999.000') FROM INT8_TBL;
+-- SELECT '' AS to_char_13, to_char(q2, 'L9999999999999999.000') FROM INT8_TBL;
+-- SELECT '' AS to_char_14, to_char(q2, 'FM9999999999999999.999') FROM INT8_TBL;
+-- SELECT '' AS to_char_15, to_char(q2, 'S 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 . 9 9 9') FROM INT8_TBL;
+-- SELECT '' AS to_char_16, to_char(q2, E'99999 "text" 9999 "9999" 999 "\\"text between quote marks\\"" 9999') FROM INT8_TBL;
+-- SELECT '' AS to_char_17, to_char(q2, '999999SG9999999999') FROM INT8_TBL;
+
+-- [SPARK-26218] Throw exception on overflow for integers
+-- check min/max values and overflow behavior
+
+-- select bigint('-9223372036854775808');
+-- select bigint('-9223372036854775809');
+-- select bigint('9223372036854775807');
+-- select bigint('9223372036854775808');
+
+-- select bigint('9223372036854775808');
+
+-- select -(bigint('-9223372036854775807'));
+-- select -(bigint('-9223372036854775808'));
+
+-- select bigint('9223372036854775800') + bigint('9223372036854775800');
+-- select bigint('-9223372036854775800') + bigint('-9223372036854775800');
+
+-- select bigint('9223372036854775800') - bigint('-9223372036854775800');
+-- select bigint('-9223372036854775800') - bigint('9223372036854775800');
+
+-- select bigint('9223372036854775800') * bigint('9223372036854775800');
+
+select bigint('9223372036854775800') / bigint('0');
+-- select bigint('9223372036854775800') % bigint('0');
+
+-- select abs(bigint('-9223372036854775808'));
+
+-- select bigint('9223372036854775800') + int('100');
+-- select bigint('-9223372036854775800') - int('100');
+-- select bigint('9223372036854775800') * int('100');
+
+-- select int('100') + bigint('9223372036854775800');
+-- select int('-100') - bigint('9223372036854775800');
+-- select int('100') * bigint('9223372036854775800');
+
+-- select bigint('9223372036854775800') + smallint('100');
+-- select bigint('-9223372036854775800') - smallint('100');
+-- select bigint('9223372036854775800') * smallint('100');
+select bigint('-9223372036854775808') / smallint('0');
+
+-- select smallint('100') + bigint('9223372036854775800');
+-- select smallint('-100') - bigint('9223372036854775800');
+-- select smallint('100') * bigint('9223372036854775800');
+select smallint('100') / bigint('0');
+
+SELECT CAST(q1 AS int) FROM int8_tbl WHERE q2 = 456;
+SELECT CAST(q1 AS int) FROM int8_tbl WHERE q2 <> 456;
+
+SELECT CAST(q1 AS smallint) FROM int8_tbl WHERE q2 = 456;
+SELECT CAST(q1 AS smallint) FROM int8_tbl WHERE q2 <> 456;
+
+SELECT CAST(smallint('42') AS bigint), CAST(smallint('-37') AS bigint);
+
+SELECT CAST(q1 AS float), CAST(q2 AS double) FROM INT8_TBL;
+SELECT CAST(float('36854775807.0') AS bigint);
+SELECT CAST(double('922337203685477580700.0') AS bigint);
+
+
+-- [SPARK-28027] Missing some mathematical operators
+-- bit operations
+
+-- SELECT q1, q2, q1 & q2 AS `and`, q1 | q2 AS `or`, q1 # q2 AS `xor`, ~q1 AS `not` FROM INT8_TBL;
+SELECT q1, q2, q1 & q2 AS `and`, q1 | q2 AS `or`, ~q1 AS `not` FROM INT8_TBL;
+-- SELECT q1, q1 << 2 AS `shl`, q1 >> 3 AS `shr` FROM INT8_TBL;
+
+
+-- generate_series
+
+SELECT * FROM range(bigint('+4567890123456789'), bigint('+4567890123456799'));
+SELECT * FROM range(bigint('+4567890123456789'), bigint('+4567890123456799'), 0);
+SELECT * FROM range(bigint('+4567890123456789'), bigint('+4567890123456799'), 2);
+
+-- corner case
+SELECT string(shiftleft(bigint(-1), 63));
+SELECT string(int(shiftleft(bigint(-1), 63))+1);
+
+-- [SPARK-26218] Throw exception on overflow for integers
+-- check sane handling of INT64_MIN overflow cases
+SELECT bigint((-9223372036854775808)) * bigint((-1));
+SELECT bigint((-9223372036854775808)) / bigint((-1));
+SELECT bigint((-9223372036854775808)) % bigint((-1));
+SELECT bigint((-9223372036854775808)) * int((-1));
+SELECT bigint((-9223372036854775808)) / int((-1));
+SELECT bigint((-9223372036854775808)) % int((-1));
+SELECT bigint((-9223372036854775808)) * smallint((-1));
+SELECT bigint((-9223372036854775808)) / smallint((-1));
+SELECT bigint((-9223372036854775808)) % smallint((-1));
+
+-- [SPARK-28028] Cast numeric to integral type need round
+-- check rounding when casting from float
+SELECT x, bigint(x) AS int8_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);
+
+-- check rounding when casting from numeric
+SELECT x, bigint(x) AS int8_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 INT8_TBL;
diff --git a/sql/core/src/test/resources/sql-tests/results/pgSQL/int8.sql.out b/sql/core/src/test/resources/sql-tests/results/pgSQL/int8.sql.out
new file mode 100644
index 0000000..13bc748
--- /dev/null
+++ b/sql/core/src/test/resources/sql-tests/results/pgSQL/int8.sql.out
@@ -0,0 +1,855 @@
+-- Automatically generated by SQLQueryTestSuite
+-- Number of queries: 85
+
+
+-- !query 0
+CREATE TABLE INT8_TBL(q1 bigint, q2 bigint) USING parquet
+-- !query 0 schema
+struct<>
+-- !query 0 output
+
+
+
+-- !query 1
+INSERT INTO INT8_TBL VALUES(trim(' 123 '),trim(' 456'))
+-- !query 1 schema
+struct<>
+-- !query 1 output
+
+
+
+-- !query 2
+INSERT INTO INT8_TBL VALUES(trim('123 '),'4567890123456789')
+-- !query 2 schema
+struct<>
+-- !query 2 output
+
+
+
+-- !query 3
+INSERT INTO INT8_TBL VALUES('4567890123456789','123')
+-- !query 3 schema
+struct<>
+-- !query 3 output
+
+
+
+-- !query 4
+INSERT INTO INT8_TBL VALUES(+4567890123456789,'4567890123456789')
+-- !query 4 schema
+struct<>
+-- !query 4 output
+
+
+
+-- !query 5
+INSERT INTO INT8_TBL VALUES('+4567890123456789','-4567890123456789')
+-- !query 5 schema
+struct<>
+-- !query 5 output
+
+
+
+-- !query 6
+SELECT * FROM INT8_TBL
+-- !query 6 schema
+struct<q1:bigint,q2:bigint>
+-- !query 6 output
+123 456
+123 4567890123456789
+4567890123456789 -4567890123456789
+4567890123456789 123
+4567890123456789 4567890123456789
+
+
+-- !query 7
+SELECT * FROM INT8_TBL WHERE q2 = 4567890123456789
+-- !query 7 schema
+struct<q1:bigint,q2:bigint>
+-- !query 7 output
+123 4567890123456789
+4567890123456789 4567890123456789
+
+
+-- !query 8
+SELECT * FROM INT8_TBL WHERE q2 <> 4567890123456789
+-- !query 8 schema
+struct<q1:bigint,q2:bigint>
+-- !query 8 output
+123 456
+4567890123456789 -4567890123456789
+4567890123456789 123
+
+
+-- !query 9
+SELECT * FROM INT8_TBL WHERE q2 < 4567890123456789
+-- !query 9 schema
+struct<q1:bigint,q2:bigint>
+-- !query 9 output
+123 456
+4567890123456789 -4567890123456789
+4567890123456789 123
+
+
+-- !query 10
+SELECT * FROM INT8_TBL WHERE q2 > 4567890123456789
+-- !query 10 schema
+struct<q1:bigint,q2:bigint>
+-- !query 10 output
+
+
+
+-- !query 11
+SELECT * FROM INT8_TBL WHERE q2 <= 4567890123456789
+-- !query 11 schema
+struct<q1:bigint,q2:bigint>
+-- !query 11 output
+123 456
+123 4567890123456789
+4567890123456789 -4567890123456789
+4567890123456789 123
+4567890123456789 4567890123456789
+
+
+-- !query 12
+SELECT * FROM INT8_TBL WHERE q2 >= 4567890123456789
+-- !query 12 schema
+struct<q1:bigint,q2:bigint>
+-- !query 12 output
+123 4567890123456789
+4567890123456789 4567890123456789
+
+
+-- !query 13
+SELECT * FROM INT8_TBL WHERE q2 = 456
+-- !query 13 schema
+struct<q1:bigint,q2:bigint>
+-- !query 13 output
+123 456
+
+
+-- !query 14
+SELECT * FROM INT8_TBL WHERE q2 <> 456
+-- !query 14 schema
+struct<q1:bigint,q2:bigint>
+-- !query 14 output
+123 4567890123456789
+4567890123456789 -4567890123456789
+4567890123456789 123
+4567890123456789 4567890123456789
+
+
+-- !query 15
+SELECT * FROM INT8_TBL WHERE q2 < 456
+-- !query 15 schema
+struct<q1:bigint,q2:bigint>
+-- !query 15 output
+4567890123456789 -4567890123456789
+4567890123456789 123
+
+
+-- !query 16
+SELECT * FROM INT8_TBL WHERE q2 > 456
+-- !query 16 schema
+struct<q1:bigint,q2:bigint>
+-- !query 16 output
+123 4567890123456789
+4567890123456789 4567890123456789
+
+
+-- !query 17
+SELECT * FROM INT8_TBL WHERE q2 <= 456
+-- !query 17 schema
+struct<q1:bigint,q2:bigint>
+-- !query 17 output
+123 456
+4567890123456789 -4567890123456789
+4567890123456789 123
+
+
+-- !query 18
+SELECT * FROM INT8_TBL WHERE q2 >= 456
+-- !query 18 schema
+struct<q1:bigint,q2:bigint>
+-- !query 18 output
+123 456
+123 4567890123456789
+4567890123456789 4567890123456789
+
+
+-- !query 19
+SELECT * FROM INT8_TBL WHERE 123 = q1
+-- !query 19 schema
+struct<q1:bigint,q2:bigint>
+-- !query 19 output
+123 456
+123 4567890123456789
+
+
+-- !query 20
+SELECT * FROM INT8_TBL WHERE 123 <> q1
+-- !query 20 schema
+struct<q1:bigint,q2:bigint>
+-- !query 20 output
+4567890123456789 -4567890123456789
+4567890123456789 123
+4567890123456789 4567890123456789
+
+
+-- !query 21
+SELECT * FROM INT8_TBL WHERE 123 < q1
+-- !query 21 schema
+struct<q1:bigint,q2:bigint>
+-- !query 21 output
+4567890123456789 -4567890123456789
+4567890123456789 123
+4567890123456789 4567890123456789
+
+
+-- !query 22
+SELECT * FROM INT8_TBL WHERE 123 > q1
+-- !query 22 schema
+struct<q1:bigint,q2:bigint>
+-- !query 22 output
+
+
+
+-- !query 23
+SELECT * FROM INT8_TBL WHERE 123 <= q1
+-- !query 23 schema
+struct<q1:bigint,q2:bigint>
+-- !query 23 output
+123 456
+123 4567890123456789
+4567890123456789 -4567890123456789
+4567890123456789 123
+4567890123456789 4567890123456789
+
+
+-- !query 24
+SELECT * FROM INT8_TBL WHERE 123 >= q1
+-- !query 24 schema
+struct<q1:bigint,q2:bigint>
+-- !query 24 output
+123 456
+123 4567890123456789
+
+
+-- !query 25
+SELECT * FROM INT8_TBL WHERE q2 = smallint('456')
+-- !query 25 schema
+struct<q1:bigint,q2:bigint>
+-- !query 25 output
+123 456
+
+
+-- !query 26
+SELECT * FROM INT8_TBL WHERE q2 <> smallint('456')
+-- !query 26 schema
+struct<q1:bigint,q2:bigint>
+-- !query 26 output
+123 4567890123456789
+4567890123456789 -4567890123456789
+4567890123456789 123
+4567890123456789 4567890123456789
+
+
+-- !query 27
+SELECT * FROM INT8_TBL WHERE q2 < smallint('456')
+-- !query 27 schema
+struct<q1:bigint,q2:bigint>
+-- !query 27 output
+4567890123456789 -4567890123456789
+4567890123456789 123
+
+
+-- !query 28
+SELECT * FROM INT8_TBL WHERE q2 > smallint('456')
+-- !query 28 schema
+struct<q1:bigint,q2:bigint>
+-- !query 28 output
+123 4567890123456789
+4567890123456789 4567890123456789
+
+
+-- !query 29
+SELECT * FROM INT8_TBL WHERE q2 <= smallint('456')
+-- !query 29 schema
+struct<q1:bigint,q2:bigint>
+-- !query 29 output
+123 456
+4567890123456789 -4567890123456789
+4567890123456789 123
+
+
+-- !query 30
+SELECT * FROM INT8_TBL WHERE q2 >= smallint('456')
+-- !query 30 schema
+struct<q1:bigint,q2:bigint>
+-- !query 30 output
+123 456
+123 4567890123456789
+4567890123456789 4567890123456789
+
+
+-- !query 31
+SELECT * FROM INT8_TBL WHERE smallint('123') = q1
+-- !query 31 schema
+struct<q1:bigint,q2:bigint>
+-- !query 31 output
+123 456
+123 4567890123456789
+
+
+-- !query 32
+SELECT * FROM INT8_TBL WHERE smallint('123') <> q1
+-- !query 32 schema
+struct<q1:bigint,q2:bigint>
+-- !query 32 output
+4567890123456789 -4567890123456789
+4567890123456789 123
+4567890123456789 4567890123456789
+
+
+-- !query 33
+SELECT * FROM INT8_TBL WHERE smallint('123') < q1
+-- !query 33 schema
+struct<q1:bigint,q2:bigint>
+-- !query 33 output
+4567890123456789 -4567890123456789
+4567890123456789 123
+4567890123456789 4567890123456789
+
+
+-- !query 34
+SELECT * FROM INT8_TBL WHERE smallint('123') > q1
+-- !query 34 schema
+struct<q1:bigint,q2:bigint>
+-- !query 34 output
+
+
+
+-- !query 35
+SELECT * FROM INT8_TBL WHERE smallint('123') <= q1
+-- !query 35 schema
+struct<q1:bigint,q2:bigint>
+-- !query 35 output
+123 456
+123 4567890123456789
+4567890123456789 -4567890123456789
+4567890123456789 123
+4567890123456789 4567890123456789
+
+
+-- !query 36
+SELECT * FROM INT8_TBL WHERE smallint('123') >= q1
+-- !query 36 schema
+struct<q1:bigint,q2:bigint>
+-- !query 36 output
+123 456
+123 4567890123456789
+
+
+-- !query 37
+SELECT '' AS five, q1 AS plus, -q1 AS minus FROM INT8_TBL
+-- !query 37 schema
+struct<five:string,plus:bigint,minus:bigint>
+-- !query 37 output
+123 -123
+ 123 -123
+ 4567890123456789 -4567890123456789
+ 4567890123456789 -4567890123456789
+ 4567890123456789 -4567890123456789
+
+
+-- !query 38
+SELECT '' AS five, q1, q2, q1 + q2 AS plus FROM INT8_TBL
+-- !query 38 schema
+struct<five:string,q1:bigint,q2:bigint,plus:bigint>
+-- !query 38 output
+123 456 579
+ 123 4567890123456789 4567890123456912
+ 4567890123456789 -4567890123456789 0
+ 4567890123456789 123 4567890123456912
+ 4567890123456789 4567890123456789 9135780246913578
+
+
+-- !query 39
+SELECT '' AS five, q1, q2, q1 - q2 AS minus FROM INT8_TBL
+-- !query 39 schema
+struct<five:string,q1:bigint,q2:bigint,minus:bigint>
+-- !query 39 output
+123 456 -333
+ 123 4567890123456789 -4567890123456666
+ 4567890123456789 -4567890123456789 9135780246913578
+ 4567890123456789 123 4567890123456666
+ 4567890123456789 4567890123456789 0
+
+
+-- !query 40
+SELECT '' AS three, q1, q2, q1 * q2 AS multiply FROM INT8_TBL
+-- !query 40 schema
+struct<three:string,q1:bigint,q2:bigint,multiply:bigint>
+-- !query 40 output
+123 456 56088
+ 123 4567890123456789 561850485185185047
+ 4567890123456789 -4567890123456789 -4868582358072306617
+ 4567890123456789 123 561850485185185047
+ 4567890123456789 4567890123456789 4868582358072306617
+
+
+-- !query 41
+SELECT '' AS three, q1, q2, q1 * q2 AS multiply FROM INT8_TBL
+ WHERE q1 < 1000 or (q2 > 0 and q2 < 1000)
+-- !query 41 schema
+struct<three:string,q1:bigint,q2:bigint,multiply:bigint>
+-- !query 41 output
+123 456 56088
+ 123 4567890123456789 561850485185185047
+ 4567890123456789 123 561850485185185047
+
+
+-- !query 42
+SELECT '' AS five, q1, q2, q1 / q2 AS divide, q1 % q2 AS mod FROM INT8_TBL
+-- !query 42 schema
+struct<five:string,q1:bigint,q2:bigint,divide:double,mod:bigint>
+-- !query 42 output
+123 456 0.26973684210526316 123
+ 123 4567890123456789 2.6927092525360204E-14 123
+ 4567890123456789 -4567890123456789 -1.0 0
+ 4567890123456789 123 3.713731807688446E13 57
+ 4567890123456789 4567890123456789 1.0 0
+
+
+-- !query 43
+SELECT '' AS five, q1, double(q1) FROM INT8_TBL
+-- !query 43 schema
+struct<five:string,q1:bigint,q1:double>
+-- !query 43 output
+123 123.0
+ 123 123.0
+ 4567890123456789 4.567890123456789E15
+ 4567890123456789 4.567890123456789E15
+ 4567890123456789 4.567890123456789E15
+
+
+-- !query 44
+SELECT '' AS five, q2, double(q2) FROM INT8_TBL
+-- !query 44 schema
+struct<five:string,q2:bigint,q2:double>
+-- !query 44 output
+-4567890123456789 -4.567890123456789E15
+ 123 123.0
+ 456 456.0
+ 4567890123456789 4.567890123456789E15
+ 4567890123456789 4.567890123456789E15
+
+
+-- !query 45
+SELECT 37 + q1 AS plus4 FROM INT8_TBL
+-- !query 45 schema
+struct<plus4:bigint>
+-- !query 45 output
+160
+160
+4567890123456826
+4567890123456826
+4567890123456826
+
+
+-- !query 46
+SELECT 37 - q1 AS minus4 FROM INT8_TBL
+-- !query 46 schema
+struct<minus4:bigint>
+-- !query 46 output
+-4567890123456752
+-4567890123456752
+-4567890123456752
+-86
+-86
+
+
+-- !query 47
+SELECT '' AS five, 2 * q1 AS `twice int4` FROM INT8_TBL
+-- !query 47 schema
+struct<five:string,twice int4:bigint>
+-- !query 47 output
+246
+ 246
+ 9135780246913578
+ 9135780246913578
+ 9135780246913578
+
+
+-- !query 48
+SELECT '' AS five, q1 * 2 AS `twice int4` FROM INT8_TBL
+-- !query 48 schema
+struct<five:string,twice int4:bigint>
+-- !query 48 output
+246
+ 246
+ 9135780246913578
+ 9135780246913578
+ 9135780246913578
+
+
+-- !query 49
+SELECT q1 + int(42) AS `8plus4`, q1 - int(42) AS `8minus4`, q1 * int(42) AS `8mul4`, q1 / int(42) AS `8div4` FROM INT8_TBL
+-- !query 49 schema
+struct<8plus4:bigint,8minus4:bigint,8mul4:bigint,8div4:double>
+-- !query 49 output
+165 81 5166 2.9285714285714284
+165 81 5166 2.9285714285714284
+4567890123456831 4567890123456747 191851385185185138 1.0875928865373308E14
+4567890123456831 4567890123456747 191851385185185138 1.0875928865373308E14
+4567890123456831 4567890123456747 191851385185185138 1.0875928865373308E14
+
+
+-- !query 50
+SELECT int(246) + q1 AS `4plus8`, int(246) - q1 AS `4minus8`, int(246) * q1 AS `4mul8`, int(246) / q1 AS `4div8` FROM INT8_TBL
+-- !query 50 schema
+struct<4plus8:bigint,4minus8:bigint,4mul8:bigint,4div8:double>
+-- !query 50 output
+369 123 30258 2.0
+369 123 30258 2.0
+4567890123457035 -4567890123456543 1123700970370370094 5.385418505072041E-14
+4567890123457035 -4567890123456543 1123700970370370094 5.385418505072041E-14
+4567890123457035 -4567890123456543 1123700970370370094 5.385418505072041E-14
+
+
+-- !query 51
+SELECT q1 + smallint(42) AS `8plus2`, q1 - smallint(42) AS `8minus2`, q1 * smallint(42) AS `8mul2`, q1 / smallint(42) AS `8div2` FROM INT8_TBL
+-- !query 51 schema
+struct<8plus2:bigint,8minus2:bigint,8mul2:bigint,8div2:double>
+-- !query 51 output
+165 81 5166 2.9285714285714284
+165 81 5166 2.9285714285714284
+4567890123456831 4567890123456747 191851385185185138 1.0875928865373308E14
+4567890123456831 4567890123456747 191851385185185138 1.0875928865373308E14
+4567890123456831 4567890123456747 191851385185185138 1.0875928865373308E14
+
+
+-- !query 52
+SELECT smallint(246) + q1 AS `2plus8`, smallint(246) - q1 AS `2minus8`, smallint(246) * q1 AS `2mul8`, smallint(246) / q1 AS `2div8` FROM INT8_TBL
+-- !query 52 schema
+struct<2plus8:bigint,2minus8:bigint,2mul8:bigint,2div8:double>
+-- !query 52 output
+369 123 30258 2.0
+369 123 30258 2.0
+4567890123457035 -4567890123456543 1123700970370370094 5.385418505072041E-14
+4567890123457035 -4567890123456543 1123700970370370094 5.385418505072041E-14
+4567890123457035 -4567890123456543 1123700970370370094 5.385418505072041E-14
+
+
+-- !query 53
+SELECT q2, abs(q2) FROM INT8_TBL
+-- !query 53 schema
+struct<q2:bigint,abs(q2):bigint>
+-- !query 53 output
+-4567890123456789 4567890123456789
+123 123
+456 456
+4567890123456789 4567890123456789
+4567890123456789 4567890123456789
+
+
+-- !query 54
+SELECT min(q1), min(q2) FROM INT8_TBL
+-- !query 54 schema
+struct<min(q1):bigint,min(q2):bigint>
+-- !query 54 output
+123 -4567890123456789
+
+
+-- !query 55
+SELECT max(q1), max(q2) FROM INT8_TBL
+-- !query 55 schema
+struct<max(q1):bigint,max(q2):bigint>
+-- !query 55 output
+4567890123456789 4567890123456789
+
+
+-- !query 56
+select bigint('9223372036854775800') / bigint('0')
+-- !query 56 schema
+struct<(CAST(CAST(9223372036854775800 AS BIGINT) AS DOUBLE) / CAST(CAST(0 AS BIGINT) AS DOUBLE)):double>
+-- !query 56 output
+NULL
+
+
+-- !query 57
+select bigint('-9223372036854775808') / smallint('0')
+-- !query 57 schema
+struct<(CAST(CAST(-9223372036854775808 AS BIGINT) AS DOUBLE) / CAST(CAST(0 AS SMALLINT) AS DOUBLE)):double>
+-- !query 57 output
+NULL
+
+
+-- !query 58
+select smallint('100') / bigint('0')
+-- !query 58 schema
+struct<(CAST(CAST(100 AS SMALLINT) AS DOUBLE) / CAST(CAST(0 AS BIGINT) AS DOUBLE)):double>
+-- !query 58 output
+NULL
+
+
+-- !query 59
+SELECT CAST(q1 AS int) FROM int8_tbl WHERE q2 = 456
+-- !query 59 schema
+struct<q1:int>
+-- !query 59 output
+123
+
+
+-- !query 60
+SELECT CAST(q1 AS int) FROM int8_tbl WHERE q2 <> 456
+-- !query 60 schema
+struct<q1:int>
+-- !query 60 output
+-869367531
+-869367531
+-869367531
+123
+
+
+-- !query 61
+SELECT CAST(q1 AS smallint) FROM int8_tbl WHERE q2 = 456
+-- !query 61 schema
+struct<q1:smallint>
+-- !query 61 output
+123
+
+
+-- !query 62
+SELECT CAST(q1 AS smallint) FROM int8_tbl WHERE q2 <> 456
+-- !query 62 schema
+struct<q1:smallint>
+-- !query 62 output
+-32491
+-32491
+-32491
+123
+
+
+-- !query 63
+SELECT CAST(smallint('42') AS bigint), CAST(smallint('-37') AS bigint)
+-- !query 63 schema
+struct<CAST(CAST(42 AS SMALLINT) AS BIGINT):bigint,CAST(CAST(-37 AS SMALLINT) AS BIGINT):bigint>
+-- !query 63 output
+42 -37
+
+
+-- !query 64
+SELECT CAST(q1 AS float), CAST(q2 AS double) FROM INT8_TBL
+-- !query 64 schema
+struct<q1:float,q2:double>
+-- !query 64 output
+123.0 4.567890123456789E15
+123.0 456.0
+4.5678899E15 -4.567890123456789E15
+4.5678899E15 123.0
+4.5678899E15 4.567890123456789E15
+
+
+-- !query 65
+SELECT CAST(float('36854775807.0') AS bigint)
+-- !query 65 schema
+struct<CAST(CAST(36854775807.0 AS FLOAT) AS BIGINT):bigint>
+-- !query 65 output
+36854775808
+
+
+-- !query 66
+SELECT CAST(double('922337203685477580700.0') AS bigint)
+-- !query 66 schema
+struct<CAST(CAST(922337203685477580700.0 AS DOUBLE) AS BIGINT):bigint>
+-- !query 66 output
+9223372036854775807
+
+
+-- !query 67
+SELECT q1, q2, q1 & q2 AS `and`, q1 | q2 AS `or`, ~q1 AS `not` FROM INT8_TBL
+-- !query 67 schema
+struct<q1:bigint,q2:bigint,and:bigint,or:bigint,not:bigint>
+-- !query 67 output
+123 456 72 507 -124
+123 4567890123456789 17 4567890123456895 -124
+4567890123456789 -4567890123456789 1 -1 -4567890123456790
+4567890123456789 123 17 4567890123456895 -4567890123456790
+4567890123456789 4567890123456789 4567890123456789 4567890123456789 -4567890123456790
+
+
+-- !query 68
+SELECT * FROM range(bigint('+4567890123456789'), bigint('+4567890123456799'))
+-- !query 68 schema
+struct<id:bigint>
+-- !query 68 output
+4567890123456789
+4567890123456790
+4567890123456791
+4567890123456792
+4567890123456793
+4567890123456794
+4567890123456795
+4567890123456796
+4567890123456797
+4567890123456798
+
+
+-- !query 69
+SELECT * FROM range(bigint('+4567890123456789'), bigint('+4567890123456799'), 0)
+-- !query 69 schema
+struct<>
+-- !query 69 output
+java.lang.IllegalArgumentException
+requirement failed: step (0) cannot be 0
+
+
+-- !query 70
+SELECT * FROM range(bigint('+4567890123456789'), bigint('+4567890123456799'), 2)
+-- !query 70 schema
+struct<id:bigint>
+-- !query 70 output
+4567890123456789
+4567890123456791
+4567890123456793
+4567890123456795
+4567890123456797
+
+
+-- !query 71
+SELECT string(shiftleft(bigint(-1), 63))
+-- !query 71 schema
+struct<CAST(shiftleft(CAST(-1 AS BIGINT), 63) AS STRING):string>
+-- !query 71 output
+-9223372036854775808
+
+
+-- !query 72
+SELECT string(int(shiftleft(bigint(-1), 63))+1)
+-- !query 72 schema
+struct<CAST((CAST(shiftleft(CAST(-1 AS BIGINT), 63) AS INT) + 1) AS STRING):string>
+-- !query 72 output
+1
+
+
+-- !query 73
+SELECT bigint((-9223372036854775808)) * bigint((-1))
+-- !query 73 schema
+struct<(CAST(-9223372036854775808 AS BIGINT) * CAST(-1 AS BIGINT)):bigint>
+-- !query 73 output
+-9223372036854775808
+
+
+-- !query 74
+SELECT bigint((-9223372036854775808)) / bigint((-1))
+-- !query 74 schema
+struct<(CAST(CAST(-9223372036854775808 AS BIGINT) AS DOUBLE) / CAST(CAST(-1 AS BIGINT) AS DOUBLE)):double>
+-- !query 74 output
+9.223372036854776E18
+
+
+-- !query 75
+SELECT bigint((-9223372036854775808)) % bigint((-1))
+-- !query 75 schema
+struct<(CAST(-9223372036854775808 AS BIGINT) % CAST(-1 AS BIGINT)):bigint>
+-- !query 75 output
+0
+
+
+-- !query 76
+SELECT bigint((-9223372036854775808)) * int((-1))
+-- !query 76 schema
+struct<(CAST(-9223372036854775808 AS BIGINT) * CAST(CAST(-1 AS INT) AS BIGINT)):bigint>
+-- !query 76 output
+-9223372036854775808
+
+
+-- !query 77
+SELECT bigint((-9223372036854775808)) / int((-1))
+-- !query 77 schema
+struct<(CAST(CAST(-9223372036854775808 AS BIGINT) AS DOUBLE) / CAST(CAST(-1 AS INT) AS DOUBLE)):double>
+-- !query 77 output
+9.223372036854776E18
+
+
+-- !query 78
+SELECT bigint((-9223372036854775808)) % int((-1))
+-- !query 78 schema
+struct<(CAST(-9223372036854775808 AS BIGINT) % CAST(CAST(-1 AS INT) AS BIGINT)):bigint>
+-- !query 78 output
+0
+
+
+-- !query 79
+SELECT bigint((-9223372036854775808)) * smallint((-1))
+-- !query 79 schema
+struct<(CAST(-9223372036854775808 AS BIGINT) * CAST(CAST(-1 AS SMALLINT) AS BIGINT)):bigint>
+-- !query 79 output
+-9223372036854775808
+
+
+-- !query 80
+SELECT bigint((-9223372036854775808)) / smallint((-1))
+-- !query 80 schema
+struct<(CAST(CAST(-9223372036854775808 AS BIGINT) AS DOUBLE) / CAST(CAST(-1 AS SMALLINT) AS DOUBLE)):double>
+-- !query 80 output
+9.223372036854776E18
+
+
+-- !query 81
+SELECT bigint((-9223372036854775808)) % smallint((-1))
+-- !query 81 schema
+struct<(CAST(-9223372036854775808 AS BIGINT) % CAST(CAST(-1 AS SMALLINT) AS BIGINT)):bigint>
+-- !query 81 output
+0
+
+
+-- !query 82
+SELECT x, bigint(x) AS int8_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 82 schema
+struct<x:double,int8_value:bigint>
+-- !query 82 output
+-0.5 0
+-1.5 -1
+-2.5 -2
+0.0 0
+0.5 0
+1.5 1
+2.5 2
+
+
+-- !query 83
+SELECT x, bigint(x) AS int8_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 83 schema
+struct<x:decimal(38,18),int8_value:bigint>
+-- !query 83 output
+-0.5 0
+-1.5 -1
+-2.5 -2
+0 0
+0.5 0
+1.5 1
+2.5 2
+
+
+-- !query 84
+DROP TABLE INT8_TBL
+-- !query 84 schema
+struct<>
+-- !query 84 output
+
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org