[BUG]: Broken shifted columns with leftJoin and same column name (on D1) #555

mizchi · 2023-05-06T15:58:13Z

What version of `drizzle-orm` are you using?

0.25.4

What version of `drizzle-kit` are you using?

0.17.6

Describe the Bug

I got broken results with leftJoin with same columns.

Details

My schema (partial)

import { sqliteTable, text, integer, uniqueIndex } from 'drizzle-orm/sqlite-core';
import { InferModel } from 'drizzle-orm';

export const users = sqliteTable('users', {
  id: integer('id').primaryKey().notNull(),
  displayName: text('displayName').notNull(),
});
export const posts = sqliteTable('posts', {
  id: integer('id').primaryKey().notNull(),
  ownerId: integer('ownerId').references(() => users.id),
  title: text('title').notNull(),
  content: text('content').notNull(),
});

... and run next query to reduce has many pattern.

  const userAndPosts = await db
    .select({
      user: {
        id: users.id, // "users"."id"
      },
      post: {
        id: posts.id, // "posts"."id"
        ownerId: posts.ownerId,
        title: posts.title,
        content: posts.content
      }
    })
    .from(users)
    .leftJoin(posts, eq(posts.ownerId, users.id))
    .all();

I got broken results.

[
  {
    user: { id: 13, displayName: 'kotaro chikuba (mizchi)' },
    post: { id: 1, ownerId: 'xx', title: 'x', content: undefined }
  },
  {
    user: { id: 1, displayName: 'kotaro chikuba (mizchi)' },
    post: { id: 1, ownerId: 'xxx', title: 'ueoaueoa', content: undefined }
  },
  {
    user: { id: 2, displayName: 'kotaro chikuba (mizchi)' },
    post: {
      id: 1,
      ownerId: 'xxxeeoa',
      title: 'ueoaueoa',
      content: undefined
    }
  }
]

id is swapped.
post data is shifted.

Running query is here.

select "users"."id", "posts"."id", "posts"."ownerId", "posts"."title", "posts"."content" from "users" left join "posts" on "posts"."ownerId" = "users"."id"

I think that duplicate id columns have been executed without as, causing the ORM to be misaligned when it repackages them into the object.

If one id is not referenced, this bug is resolved.

Expected behavior

I expect I can use both id and correct columns.

[
  {
    user: { id: 1, displayName: 'kotaro chikuba (mizchi)' },
    post: { id: 13, ownerId: 1, title: 'titile', content: 'body' }
  },
]

Environment & setup

I'm using pnpm.

pnpm add drizzle-kit drizzle-orm -D

D1 setup

import { drizzle } from 'drizzle-orm/d1';

export function createClient(db: D1Database) {
  return drizzle(db);
}

The text was updated successfully, but these errors were encountered:

AndriiSherman · 2023-05-06T16:00:29Z

Does this bug reproducible using raw driver for d1 without drizzle?

mizchi · 2023-05-06T16:05:49Z

This is d1 raw results.

$ 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       │
└────┴─────────┴───────┴───────────┘

AndriiSherman · 2023-05-06T16:09:33Z

Seems like this is an issue on d1 side. I’ll create an issue on their repo and will pin it here to track the progress

AndriiSherman · 2023-05-07T13:12:02Z

@mizchi could you please share wrangler version you are using? And OS

mizchi · 2023-05-07T15:32:45Z

@AndriiSherman

"wrangler": "2.19.0"

$ sw_vers
ProductName:            macOS
ProductVersion:         13.3.1
ProductVersionExtra:    (a)
BuildVersion:           22E772610a

AndriiSherman · 2023-05-07T15:36:33Z

tracking issue on d1 side

cloudflare/workers-sdk#3160

Angelelz · 2023-12-17T03:35:26Z

This seems like an outdated issue. Can anybody check? seems like the underlying issue was resolved?

WcaleNieWolny · 2023-12-18T07:56:31Z

The problem is that d1 overwrites columns if they share the same key. As it can be seen here

  const userAndPosts = await db
    .select({
      user: {
        id: users.id, // "users"."id"
      },
      post: {
        id: posts.id, // "posts"."id"
        ownerId: posts.ownerId,
        title: posts.title,
        content: posts.content
      }
    })
    .from(users)
    .leftJoin(posts, eq(posts.ownerId, users.id))
    .all();

