Ambiguous issue when user id filtering in multiple relations

[ajira86]

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 a "company" object which as "owner" relation ship on multiple object. This let me manage object access for multiple groups of user and keep common permissions system.

My issue come when an object as multiple children with permission rules on directus_users table.

To Reproduce

1. Create 3 objects like test1, test2, test3
2. Create a relation to directus_users in test2 and test3
3. Create a relation to test2 and test3 in test1
4. Define read rules to test2.user and test3.user with CURRENT_USER
5. Login with limited user, you can read test2 and test3 objects and its items
6. You can see test1 object but not its items with error [INTERNAL_SERVER_ERROR] An unexpected error occurred.

Errors Shown

08:57:52 🚨 error select * from (select "test3"."id" from "test3" left join "directus_users" as "xccij" on "test3"."user" = "xccij"."id" where (("xccij"."id" = $1)) and "id" = $2 limit $3) as "foo" - column reference "id" is ambiguous
error: select * from (select "test3"."id" from "test3" left join "directus_users" as "xccij" on "test3"."user" = "xccij"."id" where (("xccij"."id" = $1)) and "id" = $2 limit $3) as "foo" - column reference "id" is ambiguous

What version of Directus are you using?

v9.0.0-rc.99

What version of Node.js are you using?

16.12.0

What database are you using?

Postgres (PostGIS)

What browser are you using?

Firefox

What operating system are you using?

Debian

How are you deploying Directus?

docker

[8byr0]

If this can help troubleshooting:

Same issue here using rc99. Only the way to get this error is a bit different. On my side this is the result of a gql aggregation:

 worktime_aggregated(
      groupBy: ["project"]
      filter: { project: { id: { _eq: $projectId } }, billable: { _eq: true } }
    ) {
      group
      count {
        id
      }
      sum {
        duration_seconds
      }
    }

Which results in a similar error:

[GraphQL error]: Message: select "worktime"."project", count("id") as "count->id", count("__typename") as "count->__typename", sum("duration_seconds") as "sum->duration_seconds", sum("__typename") as "sum->__typename" from "worktime" left join "project" as "bvsva" on "worktime"."project" = "bvsva"."id" where "bvsva"."id" = $1 and "worktime"."billable" = $2 group by "project" order by "worktime"."project" asc limit $3 - column reference "id" is ambiguous, Location: [object Object], Path: worktime_aggregated

[Markario]

I have the same issue. It comes from this union all

directus/api/src/database/run-ast.ts

Lines 74 to 89 in 192de4a

	if (query.union) {
	const [field, keys] = query.union;
	if (keys.length) {
	const queries = keys.map((key) => {
	return knex.select('*').from(
	dbQuery
	.clone()
	.andWhere({ [field]: key })
	.as('foo')
	);
	});
	dbQuery = knex.unionAll(queries);
	}
	}

I patched mine to use a ref of the column (field) in the current collection/table (using getColumnPreprocessor) instead of [field]: value, but there's probably a better way to fix this. Perhaps moving this whole if (query.union) block into getDBQuery.

[Oreilles]

@rijkvanzanten in op's query:

SELECT
	"test3"."id"
FROM
	"test3"
LEFT JOIN "directus_users" AS "xccij" ON "test3"."user" = "xccij"."id"
WHERE (("xccij"."id" = $1))
AND "id" = $2
LIMIT $3

is the id in "id" = $2 refering to "test3"."id" or "xccij"."id" ?

[tspvivek]

We also started facing the issue after upgrading to RC99. The ambiguous issue arises when *.* or *.*.* is used for fields. As a workaround we add required fields one by one for now..

[rijkvanzanten]

@rijkvanzanten in op's query:

SELECT
	"test3"."id"
FROM
	"test3"
LEFT JOIN "directus_users" AS "xccij" ON "test3"."user" = "xccij"."id"
WHERE (("xccij"."id" = $1))
AND "id" = $2
LIMIT $3

is the id in "id" = $2 refering to "test3"."id" or "xccij"."id" ?

