--SET log_min_messages  TO DEBUG1;
--SET client_min_messages  TO DEBUG1;
--Testcase 44:
CREATE EXTENSION sqlite_fdw;
--Testcase 45:
CREATE SERVER sqlite_svr FOREIGN DATA WRAPPER sqlite_fdw
OPTIONS (database '/tmp/sqlite_fdw_test/common.db');
--Testcase 46:
CREATE SERVER sqlite2 FOREIGN DATA WRAPPER sqlite_fdw;
IMPORT FOREIGN SCHEMA public FROM SERVER sqlite_svr INTO public;
--Testcase 1:
INSERT INTO "type_STRING"(col) VALUES ('string');
--Testcase 4:
INSERT INTO "type_BYTE"(col) VALUES ('c');
--Testcase 5:
INSERT INTO "type_SINT"(col) VALUES (32767);
--Testcase 6:
INSERT INTO "type_SINT"(col) VALUES (-32768);
--Testcase 7:
INSERT INTO "type_BINT"(col) VALUES (9223372036854775807);
--Testcase 8:
INSERT INTO "type_BINT"(col) VALUES (-9223372036854775808);
--Testcase 9:
INSERT INTO "type_INTEGER"(col) VALUES (9223372036854775807);
--Testcase 10:
INSERT INTO "type_FLOAT"(col) VALUES (3.1415);
--Testcase 11:
INSERT INTO "type_DOUBLE"(col) VALUES (3.14159265);
--Testcase 12:
INSERT INTO "type_TIMESTAMP" VALUES ('2017.11.06 12:34:56.789', '2017.11.06');
--Testcase 13:
INSERT INTO "type_TIMESTAMP" VALUES ('2017.11.06 1:3:0', '2017.11.07');
--Testcase 14:
INSERT INTO "type_BLOB"(col) VALUES (bytea('\xDEADBEEF'));
--Testcase 15:
INSERT INTO typetest VALUES(1,'a', 'b', 'c','2017.11.06 12:34:56.789', '2017.11.06 12:34:56.789' ) ;
--Testcase 16:
SELECT * FROM "type_STRING";
  col   
--------
 string
(1 row)

--Testcase 18:
SELECT * FROM "type_BYTE";
 col 
-----
 c
(1 row)

--Testcase 19:
SELECT * FROM "type_SINT";
  col   
--------
  32767
 -32768
(2 rows)

--Testcase 20:
SELECT * FROM "type_BINT";
         col          
----------------------
  9223372036854775807
 -9223372036854775808
(2 rows)

--Testcase 21:
SELECT * FROM "type_INTEGER";
         col         
---------------------
 9223372036854775807
(1 row)

--Testcase 22:
SELECT * FROM "type_FLOAT";
  col   
--------
 3.1415
(1 row)

--Testcase 23:
SELECT * FROM "type_DOUBLE";
    col     
------------
 3.14159265
(1 row)

set datestyle=ISO;
--Testcase 24:
SELECT * FROM "type_TIMESTAMP";
           col           |          b          
-------------------------+---------------------
 2017-11-06 12:34:56.789 | 2017-11-06 00:00:00
 2017-11-06 01:03:00     | 2017-11-07 00:00:00
(2 rows)

--Testcase 25:
SELECT * FROM "type_BLOB";
    col     
------------
 \xdeadbeef
(1 row)

--Testcase 26:
SELECT * FROM typetest;
 i | v |     c      | t |            d            |           ti            
---+---+------------+---+-------------------------+-------------------------
 1 | a | b          | c | 2017-11-06 12:34:56.789 | 2017-11-06 12:34:56.789
(1 row)

--Testcase 27:
insert into "type_STRING" values('TYPE');
--Testcase 28:
insert into "type_STRING" values('type');
-- not pushdown
--Testcase 29:
SELECT  *FROM "type_STRING" WHERE col like 'TYP%';
 col  
------
 TYPE
