`data query` does not show aggregated values

[leostewart]

Summary

Depending on if and how fields are aliased in aggregate queries, the aggregated value is not shown in either human or csv result formats. This may or may not be a bug in the CLI.

Steps To Reproduce

1. Create a record

sf data record create --sobject Account --values "Name='Burlington Textiles Corp of America'"

2. Query the record and aggregate, aliasing the non-aggregated field as something other than Name

sf data query --query "SELECT Name AccountName, COUNT(Id) FROM Account WHERE Name = 'Burlington Textiles Corp of America' GROUP BY Name"

Expected result

Output the aggregated value

Querying Data... ⡿
 ACCOUNTNAME                         COUNT(ID) 
 ─────────────────────────────────── ───────── 
 Burlington Textiles Corp of America 1         
Total number of records retrieved: 1.
Querying Data... done

Actual result

The aggregated value is suppressed

Querying Data... ⡿
 ACCOUNTNAME                         COUNT(ID) 
 ─────────────────────────────────── ───────── 
 Burlington Textiles Corp of America           
Querying Data... done

System Information

{
  "architecture": "linux-arm64",
  "cliVersion": "@salesforce/cli/2.16.10",
  "nodeVersion": "node-v20.6.1",
  "osVersion": "Linux 6.1.29-0-virt",
  "rootPath": "/usr/local/share/npm-global/lib/node_modules/@salesforce/cli",
  "shell": "bash",
  "pluginVersions": [
    "@oclif/plugin-autocomplete 3.0.1 (core)",
    "@oclif/plugin-commands 3.0.5 (core)",
    "@oclif/plugin-help 6.0.5 (core)",
    "@oclif/plugin-not-found 3.0.2 (core)",
    "@oclif/plugin-plugins 4.1.4 (core)",
    "@oclif/plugin-search 1.0.5 (core)",
    "@oclif/plugin-update 4.1.3 (core)",
    "@oclif/plugin-version 2.0.4 (core)",
    "@oclif/plugin-warn-if-update-available 3.0.2 (core)",
    "@oclif/plugin-which 3.0.7 (core)",
    "@salesforce/cli 2.16.10 (core)",
    "apex 2.3.20 (core)",
    "auth 2.8.25 (core)",
    "data 2.6.1 (core)",
    "deploy-retrieve 1.19.3 (core)",
    "info 2.6.51 (core)",
    "limits 2.3.41 (core)",
    "login 1.2.40 (core)",
    "marketplace 0.3.2 (core)",
    "org 2.11.7 (core)",
    "packaging 1.26.3 (user)",
    "schema 2.3.32 (core)",
    "settings 1.4.37 (core)",
    "sobject 0.2.14 (core)",
    "source 2.10.46 (core)",
    "telemetry 2.3.8 (core)",
    "templates 55.5.17 (core)",
    "trust 3.0.2 (core)",
    "user 2.3.41 (core)"
  ]
}

Additional information

Here are some example queries that do not output the aggregated value

sf data query --query "SELECT Account.Name AnyAliasOtherThanNameOrAccount, COUNT(Id) FROM Contact GROUP BY Account.Name"

sf data query --query "SELECT ApexClass.Name, COUNT(Id) FROM AsyncApexJob GROUP BY ApexClass.Name"

sf data query --query "SELECT ApexClass.Name AnyAliasOtherThanApexClass, COUNT(Id) FROM AsyncApexJob GROUP BY ApexClass.Name"

And some that do

sf data query --query "SELECT Name Name, COUNT(Id) FROM Account GROUP BY Name"

sf data query --query "SELECT Account.Name Name, COUNT(Id) FROM Contact GROUP BY Account.Name"

sf data query --query "SELECT Account.Name Account, COUNT(Id) FROM Contact GROUP BY Account.Name"

sf data query --query "SELECT ApexClass.Name ApexClass, COUNT(Id) FROM AsyncApexJob GROUP BY ApexClass.Name"

I suspect the likely culprit is the column metadata returned by the REST API given the difference between this response

curl --silent -H 'Authorization: Bearer token' 'https://MyDomainName.my.salesforce.com/services/data/v59.0/query/?q=SELECT+Name+FROM+Account+GROUP+BY+Name&columns=true' | jq

{
  "columnMetadata": [
    {
      "aggregate": false,
      "apexType": "String",
      "booleanType": false,
      "columnName": "Name",
      "custom": false,
      "displayName": "Name",
      "foreignKeyName": null,
      "insertable": true,
      "joinColumns": [],
      "numberType": false,
      "textType": true,
      "updatable": true
    }
  ],
  "entityName": "Account",
  "groupBy": true,
  "idSelected": false,
  "keyPrefix": "001"
}

and this one

curl --silent -H 'Authorization: Bearer token' 'https://MyDomainName.my.salesforce.com/services/data/v59.0/query/?q=SELECT+Name%20AccountName+FROM+Account+GROUP+BY+Name&columns=true' | jq

{
  "columnMetadata": [
    {
      "aggregate": true,
      "apexType": null,
      "booleanType": false,
      "columnName": "AccountName",
      "custom": false,
      "displayName": "(Name)",
      "foreignKeyName": null,
      "insertable": false,
      "joinColumns": [],
      "numberType": false,
      "textType": true,
      "updatable": false
    }
  ],
  "entityName": "Account",
  "groupBy": true,
  "idSelected": false,
  "keyPrefix": "001"
}

where the only variable is the alias, but the column is considered aggregate.

[github-actions[bot]]

Thank you for filing this issue. We appreciate your feedback and will review the issue as soon as possible. Remember, however, that GitHub isn't a mechanism for receiving support under any agreement or SLA. If you require immediate assistance, contact Salesforce Customer Support.

[shetzel]

@leostewart - excellent issue write-up! I very much appreciate all the details. Unfortunately, you're correct that the problem is in the response handed us by the REST API call to get column metadata. It's a bug that they are returning "aggregate": true for a column with an alias, rather than an actual aggregate column such as COUNT(Id).

I'm going to close this since it's not a CLI bug, but please open a support case so the appropriate server side team can fix it.

[tggagne]

@shetzel , did the "force" cli use a different API? It returned aggregate rows correctly. Of course, it doesn't run on recent
OSX builds...

[shetzel]

@tggagne - by "force" cli do you mean the force data soql query command? If so, that's an alias for data query so it runs the same code. These commands haven't changed much from sfdx v7 to sf v2. Same API.