Weird inconsistency with real postgres

[GrantJamesPowell]

I have a library that generates SQL that looks like this

with "cte1" as (
  (select arg_0 as "a", arg_1 as "b" from (values (1, 'Foo'), (2, 'Bar'), (3, 'Baz')) as vals(arg_0, arg_1))
)
select "a", "b" from "cte1";

In real postgres it returns correctly

psql (16.2 (Debian 16.2-1.pgdg120+2))
Type "help" for help.

postgres=# with "cte1" as (
  (select arg_0 as "a", arg_1 as "b" from (values (1, 'Foo'), (2, 'Bar'), (3, 'Baz')) as vals(arg_0, arg_1))
)
select "a", "b" from "cte1";
 a |  b
---+-----
 1 | Foo
 2 | Bar
 3 | Baz
(3 rows)

In pglite I get weird answers

import { PGlite } from "@electric-sql/pglite"

const db = new PGlite();

const res = await db.query(`
  with "cte1" as (
    (select arg_0 as "a", arg_1 as "b" from (values (1, 'Foo'), (2, 'Bar'), (3, 'Baz')) as vals(arg_0, arg_1))
  )
  select "a", "b" from "cte1";
`);
console.log(res);

> bun foo.mjs
[
  {
    "": "Foo",
  }, {
    "": "Bar",
  }, {
    "": "Baz",
  }
]

> deno run -A foo.mjs
[ { "": "Foo" }, { "": "Bar" }, { "": "Baz" } ]

[samwillis]

Thanks for the report.

My suspicion is that that is related to how we are serialising results as JSON inside Postgres and then passing them back to js.

Internally this currently builds on the postgres single user mode. Interestingly I don't see a difference when ruling this query in single user mode to inserting and selecting from an actual table:

samwillis@Sams-MacBook-Air temp % postgres --single -D ./pgdata postgres

PostgreSQL stand-alone backend 16.0
backend> with "cte1" as ( (select arg_0 as "a", arg_1 as "b" from (values (1, 'Foo'), (2, 'Bar'), (3, 'Baz')) as vals(arg_0, arg_1)) ) select "a", "b" from "cte1";
     1: a   (typeid = 23, len = 4, typmod = -1, byval = t)
     2: b   (typeid = 25, len = -1, typmod = -1, byval = f)
    ----
     1:  = "1"  (typeid = 23, len = 4, typmod = -1, byval = t)
     2:  = "Foo"    (typeid = 25, len = -1, typmod = -1, byval = f)
    ----
     1:  = "2"  (typeid = 23, len = 4, typmod = -1, byval = t)
     2:  = "Bar"    (typeid = 25, len = -1, typmod = -1, byval = f)
    ----
     1:  = "3"  (typeid = 23, len = 4, typmod = -1, byval = t)
     2:  = "Baz"    (typeid = 25, len = -1, typmod = -1, byval = f)
    ----
backend> create table "test" ("a" int, "b" text);
backend> insert into "test" values (1, 'Foo'), (2, 'Bar'), (3, 'Baz');
backend> select * from "test";
     1: a   (typeid = 23, len = 4, typmod = -1, byval = t)
     2: b   (typeid = 25, len = -1, typmod = -1, byval = f)
    ----
     1: a = "1" (typeid = 23, len = 4, typmod = -1, byval = t)
     2: b = "Foo"   (typeid = 25, len = -1, typmod = -1, byval = f)
    ----
     1: a = "2" (typeid = 23, len = 4, typmod = -1, byval = t)
     2: b = "Bar"   (typeid = 25, len = -1, typmod = -1, byval = f)
    ----
     1: a = "3" (typeid = 23, len = 4, typmod = -1, byval = t)
     2: b = "Baz"   (typeid = 25, len = -1, typmod = -1, byval = f)
    ----

I shall investigate further, it may be that #31 is the best solution.

[samwillis]

My suspicion was correct, this will be fixed when #39 is merged and published (there is a dev build attached to the PR)