(1 row)

--Testcase 30:
EXPLAIN SELECT  *FROM "type_STRING" WHERE col like 'TYP%';
                            QUERY PLAN                             
-------------------------------------------------------------------
 Foreign Scan on "type_STRING"  (cost=10.00..7.00 rows=7 width=32)
(1 row)

-- pushdown
--Testcase 31:
SELECT  *FROM "type_STRING" WHERE col ilike 'typ%';
 col  
------
 TYPE
 type
(2 rows)

--Testcase 32:
EXPLAIN SELECT  *FROM "type_STRING" WHERE col ilike 'typ%';
                             QUERY PLAN                              
---------------------------------------------------------------------
 Foreign Scan on "type_STRING"  (cost=10.00..58.00 rows=58 width=32)
   Filter: (col ~~* 'typ%'::text)
(2 rows)

--Testcase 33:
SELECT  *FROM "type_STRING" WHERE col ilike 'typ%' and col like 'TYPE';
 col  
------
 TYPE
(1 row)

--Testcase 34:
EXPLAIN SELECT  *FROM "type_STRING" WHERE col ilike 'typ%' and col like 'TYPE';
                            QUERY PLAN                             
-------------------------------------------------------------------
 Foreign Scan on "type_STRING"  (cost=10.00..1.00 rows=1 width=32)
   Filter: (col ~~* 'typ%'::text)
(2 rows)

--Testcase 35:
SELECT * FROM "type_TIMESTAMP";
           col           |          b          
-------------------------+---------------------
 2017-11-06 12:34:56.789 | 2017-11-06 00:00:00
 2017-11-06 01:03:00     | 2017-11-07 00:00:00
(2 rows)

--Testcase 36:
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM  "type_TIMESTAMP" WHERE col > date ('2017.11.06 12:34:56.789') ;
                                         QUERY PLAN                                          
---------------------------------------------------------------------------------------------
 Foreign Scan on public."type_TIMESTAMP"
   Output: col, b
   SQLite query: SELECT `col`, `b` FROM main."type_TIMESTAMP" WHERE ((`col` > '2017-11-06'))
(3 rows)

--Testcase 37:
SELECT * FROM  "type_TIMESTAMP" WHERE col > date ('2017.11.06 12:34:56.789') ;
           col           |          b          
-------------------------+---------------------
 2017-11-06 01:03:00     | 2017-11-07 00:00:00
 2017-11-06 12:34:56.789 | 2017-11-06 00:00:00
(2 rows)

--Testcase 38:
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM  "type_TIMESTAMP" WHERE col::text > date ('2017.11.06 12:34:56.789')::text ;
                              QUERY PLAN                               
-----------------------------------------------------------------------
 Foreign Scan on public."type_TIMESTAMP"
   Output: col, b
   Filter: (("type_TIMESTAMP".col)::text > ('2017-11-06'::date)::text)
   SQLite query: SELECT `col`, `b` FROM main."type_TIMESTAMP"
(4 rows)

--Testcase 39:
SELECT * FROM  "type_TIMESTAMP" WHERE col::text > date ('2017.11.06 12:34:56.789')::text ;
           col           |          b          
-------------------------+---------------------
 2017-11-06 12:34:56.789 | 2017-11-06 00:00:00
 2017-11-06 01:03:00     | 2017-11-07 00:00:00
(2 rows)

--Testcase 40:
EXPLAIN  (VERBOSE, COSTS OFF) SELECT * FROM  "type_TIMESTAMP" WHERE col > b - interval '1 hour'; 
                                   QUERY PLAN                                   
--------------------------------------------------------------------------------
 Foreign Scan on public."type_TIMESTAMP"
   Output: col, b
   Filter: ("type_TIMESTAMP".col > ("type_TIMESTAMP".b - '@ 1 hour'::interval))
   SQLite query: SELECT `col`, `b` FROM main."type_TIMESTAMP"
(4 rows)