Both posts.id and "users"."id" are returned by cloudflare as id meaning that the data is overwritten. A very dirty hack would be to use raw sql statements

  const userAndPosts = await db
    .select({
      user: {
        id: sql<type>`${users.id}`.as('u_id')
      },
      post: {
        id: sql<type>`${posts.id}`.as('p_id'),
        ownerId: posts.ownerId,
        title: posts.title,
        content: posts.content
      }
    })
    .from(users)
    .leftJoin(posts, eq(posts.ownerId, users.id))
    .all();

This works as then d1 does not overwrite the data. However this solution is very ugly and it removes type inference for the affected columns

drizzle-team/drizzle-orm#555 cloudflare/workerd#696

jwhits · 2024-01-07T22:51:12Z

This seems like an outdated issue. Can anybody check? seems like the underlying issue was resolved?

I am still getting the issue on joins with Drizzle ORM and wrangler.

jwhits · 2024-01-08T02:00:14Z

On further testing using select compared to findmany I am not sure if this is a drizzle or D1 issue? I have data tables with some of the same common field column names like create_at, updated_at, id etc

When using select

const userEvents = await db
    .select()
    .from(event)
    .innerJoin(eventInvite, eq(event.id, eventInvite.eventId))
    .innerJoin(user, eq(eventInvite.userId, user.id))
    .where(
      sql`events.id IN (SELECT event_id FROM event_invites WHERE user_id = ${userId})`,
    );

I get the column data results shift bug and users createdAt and updatedAt fields missing

{
    "userEvents": [
      {
        "events": {
          "id": "EventId1",
          "name": "Event One",
          "location": "Location A",
          "createdAt": "2024-01-03 08:58:31",
          "updatedAt": "2024-01-03 09:19:29"
        },
        "event_invites": {
          "eventId": "EventId1",
          "userId": "UserId1",
          "inviteStatus": "No Response",
          "responseDate": null,
          "createdAt": "UserId1",
          "updatedAt": "FirstName1test"
        },
        "users": {
          "id": "LastName1",
          "firstName": "User1",
          "lastName": "user1@example.com",
          "username": "password1"
        }
      },
      {
        "events": {
          "id": "EventId1",
          "name": "Event One",
          "location": "Location A",
          "createdAt": "2024-01-03 08:58:31",
          "updatedAt": "2024-01-03 08:58:31"
        },
        "event_invites": {
          "eventId": "EventId1",
          "userId": "UserId2",
          "inviteStatus": "No Response",
          "responseDate": null,
          "createdAt": "UserId2",
          "updatedAt": "FirstName2"
        },
        "users": {
          "id": "LastName2",
          "firstName": "User2",
          "lastName": "user2@example.com",
          "username": "password2"
        }
      }
    ]
  }

However when using findmany

const invitedEventsSubquery = db
    .select({ data: eventInvite.eventId })
    .from(eventInvite)
    .where(eq(eventInvite.userId, userId));

  const userEvents = await db.query.event.findMany({
    where: inArray(event.id, invitedEventsSubquery),
    with: {
      eventInvite: {
        with: {
          user: true,
        },
      },
    },
  });

I don't get the bug. Every column is returned where it should be in nicely nested json.

