Related to 359: Original query now reporting "In doesn't support order clause inside linkentity expression."

[gpriestley]

Mark

Related to: https://github.com/MarkMpn/Sql4Cds/issues/359

I've updated to the latest version and re-ran the original query:

update contact
set pca_emailhardbounced=dateadd(hh,10,getdate())
where donotbulkemail=0 and pca_emailhardbounced is null and emailaddress1 like '%@%.%'
and contactid in
(select distinct cdi_contactid from cdi_emailevent where cdi_type = 3 AND cdi_message NOT LIKE '%mailbox full%' AND cdi_message NOT LIKE '%hop count%' and createdon>=dateadd(day,-30,getdate()))
and contactid not in
(select distinct cdi_contactid from cdi_emailevent where cdi_type in (1,2,4) and createdon>=dateadd(day,-30,getdate()))
and contactid not in
(select distinct cdi_contactid from cdi_sentemail where cdi_senton>=dateadd(day,-30,getdate()) and (cdi_deliveriescount>0 or cdi_openscount>0 or cdi_clickscount>0))

Now fails with an error message: In doesn't support order clause inside linkentity expression. See the Execution Plan tab for details of where this error occurred

Estimated execution plan screenshot attached.

I tried the refactored version using group by rather than distinct and this ran, but took 19 minutes, rather than a few minutes that the original script used to run in.

[gpriestley]

Tried another re-factored version converting to joins.

update c
set pca_emailhardbounced=dateadd(hh,10,getdate())
from contact c
inner join (select cdi_contactid from cdi_emailevent where cdi_type = 3 AND cdi_message NOT LIKE '%mailbox full%' AND cdi_message NOT LIKE '%hop count%' and createdon>=dateadd(day,-30,getdate()) group by cdi_contactid) z on c.contactid=z.cdi_contactid
left outer join (select cdi_contactid from cdi_emailevent where cdi_type in (1,2,4) and createdon>=dateadd(day,-30,getdate()) group by cdi_contactid ) a on c.contactid=a.cdi_contactid
left outer join (select cdi_contactid from cdi_sentemail where cdi_senton>=dateadd(day,-30,getdate()) and (cdi_deliveriescount>0 or cdi_openscount>0 or cdi_clickscount>0) group by cdi_contactid ) b on c.contactid=b.cdi_contactid
where c.donotbulkemail=0 and c.pca_emailhardbounced is null and c.emailaddress1 like '%@%.%'
and a.cdi_contactid is null
and b.cdi_contactid is null

Failed after 13 minutes reporting: The date-time format for @PartitionStart is invalid, or value is outside the supported range. See the Execution Plan tab for details of where this error occurred

[MarkMpn]

I’ve been able to reproduce the error with the first version so I should be able to fix that shortly. The second one is more difficult for me to reproduce. When you get this error can you please first take a screenshot of the execution plan including the red highlight on the affected node, and double-click on the FetchXML node and copy out the FetchXML query.

[gpriestley]

Mark

Execution plan attached.

FetchXML of the node to the right of the red box is:

<fetch xmlns:generator='MarkMpn.SQL4CDS' aggregate='true'>
  <entity name='cdi_sentemail'>
    <attribute name='cdi_contactid' alias='cdi_contactid' groupby='true' />
    <filter>
      <condition attribute='cdi_senton' operator='ge' value='2023-09-05T08:54:46.127+00:00' />
      <filter type='or'>
        <condition attribute='cdi_deliveriescount' operator='gt' value='0' />
        <condition attribute='cdi_openscount' operator='gt' value='0' />
        <condition attribute='cdi_clickscount' operator='gt' value='0' />
      </filter>
    </filter>
    <order alias='cdi_contactid' />
    <filter>
      <condition generator:IsVariable='true' attribute='createdon' operator='gt' value='@PartitionStart' />
      <condition generator:IsVariable='true' attribute='createdon' operator='le' value='@PartitionEnd' />
    </filter>
  </entity>
</fetch>

Let me know if you need anything else.

Thanks for all you do Greg

[gpriestley]

Just ran the relevant sub-query by itself: (select cdi_contactid from cdi_sentemail where cdi_senton>=dateadd(day,-30,getdate()) and (cdi_deliveriescount>0 or cdi_openscount>0 or cdi_clickscount>0) and createdon>=dateadd(day,-30,getdate()) group by cdi_contactid )

Ran successfully - approx 2.43m rows evaluated, and returned about 62K rows

[gpriestley]

Just ran successfully. Also much much quicker. Thank you!