--Testcase 41:
SELECT * FROM  "type_TIMESTAMP" WHERE col > b - interval '1 hour';
           col           |          b          
-------------------------+---------------------
 2017-11-06 12:34:56.789 | 2017-11-06 00:00:00
(1 row)

--Testcase 42:
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM  "type_TIMESTAMP" WHERE col > b;
                                     QUERY PLAN                                     
------------------------------------------------------------------------------------
 Foreign Scan on public."type_TIMESTAMP"
   Output: col, b
   SQLite query: SELECT `col`, `b` FROM main."type_TIMESTAMP" WHERE ((`col` > `b`))
(3 rows)

--Testcase 43:
SELECT * FROM  "type_TIMESTAMP" WHERE col > b;
           col           |          b          
-------------------------+---------------------
 2017-11-06 12:34:56.789 | 2017-11-06 00:00:00
(1 row)

--Testcase 48:
INSERT INTO "type_DATE"(col) VALUES ('2021.02.23');
--Testcase 49:
INSERT INTO "type_DATE"(col) VALUES ('2021/03/08');
--Testcase 50:
INSERT INTO "type_DATE"(col) VALUES ('9999-12-30');
--Testcase 58:
SELECT * FROM "type_DATE";
    col     
------------
 2021-02-23
 2021-03-08
 9999-12-30
(3 rows)

--Testcase 51:
INSERT INTO "type_TIME"(col) VALUES ('01:23:45');
--Testcase 52:
INSERT INTO "type_TIME"(col) VALUES ('01:23:45.6789');
--Testcase 59:
SELECT * FROM "type_TIME";
      col      
---------------
 01:23:45
 01:23:45.6789
(2 rows)

--Testcase 60:
EXPLAIN VERBOSE
SELECT c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c17, c18, c19, c2, c21, c22, c23, c24 FROM alltypetest;
                                                                                                                                        QUERY PLAN                                                                                                                                        
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.alltypetest  (cost=10.00..57.00 rows=57 width=1400)
   Output: c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c17, c18, c19, c2, c21, c22, c23, c24
   SQLite query: SELECT `c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8`, `c9`, `c10`, `c11`, `c12`, `c13`, `c14`, `c15`, sqlite_fdw_float(`c17`), sqlite_fdw_float(`c18`), sqlite_fdw_float(`c19`), sqlite_fdw_float(`c21`), sqlite_fdw_float(`c22`), `c23`, `c24` FROM main."alltypetest"
(3 rows)

--Testcase 61:
SELECT c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15,  c17, c18, c19, c2, c21, c22, c23, c24 FROM alltypetest;
   c1   | c2  |  c3   |   c4   |      c5       |      c6      |      c7       |     c8     |          c9          |          c10           |         c11         |       c12        |                    c13                    |             c14             |           c15            |   c17   |     c18      |     c19     | c2  | c21  |     c22     |    c23     |         c24         
--------+-----+-------+--------+---------------+--------------+---------------+------------+----------------------+------------------------+---------------------+------------------+-------------------------------------------+-----------------------------+--------------------------+---------+--------------+-------------+-----+------+-------------+------------+---------------------
 583647 | 127 | 12767 | 388607 | 2036854775807 | 573709551615 | 2036854775807 | abcdefghij | abcdefghijjhgfjfuafh | Côte dIvoire Fijifoxju | Hôm nay tôi rất vui | I am happy today | 今日はとても幸せです 今日はとても幸せです | The quick brown fox jumps o | ABCDEFGHIJKLMNOPQRSTUVWX | 3.4e+18 | 1.79769e+108 | 1.79769e+88 | 127 | 1234 | 99999.99999 | 9999-12-31 | 9999-12-31 23:59:59
(1 row)

