Filtering tickets by category using "is not" criteria dosen't return items with undefined value

[petertuharsky]

Code of Conduct

• I agree to follow this project's Code of Conduct

Is there an existing issue for this?

• I have searched the existing issues

Version

10.0.18

Bug description

When searching tickets by using filter with "Category - is not" criteria, with multiple "and" rows, the resulting list dosen't take in account the tickets where Category is not set.
Logically, they should get listed because they match criteria.

The filter looks like:

Relevant log output

Page URL

No response

Steps To reproduce

1. Go to Support - Tickets
2. Define filter with multiple criteria "Category - is not"
3. Results should list all tickets fulfilling the criteria
4. In reality, tickets without Category are not listed.

Your GLPI setup information

No response

Anything else?

No response

[cconard96]

This probably works this way for most dropdown type search options.

The category search option uses the glpi_itilcategories table to use the complete name in other search types like contains, so the resulting SQL criteria is like:

AND (`glpi_itilcategories`.`id` NOT IN ('1', '2'))

Since there obviously isn't a joined record for the missing category, glpi_itilcategories.id is NULL. MySQL and other DBMS consider it an unknown value and therefore resolves something like NULL <> 1 and NULL = 1 both as false. The NOT IN is equivalent to NULL <> 1 AND NULL <> 2.

"The result of any arithmetic comparison with NULL is also NULL"
https://dev.mysql.com/doc/refman/8.4/en/working-with-null.html