specific filter in tickets gives SQL error

InformatiqueOLLN commented 5 years ago

Describe the bug

For a specific search in tickets, I get no results and an SQL error.

Page(s) URL

To reproduce

Steps to reproduce the behavior:

Set the search filter as the link above
Search

Expected behavior Getting the list

Logs SQL Error :

SQL: SELECT DISTINCT glpi_tickets.id AS id, 'bertrandc' 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, GROUP_CONCAT(DISTINCT glpi_users_a5e2026cbcb683e774f93a59e24a62a4.id SEPARATOR '$$##$$') AS ITEM_Ticket_4, GROUP_CONCAT(DISTINCT CONCAT(glpi_tickets_users_d1524bb2ea1d461ab90aed3b5f0e7e60.users_id, ' ', glpi_tickets_users_d1524bb2ea1d461ab90aed3b5f0e7e60.alternative_email) SEPARATOR '$$##$$') AS ITEM_Ticket_4_2, glpi_tickets.status AS ITEM_Ticket_12, COUNT(DISTINCT glpi_itilfollowups.id) AS ITEM_Ticket_27, glpi_tickets.date AS ITEM_Ticket_15, glpi_tickets.date_mod AS ITEM_Ticket_19, GROUP_CONCAT(DISTINCT glpi_users_f201be21cd638ee780d08cba4ceff0d4.id SEPARATOR '$$##$$') AS ITEM_Ticket_5, GROUP_CONCAT(DISTINCT CONCAT(glpi_tickets_users_819efb92c8b927b345e489211ec8e43b.users_id, ' ', glpi_tickets_users_819efb92c8b927b345e489211ec8e43b.alternative_email) SEPARATOR '$$##$$') AS ITEM_Ticket_5_2, glpi_tickets.priority AS ITEM_Ticket_3, glpi_tickets.time_to_resolve AS ITEM_Ticket_18, glpi_tickets.status AS ITEM_Ticket_18_status, glpi_itilcategories.completename AS ITEM_Ticket_7, glpi_locations.completename AS ITEM_Ticket_83, GROUP_CONCAT(DISTINCT CONCAT(IFNULL(glpi_groups_1358be41ea7158319a888236ffba5355.completename, 'NULL'), '$#$',glpi_groups_1358be41ea7158319a888236ffba5355.id)ORDER BY glpi_groups_1358be41ea7158319a888236ffba5355.id SEPARATOR '$$##$$') AS ITEM_Ticket_8, GROUP_CONCAT(DISTINCT CONCAT(IFNULL(glpi_tickettasks.state, 'NULL'), '$#$',glpi_tickettasks.id)ORDER BY glpi_tickettasks.id SEPARATOR '$$##$$') AS ITEM_Ticket_33, GROUP_CONCAT(DISTINCT CONCAT(glpi_groups_groups_id_tech_e5ca2f53018fdc28a31faf534186b3a2.completename, '$#$' , glpi_groups_groups_id_tech_e5ca2f53018fdc28a31faf534186b3a2.id)ORDER BY glpi_groups_groups_id_tech_e5ca2f53018fdc28a31faf534186b3a2.id SEPARATOR '$$##$$') AS ITEM_Ticket_112FROM glpi_ticketsLEFT JOIN glpi_tickets_users AS glpi_tickets_users_d1524bb2ea1d461ab90aed3b5f0e7e60 ON (glpi_tickets.id = glpi_tickets_users_d1524bb2ea1d461ab90aed3b5f0e7e60.tickets_id AND glpi_tickets_users_d1524bb2ea1d461ab90aed3b5f0e7e60.type = 1 )LEFT JOIN glpi_users AS glpi_users_a5e2026cbcb683e774f93a59e24a62a4 ON (glpi_tickets_users_d1524bb2ea1d461ab90aed3b5f0e7e60.users_id = glpi_users_a5e2026cbcb683e774f93a59e24a62a4.id )LEFT JOIN glpi_itilfollowups ON (glpi_tickets.id = glpi_itilfollowups.items_id AND glpi_itilfollowups.itemtype = 'Ticket' )LEFT JOIN glpi_tickets_users AS glpi_tickets_users_819efb92c8b927b345e489211ec8e43b ON (glpi_tickets.id = glpi_tickets_users_819efb92c8b927b345e489211ec8e43b.tickets_id AND glpi_tickets_users_819efb92c8b927b345e489211ec8e43b.type = 2 )LEFT JOIN glpi_users AS glpi_users_f201be21cd638ee780d08cba4ceff0d4 ON (glpi_tickets_users_819efb92c8b927b345e489211ec8e43b.users_id = glpi_users_f201be21cd638ee780d08cba4ceff0d4.id )LEFT JOIN glpi_itilcategories ON (glpi_tickets.itilcategories_id = glpi_itilcategories.id )LEFT JOIN glpi_locations ON (glpi_tickets.locations_id = glpi_locations.id )LEFT JOIN glpi_groups_tickets AS glpi_groups_tickets_819efb92c8b927b345e489211ec8e43b ON (glpi_tickets.id = glpi_groups_tickets_819efb92c8b927b345e489211ec8e43b.tickets_id AND glpi_groups_tickets_819efb92c8b927b345e489211ec8e43b.type = 2 )LEFT JOIN glpi_groups AS glpi_groups_1358be41ea7158319a888236ffba5355 ON (glpi_groups_tickets_819efb92c8b927b345e489211ec8e43b.groups_id = glpi_groups_1358be41ea7158319a888236ffba5355.id )LEFT JOIN glpi_tickettasks ON (glpi_tickets.id = glpi_tickettasks.tickets_id )LEFT JOIN glpi_groups AS glpi_groups_groups_id_tech_e5ca2f53018fdc28a31faf534186b3a2 ON (glpi_tickettasks.groups_id_tech = glpi_groups_groups_id_tech_e5ca2f53018fdc28a31faf534186b3a2.id )WHERE glpi_tickets.is_deleted = 0 AND ( glpi_tickets.status NOT IN ('5','6') AND ( (glpi_users_f201be21cd638ee780d08cba4ceff0d4.id = '6') OR (glpi_groups_1358be41ea7158319a888236ffba5355.id = '183') OR ( (glpi_tickettasks.state <> '2' OR glpi_tickettasks.id IS NULL) AND (glpi_groups_groups_id_tech_e5ca2f53018fdc28a31faf534186b3a2.id = '183') ) OR ( (glpi_tickettasks.state <> '2' OR glpi_tickettasks.id IS NULL) AND (glpi_users_users_id_tech_e5ca2f53018fdc28a31faf534186b3a2.id = '6') ) OR glpi_tickets.status IN ('1')) AND ( ((CONVERT(glpi_tickets.time_to_own USING utf8) NOT LIKE '%-%' OR CONVERT(glpi_tickets.time_to_own USING utf8) IS NULL) ) OR glpi_tickets.time_to_own < '2019-09-03 12:26:34') ) GROUP BY glpi_tickets.idORDER BY ITEM_Ticket_19 DESC

