Help finding join without predicate

Much like a related question by swasheck, I have a query that historically has suffered from performance problems. I was looking through the query plan on SSMS and noticed a Nested Loops (Inner Join) with the warning:


No Join Predicate

Based on some hasty research (confidence inspiring Scary DBA, and Brent Ozar) it looks like this warning is telling me I have a hidden Cartesian product in my query. I've checked my query a few times and don't see the cross join. Here is the query:

```
DECLARE @UserId INT; -- Stored procedure input
DECLARE @Now DATETIME2(7) = SYSUTCDATETIME();
;WITH AggregateStepData_CTE AS -- Considering converting this CTE into an indexed view
(
SELECT
[UA].[UserId] -- FK to the UserId
, [UA].[DeviceId] -- FK to the push device's DeviceId (int)
, SUM(ISNULL([UA].[LatestSteps], 0)) AS [Steps]
FROM [User].[UserStatus] [UA]
INNER JOIN [User].[CurrentConnections] [M] ON
[M].[Monitored] = [UA].[UserId] AND [M].[Monitor] = @UserId
WHERE
[M].[ShareSteps] = 1 -- Only use step data if we are allowed to see.
AND
CAST([UA].[ReportedLocalTime] AS DATE) =
CAST(DATEADD(MINUTE, DATEPART(TZOFFSET, [UA].[ReportedLocalTime]), @Now) AS DATE)
-- Aggregate the steps for today based on the device's time zone.
GROUP BY
[UA].[UserId]
, [UA].[DeviceId]
)
SELECT
[UA].[UserId] -- FK to the UserId
, [UA].[ReportedLocalTime]
, CASE WHEN [M].[ShareLocation] = 1 THEN [UA].[Latitude] ELSE NULL END AS [Latitude]
, CASE WHEN [M].[ShareLocation] = 1 THEN [UA].[Longitude] ELSE NULL END AS [Longitude]
, CASE WHEN [M].[ShareLocation] = 1 THEN [UA].[LocationAccuracy] ELSE NULL END
AS [LocationAccuracy]
, CASE WHEN [M].[ShareSteps] = 1 THEN ISNULL([SD].[Steps], 0) ELSE NULL END AS [Steps]
, CASE WHEN [M].[ShareBattery] = 1 THEN [UA].[BatteryPercentage] ELSE NULL END
AS [BatteryPercentage]
, CASE WHEN

In my CTE I was missing a WITH (NOEXPAND) query hint. Once I added this query hint the join without predicate disappeared from my query plan.

;WITH AggregateStepData_CTE AS
(
    SELECT
        [UA].[UserId]
        , [UA].[DeviceId]
        , SUM(ISNULL([UA].[LatestSteps], 0)) AS [Steps]
    FROM [User].[UserStatus] [UA]
        INNER JOIN [User].[CurrentConnections] [M] WITH (NOEXPAND) -- Added query hint here
          ON [M].[Monitored] = [UA].[UserId] AND [M].[Monitor] = @UserId
    WHERE
        [M].[ShareSteps] = 1 -- Only use step data if we are allowed to see.
        AND
        CAST([UA].[ReportedLocalTime] AS DATE) = 
          CAST(DATEADD(MINUTE, DATEPART(TZOFFSET, [UA].[ReportedLocalTime]), @Now) AS DATE) 
          -- Aggregate the steps for today based on the device's time zone.         
    GROUP BY
        [UA].[UserId]
        , [UA].[DeviceId]
)

StackExchange Database Administrators Q#117506, answer score: 4