hasOne()->ofMany() failing in SQL Server with Ambiguous Column Name error since update to 8.67.0

[MartinHughes-BPC]

• Laravel Version: 8.67.0
• PHP Version: 7.4
• Database Driver & Version: MS SQL Server 2019

Description:

I have the following relationship:

return $this->hasOne(AssignmentMark::class, 'enrolment_id', 'EnrolmentID')
            ->ofMany([
                'assignment_id' => 'max'
            ],
            function ($query) {
              $query->join('assignments AS a', 'a.id', 'assignment_marks.assignment_id')
                ->where('due_date', '<=', now())
                ->whereNotNull('assignment_marks.grade')
                ->whereIn('assignment_type_id', [1, 2]);
            },
                'am'
            )->withDefault();

Comparing the generated SQL between 8.64 and 8.67, I notice that it is quite different. The problem seems to stem from the new version adding an additional join to the assignments table, which causes the ambiguous column:

Old (working) SQL:

SELECT TOP 1 *
FROM   [focus].[dbo].[assignment_marks]
       INNER JOIN (SELECT Max([id]) AS [id],
                          [focus].[dbo].[assignment_marks].[enrolment_id]
                   FROM   [focus].[dbo].[assignment_marks]
                          INNER JOIN (....

New (failing) SQL:

SELECT TOP 1
           [focus].[dbo].[assignment_marks].*
FROM       [focus].[dbo].[assignment_marks]
INNER JOIN
           (
                      SELECT     Max([id]) AS [id_aggregate],
                                 [focus].[dbo].[assignment_marks].[enrolment_id]
                      FROM       [focus].[dbo].[assignment_marks]
                      INNER JOIN [assignments] AS [a] ON [a].[id] = [assignment_marks].[assignment_id]  /*This join causes the problem /*
                      INNER JOIN
                                 (....

There are a number of other changes to the generated SQL that appear to me to be a lot more messy than in the original version (happy to reply with a side-by-side comparison if useful).

For the moment I've reverted to Laravel 8.64.

Cheers,
Martin.

[bastien-phi]

Can you provide the full error and the full query ?

[MartinHughes-BPC]

Sure thing (this from the log file):

local.ERROR: SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Ambiguous column name 'id'. (SQL: select top 1 [focus].[dbo].[assignment_marks].* from [focus].[dbo].[assignment_marks] inner join (select MAX([id]) as [id_aggregate], [focus].[dbo].[assignment_marks].[enrolment_id] from [focus].[dbo].[assignment_marks] inner join [assignments] as [a] on [a].[id] = [assignment_marks].[assignment_id] inner join (select max([assignment_id]) as [assignment_id_aggregate], [focus].[dbo].[assignment_marks].[enrolment_id] from [focus].[dbo].[assignment_marks] inner join [assignments] as [a] on [a].[id] = [assignment_marks].[assignment_id] where [due_date] <= 2021-10-26 13:36:34.768 and [assignment_marks].[grade] is not null and [assignment_type_id] in (1, 2) and [focus].[dbo].[assignment_marks].[enrolment_id] = 225334 and [focus].[dbo].[assignment_marks].[enrolment_id] is not null group by [focus].[dbo].[assignment_marks].[enrolment_id]) as [am] on [am].[assignment_id_aggregate] = [focus].[dbo].[assignment_marks].[assignment_id] and [am].[enrolment_id] = [focus].[dbo].[assignment_marks].[enrolment_id] where [due_date] <= 2021-10-26 13:36:34.768 and [assignment_marks].[grade] is not null and [assignment_type_id] in (1, 2) group by [focus].[dbo].[assignment_marks].[enrolment_id]) as [am] on [am].[id_aggregate] = [focus].[dbo].[assignment_marks].[id] and [am].[enrolment_id] = [focus].[dbo].[assignment_marks].[enrolment_id] where [focus].[dbo].[assignment_marks].[enrolment_id] = 225334 and [focus].[dbo].[assignment_marks].[enrolment_id] is not null)

And for completeness, here's what the query looks like in the previous working state:

select top 1 * from [focus].[dbo].[assignment_marks] inner join (select MAX([id]) as [id], [focus].[dbo].[assignment_marks].[enrolment_id] from [focus].[dbo].[assignment_marks] inner join (select max([assignment_id]) as [assignment_id], [focus].[dbo].[assignment_marks].[enrolment_id] from [focus].[dbo].[assignment_marks] inner join [assignments] as [a] on [a].[id] = [assignment_marks].[assignment_id] where [due_date] <= '2021-10-26 16:18:33.640' and [assignment_marks].[grade] is not null and [assignment_type_id] in (1, 2) and [focus].[dbo].[assignment_marks].[enrolment_id] = 225334 and [focus].[dbo].[assignment_marks].[enrolment_id] is not null group by [focus].[dbo].[assignment_marks].[enrolment_id]) as [am] on [am].[assignment_id] = [focus].[dbo].[assignment_marks].[assignment_id] and [am].[enrolment_id] = [focus].[dbo].[assignment_marks].[enrolment_id] group by [focus].[dbo].[assignment_marks].[enrolment_id]) as [am] on [am].[id] = [focus].[dbo].[assignment_marks].[id] and [am].[enrolment_id] = [focus].[dbo].[assignment_marks].[enrolment_id] where [focus].[dbo].[assignment_marks].[enrolment_id] = 225334 and [focus].[dbo].[assignment_marks].[enrolment_id] is not null

[bastien-phi]

I'll try to take a look on this.

I guess SELECT Max([id]) AS [id_aggregate], should be SELECT Max([focus].[dbo].[assignment_marks].[id]) AS [id_aggregate],

[bastien-phi]

@MartinHughes-BPC Can you test the fix ? It should solve the issue

[MartinHughes-BPC]

Hi @bastien-phi

Tested and working - yes, it was the need to qualify the aggregated column. I'm still a bit intrigued as to why the new version added the additional join (and duplicated the where conditions) when the original only put these in the very inner select?

Anyway, thanks for looking at this and providing a fix.

Cheers,
Martin.

[bastien-phi]

@MartinHughes-BPC you will find more information on why we need to duplicate the where conditions in #39187

No problem, I'm happy to help !