Repository: paustint/soql-parser-js (ts)

Issue:
Date functions should be allowed in ORDER BY clause
- [ ] 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:
```sql
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](https://paustint.github.io/soql-parser-js/): 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:
```javascript
{
  ...
  "orderBy": {
    "fn": {
      "functionName": "WEEK_IN_YEAR",
      "parameters": [
        "CloseDate"
      ],
      "rawValue": "WEEK_IN_YEAR(CloseDate)"
    },
  ...
}
```



Pull request:
Bug 139 3.x
1. Date functions were not properly parsed when used in order by clauses. (#139)
2. Modified names of functions / types (internal)
3. Removed improper import of `isString` from node utils

resolves #139

Patch:
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 5b1deb7..e5b3b34 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -1,5 +1,15 @@
 # Changelog
 
+## 3.0.2
+
+March 6, 2021
+
+1. Date functions were not properly parsed when used in order by clauses. (#139)
+2. Modified names of functions / types (internal)
+3. Removed improper import of `isString` from node utils
+
+Changes also released to 2.5.6
+
 ## 3.0.1
 
 January 7, 20201
diff --git a/docs/package-lock.json b/docs/package-lock.json
index 6874662..163c318 100644
--- a/docs/package-lock.json
+++ b/docs/package-lock.json
@@ -18992,9 +18992,9 @@
       }
     },
     "soql-parser-js": {
-      "version": "3.0.1",
-      "resolved": "https://registry.npmjs.org/soql-parser-js/-/soql-parser-js-3.0.1.tgz",
-      "integrity": "sha512-2Ie/SIO7dafKoNpDnCe/zT3ZriP7aW2YliCdEEEP2rUJyatoNDgtYc747DVK64KftYkwJmru9HDrgzKwzaHfBA==",
+      "version": "3.0.2",
+      "resolved": "https://registry.npmjs.org/soql-parser-js/-/soql-parser-js-3.0.2.tgz",
+      "integrity": "sha512-jz2nwAJzl33woEBOp/LFwnq7e5Y48pnPKJo6LxCcEuMm4waW0HtHvlxCx1naxp5PGrDYS4Qb3RNWfi25g97jZw==",
       "requires": {
         "chevrotain": "^6.5.0",
         "lodash.get": "^4.4.2"
diff --git a/docs/package.json b/docs/package.json
index 8c3cd07..e2e9b8f 100644
--- a/docs/package.json
+++ b/docs/package.json
@@ -11,7 +11,7 @@
     "gh-pages": "^2.2.0",
     "highlight.js": "^9.18.1",
     "prismjs": "^1.19.0",
-    "soql-parser-js": "^3.0.1",
+    "soql-parser-js": "^3.0.2",
     "tailwindcss": "^1.2.0"
   },
   "devDependencies": {
diff --git a/docs/src/resources/sample-queries-json.json b/docs/src/resources/sample-queries-json.json
index e2413f4..44ff518 100644
--- a/docs/src/resources/sample-queries-json.json
+++ b/docs/src/resources/sample-queries-json.json
@@ -102,5 +102,8 @@
   "SELECT ProductCode FROM Product2 GROUP BY ProductCode HAVING COUNT(Id) > 1 ORDER BY COUNT(Id) DESC",
   "SELECT AnnualRevenue FROM Account WHERE NOT (AnnualRevenue > 0 AND AnnualRevenue < 200000)",
   "SELECT AnnualRevenue FROM Account WHERE ((NOT AnnualRevenue > 0) AND AnnualRevenue < 200000)",
-  "SELECT Id FROM Account WHERE NOT Id = '2'"
+  "SELECT Id FROM Account WHERE NOT Id = '2'",
+  "SELECT WEEK_IN_YEAR(CloseDate), SUM(amount) FROM Opportunity GROUP BY WEEK_IN_YEAR(CloseDate) ORDER BY WEEK_IN_YEAR(CloseDate)",
+  "SELECT WEEK_IN_YEAR(CloseDate), SUM(amount) FROM Opportunity GROUP BY WEEK_IN_YEAR(CloseDate) ORDER BY WEEK_IN_YEAR(CloseDate) DESC NULLS FIRST",
+  "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"
 ]
diff --git a/package-lock.json b/package-lock.json
index 55b6c62..5a312a0 100644
--- a/package-lock.json
+++ b/package-lock.json
@@ -1,6 +1,6 @@
 {
   "name": "soql-parser-js",
-  "version": "3.0.1",
+  "version": "3.0.2",
   "lockfileVersion": 1,
   "requires": true,
   "dependencies": {
diff --git a/package.json b/package.json
index 04df77a..fde040e 100644
--- a/package.json
+++ b/package.json
@@ -1,6 +1,6 @@
 {
   "name": "soql-parser-js",
-  "version": "3.0.1",
+  "version": "3.0.2",
   "description": "Salesforce.com SOQL parser and composer",
   "main": "dist/index.js",
   "module": "dist_esm/index.js",
diff --git a/src/models.ts b/src/models.ts
index 350c7ae..4e8f783 100644
--- a/src/models.ts
+++ b/src/models.ts
@@ -136,15 +136,16 @@ export interface OrderByExpressionContext extends WithIdentifier {
   nulls?: IToken[];
 }
 
-export interface OrderByFunctionExpressionContext extends WithIdentifier {
+export interface OrderByGroupingFunctionExpressionContext extends WithIdentifier {
   fn: IToken[];
   order?: IToken[];
   nulls?: IToken[];
 }
 
-export interface orderByAggregateOrLocationExpressionContext {
-  locationFunction?: CstNode[];
+export interface OrderBySpecialFunctionExpressionContext {
   aggregateFunction?: CstNode[];
+  dateFunction?: CstNode[];
+  locationFunction?: CstNode[];
   order?: IToken[];
   nulls?: IToken[];
 }
diff --git a/src/parser/parser.ts b/src/parser/parser.ts
index 669b3f0..54d0e14 100644
--- a/src/parser/parser.ts
+++ b/src/parser/parser.ts
@@ -339,8 +339,8 @@ export class SoqlParser extends CstParser {
       SEP: lexer.Comma,
       DEF: () => {
         this.OR([
-          { ALT: () => this.SUBRULE(this.orderByFunctionExpression, { LABEL: 'orderByExpressionOrFn' }) },
-          { ALT: () => this.SUBRULE(this.orderByAggregateOrLocationExpression, { LABEL: 'orderByExpressionOrFn' }) },
+          { ALT: () => this.SUBRULE(this.orderByGroupingFunctionExpression, { LABEL: 'orderByExpressionOrFn' }) },
+          { ALT: () => this.SUBRULE(this.orderBySpecialFunctionExpression, { LABEL: 'orderByExpressionOrFn' }) },
           { ALT: () => this.SUBRULE(this.orderByExpression, { LABEL: 'orderByExpressionOrFn' }) },
         ]);
       },
@@ -358,13 +358,17 @@ export class SoqlParser extends CstParser {
     });
   });
 
-  private orderByFunctionExpression = this.RULE('orderByFunctionExpression', () => {
+  private orderByGroupingFunctionExpression = this.RULE('orderByGroupingFunctionExpression', () => {
     this.CONSUME(lexer.Grouping, { LABEL: 'fn' });
     this.SUBRULE(this.functionExpression);
   });
 
-  private orderByAggregateOrLocationExpression = this.RULE('orderByAggregateOrLocationExpression', () => {
-    this.OR([{ ALT: () => this.SUBRULE(this.locationFunction) }, { ALT: () => this.SUBRULE(this.aggregateFunction) }]);
+  private orderBySpecialFunctionExpression = this.RULE('orderBySpecialFunctionExpression', () => {
+    this.OR([
+      { ALT: () => this.SUBRULE(this.aggregateFunction) },
+      { ALT: () => this.SUBRULE(this.dateFunction) },
+      { ALT: () => this.SUBRULE(this.locationFunction) },
+    ]);
     this.OPTION(() => {
       this.OR1([{ ALT: () => this.CONSUME(lexer.Asc, { LABEL: 'order' }) }, { ALT: () => this.CONSUME(lexer.Desc, { LABEL: 'order' }) }]);
     });
diff --git a/src/parser/visitor.ts b/src/parser/visitor.ts
index e417869..8a0aaa2 100644
--- a/src/parser/visitor.ts
+++ b/src/parser/visitor.ts
@@ -1,37 +1,38 @@
+import { IToken } from 'chevrotain';
 import {
   Condition,
+  ConditionWithValueQuery,
   DateLiteral,
   DateNLiteral,
+  Field,
   FieldFunctionExpression,
   FieldRelationship,
+  FieldRelationshipWithAlias,
   FieldSubquery,
   FieldType,
   FieldTypeOf,
   FieldTypeOfCondition,
+  FieldWithAlias,
   FunctionExp,
   GroupByClause,
+  GroupByFieldClause,
+  GroupByFnClause,
   HavingClause,
+  HavingClauseWithRightCondition,
   LiteralType,
   NullsOrder,
   OrderByClause,
   OrderByCriterion,
+  OrderByFnClause,
   Query,
   Subquery,
-  WhereClause,
-  WithDataCategoryCondition,
-  Field,
-  FieldRelationshipWithAlias,
-  FieldWithAlias,
-  WhereClauseWithRightCondition,
-  GroupByFnClause,
-  GroupByFieldClause,
-  HavingClauseWithRightCondition,
-  OrderByFnClause,
-  ConditionWithValueQuery,
-  ValueFunctionCondition,
   ValueCondition,
+  ValueFunctionCondition,
   ValueQueryCondition,
   ValueWithDateNLiteralCondition,
+  WhereClause,
+  WhereClauseWithRightCondition,
+  WithDataCategoryCondition,
 } from '../api/api-models';
 import {
   ApexBindVariableExpressionContext,
@@ -46,15 +47,20 @@ import {
   FieldFunctionContext,
   FromClauseContext,
   FunctionExpressionContext,
+  GeoLocationFunctionContext,
   GroupByClauseContext,
+  GroupByFieldListContext,
   HavingClauseContext,
   LiteralTypeWithSubquery,
+  LocationFunctionContext,
   OperatorContext,
   OrderByClauseContext,
   OrderByExpressionContext,
-  OrderByFunctionExpressionContext,
+  OrderByGroupingFunctionExpressionContext,
+  OrderBySpecialFunctionExpressionContext,
   SelectClauseContext,
   SelectClauseFunctionIdentifierContext,
+  SelectClauseIdentifierContext,
   SelectClauseSubqueryIdentifierContext,
   SelectClauseTypeOfContext,
   SelectClauseTypeOfElseContext,
@@ -66,16 +72,9 @@ import {
   WhereClauseSubqueryContext,
   WithClauseContext,
   WithDateCategoryContext,
-  LocationFunctionContext,
-  GeoLocationFunctionContext,
-  orderByAggregateOrLocationExpressionContext,
-  GroupByFieldListContext,
-  SelectClauseIdentifierContext,
 } from '../models';
-import { isSubqueryFromFlag, isToken } from '../utils';
+import { isString, isSubqueryFromFlag, isToken } from '../utils';
 import { parse, ParseQueryConfig, SoqlParser } from './parser';
-import { isString, isNull } from 'util';
-import { IToken } from 'chevrotain';
 
 const parser = new SoqlParser();
 
@@ -481,7 +480,7 @@ class SOQLVisitor extends BaseSoqlVisitor {
     return orderByClause;
   }
 
-  orderByFunctionExpression(ctx: OrderByFunctionExpressionContext): OrderByClause {
+  orderByGroupingFunctionExpression(ctx: OrderByGroupingFunctionExpressionContext): OrderByClause {
     const orderByClause: OrderByClause = {
       fn: this.$_getFieldFunction(ctx, false, false),
     };
@@ -494,12 +493,14 @@ class SOQLVisitor extends BaseSoqlVisitor {
     return orderByClause;
   }
 
-  orderByAggregateOrLocationExpression(ctx: orderByAggregateOrLocationExpressionContext): OrderByClause {
+  orderBySpecialFunctionExpression(ctx: OrderBySpecialFunctionExpressionContext): OrderByClause {
     const orderByClause: Partial<OrderByClause> = {};
-    if (ctx.locationFunction) {
-      (orderByClause as OrderByFnClause).fn = this.visit(ctx.locationFunction, { includeType: false });
-    } else {
+    if (ctx.aggregateFunction) {
       (orderByClause as OrderByFnClause).fn = this.visit(ctx.aggregateFunction, { includeType: false });
+    } else if (ctx.dateFunction) {
+      (orderByClause as OrderByFnClause).fn = this.visit(ctx.dateFunction, { includeType: false });
+    } else if (ctx.locationFunction) {
+      (orderByClause as OrderByFnClause).fn = this.visit(ctx.locationFunction, { includeType: false });
     }
     if (ctx.order && ctx.order[0]) {
       orderByClause.order = ctx.order[0].tokenType.name as OrderByCriterion;
@@ -736,7 +737,7 @@ class SOQLVisitor extends BaseSoqlVisitor {
       const arrayValues: ArrayExpressionWithType[] = this.visit(ctx.arrayExpression);
       value = arrayValues.map((item: any) => item.value);
       const dateLiteralTemp = arrayValues.map((item: any) => item.variable || null);
-      const hasDateLiterals = dateLiteralTemp.some(item => !isNull(item));
+      const hasDateLiterals = dateLiteralTemp.some(item => item !== null);
       if (new Set(arrayValues.map((item: any) => item.type)).size === 1) {
         literalType = this.$_getLiteralTypeFromTokenType(arrayValues[0].type);
       } else {


Test patch:
diff --git a/test/test-cases.ts b/test/test-cases.ts
index 7a2a523..e6147ed 100644
--- a/test/test-cases.ts
+++ b/test/test-cases.ts
@@ -2012,6 +2012,129 @@ export const testCases: TestCase[] = [
       },
     },
   },
+  {
+    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',
+        },
+      ],
+    },
+  },
 ];
 
 export default testCases;