Error encountered when filtering a UUID field in a collection

Oded_Magger · August 15, 2021, 8:34am

Expected behavior

I have a collection called ‘Account’, with a field called id which is a primary key and has type UUID:

  const Account = sequelize.define('account', {
    id: {
      type: DataTypes.UUID,
      primaryKey: true,
      defaultValue: Sequelize.literal('uuid_generate_v4()'),
      allowNull: false,
    },
...

In Forest Admin, when I select the “Account” collection and try to filter it using ‘is’ or ‘starts with’, I expect to see Account records, filtered accordingly.

Actual behavior

The webpage shows ‘Your server encountered an error’.

(Importantly, without filter the collection works fine).

Failure Logs

The following appears as soon as I try to filter:

When filtering with 'Starts with":

mpathy_backoffice | [forest] 🌳🌳🌳  Unexpected error: operator does not exist: uuid ~~ unknown
empathy_backoffice | {
empathy_backoffice |   "name": "SequelizeDatabaseError",
empathy_backoffice |   "parent": {
empathy_backoffice |     "length": 200,
empathy_backoffice |     "name": "error",
empathy_backoffice |     "severity": "ERROR",
empathy_backoffice |     "code": "42883",
empathy_backoffice |     "hint": "No operator matches the given name and argument types. You might need to add explicit type casts.",
empathy_backoffice |     "position": "86",
empathy_backoffice |     "file": "parse_oper.c",
empathy_backoffice |     "line": "731",
empathy_backoffice |     "routine": "op_error",
empathy_backoffice |     "sql": "SELECT count(*) AS \"count\" FROM \"public\".\"account\" AS \"account\" WHERE \"account\".\"id\" LIKE '45%';"
empathy_backoffice |   },
empathy_backoffice |   "original": {
empathy_backoffice |     "length": 200,
empathy_backoffice |     "name": "error",
empathy_backoffice |     "severity": "ERROR",
empathy_backoffice |     "code": "42883",
empathy_backoffice |     "hint": "No operator matches the given name and argument types. You might need to add explicit type casts.",
empathy_backoffice |     "position": "86",
empathy_backoffice |     "file": "parse_oper.c",
empathy_backoffice |     "line": "731",
empathy_backoffice |     "routine": "op_error",
empathy_backoffice |     "sql": "SELECT count(*) AS \"count\" FROM \"public\".\"account\" AS \"account\" WHERE \"account\".\"id\" LIKE '45%';"
empathy_backoffice |   },
empathy_backoffice |   "sql": "SELECT count(*) AS \"count\" FROM \"public\".\"account\" AS \"account\" WHERE \"account\".\"id\" LIKE '45%';",
empathy_backoffice |   "stack": "SequelizeDatabaseError: operator does not exist: uuid ~~ unknown\n    at Query.formatError (/usr/src/app/node_modules/sequelize/lib/dialects/postgres/query.js:354:16)\n    at query.catch.err (/usr/src/app/node_modules/sequelize/lib/dialects/postgres/query.js:71:18)\n    at tryCatcher (/usr/src/app/node_modules/sequelize/node_modules/bluebird/js/release/util.js:16:23)\n    at Promise._settlePromiseFromHandler (/usr/src/app/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:547:31)\n    at Promise._settlePromise (/usr/src/app/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:604:18)\n    at Promise._settlePromise0 (/usr/src/app/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:649:10)\n    at Promise._settlePromises (/usr/src/app/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:725:18)\n    at _drainQueueStep (/usr/src/app/node_modules/sequelize/node_modules/bluebird/js/release/async.js:93:12)\n    at _drainQueue (/usr/src/app/node_modules/sequelize/node_modules/bluebird/js/release/async.js:86:9)\n    at Async._drainQueues (/usr/src/app/node_modules/sequelize/node_modules/bluebird/js/release/async.js:102:5)\n    at Immediate.Async.drainQueues [as _onImmediate] (/usr/src/app/node_modules/sequelize/node_modules/bluebird/js/release/async.js:15:14)\n    at runCallback (timers.js:705:18)\n    at tryOnImmediate (timers.js:676:5)\n    at processImmediate (timers.js:658:5)"

When filtering with ‘is’:

empathy_backoffice | [forest] 🌳🌳🌳  Unexpected error: invalid input syntax for type uuid: "34"
empathy_backoffice | {
empathy_backoffice |   "name": "SequelizeDatabaseError",
empathy_backoffice |   "parent": {
empathy_backoffice |     "length": 101,
empathy_backoffice |     "name": "error",
empathy_backoffice |     "severity": "ERROR",
empathy_backoffice |     "code": "22P02",
empathy_backoffice |     "position": "88",
empathy_backoffice |     "file": "uuid.c",
empathy_backoffice |     "line": "137",
empathy_backoffice |     "routine": "string_to_uuid",
empathy_backoffice |     "sql": "SELECT count(*) AS \"count\" FROM \"public\".\"account\" AS \"account\" WHERE \"account\".\"id\" = '34';"
empathy_backoffice |   },
empathy_backoffice |   "original": {
empathy_backoffice |     "length": 101,
empathy_backoffice |     "name": "error",
empathy_backoffice |     "severity": "ERROR",
empathy_backoffice |     "code": "22P02",
empathy_backoffice |     "position": "88",
empathy_backoffice |     "file": "uuid.c",
empathy_backoffice |     "line": "137",
empathy_backoffice |     "routine": "string_to_uuid",
empathy_backoffice |     "sql": "SELECT count(*) AS \"count\" FROM \"public\".\"account\" AS \"account\" WHERE \"account\".\"id\" = '34';"
empathy_backoffice |   },
empathy_backoffice |   "sql": "SELECT count(*) AS \"count\" FROM \"public\".\"account\" AS \"account\" WHERE \"account\".\"id\" = '34';",
empathy_backoffice |   "stack": "SequelizeDatabaseError: invalid input syntax for type uuid: \"34\"\n    at Query.formatError (/usr/src/app/node_modules/sequelize/lib/dialects/postgres/query.js:354:16)\n    at query.catch.err (/usr/src/app/node_modules/sequelize/lib/dialects/postgres/query.js:71:18)\n    at tryCatcher (/usr/src/app/node_modules/sequelize/node_modules/bluebird/js/release/util.js:16:23)\n    at Promise._settlePromiseFromHandler (/usr/src/app/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:547:31)\n    at Promise._settlePromise (/usr/src/app/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:604:18)\n    at Promise._settlePromise0 (/usr/src/app/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:649:10)\n    at Promise._settlePromises (/usr/src/app/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:725:18)\n    at _drainQueueStep (/usr/src/app/node_modules/sequelize/node_modules/bluebird/js/release/async.js:93:12)\n    at _drainQueue (/usr/src/app/node_modules/sequelize/node_modules/bluebird/js/release/async.js:86:9)\n    at Async._drainQueues (/usr/src/app/node_modules/sequelize/node_modules/bluebird/js/release/async.js:102:5)\n    at Immediate.Async.drainQueues [as _onImmediate] (/usr/src/app/node_modules/sequelize/node_modules/bluebird/js/release/async.js:15:14)\n    at runCallback (timers.js:705:18)\n    at tryOnImmediate (timers.js:676:5)\n    at processImmediate (timers.js:658:5)"
empathy_backoffice | }

Context

Please provide any relevant information about your setup.

We are using Typescript
Package Version: forest-express-sequelize 7.12.2
Express Version: 4.17.1
Sequelize Version: 5.22.4
Database Dialect: PostGres
Database Version: 12.4
Project Name: Empathy Backoffice

Sliman_Medini · August 16, 2021, 9:39am

Hi @Oded_Magger,

Thank you for your feedback.

The UI allows making a “start with” filter on a UUID field. But as soon as the field is not a string, the filter fails to be applied. I am creating a ticket to ask for an explicit cast to be made.

The UI allows making a “is” filter with any string. Since only a valid UUID is possible in this case, please type the entire UUID when using the “is” filter.
I am creating a ticket here, to block any filter value other than a valid UUID.

Regards

Oded_Magger · August 16, 2021, 9:42am

Thanks Sliman,

Regarding the second item (using ‘is’) - if the input is not a valid UUID, I would expect the filtering to fail gracefully (e.g. return 0 records) rather than show a scary “server error”.

Our non-tech Forest Admin users were sure our services crashed because of that error .

Sliman_Medini · August 16, 2021, 9:47am

Yes in this case the UI should block instead of the request going to the server.

Nam_B · March 1, 2022, 5:18pm

@Sliman_Medini Is there a resolution to fix this error, I am getting similar one -
You might need to add explicit type casts.

2022-03-01T22:07:43.597+05:30	e[31m[forest] 🌳🌳🌳 Unexpected error: operator does not exist: uuid = character varyinge[39m

2022-03-01T22:07:43.597+05:30	e[31m{e[39m

2022-03-01T22:07:43.597+05:30	e[31m "name": "SequelizeDatabaseError",e[39m

2022-03-01T22:07:43.597+05:30	e[31m "parent": {e[39m

2022-03-01T22:07:43.597+05:30

Copy
e[31m    "length": 210,e[39m
e[31m "length": 210,e[39m

2022-03-01T22:07:43.597+05:30	e[31m "name": "error",e[39m

2022-03-01T22:07:43.597+05:30	e[31m "severity": "ERROR",e[39m

2022-03-01T22:07:43.597+05:30	e[31m "code": "42883",e[39m

2022-03-01T22:07:43.597+05:30	e[31m "hint": "No operator matches the given name and argument types. You might need to add explicit type casts.",e[39m

2022-03-01T22:07:43.597+05:30	e[31m "position": "292",e[39m

2022-03-01T22:07:43.597+05:30	e[31m "file": "parse_oper.c",e[39m

2022-03-01T22:07:43.597+05:30	e[31m "line": "731",e[39m

2022-03-01T22:07:43.597+05:30	e[31m "routine": "op_error",e[39m

2022-03-01T22:07:43.597+05:30

Topic		Replies	Views
Bug: Can't search by strings that begin with a number Help me!	7	446	August 11, 2020
Search on collection is sending an error - sequelize-typescript UUID are interpreted as string Help me! typescript	12	2256	February 3, 2022
"Not a valid UUID" on nil UUID Help me!	5	1337	December 2, 2021
Duplicate entries when filtering for ID Help me!	9	690	May 2, 2022
Search on collection is sending an error - "function lower(uuid) does not exist" Help me!	4	1329	May 27, 2020

Error encountered when filtering a UUID field in a collection

Expected behavior

Actual behavior

Failure Logs

Context

Related topics