Results view issue (left vs inner)?

[chironh]

Pretty wierd behaviour I'm experiencing, which I cannot explain.

Left join gives me 7xx records, but all of them were joined in the end.

select account.accountid, account.inf_countryinvoiceaddressid, account.inf_countryinvoiceaddress, inf_country.inf_countryid from account left join inf_country ON account.inf_countryinvoiceaddress = inf_country.inf_name where account.inf_accountstatus = 1 and account.inf_countryinvoiceaddressid is null and account.inf_countryinvoiceaddress is not null

Inner join gives me 7 records, with strange red signs. The inner join should also give me 7xx results.

select account.accountid, account.inf_countryinvoiceaddressid, account.inf_countryinvoiceaddress, inf_country.inf_countryid from account inner join inf_country ON account.inf_countryinvoiceaddress = inf_country.inf_name where account.inf_accountstatus = 1 and account.inf_countryinvoiceaddressid is null and account.inf_countryinvoiceaddress is not null

Messages tab still shows me this: (713 rows affected)?

I've checked the fetchxml's behind the scenes and the only difference is inner vs outer.

Ran the same inner query in FetchXML Builder, and this gave me the expected 7xx results.

I'm using version 9.0.1.

[MarkMpn]

I’ve never seen anything like that before - can you reproduce it?

I wonder if it’s possible that some other tool running within XrmToolBox at the same time could have had some sort of effect on the grid control - can you try restarting XrmToolBox, open only SQL 4 CDS and run the query again?

[chironh]

Yes I can reproduce it.

I'll try reinstalling SQL4CDS and try again.

[chironh]

I have uninstalled the plugin, reinstalled the plugin, removed the roaming files as asked by the plugin, checked XrmToolbox (which is latest), .. still the issue persist somehow.

<fetch xmlns:generator='MarkMpn.SQL4CDS'> <entity name='account'> <attribute name='accountid' /> <attribute name='inf_countryinvoiceaddressid' /> <attribute name='inf_countryinvoiceaddress' /> <link-entity name='inf_country' to='inf_countryinvoiceaddress' from='inf_name' alias='inf_country' link-type='inner'> <attribute name='inf_countryid' /> <filter> <condition attribute='inf_countryid' operator='not-in'> <value>35f7b77d-cc45-e711-80fb-5065f38b3661</value> <value>a3f7b77d-cc45-e711-80fb-5065f38b3661</value> <value>dff8b77d-cc45-e711-80fb-5065f38b3661</value> <value>7ff7b77d-cc45-e711-80fb-5065f38b3661</value> <value>59f8b77d-cc45-e711-80fb-5065f38b3661</value> <value>a7f7b77d-cc45-e711-80fb-5065f38b3661</value> </condition> </filter> <order attribute='inf_countryid' /> </link-entity> <filter> <condition attribute='inf_accountstatus' operator='eq' value='1' /> <condition attribute='inf_countryinvoiceaddress' operator='not-null' /> </filter> <order attribute='accountid' /> </entity> </fetch>

If you want me to test certain things, or to provide you with some kind of logs, let me know. Just trying to help you out by making this plugin even better, because this thing is freaking awesome :)

[MarkMpn]

Is there any way you can either get me access to this instance to try to reproduce it myself, or export a solution containing these entities and a CSV export of the records I can then import into my own instance for testing?

If you could create an application user with read access to the metadata and these entities and send the client ID and secret to sql4cds@markcarrington.dev that would be great.

[chironh]

I think I just found a possible cause:
As you can see I'm not joining on the ID of the records, but on a string field. If joining on the ID, which gives me in this case more of less the same amount of results, there is no difference between inner and left/outer.

Could this be the culprit?

[chironh]

Yeah, this could be it. I tested the same scenario on a different environment (other customer):

[MarkMpn]

That's great, thanks for your help debugging this. I've managed to reproduce this now, I should be able to include a fix in the next update.