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

[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]

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

@dplewis @davimacedo Moved this from JS SDK repo to here

[srameshr]

The error is because of the following line:
https://github.com/parse-community/parse-server/blob/master/src/Adapters/Storage/Postgres/PostgresStorageAdapter.js#L2222

It considers group _ids with only string and object syntax.
group: { _id: { user: '$user' }}
group: { _id: { birthday: { $dayOfMonth: '$createdAt' } }}

But not,
group: { _id: { user: '$user', media: '$media' }}

Query formed for 1 and 2 below

1. SELECT COUNT(*) AS $2:name,$3:name AS "objectId" FROM $1:name GROUP BY $3:name [ 'Conversion', 'total', 'user' ]

2. SELECT COUNT(*) AS $2:name FROM $1:name GROUP BY $3:raw [ 'Conversion', 'total', '' ]

[davimacedo]

Hi @srameshr thanks for reporting. Would you be willed to tackle this one and send a PR with the fix?

[srameshr]

@davimacedo I am pressed on time and not good with Postgres either. I will give it a shot.

[srameshr]

@davimacedo Also, the number of combinations that can be formed via Mongo aggregation and mapped to Postgres is nearly limitless. How about an option that allows just to pass a string to the aggregate method instead of having incomplete and breaking implementations?

Like, just pass a string to aggregate:

  query.aggregate(`
  SELECT 
   column_1, 
   column_2,
   aggregate_function(column_3)
FROM 
   table_name
GROUP BY 
   column_1,
   column_2;
`)

And pass this query directly to Node PGP instead of trying to requiring mongo like aggregate object to be passed.

[BufferUnderflower]

+1 for raw aggregate queries. It could return raw json to deal with. Even though you'll have to convert pointers, dates, etc by hand, it still can help to solve a bunch of complicated scenarios. I find it nearly impossible and not worth spending time to implement and support all the combinations of Mongo and Postgres versions and aggregate operators to comply with single SDK object format.

[srameshr]

@davimacedo @dplewis
Here is the PR: #6483

[davimacedo]

Thanks for the PR @srameshr . I've just merged it. About the raw aggregates, it looks a good idea.