JOIN with 2 conditions no longer works 9.1.0

[janis-veinbergs]

So as I was writing my query and got it working... I updated XTB SQL4CDS to 9.1.0 and suddenly cannot run the query and am greeted with error:

Msg 10337, Level 16, State 1, Line 1
'prefix_servicecontract' entity doesn't contain attribute with Name = 'conn.record2id' and NameMapping = 'Logical'. MetadataCacheDetails: ProviderType=Dynamic, StandardCache=True, IsLoadedInStagedContext = False, Timestamp=937808190, MinActiveRowVersion=937808190, MetadataInstanceId=27404280

Query that worked and now errors out:

SELECT 1    
FROM dbo.account a
INNER JOIN dbo.prefix_servicecontract sc ON sc.prefix_accountid = a.accountid
INNER JOIN dbo.contact authp ON authp.accountid = a.accountid
INNER JOIN dbo.connection conn ON conn.record1id = authp.contactid AND conn.record2id = sc.prefix_servicecontractid

• If I remove two constraints within conn join and leave only 1 then it works.
• Moving conn.record2id = sc.prefix_servicecontractid to WHERE clause spits same error

I may have had 9.0.1 before or maybe earlyer.

This refactor works:

SELECT 1
FROM dbo.connection conn
INNER JOIN dbo.prefix_servicecontract sc ON sc.prefix_servicecontractid = conn.record2id
INNER JOIN dbo.contact authp ON authp.contactid = conn.record1id
INNER JOIN dbo.account a ON authp.accountid = a.accountid

But if I add WHERE sc.prefix_accountid = a.accountid to be more correct, I get:

'prefix_servicecontract' entity doesn't contain attribute with Name = 'a.accountid' and NameMapping = 'Logical'. MetadataCacheDetails: ProviderType=Dynamic, StandardCache=True, IsLoadedInStagedContext = False, Timestamp=937808190, MinActiveRowVersion=937808190, MetadataInstanceId=27404280

[MarkMpn]

Thanks for the details. I can't reproduce this error at the moment, but it looks like the server is having problems evaluating the cross-table column comparison condition.

Could you please post the FetchXML that this query generates, along with the results of SELECT @@VERSION?

[janis-veinbergs]

I'm gonna be off for a week so will post a week after.

I'm on-prem v9.1, perhaps that is the issue.

Mark Carrington @.***> (šajā datumā: Pk, 2024. g. 14. jūn. 21:36) rakstīja:

Thanks for the details. I can't reproduce this error at the moment, but it looks like the server is having problems evaluating the cross-table column comparison https://learn.microsoft.com/en-us/power-apps/developer/data-platform/fetchxml/filter-rows?tabs=fetchxml&WT.mc_id=DX-MVP-5004203#cross-table-column-comparisons condition.

Could you please post the FetchXML that this query generates, along with the results of SELECT @@VERSION?

— Reply to this email directly, view it on GitHub https://github.com/MarkMpn/Sql4Cds/issues/486#issuecomment-2168563845, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAFOPK7BJM5NYVG4I4QYPE3ZHMZ33AVCNFSM6AAAAABJIR2NSKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCNRYGU3DGOBUGU . You are receiving this because you authored the thread.Message ID: @.***>

[MarkMpn]

That is almost certainly the issue - if you can get me the version number details I can build in an extra check so it doesn't attempt to use this feature in the on-prem version.

[chironh]

Experiencing a similar issue (online):

select account.accountid
from account
inner join inf_place ON account.inf_placevisitaddressid = inf_place.inf_placeid
inner join inf_stateprovincecountydepartement ON inf_place.inf_stateprovincecountydepartementid = inf_stateprovincecountydepartement.inf_stateprovincecountydepartementid
where account.address1_stateorprovince is null or account.address1_stateorprovince != inf_stateprovincecountydepartement.inf_name

Giving me fetchxml:

<fetch xmlns:generator='MarkMpn.SQL4CDS'>
  <entity name='account'>
    <attribute name='accountid' />
    <link-entity name='inf_place' to='inf_placevisitaddressid' from='inf_placeid' alias='inf_place' link-type='inner'>
      <link-entity name='inf_stateprovincecountydepartement' to='inf_stateprovincecountydepartementid' from='inf_stateprovincecountydepartementid' alias='inf_stateprovincecountydepartement' link-type='inner' />
    </link-entity>
    <filter type='or'>
      <condition attribute='address1_stateorprovince' operator='null' />
      <filter>
        <condition valueof='inf_stateprovincecountydepartement.inf_name' attribute='address1_stateorprovince' operator='ne' />
        <condition attribute='address1_stateorprovince' operator='not-null' />
        <condition attribute='inf_name' operator='not-null' />
      </filter>
    </filter>
  </entity>
</fetch>

Last condition breaks the query.

Creating a calculated condition fixes the issue:

or account.address1_stateorprovince != inf_stateprovincecountydepartement.inf_name + ''