Prisma1: Query by empty (nullable 1:1) relation always return empty result set.

[zone42]

Describe the bug
Query by empty (nullable 1:1) relation always return empty result set.

To Reproduce
Datamodel:

type Message {
  id: ID! @id
  image: Image @relation(link: INLINE, name: "MessageImageRelation") # NOT mandatory
  ...
}

type Image {
  id: ID! @id
  message: Message @relation(name: "MessageImageRelation")
  ...
}

Query for unused images (images witch hasn't relation to message table) always return empty result set:

query {
  images(where: { message: null }) {
    id
  }
}

Expected behavior
Return all rows where relation is null

Just a guess
I don't know what prisma does under hood, and logging queries still not work (#2833), I guess the mysql query should look something like that:

SELECT id FROM `default@default`.`Image` WHERE id NOT IN (
  SELECT distinct image FROM `default@default`.`Message` WHERE image IS NOT NULL
)

Important: For nullable 1:1 relations subquery must contain WHERE image IS NOT NULL clause. Without that condition result set is always empty.

Versions:

• Connector: MySQL
• Prisma Server: 1.34.3
• prisma CLI: prisma/1.34.3 (windows-x64) node-v10.15.3
• OS: Windows 10

[grimunit]

@kermiv  @pantharshit00 This is the issue that we were having. The issue we were talking to you in Slack about. This is also happening with 1:Many relations.

We found out that the issue is when the relation is an INLINE relation, when you switch it back to a TABLE relation the result set comes back correctly.

Also we looked at our data, and there was definitely data in both the foreign key column when it was INLINE and data in the relation table when it was TABLE