glpi icon indicating copy to clipboard operation
glpi copied to clipboard
verified publisher icon glpi-project

[10.0.2] A search for a ticket technician with "IS NOT" doesn't give correct results

Open tomolimo opened this issue 3 years ago • 3 comments

Code of Conduct

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

Is there an existing issue for this?

  • [X] I have searched the existing issues

Version

10.0.2

Bug description

Hello, When searching tickets for which a tech is not assigned, then resuslt list is wrong. Thank you, Regards, Tomolimo

Relevant log output

No error in logs

Page URL

/front/ticket.php

Steps To reproduce

  1. In an empty GLPI 10.0.2 instance
  2. Create two tickets: assign Ticket 1 to tech and glpi users and assign Ticket 2 to tech only image
  3. In ticket list, query the tickets where Technician is not glpi user
  4. in the ticket list both tickets will appear (even the one which contains glpi user as Tech) image

Your GLPI setup information

Information about system installation and configuration
GLPI 10.0.2 ( => L:\inetpub\wwwroot\glpi10.0-official)
Installation mode: TARBALL
Current language:en_GB
Server
 
Operating system: Windows NT ARGLPIT20 10.0 build 14393 (Windows Server 2016) AMD64
PHP 8.1.7 cgi-fcgi (Core, PDO, Phar, Reflection, SPL, SimpleXML, Zend OPcache, bcmath, bz2, calendar, cgi-fcgi, ctype, curl,
	date, dom, exif, fileinfo, filter, gd, hash, iconv, intl, json, ldap, libxml, mbstring, mysqli, mysqlnd, openssl, pcre,
	readline, redis, session, soap, sodium, standard, tokenizer, xml, xmlreader, xmlwriter, xsl, zip, zlib)
Setup: max_execution_time="300" memory_limit="1G" post_max_size="150M" safe_mode="" session.save_handler="files"
	upload_max_filesize="100M" 
Software: Microsoft-IIS/10.0
	Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/104.0.0.0 Safari/537.36
Server Software: MySQL Community Server - GPL
	Server Version: 8.0.29
	Server SQL Mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
	Parameters: [email protected]:3307/glpi10.0official
	Host info: 127.0.0.1 via TCP/IP

PHP version (8.1.7) is supported.
Sessions configuration is OK.
Allocated memory is sufficient.
mysqli extension is installed.
Following extensions are installed: dom, fileinfo, json, simplexml.
curl extension is installed.
gd extension is installed.
intl extension is installed.
libxml extension is installed.
zlib extension is installed.
The constant SODIUM_CRYPTO_AEAD_XCHACHA20POLY1305_IETF_NPUBBYTES is present.
Database engine version (8.0.29) is supported.
The log file has been created successfully.
Write access to L:\inetpub\wwwroot\glpi10.0-official/files/_cache has been validated.
Write access to L:\inetpub\wwwroot\glpi10.0-official/config has been validated.
Write access to L:\inetpub\wwwroot\glpi10.0-official/files/_cron has been validated.
Write access to L:\inetpub\wwwroot\glpi10.0-official/files has been validated.
Write access to L:\inetpub\wwwroot\glpi10.0-official/files/_dumps has been validated.
Write access to L:\inetpub\wwwroot\glpi10.0-official/files/_graphs has been validated.
Write access to L:\inetpub\wwwroot\glpi10.0-official/files/_lock has been validated.
Write access to L:\inetpub\wwwroot\glpi10.0-official/files/_pictures has been validated.
Write access to L:\inetpub\wwwroot\glpi10.0-official/files/_plugins has been validated.
Write access to L:\inetpub\wwwroot\glpi10.0-official/files/_rss has been validated.
Write access to L:\inetpub\wwwroot\glpi10.0-official/files/_sessions has been validated.
Write access to L:\inetpub\wwwroot\glpi10.0-official/files/_tmp has been validated.
Write access to L:\inetpub\wwwroot\glpi10.0-official/files/_uploads has been validated.
Web access to the files directory should not be allowed but this cannot be checked automatically on this instance.
Make sure access to error log file (/files/_log/php-errors.log) is forbidden; otherwise review .htaccess file and web server configuration.
exif extension is installed.
ldap extension is installed.
openssl extension is installed.
zip extension is installed.
bz2 extension is installed.
Zend OPcache extension is installed.
Following extensions are installed: ctype, iconv, mbstring, sodium.
Write access to L:\inetpub\wwwroot\glpi10.0-official/marketplace has been validated.
Timezones seems loaded in database.
GLPI constants
 
