Issues in SOQL Query (GROUP BY CUBE, GROUPING(fieldName), WITH SECURITY_ENFORCED, ALL ROWS, ...)

Hi Scott,

I’m checking the SOQL Query builder within IC, learning about features I’ve never used

Noting all quirks I encounter below:

TYPEOF

When I begin a TYPEOF (after having written the FROM clause so the inspection “knows” which object it is), then initially the first suggestion after TYPEOF What is not WHEN (on the second try it is):

‌

Reformatting kicks in:

Directly if TYPEOF is preceded by another field (incomplete query):

SELECT Id,
    TYPEOF 
FROM Task

Only after WHEN is selected if TYPEOF is not preceded by another field (incomplete query):

SELECT
    TYPEOF What
        WHEN 
FROM Task

GROUP BY CUBE

The following query is deemed invalid (The SOQL query does not appear valid. Would you still like to execute it?), it does however work fine:

SELECT Product__r.Name, Type__c, Status__c, COUNT(Id)
FROM Issue__c
GROUP BY CUBE (Product__r.Name, Type__c, Status__c)
ORDER BY GROUPING(Product__r.Name)

The warning isn’t shown in this version:

SELECT Product__r.Name, Type__c, Status__c, COUNT(Id)
FROM Issue__c
GROUP BY CUBE (Product__r.Name, Type__c, Status__c)
ORDER BY GROUPING(Type__c)

So it’s the use of the relationship field in GROUPING(Product__r.Name) which triggers the warning.

Additionally, the following inspection is raised for GROUPING(Product__r.Name):

Keyword does not use preferred case: Name != NAME

Observation

Parentheses are not automatically added when completing GROUP BY CUBE

Using GROUPING(fieldName) to Identify Subtotals

While the following query is fine:

SELECT Product__r.Name, Type__c, Status__c,
    GROUPING(Type__c) grpType, GROUPING(Status__c) grpStatus,
    COUNT(Id) cnt
FROM Issue__c
GROUP BY ROLLUP (Product__r.Name, Type__c, Status__c)

The following query is deemed invalid:

SELECT Product__r.Name, Type__c, Status__c,
    GROUPING(Product__r.Name) grpProduct,
    GROUPING(Type__c) grpType, GROUPING(Status__c) grpStatus,
    COUNT(Id) cnt
FROM Issue__c
GROUP BY ROLLUP (Product__r.Name, Type__c, Status__c)

The inspection is thrown off track by the relationship field:

‌

FOR VIEW / FOR REFERENCE / FOR UPDATE

I have the following query:

SELECT Id, Subject
FROM Task
ORDER BY CreatedDate DESC 
LIMIT 5

When I try to add FOR VIEW, once I confirm FOR, I end up with parentheses being added:

SELECT Id, Subject
FROM Task
ORDER BY CreatedDate DESC 
LIMIT 5
FOR ()

If I type VIEW instead of the parenthesis, the keyword is recognised just fine:

SELECT Id, Subject
FROM Task
ORDER BY CreatedDate DESC 
LIMIT 5
FOR VIEW

The same applies to FOR REFERENCE and FOR UPDATE.

Filter SOQL Queries Using WITH SECURITY_ENFORCED

The attempt to execute the following query in SOQL Query:

SELECT Id, LegalName__c
FROM Account 
WITH SECURITY_ENFORCED

Results in the following exception:

org.apache.cxf.binding.soap.SoapFault: MALFORMED_QUERY:
WITH SECURITY_ENFORCED
^
ERROR at Row:3:Column:6
SECURITY_ENFORCED not allowed in this context

If I understand correctly, this is because, according to the documentation, “The WITH SECURITY_ENFORCED clause is only available in Apex.“ Whereas if I understand correctly, the SOQL Query editor leverages the SOAP API.

So I can build the query, but not execute it

Querying All Records with a SOQL Statement