Invalid uuid is breaking relationship filters when using postgresql

[skindc]

I am currently trying to upgrade including core@5.6.0
There is now a bug in RelationshipSelect where on entering text to filter the related items the UI breaks and just displays 'Error'

To replicate:
As far as I can see all my relationships that use the 'select' ui option, therefore I am presuming this affects all relationship fields.

Expected:
When I type any value into the input field of RelationShip select I expect it to filter the dropdown list by filtering the search fields defined in the relationship or the related item.

I have done some investigation into this.

When attempting to debug this I watched the network panel of developer tools in chrome to see if there was a Graphql Error reported. In fact there was.

The response from the RelationshipQuery when entering the filter reported the error below.

        {
            "message": "Prisma error: Inconsistent column data: Error creating UUID, invalid character: expected an optional prefix of `urn:uuid:` followed by [0-9a-zA-Z], found `g` at 1",
            "locations": [
                {
                    "line": 7,
                    "column": 3
                }
            ],
            "path": [
                "count"
            ],
            "extensions": {
                "code": "KS_PRISMA_ERROR",
                "prisma": {
                    "name": "PrismaClientKnownRequestError",
                    "code": "P2023",
                    "clientVersion": "4.16.2",
                    "meta": {
                        "message": "Error creating UUID, invalid character: expected an optional prefix of `urn:uuid:` followed by [0-9a-zA-Z], found `g` at 1"
                    }
                },
                "stacktrace": [
                    "GraphQLError: Prisma error: Inconsistent column data: Error creating UUID, invalid character: expected an optional prefix of `urn:uuid:` followed by [0-9a-zA-Z], found `g` at 1",
                    "    at Object.prismaError (/Users/skin/Projects/activeProjects/Lokalliving/lokalliving-stack/apps/keystone6/node_modules/@keystone-6/core/dist/graphql-errors-f1c235e2.cjs.dev.js:43:10)",
                    "    at runWithPrisma (/Users/skin/Projects/activeProjects/Lokalliving/lokalliving-stack/apps/keystone6/node_modules/@keystone-6/core/dist/config-e832ee3f.cjs.dev.js:37:25)",
                    "    at async Object.count (/Users/skin/Projects/activeProjects/Lokalliving/lokalliving-stack/apps/keystone6/node_modules/@keystone-6/core/dist/config-e832ee3f.cjs.dev.js:821:17)"
                ]
            }
        },

You can see that is complaining about the letter 'g', of which is what I typed into the UI input could not be used to construct a UUID. I believed this was because despite the 'id' not being in my defined search fields that the where statement was including a comparitor for the id from the my input. I inspected the query to prove this correct and yes it was indeed.

{
  "operationName": "RelationshipSelect",
  "variables": {
      "where": {
          "OR": [
              {
                  "id": {
                      "equals": "g"
                  }
              },
              {
                  "id": {
                      "equals": "g"
                  }
              },
              {
                  "name": {
                      "contains": "g",
                      "mode": "insensitive"
                  }
              },
              {
                  "email": {
                      "contains": "g",
                      "mode": "insensitive"
                  }
              },
              {
                  "contactNumber": {
                      "contains": "g",
                      "mode": "insensitive"
                  }
              },
              {
                  "notes": {
                      "contains": "g",
                      "mode": "insensitive"
                  }
              }
          ]
      },
      "take": 10,
      "skip": 0
  },
  "query": "query RelationshipSelect($where: ContactWhereInput!, $take: Int!, $skip: Int!) {\n  items: contacts(where: $where, take: $take, skip: $skip) {\n    ____id____: id\n    ____label____: name\n    __typename\n  }\n  count: contactsCount(where: $where)\n}"
}

I believe this could have possibly been introduced in commit 27a8933 when the useFilter function in RelationshipSelect View was changed.

[skindc]

I have just rolled back one release at a time and the last release of core where this seems to operate as expected is 5.3.2

