--
-- INT8
-- Test int8 64-bit integers.
--
--Testcase 140:
CREATE EXTENSION sqlite_fdw;
--Testcase 141:
CREATE SERVER sqlite_svr FOREIGN DATA WRAPPER sqlite_fdw
OPTIONS (database '/tmp/sqlitefdw_test_core.db');
--Testcase 142:
CREATE FOREIGN TABLE INT8_TBL(
	q1 int8 OPTIONS (key 'true'),
	q2 int8 OPTIONS (key 'true')
) SERVER sqlite_svr;
--Testcase 143:
CREATE FOREIGN TABLE INT8_TMP(
	q1 int8,
	q2 int8,
	q3 int4, 
	q4 int2,
	q5 text,
	id int options (key 'true')
) SERVER sqlite_svr;
--Testcase 1:
INSERT INTO INT8_TBL VALUES('  123   ','  456');
--Testcase 2:
INSERT INTO INT8_TBL VALUES('123   ','4567890123456789');
--Testcase 3:
INSERT INTO INT8_TBL VALUES('4567890123456789','123');
--Testcase 4:
INSERT INTO INT8_TBL VALUES(+4567890123456789,'4567890123456789');
--Testcase 5:
INSERT INTO INT8_TBL VALUES('+4567890123456789','-4567890123456789');
-- bad inputs
--Testcase 6:
INSERT INTO INT8_TBL(q1) VALUES ('      ');
ERROR:  invalid input syntax for type bigint: "      "
LINE 1: INSERT INTO INT8_TBL(q1) VALUES ('      ');
                                         ^
--Testcase 7:
INSERT INTO INT8_TBL(q1) VALUES ('xxx');
ERROR:  invalid input syntax for type bigint: "xxx"
LINE 1: INSERT INTO INT8_TBL(q1) VALUES ('xxx');
                                         ^