{
    "userEvents": [
      {
        "id": "EventId1",
        "name": "Event One",
        "location": "Location A",
        "createdAt": "2024-01-03 09:35:52",
        "updatedAt": "2024-01-03 09:35:52",
        "eventInvite": [
          {
            "eventId": "EventId1",
            "userId": "UserId1",
            "inviteStatus": "No Response",
            "responseDate": null,
            "createdAt": "2024-01-03 10:14:40",
            "updatedAt": "2024-01-03 10:14:40",
            "user": {
              "id": "UserId1",
              "firstName": "FirstName1test",
              "lastName": "LastName1",
              "username": "User1",
              "email": "user1@example.com",
              "password": "password1",
              "createdAt": "2024-01-03 08:58:31",
              "updatedAt": "2024-01-03 09:19:29"
            }
          },
          {
            "eventId": "EventId1",
            "userId": "UserId2",
            "inviteStatus": "No Response",
            "responseDate": null,
            "createdAt": "2024-01-03 10:14:40",
            "updatedAt": "2024-01-03 10:14:40",
            "user": {
              "id": "UserId2",
              "firstName": "FirstName2",
              "lastName": "LastName2",
              "username": "User2",
              "email": "user2@example.com",
              "password": "password2",
              "createdAt": "2024-01-03 08:58:31",
              "updatedAt": "2024-01-03 08:58:31"
            }
          },
          {
            "eventId": "EventId1",
            "userId": "UserId3",
            "inviteStatus": "No Response",
            "responseDate": null,
            "createdAt": "2024-01-03 10:14:40",
            "updatedAt": "2024-01-03 10:14:40",
            "user": {
              "id": "UserId3",
              "firstName": "FirstName3",
              "lastName": "LastName3",
              "username": "User3",
              "email": "user3@example.com",
              "password": "password3",
              "createdAt": "2024-01-03 08:58:31",
              "updatedAt": "2024-01-03 08:58:31"
            }
          }
        ]
      },
      {
        "id": "EventId3",
        "name": "Event Three",
        "location": "Location C",
        "createdAt": "2024-01-03 09:35:52",
        "updatedAt": "2024-01-03 09:35:52",
        "eventInvite": [
          {
            "eventId": "EventId3",
            "userId": "UserId1",
            "inviteStatus": "No Response",
            "responseDate": null,
            "createdAt": "2024-01-03 10:14:40",
            "updatedAt": "2024-01-03 10:14:40",
            "user": {
              "id": "UserId1",
              "firstName": "FirstName1test",
              "lastName": "LastName1",
              "username": "User1",
              "email": "user1@example.com",
              "password": "password1",
              "createdAt": "2024-01-03 08:58:31",
              "updatedAt": "2024-01-03 09:19:29"
            }
          }
        ]
      }
    ]
  }

alexturpin · 2024-01-09T07:10:22Z

Thanks for the workaround @WcaleNieWolny, this works for me while maintaining type support it seems:

    .select({
      id: transactions.id,
      date: transactions.date,
      foreignCurrencyCode: transactions.foreignCurrencyCode,
      inboxCurrencyCode: sql<string>`${inboxes.currencyCode}`.as("inbox_currency_code"),
      cardType: cards.type,
      cardCurrencyCode: sql<string>`${cards.currencyCode}`.as("card_currency_code"),
    })

quolpr · 2024-01-17T22:57:25Z

It happens with SQLite too. This query:

qb
  .select({
    dailyListId: dailyListsTable.id,
    projectionId: todoProjectionsTable.id,
    todoId: todosTable.id,
    projectId: projectsTable.id,
  })
  .from(dailyListsTable)
  .fullJoin(
    todoProjectionsTable,
    eq(todoProjectionsTable.dailyListId, dailyListsTable.id),
  )
  .fullJoin(todosTable, eq(todosTable.id, todoProjectionsTable.todoId))
  .fullJoin(projectsTable, eq(projectsTable.id, todosTable.projectId)),

Generate with SQL query:

select "dailyLists"."id", "todoProjections"."id", "todos"."id", "projects"."id" from "dailyLists" full join "todoProjections" on "todoProjections"."dailyListId" = "dailyLists"."id" full join "todos" on "todos"."id" = "todoProjections"."todoId" full join "projects" on "projects"."id" = "todos"."projectId"

And due to how SQLite itself run the query, rows returns like this: {id: string}[].

I think the problem is on drizzle side(and aliasing will fix it)

jwhits · 2024-01-19T10:32:32Z

It happens with SQLite too. This query:

qb
  .select({
    dailyListId: dailyListsTable.id,
    projectionId: todoProjectionsTable.id,
    todoId: todosTable.id,
    projectId: projectsTable.id,
  })
  .from(dailyListsTable)
  .fullJoin(
    todoProjectionsTable,
    eq(todoProjectionsTable.dailyListId, dailyListsTable.id),
  )
  .fullJoin(todosTable, eq(todosTable.id, todoProjectionsTable.todoId))
  .fullJoin(projectsTable, eq(projectsTable.id, todosTable.projectId)),

Generate with SQL query:

select "dailyLists"."id", "todoProjections"."id", "todos"."id", "projects"."id" from "dailyLists" full join "todoProjections" on "todoProjections"."dailyListId" = "dailyLists"."id" full join "todos" on "todos"."id" = "todoProjections"."todoId" full join "projects" on "projects"."id" = "todos"."projectId"

And due to how SQLite itself run the query, rows returns like this: {id: string}[].

I think the problem is on drizzle side(and aliasing will fix it)

Is this repo available to test?

