inputs/postgreSQL/int4.sql

0001 --
0002 -- Portions Copyright (c) 1996-2019, PostgreSQL Global Development Group
0003 --
0004 --
0005 -- INT4
0006 -- https://github.com/postgres/postgres/blob/REL_12_BETA2/src/test/regress/sql/int4.sql
0007 --
0008
0009 CREATE TABLE INT4_TBL(f1 int) USING parquet;
0010
0011 -- [SPARK-28023] Trim the string when cast string type to other types
0012 -- PostgreSQL implicitly casts string literals to data with integral types, but
0013 -- Spark does not support that kind of implicit casts.
0014 INSERT INTO INT4_TBL VALUES (int(trim('   0  ')));
0015
0016 INSERT INTO INT4_TBL VALUES (int(trim('123456     ')));
0017
0018 INSERT INTO INT4_TBL VALUES (int(trim('    -123456')));
0019
0020 -- [SPARK-27923] Invalid input syntax for integer: "34.5" at PostgreSQL
0021 -- INSERT INTO INT4_TBL(f1) VALUES ('34.5');
0022
0023 -- largest and smallest values
0024 -- PostgreSQL implicitly casts string literals to data with integral types, but
0025 -- Spark does not support that kind of implicit casts.
0026 INSERT INTO INT4_TBL VALUES (int('2147483647'));
0027
0028 INSERT INTO INT4_TBL VALUES (int('-2147483647'));
0029
0030 -- [SPARK-27923] Spark SQL insert these bad inputs to NULL
0031 -- bad input values
0032 -- INSERT INTO INT4_TBL(f1) VALUES ('1000000000000');
0033 -- INSERT INTO INT4_TBL(f1) VALUES ('asdf');
0034 -- INSERT INTO INT4_TBL(f1) VALUES ('     ');
0035 -- INSERT INTO INT4_TBL(f1) VALUES ('   asdf   ');
0036 -- INSERT INTO INT4_TBL(f1) VALUES ('- 1234');
0037 -- INSERT INTO INT4_TBL(f1) VALUES ('123       5');
0038 -- INSERT INTO INT4_TBL(f1) VALUES ('');
0039
0040 SELECT '' AS five, * FROM INT4_TBL;
0041
0042 SELECT '' AS four, i.* FROM INT4_TBL i WHERE i.f1 <> smallint('0');
0043
0044 SELECT '' AS four, i.* FROM INT4_TBL i WHERE i.f1 <> int('0');
0045
0046 SELECT '' AS one, i.* FROM INT4_TBL i WHERE i.f1 = smallint('0');
0047
0048 SELECT '' AS one, i.* FROM INT4_TBL i WHERE i.f1 = int('0');
0049
0050 SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 < smallint('0');
0051
0052 SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 < int('0');
0053
0054 SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 <= smallint('0');
0055
0056 SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 <= int('0');
0057
0058 SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 > smallint('0');
0059
0060 SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 > int('0');
0061
0062 SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 >= smallint('0');
0063
0064 SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 >= int('0');
0065
0066 -- positive odds
0067 SELECT '' AS one, i.* FROM INT4_TBL i WHERE (i.f1 % smallint('2')) = smallint('1');
0068
0069 -- any evens
0070 SELECT '' AS three, i.* FROM INT4_TBL i WHERE (i.f1 % int('2')) = smallint('0');
0071
0072 -- [SPARK-28024] Incorrect value when out of range
0073 SELECT '' AS five, i.f1, i.f1 * smallint('2') AS x FROM INT4_TBL i;
0074
0075 SELECT '' AS five, i.f1, i.f1 * smallint('2') AS x FROM INT4_TBL i
0076 WHERE abs(f1) < 1073741824;
0077
0078 -- [SPARK-28024] Incorrect value when out of range
0079 SELECT '' AS five, i.f1, i.f1 * int('2') AS x FROM INT4_TBL i;
0080
0081 SELECT '' AS five, i.f1, i.f1 * int('2') AS x FROM INT4_TBL i
0082 WHERE abs(f1) < 1073741824;
0083
0084 -- [SPARK-28024] Incorrect value when out of range
0085 SELECT '' AS five, i.f1, i.f1 + smallint('2') AS x FROM INT4_TBL i;
0086
0087 SELECT '' AS five, i.f1, i.f1 + smallint('2') AS x FROM INT4_TBL i
0088 WHERE f1 < 2147483646;
0089
0090 -- [SPARK-28024] Incorrect value when out of range
0091 SELECT '' AS five, i.f1, i.f1 + int('2') AS x FROM INT4_TBL i;
0092
0093 SELECT '' AS five, i.f1, i.f1 + int('2') AS x FROM INT4_TBL i
0094 WHERE f1 < 2147483646;
0095
0096 -- [SPARK-28024] Incorrect value when out of range
0097 SELECT '' AS five, i.f1, i.f1 - smallint('2') AS x FROM INT4_TBL i;
0098
0099 SELECT '' AS five, i.f1, i.f1 - smallint('2') AS x FROM INT4_TBL i
0100 WHERE f1 > -2147483647;
0101
0102 -- [SPARK-28024] Incorrect value when out of range
0103 SELECT '' AS five, i.f1, i.f1 - int('2') AS x FROM INT4_TBL i;
0104
0105 SELECT '' AS five, i.f1, i.f1 - int('2') AS x FROM INT4_TBL i
0106 WHERE f1 > -2147483647;
0107
0108 SELECT '' AS five, i.f1, i.f1 / smallint('2') AS x FROM INT4_TBL i;
0109
0110 SELECT '' AS five, i.f1, i.f1 / int('2') AS x FROM INT4_TBL i;
0111
0112 --
0113 -- more complex expressions
0114 --
0115
0116 -- variations on unary minus parsing
0117 SELECT -2+3 AS one;
0118
0119 SELECT 4-2 AS two;
0120
0121 SELECT 2- -1 AS three;
0122
0123 SELECT 2 - -2 AS four;
0124
0125 SELECT smallint('2') * smallint('2') = smallint('16') / smallint('4') AS true;
0126
0127 SELECT int('2') * smallint('2') = smallint('16') / int('4') AS true;
0128
0129 SELECT smallint('2') * int('2') = int('16') / smallint('4') AS true;
0130
0131 -- [SPARK-28349] We do not need to follow PostgreSQL to support reserved words in column alias
0132 SELECT int('1000') < int('999') AS `false`;
0133
0134 -- [SPARK-28027] Our ! and !! has different meanings
0135 -- SELECT 4! AS twenty_four;
0136
0137 -- SELECT !!3 AS six;
0138
0139 SELECT 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 AS ten;
0140
0141 SELECT 2 + 2 / 2 AS three;
0142
0143 SELECT (2 + 2) / 2 AS two;
0144
0145 -- [SPARK-28027] Add bitwise shift left/right operators
0146 -- corner case
0147 SELECT string(shiftleft(int(-1), 31));
0148 SELECT string(int(shiftleft(int(-1), 31))+1);
0149
0150 -- [SPARK-28024] Incorrect numeric values when out of range
0151 -- check sane handling of INT_MIN overflow cases
0152 -- SELECT (-2147483648)::int4 * (-1)::int4;
0153 -- SELECT (-2147483648)::int4 / (-1)::int4;
0154 SELECT int(-2147483648) % int(-1);
0155 -- SELECT (-2147483648)::int4 * (-1)::int2;
0156 -- SELECT (-2147483648)::int4 / (-1)::int2;
0157 SELECT int(-2147483648) % smallint(-1);
0158
0159 -- [SPARK-28028] Cast numeric to integral type need round
0160 -- check rounding when casting from float
0161 SELECT x, int(x) AS int4_value
0162 FROM (VALUES double(-2.5),
0163              double(-1.5),
0164              double(-0.5),
0165              double(0.0),
0166              double(0.5),
0167              double(1.5),
0168              double(2.5)) t(x);
0169
0170 -- [SPARK-28028] Cast numeric to integral type need round
0171 -- check rounding when casting from numeric
0172 SELECT x, int(x) AS int4_value
0173 FROM (VALUES cast(-2.5 as decimal(38, 18)),
0174              cast(-1.5 as decimal(38, 18)),
0175              cast(-0.5 as decimal(38, 18)),
0176              cast(-0.0 as decimal(38, 18)),
0177              cast(0.5 as decimal(38, 18)),
0178              cast(1.5 as decimal(38, 18)),
0179              cast(2.5 as decimal(38, 18))) t(x);
0180
0181 DROP TABLE INT4_TBL;