GLPI_ROOT: L:\inetpub\wwwroot\glpi10.0-official
GLPI_CONFIG_DIR: L:\inetpub\wwwroot\glpi10.0-official/config
GLPI_VAR_DIR: L:\inetpub\wwwroot\glpi10.0-official/files
GLPI_MARKETPLACE_DIR: L:\inetpub\wwwroot\glpi10.0-official/marketplace
GLPI_USE_CSRF_CHECK: 1
GLPI_CSRF_EXPIRES: 7200
GLPI_CSRF_MAX_TOKENS: 100
GLPI_USE_IDOR_CHECK: 1
GLPI_IDOR_EXPIRES: 7200
GLPI_ALLOW_IFRAME_IN_RICH_TEXT: 
GLPI_TELEMETRY_URI: https://telemetry.glpi-project.org
GLPI_INSTALL_MODE: TARBALL
GLPI_NETWORK_MAIL: [email protected]
GLPI_NETWORK_SERVICES: https://services.glpi-network.com
GLPI_MARKETPLACE_ALLOW_OVERRIDE: 1
GLPI_MARKETPLACE_MANUAL_DOWNLOADS: 1
GLPI_USER_AGENT_EXTRA_COMMENTS: 
GLPI_DISABLE_ONLY_FULL_GROUP_BY_SQL_MODE: 1
GLPI_AJAX_DASHBOARD: 1
GLPI_CALDAV_IMPORT_STATE: 0
GLPI_DEMO_MODE: 0
GLPI_CENTRAL_WARNINGS: 1
GLPI_DOC_DIR: L:\inetpub\wwwroot\glpi10.0-official/files
GLPI_CACHE_DIR: L:\inetpub\wwwroot\glpi10.0-official/files/_cache
GLPI_CRON_DIR: L:\inetpub\wwwroot\glpi10.0-official/files/_cron
GLPI_DUMP_DIR: L:\inetpub\wwwroot\glpi10.0-official/files/_dumps
GLPI_GRAPH_DIR: L:\inetpub\wwwroot\glpi10.0-official/files/_graphs
GLPI_LOCAL_I18N_DIR: L:\inetpub\wwwroot\glpi10.0-official/files/_locales
GLPI_LOCK_DIR: L:\inetpub\wwwroot\glpi10.0-official/files/_lock
GLPI_LOG_DIR: L:\inetpub\wwwroot\glpi10.0-official/files/_log
GLPI_PICTURE_DIR: L:\inetpub\wwwroot\glpi10.0-official/files/_pictures
GLPI_PLUGIN_DOC_DIR: L:\inetpub\wwwroot\glpi10.0-official/files/_plugins
GLPI_RSS_DIR: L:\inetpub\wwwroot\glpi10.0-official/files/_rss
GLPI_SESSION_DIR: L:\inetpub\wwwroot\glpi10.0-official/files/_sessions
GLPI_TMP_DIR: L:\inetpub\wwwroot\glpi10.0-official/files/_tmp
GLPI_UPLOAD_DIR: L:\inetpub\wwwroot\glpi10.0-official/files/_uploads
GLPI_INVENTORY_DIR: L:\inetpub\wwwroot\glpi10.0-official/files/_inventories
GLPI_NETWORK_REGISTRATION_API_URL: https://services.glpi-network.com/api/registration/
GLPI_MARKETPLACE_PLUGINS_API_URI: https://services.glpi-network.com/api/glpi-plugins/
GLPI_I18N_DIR: L:\inetpub\wwwroot\glpi10.0-official/locales
GLPI_VERSION: 10.0.2
GLPI_SCHEMA_VERSION: 10.0.2@a130db99c7d9b131c2e2ea59fe0d6260fe93d831
GLPI_MARKETPLACE_PRERELEASES: 
GLPI_MIN_PHP: 7.4.0
GLPI_MAX_PHP: 8.2.0
GLPI_YEAR: 2022
Libraries
 
