\set ECHO errors /****************************************************************************/ -- By default ClickHouse flattens a Nested column. CALL clickhouse_perform('nested_admin', $$ CREATE TABLE nested_test.visits( visit_id UInt64, user_id UInt64, goals Nested( serial UInt32, order_id String ) ) ENGINE = MergeTree ORDER BY visit_id $$); -- By default, a Nested column is flattened into multiple columns. IMPORT FOREIGN SCHEMA nested_test FROM SERVER binary_nested_loopback INTO nest_bin; EXECUTE describe('nest_bin.visits'); attname | type | attndims | attnotnull ----------------+---------------+----------+------------ visit_id | numeric(20,0) | 0 | t user_id | numeric(20,0) | 0 | t goals.serial | bigint[] | 1 | t goals.order_id | text[] | 1 | t (4 rows) IMPORT FOREIGN SCHEMA nested_test FROM SERVER http_nested_loopback INTO nest_http; EXECUTE describe('nest_http.visits'); attname | type | attndims | attnotnull ----------------+---------------+----------+------------ visit_id | numeric(20,0) | 0 | t user_id | numeric(20,0) | 0 | t goals.serial | bigint[] | 1 | t goals.order_id | text[] | 1 | t (4 rows) -- Insert values. INSERT INTO nest_bin.visits VALUES (1, 1, '{1,2}'::bigint[],'{xx,yy}'::text[]); INSERT INTO nest_bin.visits VALUES (2, 2, '{3,4}'::bigint[],'{aa,bb}'::text[]); SELECT * FROM nest_bin.visits ORDER BY visit_id; visit_id | user_id | goals.serial | goals.order_id ----------+---------+--------------+---------------- 1 | 1 | {1,2} | {xx,yy} 2 | 2 | {3,4} | {aa,bb} (2 rows) SELECT * FROM nest_http.visits ORDER BY visit_id; visit_id | user_id | goals.serial | goals.order_id ----------+---------+--------------+---------------- 1 | 1 | {1,2} | {xx,yy} 2 | 2 | {3,4} | {aa,bb} (2 rows) -- Should pushdown array access. EXPLAIN (VERBOSE, COSTS OFF) SELECT visit_id FROM nest_bin.visits WHERE "goals.serial"[1] = 1; QUERY PLAN ----------------------------------------------------------------------------------------- Foreign Scan on nest_bin.visits Output: visit_id Remote SQL: SELECT visit_id FROM nested_test.visits WHERE ((("goals.serial"[1]) = 1)) (3 rows) SELECT visit_id FROM nest_bin.visits WHERE "goals.serial"[1] = 1; visit_id ---------- 1 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT visit_id FROM nest_http.visits WHERE "goals.serial"[1] = 1; QUERY PLAN ----------------------------------------------------------------------------------------- Foreign Scan on nest_http.visits Output: visit_id Remote SQL: SELECT visit_id FROM nested_test.visits WHERE ((("goals.serial"[1]) = 1)) (3 rows) SELECT visit_id FROM nest_http.visits WHERE "goals.serial"[1] = 1; visit_id ---------- 1 (1 row) \set ECHO errors /****************************************************************************/ -- Recreate everything with flatten_nested=0 DROP FOREIGN TABLE nest_bin.visits; DROP FOREIGN TABLE nest_http.visits; CALL clickhouse_perform('nested_admin', 'DROP TABLE nested_test.visits'); CALL clickhouse_perform('nested_admin', $$ CREATE TABLE nested_test.visits( visit_id UInt64, user_id UInt64, goals Nested( serial UInt32, order_id String ) ) ENGINE = MergeTree ORDER BY visit_id SETTINGS flatten_nested = 0 $$); IMPORT FOREIGN SCHEMA nested_test FROM SERVER binary_nested_loopback INTO nest_bin; NOTICE: pg_clickhouse: ClickHouse type was translated to type for column "goals"; please create composite type and alter the column if needed EXECUTE describe('nest_bin.visits'); attname | type | attndims | attnotnull ----------+---------------+----------+------------ visit_id | numeric(20,0) | 0 | t user_id | numeric(20,0) | 0 | t goals | text[] | 2 | t (3 rows) IMPORT FOREIGN SCHEMA nested_test FROM SERVER http_nested_loopback INTO nest_http; NOTICE: pg_clickhouse: ClickHouse type was translated to type for column "goals"; please create composite type and alter the column if needed EXECUTE describe('nest_http.visits'); attname | type | attndims | attnotnull ----------+---------------+----------+------------ visit_id | numeric(20,0) | 0 | t user_id | numeric(20,0) | 0 | t goals | text[] | 2 | t (3 rows) -- Insert values. INSERT INTO nest_bin.visits VALUES (1, 1, '{{1,xx},{2,yy}}'::text[]); INSERT INTO nest_bin.visits VALUES (2, 2, '{{3,aa},{4,bb}}'::text[]); SELECT * FROM nest_bin.visits ORDER BY visit_id; visit_id | user_id | goals ----------+---------+----------------- 1 | 1 | {{1,xx},{2,yy}} 2 | 2 | {{3,aa},{4,bb}} (2 rows) SELECT * FROM nest_http.visits ORDER BY visit_id; visit_id | user_id | goals ----------+---------+----------------- 1 | 1 | {{1,xx},{2,yy}} 2 | 2 | {{3,aa},{4,bb}} (2 rows) -- Pushdown multidimensional array access fails. Failure expected: we would -- need to know that the column is Nested and thus should be converted to -- `tupleElement(goals[1], 1) = '1'`. EXPLAIN (VERBOSE, COSTS OFF) SELECT visit_id FROM nest_bin.visits WHERE goals[1][1] = '1'; QUERY PLAN ------------------------------------------------------------------------------------- Foreign Scan on nest_bin.visits Output: visit_id Remote SQL: SELECT visit_id FROM nested_test.visits WHERE (((goals[1][1]) = '1')) (3 rows) SELECT visit_id FROM nest_bin.visits WHERE goals[1][1] = '1'; ERROR: pg_clickhouse: DB::Exception: First argument for function 'arrayElement' must be array, got 'Tuple(serial UInt32, order_id String)' instead: In scope SELECT visit_id FROM nested_test.visits WHERE (((goals[1])[1]) = '1') DETAIL: Remote Query: SELECT visit_id FROM nested_test.visits WHERE (((goals[1][1]) = '1')) EXPLAIN (VERBOSE, COSTS OFF) SELECT visit_id FROM nest_http.visits WHERE goals[1][1] = '1'; QUERY PLAN ------------------------------------------------------------------------------------- Foreign Scan on nest_http.visits Output: visit_id Remote SQL: SELECT visit_id FROM nested_test.visits WHERE (((goals[1][1]) = '1')) (3 rows) SELECT visit_id FROM nest_http.visits WHERE goals[1][1] = '1'; ERROR: pg_clickhouse: Code: 43. DB::Exception: First argument for function 'arrayElement' must be array, got 'Tuple(serial UInt32, order_id String)' instead: In scope SELECT visit_id FROM nested_test.visits WHERE (((goals[1])[1]) = '1'). (ILLEGAL_TYPE_OF_ARGUMENT) DETAIL: Remote Query: SELECT visit_id FROM nested_test.visits WHERE (((goals[1][1]) = '1')) CONTEXT: HTTP status code: 500 /****************************************************************************/ -- Create them manually with a composite type; CREATE TYPE goal_type AS (serial bigint, order_id text); DROP FOREIGN TABLE nest_bin.visits; CREATE FOREIGN TABLE nest_bin.visits( visit_id numeric(20,0) NOT NULL, user_id numeric(20,0) NOT NULL, goals goal_type[] NOT NULL ) SERVER binary_nested_loopback OPTIONS(table_name 'visits'); DROP FOREIGN TABLE nest_http.visits; CREATE FOREIGN TABLE nest_http.visits( visit_id numeric(20,0) NOT NULL, user_id numeric(20,0) NOT NULL, goals goal_type[] NOT NULL ) SERVER http_nested_loopback OPTIONS(table_name 'visits'); -- Insert values. INSERT INTO nest_bin.visits VALUES (3, 3, ARRAY[row(5, 'jj'), row(6, 'zz')]::goal_type[]); INSERT INTO nest_bin.visits VALUES (4, 4, ARRAY[row(7, 'mm'), row(8, 'uu')]::goal_type[]); SELECT * FROM nest_bin.visits ORDER BY visit_id; visit_id | user_id | goals ----------+---------+--------------------- 1 | 1 | {"(1,xx)","(2,yy)"} 2 | 2 | {"(3,aa)","(4,bb)"} 3 | 3 | {"(5,jj)","(6,zz)"} 4 | 4 | {"(7,mm)","(8,uu)"} (4 rows) SELECT * FROM nest_http.visits ORDER BY visit_id; visit_id | user_id | goals ----------+---------+--------------------- 1 | 1 | {"(1,xx)","(2,yy)"} 2 | 2 | {"(3,aa)","(4,bb)"} 3 | 3 | {"(5,jj)","(6,zz)"} 4 | 4 | {"(7,mm)","(8,uu)"} (4 rows) -- Querying composite field access works, unlike for multidimensional arrays, -- but remains local for now. EXPLAIN (VERBOSE, COSTS OFF) SELECT visit_id FROM nest_bin.visits WHERE goals[1].serial = 1; QUERY PLAN -------------------------------------------------------------- Foreign Scan on nest_bin.visits Output: visit_id Filter: (visits.goals[1].serial = 1) Remote SQL: SELECT visit_id, goals FROM nested_test.visits (4 rows) SELECT visit_id FROM nest_bin.visits WHERE goals[1].serial = 1; visit_id ---------- 1 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT visit_id FROM nest_http.visits WHERE goals[1].serial = 1; QUERY PLAN -------------------------------------------------------------- Foreign Scan on nest_http.visits Output: visit_id Filter: (visits.goals[1].serial = 1) Remote SQL: SELECT visit_id, goals FROM nested_test.visits (4 rows) SELECT visit_id FROM nest_http.visits WHERE goals[1].serial = 1; visit_id ---------- 1 (1 row) /****************************************************************************/ -- Replicate the composites example from the docs. CALL clickhouse_perform('nested_admin', $$ CREATE TABLE nested_test.events ( id UInt32, status Enum8('new' = 1, 'done' = 2), point Tuple(Int32, Int32), labels Map(String, Int64), items Nested(id Int32, name String) ) ORDER BY id SETTINGS flatten_nested = 0 $$); CALL clickhouse_perform('nested_admin', $$ INSERT INTO nested_test.events VALUES(1, 'new', tuple(3, 4), {'k1': 5, 'k2': 6}, [tuple(100, 'xx'), tuple(101, 'yy')]) $$); CREATE TYPE event_status AS ENUM ('new', 'done'); CREATE TYPE event_point AS (x integer, y integer); CREATE TYPE event_label AS (key text, value bigint); CREATE TYPE event_item AS (id integer, name text); CREATE FOREIGN TABLE nest_bin.events ( id bigint, status event_status, point event_point, labels event_label[], items event_item[] ) SERVER binary_nested_loopback OPTIONS(table_name 'events'); SELECT * FROM nest_bin.events ORDER BY id; id | status | point | labels | items ----+--------+-------+---------------------+------------------------- 1 | new | (3,4) | {"(k1,5)","(k2,6)"} | {"(100,xx)","(101,yy)"} (1 row) SELECT (point).x, (point).y, (labels[1]).key, (labels[1]).value, (items[1]).id, (items[1]).name FROM nest_bin.events ORDER BY id; x | y | key | value | id | name ---+---+-----+-------+-----+------ 3 | 4 | k1 | 5 | 100 | xx (1 row) -- Composite access does not yet push down. EXPLAIN (VERBOSE, COSTS OFF) SELECT id FROM nest_bin.events WHERE (point).x = 3; QUERY PLAN -------------------------------------------------------- Foreign Scan on nest_bin.events Output: id Filter: ((events.point).x = 3) Remote SQL: SELECT id, point FROM nested_test.events (4 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT id FROM nest_bin.events WHERE (labels[1]).key = 'k2'; QUERY PLAN --------------------------------------------------------- Foreign Scan on nest_bin.events Output: id Filter: (events.labels[1].key = 'k2'::text) Remote SQL: SELECT id, labels FROM nested_test.events (4 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT id FROM nest_bin.events WHERE (items[1]).id = 100; QUERY PLAN -------------------------------------------------------- Foreign Scan on nest_bin.events Output: id Filter: (events.items[1].id = 100) Remote SQL: SELECT id, items FROM nested_test.events (4 rows) CALL clickhouse_perform('nested_admin', 'DROP DATABASE nested_test'); DROP USER MAPPING FOR CURRENT_USER SERVER binary_nested_loopback; DROP USER MAPPING FOR CURRENT_USER SERVER http_nested_loopback; DROP SERVER binary_nested_loopback CASCADE; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to foreign table nest_bin.visits drop cascades to foreign table nest_bin.events DROP SERVER http_nested_loopback CASCADE; NOTICE: drop cascades to foreign table nest_http.visits