--Testcase 8:
INSERT INTO INT8_TBL(q1) VALUES ('3908203590239580293850293850329485');
ERROR:  value "3908203590239580293850293850329485" is out of range for type bigint
LINE 1: INSERT INTO INT8_TBL(q1) VALUES ('39082035902395802938502938...
                                         ^
--Testcase 9:
INSERT INTO INT8_TBL(q1) VALUES ('-1204982019841029840928340329840934');
ERROR:  value "-1204982019841029840928340329840934" is out of range for type bigint
LINE 1: INSERT INTO INT8_TBL(q1) VALUES ('-1204982019841029840928340...
                                         ^
--Testcase 10:
INSERT INTO INT8_TBL(q1) VALUES ('- 123');
ERROR:  invalid input syntax for type bigint: "- 123"
LINE 1: INSERT INTO INT8_TBL(q1) VALUES ('- 123');
                                         ^
--Testcase 11:
INSERT INTO INT8_TBL(q1) VALUES ('  345     5');
ERROR:  invalid input syntax for type bigint: "  345     5"
LINE 1: INSERT INTO INT8_TBL(q1) VALUES ('  345     5');
                                         ^
--Testcase 12:
INSERT INTO INT8_TBL(q1) VALUES ('');
ERROR:  invalid input syntax for type bigint: ""
LINE 1: INSERT INTO INT8_TBL(q1) VALUES ('');
                                         ^
--Testcase 13:
SELECT * FROM INT8_TBL;
        q1        |        q2         
------------------+-------------------
              123 |               456
              123 |  4567890123456789
 4567890123456789 |               123
 4567890123456789 |  4567890123456789
 4567890123456789 | -4567890123456789
(5 rows)

-- int8/int8 cmp
--Testcase 14:
SELECT * FROM INT8_TBL WHERE q2 = 4567890123456789;
        q1        |        q2        
------------------+------------------
              123 | 4567890123456789
 4567890123456789 | 4567890123456789
(2 rows)

--Testcase 15:
SELECT * FROM INT8_TBL WHERE q2 <> 4567890123456789;
        q1        |        q2         
------------------+-------------------
              123 |               456
 4567890123456789 |               123
 4567890123456789 | -4567890123456789
(3 rows)

--Testcase 16:
SELECT * FROM INT8_TBL WHERE q2 < 4567890123456789;
        q1        |        q2         
------------------+-------------------
              123 |               456
 4567890123456789 |               123
 4567890123456789 | -4567890123456789
(3 rows)

--Testcase 17:
SELECT * FROM INT8_TBL WHERE q2 > 4567890123456789;
 q1 | q2 
----+----
(0 rows)

--Testcase 18:
SELECT * FROM INT8_TBL WHERE q2 <= 4567890123456789;
        q1        |        q2         
------------------+-------------------
              123 |               456
              123 |  4567890123456789
 4567890123456789 |               123
 4567890123456789 |  4567890123456789
 4567890123456789 | -4567890123456789
(5 rows)

--Testcase 19:
SELECT * FROM INT8_TBL WHERE q2 >= 4567890123456789;
        q1        |        q2        
------------------+------------------
              123 | 4567890123456789
 4567890123456789 | 4567890123456789
(2 rows)

-- int8/int4 cmp
--Testcase 20:
SELECT * FROM INT8_TBL WHERE q2 = 456;
 q1  | q2  
-----+-----
 123 | 456
(1 row)

--Testcase 21:
SELECT * FROM INT8_TBL WHERE q2 <> 456;
        q1        |        q2         
------------------+-------------------
              123 |  4567890123456789
 4567890123456789 |               123
 4567890123456789 |  4567890123456789
 4567890123456789 | -4567890123456789
(4 rows)

--Testcase 22:
SELECT * FROM INT8_TBL WHERE q2 < 456;
        q1        |        q2         
------------------+-------------------
 4567890123456789 |               123
 4567890123456789 | -4567890123456789
(2 rows)

--Testcase 23:
SELECT * FROM INT8_TBL WHERE q2 > 456;
        q1        |        q2        
------------------+------------------
              123 | 4567890123456789
 4567890123456789 | 4567890123456789
(2 rows)

--Testcase 24:
SELECT * FROM INT8_TBL WHERE q2 <= 456;
        q1        |        q2         
------------------+-------------------
              123 |               456
 4567890123456789 |               123
 4567890123456789 | -4567890123456789
(3 rows)

--Testcase 25:
SELECT * FROM INT8_TBL WHERE q2 >= 456;
        q1        |        q2        
------------------+------------------
              123 |              456
              123 | 4567890123456789
 4567890123456789 | 4567890123456789
(3 rows)

-- int4/int8 cmp
--Testcase 26:
SELECT * FROM INT8_TBL WHERE 123 = q1;
 q1  |        q2        
-----+------------------
 123 |              456
 123 | 4567890123456789
(2 rows)

--Testcase 27:
SELECT * FROM INT8_TBL WHERE 123 <> q1;
        q1        |        q2         
------------------+-------------------
 4567890123456789 |               123
 4567890123456789 |  4567890123456789
 4567890123456789 | -4567890123456789
(3 rows)

--Testcase 28:
SELECT * FROM INT8_TBL WHERE 123 < q1;
        q1        |        q2         
------------------+-------------------
 4567890123456789 | -4567890123456789
 4567890123456789 |               123
 4567890123456789 |  4567890123456789
(3 rows)

--Testcase 29:
SELECT * FROM INT8_TBL WHERE 123 > q1;
 q1 | q2 
----+----
(0 rows)

--Testcase 30:
SELECT * FROM INT8_TBL WHERE 123 <= q1;
        q1        |        q2         
------------------+-------------------
              123 |               456
              123 |  4567890123456789
 4567890123456789 | -4567890123456789
 4567890123456789 |               123
 4567890123456789 |  4567890123456789
(5 rows)

--Testcase 31:
SELECT * FROM INT8_TBL WHERE 123 >= q1;
 q1  |        q2        
-----+------------------
 123 |              456
 123 | 4567890123456789
(2 rows)

-- int8/int2 cmp
--Testcase 32:
SELECT * FROM INT8_TBL WHERE q2 = '456'::int2;
 q1  | q2  
-----+-----
 123 | 456
(1 row)

--Testcase 33:
SELECT * FROM INT8_TBL WHERE q2 <> '456'::int2;
        q1        |        q2         
------------------+-------------------
              123 |  4567890123456789
 4567890123456789 |               123
 4567890123456789 |  4567890123456789
 4567890123456789 | -4567890123456789
(4 rows)

--Testcase 34:
SELECT * FROM INT8_TBL WHERE q2 < '456'::int2;
        q1        |        q2         
------------------+-------------------
 4567890123456789 |               123
 4567890123456789 | -4567890123456789
(2 rows)

--Testcase 35:
SELECT * FROM INT8_TBL WHERE q2 > '456'::int2;
        q1        |        q2        
------------------+------------------
              123 | 4567890123456789
 4567890123456789 | 4567890123456789
(2 rows)

--Testcase 36:
SELECT * FROM INT8_TBL WHERE q2 <= '456'::int2;
        q1        |        q2         
------------------+-------------------
              123 |               456
 4567890123456789 |               123
 4567890123456789 | -4567890123456789
(3 rows)

--Testcase 37:
SELECT * FROM INT8_TBL WHERE q2 >= '456'::int2;
        q1        |        q2        
------------------+------------------
              123 |              456
              123 | 4567890123456789
 4567890123456789 | 4567890123456789
(3 rows)

-- int2/int8 cmp
--Testcase 38:
SELECT * FROM INT8_TBL WHERE '123'::int2 = q1;
 q1  |        q2        
-----+------------------
 123 |              456
 123 | 4567890123456789
(2 rows)

--Testcase 39:
SELECT * FROM INT8_TBL WHERE '123'::int2 <> q1;
        q1        |        q2         
------------------+-------------------
 4567890123456789 |               123
 4567890123456789 |  4567890123456789
 4567890123456789 | -4567890123456789
(3 rows)

--Testcase 40:
SELECT * FROM INT8_TBL WHERE '123'::int2 < q1;
        q1        |        q2         
------------------+-------------------
 4567890123456789 | -4567890123456789
 4567890123456789 |               123
 4567890123456789 |  4567890123456789
(3 rows)

--Testcase 41:
SELECT * FROM INT8_TBL WHERE '123'::int2 > q1;
 q1 | q2 
----+----
(0 rows)

--Testcase 42:
SELECT * FROM INT8_TBL WHERE '123'::int2 <= q1;
        q1        |        q2         
------------------+-------------------
              123 |               456
              123 |  4567890123456789
 4567890123456789 | -4567890123456789
 4567890123456789 |               123
 4567890123456789 |  4567890123456789
(5 rows)

--Testcase 43:
SELECT * FROM INT8_TBL WHERE '123'::int2 >= q1;
 q1  |        q2        
-----+------------------
 123 |              456
 123 | 4567890123456789
(2 rows)

--Testcase 44:
SELECT q1 AS plus, -q1 AS minus FROM INT8_TBL;
       plus       |       minus       
------------------+-------------------
              123 |              -123
              123 |              -123
 4567890123456789 | -4567890123456789
 4567890123456789 | -4567890123456789
 4567890123456789 | -4567890123456789
(5 rows)

--Testcase 45:
SELECT q1, q2, q1 + q2 AS plus FROM INT8_TBL;
        q1        |        q2         |       plus       
------------------+-------------------+------------------
              123 |               456 |              579
              123 |  4567890123456789 | 4567890123456912
 4567890123456789 |               123 | 4567890123456912
 4567890123456789 |  4567890123456789 | 9135780246913578
 4567890123456789 | -4567890123456789 |                0
(5 rows)

--Testcase 46:
SELECT q1, q2, q1 - q2 AS minus FROM INT8_TBL;
        q1        |        q2         |       minus       
------------------+-------------------+-------------------
              123 |               456 |              -333
              123 |  4567890123456789 | -4567890123456666
 4567890123456789 |               123 |  4567890123456666
 4567890123456789 |  4567890123456789 |                 0
 4567890123456789 | -4567890123456789 |  9135780246913578
(5 rows)

--Testcase 47:
SELECT q1, q2, q1 * q2 AS multiply FROM INT8_TBL;
ERROR:  bigint out of range
--Testcase 48:
SELECT q1, q2, q1 * q2 AS multiply FROM INT8_TBL
 WHERE q1 < 1000 or (q2 > 0 and q2 < 1000);
        q1        |        q2        |      multiply      
------------------+------------------+--------------------
              123 |              456 |              56088
              123 | 4567890123456789 | 561850485185185047
 4567890123456789 |              123 | 561850485185185047
(3 rows)

--Testcase 49:
SELECT q1, q2, q1 / q2 AS divide, q1 % q2 AS mod FROM INT8_TBL;
        q1        |        q2         |     divide     | mod 
------------------+-------------------+----------------+-----
              123 |               456 |              0 | 123
              123 |  4567890123456789 |              0 | 123
 4567890123456789 |               123 | 37137318076884 |  57
 4567890123456789 |  4567890123456789 |              1 |   0
 4567890123456789 | -4567890123456789 |             -1 |   0
(5 rows)

--Testcase 50:
SELECT q1, float8(q1) FROM INT8_TBL;
        q1        |        float8         
------------------+-----------------------
              123 |                   123
              123 |                   123
 4567890123456789 | 4.567890123456789e+15
 4567890123456789 | 4.567890123456789e+15
 4567890123456789 | 4.567890123456789e+15
(5 rows)

--Testcase 51:
SELECT q2, float8(q2) FROM INT8_TBL;
        q2         |         float8         
-------------------+------------------------
               456 |                    456
  4567890123456789 |  4.567890123456789e+15
               123 |                    123
  4567890123456789 |  4.567890123456789e+15
 -4567890123456789 | -4.567890123456789e+15
(5 rows)

--Testcase 52:
SELECT 37 + q1 AS plus4 FROM INT8_TBL;
      plus4       
------------------
              160
              160
 4567890123456826
 4567890123456826
 4567890123456826
(5 rows)

--Testcase 53:
SELECT 37 - q1 AS minus4 FROM INT8_TBL;
      minus4       
-------------------
               -86
               -86
 -4567890123456752
 -4567890123456752
 -4567890123456752
(5 rows)

--Testcase 54:
SELECT 2 * q1 AS "twice int4" FROM INT8_TBL;
    twice int4    
------------------
              246
              246
 9135780246913578
 9135780246913578
 9135780246913578
(5 rows)

--Testcase 55:
SELECT q1 * 2 AS "twice int4" FROM INT8_TBL;
    twice int4    
------------------
              246
              246
 9135780246913578
 9135780246913578
 9135780246913578
(5 rows)

-- int8 op int4
--Testcase 56:
SELECT q1 + 42::int4 AS "8plus4", q1 - 42::int4 AS "8minus4", q1 * 42::int4 AS "8mul4", q1 / 42::int4 AS "8div4" FROM INT8_TBL;
      8plus4      |     8minus4      |       8mul4        |      8div4      
------------------+------------------+--------------------+-----------------
              165 |               81 |               5166 |               2
              165 |               81 |               5166 |               2
 4567890123456831 | 4567890123456747 | 191851385185185138 | 108759288653733
 4567890123456831 | 4567890123456747 | 191851385185185138 | 108759288653733
 4567890123456831 | 4567890123456747 | 191851385185185138 | 108759288653733
(5 rows)

-- int4 op int8
--Testcase 57:
SELECT 246::int4 + q1 AS "4plus8", 246::int4 - q1 AS "4minus8", 246::int4 * q1 AS "4mul8", 246::int4 / q1 AS "4div8" FROM INT8_TBL;
      4plus8      |      4minus8      |        4mul8        | 4div8 
------------------+-------------------+---------------------+-------
              369 |               123 |               30258 |     2
              369 |               123 |               30258 |     2
 4567890123457035 | -4567890123456543 | 1123700970370370094 |     0
 4567890123457035 | -4567890123456543 | 1123700970370370094 |     0
 4567890123457035 | -4567890123456543 | 1123700970370370094 |     0
(5 rows)

-- int8 op int2
--Testcase 58:
SELECT q1 + 42::int2 AS "8plus2", q1 - 42::int2 AS "8minus2", q1 * 42::int2 AS "8mul2", q1 / 42::int2 AS "8div2" FROM INT8_TBL;
      8plus2      |     8minus2      |       8mul2        |      8div2      
------------------+------------------+--------------------+-----------------
              165 |               81 |               5166 |               2
              165 |               81 |               5166 |               2
 4567890123456831 | 4567890123456747 | 191851385185185138 | 108759288653733
 4567890123456831 | 4567890123456747 | 191851385185185138 | 108759288653733
 4567890123456831 | 4567890123456747 | 191851385185185138 | 108759288653733
(5 rows)

-- int2 op int8
--Testcase 59:
SELECT 246::int2 + q1 AS "2plus8", 246::int2 - q1 AS "2minus8", 246::int2 * q1 AS "2mul8", 246::int2 / q1 AS "2div8" FROM INT8_TBL;
      2plus8      |      2minus8      |        2mul8        | 2div8 
------------------+-------------------+---------------------+-------
              369 |               123 |               30258 |     2
              369 |               123 |               30258 |     2
 4567890123457035 | -4567890123456543 | 1123700970370370094 |     0
 4567890123457035 | -4567890123456543 | 1123700970370370094 |     0
 4567890123457035 | -4567890123456543 | 1123700970370370094 |     0
(5 rows)

--Testcase 60:
SELECT q2, abs(q2) FROM INT8_TBL;
        q2         |       abs        
-------------------+------------------
               456 |              456
  4567890123456789 | 4567890123456789
               123 |              123
  4567890123456789 | 4567890123456789
 -4567890123456789 | 4567890123456789
(5 rows)

--Testcase 61:
SELECT min(q1), min(q2) FROM INT8_TBL;
 min |        min        
-----+-------------------
 123 | -4567890123456789
(1 row)

--Testcase 62:
SELECT max(q1), max(q2) FROM INT8_TBL;
       max        |       max        
------------------+------------------
 4567890123456789 | 4567890123456789
(1 row)

-- TO_CHAR()
--
--Testcase 63:
SELECT to_char(q1, '9G999G999G999G999G999'), to_char(q2, '9,999,999,999,999,999')
	FROM INT8_TBL;
        to_char         |        to_char         
------------------------+------------------------
                    123 |                    456
                    123 |  4,567,890,123,456,789
  4,567,890,123,456,789 |                    123
  4,567,890,123,456,789 |  4,567,890,123,456,789
  4,567,890,123,456,789 | -4,567,890,123,456,789
(5 rows)

--Testcase 64:
SELECT to_char(q1, '9G999G999G999G999G999D999G999'), to_char(q2, '9,999,999,999,999,999.999,999')
	FROM INT8_TBL;
            to_char             |            to_char             
--------------------------------+--------------------------------
                    123.000,000 |                    456.000,000
                    123.000,000 |  4,567,890,123,456,789.000,000
  4,567,890,123,456,789.000,000 |                    123.000,000
  4,567,890,123,456,789.000,000 |  4,567,890,123,456,789.000,000
  4,567,890,123,456,789.000,000 | -4,567,890,123,456,789.000,000
(5 rows)

--Testcase 65:
SELECT to_char( (q1 * -1), '9999999999999999PR'), to_char( (q2 * -1), '9999999999999999.999PR')
	FROM INT8_TBL;
      to_char       |        to_char         
--------------------+------------------------
              <123> |              <456.000>
              <123> | <4567890123456789.000>
 <4567890123456789> |              <123.000>
 <4567890123456789> | <4567890123456789.000>
 <4567890123456789> |  4567890123456789.000 
(5 rows)

--Testcase 66:
SELECT to_char( (q1 * -1), '9999999999999999S'), to_char( (q2 * -1), 'S9999999999999999')
	FROM INT8_TBL;
      to_char      |      to_char      
-------------------+-------------------
              123- |              -456
              123- | -4567890123456789
 4567890123456789- |              -123
 4567890123456789- | -4567890123456789
 4567890123456789- | +4567890123456789
(5 rows)

--Testcase 67:
SELECT to_char(q2, 'MI9999999999999999')     FROM INT8_TBL;
      to_char      
-------------------
               456
  4567890123456789
               123
  4567890123456789
 -4567890123456789
(5 rows)

--Testcase 68:
SELECT to_char(q2, 'FMS9999999999999999')    FROM INT8_TBL;
      to_char      
-------------------
 +456
 +4567890123456789
 +123
 +4567890123456789
 -4567890123456789
(5 rows)

--Testcase 69:
SELECT to_char(q2, 'FM9999999999999999THPR') FROM INT8_TBL;
      to_char       
--------------------
 456TH
 4567890123456789TH
 123RD
 4567890123456789TH
 <4567890123456789>
(5 rows)

--Testcase 70:
SELECT to_char(q2, 'SG9999999999999999th')   FROM INT8_TBL;
       to_char       
---------------------
 +             456th
 +4567890123456789th
 +             123rd
 +4567890123456789th
 -4567890123456789
(5 rows)

--Testcase 71:
SELECT to_char(q2, '0999999999999999')       FROM INT8_TBL;
      to_char      
-------------------
  0000000000000456
  4567890123456789
  0000000000000123
  4567890123456789
 -4567890123456789
(5 rows)

--Testcase 72:
SELECT to_char(q2, 'S0999999999999999')      FROM INT8_TBL;
      to_char      
-------------------
 +0000000000000456
 +4567890123456789
 +0000000000000123
 +4567890123456789
 -4567890123456789
(5 rows)

--Testcase 73:
SELECT to_char(q2, 'FM0999999999999999')     FROM INT8_TBL;
      to_char      
-------------------
 0000000000000456
 4567890123456789
 0000000000000123
 4567890123456789
 -4567890123456789
(5 rows)

--Testcase 74:
SELECT to_char(q2, 'FM9999999999999999.000') FROM INT8_TBL;
        to_char        
-----------------------
 456.000
 4567890123456789.000
 123.000
 4567890123456789.000
 -4567890123456789.000
(5 rows)

--Testcase 75:
SELECT to_char(q2, 'L9999999999999999.000')  FROM INT8_TBL;
        to_char         
------------------------
                456.000
   4567890123456789.000
                123.000
   4567890123456789.000
  -4567890123456789.000
(5 rows)

--Testcase 76:
SELECT to_char(q2, 'FM9999999999999999.999') FROM INT8_TBL;
      to_char       
--------------------
 456.
 4567890123456789.
 123.
 4567890123456789.
 -4567890123456789.
(5 rows)

--Testcase 77:
SELECT 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;
                  to_char                  
-------------------------------------------
                            +4 5 6 . 0 0 0
  +4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 . 0 0 0
                            +1 2 3 . 0 0 0
  +4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 . 0 0 0
  -4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 . 0 0 0
(5 rows)

--Testcase 78:
SELECT to_char(q2, E'99999 "text" 9999 "9999" 999 "\\"text between quote marks\\"" 9999') FROM INT8_TBL;
                          to_char                          
-----------------------------------------------------------
       text      9999     "text between quote marks"   456
  45678 text 9012 9999 345 "text between quote marks" 6789
       text      9999     "text between quote marks"   123
  45678 text 9012 9999 345 "text between quote marks" 6789
 -45678 text 9012 9999 345 "text between quote marks" 6789
(5 rows)

--Testcase 79:
SELECT to_char(q2, '999999SG9999999999')     FROM INT8_TBL;
      to_char      
-------------------
       +       456
 456789+0123456789
       +       123
 456789+0123456789
 456789-0123456789
(5 rows)

-- check min/max values and overflow behavior
--Testcase 80:
DELETE FROM INT8_TMP;
--Testcase 144:
INSERT INTO INT8_TMP VALUES ('-9223372036854775808'::int8);
--Testcase 145:
SELECT q1 FROM INT8_TMP;
          q1          
----------------------
 -9223372036854775808
(1 row)

--Testcase 81:
DELETE FROM INT8_TMP;
--Testcase 146:
INSERT INTO INT8_TMP VALUES ('-9223372036854775809'::int8);
ERROR:  value "-9223372036854775809" is out of range for type bigint
LINE 1: INSERT INTO INT8_TMP VALUES ('-9223372036854775809'::int8);
                                     ^
--Testcase 147:
SELECT q1 FROM INT8_TMP;
 q1 
----
(0 rows)

--Testcase 82:
DELETE FROM INT8_TMP;
--Testcase 148:
INSERT INTO INT8_TMP VALUES ('9223372036854775807'::int8);
--Testcase 149:
SELECT q1 FROM INT8_TMP;
         q1          
---------------------
 9223372036854775807
(1 row)

--Testcase 83:
DELETE FROM INT8_TMP;
--Testcase 150:
INSERT INTO INT8_TMP VALUES ('9223372036854775808'::int8);
ERROR:  value "9223372036854775808" is out of range for type bigint
LINE 1: INSERT INTO INT8_TMP VALUES ('9223372036854775808'::int8);
                                     ^
--Testcase 151:
SELECT q1 FROM INT8_TMP;
 q1 
----
(0 rows)

--Testcase 84:
DELETE FROM INT8_TMP;
--Testcase 152:
INSERT INTO INT8_TMP VALUES (-('-9223372036854775807'::int8));
--Testcase 153:
SELECT q1 FROM INT8_TMP;
         q1          
---------------------
 9223372036854775807
(1 row)

--Testcase 86:
DELETE FROM INT8_TMP;
--Testcase 154:
INSERT INTO INT8_TMP VALUES (-('-9223372036854775808'::int8));
ERROR:  bigint out of range
--Testcase 155:
SELECT q1 FROM INT8_TMP;
 q1 
----
(0 rows)

--Testcase 87:
DELETE FROM INT8_TMP;
--Testcase 156:
INSERT INTO INT8_TMP VALUES ('9223372036854775800'::int8 , '9223372036854775800'::int8);
--Testcase 157:
SELECT q1 + q2 FROM INT8_TMP;
ERROR:  bigint out of range
--Testcase 88:
DELETE FROM INT8_TMP;
--Testcase 158:
INSERT INTO INT8_TMP VALUES ('-9223372036854775800'::int8 , '-9223372036854775800'::int8);
--Testcase 159:
SELECT q1 + q2 FROM INT8_TMP;
ERROR:  bigint out of range
--Testcase 89:
DELETE FROM INT8_TMP;
--Testcase 160:
INSERT INTO INT8_TMP VALUES ('9223372036854775800'::int8 , '-9223372036854775800'::int8);
--Testcase 161:
SELECT q1-q2 FROM INT8_TMP;
ERROR:  bigint out of range
--Testcase 90:
DELETE FROM INT8_TMP;
--Testcase 162:
INSERT INTO INT8_TMP VALUES ('-9223372036854775800'::int8 , '9223372036854775800'::int8);
--Testcase 163:
SELECT q1 - q2 FROM INT8_TMP;
ERROR:  bigint out of range
--Testcase 91:
DELETE FROM INT8_TMP;
--Testcase 164:
INSERT INTO INT8_TMP VALUES ('9223372036854775800'::int8 , '9223372036854775800'::int8);
--Testcase 165:
SELECT q1 * q2 FROM INT8_TMP;
ERROR:  bigint out of range
--Testcase 92:
DELETE FROM INT8_TMP;
--Testcase 166:
INSERT INTO INT8_TMP VALUES ('9223372036854775800'::int8 , '0'::int8);
--Testcase 167:
SELECT q1 / q2 FROM INT8_TMP;
ERROR:  division by zero
--Testcase 93:
DELETE FROM INT8_TMP;
--Testcase 168:
INSERT INTO INT8_TMP VALUES ('9223372036854775800'::int8 , '0'::int8);
--Testcase 169:
SELECT q1 % q2 FROM INT8_TMP;
ERROR:  division by zero
--Testcase 94:
DELETE FROM INT8_TMP;
--Testcase 170:
INSERT INTO INT8_TMP VALUES ('-9223372036854775808'::int8);
--Testcase 171:
SELECT abs(q1) FROM INT8_TMP;
ERROR:  bigint out of range
--Testcase 95:
DELETE FROM INT8_TMP;
--Testcase 172:
INSERT INTO INT8_TMP(q1, q3) VALUES ('9223372036854775800'::int8 , '100'::int4);
--Testcase 173:
SELECT q1 + q3 FROM INT8_TMP;
ERROR:  bigint out of range
--Testcase 96:
DELETE FROM INT8_TMP;
--Testcase 174:
INSERT INTO INT8_TMP(q1, q3) VALUES ('-9223372036854775800'::int8 , '100'::int4);
--Testcase 175:
SELECT q1 - q3 FROM INT8_TMP;
ERROR:  bigint out of range
--Testcase 97:
DELETE FROM INT8_TMP;
--Testcase 176:
INSERT INTO INT8_TMP(q1, q3) VALUES ('9223372036854775800'::int8 , '100'::int4);
--Testcase 177:
SELECT q1 * q3 FROM INT8_TMP;
ERROR:  bigint out of range
--Testcase 98:
DELETE FROM INT8_TMP;
--Testcase 178:
INSERT INTO INT8_TMP(q3, q1) VALUES ('100'::int4 , '9223372036854775800'::int8);
--Testcase 179:
SELECT q3 + q1 FROM INT8_TMP;
ERROR:  bigint out of range
--Testcase 99:
DELETE FROM INT8_TMP;
--Testcase 180:
INSERT INTO INT8_TMP(q3, q1) VALUES ('-100'::int4 , '9223372036854775800'::int8);
--Testcase 181:
SELECT q3 - q1 FROM INT8_TMP;
ERROR:  bigint out of range
--Testcase 100:
DELETE FROM INT8_TMP;
--Testcase 182:
INSERT INTO INT8_TMP(q3, q1) VALUES ('100'::int4 , '9223372036854775800'::int8);
--Testcase 183:
SELECT q3 * q1 FROM INT8_TMP;
ERROR:  bigint out of range
--Testcase 101:
DELETE FROM INT8_TMP;
--Testcase 184:
INSERT INTO INT8_TMP(q1, q4) VALUES ('9223372036854775800'::int8 , '100'::int2);
--Testcase 185:
SELECT q1 + q4 FROM INT8_TMP;
ERROR:  bigint out of range
--Testcase 102:
DELETE FROM INT8_TMP;
--Testcase 186:
INSERT INTO INT8_TMP(q1, q4) VALUES ('-9223372036854775800'::int8 , '100'::int2);
--Testcase 187:
SELECT q1 - q4 FROM INT8_TMP;
ERROR:  bigint out of range
--Testcase 103:
DELETE FROM INT8_TMP;
--Testcase 188:
INSERT INTO INT8_TMP VALUES ('9223372036854775800'::int8 , '100'::int2);
--Testcase 189:
SELECT q1 * q4 FROM INT8_TMP;
 ?column? 
----------
         
(1 row)

--Testcase 104:
DELETE FROM INT8_TMP;
--Testcase 190:
INSERT INTO INT8_TMP(q1, q4) VALUES ('-9223372036854775808'::int8 , '0'::int2);
--Testcase 191:
SELECT q1 / q4 FROM INT8_TMP;
ERROR:  division by zero
--Testcase 105:
DELETE FROM INT8_TMP;
--Testcase 192:
INSERT INTO INT8_TMP(q4, q1) VALUES ('100'::int2 , '9223372036854775800'::int8);
--Testcase 193:
SELECT q4 + q1 FROM INT8_TMP;
ERROR:  bigint out of range
--Testcase 106:
DELETE FROM INT8_TMP;
--Testcase 194:
INSERT INTO INT8_TMP(q4, q1) VALUES ('-100'::int2 , '9223372036854775800'::int8);
--Testcase 195:
SELECT q4 - q1 FROM INT8_TMP;
ERROR:  bigint out of range
--Testcase 107:
DELETE FROM INT8_TMP;
--Testcase 196:
INSERT INTO INT8_TMP(q4, q1) VALUES ('100'::int2 , '9223372036854775800'::int8);
--Testcase 197:
SELECT q4 * q1 FROM INT8_TMP;
ERROR:  bigint out of range
--Testcase 108:
DELETE FROM INT8_TMP;
--Testcase 198:
INSERT INTO INT8_TMP(q4, q1) VALUES ('100'::int2 , '0'::int8);
--Testcase 199:
SELECT q4 / q1 FROM INT8_TMP;
ERROR:  division by zero
--Testcase 110:
SELECT CAST(q1 AS int4) FROM int8_tbl WHERE q2 = 456;
 q1  
-----
 123
(1 row)

--Testcase 111:
SELECT CAST(q1 AS int4) FROM int8_tbl WHERE q2 <> 456;
ERROR:  integer out of range
--Testcase 112:
SELECT CAST(q1 AS int2) FROM int8_tbl WHERE q2 = 456;
 q1  
-----
 123
(1 row)

--Testcase 113:
SELECT CAST(q1 AS int2) FROM int8_tbl WHERE q2 <> 456;
ERROR:  smallint out of range
--Testcase 200:
DELETE FROM INT8_TMP;
--Testcase 201:
INSERT INTO INT8_TMP(q5) VALUES ('42'), ('-37');
--Testcase 202:
SELECT CAST(q5::int2 as int8) FROM INT8_TMP;
 q5  
-----
  42
 -37
(2 rows)

--Testcase 114:
SELECT CAST(q1 AS float4), CAST(q2 AS float8) FROM INT8_TBL;
     q1      |           q2           
-------------+------------------------
         123 |                    456
         123 |  4.567890123456789e+15
 4.56789e+15 |                    123
 4.56789e+15 |  4.567890123456789e+15
 4.56789e+15 | -4.567890123456789e+15
(5 rows)

--Testcase 203:
DELETE FROM INT8_TMP;
--Testcase 204:
INSERT INTO INT8_TMP(q5) VALUES ('36854775807.0');
--Testcase 205:
SELECT CAST(q5::float4 AS int8) FROM INT8_TMP;
     q5      
-------------
 36854775808
(1 row)

--Testcase 206:
DELETE FROM INT8_TMP;
--Testcase 207:
INSERT INTO INT8_TMP(q5) VALUES ('922337203685477580700.0');
--Testcase 208:
SELECT CAST(q5::float8 AS int8) FROM INT8_TMP;
ERROR:  bigint out of range
--Testcase 115:
SELECT CAST(q1 AS oid) FROM INT8_TBL;
ERROR:  OID out of range
--Testcase 209:
SELECT oid::int8 FROM pg_class WHERE relname = 'pg_class';
 oid  
------
 1259
(1 row)

-- bit operations
--Testcase 116:
SELECT q1, q2, q1 & q2 AS "and", q1 | q2 AS "or", q1 # q2 AS "xor", ~q1 AS "not" FROM INT8_TBL;
        q1        |        q2         |       and        |        or        |       xor        |        not        
------------------+-------------------+------------------+------------------+------------------+-------------------
              123 |               456 |               72 |              507 |              435 |              -124
              123 |  4567890123456789 |               17 | 4567890123456895 | 4567890123456878 |              -124
 4567890123456789 |               123 |               17 | 4567890123456895 | 4567890123456878 | -4567890123456790
 4567890123456789 |  4567890123456789 | 4567890123456789 | 4567890123456789 |                0 | -4567890123456790
 4567890123456789 | -4567890123456789 |                1 |               -1 |               -2 | -4567890123456790
(5 rows)

--Testcase 117:
SELECT q1, q1 << 2 AS "shl", q1 >> 3 AS "shr" FROM INT8_TBL;
        q1        |        shl        |       shr       
------------------+-------------------+-----------------
              123 |               492 |              15
              123 |               492 |              15
 4567890123456789 | 18271560493827156 | 570986265432098
 4567890123456789 | 18271560493827156 | 570986265432098
 4567890123456789 | 18271560493827156 | 570986265432098
(5 rows)

-- generate_series
--Testcase 118:
DELETE FROM INT8_TMP;
--Testcase 210:
INSERT INTO INT8_TMP SELECT q1 FROM generate_series('+4567890123456789'::int8, '+4567890123456799'::int8) q1;
--Testcase 211:
SELECT q1 FROM INT8_TMP;
        q1        
------------------
 4567890123456789
 4567890123456790
 4567890123456791
 4567890123456792
 4567890123456793
 4567890123456794
 4567890123456795
 4567890123456796
 4567890123456797
 4567890123456798
 4567890123456799
(11 rows)

--Testcase 120:
DELETE FROM INT8_TMP;
--Testcase 212:
INSERT INTO INT8_TMP SELECT q1 FROM generate_series('+4567890123456789'::int8, '+4567890123456799'::int8, 0) q1; -- should error
ERROR:  step size cannot equal zero
--Testcase 213:
SELECT q1 FROM INT8_TMP;
 q1 
----
(0 rows)

--Testcase 122:
DELETE FROM INT8_TMP;
--Testcase 214:
INSERT INTO INT8_TMP SELECT q1 FROM generate_series('+4567890123456789'::int8, '+4567890123456799'::int8, 2) q1;
--Testcase 215:
SELECT q1 FROM INT8_TMP;
        q1        
------------------
 4567890123456789
 4567890123456791
 4567890123456793
 4567890123456795
 4567890123456797
 4567890123456799
(6 rows)

-- corner case
--Testcase 216:
DELETE FROM INT8_TMP;
--Testcase 217:
INSERT INTO INT8_TMP VALUES (-1::int8<<63);
--Testcase 218:
SELECT q1::text FROM INT8_TMP;
          q1          
----------------------
 -9223372036854775808
(1 row)

--Testcase 219:
DELETE FROM INT8_TMP;
--Testcase 220:
INSERT INTO INT8_TMP VALUES ((-1::int8<<63)+1);
--Testcase 221:
SELECT q1::text FROM INT8_TMP;
          q1          
----------------------
 -9223372036854775807
(1 row)

-- check sane handling of INT64_MIN overflow cases
--Testcase 125:
DELETE FROM INT8_TMP;
--Testcase 222:
INSERT INTO INT8_TMP VALUES ((-9223372036854775808)::int8 * (-1)::int8, 888);
ERROR:  bigint out of range
--Testcase 126:
INSERT INTO INT8_TMP VALUES ((-9223372036854775808)::int8 / (-1)::int8, 888);
ERROR:  bigint out of range
--Testcase 127:
INSERT INTO INT8_TMP VALUES ((-9223372036854775808)::int8 % (-1)::int8, 888);
--Testcase 128:
SELECT q1 FROM INT8_TMP WHERE q2 = 888;
 q1 
----
  0
(1 row)

--Testcase 129:
DELETE FROM INT8_TMP WHERE q2 = 888;
--Testcase 130:
INSERT INTO INT8_TMP VALUES ((-9223372036854775808)::int8 * (-1)::int4, 888);
ERROR:  bigint out of range
--Testcase 131:
INSERT INTO INT8_TMP VALUES ((-9223372036854775808)::int8 / (-1)::int4, 888);
ERROR:  bigint out of range
--Testcase 132:
INSERT INTO INT8_TMP VALUES ((-9223372036854775808)::int8 % (-1)::int4, 888);
--Testcase 133:
SELECT q1 FROM INT8_TMP WHERE q2 = 888;
 q1 
----
  0
(1 row)

--Testcase 134:
DELETE FROM INT8_TMP WHERE q2 = 888;
--Testcase 135:
INSERT INTO INT8_TMP VALUES ((-9223372036854775808)::int8 * (-1)::int2, 888);
ERROR:  bigint out of range
--Testcase 136:
INSERT INTO INT8_TMP VALUES ((-9223372036854775808)::int8 / (-1)::int2, 888);
ERROR:  bigint out of range
--Testcase 137:
INSERT INTO INT8_TMP VALUES ((-9223372036854775808)::int8 % (-1)::int2, 888);
--Testcase 138:
SELECT q1 FROM INT8_TMP WHERE q2 = 888;
 q1 
----
  0
(1 row)

--Testcase 139:
DELETE FROM INT8_TMP WHERE q2 = 888;
-- check rounding when casting from float
--Testcase 223:
CREATE FOREIGN TABLE FLOAT8_TMP(f1 float8, id int OPTIONS (key 'true')) SERVER sqlite_svr;
--Testcase 224:
DELETE FROM FLOAT8_TMP;
--Testcase 225:
INSERT INTO FLOAT8_TMP VALUES 
             (-2.5::float8),
             (-1.5::float8),
             (-0.5::float8),
             (0.0::float8),
             (0.5::float8),
             (1.5::float8),
             (2.5::float8);
--Testcase 226:
SELECT f1 as x, f1::int8 as int8_value FROM FLOAT8_TMP;
  x   | int8_value 
------+------------
 -2.5 |         -2
 -1.5 |         -2
 -0.5 |          0
    0 |          0
  0.5 |          0
  1.5 |          2
  2.5 |          2
(7 rows)

-- check rounding when casting from numeric
--Testcase 227:
CREATE FOREIGN TABLE NUMERIC_TMP(f1 numeric, id int OPTIONS (key 'true')) SERVER sqlite_svr;
--Testcase 228:
DELETE FROM NUMERIC_TMP;
--Testcase 229:
INSERT INTO NUMERIC_TMP VALUES
             (-2.5::numeric),
             (-1.5::numeric),
             (-0.5::numeric),
             (0.0::numeric),
             (0.5::numeric),
             (1.5::numeric),
             (2.5::numeric);
--Testcase 230:
SELECT f1 as x, f1::int8 as int8_value FROM NUMERIC_TMP;
  x   | int8_value 
------+------------
 -2.5 |         -3
 -1.5 |         -2
 -0.5 |         -1
    0 |          0
  0.5 |          1
  1.5 |          2
  2.5 |          3
(7 rows)

-- test gcd()
--Testcase 231:
DELETE FROM INT8_TMP;
--Testcase 232:
INSERT INTO INT8_TMP VALUES
             (0::int8, 0::int8),
             (0::int8, 29893644334::int8),
             (288484263558::int8, 29893644334::int8),
             (-288484263558::int8, 29893644334::int8),
             ((-9223372036854775808)::int8, 1::int8),
             ((-9223372036854775808)::int8, 9223372036854775807::int8),
             ((-9223372036854775808)::int8, 4611686018427387904::int8);
--Testcase 233:
SELECT q1, q2, gcd(q1, q2), gcd(q1, -q2), gcd(q2, q1), gcd(-q2, q1) FROM INT8_TMP;
          q1          |         q2          |         gcd         |         gcd         |         gcd         |         gcd         
----------------------+---------------------+---------------------+---------------------+---------------------+---------------------
                    0 |                   0 |                   0 |                   0 |                   0 |                   0
                    0 |         29893644334 |         29893644334 |         29893644334 |         29893644334 |         29893644334
         288484263558 |         29893644334 |             6835958 |             6835958 |             6835958 |             6835958
        -288484263558 |         29893644334 |             6835958 |             6835958 |             6835958 |             6835958
 -9223372036854775808 |                   1 |                   1 |                   1 |                   1 |                   1
 -9223372036854775808 | 9223372036854775807 |                   1 |                   1 |                   1 |                   1
 -9223372036854775808 | 4611686018427387904 | 4611686018427387904 | 4611686018427387904 | 4611686018427387904 | 4611686018427387904
(7 rows)

--Testcase 234:
DELETE FROM INT8_TMP;
--Testcase 235:
INSERT INTO INT8_TMP VALUES ((-9223372036854775808)::int8, 0::int8);
--Testcase 236:
SELECT gcd(q1, q2) FROM INT8_TMP; -- overflow
ERROR:  bigint out of range
--Testcase 237:
DELETE FROM INT8_TMP;
--Testcase 238:
INSERT INTO INT8_TMP VALUES ((-9223372036854775808)::int8, (-9223372036854775808)::int8);
--Testcase 239:
SELECT gcd(q1, q2) FROM INT8_TMP; -- overflow
ERROR:  bigint out of range
-- test lcm()
--Testcase 240:
DELETE FROM INT8_TMP;
--Testcase 241:
INSERT INTO INT8_TMP VALUES
             (0::int8, 0::int8),
             (0::int8, 29893644334::int8),
             (29893644334::int8, 29893644334::int8),
             (288484263558::int8, 29893644334::int8),
             (-288484263558::int8, 29893644334::int8),
             ((-9223372036854775808)::int8, 0::int8);
--Testcase 242:
SELECT q1, q2, lcm(q1, q2), lcm(q1, -q2), lcm(q2, q1), lcm(-q2, q1) FROM INT8_TMP;
          q1          |     q2      |       lcm        |       lcm        |       lcm        |       lcm        
----------------------+-------------+------------------+------------------+------------------+------------------
                    0 |           0 |                0 |                0 |                0 |                0
                    0 | 29893644334 |                0 |                0 |                0 |                0
          29893644334 | 29893644334 |      29893644334 |      29893644334 |      29893644334 |      29893644334
         288484263558 | 29893644334 | 1261541684539134 | 1261541684539134 | 1261541684539134 | 1261541684539134
        -288484263558 | 29893644334 | 1261541684539134 | 1261541684539134 | 1261541684539134 | 1261541684539134
 -9223372036854775808 |           0 |                0 |                0 |                0 |                0
(6 rows)

--Testcase 243:
DELETE FROM INT8_TMP;
--Testcase 244:
INSERT INTO INT8_TMP VALUES ((-9223372036854775808)::int8, 1::int8);
--Testcase 245:
SELECT lcm(q1, q2) FROM INT8_TMP; -- overflow
ERROR:  bigint out of range
--Testcase 246:
DELETE FROM INT8_TMP;
--Testcase 247:
INSERT INTO INT8_TMP VALUES ((9223372036854775807)::int8, (9223372036854775806)::int8);
--Testcase 248:
SELECT lcm(q1, q2) FROM INT8_TMP; -- overflow
ERROR:  bigint out of range
-- Clean up
DO $d$
declare
  l_rec record;
begin
  for l_rec in (select foreign_table_schema, foreign_table_name 
                from information_schema.foreign_tables) loop
     execute format('drop foreign table %I.%I cascade;', l_rec.foreign_table_schema, l_rec.foreign_table_name);
  end loop;
end;
$d$;
--Testcase 249:
DROP SERVER sqlite_svr;
--Testcase 250:
DROP EXTENSION sqlite_fdw CASCADE;