PostgresSQL error - Syntax error at the end of input - Aggregation

[srameshr]

Issue Description

The below aggregation query does not work on POSTGRES while the same works with MONGODB

Steps to reproduce

Run the below query inside a cloud function, with user and media being string type columns.

      var pipeline = {
            group: { objectId: { user: "$user", media: "$media" }, count: { $sum: 1 } }
      };
      
      var query = new Parse.Query("Conversion");
      const data = await query.aggregate(pipeline);;
      return data;

Expected Results

Array of data with count and unique media and user values

[
{ media: 'm1', user: 'u1', count: 100 },
{media: 'm2', user: 'u2', count: 20 }
]

Actual Outcome

It throw an error

error: syntax error at end of input
    at Connection.parseE (code/apps/app/app-analytics/node_modules/pg/lib/connection.js:606:11)
    at Connection.parseMessage (code/apps/app/app-analytics/node_modules/pg/lib/connection.js:403:19)
    at Socket.<anonymous> (code/apps/app/app-analytics/node_modules/pg/lib/connection.js:123:22)
    at Socket.emit (events.js:210:5)
    at Socket.EventEmitter.emit (domain.js:476:20)
    at addChunk (_stream_readable.js:308:12)
    at readableAddChunk (_stream_readable.js:289:11)
    at Socket.Readable.push (_stream_readable.js:223:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:182:23)
error: Uncaught internal server error.syntax error at end of input {"name":"error","length":91,"severity":"ERROR","code":"42601","position":"59","file":"scan.l","line":"1126","routine":"scanner_yyerror","stack":"error: syntax error at end of input\n    at Connection.parseE (code/apps/app/app-analytics/node_modules/pg/lib/connection.js:606:11)\n    at Connection.parseMessage (code/apps/app/app-analytics/node_modules/pg/lib/connection.js:403:19)\n    at Socket.<anonymous> (code/apps/app/app-analytics/node_modules/pg/lib/connection.js:123:22)\n    at Socket.emit (events.js:210:5)\n    at Socket.EventEmitter.emit (domain.js:476:20)\n    at addChunk (_stream_readable.js:308:12)\n    at readableAddChunk (_stream_readable.js:289:11)\n    at Socket.Readable.push (_stream_readable.js:223:10)\n    at TCP.onStreamRead (internal/stream_base_commons.js:182:23)"}

Test Case

Environment Setup

• Server

  • parse-server version : 3.10.0

• Operating System: AWS, Amazon Linux

  • Hardware: t1 instance
  • Localhost or remote server? (AWS, Heroku, Azure, Digital Ocean, etc): Both local and AWS

• JS SDK

  • JS SDK version: 2.11.0
  • Application? (Browser, Node, React-Native, etc): Browser

Logs/Trace

parse-server-example running on port 1337.
verbose: REQUEST for [POST] /parse/functions/get_analytics: {
  "campaignId": "Gkl1uGMZpK"
} {"method":"POST","url":"/parse/functions/get_analytics","headers":{"host":"localhost:1337","accept-encoding":"deflate, gzip","connection":"keep-alive","accept":"application/json, text/plain, */*","sec-fetch-dest":"empty","x-parse-application-id":"frills_analytics","user-agent":"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_3) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.116 Safari/537.36","x-parse-rest-api-key":"restApiKey","content-type":"application/json","origin":"http://localhost:3000","sec-fetch-site":"same-site","sec-fetch-mode":"cors","referer":"http://localhost:3000/dashboard/campaigns/ozmZuh5lMz/analytics","accept-language":"en-US,en;q=0.9","content-length":"27"},"body":{"campaignId":"Gkl1uGMZpK"}}
verbose: REQUEST for [GET] /parse/aggregate/Conversion: {
  "pipeline": {
    "group": {
      "objectId": {
        "user": "$user",
        "media": "$media"
      },
      "count": {
        "$sum": 1
      }
    }
  }
} {"method":"GET","url":"/parse/aggregate/Conversion","headers":{"user-agent":"node-XMLHttpRequest, Parse/js2.11.0 (NodeJS 12.13.0)","accept":"*/*","content-type":"text/plain","host":"localhost:1337","content-length":"250","connection":"close"},"body":{"pipeline":{"group":{"objectId":{"user":"$user","media":"$media"},"count":{"$sum":1}}}}}
error: Uncaught internal server error.syntax error at end of input {"name":"error","length":91,"severity":"ERROR","code":"42601","position":"59","file":"scan.l","line":"1126","routine":"scanner_yyerror","stack":"error: syntax error at end of input\n    at Connection.parseE (/app/node_modules/pg/lib/connection.js:606:11)\n    at Connection.parseMessage (/app/node_modules/pg/lib/connection.js:403:19)\n    at Socket.<anonymous> (/app/node_modules/pg/lib/connection.js:123:22)\n    at Socket.emit (events.js:210:5)\n    at Socket.EventEmitter.emit (domain.js:476:20)\n    at addChunk (_stream_readable.js:308:12)\n    at readableAddChunk (_stream_readable.js:289:11)\n    at Socket.Readable.push (_stream_readable.js:223:10)\n    at TCP.onStreamRead (internal/stream_base_commons.js:182:23)"}
error: syntax error at end of input
    at Connection.parseE (/app/node_modules/pg/lib/connection.js:606:11)
    at Connection.parseMessage (/app/node_modules/pg/lib/connection.js:403:19)
    at Socket.<anonymous> (/app/node_modules/pg/lib/connection.js:123:22)
    at Socket.emit (events.js:210:5)
    at Socket.EventEmitter.emit (domain.js:476:20)
    at addChunk (_stream_readable.js:308:12)
    at readableAddChunk (_stream_readable.js:289:11)
    at Socket.Readable.push (_stream_readable.js:223:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:182:23)

```

[srameshr]

@dplewis @davimacedo

[dplewis]

The aggregate framework as with much of the API is built around mongo. With that we tried to bind the mongo syntax to PG as much as we could.

Count should work there is a test case for it.

[srameshr]

No it does not. When objectId value is a string, it works fine. But when its an object it throws an error. See below

// This aggregation works fine

    "group": {
      "objectId": "$user",
      "count": {
        "$sum": 1
      }
    }
  }

Where as this does not.

 "pipeline": {
    "group": {
      "objectId": {
        "user": "$user",
        "media": "$media"
      },
      "count": {
        "$sum": 1
      }
    }
  }

Is there any test case to validate objectId working fine with Postgres aggregation? @dplewis

[srameshr]

Moving the issue to Parse Server repo
parse-community/parse-server#6479