Date functions should be allowed in ORDER BY clause

[0x17h]

• [ ] Feature
• [x] Bug

Description

SOQL parser dislikes some functions in 'ORDER BY' and throws the error: "Redundant input, expecting EOF but found: (". Although the query is completely valid: you can execute it in Salesforce developer console and observe, that objects are ordered.

Steps to reproduce

Execute the following query:

SELECT WEEK_IN_YEAR(CloseDate), SUM(amount)
FROM Opportunity 
GROUP BY WEEK_IN_YEAR(CloseDate)
ORDER BY WEEK_IN_YEAR(CloseDate)

Affected versions

• 2.5.3
• Version used in demo: I assume, the latest one (3.0.1^) is used there

Observed behavior

Parser throws "Redundant input, expecting EOF but found: (" for 'WEEK_IN_YEAR' function call in 'ORDER BY' clause

Expected behavior

Query is parsed and parsing result contains something like this:

{
  ...
  "orderBy": {
    "fn": {
      "functionName": "WEEK_IN_YEAR",
      "parameters": [
        "CloseDate"
      ],
      "rawValue": "WEEK_IN_YEAR(CloseDate)"
    },
  ...
}

[paustint]

Thank you for reporting this, I will try to investigate and resolve in the next few days.

[paustint]

@0x17h - I have resolved this bug and have released the changes to two new versions 2.5.6 and 3.0.2. All date functions are now parsed properly in order by causes.

These test-cases have been added, which also generate the examples for the documentation.

Thanks again for the detailed bug report!

[{
    testCase: 106,
    soql: `SELECT WEEK_IN_YEAR(CloseDate), SUM(amount) FROM Opportunity GROUP BY WEEK_IN_YEAR(CloseDate) ORDER BY WEEK_IN_YEAR(CloseDate)`,
    output: {
      fields: [
        {
          type: 'FieldFunctionExpression',
          functionName: 'WEEK_IN_YEAR',
          parameters: ['CloseDate'],
          rawValue: 'WEEK_IN_YEAR(CloseDate)',
        },
        {
          type: 'FieldFunctionExpression',
          functionName: 'SUM',
          parameters: ['amount'],
          isAggregateFn: true,
          rawValue: 'SUM(amount)',
        },
      ],
      sObject: 'Opportunity',
      groupBy: {
        fn: {
          functionName: 'WEEK_IN_YEAR',
          parameters: ['CloseDate'],
          rawValue: 'WEEK_IN_YEAR(CloseDate)',
        },
      },
      orderBy: {
        fn: {
          functionName: 'WEEK_IN_YEAR',
          parameters: ['CloseDate'],
          rawValue: 'WEEK_IN_YEAR(CloseDate)',
        },
      },
    },
  },
  {
    testCase: 107,
    soql: `SELECT WEEK_IN_YEAR(CloseDate), SUM(amount) FROM Opportunity GROUP BY WEEK_IN_YEAR(CloseDate) ORDER BY WEEK_IN_YEAR(CloseDate) DESC NULLS FIRST`,
    output: {
      fields: [
        {
          type: 'FieldFunctionExpression',
          functionName: 'WEEK_IN_YEAR',
          parameters: ['CloseDate'],
          rawValue: 'WEEK_IN_YEAR(CloseDate)',
        },
        {
          type: 'FieldFunctionExpression',
          functionName: 'SUM',
          parameters: ['amount'],
          isAggregateFn: true,
          rawValue: 'SUM(amount)',
        },
      ],
      sObject: 'Opportunity',
      groupBy: {
        fn: {
          functionName: 'WEEK_IN_YEAR',
          parameters: ['CloseDate'],
          rawValue: 'WEEK_IN_YEAR(CloseDate)',
        },
      },
      orderBy: {
        fn: {
          functionName: 'WEEK_IN_YEAR',
          parameters: ['CloseDate'],
          rawValue: 'WEEK_IN_YEAR(CloseDate)',
        },
        order: 'DESC',
        nulls: 'FIRST',
      },
    },
  },
  {
    testCase: 108,
    soql: `SELECT WEEK_IN_YEAR(CloseDate), SUM(amount) FROM Opportunity GROUP BY WEEK_IN_YEAR(CloseDate) ORDER BY WEEK_IN_YEAR(CloseDate) DESC NULLS LAST, SUM(amount) ASC NULLS LAST`,
    output: {
      fields: [
        {
          type: 'FieldFunctionExpression',
          functionName: 'WEEK_IN_YEAR',
          parameters: ['CloseDate'],
          rawValue: 'WEEK_IN_YEAR(CloseDate)',
        },
        {
          type: 'FieldFunctionExpression',
          functionName: 'SUM',
          parameters: ['amount'],
          isAggregateFn: true,
          rawValue: 'SUM(amount)',
        },
      ],
      sObject: 'Opportunity',
      groupBy: {
        fn: {
          functionName: 'WEEK_IN_YEAR',
          parameters: ['CloseDate'],
          rawValue: 'WEEK_IN_YEAR(CloseDate)',
        },
      },
      orderBy: [
        {
          fn: {
            functionName: 'WEEK_IN_YEAR',
            parameters: ['CloseDate'],
            rawValue: 'WEEK_IN_YEAR(CloseDate)',
          },
          order: 'DESC',
          nulls: 'LAST',
        },
        {
          fn: {
            functionName: 'SUM',
            parameters: ['amount'],
            rawValue: 'SUM(amount)',
          },
          order: 'ASC',
          nulls: 'LAST',
        },
      ],
    },
  },
]

[0x17h]

@paustint Thank you so much for such a quick response and fix! Outstanding support from your side