--Testcase 53:
CREATE FOREIGN TABLE type_JSON(col JSON OPTIONS (key 'true')) SERVER sqlite_svr OPTIONS (table 'type_TEXT');
--Testcase 54:
INSERT INTO type_JSON(col) VALUES ('[1, 2, "foo", null]');
--Testcase 55:
INSERT INTO type_JSON(col) VALUES ('{"bar": "baz", "balance": 7.77, "active": false}'::json);
--Testcase 56
SELECT * FROM type_JSON;
                       col                        
--------------------------------------------------
 [1, 2, "foo", null]
 {"bar": "baz", "balance": 7.77, "active": false}
(2 rows)

--Testcase 57
DELETE FROM type_JSON;
--Testcase 62:
DROP FOREIGN TABLE IF EXISTS "type_BOOLEAN";
--Testcase 63:
CREATE FOREIGN TABLE "type_BOOLEAN" (i int, b boolean OPTIONS (key 'true')) SERVER sqlite_svr;
--Testcase 108:
INSERT INTO "type_BOOLEAN"(i, b) VALUES 
  (1, TRUE), 
  (2, FALSE), 
  (3, TRUE), 
  (4, FALSE), 
  (5, true), 
  (6, false), 
  (7, 'Yes'), 
  (8, 'YeS'), 
  (9, 'yes'), 
  (10, 'no'), 
  (11, 'No'), 
  (12, 'nO'), 
  (13, 'off'), 
  (14, 'oFf'), 
  (15, 'on'), 
  (16, 'ON'), 
  (17, 't'), 
  (18, 'T'), 
  (19, 'Y'), 
  (20, 'y'), 
  (21, 'F'), 
  (22, 'f'), 
  (24, '0'), 
  (25, '1'), 
  (26, NULL);
--Testcase 64:
ALTER FOREIGN TABLE "type_BOOLEAN" DROP COLUMN i;
--Testcase 65:
SELECT * FROM "type_BOOLEAN"; -- OK
 b 
---
 t
 f
 t
 f
 t
 f
 t
 t
 t
 f
 f
 f
 f
 f
 t
 t
 t
 t
 t
 t
 f
 f
 f
 t
 
(25 rows)

-- define INTEGER as TEXT column
--Testcase 67:
ALTER FOREIGN TABLE "type_INTEGER" ALTER COLUMN col TYPE text;
--Testcase 68:
SELECT * FROM "type_INTEGER"; -- OK
         col         
---------------------
 9223372036854775807
(1 row)

-- define INTEGER as bpchar
--Testcase 69:
ALTER FOREIGN TABLE "type_INTEGER" ALTER COLUMN col TYPE char(30);
--Testcase 70:
SELECT * FROM "type_INTEGER"; -- OK
              col               
--------------------------------
 9223372036854775807           
(1 row)

-- define INTEGER as varchar
--Testcase 71:
ALTER FOREIGN TABLE "type_INTEGER" ALTER COLUMN col TYPE varchar(30);
--Testcase 72:
SELECT * FROM "type_INTEGER"; -- OK
         col         
---------------------
 9223372036854775807
(1 row)

-- define INTEGER as name
--Testcase 73:
ALTER FOREIGN TABLE "type_INTEGER" ALTER COLUMN col TYPE name;
--Testcase 74:
SELECT * FROM "type_INTEGER"; -- OK
         col         
---------------------
 9223372036854775807
(1 row)

-- define INTEGER as json
--Testcase 75:
ALTER FOREIGN TABLE "type_INTEGER" ALTER COLUMN col TYPE json;
--Testcase 76:
SELECT * FROM "type_INTEGER"; -- OK
         col         
---------------------
 9223372036854775807
(1 row)

-- define INTEGER as time
--Testcase 77:
DELETE FROM "type_INTEGER";
--Testcase 78:
ALTER FOREIGN TABLE "type_INTEGER" ALTER COLUMN col TYPE int;
--Testcase 79:
INSERT INTO "type_INTEGER" VALUES (120506);
--Testcase 80:
ALTER FOREIGN TABLE "type_INTEGER" ALTER COLUMN col TYPE time;
--Testcase 81:
SELECT * FROM "type_INTEGER"; -- OK
   col    
