SOQL Alias Notation Not Recognised

[Tom Gangemi]

The editor shows a syntax error of " ',' unexpected " when encountering sObject aliases in an SOQL statement.

More information on alias notation - "Alias Notation" Force.com SOQL and SOSL Reference (PDF)

Concrete example of unsupported syntax:

SELECT count()
FROM Contact c, c.Account a
WHERE a.name = 'MyriadPubs'

[Scott Wells]

Thanks for the report, Tom. Let me take a look and see what's going on. Likely I just missed something small in my parser. Assuming so, I'll update it for a near-term release.

[Scott Wells]

Interesting...so the issue is that my parser doesn't recognize more than one FROM target. As you can likely appreciate, I have to be VERY careful making these types of changes to the parser, so while I do plan to address this, it won't as quick a turnaround as I'd hoped/expected. More to come, but not likely until I get a few other bugs knocked out.

[Mike Mikula]

I just had this issue as well! Wonder if its still up for consideration!

[Scott Wells]

Hi, Mike (and Tom). Yes, it's still on the list. Interestingly I just fixed another issue with aliases in the release that will come out tomorrow with Spring '20 support, but not yet this one. I'll try to prioritize it since it's such a core consideration (Apex/SOQL parsing).

[Michael Simkin]

[Scott Wells]

Wipe away those tears. I've implemented this for inclusion in the next build:

[Mike Mikula]

This is great news! Thank you!

[Michael Simkin]

[Scott Wells]

Delivered in 2.1.3.6.

[Piotr Kożuchowski]

Hi Scott,

I’m not sure if we should reopen this issue or create a new one - this issue still appears, but when you traverse through more parent levels. For example in apex, this is not caught by syntax:

@Scott Wells

[Scott Wells]

Hi, Piotr. I'm happy to handle this here. I have a question about your query, though. When I try to create a similar query--assuming I understand it properly--it fails to compile. For example, this works:

SELECT Id, c.LastName, a.AccountNumber, o.Username
FROM Contact c, Account a, Account.Owner o
WHERE c.LastName = 'Wells'
AND a.AccountNumber = '12345'
AND o.Username LIKE '[e-mail]'

but this fails with "A driving SObject type has already been set, all other entity types in the FROM clause must be relationships to the initial object. The driving object is Contact":

SELECT Id, c.LastName, a.AccountNumber, o.Username
FROM Contact c, c.Account a, a.Owner o
WHERE c.LastName = 'Wells'
AND a.AccountNumber = '12345'
AND o.Username LIKE '[e-mail]'

I thought perhaps that meant that all relationship fields used in the FROM clause must also be to the main selected type, but the following fails in the same manner:

SELECT Id, u.Username, cb.Username, lmb.Username
FROM User u, CreatedBy cb, CreatedBy.LastModifiedBy lmb

So hopefully you can educate me on exactly what's happening in your query that's different from what I've tried above that fails. Thanks!

[Piotr Kożuchowski]

Hi @Scott Wells

I had to educate myself first to answer your question apparently, this only works on Master-Detail relationships. On my sandbox, the first example doesn’t compile because Contact is not in MD with Account.
All standard fields and relations seems to be lookups, so I can’t give you piece of code that would work out of the box. If you could create 3 objects in master detail relationship A ← B ← C, then you should be able to replicate the same query I did on C object

SELECT Id,
     a.Name
FROM C__c, B__r b, B__r.A__r a

‌

‌

[Scott Wells]

By the way, this is still in my active work queue. I had to shelve it for the changes that will be in this week's release but plan to return to it for next week's build. I already have the changes in place that support adding these references, and I've created a code inspection that flags queries that are invalid, but the logic for that inspection isn't quite right. Hopefully with the latest you've provided I can tune that inspection to flag the right things. I'll reach out if I still have questions.

[Scott Wells]

Piotr, I guess I still don't follow as I'm unable to corner this problem. In a brand new scratch org I modeled three objects:

1. RootMaster__c which has no M/D relationships. This is your A.
2. IntermediateMasterDetail__c which has an M/D relationship with RootMaster__c. This is your B.
3. LeafDetail__c which has an M/D relationship with IntermediateMasterDetail__c. This is your C.

The following query fails with "A driving SObject type has already been set, all other entity types in the FROM clause must be relationships to the initial object. The driving object is LeafDetail__c.":

SELECT Id, a.Name
FROM LeafDetail__c c, 
    IntermediateMasterDetail__r b,
    IntermediateMasterDetail__r.RootMaster__r a

Did I model these objects and relationships incorrectly?

As I mentioned, once I have the validation logic nailed down this will go into a build as the code completion and reference tracking is all there, but IC2 isn't currently marking queries of this form as valid/invalid properly, and that's quite important to me before including this fix.

[Piotr Kożuchowski]

@Scott Wells I’m looking into this, I will try to create reproducible scenario on scratch org. Right now I’m not sure what is different about schema from project.

[Piotr Kożuchowski]

@Scott Wells

Please try any of the following and don’t ask me why this works…

SELECT Id, a.Name
FROM LeafDetail__c c, 
    IntermediateMasterDetail__r.RootMaster__r a

‌

SELECT Id, a.Name
FROM LeafDetail__c c,
LeafDetail__c.IntermediateMasterDetail__r b,
IntermediateMasterDetail__r.RootMaster__r a

‌

SELECT Id, a.Name
FROM LeafDetail__c c,
LeafDetail__c.IntermediateMasterDetail__r b,
LeafDetail__c.IntermediateMasterDetail__r.RootMaster__r a

SELECT Id, a.Name
FROM LeafDetail__c c,
c.IntermediateMasterDetail__r b,
c.IntermediateMasterDetail__r.RootMaster__r a

‌

SELECT Id, a.Name
FROM LeafDetail__c c,
c.IntermediateMasterDetail__r b,
b.RootMaster__r a

‌

[Scott Wells]

Okay, with the latest you've provided I think I've narrowed this down considerably and produced a nice editor experience:

Testing against a large number of test projects--both mine and open source--I've seen no false positives from the code inspection but I have seen one false negative for:

List<User> usersBad =
[
    SELECT Id, u.Username, cb.Username, lmb.Username
    FROM User u, CreatedBy cb, CreatedBy.LastModifiedBy lmb
];

where CreatedBy cb in the FROM clause should be flagged as an error but isn't. I may investigate that a bit further, but at this point I think that the signal-to-noise ratio is pretty solid and a deployment will report that corner-case as an error anyway, so I may just see if anyone ever actually reports it.

This will be included in the next build, currently targeting Thursday morning.

[Scott Wells]

Delivered in 2.2.2.1.

[Mike Mikula]

@Scott Wells

This is the kinda support that has kept me with your tooling all these years!

Thank you 🙏

[Scott Wells]

Removing component: Parser (automated comment)