Error : Error Code: 1054. Unknown column 'glpi_users_users_id_tech_e5ca2f53018fdc28a31faf534186b3a2.id' in 'where clause'

Screenshots

If applicable, add screenshots to help explain your problem.

Your GLPI setup (you can find it in Setup > General menu, System tab)

[code]

GLPI 9.4.3 (/glpi => /var/www/glpi)
Installation mode: TARBALL

Server

Operating system: Linux web 3.2.0-4-amd64 #1 SMP Debian 3.2.41-1 x86_64
PHP 5.6.20-0+deb8u1 apache2handler (Core, PDO, Phar, Reflection, SPL, SimpleXML, Zend OPcache, apache2handler, apc, apcu,
    bcmath, bz2, calendar, ctype, curl, date, dba, dom, ereg, exif, fileinfo, filter, ftp, gd, gettext, hash, iconv, imap, intl,
    ionCube Loader, json, ldap, libxml, mbstring, mcrypt, mhash, mysql, mysqli, openssl, pcre, pdo_mysql, pdo_pgsql, pgsql, posix,
    session, shmop, soap, sockets, ssh2, standard, sysvmsg, sysvsem, sysvshm, tokenizer, wddx, xdebug, xml, xmlreader, xmlrpc,
    xmlwriter, xsl, zip, zlib)
Setup: max_execution_time="30" memory_limit="512M" post_max_size="32M" safe_mode="" session.save_handler="files"
    upload_max_filesize="32M" 
Software: Apache/2.4.10 (Debian) PHP/5.6.20-0+deb8u1 mod_python/3.3.1 Python/2.7.9 OpenSSL/1.0.1t ()
    Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/76.0.3809.132 Safari/537.36
Server Software: (Debian)
    Server Version: 5.6.30-1
    Server SQL Mode: 
    Parameters: glpidbuser@192.168.65.80/glpidb
    Host info: 192.168.65.80 via TCP/IP

mysqli extension is installed
ctype extension is installed
fileinfo extension is installed
json extension is installed
mbstring extension is installed
iconv extension is installed
zlib extension is installed
curl extension is installed
gd extension is installed
simplexml extension is installed
xml extension is installed
ldap extension is installed
imap extension is installed
Zend OPcache extension is installed
APCu extension is installed
xmlrpc extension is installed
exif extension is installed
CAS extension is not present
Database version seems correct (5.6.30) - Perfect!Database version seems correct (5.6.30) - Perfect!
OK/var/www/glpi/files/_log : OK
OK/var/www/glpi/config : OK
OK/var/www/glpi/files : OK
OK/var/www/glpi/files/_dumps : OK
OK/var/www/glpi/files/_sessions : OK
OK/var/www/glpi/files/_cron : OK
OK/var/www/glpi/files/_graphs : OK
OK/var/www/glpi/files/_lock : OK
OK/var/www/glpi/files/_plugins : OK
OK/var/www/glpi/files/_tmp : OK
OK/var/www/glpi/files/_cache : OK
OK/var/www/glpi/files/_rss : OK
OK/var/www/glpi/files/_uploads : OK
OK/var/www/glpi/files/_pictures : OK
Web access to the files directory should not be allowed but this cannot be checked automatically on this instance.
Make sure acces to error log file is forbidden; otherwise review .htaccess file and web server configuration.
Libraries

