[BUG]: Broken shifted columns with leftJoin and same column name (on D1)
Problem
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) [code block] ... and run next query to reduce has many pattern. [code block] I got broken results. [code block] - id is swapped. - post data is shifted. Running query is here. [code block] 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. [code block] Environment & setup I'm using pnpm. [code block] D1 setup [code block]
Unverified for your environment
Select your OS to check compatibility.
1 Fix
Fix Column Name Conflicts in Left Join Queries
The issue arises from the left join operation where multiple columns share the same name (e.g., 'id'). Without aliasing these columns using 'as', the ORM misaligns the results when repackaging them into objects, leading to swapped or shifted data.
Awaiting Verification
Be the first to verify this fix
- 1
Identify Conflicting Columns
Review the schema and identify all columns that have the same name across the joined tables. This includes primary keys like 'id' that may exist in both tables.
- 2
Modify the Query to Use Aliases
Update the left join query to use aliases for any conflicting columns. This ensures that each column is uniquely identified in the result set.
sqlSELECT a.id AS post_id, a.title, b.id AS user_id, b.name FROM posts AS a LEFT JOIN users AS b ON a.user_id = b.id; - 3
Test the Updated Query
Run the modified query to ensure that the results are correctly aligned and that there are no more swapped or shifted data issues.
- 4
Update ORM Handling if Necessary
If you are still facing issues, check the ORM configuration to ensure it correctly handles aliased columns. Update any relevant ORM settings or methods to accommodate the changes.
- 5
Review Documentation for Best Practices
Consult the drizzle-orm documentation for best practices regarding joins and column naming to prevent similar issues in future queries.
Validation
Confirm that the query returns the expected results with correctly aligned data. Check that no columns are swapped or shifted and that the output matches the intended schema.
Sign in to verify this fix
Environment
Submitted by
Alex Chen
2450 rep