I tried testing just Sqlite using the Drizzle SQLite example repo I also added an extra table for a many to many join. I couldn't reproduce it in SQLite. However the issue still exists using D1

jwhits · 2024-01-19T11:49:38Z

@AndriiSherman or @Angelelz have you got any more insights on this issue?
From what I can tell the CF D1 issue 696 has been closed and a code change merged into main. However I can't actually tell what was done to fix it?

It seems like D1 is expecting duplicate columns to have aliases? If this is the case is this something that needs to happen on the drizzle side?

When running a join query directly with wrangler without alias on duplicate columns, it only returns one of the duplicate columns, however the values are wrong. Compared to using drizzle it returns all the columns but the values shifted. When using aliases in wrangler it works as expected.

Using DB browser for SQLite connecting directly to the local D1 Sqlite database, all columns and values return correctly without aliases.

vincaslt · 2024-01-26T13:23:09Z

Is there a way to auto-alias columns, or maybe nest them like .findMany does when using db.schema.query, maybe by means of something similar to knex's .options({ nestTables: true })? The find* queries don't seem to be affected by this issue as they're using nested select queries, but joining when using builder syntax doesn't work. Solving the issue via manual aliasing for affected columns is not a great solution as it's very brittle and prone to error, in addition to poor DX.

My workaround for this is to implement a utility that auto-prefixes all columns from the table (I'm selecting all columns, would need to adjust to select a subset):

function autoAliasColumns<T extends TableConfig>(
  table: SQLiteTableWithColumns<T>,
): {
  [Key in keyof T["columns"]]: T["columns"][Key];
} {
  const { name: tableName } = getTableConfig(table);

  return Object.entries(table).reduce<any>((acc, [colName, column]) => {
    acc[colName] = sql`${column}`.as(`${tableName}__${column.name}`);
    return acc;
  }, {});
}

const courseQ = initDB(context)
  .select({
    course: autoAliasColumns(schema.course),
    member: autoAliasColumns(schema.courseMember),
    module: autoAliasColumns(schema.module),
    lesson: autoAliasColumns(schema.lesson),
  })
 // ...

AndriiSherman · 2024-02-05T18:28:09Z

Seems like it was fixed on the D1 side, so we need to run tests and check it. The issue with aliasing is a D1 problem that should be addressed on the D1 side. As a standard practice in any driver library, D1 should have the capability to respond with an array of arrays without any isues. If they don't, it's a bug on their side.

I hope this cloudflare/workerd#696 fixed the problem. If not, we will need to create another issue for D1

AndriiSherman · 2024-02-05T18:28:44Z

Also seems like this original issue is still open cloudflare/workers-sdk#3160

geelen · 2024-02-13T22:39:21Z

The D1 fix is now live. .raw() returns the full result set, regardless of clashes in column names.

F0rce · 2024-03-03T21:01:09Z

it seems like the types have been released in 4.20240222.0 (with a bit of delay). is there a way to get this issue sort of prioritised because especially with Lucia this is a complete blocker when trying to use D1 and drizzle.

cyco130 · 2024-03-07T16:47:12Z

I created a reproduction before searching for similar issues. I might as well share it, maybe it can help.

https://github.com/cyco130/drizzle-d1-bug

dankochetov · 2024-03-20T22:59:37Z

@cyco130 thank you for the reproduction, looks like there's still an issue on D1 side. I've reported it back to their team: cloudflare/workers-sdk#3160 (comment)

fernandortec · 2024-04-10T19:39:56Z

sql<type>`${users.id}`.as('u_id')

did you managed to fix it? I'm having the same problem

kossnocorp · 2024-05-31T06:14:56Z

Good news, my fix landed with wrangler@3.57.2, and I can confirm that the problem is gone.

@AndriiSherman @dankochetov, you can close the issue!

brenfwd · 2024-06-24T22:31:28Z

FWIW, also getting this issue with the Bun sqlite driver

mizchi added the bug Something isn't working label May 6, 2023

AndriiSherman mentioned this issue May 7, 2023

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

Open

NielXu mentioned this issue May 15, 2023

[BUG]: Leftjoin is overriding the columns with the same names #582

Closed

AndriiSherman mentioned this issue May 23, 2023

[BUG]: Sqlite Cloudflare D1 inner joins do not work correctly #528

Closed

tufusa mentioned this issue Sep 3, 2023

fix: モデルを直す mct-joken/WashBoard#32

Closed

beeequeue added a commit to beeequeue/dota-matches-api that referenced this issue Jan 7, 2024

work around D1 bug

c471b59

drizzle-team/drizzle-orm#555 cloudflare/workerd#696

beeequeue mentioned this issue Jan 7, 2024

Replace itty-* with Hono, Workaround D1 Bug beeequeue/dota-matches-api#261

Merged

serrf0f mentioned this issue Feb 9, 2024

[Bug]: Cloudflare D1 issue on Lucia 1.0.1 lucia-auth/lucia#1420

Closed

F0rce mentioned this issue Mar 4, 2024

[BUG]: Incorrect/misaligned object mapping #1959

Closed

ReWWeR mentioned this issue May 7, 2024

[BUG]: more than one JOIN return not correct response in SQLlite #2267

Closed

kossnocorp mentioned this issue May 26, 2024

Partially fix #3160 by properly handling resultsFormat query cloudflare/workers-sdk#5917

Merged

12 tasks

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

[BUG]: Broken shifted columns with leftJoin and same column name (on D1) #555

[BUG]: Broken shifted columns with leftJoin and same column name (on D1) #555

mizchi commented May 6, 2023 •

edited

Loading

AndriiSherman commented May 6, 2023

mizchi commented May 6, 2023

AndriiSherman commented May 6, 2023

AndriiSherman commented May 7, 2023 •

edited

Loading

mizchi commented May 7, 2023 •

edited

Loading

AndriiSherman commented May 7, 2023

Angelelz commented Dec 17, 2023

WcaleNieWolny commented Dec 18, 2023

jwhits commented Jan 7, 2024

jwhits commented Jan 8, 2024 •

edited

Loading

alexturpin commented Jan 9, 2024 •

edited

Loading

quolpr commented Jan 17, 2024 •

edited

Loading

jwhits commented Jan 19, 2024

jwhits commented Jan 19, 2024

vincaslt commented Jan 26, 2024 •

edited

Loading

AndriiSherman commented Feb 5, 2024

AndriiSherman commented Feb 5, 2024

geelen commented Feb 13, 2024

F0rce commented Mar 3, 2024

cyco130 commented Mar 7, 2024 •

edited

Loading

dankochetov commented Mar 20, 2024

fernandortec commented Apr 10, 2024

kossnocorp commented May 31, 2024

brenfwd commented Jun 24, 2024

[BUG]: Broken shifted columns with leftJoin and same column name (on D1) #555

[BUG]: Broken shifted columns with leftJoin and same column name (on D1) #555

Comments

mizchi commented May 6, 2023 • edited Loading

What version of drizzle-orm are you using?

What version of drizzle-kit are you using?

Describe the Bug

Details

Expected behavior

Environment & setup

AndriiSherman commented May 6, 2023

mizchi commented May 6, 2023

AndriiSherman commented May 6, 2023

AndriiSherman commented May 7, 2023 • edited Loading

mizchi commented May 7, 2023 • edited Loading

AndriiSherman commented May 7, 2023

Angelelz commented Dec 17, 2023

WcaleNieWolny commented Dec 18, 2023

jwhits commented Jan 7, 2024

jwhits commented Jan 8, 2024 • edited Loading

alexturpin commented Jan 9, 2024 • edited Loading

quolpr commented Jan 17, 2024 • edited Loading

jwhits commented Jan 19, 2024

jwhits commented Jan 19, 2024

vincaslt commented Jan 26, 2024 • edited Loading

AndriiSherman commented Feb 5, 2024

AndriiSherman commented Feb 5, 2024

geelen commented Feb 13, 2024

F0rce commented Mar 3, 2024

cyco130 commented Mar 7, 2024 • edited Loading

dankochetov commented Mar 20, 2024

fernandortec commented Apr 10, 2024

kossnocorp commented May 31, 2024

brenfwd commented Jun 24, 2024

mizchi commented May 6, 2023 •

edited

Loading

What version of `drizzle-orm` are you using?

What version of `drizzle-kit` are you using?

AndriiSherman commented May 7, 2023 •

edited

Loading

mizchi commented May 7, 2023 •

edited

Loading

jwhits commented Jan 8, 2024 •

edited

Loading

alexturpin commented Jan 9, 2024 •

edited

Loading

quolpr commented Jan 17, 2024 •

edited

Loading

vincaslt commented Jan 26, 2024 •

edited

Loading

cyco130 commented Mar 7, 2024 •

edited

Loading