-- -- INT4 -- CREATE EXTENSION sqlite_fdw; CREATE SERVER sqlite_svr FOREIGN DATA WRAPPER sqlite_fdw OPTIONS (database '/tmp/sqlitefdw_test_core.db'); CREATE FOREIGN TABLE INT4_TBL(f1 int4 OPTIONS (key 'true')) SERVER sqlite_svr; INSERT INTO INT4_TBL(f1) VALUES (' 0 '); INSERT INTO INT4_TBL(f1) VALUES ('123456 '); INSERT INTO INT4_TBL(f1) VALUES (' -123456'); INSERT INTO INT4_TBL(f1) VALUES ('34.5'); ERROR: invalid input syntax for integer: "34.5" LINE 1: INSERT INTO INT4_TBL(f1) VALUES ('34.5'); ^ -- largest and smallest values INSERT INTO INT4_TBL(f1) VALUES ('2147483647'); INSERT INTO INT4_TBL(f1) VALUES ('-2147483647'); -- bad input values -- should give errors INSERT INTO INT4_TBL(f1) VALUES ('1000000000000'); ERROR: value "1000000000000" is out of range for type integer LINE 1: INSERT INTO INT4_TBL(f1) VALUES ('1000000000000'); ^ INSERT INTO INT4_TBL(f1) VALUES ('asdf'); ERROR: invalid input syntax for integer: "asdf" LINE 1: INSERT INTO INT4_TBL(f1) VALUES ('asdf'); ^ INSERT INTO INT4_TBL(f1) VALUES (' '); ERROR: invalid input syntax for integer: " " LINE 1: INSERT INTO INT4_TBL(f1) VALUES (' '); ^ INSERT INTO INT4_TBL(f1) VALUES (' asdf '); ERROR: invalid input syntax for integer: " asdf " LINE 1: INSERT INTO INT4_TBL(f1) VALUES (' asdf '); ^ INSERT INTO INT4_TBL(f1) VALUES ('- 1234'); ERROR: invalid input syntax for integer: "- 1234" LINE 1: INSERT INTO INT4_TBL(f1) VALUES ('- 1234'); ^ INSERT INTO INT4_TBL(f1) VALUES ('123 5'); ERROR: invalid input syntax for integer: "123 5" LINE 1: INSERT INTO INT4_TBL(f1) VALUES ('123 5'); ^ INSERT INTO INT4_TBL(f1) VALUES (''); ERROR: invalid input syntax for integer: "" LINE 1: INSERT INTO INT4_TBL(f1) VALUES (''); ^ SELECT '' AS five, * FROM INT4_TBL; five | f1 ------+------------- | 0 | 123456 | -123456 | 2147483647 | -2147483647 (5 rows) SELECT '' AS four, i.* FROM INT4_TBL i WHERE i.f1 <> int2 '0'; four | f1 ------+------------- | 123456 | -123456 | 2147483647 | -2147483647 (4 rows) SELECT '' AS four, i.* FROM INT4_TBL i WHERE i.f1 <> int4 '0'; four | f1 ------+------------- | 123456 | -123456 | 2147483647 | -2147483647 (4 rows) SELECT '' AS one, i.* FROM INT4_TBL i WHERE i.f1 = int2 '0'; one | f1 -----+---- | 0 (1 row) SELECT '' AS one, i.* FROM INT4_TBL i WHERE i.f1 = int4 '0'; one | f1 -----+---- | 0 (1 row) SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 < int2 '0'; two | f1 -----+------------- | -123456 | -2147483647 (2 rows) SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 < int4 '0'; two | f1 -----+------------- | -123456 | -2147483647 (2 rows) SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 <= int2 '0'; three | f1 -------+------------- | 0 | -123456 | -2147483647 (3 rows) SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 <= int4 '0'; three | f1 -------+------------- | 0 | -123456 | -2147483647 (3 rows) SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 > int2 '0'; two | f1 -----+------------ | 123456 | 2147483647 (2 rows) SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 > int4 '0'; two | f1 -----+------------ | 123456 | 2147483647 (2 rows) SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 >= int2 '0'; three | f1 -------+------------ | 0 | 123456 | 2147483647 (3 rows) SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 >= int4 '0'; three | f1 -------+------------ | 0 | 123456 | 2147483647 (3 rows) -- positive odds SELECT '' AS one, i.* FROM INT4_TBL i WHERE (i.f1 % int2 '2') = int2 '1'; one | f1 -----+------------ | 2147483647 (1 row) -- any evens SELECT '' AS three, i.* FROM INT4_TBL i WHERE (i.f1 % int4 '2') = int2 '0'; three | f1 -------+--------- | 0 | 123456 | -123456 (3 rows) SELECT '' AS five, i.f1, i.f1 * int2 '2' AS x FROM INT4_TBL i; ERROR: integer out of range SELECT '' AS five, i.f1, i.f1 * int2 '2' AS x FROM INT4_TBL i WHERE abs(f1) < 1073741824; five | f1 | x ------+---------+--------- | 0 | 0 | 123456 | 246912 | -123456 | -246912 (3 rows) SELECT '' AS five, i.f1, i.f1 * int4 '2' AS x FROM INT4_TBL i; ERROR: integer out of range SELECT '' AS five, i.f1, i.f1 * int4 '2' AS x FROM INT4_TBL i WHERE abs(f1) < 1073741824; five | f1 | x ------+---------+--------- | 0 | 0 | 123456 | 246912 | -123456 | -246912 (3 rows) SELECT '' AS five, i.f1, i.f1 + int2 '2' AS x FROM INT4_TBL i; ERROR: integer out of range SELECT '' AS five, i.f1, i.f1 + int2 '2' AS x FROM INT4_TBL i WHERE f1 < 2147483646; five | f1 | x ------+-------------+------------- | 0 | 2 | 123456 | 123458 | -123456 | -123454 | -2147483647 | -2147483645 (4 rows) SELECT '' AS five, i.f1, i.f1 + int4 '2' AS x FROM INT4_TBL i; ERROR: integer out of range SELECT '' AS five, i.f1, i.f1 + int4 '2' AS x FROM INT4_TBL i WHERE f1 < 2147483646; five | f1 | x ------+-------------+------------- | 0 | 2 | 123456 | 123458 | -123456 | -123454 | -2147483647 | -2147483645 (4 rows) SELECT '' AS five, i.f1, i.f1 - int2 '2' AS x FROM INT4_TBL i; ERROR: integer out of range SELECT '' AS five, i.f1, i.f1 - int2 '2' AS x FROM INT4_TBL i WHERE f1 > -2147483647; five | f1 | x ------+------------+------------ | 0 | -2 | 123456 | 123454 | -123456 | -123458 | 2147483647 | 2147483645 (4 rows) SELECT '' AS five, i.f1, i.f1 - int4 '2' AS x FROM INT4_TBL i; ERROR: integer out of range SELECT '' AS five, i.f1, i.f1 - int4 '2' AS x FROM INT4_TBL i WHERE f1 > -2147483647; five | f1 | x ------+------------+------------ | 0 | -2 | 123456 | 123454 | -123456 | -123458 | 2147483647 | 2147483645 (4 rows) SELECT '' AS five, i.f1, i.f1 / int2 '2' AS x FROM INT4_TBL i; five | f1 | x ------+-------------+------------- | 0 | 0 | 123456 | 61728 | -123456 | -61728 | 2147483647 | 1073741823 | -2147483647 | -1073741823 (5 rows) SELECT '' AS five, i.f1, i.f1 / int4 '2' AS x FROM INT4_TBL i; five | f1 | x ------+-------------+------------- | 0 | 0 | 123456 | 61728 | -123456 | -61728 | 2147483647 | 1073741823 | -2147483647 | -1073741823 (5 rows) -- -- more complex expressions -- -- variations on unary minus parsing BEGIN; DELETE FROM INT4_TBL; INSERT INTO INT4_TBL VALUES (-2+3); INSERT INTO INT4_TBL VALUES (4-2); INSERT INTO INT4_TBL VALUES (2- -1); INSERT INTO INT4_TBL VALUES (2 - -2); INSERT INTO INT4_TBL VALUES (4!); INSERT INTO INT4_TBL VALUES (!!3); INSERT INTO INT4_TBL VALUES (1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1); INSERT INTO INT4_TBL VALUES (2 + 2 / 2); INSERT INTO INT4_TBL VALUES ((2 + 2) / 2); SELECT * FROM INT4_TBL; f1 ---- 1 2 3 4 24 6 10 3 2 (9 rows) DELETE FROM INT4_TBL; -- corner case INSERT INTO INT4_TBL VALUES ((-1::int4<<31)); INSERT INTO INT4_TBL VALUES (((-1::int4<<31)+1)); SELECT * FROM INT4_TBL; f1 ------------- -2147483648 -2147483647 (2 rows) ROLLBACK; -- check sane handling of INT_MIN overflow cases INSERT INTO INT4_TBL VALUES ((-2147483648)::int4 * (-1)::int4); ERROR: integer out of range INSERT INTO INT4_TBL VALUES ((-2147483648)::int4 / (-1)::int4); ERROR: integer out of range INSERT INTO INT4_TBL VALUES ((-2147483648)::int4 * (-1)::int2); ERROR: integer out of range INSERT INTO INT4_TBL VALUES ((-2147483648)::int4 / (-1)::int2); ERROR: integer out of range DROP FOREIGN TABLE INT4_TBL; DROP SERVER sqlite_svr; DROP EXTENSION sqlite_fdw CASCADE;