Query builder WHERE does not convert != to NOT NULL in case if parameter value is null #6008

New Issue

admin · 2026-01-22T15:24:40+01:00

admin commented

2026-01-22 15:24:40 +01:00

Originally created by @Arkemlar on GitHub (Jul 2, 2018).

Originally assigned to: @Ocramius on GitHub.

Bug Report

Q	A
BC Break	dont know
Version	v2.6.1 (doctrine/orm)

Summary

Using MYSQL. Imagine simple query:

$qb
  ->andWhere('entity.id != :value')
  ->setParameter('value', $someValue);

If $someValue is int - then OK, but if NULL - then query executes and returns 0 rows. Thats because doctrine builds query like WHERE entity.id <> NULL. I expected that != will be converted to IS NOT.

Current behavior

!= converts to <> if NULL passed as parameter value.

How to reproduce

// init some query builder
$qb
  ->andWhere('entity.id != :value')
  ->setParameter('value', null);
// execute query and check generated SQL

Expected behavior

!= converts to IS NOT if NULL passed as parameter value.

Originally created by @Arkemlar on GitHub (Jul 2, 2018). Originally assigned to: @Ocramius on GitHub. ### Bug Report  | Q | A |------------ | ------ | BC Break | dont know | Version | v2.6.1 (doctrine/orm) #### Summary Using MYSQL. Imagine simple query: ``` $qb ->andWhere('entity.id != :value') ->setParameter('value', $someValue); ``` If `$someValue` is int - then OK, but if NULL - then query executes and returns 0 rows. Thats because doctrine builds query like `WHERE entity.id <> NULL`. I expected that `!=` will be converted to `IS NOT`. #### Current behavior `!=` converts to `<>` if NULL passed as parameter value. #### How to reproduce ``` // init some query builder $qb ->andWhere('entity.id != :value') ->setParameter('value', null); // execute query and check generated SQL ``` #### Expected behavior `!=` converts to `IS NOT` if NULL passed as parameter value.

admin added the Bug Invalid Question labels 2026-01-22 15:24:40 +01:00

admin closed this issue

2026-01-22 15:24:41 +01:00

admin commented

2026-01-22 15:24:41 +01:00

@Ocramius commented on GitHub (Jul 3, 2018):

This is correct: in SQL and DQL, null comparison needs to be explicitly defined with IS NULL or IS NOT NULL.

Closing as invalid

@Ocramius commented on GitHub (Jul 3, 2018): This is correct: in SQL and DQL, `null` comparison needs to be explicitly defined with `IS NULL` or `IS NOT NULL`. Closing as `invalid`

admin commented

2026-01-22 15:24:42 +01:00

@parijke commented on GitHub (Feb 14, 2021):

How to handle this situation? I ran into the same issue, where a parameter can be null. but != null obviousely returns the worng result?

@parijke commented on GitHub (Feb 14, 2021): How to handle this situation? I ran into the same issue, where a parameter can be null. but != null obviousely returns the worng result?

admin commented

2026-01-22 15:24:42 +01:00

@Adamko23 commented on GitHub (Aug 10, 2022):

omg, really is it that hard make the conversion in doctrine? Cause programmer must do this disgusting thing

if(is_null($value)){
    $qb->andWhere('entity.id IS NULL');
} else { 
    $qb->andWhere('entity.id != :value')->setParameter('value', $value);
}

@Adamko23 commented on GitHub (Aug 10, 2022): omg, really is it that hard make the conversion in doctrine? Cause programmer must do this disgusting thing ``` if(is_null($value)){ $qb->andWhere('entity.id IS NULL'); } else { $qb->andWhere('entity.id != :value')->setParameter('value', $value); } ```

admin commented

2026-01-22 15:24:42 +01:00

@Ocramius commented on GitHub (Aug 10, 2022):

Yes, it is part of the SQL standard: IS NULL is the correct way to do this.

Setting parameter values also doesn't modify the SQL/DQL structure pre-defined before: it's too late to modify a prepared statement while parameters are being bound to it.

omg, really is it that hard make the conversion in doctrine?

Yes, really.

@Ocramius commented on GitHub (Aug 10, 2022): Yes, it is part of the SQL standard: `IS NULL` is the correct way to do this. Setting parameter values also doesn't modify the SQL/DQL structure pre-defined before: it's too late to modify a prepared statement while parameters are being bound to it. > omg, really is it that hard make the conversion in doctrine? Yes, really.

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#6008