dongjoon-hyun commented on a change in pull request #24933: [SPARK-28136][SQL][TEST] Port int8.sql URL: https://github.com/apache/spark/pull/24933#discussion_r301738330
########## File path: 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-28024] Incorrect value when out of range +-- 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'); Review comment: Please comment out from line 133 to line 173 because all of them expects errors `ERROR: bigint out of range`. The only exceptions are line 152, 168, and 173. We can keep those three because Spark returns `NULL` while PostgreSQL expects `ERROR: division by zero` for those three. ---------------------------------------------------------------- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: [email protected] With regards, Apache Git Services --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
