seg fault on select or attach to remote postgresql TEXT [] / VARCHAR [] arrays with more than 1 dimension

[HackettJP]

What happens?

not sure but duckdb may not able to handle multidimensional arrays in postgresql either in the CLI binary or the c++ lib arrays in postgresql via postgres_attach or postgres_scan method,

not sure that the postgresql text arrays be treated as VARCHAR [] in duckfb .

It will either 1 dimension text array of data enclosed in 2 dimensions give [\0\0\0] from a select query , or with 2 dimension text array give seg fault in duckdb both in CLI or C++ lib. with quick result--> print() method.

quick examples below my test 4 and 5 is were the fault can be reproduced occurs.

single nest {} text array in postgresql
Postgresql table data '{1,TEXT1,A1}', --> select * from table [1, TEXT1, A1] (PASS) VARCHAR[ ]

double nest {{}} text array in postgresql
Postgresql table data '{{1,TEXT1,A1}}' --> select * from table [\0\0\0] (FAIL) VARCHAR[ ]

double nest {{},{}} text array multidimension in postgresql
Postgresql table data '{{1,TEXT1,A1},{2,TEXT2,A2}}' --> select * from table segfault crash duckdb (FAIL)

thks

J.

To Reproduce

CREATE Table on postgresql

CREATE TABLE public.testlisttable
(
testrow text,
column2 text[], -- text array []
column3 text[][] -- same as column 2, postgres treats [] same as [] [] , but to prove both cases, setup for completeness
);

(A)
LOAD postgres_scanner;
SELECT * FROM POSTGRES_SCAN('','public', 'testlisttable)'
(B)
OR can do
LOAD postgres_scanner;
CALL postgres_attach('',source_schema='public', sink_schema='main') etc.
SELECT * FROM testlisttable;

TEST(1) insert records into postgresql db
INSERT INTO public.testlisttable(testrow) VALUES ('TEST 1');
query from duckdb
SELECT * FROM POSTGRES_SCAN('','public', 'testlisttable)'
--- PASS NULLS IN column 2, column 3

TEST(2) insert records into postgresql db
INSERT INTO public.testlisttable(testrow,column2,column3) VALUES ('TEST 2','{}','{}');
query from duckdb
SELECT * FROM POSTGRES_SCAN('','public', 'testlisttable)'
--- PASS has [] closing brackets in column2, column3

TEST(3) insert records into postgresql db
INSERT INTO public.testlisttable(testrow,column2,column3) VALUES ('TEST 3','{1,TEXT1,A1}','{1,TEXT1,A1}');
query from duckdb
SELECT * FROM POSTGRES_SCAN('','public', 'testlisttable)'
--- PASS VARCHAR [] [1, TEXT1, A1] [1, TEXT1, A1]

TEST(4) insert records into postgresql db 1-dimension data in 2-dimension text array
INSERT INTO public.testlisttable(testrow,column2,column3) VALUES ('TEST 4','{1,TEXT1,A1}','{{1,TEXT1,A1}}');
query from duckdb
SELECT * FROM POSTGRES_SCAN('','public', 'testlisttable)'
-- FAIL has column2 [1, TEXT1, A1] column3 [\0\0\0]

TEST(5) insert records into postgresql db 2-dimension text array
INSERT INTO public.testlisttable(testrow,column2,column3) VALUES ('TEST 5','{1,TEXT1,A1}','{{1,TEXT1,A1},{2,TEXT2,A2}}');
query from duckdb
SELECT * FROM POSTGRES_SCAN('','public', 'testlisttable)'
-- FAIL has segment core dump fault.

---

Also just want add did a quick test on other datatypes VARCHAR and INTEGER

(1) If instead i used VARCHAR instead of text on creating the tables and used the same test pattern same issue occurs on fail on test (4) and test (5);

(2) next i used INTEGER on test table in postgresql and then changed the data to numbers ('4','{1}','{{1}}'); and ('5','{1}','{{1},{2}}') the duckdb will come back with error message:-

Error: Invalid Input Error: Expected to read a Postgres list value of length 4, but only have size 1

OS:

ubuntu 22.04

PostgreSQL Version:

15.2 (Ubuntu 15.2-1.pgdg22.04+1)

DuckDB Version:

0.7.1

DuckDB Client:

CLI on unix or C++ lib.

Full Name:

James Hackett

Affiliation:

none

Have you tried this on the latest master branch?

• I agree

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

• I agree

[HackettJP]

OOPS went through the source code and appears nested arrays is not supported