【Bug?】Wrong error message "No from or join clause could be found for table alias"

[hiroyuki0415]

SELECT w.name
FROM   workflow AS w
       INNER JOIN
       solutioncomponent AS sc
       ON w.workflowid = sc.objectid
WHERE  w.name = 'xxx'
       OR w.name = 'yyy';

When I run the above SQL, I get the following error

No from or join clause could be found for table alias w
See the Execution Plan tab for details of where this error occurred

• This error message looks wrong (In fact, in SSMS or AzureDataStudio, this SQL can be executed)
• If I change the OR to AND, it works fine🤔.

Thanks in advance for your support.

[MarkMpn]

I'm investigating this one with Microsoft, I believe this is an error in how the FetchXML is processed for specific entities. The same format of query works correctly with other entities, but the error occurs when using filters like this with joins to tables like solution, solutioncomponent etc. I haven't found a full list of affected tables. See also #309.

The query works when the filter can be lifted to within the <link-entity> node in the FetchXML, rather than being at the root level with an entityname attribute. At the moment this SQL generates the FetchXML:

<fetch xmlns:generator='MarkMpn.SQL4CDS'>
  <entity name='solutioncomponent'>
    <link-entity name='workflow' to='objectid' from='workflowid' alias='w' link-type='inner'>
      <attribute name='name' />
    </link-entity>
    <filter type='or'>
      <condition attribute='name' entityname='w' operator='eq' value='xxx' />
      <condition attribute='name' entityname='w' operator='eq' value='yyy' />
    </filter>
  </entity>
</fetch>

If the filter is moved to:

<fetch xmlns:generator='MarkMpn.SQL4CDS'>
  <entity name='solutioncomponent'>
    <link-entity name='workflow' to='objectid' from='workflowid' alias='w' link-type='inner'>
      <attribute name='name' />
      <filter type='or'>
        <condition attribute='name' operator='eq' value='xxx' />
        <condition attribute='name' operator='eq' value='yyy' />
      </filter>
    </link-entity>
  </entity>
</fetch>

it executes as expected.

I'll try to get it to generate this form of FetchXML in the next update, however this won't be possible for all combinations of filters and I believe the ultimate fix will require an update from Microsoft for how they process the FetchXML in this case. In the meantime you can force it to generate this FetchXML by rewriting your filter as part of the join clause:

SELECT w.name
FROM   workflow AS w
       INNER JOIN
       solutioncomponent AS sc
       ON w.workflowid = sc.objectid
          AND (w.name = 'xxx'
               OR w.name = 'yyy');