- edited description
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
The attempt to execute the following query in SOQL Query:
SELECT Id, LegalName__c
FROM Account
ALL ROWS
Results in the following exception:
org.apache.cxf.binding.soap.SoapFault: MALFORMED_QUERY: ALL ROWS not allowed in this context
This should be feasible with queryAll() via SOAP if I understand correctly.
Comments (11)
-
reporter -
reporter - edited description
-
reporter - edited description
-
reporter - edited description
-
reporter I would consider the FOR () issue non-trivial
-
reporter - edited description
-
reporter - edited description
-
reporter - changed title to Issues in SOQL Query (GROUP BY CUBE, GROUPING(fieldName), WITH SECURITY_ENFORCED, ALL ROWS, ...)
-
repo owner Thanks for all the details.
I’ve addressed the ones that result in parser syntax errors. They were a simple matter of using the proper production for a qualified field name instead of what was being used. That also eliminated the side-effect of seeing
Name
as the SOQL keyword instead of as the field name and recommending a case change.I’ve also addressed the insert handler for
FOR
in a SOQL context including parentheses. That would only have happened in a standalone query as one couched in an Apex query expression would have been handled properly, but that was also a bug that’s been resolved.The
WITH SECURITY_ENFORCED
andALL ROWS
clauses are only valid in an Apex query expression context. I already have a very simple SOQL query annotator that flags invalid combinations of Tooling API SObjects and the Use Tooling API toggle, so I added checks to that for those two clauses in standalone queries. Note that the link regardingqueryAll
is about whether deleted rows are included and theIsDeleted
field is visible rather than whether theALL ROWS
clause is or is not allowed in a standalone query context.Those are the ones that will be addressed for the next build.
-
reporter Thank you Scott
-
repo owner - changed status to resolved
Fixes delivered in 2.2.4.7.
- Log in to comment