Skip to content

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

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

Description

@srameshr
srameshr
opened on Mar 8, 2020

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)

```

Activity

  1. srameshr commented on Mar 8, 2020

    @srameshr
    srameshr
    on Mar 8, 2020
    ContributorAuthor

    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

  2. srameshr commented on Mar 8, 2020

    @srameshr
    srameshr
    on Mar 8, 2020
    ContributorAuthor

    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', '' ]

  3. davimacedo commented on Mar 9, 2020

    @davimacedo
    davimacedo
    on Mar 9, 2020
    Member

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

  4. srameshr commented on Mar 9, 2020

    @srameshr
    srameshr
    on Mar 9, 2020
    ContributorAuthor

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

  5. srameshr commented on Mar 9, 2020

    @srameshr
    srameshr
    on Mar 9, 2020
    ContributorAuthor

    @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.

  6. BufferUnderflower commented on Mar 9, 2020

    @BufferUnderflower
    BufferUnderflower
    on Mar 9, 2020
    Contributor

    +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.

  7. srameshr commented on Mar 9, 2020

    @srameshr
    srameshr
    on Mar 9, 2020
    ContributorAuthor

    @davimacedo @dplewis
    Here is the PR: #6483

  8. davimacedo commented on Mar 9, 2020

    @davimacedo
    davimacedo
    on Mar 9, 2020
    Member

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

  9. davimacedo
    closed this as completedon Mar 9, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions