Incorrect result from DuckDB execution

[dpxcc]

What happens?

Turning on duckdb.force_execution produces incorrect result
Please see the repro below

To Reproduce

postgres=# CREATE TABLE s (a text[]);
CREATE TABLE
postgres=# INSERT INTO s VALUES (ARRAY['abc', 'def', 'ghi']);
INSERT 0 1
postgres=# CREATE TABLE t AS TABLE s;
SELECT 1
postgres=# SELECT * FROM t;
       a       
---------------
 {abc,def,ghi}
(1 row)

postgres=# SET duckdb.force_execution TO true;
SET
postgres=# SELECT * FROM t;
  a   
------
 \x01
(1 row)

OS:

Linux

pg_duckdb Version (if built from source use commit hash):

0.2.0

Postgres Version (if built from source use commit hash):

17.2

Hardware:

No response

Full Name:

Cheng Chen

Affiliation:

Mooncake Labs

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

Did you include all relevant data sets for reproducing the issue?

Not applicable - the reproduction does not require a data set

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant configuration (e.g., CPU architecture, Linux distribution) to reproduce the issue?

• Yes, I have

[JelteF]

Hmm. Strange... Somehow that CREATE TABLE AS is significant, since this incorrect output does not occur when querying the s table.

Copy pastable reproduction script:

CREATE TABLE s (a text[]);
INSERT INTO s VALUES (ARRAY['abc', 'def', 'ghi']);
CREATE TABLE t AS TABLE s;
SELECT * FROM s;
SELECT * FROM t;
SET duckdb.force_execution TO true;
SELECT * FROM s;
SELECT * FROM t;

[YuweiXiao]

not sure if it is related to toast. for varchar, ConvertPostgresToDuckValue uses datum directly instead of wrapper such asDatumGetTextP

btw, detoast will palloc memory if needed. so there is memory leak for decimal in ConvertPostgresToDuckValue, as DatumGetNumeric do detoast internally.

[kysshsy]

Hi. While investigating the problem, I observed two inconsistent behaviors:

1. Column Type Discrepancy:
   table t.a has the different column type from table s.a

postgres=# \d+ s
                                            Table "public.s"
 Column |  Type  | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
--------+--------+-----------+----------+---------+----------+-------------+--------------+-------------
 a      | text[] |           |          |         | extended |             |              |
Access method: heap

postgres=# \d+ t
                                                  Table "public.t"
 Column |        Type         | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
--------+---------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 a      | character varying[] |           |          |         | extended |             |              |
Access method: heap

2. Type Conversion Anomaly:
   During the execution of CREATE TABLE t AS TABLE s;, the function pgduckdb::GetPostgresDuckDBType(column); returns an OID of 1015 (which corresponds to VARCHAR_ARRAY), so Postgres interprets the column type in table t as character varying[]. However, when executing SELECT * FROM t;, the system returns an OID of 1043 (VARCHAR), causing the query results to be incorrect.

So I suspect the issue lies in the definition of the corresponding table in DuckDB.( I can't figure out how the corresponding table is created.)

I haven't quite understood how the only Postgres table mechanism works. Is there a corresponding table in DuckDB?

[JelteF]

@kysshsy The issue you're describing is a separate issue from the wrong result one. It happens when you run the CREATE TABLE AS while duckdb.force_execution is true. If you don't, like I did in the steps in my previous comment, both will be the text[] type. I do think it's confusing behaviour so I created a draft PR to fix this issue (which needs some more work): #583

@dpxcc I tested if this was a regression, but this is also happening on 0.2.0. So while it's a serious issue, I don't think it needs to block the 0.3.0 release.

[YuweiXiao]

The attribute's attndims is 0 for table created by CTAS, though it is VARCHARARRAYOID. it is weird and should be a PG behavior. During scan, pg_duckdb constructs varchar instead of list<varchar>, leading to incorrect result.

[JelteF]

@YuweiXiao thanks a lot for that investigation!

The attribute's attndims is 0 for table created by CTAS, though it is VARCHARARRAYOID.

As explained in my comment above, the fact that it is VARCHARARRAYOID is tracked in #583. (and is not relevant to the incorrect result).

The attndims=0 situation is new to me though. And indeed pg_duckdbs understanding of attndims=0 seems to be the reason for the incorrect result bug.

To be clear, that attndims=0 occurs for this CTAS seems to be fully expected. Using a Postgres without pg_duckdb also results in attndims=0 for that column. So the problem here is that pg_duckdb interprets that incorrectly to mean that it is the basetype. Without looking closer at what attndims=0 actually means, I'm not 100% sure how we should interpret it instead. But it's clear that pg_duckdb its current interpretation is plain wrong.

[YuweiXiao]

The ndims property is simply ignored during the CTAS. https://github.com/postgres/postgres/blob/80d7f990496b1c7be61d9a00a2635b7d96b96197/src/backend/commands/createas.c#L495

I suggest using pg_type's typcategory to check if the type is an array. The ndim will be handled when we parsing the tuple, as the header stores the correct ndims value.

[JelteF]

Thanks again. I'm working on a fix/workaround for this problem now.