inputs/postgreSQL/int8.sql

0001 --
0002 -- Portions Copyright (c) 1996-2019, PostgreSQL Global Development Group
0003 --
0004 --
0005 -- INT8
0006 -- Test int8 64-bit integers.
0007 -- https://github.com/postgres/postgres/blob/REL_12_BETA2/src/test/regress/sql/int8.sql
0008 --
0009 CREATE TABLE INT8_TBL(q1 bigint, q2 bigint) USING parquet;
0010
0011 -- PostgreSQL implicitly casts string literals to data with integral types, but
0012 -- Spark does not support that kind of implicit casts.
0013 INSERT INTO INT8_TBL VALUES(bigint(trim('  123   ')),bigint(trim('  456')));
0014 INSERT INTO INT8_TBL VALUES(bigint(trim('123   ')),bigint('4567890123456789'));
0015 INSERT INTO INT8_TBL VALUES(bigint('4567890123456789'),bigint('123'));
0016 INSERT INTO INT8_TBL VALUES(+4567890123456789,bigint('4567890123456789'));
0017 INSERT INTO INT8_TBL VALUES(bigint('+4567890123456789'),bigint('-4567890123456789'));
0018
0019 -- [SPARK-27923] Spark SQL insert there bad inputs to NULL
0020 -- bad inputs
0021 -- INSERT INTO INT8_TBL(q1) VALUES ('      ');
0022 -- INSERT INTO INT8_TBL(q1) VALUES ('xxx');
0023 -- INSERT INTO INT8_TBL(q1) VALUES ('3908203590239580293850293850329485');
0024 -- INSERT INTO INT8_TBL(q1) VALUES ('-1204982019841029840928340329840934');
0025 -- INSERT INTO INT8_TBL(q1) VALUES ('- 123');
0026 -- INSERT INTO INT8_TBL(q1) VALUES ('  345     5');
0027 -- INSERT INTO INT8_TBL(q1) VALUES ('');
0028
0029 SELECT * FROM INT8_TBL;
0030
0031 -- int8/int8 cmp
0032 SELECT * FROM INT8_TBL WHERE q2 = 4567890123456789;
0033 SELECT * FROM INT8_TBL WHERE q2 <> 4567890123456789;
0034 SELECT * FROM INT8_TBL WHERE q2 < 4567890123456789;
0035 SELECT * FROM INT8_TBL WHERE q2 > 4567890123456789;
0036 SELECT * FROM INT8_TBL WHERE q2 <= 4567890123456789;
0037 SELECT * FROM INT8_TBL WHERE q2 >= 4567890123456789;
0038
0039 -- int8/int4 cmp
0040 SELECT * FROM INT8_TBL WHERE q2 = 456;
0041 SELECT * FROM INT8_TBL WHERE q2 <> 456;
0042 SELECT * FROM INT8_TBL WHERE q2 < 456;
0043 SELECT * FROM INT8_TBL WHERE q2 > 456;
0044 SELECT * FROM INT8_TBL WHERE q2 <= 456;
0045 SELECT * FROM INT8_TBL WHERE q2 >= 456;
0046
0047 -- int4/int8 cmp
0048 SELECT * FROM INT8_TBL WHERE 123 = q1;
0049 SELECT * FROM INT8_TBL WHERE 123 <> q1;
0050 SELECT * FROM INT8_TBL WHERE 123 < q1;
0051 SELECT * FROM INT8_TBL WHERE 123 > q1;
0052 SELECT * FROM INT8_TBL WHERE 123 <= q1;
0053 SELECT * FROM INT8_TBL WHERE 123 >= q1;
0054
0055 -- int8/int2 cmp
0056 SELECT * FROM INT8_TBL WHERE q2 = smallint('456');
0057 SELECT * FROM INT8_TBL WHERE q2 <> smallint('456');
0058 SELECT * FROM INT8_TBL WHERE q2 < smallint('456');
0059 SELECT * FROM INT8_TBL WHERE q2 > smallint('456');
0060 SELECT * FROM INT8_TBL WHERE q2 <= smallint('456');
0061 SELECT * FROM INT8_TBL WHERE q2 >= smallint('456');
0062
0063 -- int2/int8 cmp
0064 SELECT * FROM INT8_TBL WHERE smallint('123') = q1;
0065 SELECT * FROM INT8_TBL WHERE smallint('123') <> q1;
0066 SELECT * FROM INT8_TBL WHERE smallint('123') < q1;
0067 SELECT * FROM INT8_TBL WHERE smallint('123') > q1;
0068 SELECT * FROM INT8_TBL WHERE smallint('123') <= q1;
0069 SELECT * FROM INT8_TBL WHERE smallint('123') >= q1;
0070
0071
0072 -- [SPARK-28349] We do not need to follow PostgreSQL to support reserved words in column alias
0073 SELECT '' AS five, q1 AS plus, -q1 AS `minus` FROM INT8_TBL;
0074
0075 SELECT '' AS five, q1, q2, q1 + q2 AS plus FROM INT8_TBL;
0076 SELECT '' AS five, q1, q2, q1 - q2 AS `minus` FROM INT8_TBL;
0077 SELECT '' AS three, q1, q2, q1 * q2 AS multiply FROM INT8_TBL;
0078 SELECT '' AS three, q1, q2, q1 * q2 AS multiply FROM INT8_TBL
0079  WHERE q1 < 1000 or (q2 > 0 and q2 < 1000);
0080 SELECT '' AS five, q1, q2, q1 / q2 AS divide, q1 % q2 AS mod FROM INT8_TBL;
0081
0082 SELECT '' AS five, q1, double(q1) FROM INT8_TBL;
0083 SELECT '' AS five, q2, double(q2) FROM INT8_TBL;
0084
0085 SELECT 37 + q1 AS plus4 FROM INT8_TBL;
0086 SELECT 37 - q1 AS minus4 FROM INT8_TBL;
0087 SELECT '' AS five, 2 * q1 AS `twice int4` FROM INT8_TBL;
0088 SELECT '' AS five, q1 * 2 AS `twice int4` FROM INT8_TBL;
0089
0090 -- int8 op int4
0091 SELECT q1 + int(42) AS `8plus4`, q1 - int(42) AS `8minus4`, q1 * int(42) AS `8mul4`, q1 / int(42) AS `8div4` FROM INT8_TBL;
0092 -- int4 op int8
0093 SELECT int(246) + q1 AS `4plus8`, int(246) - q1 AS `4minus8`, int(246) * q1 AS `4mul8`, int(246) / q1 AS `4div8` FROM INT8_TBL;
0094
0095 -- int8 op int2
0096 SELECT q1 + smallint(42) AS `8plus2`, q1 - smallint(42) AS `8minus2`, q1 * smallint(42) AS `8mul2`, q1 / smallint(42) AS `8div2` FROM INT8_TBL;
0097 -- int2 op int8
0098 SELECT smallint(246) + q1 AS `2plus8`, smallint(246) - q1 AS `2minus8`, smallint(246) * q1 AS `2mul8`, smallint(246) / q1 AS `2div8` FROM INT8_TBL;
0099
0100 SELECT q2, abs(q2) FROM INT8_TBL;
0101 SELECT min(q1), min(q2) FROM INT8_TBL;
0102 SELECT max(q1), max(q2) FROM INT8_TBL;
0103
0104 -- [SPARK-28137] Missing Data Type Formatting Functions
0105 -- TO_CHAR()
0106 --
0107 -- SELECT '' AS to_char_1, to_char(q1, '9G999G999G999G999G999'), to_char(q2, '9,999,999,999,999,999')
0108 --      FROM INT8_TBL;
0109
0110 -- SELECT '' AS to_char_2, to_char(q1, '9G999G999G999G999G999D999G999'), to_char(q2, '9,999,999,999,999,999.999,999')
0111 --      FROM INT8_TBL;
0112
0113 -- SELECT '' AS to_char_3, to_char( (q1 * -1), '9999999999999999PR'), to_char( (q2 * -1), '9999999999999999.999PR')
0114 --      FROM INT8_TBL;
0115
0116 -- SELECT '' AS to_char_4, to_char( (q1 * -1), '9999999999999999S'), to_char( (q2 * -1), 'S9999999999999999')
0117 --      FROM INT8_TBL;
0118
0119 -- SELECT '' AS to_char_5,  to_char(q2, 'MI9999999999999999')     FROM INT8_TBL;
0120 -- SELECT '' AS to_char_6,  to_char(q2, 'FMS9999999999999999')    FROM INT8_TBL;
0121 -- SELECT '' AS to_char_7,  to_char(q2, 'FM9999999999999999THPR') FROM INT8_TBL;
0122 -- SELECT '' AS to_char_8,  to_char(q2, 'SG9999999999999999th')   FROM INT8_TBL;
0123 -- SELECT '' AS to_char_9,  to_char(q2, '0999999999999999')       FROM INT8_TBL;
0124 -- SELECT '' AS to_char_10, to_char(q2, 'S0999999999999999')      FROM INT8_TBL;
0125 -- SELECT '' AS to_char_11, to_char(q2, 'FM0999999999999999')     FROM INT8_TBL;
0126 -- SELECT '' AS to_char_12, to_char(q2, 'FM9999999999999999.000') FROM INT8_TBL;
0127 -- SELECT '' AS to_char_13, to_char(q2, 'L9999999999999999.000')  FROM INT8_TBL;
0128 -- SELECT '' AS to_char_14, to_char(q2, 'FM9999999999999999.999') FROM INT8_TBL;
0129 -- 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;
0130 -- SELECT '' AS to_char_16, to_char(q2, E'99999 "text" 9999 "9999" 999 "\\"text between quote marks\\"" 9999') FROM INT8_TBL;
0131 -- SELECT '' AS to_char_17, to_char(q2, '999999SG9999999999')     FROM INT8_TBL;
0132
0133 -- [SPARK-26218] Throw exception on overflow for integers
0134 -- check min/max values and overflow behavior
0135
0136 -- select bigint('-9223372036854775808');
0137 -- select bigint('-9223372036854775809');
0138 -- select bigint('9223372036854775807');
0139 -- select bigint('9223372036854775808');
0140
0141 -- select bigint('9223372036854775808');
0142
0143 -- select -(bigint('-9223372036854775807'));
0144 -- select -(bigint('-9223372036854775808'));
0145
0146 -- select bigint('9223372036854775800') + bigint('9223372036854775800');
0147 -- select bigint('-9223372036854775800') + bigint('-9223372036854775800');
0148
0149 -- select bigint('9223372036854775800') - bigint('-9223372036854775800');
0150 -- select bigint('-9223372036854775800') - bigint('9223372036854775800');
0151
0152 -- select bigint('9223372036854775800') * bigint('9223372036854775800');
0153
0154 select bigint('9223372036854775800') / bigint('0');
0155 -- select bigint('9223372036854775800') % bigint('0');
0156
0157 -- select abs(bigint('-9223372036854775808'));
0158
0159 -- select bigint('9223372036854775800') + int('100');
0160 -- select bigint('-9223372036854775800') - int('100');
0161 -- select bigint('9223372036854775800') * int('100');
0162
0163 -- select int('100') + bigint('9223372036854775800');
0164 -- select int('-100') - bigint('9223372036854775800');
0165 -- select int('100') * bigint('9223372036854775800');
0166
0167 -- select bigint('9223372036854775800') + smallint('100');
0168 -- select bigint('-9223372036854775800') - smallint('100');
0169 -- select bigint('9223372036854775800') * smallint('100');
0170 select bigint('-9223372036854775808') / smallint('0');
0171
0172 -- select smallint('100') + bigint('9223372036854775800');
0173 -- select smallint('-100') - bigint('9223372036854775800');
0174 -- select smallint('100') * bigint('9223372036854775800');
0175 select smallint('100') / bigint('0');
0176
0177 SELECT CAST(q1 AS int) FROM int8_tbl WHERE q2 = 456;
0178 SELECT CAST(q1 AS int) FROM int8_tbl WHERE q2 <> 456;
0179
0180 SELECT CAST(q1 AS smallint) FROM int8_tbl WHERE q2 = 456;
0181 SELECT CAST(q1 AS smallint) FROM int8_tbl WHERE q2 <> 456;
0182
0183 SELECT CAST(smallint('42') AS bigint), CAST(smallint('-37') AS bigint);
0184
0185 SELECT CAST(q1 AS float), CAST(q2 AS double) FROM INT8_TBL;
0186 SELECT CAST(float('36854775807.0') AS bigint);
0187 SELECT CAST(double('922337203685477580700.0') AS bigint);
0188
0189
0190 -- [SPARK-28027] Missing some mathematical operators
0191 -- bit operations
0192
0193 -- SELECT q1, q2, q1 & q2 AS `and`, q1 | q2 AS `or`, q1 # q2 AS `xor`, ~q1 AS `not` FROM INT8_TBL;
0194 SELECT q1, q2, q1 & q2 AS `and`, q1 | q2 AS `or`, ~q1 AS `not` FROM INT8_TBL;
0195 -- SELECT q1, q1 << 2 AS `shl`, q1 >> 3 AS `shr` FROM INT8_TBL;
0196
0197
0198 -- generate_series
0199
0200 SELECT * FROM range(bigint('+4567890123456789'), bigint('+4567890123456799'));
0201 SELECT * FROM range(bigint('+4567890123456789'), bigint('+4567890123456799'), 0);
0202 SELECT * FROM range(bigint('+4567890123456789'), bigint('+4567890123456799'), 2);
0203
0204 -- corner case
0205 SELECT string(shiftleft(bigint(-1), 63));
0206 SELECT string(int(shiftleft(bigint(-1), 63))+1);
0207
0208 -- [SPARK-26218] Throw exception on overflow for integers
0209 -- check sane handling of INT64_MIN overflow cases
0210 SELECT bigint((-9223372036854775808)) * bigint((-1));
0211 SELECT bigint((-9223372036854775808)) / bigint((-1));
0212 SELECT bigint((-9223372036854775808)) % bigint((-1));
0213 SELECT bigint((-9223372036854775808)) * int((-1));
0214 SELECT bigint((-9223372036854775808)) / int((-1));
0215 SELECT bigint((-9223372036854775808)) % int((-1));
0216 SELECT bigint((-9223372036854775808)) * smallint((-1));
0217 SELECT bigint((-9223372036854775808)) / smallint((-1));
0218 SELECT bigint((-9223372036854775808)) % smallint((-1));
0219
0220 -- [SPARK-28028] Cast numeric to integral type need round
0221 -- check rounding when casting from float
0222 SELECT x, bigint(x) AS int8_value
0223 FROM (VALUES (double(-2.5)),
0224              (double(-1.5)),
0225              (double(-0.5)),
0226              (double(0.0)),
0227              (double(0.5)),
0228              (double(1.5)),
0229              (double(2.5))) t(x);
0230
0231 -- check rounding when casting from numeric
0232 SELECT x, bigint(x) AS int8_value
0233 FROM (VALUES cast(-2.5 as decimal(38, 18)),
0234              cast(-1.5 as decimal(38, 18)),
0235              cast(-0.5 as decimal(38, 18)),
0236              cast(-0.0 as decimal(38, 18)),
0237              cast(0.5 as decimal(38, 18)),
0238              cast(1.5 as decimal(38, 18)),
0239              cast(2.5 as decimal(38, 18))) t(x);
0240
0241 DROP TABLE INT8_TBL;