GraphQL complex query + deep filtering issue

[derrickmehaffy]

Describe the bug
Attempting to use complex queries with deep filtering yields error or lack of data depending on Database

Steps to reproduce the behavior
(See models and queries below)

1. Setup two models, Article and Category in a oneWay relation (Article has one Category)
2. Add some test data and make sure to publish (or turn off D&P)
3. Construct a complex query (examples below)
4. Run test on GraphQL and REST
5. See error

Expected behavior
Should filter properly

Screenshots
If applicable, add screenshots to help explain your problem.

Code snippets

Article model:

{
  "kind": "collectionType",
  "collectionName": "articles",
  "info": {
    "name": "article",
    "description": ""
  },
  "options": {
    "increments": true,
    "timestamps": true,
    "draftAndPublish": true
  },
  "attributes": {
    "name": {
      "type": "string"
    },
    "body": {
      "type": "richtext"
    },
    "category": {
      "model": "category"
    }
  }
}

Category model:

{
  "kind": "collectionType",
  "collectionName": "categories",
  "info": {
    "name": "category"
  },
  "options": {
    "increments": true,
    "timestamps": true,
    "draftAndPublish": true
  },
  "attributes": {
    "name": {
      "type": "string"
    }
  }
}

"working" query:

query {
  articles(
    where: { _or: [{ name: "test" }, { category: 1 }] }
  ) {
    id
    name
    body
    category {
      id
      name
    }
  }
}

Not working:

query {
  articles(
    where: { _or: [{ name: "test" }, { category: { name: "test" } }] }
  ) {
    id
    name
    body
    category {
      id
      name
    }
  }
}

System

• Node.js version: 12.18.3
• NPM version: 6.14.6
• Strapi version: 3.2.3
• Database: SQLite & MariaDB 10.5
• Operating system: Linux Mint 20 (Ubuntu 20.04)

Additional context

There is no "error" on SQLite, the deep filtering just returns no results.

The error on MariaDB/MySQL:

GraphQL

{
  "errors": [
    {
      "message": "select distinct `articles`.* from `articles` left join `strapi`.`categories` as `categories_1` on `categories_1`.`id` = `articles`.`category` where (((`articles`.`name` = 'test')) or ((`categories_1`.`id` = {\"name\":\"test\"}))) and (`articles`.`published_at` is not null) limit 100 - ER_NON_UNIQ_ERROR: Column 'name' in where clause is ambiguous",
      "locations": [
        {
          "line": 2,
          "column": 3
        }
      ],
      "path": [
        "articles"
      ],
      "extensions": {
        "code": "INTERNAL_SERVER_ERROR",
        "exception": {
          "code": "ER_NON_UNIQ_ERROR",
          "errno": 1052,
          "sqlMessage": "Column 'name' in where clause is ambiguous",
          "sqlState": "23000",
          "index": 0,
          "sql": "select distinct `articles`.* from `articles` left join `strapi`.`categories` as `categories_1` on `categories_1`.`id` = `articles`.`category` where (((`articles`.`name` = 'test')) or ((`categories_1`.`id` = `name` = 'test'))) and (`articles`.`published_at` is not null) limit 100",
          "stacktrace": [
            "Error: ER_NON_UNIQ_ERROR: Column 'name' in where clause is ambiguous",
            "    at Query.Sequence._packetToError (/home/dmehaffy/Development/testComplex/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)",
            "    at Query.ErrorPacket (/home/dmehaffy/Development/testComplex/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)",
            "    at Protocol._parsePacket (/home/dmehaffy/Development/testComplex/node_modules/mysql/lib/protocol/Protocol.js:291:23)",
            "    at Parser._parsePacket (/home/dmehaffy/Development/testComplex/node_modules/mysql/lib/protocol/Parser.js:433:10)",
            "    at Parser.write (/home/dmehaffy/Development/testComplex/node_modules/mysql/lib/protocol/Parser.js:43:10)",
            "    at Protocol.write (/home/dmehaffy/Development/testComplex/node_modules/mysql/lib/protocol/Protocol.js:38:16)",
            "    at Socket.<anonymous> (/home/dmehaffy/Development/testComplex/node_modules/mysql/lib/Connection.js:88:28)",
            "    at Socket.<anonymous> (/home/dmehaffy/Development/testComplex/node_modules/mysql/lib/Connection.js:526:10)",
            "    at Socket.emit (events.js:314:20)",
            "    at addChunk (_stream_readable.js:298:12)",
            "    at readableAddChunk (_stream_readable.js:273:9)",
            "    at Socket.Readable.push (_stream_readable.js:214:10)",
            "    at TCP.onStreamRead (internal/stream_base_commons.js:188:23)",
            "    at Protocol._enqueue (/home/dmehaffy/Development/testComplex/node_modules/mysql/lib/protocol/Protocol.js:144:48)",
            "    at Connection.query (/home/dmehaffy/Development/testComplex/node_modules/mysql/lib/Connection.js:198:25)",
            "    at /home/dmehaffy/Development/testComplex/node_modules/knex/lib/dialects/mysql/index.js:130:18",
            "From previous event:",
            "    at Client_MySQL._query (/home/dmehaffy/Development/testComplex/node_modules/knex/lib/dialects/mysql/index.js:124:12)",
            "    at Client_MySQL.query (/home/dmehaffy/Development/testComplex/node_modules/knex/lib/client.js:158:17)",
            "    at Runner.query (/home/dmehaffy/Development/testComplex/node_modules/knex/lib/runner.js:135:36)",
            "    at /home/dmehaffy/Development/testComplex/node_modules/knex/lib/runner.js:39:23",
            "From previous event:",
            "    at Runner.run (/home/dmehaffy/Development/testComplex/node_modules/knex/lib/runner.js:25:16)",
            "    at Builder.Target.then (/home/dmehaffy/Development/testComplex/node_modules/knex/lib/interface.js:14:43)",
            "    at processImmediate (internal/timers.js:461:21)"
          ]
        }
      }
    }
  ],
  "data": {
    "articles": null
  }
}

