SET datestyle = 'ISO';
CREATE SERVER binary_loopback FOREIGN DATA WRAPPER clickhouse_fdw
    OPTIONS(dbname 'binary_test', driver 'binary');
CREATE USER MAPPING FOR CURRENT_USER SERVER binary_loopback;
SELECT clickhouse_raw_query('DROP DATABASE IF EXISTS binary_test');
 clickhouse_raw_query 
----------------------
 
(1 row)

SELECT clickhouse_raw_query('CREATE DATABASE binary_test');
 clickhouse_raw_query 
----------------------
 
(1 row)

-- integer types
SELECT clickhouse_raw_query('CREATE TABLE binary_test.ints (
    c1 Int8, c2 Int16, c3 Int32, c4 Int64,
    c5 UInt8, c6 UInt16, c7 UInt32, c8 UInt64,
    c9 Float32, c10 Float64, c11 Bool
) ENGINE = MergeTree PARTITION BY c1 ORDER BY (c1);
');
 clickhouse_raw_query 
----------------------
 
(1 row)

SELECT clickhouse_raw_query('INSERT INTO binary_test.ints SELECT
    number, number + 1, number + 2, number + 3, number + 4, number + 5,
    number + 6, number + 7, number + 8.1, number + 9.2, cast(number % 2 as Bool)
    FROM numbers(10);');
 clickhouse_raw_query 
----------------------
 
(1 row)

-- date and string types
SELECT clickhouse_raw_query('CREATE TABLE binary_test.types (
    c1 Date, c2 DateTime, c3 String, c4 FixedString(5), c5 UUID,
    c6 Enum8(''one'' = 1, ''two'' = 2),
    c7 Enum16(''one'' = 1, ''two'' = 2, ''three'' = 3)
) ENGINE = MergeTree PARTITION BY c1 ORDER BY (c1);
');
 clickhouse_raw_query 
----------------------
 
(1 row)

SELECT clickhouse_raw_query('INSERT INTO binary_test.types SELECT
    addDays(toDate(''1990-01-01''), number),
    addMinutes(addSeconds(addDays(toDateTime(''1990-01-01 10:00:00'', ''UTC''), number), number), number),
    format(''number {0}'', toString(number)),
    format(''num {0}'', toString(number)),
    format(''f4bf890f-f9dc-4332-ad5c-0c18e73f28e{0}'', toString(number)),
    ''two'',
    ''three''
    FROM numbers(10);');
 clickhouse_raw_query 
----------------------
 
(1 row)

-- array types
SELECT clickhouse_raw_query('CREATE TABLE binary_test.arrays (
    c1 Array(Int), c2 Array(String)
) ENGINE = MergeTree PARTITION BY c1 ORDER BY (c1);
');
 clickhouse_raw_query 
----------------------
 
(1 row)

SELECT clickhouse_raw_query('INSERT INTO binary_test.arrays SELECT
    [number, number + 1],
    [format(''num{0}'', toString(number)), format(''num{0}'', toString(number + 1))]
    FROM numbers(10);');
 clickhouse_raw_query 
----------------------
 
(1 row)

SELECT clickhouse_raw_query('CREATE TABLE binary_test.tuples (
    c1 Int8,
    c2 Tuple(Int, String, Float32),
    c3 UInt8
) ENGINE = MergeTree PARTITION BY c1 ORDER BY (c1);
');
 clickhouse_raw_query 
----------------------
 
(1 row)

SELECT clickhouse_raw_query('INSERT INTO binary_test.tuples SELECT
    number,
    (number, toString(number), number + 1.0),
    number % 2
    FROM numbers(10);');
 clickhouse_raw_query 
----------------------
 
(1 row)

SELECT clickhouse_raw_query('CREATE TABLE binary_test.bytes (
    c1 Int8,
    c2 String,
    c3 FixedString(16)
) ENGINE = MergeTree PARTITION BY c1 ORDER BY (c1);
');
 clickhouse_raw_query 
----------------------
 
(1 row)

SELECT clickhouse_raw_query('INSERT INTO binary_test.bytes SELECT
    number,
    SHA1(''val'' || toString(number)),
    MD5(''val'' || toString(number))
    FROM numbers(10);');
 clickhouse_raw_query 
----------------------
 
(1 row)

CREATE FOREIGN TABLE fints (
	c1 int2,
	c2 int2,
	c3 int,
	c4 int8,
	c5 int2,
	c6 int,
	c7 int8,
	c8 int8,
    c9 float4,
    c10 float8,
    c11 bool
) SERVER binary_loopback OPTIONS (table_name 'ints');
CREATE FOREIGN TABLE ftypes (
	c1 date,
	c2 timestamp with time zone,
    c3 text,
    c4 text,
    c5 uuid,
    c6 text, -- Enum8
    c7 text  -- Enum16
) SERVER binary_loopback OPTIONS (table_name 'types');
CREATE FOREIGN TABLE farrays (
	c1 int[],
    c2 text[]
) SERVER binary_loopback OPTIONS (table_name 'arrays');
CREATE FOREIGN TABLE farrays2 (
	c1 int8[],
    c2 text[]
) SERVER binary_loopback OPTIONS (table_name 'arrays');
CREATE TYPE tupformat AS (a int, b text, c float4);
CREATE FOREIGN TABLE ftuples (
    c1 int,
    c2 tupformat,
    c3 bool
) SERVER binary_loopback OPTIONS (table_name 'tuples');
CREATE FOREIGN TABLE fbytes(
    c1 int,
    c2 BYTEA,
    c3 BYTEA
) SERVER binary_loopback OPTIONS (table_name 'bytes');
COPY fints FROM stdin;
-- integers
SELECT * FROM fints ORDER BY c1;
 c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 |  c9  | c10  | c11 
----+----+----+----+----+----+----+----+------+------+-----
  0 |  1 |  2 |  3 |  4 |  5 |  6 |  7 |  8.1 |  9.2 | f
  1 |  2 |  3 |  4 |  5 |  6 |  7 |  8 |  9.1 | 10.2 | t
  2 |  3 |  4 |  5 |  6 |  7 |  8 |  9 | 10.1 | 11.2 | f
  3 |  4 |  5 |  6 |  7 |  8 |  9 | 10 | 11.1 | 12.2 | t
  4 |  5 |  6 |  7 |  8 |  9 | 10 | 11 | 12.1 | 13.2 | f
  5 |  6 |  7 |  8 |  9 | 10 | 11 | 12 | 13.1 | 14.2 | t
  6 |  7 |  8 |  9 | 10 | 11 | 12 | 13 | 14.1 | 15.2 | f
  7 |  8 |  9 | 10 | 11 | 12 | 13 | 14 | 15.1 | 16.2 | t
  8 |  9 | 10 | 11 | 12 | 13 | 14 | 15 | 16.1 | 17.2 | f
  9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17.1 | 18.2 | t
(10 rows)

SELECT c2, c1, c8, c3, c4, c7, c6, c5 FROM fints ORDER BY c1;
 c2 | c1 | c8 | c3 | c4 | c7 | c6 | c5 
----+----+----+----+----+----+----+----
  1 |  0 |  7 |  2 |  3 |  6 |  5 |  4
  2 |  1 |  8 |  3 |  4 |  7 |  6 |  5
  3 |  2 |  9 |  4 |  5 |  8 |  7 |  6
  4 |  3 | 10 |  5 |  6 |  9 |  8 |  7
  5 |  4 | 11 |  6 |  7 | 10 |  9 |  8
  6 |  5 | 12 |  7 |  8 | 11 | 10 |  9
  7 |  6 | 13 |  8 |  9 | 12 | 11 | 10
  8 |  7 | 14 |  9 | 10 | 13 | 12 | 11
  9 |  8 | 15 | 10 | 11 | 14 | 13 | 12
 10 |  9 | 16 | 11 | 12 | 15 | 14 | 13
(10 rows)

SELECT a, b FROM (SELECT c1 * 10 as a, c8 * 11 as b FROM fints ORDER BY a LIMIT 2) t1;
 a  | b  
----+----
  0 | 77
 10 | 88
(2 rows)

SELECT NULL FROM fints LIMIT 2;
 ?column? 
----------
 
 
(2 rows)

SELECT c2, NULL, c1, NULL FROM fints ORDER BY c2 LIMIT 2;
 c2 | ?column? | c1 | ?column? 
----+----------+----+----------
  1 |          |  0 | 
  2 |          |  1 | 
(2 rows)

-- types
SELECT * FROM ftypes ORDER BY c1;
     c1     |           c2           |    c3    |  c4   |                  c5                  | c6  |  c7   
------------+------------------------+----------+-------+--------------------------------------+-----+-------
 1990-01-01 | 1990-01-01 02:00:00-08 | number 0 | num 0 | f4bf890f-f9dc-4332-ad5c-0c18e73f28e0 | two | three
 1990-01-02 | 1990-01-02 02:01:01-08 | number 1 | num 1 | f4bf890f-f9dc-4332-ad5c-0c18e73f28e1 | two | three
 1990-01-03 | 1990-01-03 02:02:02-08 | number 2 | num 2 | f4bf890f-f9dc-4332-ad5c-0c18e73f28e2 | two | three
 1990-01-04 | 1990-01-04 02:03:03-08 | number 3 | num 3 | f4bf890f-f9dc-4332-ad5c-0c18e73f28e3 | two | three
 1990-01-05 | 1990-01-05 02:04:04-08 | number 4 | num 4 | f4bf890f-f9dc-4332-ad5c-0c18e73f28e4 | two | three
 1990-01-06 | 1990-01-06 02:05:05-08 | number 5 | num 5 | f4bf890f-f9dc-4332-ad5c-0c18e73f28e5 | two | three
 1990-01-07 | 1990-01-07 02:06:06-08 | number 6 | num 6 | f4bf890f-f9dc-4332-ad5c-0c18e73f28e6 | two | three
 1990-01-08 | 1990-01-08 02:07:07-08 | number 7 | num 7 | f4bf890f-f9dc-4332-ad5c-0c18e73f28e7 | two | three
 1990-01-09 | 1990-01-09 02:08:08-08 | number 8 | num 8 | f4bf890f-f9dc-4332-ad5c-0c18e73f28e8 | two | three
 1990-01-10 | 1990-01-10 02:09:09-08 | number 9 | num 9 | f4bf890f-f9dc-4332-ad5c-0c18e73f28e9 | two | three
(10 rows)

SELECT c2, c1, c4, c3, c5, c7, c6 FROM ftypes ORDER BY c1;
           c2           |     c1     |  c4   |    c3    |                  c5                  |  c7   | c6  
------------------------+------------+-------+----------+--------------------------------------+-------+-----
 1990-01-01 02:00:00-08 | 1990-01-01 | num 0 | number 0 | f4bf890f-f9dc-4332-ad5c-0c18e73f28e0 | three | two
 1990-01-02 02:01:01-08 | 1990-01-02 | num 1 | number 1 | f4bf890f-f9dc-4332-ad5c-0c18e73f28e1 | three | two
 1990-01-03 02:02:02-08 | 1990-01-03 | num 2 | number 2 | f4bf890f-f9dc-4332-ad5c-0c18e73f28e2 | three | two
 1990-01-04 02:03:03-08 | 1990-01-04 | num 3 | number 3 | f4bf890f-f9dc-4332-ad5c-0c18e73f28e3 | three | two
 1990-01-05 02:04:04-08 | 1990-01-05 | num 4 | number 4 | f4bf890f-f9dc-4332-ad5c-0c18e73f28e4 | three | two
 1990-01-06 02:05:05-08 | 1990-01-06 | num 5 | number 5 | f4bf890f-f9dc-4332-ad5c-0c18e73f28e5 | three | two
 1990-01-07 02:06:06-08 | 1990-01-07 | num 6 | number 6 | f4bf890f-f9dc-4332-ad5c-0c18e73f28e6 | three | two
 1990-01-08 02:07:07-08 | 1990-01-08 | num 7 | number 7 | f4bf890f-f9dc-4332-ad5c-0c18e73f28e7 | three | two
 1990-01-09 02:08:08-08 | 1990-01-09 | num 8 | number 8 | f4bf890f-f9dc-4332-ad5c-0c18e73f28e8 | three | two
 1990-01-10 02:09:09-08 | 1990-01-10 | num 9 | number 9 | f4bf890f-f9dc-4332-ad5c-0c18e73f28e9 | three | two
(10 rows)

-- arrays
SELECT * FROM farrays ORDER BY c1;
   c1   |      c2      
--------+--------------
 {0,1}  | {num0,num1}
 {1,2}  | {num1,num2}
 {2,3}  | {num2,num3}
 {3,4}  | {num3,num4}
 {4,5}  | {num4,num5}
 {5,6}  | {num5,num6}
 {6,7}  | {num6,num7}
 {7,8}  | {num7,num8}
 {8,9}  | {num8,num9}
 {9,10} | {num9,num10}
(10 rows)

SELECT * FROM farrays2 ORDER BY c1;
ERROR:  pg_clickhouse: could not cast value from integer[] to bigint[]
-- tuples
SELECT * FROM ftuples ORDER BY c1;
 c1 |    c2    | c3 
----+----------+----
  0 | (0,0,1)  | f
  1 | (1,1,2)  | t
  2 | (2,2,3)  | f
  3 | (3,3,4)  | t
  4 | (4,4,5)  | f
  5 | (5,5,6)  | t
  6 | (6,6,7)  | f
  7 | (7,7,8)  | t
  8 | (8,8,9)  | f
  9 | (9,9,10) | t
(10 rows)

-- Bytes.
SELECT * FROM fbytes ORDER BY c1;
 c1 |                     c2                     |                 c3                 
----+--------------------------------------------+------------------------------------
  0 | \x24e3800ca47f2504c25917d3b07306a28d0ac6fc | \x8664dccd1d83673d4fa8ef755ae88439
  1 | \x5e6e4c0fb8ef47b4f1d6eea3e6c51152dbee94ec | \x8de92ce2033cf3ca03fa8cc63e7a703f
  2 | \x4f47fdcd4e8d3b4802d50f990b401066bbfe0379 | \x38ceaa3b09c5a07d329888ba1ccde9ad
  3 | \xb6a0af46ee8d92da3f0c176da8d88517a1b21c54 | \x9163c8c66d03c512404cca8549a250e7
  4 | \xba42461dcb3e24f04e0d236f61d6804a4f4b3bee | \xa8516b0468eb31028c3dd669867c15b1
  5 | \x483d5aa3a98e3d1d9f3f40c26ac15c9d42c2f6b8 | \x294a6e0d759cdbcf55753c4a58161721
  6 | \x7d6934814141b108771faa00ba53750372cb413c | \x1b18b7cd2d63787cbe1d97e57a54853c
  7 | \xd750c9d1e88df77272d3bde4a0ad16ca7ca6f14a | \x7e526a670883f267ce15deff74740a6e
  8 | \x7673723fea187e3e78522d4b19ea97eb1a3d6787 | \xeafd00c5d025e9d71e9ed588f7f97e4b
  9 | \xda1a828a08d9d57450ce10a89cb5ee91dc2feed7 | \xa2da4af9e1d8f4d37887f719cda60bda
(10 rows)

DROP USER MAPPING FOR CURRENT_USER SERVER binary_loopback;
SELECT clickhouse_raw_query('DROP DATABASE binary_test');
 clickhouse_raw_query 
----------------------
 
(1 row)

DROP SERVER binary_loopback CASCADE;
NOTICE:  drop cascades to 6 other objects
DETAIL:  drop cascades to foreign table fints
drop cascades to foreign table ftypes
drop cascades to foreign table farrays
drop cascades to foreign table farrays2
drop cascades to foreign table ftuples
drop cascades to foreign table fbytes