----------
 12:05:06
(1 row)

-- define INTEGER as date
--Testcase 82:
ALTER FOREIGN TABLE "type_INTEGER" ALTER COLUMN col TYPE date;
--Testcase 83:
SELECT * FROM "type_INTEGER"; -- OK
    col     
------------
 2012-05-06
(1 row)

--Testcase 84:
ALTER FOREIGN TABLE "type_INTEGER" ALTER COLUMN col TYPE int;
--Testcase 85:
INSERT INTO "type_DOUBLE" VALUES (1.3e-5);
--Testcase 86:
SELECT * FROM "type_DOUBLE";
    col     
------------
 3.14159265
    1.3e-05
(2 rows)

-- define DOUBLE as TEXT column
--Testcase 87:
ALTER FOREIGN TABLE "type_DOUBLE" ALTER COLUMN col TYPE text;
--Testcase 88:
SELECT * FROM "type_DOUBLE"; -- OK
    col     
------------
 3.14159265
 1.3e-05
(2 rows)

-- define DOUBLE as bpchar
--Testcase 89:
ALTER FOREIGN TABLE "type_DOUBLE" ALTER COLUMN col TYPE char(30);
--Testcase 90:
SELECT * FROM "type_DOUBLE"; -- OK
              col               
--------------------------------
 3.14159265                    
 1.3e-05                       
(2 rows)

-- define DOUBLE as varchar
--Testcase 91:
ALTER FOREIGN TABLE "type_DOUBLE" ALTER COLUMN col TYPE varchar(30);
--Testcase 92:
SELECT * FROM "type_DOUBLE"; -- OK
    col     
------------
 3.14159265
 1.3e-05
(2 rows)

-- define DOUBLE as name
--Testcase 93:
ALTER FOREIGN TABLE "type_DOUBLE" ALTER COLUMN col TYPE name;
--Testcase 94:
SELECT * FROM "type_DOUBLE"; -- OK
    col     
------------
 3.14159265
 1.3e-05
(2 rows)

-- define DOUBLE as json
--Testcase 95:
ALTER FOREIGN TABLE "type_DOUBLE" ALTER COLUMN col TYPE json;
--Testcase 96:
SELECT * FROM "type_DOUBLE"; -- OK
    col     
------------
 3.14159265
 1.3e-05
(2 rows)

--Testcase 97:
DELETE FROM "type_DOUBLE";
--Testcase 98:
ALTER FOREIGN TABLE "type_DOUBLE" ALTER COLUMN col TYPE float8;
--Testcase 99:
INSERT INTO "type_DOUBLE" VALUES (120506.12);
-- define DOUBLE as time
--Testcase 100:
ALTER FOREIGN TABLE "type_DOUBLE" ALTER COLUMN col TYPE time;
--Testcase 101:
SELECT * FROM "type_DOUBLE"; -- OK
     col     
-------------
 12:05:06.12
(1 row)

--Testcase 102:
DELETE FROM "type_DOUBLE";
--Testcase 103:
ALTER FOREIGN TABLE "type_DOUBLE" ALTER COLUMN col TYPE float8;
--Testcase 104:
INSERT INTO "type_DOUBLE" VALUES (1999.012);
-- define DOUBLE as date
--Testcase 105:
ALTER FOREIGN TABLE "type_DOUBLE" ALTER COLUMN col TYPE date;
--Testcase 106:
SELECT * FROM "type_DOUBLE"; -- OK
    col     
------------
 1999-01-12
(1 row)