REST

[2020-10-13T15:37:14.136Z] error Error: ER_NON_UNIQ_ERROR: Column 'name' in where clause is ambiguous
    at Query.Sequence._packetToError (/home/dmehaffy/Development/testComplex/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
    at Query.ErrorPacket (/home/dmehaffy/Development/testComplex/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
    at Protocol._parsePacket (/home/dmehaffy/Development/testComplex/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/home/dmehaffy/Development/testComplex/node_modules/mysql/lib/protocol/Parser.js:433:10)
    at Parser.write (/home/dmehaffy/Development/testComplex/node_modules/mysql/lib/protocol/Parser.js:43:10)
    at Protocol.write (/home/dmehaffy/Development/testComplex/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket.<anonymous> (/home/dmehaffy/Development/testComplex/node_modules/mysql/lib/Connection.js:88:28)
    at Socket.<anonymous> (/home/dmehaffy/Development/testComplex/node_modules/mysql/lib/Connection.js:526:10)
    at Socket.emit (events.js:314:20)
    at addChunk (_stream_readable.js:298:12)
    at readableAddChunk (_stream_readable.js:273:9)
    at Socket.Readable.push (_stream_readable.js:214:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:188:23)
    at Protocol._enqueue (/home/dmehaffy/Development/testComplex/node_modules/mysql/lib/protocol/Protocol.js:144:48)
    at Connection.query (/home/dmehaffy/Development/testComplex/node_modules/mysql/lib/Connection.js:198:25)
    at /home/dmehaffy/Development/testComplex/node_modules/knex/lib/dialects/mysql/index.js:130:18
From previous event:
    at Client_MySQL._query (/home/dmehaffy/Development/testComplex/node_modules/knex/lib/dialects/mysql/index.js:124:12)
    at Client_MySQL.query (/home/dmehaffy/Development/testComplex/node_modules/knex/lib/client.js:158:17)
    at Runner.query (/home/dmehaffy/Development/testComplex/node_modules/knex/lib/runner.js:135:36)
    at /home/dmehaffy/Development/testComplex/node_modules/knex/lib/runner.js:39:23
From previous event:
    at Runner.run (/home/dmehaffy/Development/testComplex/node_modules/knex/lib/runner.js:25:16)
    at Builder.Target.then (/home/dmehaffy/Development/testComplex/node_modules/knex/lib/interface.js:14:43)
    at processImmediate (internal/timers.js:461:21)
[2020-10-13T15:37:14.137Z] debug GET /articles?_where%5B_or%5D%5B0%5D%5Bname%5D=test&_where%5B_or%5D%5B1%5D%5Bcategory%5D%5Bname%5D=test (22 ms) 500

[lauriejim]

This issue has been mentioned on Strapi Community. There might be relevant details there:

https://forum.strapi.io/t/strapi-graphql-or-operator-for-where-clause/410/2