TL;DR: Cloudflare D1 has a critical bug in Workers SDK reproducible on the Cloudflare D1 Console and when running D1 through Wrangler. The PR fixes the bug and also improves compatibility with workerd.

The problem

The essence of the problem is selecting two columns with similar names:

SELECT
    projects.name,
    organizations.name
FROM
    projects
    JOIN organizations ON projects.organization_id = organizations.id;

...must return in the row containing both columns (tested on PostgreSQL v16.3):

test=# SELECT projects.name, organizations.name
        FROM projects
        JOIN organizations ON projects.organization_id = organizations.id;
    name    |    name    
------------+------------
 Cloudflare | Cloudflare
(1 row)

SQLite (on a database created by Wrangler) gives a similar result:

sqlite> SELECT
    projects.name,
    organizations.name
FROM
    projects
    JOIN organizations ON projects.organization_id = organizations.id;
Daisy Chain|Daisy Chain
Fake Daisy Chain|Fake Org
Mind Control|Daisy Chain
sqlite> 

I also added a test for this behavior to workerd that is passing:

await itShould(
  // ...
  () =>
    DB.prepare(
      `SELECT projects.name, organizations.name
      FROM projects
      JOIN organizations ON projects.organization_id = organizations.id;`
    ).raw(),
  [['Cloudflare', 'Cloudflare']]
)

However, when sending the same request through Workers SDK, I get:

| name       |
| Cloudflare |

As both columns have the same name, name, they got squashed into a single column.

A GitHub user raised the issue a year ago and since then it got fixed in workerd (that my test confirms), but it is still present in Workers SDK.

The problem is also reproducible in Cloudflare D1 Console, where I get the same data:

| name       |
| Cloudflare |

It breaks people's code in unexpected ways and creates an impression of D1 as an unreliable product that might behave differently in production.

In my opinion, it's a critical bug that must be prioritized.

The source

I chased the bug through wrangler → workerd → miniflare to these line in database.worker.ts:

const cursor = this.db.prepare(query.sql)(...params);
const results = convertResults(all(cursor));

When running the test:

await db
  .prepare(
	`SELECT ${tableColours}.name, ${tablePalettes}.name FROM ${tableColours} JOIN ${tablePalettes} ON ${tableColours}.id = ${tablePalettes}.colour_id`
  )
  .raw();

...the cursor object passed through all results in the given structure:

[{ name: "Night" }]

You can see that at this point, the information is already lost, and the name columns have been reduced to one property.

After further researching I found that workerd sends the resultsFormat query that determines the format of the results.

It worked before only because workerd handles the case when the API always returns an array of objects either by sheer luck or as a workaround for Workers SDK behavior.

The solution

The PR fixes the problem and adds proper handling of the resultsFormat query.

It makes raw preserve columns with the same name, addressing the original problem.

It also adds handling of NONE which workerd sends with run and exec (hence the change in existing test cases).

Extras

Here's the SQL I used to test the behavior:

CREATE TABLE organizations (id INTEGER PRIMARY KEY, name TEXT);

CREATE TABLE projects (
    id INTEGER PRIMARY KEY,
    name TEXT,
    organization_id INTEGER,
    FOREIGN KEY (organization_id) REFERENCES organizations(id)
);

INSERT INTO
    organizations (id, name)
VALUES
    (1, 'Cloudflare');
    
INSERT INTO
    projects (id, name, organization_id)
VALUES
    (1, 'Cloudflare', 1);
    
SELECT
    projects.name,
    organizations.name
FROM
    projects
    JOIN organizations ON projects.organization_id = organizations.id;

What this PR solves / how to test

The PR fixes #3160.

Author has addressed the following

• Tests
  • TODO (before merge)
  • Included
  • Not necessary because:
• E2E Tests CI Job required? (Use "e2e" label or ask maintainer to run separately)
  • I don't know
  • Required / Maybe required
  • Not required because:
• Changeset (Changeset guidelines)
  • TODO (before merge)
  • Included
  • Not necessary because:
• Public documentation
  • TODO (before merge)
  • Cloudflare docs PR(s): https://github.com/cloudflare/cloudflare-docs/pull/...
  • Not necessary because: it is expected behavior