htmlawed/htmlawed version 1.2.6 in (L:\inetpub\wwwroot\glpi10.0-official\vendor\htmlawed\htmlawed)
phpmailer/phpmailer version 6.6.0 in (L:\inetpub\wwwroot\glpi10.0-official\vendor\phpmailer\phpmailer\src)
simplepie/simplepie version 1.5.8 in (L:\inetpub\wwwroot\glpi10.0-official\vendor\simplepie\simplepie\library)
tecnickcom/tcpdf version 6.4.4 in (L:\inetpub\wwwroot\glpi10.0-official\vendor\tecnickcom\tcpdf)
michelf/php-markdown in (L:\inetpub\wwwroot\glpi10.0-official\vendor\michelf\php-markdown\Michelf)
true/punycode in (L:\inetpub\wwwroot\glpi10.0-official\vendor\true\punycode\src)
iamcal/lib_autolink in (L:\inetpub\wwwroot\glpi10.0-official\vendor\iamcal\lib_autolink)
sabre/dav in (L:\inetpub\wwwroot\glpi10.0-official\vendor\sabre\dav\lib\DAV)
sabre/http in (L:\inetpub\wwwroot\glpi10.0-official\vendor\sabre\http\lib)
sabre/uri in (L:\inetpub\wwwroot\glpi10.0-official\vendor\sabre\uri\lib)
sabre/vobject in (L:\inetpub\wwwroot\glpi10.0-official\vendor\sabre\vobject\lib)
laminas/laminas-i18n in (L:\inetpub\wwwroot\glpi10.0-official\vendor\laminas\laminas-i18n\src)
laminas/laminas-servicemanager in (L:\inetpub\wwwroot\glpi10.0-official\vendor\laminas\laminas-servicemanager\src)
monolog/monolog in (L:\inetpub\wwwroot\glpi10.0-official\vendor\monolog\monolog\src\Monolog)
sebastian/diff in (L:\inetpub\wwwroot\glpi10.0-official\vendor\sebastian\diff\src)
donatj/phpuseragentparser in (L:\inetpub\wwwroot\glpi10.0-official\vendor\donatj\phpuseragentparser\src\UserAgent)
elvanto/litemoji in (L:\inetpub\wwwroot\glpi10.0-official\vendor\elvanto\litemoji\src)
symfony/console in (L:\inetpub\wwwroot\glpi10.0-official\vendor\symfony\console)
scssphp/scssphp in (L:\inetpub\wwwroot\glpi10.0-official\vendor\scssphp\scssphp\src)
laminas/laminas-mail in (L:\inetpub\wwwroot\glpi10.0-official\vendor\laminas\laminas-mail\src\Protocol)
laminas/laminas-mime in (L:\inetpub\wwwroot\glpi10.0-official\vendor\laminas\laminas-mime\src)
rlanvin/php-rrule in (L:\inetpub\wwwroot\glpi10.0-official\vendor\rlanvin\php-rrule\src)
blueimp/jquery-file-upload in (L:\inetpub\wwwroot\glpi10.0-official\vendor\blueimp\jquery-file-upload\server\php)
ramsey/uuid in (L:\inetpub\wwwroot\glpi10.0-official\vendor\ramsey\uuid\src)
psr/log in (L:\inetpub\wwwroot\glpi10.0-official\vendor\psr\log\Psr\Log)
psr/simple-cache in (L:\inetpub\wwwroot\glpi10.0-official\vendor\psr\simple-cache\src)
psr/cache in (L:\inetpub\wwwroot\glpi10.0-official\vendor\psr\cache\src)
league/csv in (L:\inetpub\wwwroot\glpi10.0-official\vendor\league\csv\src)
mexitek/phpcolors in (L:\inetpub\wwwroot\glpi10.0-official\vendor\mexitek\phpcolors\src\Mexitek\PHPColors)
guzzlehttp/guzzle in (L:\inetpub\wwwroot\glpi10.0-official\vendor\guzzlehttp\guzzle\src)
guzzlehttp/psr7 in (L:\inetpub\wwwroot\glpi10.0-official\vendor\guzzlehttp\psr7\src)
glpi-project/inventory_format in (L:\inetpub\wwwroot\glpi10.0-official\vendor\glpi-project\inventory_format\lib\php)
wapmorgan/unified-archive in (L:\inetpub\wwwroot\glpi10.0-official\vendor\wapmorgan\unified-archive\src)
paragonie/sodium_compat in (L:\inetpub\wwwroot\glpi10.0-official\vendor\paragonie\sodium_compat\src)
symfony/cache in (L:\inetpub\wwwroot\glpi10.0-official\vendor\symfony\cache)
html2text/html2text in (L:\inetpub\wwwroot\glpi10.0-official\vendor\html2text\html2text\src)
symfony/css-selector in (L:\inetpub\wwwroot\glpi10.0-official\vendor\symfony\css-selector)
symfony/dom-crawler in (L:\inetpub\wwwroot\glpi10.0-official\vendor\symfony\dom-crawler)
twig/twig in (L:\inetpub\wwwroot\glpi10.0-official\vendor\twig\twig\src)
twig/string-extra in (L:\inetpub\wwwroot\glpi10.0-official\vendor\twig\string-extra)
symfony/polyfill-ctype not found
symfony/polyfill-iconv not found
symfony/polyfill-mbstring not found
symfony/polyfill-php80 not found
symfony/polyfill-php81 not found
SQL replicas
 
