For SQL databases treat undefined values in FindConditions the same as values equal to the FindOperator returned from IsNull()

[brokenthorn]

Issue type:

[X] feature request

Database system/driver:

[X] mssql

TypeORM version:

[X] latest

As pointed out in this answer on StackOverflow, it would be great if FindConditions where the field is declared as undefined would generate IS NULL SQL code, instead of equals to NULL code = NULL. This is currently achievable only if the find condition is declared as IsNull() (returning a FindOperator).

For reference, here is the original post on StackOverflow (I admit, I am the poster):

I really don't like to have to use the QueryBuilder from TypeORM for this as this should, in my opinion, be treated as expected when using FindConditions.

Unfortunately, with something like the following code:

async articleRequests(
  accepted?: ArticleRequestAcceptance,
): Promise<ArticleRequest[]> {
  const where: FindConditions<ArticleRequest>[] | FindConditions<ArticleRequest> = {};

  if (accepted !== undefined) {
    switch (accepted) {
      case ArticleRequestAcceptance.Accepted:
        where.accepted = true;
        break;
      case ArticleRequestAcceptance.Rejected:
        where.accepted = false;
        break;
      case ArticleRequestAcceptance.NotReviewedYet:
        where.accepted = undefined;
        break;
    }
  }

  return await ArticleRequest.find({ where }).catch(reason => {
    throw reason.message;
  });
}

TypeORM gets you a SQL query that looks like this:

SELECT '...' WHERE "ArticleRequest"."accepted" = NULL

because, as can be seen from TypeORM log output, ... WHERE "ArticleRequest"."accepted" = @0 -- PARAMETERS: [null], properties with undefined values (accepted in this case) get converted to nulls inside the parameters array and then they are simply injected into the SQL string.

The SQL standard says that any comparison with null results in null so for comparison operators, like = or <>, in SQL this should make no sense, but the reasoning is that comparing to null means "unknown" so that why such queries don't return any results. If you ask me, SQL is broken here.

So yeah, as @hungneox said, the solution is to use IsNull() which returns a special FindOperator for that specific column you need to be queried as IS NULL and not = NULL.

Like this:

  if (accepted !== undefined) {
    switch (accepted) {
      case ArticleRequestAcceptance.Accepted:
        where.accepted = true;
        break;
      case ArticleRequestAcceptance.Rejected:
        where.accepted = false;
        break;
      case ArticleRequestAcceptance.NotReviewedYet:
        where.accepted = IsNull();
        break;
    }
  }

[dsbert]

It seems surprising to me that where.accepted = undefined; is even resulting in a filter operation instead of that field being dropped.

[brokenthorn]

It shouldn't be dropped because a boolean SQL column that is nullable is basically a tristate boolean: true, false, neither. In my case the business logic is that a request is either accepted, rejected or not reviewed yet, all states being handled by the same variable/column.

Using FindConditionsInstance.column = IsNull() instead of FindConditionsInstance.column = null seems counterintuitive to me.

Sorry, I just realized that in JS, having a property equal undefined, is equivalent to the property not even existing in the first place (ie. undefined, duuh!).

Now the question becomes "Does using = null instead of = undefined work as expected?". I will have to try. Depending on the results, this issue might have been a stupid mistake on my part...

[imnotjames]

Duplicate of #3416