htmLawed version 1.2.4 in (/var/www/glpi/lib/htmlawed)
phpmailer/phpmailer version 6.0.7 in (/var/www/glpi/vendor/phpmailer/phpmailer/src)
simplepie/simplepie version 1.5.2 in (/var/www/glpi/vendor/simplepie/simplepie/library)
tecnickcom/tcpdf version 6.2.26 in (/var/www/glpi/vendor/tecnickcom/tcpdf)
michelf/php-markdown in (/var/www/glpi/vendor/michelf/php-markdown/Michelf)
true/punycode in (/var/www/glpi/vendor/true/punycode/src)
iamcal/lib_autolink in (/var/www/glpi/vendor/iamcal/lib_autolink)
sabre/vobject in (/var/www/glpi/vendor/sabre/vobject/lib)
zendframework/zend-cache in (/var/www/glpi/vendor/zendframework/zend-cache/src)
zendframework/zend-i18n in (/var/www/glpi/vendor/zendframework/zend-i18n/src)
zendframework/zend-serializer in (/var/www/glpi/vendor/zendframework/zend-serializer/src)
monolog/monolog in (/var/www/glpi/vendor/monolog/monolog/src/Monolog)
sebastian/diff in (/var/www/glpi/vendor/sebastian/diff/src)
elvanto/litemoji in (/var/www/glpi/vendor/elvanto/litemoji/src)
symfony/console in (/var/www/glpi/vendor/symfony/console)
leafo/scssphp in (/var/www/glpi/vendor/leafo/scssphp/src)
paragonie/random_compat in (/var/www/glpi/vendor/paragonie/random_compat/lib)

LDAP directories

Server: 'olln.grp', Port: '3268', BaseDN: 'xx=xx', Connection filter: 'xx', RootDN:
        'xx=xx', Use TLS: xx

SQL replicas

Not active

Notifications

Way of sending emails: PHP

Plugins list

    fields               Name: Champs supplémentaires         Version: 1.10.1     State: Enabled
    shellcommands        Name: Commandes Shell                Version: 2.3.0      State: Enabled
    mydashboard          Name: Dashboard                      Version: 1.7.4      State: Enabled
    fusioninventory      Name: FusionInventory                Version: 9.4+1.1    State: Enabled
    genericobject        Name: Gestion d'objets               Version: 2.7.0      State: Enabled
    order                Name: Gestion des commandes          Version: 2.5.2      State: Enabled
    datainjection        Name: Injection de fichiers          Version: 2.7.0      State: Enabled
    mreporting           Name: Plus de rapports               Version: 1.6.1      State: Enabled
    dashboard            Name: Tableau de bord                Version: 0.9.6      State: Enabled

[/code]

Additional context

Add any other context about the problem here.

trasher commented 5 years ago

I've just tested on a up to date 9.4/bugfixes; I cannot reproduce. Please try with all plugins disabled maybe, and/or with up to date current bugfixes branch (or latest nightly)

InformatiqueOLLN commented 5 years ago

I've Just tested without any plugins, and the problem persists.

I'll test on the current bugfixes branch asap

InformatiqueOLLN commented 5 years ago

The problem is present on the online demo too. (logged in as admin)

online

trasher commented 5 years ago

Online demo is not up to date with latest 9.4/bugfixes branch yet

InformatiqueOLLN commented 5 years ago

Just tested with latest nightly, and without any plugin. The problem is still present

InformatiqueOLLN commented 5 years ago

After investigations: Filtering on task-technician only works Filtering on task-group only works Filtering on task-technician + task-group crashes the serarch Hoping it's helping

trasher commented 5 years ago

I still cannot reproduce

InformatiqueOLLN commented 5 years ago

On https://demo.glpi-project.org logged in as admin

I set a saved search having the problem : simpler bugged search

trasher commented 5 years ago

Thank for the saved search, good idea :)

trasher commented 5 years ago

@InformatiqueOLLN please try with #6290; issu should be fixed (but wait for this pull request to be approved and merged before using it on any production environment).

InformatiqueOLLN commented 5 years ago

It solves the problem !

Thanks

glpi-project / glpi

specific filter in tickets gives SQL error #6266