Not active
Notifications
 
Way of sending emails: PHP
Plugins list
 
	advancedplanning     Name: advancedplanning               Version: 1.1.0      State: Enabled                                 
		Install Method: Marketplace

Anything else?

This issue is already existing in GLPI 9.5 releases This issue is also present when requesting for a Watcher I made tests also with the "AND NOT", and with the "not contains" and results are identical to the query for the Technician (= wrong results).

tomolimo avatar Aug 18 '22 08:08 tomolimo

The search that gives closer to the expected results is: NOT Assigned to - Technician is "glpi" The way the SQL gets built for the other search looks for any tickets that have any technician that is not "glpi" rather than searching for tickets without "glpi" technician.

For both searches, tickets without anyone assigned are excluded.

cconard96 avatar Aug 18 '22 09:08 cconard96

Hello @cconard96 Like explained, it has been tested, and it's giving the same wrong results :( image

Thank you Regards, Tomolimo

tomolimo avatar Aug 18 '22 09:08 tomolimo

Hello @cconard96 Just to give an example:

Here is the search with the "AND + IS NOT" image And the SQL request:

SELECT DISTINCT `glpi_tickets`.`id` AS id, 'glpi' AS currentuser, `glpi_tickets`.`id` AS `ITEM_Ticket_2`, `glpi_tickets`.`name` AS `ITEM_Ticket_1`, `glpi_tickets`.`id` AS `ITEM_Ticket_1_id`, `glpi_tickets`.`id` AS `ITEM_Ticket_1_id`, `glpi_tickets`.`content` AS `ITEM_Ticket_1_content`, `glpi_tickets`.`status` AS `ITEM_Ticket_1_status`, `glpi_entities`.`completename` AS `ITEM_Ticket_80`, `glpi_tickets`.`status` AS `ITEM_Ticket_12`, `glpi_tickets`.`date_mod` AS `ITEM_Ticket_19`, `glpi_tickets`.`date` AS `ITEM_Ticket_15`, `glpi_tickets`.`priority` AS `ITEM_Ticket_3`, GROUP_CONCAT(DISTINCT `glpi_users_af1042e23ce6565cfe58c6db91f84692`.`id` SEPARATOR '$$##$$') AS `ITEM_Ticket_4`, GROUP_CONCAT(DISTINCT CONCAT(`glpi_tickets_users_019878060c6d5f06cbe3c4d7c31dec24`.`users_id`, ' ', `glpi_tickets_users_019878060c6d5f06cbe3c4d7c31dec24`.`alternative_email`) SEPARATOR '$$##$$') AS `ITEM_Ticket_4_2`, GROUP_CONCAT(DISTINCT `glpi_users_b1b92f6be5e70531688d870931e94a65`.`id` SEPARATOR '$$##$$') AS `ITEM_Ticket_5`, GROUP_CONCAT(DISTINCT CONCAT(`glpi_tickets_users_c6f7f58b9365babf7388e555e526efd5`.`users_id`, ' ', `glpi_tickets_users_c6f7f58b9365babf7388e555e526efd5`.`alternative_email`) SEPARATOR '$$##$$') AS `ITEM_Ticket_5_2`, `glpi_itilcategories`.`completename` AS `ITEM_Ticket_7`, `glpi_tickets`.`time_to_resolve` AS `ITEM_Ticket_18`, `glpi_tickets`.`solvedate` AS `ITEM_Ticket_18_solvedate`, `glpi_tickets`.`status` AS `ITEM_Ticket_18_status`
FROM `glpi_tickets`
LEFT JOIN `glpi_entities` ON (`glpi_tickets`.`entities_id` = `glpi_entities`.`id` )
LEFT JOIN `glpi_tickets_users` AS `glpi_tickets_users_019878060c6d5f06cbe3c4d7c31dec24` ON (`glpi_tickets`.`id` = `glpi_tickets_users_019878060c6d5f06cbe3c4d7c31dec24`.`tickets_id` AND `glpi_tickets_users_019878060c6d5f06cbe3c4d7c31dec24`.`type` = '1' )
LEFT JOIN `glpi_users` AS `glpi_users_af1042e23ce6565cfe58c6db91f84692` ON (`glpi_tickets_users_019878060c6d5f06cbe3c4d7c31dec24`.`users_id` = `glpi_users_af1042e23ce6565cfe58c6db91f84692`.`id` )
LEFT JOIN `glpi_tickets_users` AS `glpi_tickets_users_c6f7f58b9365babf7388e555e526efd5` ON (`glpi_tickets`.`id` = `glpi_tickets_users_c6f7f58b9365babf7388e555e526efd5`.`tickets_id` AND `glpi_tickets_users_c6f7f58b9365babf7388e555e526efd5`.`type` = '2' )
LEFT JOIN `glpi_users` AS `glpi_users_b1b92f6be5e70531688d870931e94a65` ON (`glpi_tickets_users_c6f7f58b9365babf7388e555e526efd5`.`users_id` = `glpi_users_b1b92f6be5e70531688d870931e94a65`.`id` )
LEFT JOIN `glpi_itilcategories` ON (`glpi_tickets`.`itilcategories_id` = `glpi_itilcategories`.`id` )
WHERE `glpi_tickets`.`is_deleted` = 0 AND ( (`glpi_tickets`.`name` LIKE '%ticket%' ) AND (`glpi_users_b1b92f6be5e70531688d870931e94a65`.`id` <> '2') ) GROUP BY `glpi_tickets`.`id`
ORDER BY `ITEM_Ticket_19` DESC

