inputs/postgreSQL/int2.sql

0001 --
0002 -- Portions Copyright (c) 1996-2019, PostgreSQL Global Development Group
0003 --
0004 --
0005 -- INT2
0006 -- https://github.com/postgres/postgres/blob/REL_12_BETA2/src/test/regress/sql/int2.sql
0007
0008 CREATE TABLE INT2_TBL(f1 smallint) USING parquet;
0009
0010 -- [SPARK-28023] Trim the string when cast string type to other types
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 INT2_TBL VALUES (smallint(trim('0   ')));
0014
0015 INSERT INTO INT2_TBL VALUES (smallint(trim('  1234 ')));
0016
0017 INSERT INTO INT2_TBL VALUES (smallint(trim('    -1234')));
0018
0019 -- [SPARK-27923] Invalid input syntax for type short throws exception at PostgreSQL
0020 -- INSERT INTO INT2_TBL VALUES ('34.5');
0021
0022 -- largest and smallest values
0023 -- PostgreSQL implicitly casts string literals to data with integral types, but
0024 -- Spark does not support that kind of implicit casts.
0025 INSERT INTO INT2_TBL VALUES (smallint('32767'));
0026
0027 INSERT INTO INT2_TBL VALUES (smallint('-32767'));
0028
0029 -- bad input values -- should give errors
0030 -- INSERT INTO INT2_TBL VALUES ('100000');
0031 -- INSERT INTO INT2_TBL VALUES ('asdf');
0032 -- INSERT INTO INT2_TBL VALUES ('    ');
0033 -- INSERT INTO INT2_TBL VALUES ('- 1234');
0034 -- INSERT INTO INT2_TBL VALUES ('4 444');
0035 -- INSERT INTO INT2_TBL VALUES ('123 dt');
0036 -- INSERT INTO INT2_TBL VALUES ('');
0037
0038
0039 SELECT '' AS five, * FROM INT2_TBL;
0040
0041 SELECT '' AS four, i.* FROM INT2_TBL i WHERE i.f1 <> smallint('0');
0042
0043 SELECT '' AS four, i.* FROM INT2_TBL i WHERE i.f1 <> int('0');
0044
0045 SELECT '' AS one, i.* FROM INT2_TBL i WHERE i.f1 = smallint('0');
0046
0047 SELECT '' AS one, i.* FROM INT2_TBL i WHERE i.f1 = int('0');
0048
0049 SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 < smallint('0');
0050
0051 SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 < int('0');
0052
0053 SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 <= smallint('0');
0054
0055 SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 <= int('0');
0056
0057 SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 > smallint('0');
0058
0059 SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 > int('0');
0060
0061 SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 >= smallint('0');
0062
0063 SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 >= int('0');
0064
0065 -- positive odds
0066 SELECT '' AS one, i.* FROM INT2_TBL i WHERE (i.f1 % smallint('2')) = smallint('1');
0067
0068 -- any evens
0069 SELECT '' AS three, i.* FROM INT2_TBL i WHERE (i.f1 % int('2')) = smallint('0');
0070
0071 -- [SPARK-28024] Incorrect value when out of range
0072 -- SELECT '' AS five, i.f1, i.f1 * smallint('2') AS x FROM INT2_TBL i;
0073
0074 SELECT '' AS five, i.f1, i.f1 * smallint('2') AS x FROM INT2_TBL i
0075 WHERE abs(f1) < 16384;
0076
0077 SELECT '' AS five, i.f1, i.f1 * int('2') AS x FROM INT2_TBL i;
0078
0079 -- [SPARK-28024] Incorrect value when out of range
0080 -- SELECT '' AS five, i.f1, i.f1 + smallint('2') AS x FROM INT2_TBL i;
0081
0082 SELECT '' AS five, i.f1, i.f1 + smallint('2') AS x FROM INT2_TBL i
0083 WHERE f1 < 32766;
0084
0085 SELECT '' AS five, i.f1, i.f1 + int('2') AS x FROM INT2_TBL i;
0086
0087 -- [SPARK-28024] Incorrect value when out of range
0088 -- SELECT '' AS five, i.f1, i.f1 - smallint('2') AS x FROM INT2_TBL i;
0089
0090 SELECT '' AS five, i.f1, i.f1 - smallint('2') AS x FROM INT2_TBL i
0091 WHERE f1 > -32767;
0092
0093 SELECT '' AS five, i.f1, i.f1 - int('2') AS x FROM INT2_TBL i;
0094
0095 SELECT '' AS five, i.f1, i.f1 / smallint('2') AS x FROM INT2_TBL i;
0096
0097 SELECT '' AS five, i.f1, i.f1 / int('2') AS x FROM INT2_TBL i;
0098
0099 -- corner cases
0100 SELECT string(shiftleft(smallint(-1), 15));
0101 SELECT string(smallint(shiftleft(smallint(-1), 15))+1);
0102
0103 -- check sane handling of INT16_MIN overflow cases
0104 -- [SPARK-28024] Incorrect numeric values when out of range
0105 -- SELECT smallint((-32768)) * smallint(-1);
0106 -- SELECT smallint(-32768) / smallint(-1);
0107 SELECT smallint(-32768) % smallint(-1);
0108
0109 -- [SPARK-28028] Cast numeric to integral type need round
0110 -- check rounding when casting from float
0111 SELECT x, smallint(x) AS int2_value
0112 FROM (VALUES float(-2.5),
0113              float(-1.5),
0114              float(-0.5),
0115              float(0.0),
0116              float(0.5),
0117              float(1.5),
0118              float(2.5)) t(x);
0119
0120 -- [SPARK-28028] Cast numeric to integral type need round
0121 -- check rounding when casting from numeric
0122 SELECT x, smallint(x) AS int2_value
0123 FROM (VALUES cast(-2.5 as decimal(38, 18)),
0124              cast(-1.5 as decimal(38, 18)),
0125              cast(-0.5 as decimal(38, 18)),
0126              cast(-0.0 as decimal(38, 18)),
0127              cast(0.5 as decimal(38, 18)),
0128              cast(1.5 as decimal(38, 18)),
0129              cast(2.5 as decimal(38, 18))) t(x);
0130
0131 DROP TABLE INT2_TBL;