[BUG]: Selecting from sub-query does not qualify the field
Problem
What version of `drizzle-orm` are you using? 0.28.6 What version of `drizzle-kit` are you using? n/a Describe the Bug I have this query / subquery [code block] which generates this SQL [code block] Note the outermost selection list on line 1 of the sql. Since the subquery is aliased as `agg` I believe the `subjects` selection should be qualified as [code block] like [code block] is in the join condition here [code block] Expected behavior Field should be aliased Environment & setup planetscale-serverless
Unverified for your environment
Select your OS to check compatibility.
1 Fix
Fix Field Qualification in Sub-query Selection
The issue arises because the outermost selection in the SQL query does not qualify the field from the sub-query with its alias. This leads to ambiguity in field resolution, especially when the same field name exists in both the outer query and the sub-query.
Awaiting Verification
Be the first to verify this fix
- 1
Identify the Sub-query
Locate the sub-query in your code that is generating the SQL. Ensure that you understand the structure and the aliases used in the query.
typescriptconst subQuery = db.select(...).from(...).as('agg'); - 2
Modify the Outer Selection
Update the outer selection to qualify the field from the sub-query with its alias. This ensures that the SQL generated is unambiguous and correctly references the intended field.
typescriptconst query = db.select(['agg.subjects']).from(subQuery); - 3
Test the Query
Run the modified query to ensure that it executes without errors and returns the expected results. Check the SQL output to confirm that the field is properly qualified.
typescriptconst result = await query.execute(); - 4
Review Other Queries
Check other queries in your codebase that may have similar structures to ensure they also qualify fields correctly to prevent similar issues.
Validation
Confirm the fix by executing the modified query and verifying that the SQL output correctly qualifies the field with the alias 'agg'. Ensure that the results match the expected output without any ambiguity.
Sign in to verify this fix
Environment
Submitted by
Alex Chen
2450 rep