workers-sdk: 🐛 BUG: when trying to left join 2 tables that have same name columns - second column is not returned

Which Cloudflare product(s) does this pertain to?

What version of `Wrangler` are you using?

2.19.0

What operating system are you using?

macOS

Describe the Bug

As per issue #555 we can see, that d1 stopped returning duplicate names from different tables. It was working before and it’s how any other driver is doing for raw mode

$ pnpm wrangler d1 execute dzltest --command='select "users"."id", "posts"."id", "posts"."ownerId", "posts"."title", "posts"."content" from "users" left join "posts" on "posts"."ownerId" = "users"."id";'

┌────┬─────────┬───────┬───────────┐
│ id │ ownerId │ title │ content   │
├────┼─────────┼───────┼───────────┤
│ 2  │ 1       │ test  │ 111111111 │
├────┼─────────┼───────┼───────────┤
│ 3  │ 1       │ ueoau │ ueueu     │
├────┼─────────┼───────┼───────────┤
│ 1  │ 1       │ xxx   │ yyy       │
└────┴─────────┴───────┴───────────┘

But should return id for user and id for posts as well Expected

┌────┬────┬─────────┬───────┬───────────┐
│ id │ id │ ownerId │ title │ content   │
├────┼────┼─────────┼───────┼───────────┤
│ 2  │ 1  │    1    │ test  │ 111111111 │
├────┼────┼─────────┼───────┼───────────┤
│ 3  │ 2  │    1    │ ueoau │ ueueu     │
├────┼────┼─────────┼───────┼───────────┤
│ 1  │ 3  │    1    │ xxx   │ yyy       │
└────┴────┴─────────┴───────┴───────────┘

About this issue

Original URL
State: open
Created a year ago
Reactions: 15
Comments: 20 (10 by maintainers)

Most upvoted comments

We are working on this. Current priority is larger databases, which is the biggest ask from most users.

+13

elithrar on Aug 1, 2023

@rozenmd I’ve tested this with latest types version (4.20240320.1), and the .raw() method still doesn’t return multiple columns with the same name. This change shouldn’t require any actions from our side, since we don’t bundle @cloudflare/workers-types with Drizzle, it’s used as a peer dependency, so it should be enough for users to just update their dependency version. However, it doesn’t seem to be the case.

Here’s a reproduction repo: https://github.com/dankochetov/drizzle-d1-bug I’ve added a raw D1 call to make sure it wasn’t Drizzle’s fault, and observed the same behavior. As you could see from the logs, the columns list in the response don’t match the columns list in the query.

dankochetov on Mar 20, 2024

We are working on this. Current priority is larger databases, which is the biggest ask from most users.

Is there any update on resolving this issue? I have tables with the same names for common fields like created_at, updated_at, id etc and joins don’t work because all the response data is shifting columns in the results.

jwhits on Jan 19, 2024

Changelog: https://developers.cloudflare.com/d1/platform/changelog/#2024-02-13

cc @AndriiSherman on the Drizzle team.

elithrar on Feb 14, 2024

has been fixed in production though

@geelen I’m using the latest wrangler 3.37.0 to deploy my app with compatibility_date = "2024-03-25" and I’m still facing this issue.

Is there anything special I need to do to get the new version?

stefanmaric on Mar 25, 2024

FYI, this will be fixed D1 once https://github.com/cloudflare/workerd/pull/1586 is merged and rolls out

It will only fix the results of .raw(), .all() will still have later columns with the same name overwriting earlier ones.

geelen on Jan 30, 2024

@AndriiSherman sorry for the delay there - the types are released in 4.20240222.0

rozenmd on Feb 23, 2024

Changelog: https://developers.cloudflare.com/d1/platform/changelog/#2024-02-13

cc @AndriiSherman on the Drizzle team.

Has it been released already? I would like to run some tests, can’t find this version for types on npm

AndriiSherman on Feb 15, 2024