--Testcase 107:
ALTER FOREIGN TABLE "type_DOUBLE" ALTER COLUMN col TYPE float8;
--Testcase 108:
INSERT INTO "type_DOUBLE" VALUES (PI());
--Testcase 109: ERR primary key
INSERT INTO "type_DOUBLE" VALUES (PI());
ERROR:  Failed to execute remote SQL
HINT:  SQLite error 'UNIQUE constraint failed: type_DOUBLE.col', SQLite result code 19
CONTEXT:  SQL query: INSERT INTO main."type_DOUBLE"(`col`) VALUES (?)
--Testcase 110:
INSERT INTO "type_DOUBLE" VALUES ('Infinity');
--Testcase 111:
INSERT INTO "type_DOUBLE" VALUES ('-Infinity');
--Testcase 113:
SELECT * FROM "type_DOUBLE"; -- OK, +- Inf
        col        
-------------------
          1999.012
 3.141592653589793
          Infinity
         -Infinity
(4 rows)

--Testcase 114: ERR primary key
INSERT INTO "type_DOUBLE" VALUES ('Infinity');
ERROR:  Failed to execute remote SQL
HINT:  SQLite error 'UNIQUE constraint failed: type_DOUBLE.col', SQLite result code 19
CONTEXT:  SQL query: INSERT INTO main."type_DOUBLE"(`col`) VALUES (?)
--Testcase 115: ERR primary key
INSERT INTO "type_DOUBLE" VALUES ('-Infinity');
ERROR:  Failed to execute remote SQL
HINT:  SQLite error 'UNIQUE constraint failed: type_DOUBLE.col', SQLite result code 19
CONTEXT:  SQL query: INSERT INTO main."type_DOUBLE"(`col`) VALUES (?)
--Testcase 116:
SELECT * FROM "type_DOUBLE"; -- OK, +- Inf
        col        
-------------------
          1999.012
 3.141592653589793
          Infinity
         -Infinity
(4 rows)

--Testcase 47:
DROP EXTENSION sqlite_fdw CASCADE;
NOTICE:  drop cascades to 48 other objects
DETAIL:  drop cascades to server sqlite_svr
drop cascades to foreign table department
drop cascades to foreign table employee
drop cascades to foreign table empdata
drop cascades to foreign table numbers
drop cascades to foreign table t
drop cascades to foreign table multiprimary
drop cascades to foreign table columntest
drop cascades to foreign table noprimary
drop cascades to foreign table limittest
drop cascades to foreign table grem1_1
drop cascades to foreign table grem1_2
drop cascades to foreign table case_exp
drop cascades to foreign table "type_STRING"
drop cascades to foreign table "type_BOOLEANpk"
drop cascades to foreign table "type_BYTE"
drop cascades to foreign table "type_SINT"
drop cascades to foreign table "type_BINT"
drop cascades to foreign table "type_INTEGER"
drop cascades to foreign table "type_FLOAT"
drop cascades to foreign table "type_DOUBLE"
drop cascades to foreign table "type_TIMESTAMP"
drop cascades to foreign table "type_BLOB"
drop cascades to foreign table "type_DATE"
drop cascades to foreign table "type_TIME"
drop cascades to foreign table "type_BIT"
drop cascades to foreign table "type_VARBIT"
drop cascades to foreign table "type_UUIDpk"
drop cascades to foreign table "type_UUID"
drop cascades to foreign table "BitT"
drop cascades to foreign table notype
drop cascades to foreign table typetest
drop cascades to foreign table "type_TEXT"
drop cascades to foreign table alltypetest
drop cascades to foreign table shorty
drop cascades to foreign table "A a"
drop cascades to foreign table fts_table
drop cascades to foreign table fts_table_data
drop cascades to foreign table fts_table_idx
drop cascades to foreign table fts_table_content
drop cascades to foreign table fts_table_docsize
drop cascades to foreign table fts_table_config
drop cascades to foreign table "RO_RW_test"
drop cascades to foreign table "Unicode data"
drop cascades to foreign table "type_BOOLEAN_oper"
drop cascades to foreign table type_json
drop cascades to foreign table "type_BOOLEAN"
drop cascades to server sqlite2