Not sure! I'm afraid there isn't enough information in the OP to know exactly where that filter on id = 2 comes from. That being said, I suspect it's the primary key of the item that's used in a filter, as that would align with what @8byr0 mentioned.

[rijkvanzanten]

@tspvivek Do you have a database dump to reproduce with? Are you using any other filters? I just tried this in a new setup, but am having some trouble reproducing it 🤔

[8byr0]

@rijkvanzanten in op's query:

SELECT
	"test3"."id"
FROM
	"test3"
LEFT JOIN "directus_users" AS "xccij" ON "test3"."user" = "xccij"."id"
WHERE (("xccij"."id" = $1))
AND "id" = $2
LIMIT $3

is the id in "id" = $2 refering to "test3"."id" or "xccij"."id" ?

Not sure! I'm afraid there isn't enough information in the OP to know exactly where that filter on id = 2 comes from. That being said, I suspect it's the primary key of the item that's used in a filter, as that would align with what @8byr0 mentioned.

In my case the problem actually comes from the count("id") select statement.
The actual query is

select "worktime"."project", 
count("id") as "count->id", 
sum("duration_seconds") as "sum->duration_seconds" 

from "worktime" 
left join "project" as "bvsva" on "worktime"."project" = "bvsva"."id" 
where "bvsva"."id" = $1 and "worktime"."billable" = $2 

group by "project" 
order by "worktime"."project" asc 

when it should be

select "worktime"."project", 
# We explicitly say that it's worktime.id that has to be counted (otherwise it's confusing with project.id)
count("worktime"."id") as "count->id", 
sum("duration_seconds") as "sum->duration_seconds" 

from "worktime" 
left join "project" as "bvsva" on "worktime"."project" = "bvsva"."id" 
where "bvsva"."id" = $1 and "worktime"."billable" = $2 

group by "project" 
order by "worktime"."project" asc 

[Markario]

In my case there was no filter on the client side. There is a M2O relation + permissions, and the and id = ? is added in the query.union block I linked previously.

The client query was simplified to this

query {
  collectionOne {
    m2oRelation {
      anyFieldScalarOrOtherwise
    }
  }
}

id in my particular case is picked not from a filter, nor the client query, but as the primary key in this block (in the case of M2O)

directus/api/src/database/run-ast.ts

