Page 1 sur 1

Maarch 1.6, pgsql errors

Message Publié : 13 Jan 2017, 17:01
par fangebault

I'm testing maarch_courrier 1.6 and I've hit the same kind of SQL bug on many pages.
Here is an an example:
Code : Tout sélectionner
ERREUR:  syntaxe en entrée invalide pour l'entier : «  »
2016-12-16 10:45:24 CET [10354-586] maarch_dcpaf@maarch16_dcpaf INSTRUCTION :  SELECT  res_view_letterbox.res_id,res_view_letterbox.res_id as is_labeled,res_view_letterbox.status,res_view_letterbox.subject,res_view_letterbox.category_id as category_img,res_view_letterbox.contact_firstname,res_view_letterbox.contact_lastname,res_view_letterbox.contact_society,res_view_letterbox.user_lastname,res_view_letterbox.user_firstname,res_view_letterbox.dest_user,res_view_letterbox.type_label,res_view_letterbox.creation_date,res_view_letterbox.entity_label,res_view_letterbox.category_id, exp_user_id,res_view_letterbox.alt_identifier,regexp_replace(alt_identifier, '[^a-zA-Z]', '', 'g'),regexp_replace(alt_identifier, '[^0-9]', '', 'g')::int,res_view_letterbox.count_attachment,res_view_letterbox.is_multicontacts,res_view_letterbox.priority,res_view_letterbox.folders_system_id,res_view_letterbox.folder_name FROM res_view_letterbox  WHERE (status not in ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10)) and ((( (DESTINATION = 'EMUDGAC' or 1=2) )) or ( (  scan_location = 'EMUDGAC'  AND  status = 'ATT') or (  status = 'RES' AND (scan_location = 'EMUDGAC' or initiator = 'EMUDGAC')) or (  status IN ('NEW', 'COU') AND dest_user in (SELECT user_id FROM users_entities WHERE entity_id = 'EMUDGAC')) or (  status = 'VAL' AND destination <> 'COU' AND dest_user in (SELECT user_id FROM users_entities WHERE entity_id = 'EMUDGAC')) or (  status = 'RET' AND (scan_location = 'EMUDGAC' or initiator = 'EMUDGAC')))) order by creation_date desc OFFSET 0 LIMIT 500

After some investigations it appears that the problem comes from unexpected blank values in mlb_coll_ext.alt_identifier.
I don't know if such inconsistency is possible in real usage. I'm working with sample data provided during installation. The fact is that "good rows" look like this "MAARCH/2016A/5" and bad rows contains the empty string instead of null.

However I wrote a patch that seems to circumvent this issue. Can you review it and possibly integrate it?

PS: I've installed maarch_courrier 1.6 from SVN by following the instructions for Debian-Ubuntu in the wiki.

Re: Maarch 1.6, pgsql errors

Message Publié : 14 Fév 2017, 16:46
par fangebault
Hi, I just noticed that I forgot to upload the patch. Here it is.

Re: Maarch 1.6, pgsql errors

Message Publié : 15 Mars 2017, 12:04
par lgi

Thank you for the patch.

We can not accept this patch because it generates regressions.

We advise you rather to set the application correctly so as not to arrive in case the chrono number is empty.

Re: Maarch 1.6, pgsql errors

Message Publié : 24 Mars 2017, 15:40
par odaniel

Can you tell me what regressions are generated please ?

Re: Maarch 1.6, pgsql errors

Message Publié : 28 Mars 2017, 10:48
par fangebault
I guess the regression occurs with postgresql < 9.1 because my patch uses the 'concat()' syntax.
Here is the same patch using the more portable syntax "||" for postgresql.
Can you please review this new patch?