[dcousens]

I haven't seen this in my own projects, I'll try and replicate this soon - but I rarely use uuid tbh

[klemola]

I can verify that this happens to us too, exactly in the same way as the for @skindc .

Additionally, this has broken search in lists in the admin UI:

Same root cause: the letters in my search query are assumed to be part of an uuid by Prisma, and therefore the search fails.

We are on core v5.7.0.

I can also confirm that v5.3.2 does not have this issue.

In v5.6.0 the search fails silently as the Prisma GQL errors are not displayed in the UI (the feature landed in v5.7.0).

One of the possible culprits for the issue could be any of these ID related changes in core v5.4.0

[core] Adds arbitrary string identifier support as db: { idField: { kind: 'string' } } (https://github.com/keystonejs/keystone/pull/8645, https://github.com/keystonejs/keystone/pull/8648) @dcousens, @molomby
[core] Adds new unique filters for querying by more than 1 unique value (https://github.com/keystonejs/keystone/pull/8648) @dcousens
[core] Changes id field behaviours to not reject invalid identifiers when used in GraphQL resolvers, allowing developers to swap idField types without breaking (https://github.com/keystonejs/keystone/pull/8648) @dcousens
[core] Adds findOne and findMany support for Singleton lists with identifiers other than the default, not recommended in typical usage (https://github.com/keystonejs/keystone/pull/8648) @dcousens

[skindc]

Hey, any news on this? Maybe something I could help with when I get a minute.

[klemola]

I'm also curious about a potential fix to this issue. Ping @jsaarest, you should subscribe to be notified of any resolution!

[esaruoho]

any progress on this issue? we're meeting it consistently

[dcousens]

Can someone provide a minimum reproducible example? @esaruoho? 💙

[klemola]

@dcousens Here you go, repro:

https://github.com/klemola/keystone-id-bug-repro

Repro steps:

• setup Postgres (with docker-compose, config included)
• npm run dev
• create user
• create "Example" content
• search for it

While building the repro I noticed few things:

• Only happens with idField: { kind: "uuid" }
• Only happens with postgres - works with sqlite, even using uuid() instead of cuid()

About the repro:
I wanted to include the base setup we have, with auth and all.
Requires Postgres, I added a docker-compose.yml for easy setup - it also shows the example DB config.

Also ping @jsaarest

[skindc]

@klemola Thank you for submitting this sample, I just havn't had time.

[gautamsi]

you can generate uuid yourself if you set kind: 'string'

[fkrauthan]

Any progress here? I would love to update to latest version to be finally able to use node 20

@gautamsi how would you configure that kind: 'string' and then have a custom generation code in keystone?

[dcousens]

@fkrauthan please see

keystone/examples/custom-id/schema.ts

Lines 33 to 51 in bd33f07

	Order: list({
	access: allowAll,
	db: {
	idField: { kind: 'string' },
	},
	fields: {
	description: text({ validation: { isRequired: true } }),
	assignedTo: relationship({ ref: 'Person', many: false }),
	options: relationship({ ref: 'Option', many: true }),
	orderedAt: timestamp(),
	},
	hooks: {
	resolveInput: {
	create: async ({ listKey, operation, resolvedData }) => {
	return { ...resolvedData, id: makeCustomIdentifier(listKey) }
	},
	},
	},
	}),

[fkrauthan]

Ah yeah, unfortunately that changes the id field in the database (for existing data). To me the issue seem to be as "simple" as currently when using free text search (on dropdown and/or the admin list page) keystone just assembles a query for all searchable columns and adds the value entered. However it seems like that prisma got more strict if you have a UUID column. So what probably needs to change is that if you search for a value and the field is a UUID field (e.g. id) and the entered value is not a valid UUID to skip passing it into the query. That would solve that issue I believe.

I tried looking a bit into the code-base but couldn't really find the place where that search would be translated to individual queries.