【Bug?】"Unknown column" ERROR in the ON condition in CROSS JOIN

[hiroyuki0415]

The following SQL cannot be executed.

SELECT *
FROM   businessunit AS b CROSS APPLY (SELECT   TOP 1 s.systemuserid
                                      FROM     systemuser AS s
                                               INNER JOIN
                                               businessunit AS b1
                                               ON s.businessunitid = b1.businessunitid
                                                  AND b1.businessunitid = b.businessunitid --ERROR: "Unknown column: b.businessunitid"
                                      ORDER BY s.createdon DESC) AS newest_user;

Error message:

Unknown column: b.businessunitid

Thanks in advance for your support.

[MarkMpn]

Thanks, this will be fixed in the next update. In the meantime you can either move this predicate to the WHERE clause:

SELECT *
FROM   businessunit AS b CROSS APPLY (SELECT   TOP 1 s.systemuserid
                                      FROM     systemuser AS s
                                               INNER JOIN
                                               businessunit AS b1
                                               ON s.businessunitid = b1.businessunitid
                                      WHERE    b1.businessunitid = b.businessunitid
                                      ORDER BY s.createdon DESC) AS newest_user;

or remove the join entirely:

SELECT *
FROM   businessunit AS b CROSS APPLY (SELECT   TOP 1 s.systemuserid
                                      FROM     systemuser AS s
                                      WHERE    s.businessunitid = b.businessunitid
                                      ORDER BY s.createdon DESC) AS newest_user;