SQL syntax error when querying join field on arrays of object

[hai-trung-le]

Describe the Bug

With the following collections

[{
  slug: "foo",
  access: {
    read: () => true,
  },
  fields: [
    {
      name: "bars",
      type: "array",
      fields: [
        {
          name: "bar",
          type: "relationship",
          relationTo: "bar"
        },
        {
          name: "optional",
          type: "checkbox",
        }
      ]
    }
  ]
}, {
  slug: "bar",
  access: {
    read: () => true,
  },
  fields: [
    {
      name: "foos",
      type: "join",
      collection: "foo",
      on: "bars.bar",
    },
  ]
}]

Then, curl localhost:3000/api/bar?where[foos][equals]=1 throws exception:

Failed query: select distinct "bar"."id", "bar"."created_at", "bar"."created_at" from "bar" left join "foo" "b3cd9786_c0fa_4b11_9278_9ecf8296b06e" on = "bar"."id" where "b3cd9786_c0fa_4b11_9278_9ecf8296b06e"."id" = ? order by "bar"."created_at" desc limit ?
params: 1,10: SQLITE_ERROR: near "=": syntax error: near "=": syntax error
at LibSQLPreparedQuery.queryWithCache (file:///.../node_modules/drizzle-orm/sqlite-core/session.js:43:15)
at async LibSQLPreparedQuery.values (file:///.../node_modules/drizzle-orm/libsql/session.js:193:12)
at async LibSQLPreparedQuery.all (file:///.../node_modules/drizzle-orm/libsql/session.js:136:18)
at async find (file:///.../node_modules/@payloadcms/drizzle/dist/find/findMany.js:79:32)

Link to the code that reproduces this issue

https://github.com/hai-trung-le/payload-join-bug

Reproduction Steps

See above

Which area(s) are affected?

area: core, db: sqlite

Environment Info

Binaries:
  Node: 24.14.0
Relevant Packages:
  payload: 3.80.0
  next: 16.2.1
  @payloadcms/db-sqlite: 3.80.0       
  @payloadcms/drizzle: 3.80.0
  @payloadcms/graphql: 3.80.0
  @payloadcms/next/utilities: 3.80.0  
  @payloadcms/richtext-lexical: 3.80.0
  @payloadcms/translations: 3.80.0    
  @payloadcms/ui/shared: 3.80.0
  react: 19.2.4
  react-dom: 19.2.4
Operating System:
  Platform: win32

[hai-trung-le]

I haven't checked but it's likely this also happens with Postgres. This is a minimal example trimmed down from my PayloadCMS + Postgres backend.

There is another (unrelated?) problem in the query: the field createdAt is selected twice, while updatedAt is not selected at all.

[github-actions]

🚀 This is included in version v3.81.0

[github-actions]

This issue has been automatically locked.
Please open a new issue if this issue persists with any additional detail.