Ambigous foreign key column reference

[mhdaouas]

Preflight Checklist

• I have completed all Troubleshooting Steps.
• I'm on the latest version of Directus.
• There's no other issue that already describes my problem.

Describe the Bug

I created different relation type fields (like O2M) in a single content type object and named the foreign key as "role" (I wanted to refer to the directus_roles table to have a list of roles in the object to be able to configure a workflow). Now I'm unable to access the app (white screen) and I get this error "column reference "role" is ambiguous".

I also deleted the single content type table and recreated it but couldn't get back the app to work.

To Reproduce

See above

What version of Directus are you using?

9.0.0-rc.93

What version of Node.js are you using?

14.16.1

What database are you using?

Postgresql 13

What browser are you using?

Chrome

What operating system are you using?

Windows

How are you deploying Directus?

Locally on Visual Code

[mhdaouas]

Ok I figured out that a role column with integer type has been added to directus_roles so I deleted it and fixed the problem.

[mhdaouas]

I re-opened the issue because I believe this case should be taken into consideration by preventing the user from using a standard column name like "role" in this case.

[azrikahar]

Ok I figured out that a role column with integer type has been added to directus_roles so I deleted it and fixed the problem.

Thanks for the investigation! That helps alot to narrow down what happened.

Upon investigation, seems like it's not really standard column name being the issue (as directus_user always have been using role as the column name), but more so the API middleware here:

directus/api/src/middleware/authenticate.ts

Lines 41 to 44 in 437e52a

	const user = await database
	.select('role', 'directus_roles.admin_access', 'directus_roles.app_access')
	.from('directus_users')
	.leftJoin('directus_roles', 'directus_users.role', 'directus_roles.id')

Notice how line 42 just selects role, but there's 2 possibilities (after your above action) now which are directus_users.role and directus_roles.role, hence the "ambiguous" error. Ensuring we select directus_users.role (same like line 44) will prevent such error from ever happening 👍 Just tested and that fix worked. Thanks again for re-opening the issue for us to properly look into it.