pgduckdb_get_querydef() ignores certain type conversion

[l1t1]

What happens?

chr()function does not works when insert into a columnstore, it seems the casts to int operate is omitted

postgres=# create table games(i int, j varchar)USING columnstore;
CREATE TABLE
Time: 6.261 ms
postgres=# with t as (select chr(126983+i::int) pic from generate_series(0,26)t(i))insert into games select 2, string_agg(pic,''order by pic)j from t,generate_series(1,4);
ERROR:  (PGDuckDB/CreatePlan) Prepared query returned an error: 'Binder Error: No function matches the given name and argument types 'chr(BIGINT)'. You might need to add explicit type casts.
        Candidate functions:
        chr(INTEGER) -> VARCHAR

LINE 1: WITH t AS (SELECT chr((126983 + t.i)) AS pic FROM generat...
                          ^
Time: 59.376 ms
postgres=# with t as (select chr((126983+i)::int) pic from generate_series(0,26)t(i))insert into games select 2, string_agg(pic,''order by pic)j from t,
generate_series(1,4);
ERROR:  (PGDuckDB/CreatePlan) Prepared query returned an error: 'Binder Error: No function matches the given name and argument types 'chr(BIGINT)'. You might need to add explicit type casts.
        Candidate functions:
        chr(INTEGER) -> VARCHAR

LINE 1: WITH t AS (SELECT chr((126983 + t.i)) AS pic FROM generat...
                          ^
Time: 2.486 ms

when insert a normal table, it works. duckdb also works.

postgres=# create table games2(i int, j varchar);
CREATE TABLE
Time: 3.526 ms
postgres=# with t as (select chr((126983+i)::int) pic from generate_series(0,26)t(i))insert into games2 select 2, string_agg(pic,''order by pic)j from t
,generate_series(1,4);
INSERT 0 1
Time: 3.802 ms
postgres=# \q
root@DESKTOP-59T6U68:/par# ./duckdb
v1.1.4-dev4923 d0c4cf8a28
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D create table games2(i int, j varchar);
D with t as (select chr((126983+i)::int) pic from generate_series(0,26)t(i))insert into games2 select 2, string_agg(pic,''order by pic)j from t
  ,generate_series(1,4);

To Reproduce

create table games(i int, j varchar)USING columnstore;

with t as (select chr((126983+i)::int) pic from generate_series(0,26)t(i))insert into games select 2, string_agg(pic,''order by pic)j from t,
generate_series(1,4);

OS:

x64 Linux

pg_mooncake Version:

0.1

Postgres Version:

17.2

Are you using pg_mooncake Docker, Neon, or the extension standalone?

pg_mooncake Docker Image

[l1t1]

and when I insert into the same row from a normal table, run the sql file reports

postgres=# insert into games select * from games2;
INSERT 0 1
postgres=# \i mj9apg.txt
psql:mj9apg.txt:127: ERROR:  columnstore_slot_callbacks not implemented

mj9apg.txt

[dpxcc]

The issue is because pgduckdb_get_querydef() ignores the type conversion in (126983+i)::int
Filed an upstream bug to pg_duckdb team: duckdb/pg_duckdb#561

[dpxcc]

and when I insert into the same row from a normal table, run the sql file reports

postgres=# insert into games select * from games2;
INSERT 0 1
postgres=# \i mj9apg.txt
psql:mj9apg.txt:127: ERROR:  columnstore_slot_callbacks not implemented

mj9apg.txt

It doesn't error on my side

scratch (pid: 5831) =# create table games2(i int, j varchar);
CREATE TABLE
scratch (pid: 5831) =# with t as (select chr((126983+i)::int) pic from generate_series(0,26)t(i))insert into games2 select 2, string_agg(pic,''order by pic)j from t
,generate_series(1,4);
INSERT 0 1
scratch (pid: 5831) =# CREATE TABLE games USING columnstore AS TABLE games2;
CREATE TABLE AS
scratch (pid: 5831) =# \i mj9apg.txt
      ret       | k1  | k2  | k3  | k4  | d  
----------------+-----+-----+-----+-----+----
 🀇🀇🀇🀇🀈🀈🀈🀈🀉🀉🀉🀉🀊🀊 | 🀇🀇🀇 | 🀈🀈🀈 | 🀇🀈🀉 | 🀉🀉🀉 | 🀊🀊
(1 row)

Can you provide more details to repro?

I already changed

        games c)w  -- when use games table here,  raise error

[l1t1]

I tried the table games again, the error still there.
then I created a table games3 using CREATE TABLE games3 USING columnstore AS TABLE games2;, it also has the error.

[l1t1]

I narrow the question, the sql raise error when processing tmp1, if only query tmp0, it'ok.

postgres=# \i mj1.txt
psql:mj1.txt:33: ERROR:  columnstore_slot_callbacks not implemented
postgres=# \i mj0.txt
 category | seq | chr | n  | hs | pd
----------+-----+-----+----+----+----
        0 |   1 | 🀇   |  1 |  0 |  1
        0 |   2 | 🀇   |  1 |  0 |  1
        0 |   3 | 🀇   |  1 |  0 |  1
        0 |   4 | 🀇   |  1 |  0 |  1
        0 |   5 | 🀈   |  2 |  0 |  2
        0 |   6 | 🀈   |  2 |  0 |  2
        0 |   7 | 🀈   |  2 |  0 |  2
。。

mj0.txt
mj1.txt

[dpxcc]

I narrow the question, the sql raise error when processing tmp1, if only query tmp0, it'ok.

postgres=# \i mj1.txt
psql:mj1.txt:33: ERROR:  columnstore_slot_callbacks not implemented
postgres=# \i mj0.txt
 category | seq | chr | n  | hs | pd
----------+-----+-----+----+----+----
        0 |   1 | 🀇   |  1 |  0 |  1
        0 |   2 | 🀇   |  1 |  0 |  1
        0 |   3 | 🀇   |  1 |  0 |  1
        0 |   4 | 🀇   |  1 |  0 |  1
        0 |   5 | 🀈   |  2 |  0 |  2
        0 |   6 | 🀈   |  2 |  0 |  2
        0 |   7 | 🀈   |  2 |  0 |  2
。。

mj0.txt mj1.txt

OK, you are hitting #100
The bug is already fixed on main branch, but we haven't published a new Docker image yet

[l1t1]

I narrow the question, the sql raise error when processing tmp1, if only query tmp0, it'ok.

postgres=# \i mj1.txt
psql:mj1.txt:33: ERROR:  columnstore_slot_callbacks not implemented
postgres=# \i mj0.txt
 category | seq | chr | n  | hs | pd
----------+-----+-----+----+----+----
        0 |   1 | 🀇   |  1 |  0 |  1
        0 |   2 | 🀇   |  1 |  0 |  1
        0 |   3 | 🀇   |  1 |  0 |  1
        0 |   4 | 🀇   |  1 |  0 |  1
        0 |   5 | 🀈   |  2 |  0 |  2
        0 |   6 | 🀈   |  2 |  0 |  2
        0 |   7 | 🀈   |  2 |  0 |  2
。。

mj0.txt mj1.txt

OK, you are hitting #100 The bug is already fixed on main branch, but we haven't published a new Docker image yet

columnstore_slot_callback error fixed in new docker image of today.
BTW, how to query the pg_mooncake version in psql?

[dpxcc]

postgres (pid: 71269) =# CREATE EXTENSION pg_mooncake;
CREATE EXTENSION
postgres (pid: 71269) =# \dx
                    List of installed extensions
    Name     | Version |   Schema   |          Description          
-------------+---------+------------+-------------------------------
 pg_mooncake | 0.1.1   | public     | Columnstore Table in Postgres
 plpgsql     | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)