Here is the search with the "AND NOT + IS" image And the SQL query

SELECT DISTINCT `glpi_tickets`.`id` AS id, 'glpi' AS currentuser, `glpi_tickets`.`id` AS `ITEM_Ticket_2`, `glpi_tickets`.`name` AS `ITEM_Ticket_1`, `glpi_tickets`.`id` AS `ITEM_Ticket_1_id`, `glpi_tickets`.`id` AS `ITEM_Ticket_1_id`, `glpi_tickets`.`content` AS `ITEM_Ticket_1_content`, `glpi_tickets`.`status` AS `ITEM_Ticket_1_status`, `glpi_entities`.`completename` AS `ITEM_Ticket_80`, `glpi_tickets`.`status` AS `ITEM_Ticket_12`, `glpi_tickets`.`date_mod` AS `ITEM_Ticket_19`, `glpi_tickets`.`date` AS `ITEM_Ticket_15`, `glpi_tickets`.`priority` AS `ITEM_Ticket_3`, GROUP_CONCAT(DISTINCT `glpi_users_af1042e23ce6565cfe58c6db91f84692`.`id` SEPARATOR '$$##$$') AS `ITEM_Ticket_4`, GROUP_CONCAT(DISTINCT CONCAT(`glpi_tickets_users_019878060c6d5f06cbe3c4d7c31dec24`.`users_id`, ' ', `glpi_tickets_users_019878060c6d5f06cbe3c4d7c31dec24`.`alternative_email`) SEPARATOR '$$##$$') AS `ITEM_Ticket_4_2`, GROUP_CONCAT(DISTINCT `glpi_users_b1b92f6be5e70531688d870931e94a65`.`id` SEPARATOR '$$##$$') AS `ITEM_Ticket_5`, GROUP_CONCAT(DISTINCT CONCAT(`glpi_tickets_users_c6f7f58b9365babf7388e555e526efd5`.`users_id`, ' ', `glpi_tickets_users_c6f7f58b9365babf7388e555e526efd5`.`alternative_email`) SEPARATOR '$$##$$') AS `ITEM_Ticket_5_2`, `glpi_itilcategories`.`completename` AS `ITEM_Ticket_7`, `glpi_tickets`.`time_to_resolve` AS `ITEM_Ticket_18`, `glpi_tickets`.`solvedate` AS `ITEM_Ticket_18_solvedate`, `glpi_tickets`.`status` AS `ITEM_Ticket_18_status`
FROM `glpi_tickets`
LEFT JOIN `glpi_entities` ON (`glpi_tickets`.`entities_id` = `glpi_entities`.`id` )
LEFT JOIN `glpi_tickets_users` AS `glpi_tickets_users_019878060c6d5f06cbe3c4d7c31dec24` ON (`glpi_tickets`.`id` = `glpi_tickets_users_019878060c6d5f06cbe3c4d7c31dec24`.`tickets_id` AND `glpi_tickets_users_019878060c6d5f06cbe3c4d7c31dec24`.`type` = '1' )
LEFT JOIN `glpi_users` AS `glpi_users_af1042e23ce6565cfe58c6db91f84692` ON (`glpi_tickets_users_019878060c6d5f06cbe3c4d7c31dec24`.`users_id` = `glpi_users_af1042e23ce6565cfe58c6db91f84692`.`id` )
LEFT JOIN `glpi_tickets_users` AS `glpi_tickets_users_c6f7f58b9365babf7388e555e526efd5` ON (`glpi_tickets`.`id` = `glpi_tickets_users_c6f7f58b9365babf7388e555e526efd5`.`tickets_id` AND `glpi_tickets_users_c6f7f58b9365babf7388e555e526efd5`.`type` = '2' )
LEFT JOIN `glpi_users` AS `glpi_users_b1b92f6be5e70531688d870931e94a65` ON (`glpi_tickets_users_c6f7f58b9365babf7388e555e526efd5`.`users_id` = `glpi_users_b1b92f6be5e70531688d870931e94a65`.`id` )
LEFT JOIN `glpi_itilcategories` ON (`glpi_tickets`.`itilcategories_id` = `glpi_itilcategories`.`id` )
WHERE `glpi_tickets`.`is_deleted` = 0 AND ( (`glpi_tickets`.`name` LIKE '%ticket%' ) AND (`glpi_users_b1b92f6be5e70531688d870931e94a65`.`id` <> '2') ) GROUP BY `glpi_tickets`.`id`
ORDER BY `ITEM_Ticket_19` DESC

as you can see, the queries are strictly identical... image

Thank you, Regards, Tomolimo

tomolimo avatar Aug 29 '22 08:08 tomolimo