Lines 248 to 257 in 192de4a

	if (nestedNode.type === 'm2o') {
	const foreignField = schema.collections[nestedNode.relation.related_collection!].primary;
	const foreignIds = uniq(parentItems.map((res) => res[nestedNode.relation.field])).filter((id) => id);
	const limit = nestedNode.query.limit;
	if (limit === -1) {
	merge(nestedNode, { query: { filter: { [foreignField]: { _in: foreignIds } } } });
	} else {
	nestedNode.query.union = [foreignField, foreignIds];
	}
	} else if (nestedNode.type === 'o2m') {

foreignField is a reference to the primary key field, id in my case and probably most cases.

directus/api/src/database/run-ast.ts

Line 249 in 192de4a

const foreignField = schema.collections[nestedNode.relation.related_collection!].primary;

and passing it in nestedNode.query.union = [foreignField, foreignIds] which results in the additional where added here

directus/api/src/database/run-ast.ts

Lines 74 to 89 in 192de4a

	if (query.union) {
	const [field, keys] = query.union;
	if (keys.length) {
	const queries = keys.map((key) => {
	return knex.select('*').from(
	dbQuery
	.clone()
	.andWhere({ [field]: key })
	.as('foo')
	);
	});
	dbQuery = knex.unionAll(queries);
	}
	}

The foreignField/field is not prefixed with the table and is passed as-is in the additional where added for unions.

[Markario]

One way I narrowed it down was by adding limit=-1 to my query to bypass the union logic for M2O

[rijkvanzanten]

@Markario Does the same problem apply in the REST API? I was trying to reproduce it, but didn't test with GraphQL. Wondering if that's where it goes wrong

[Markario]

@rijkvanzanten Yeah, took me a few mins to undo my patch and look at the REST docs. For the same collection I did GET /items/collectionOne?fields[]=m2oCollection.anyColumn and get the same error as the GraphQL equivalent. I'll try to create a minimal dump to reproduce it.

error: 
select * from (
  select "m2oCollection"."id"
  from "m2oCollection"
  left join "aCollectionForSomePermissions1" as "folec" on "m2oCollection"."relationForPermissions1" = "folec"."id"
  left join "aCollectionForSomePermissions2" as "nsipf" on "m2oCollection"."relationForPermissions2" = "nsipf"."id"
  where ("folec"."status" = $1 and "nsipf"."status" = $2) and "id" = $3
  limit $4
) as "foo"
union all
select * from (
  select "m2oCollection"."id"
  from "m2oCollection"
  left join "aCollectionForSomePermissions1" as "folec" on "m2oCollection"."relationForPermissions1" = "folec"."id"
  left join "aCollectionForSomePermissions2" as "nsipf" on "m2oCollection"."relationForPermissions2" = "nsipf"."id"
  where ("folec"."status" = $5 and "nsipf"."status" = $6) and "id" = $7
  limit $8
) as "foo" - column reference "id" is ambiguous

[Markario]

Created a fresh directus project and reproduced the issue with both GraphQL & REST queries.

https://gist.github.com/Markario/4d004c67b9df108f7d72ca5b72472458 (gist of dump + notes)

Authorization Bearer customer

GraphQL

query {
  collection1 {
    related {
      name
    }
  }
}

results in

{
  "errors": [
    {
      "message": "select * from (select \"collection2\".\"name\", \"collection2\".\"id\" from \"collection2\" left join \"collection3\" as \"yujfc\" on \"collection2\".\"author\" = \"yujfc\".\"id\" where (\"yujfc\".\"status\" = $1) and \"id\" = $2 limit $3) as \"foo\" - column reference \"id\" is ambiguous",
      "locations": [
        {
          "line": 31,
          "column": 3
        }
      ],
      "path": [
        "collection1"
      ]
    }
  ],
  "data": {
    "collection1": null
  }
}

Where $2 = 51cc0a0a-688e-41d4-80e1-2a8409f19b01 (an id in collection2)

The following works as it bypasses the M2O query.union

query {
  collection1 {
    related(limit: -1) {
      name
    }
  }
}

REST

http://localhost:8055/items/collection1?access_token=customer&fields=related.name

results in error: select * from (select "collection2"."name", "collection2"."id" from "collection2" left join "collection3" as "khdcz" on "collection2"."author" = "khdcz"."id" where ("khdcz"."status" = $1) and "id" = $2 limit $3) as "foo" - column reference "id" is ambiguous

[rijkvanzanten]

Thanks @Markario! That reproduction is super helpful 👍🏻

[Oreilles]

So, there is two problems: The foreignField/field is effectively not prefixed with its the table when adding the where clause - that could have been an easy fix, but the other problem is that the related collection is joined with an alias name. This happens deep inside applyFilter in applyQuery in getDBQuery. So we'd need a way to pass those alias up to runAST.run...

[Markario]

In my query above the table collection2, collection1.related's relation, isn't aliased and only the joined tables are; so, prefixing works collection2.id. Is there a scenario where that would not be the case?

[rijkvanzanten]

@Markario I believe @Oreilles means the internal aliasing that the API does. In order to prevent this exact problem on the collection naming, each collection is aliased to a random string internally 👍🏻

[Markario]

Right that makes sense, but there isn't any internal aliasing happening in any of the above cases on the primary query tables. I see the joined tables have generated aliases, but not the first table. Is there a situation where the first table in the query is also aliased?

directus/api/src/database/run-ast.ts

Line 228 in 192de4a

const dbQuery = knex.select(fieldNodes.map(preProcess)).from(table);

Is there a scenario where nestedNode.query.union's foreignField does not refer to that same table? Maybe M2A? I haven't tested with a similar M2A relation.

[rijkvanzanten]

Is there a situation where the first table in the query is also aliased?

Hmm no I don't think so. As of right now the root query just relies on the original table name, as that should be unique in the query as we alias all the relational ones 🤔