Count elements in some lists

[lvessiller-opendsi]

Bug

In some lists, the new way to count elements can occur an SQL error :

• this new way uses "preg_replace" method with a regexp "^SELECT[a-zA-Z0-9._\s(),=<>:-']+\sFROM\s"
• and replace all fields in select with a "COUNT(rowid)" SQL request

In an external module you can use the hook "printFieldListFrom()", to add a subrequest with "LEFT JOIN (SELECT fields FROM table)", and an SQL error occurs when you show this list and you got a blank page.

This new way to build the count SQL request in list, seems to be a good idea to improve memory performances, however it also occurs errors with external modules.

Environment Version

14.0

Environment OS

No response

Environment Web server

No response

Environment PHP

7.3

Environment Database

Mysql

Environment URL(s)

No response

Expected and actual behavior

No response

Steps to reproduce the behavior

No response

Attached files

No response

[eldy]

To help me understand the issue #21444 can you provide the full SQL request you have when using your external module. String before and after.

[lvessiller-opendsi]

@eldy

SQL before

SELECT f.rowid AS id,
f.ref,
f.ref_client,
f.fk_soc,
f.type,
f.note_private,
f.note_public,
f.increment,
f.fk_mode_reglement,
f.fk_cond_reglement,
f.total_ht,
f.total_tva,
f.total_ttc,
f.localtax1 AS total_localtax1,
f.localtax2 AS total_localtax2,
f.fk_user_author,
f.fk_multicurrency,
f.multicurrency_code,
f.multicurrency_tx,
f.multicurrency_total_ht,
f.multicurrency_total_tva AS multicurrency_total_vat,
f.multicurrency_total_ttc,
f.datef,
f.date_valid,
f.date_lim_reglement AS datelimite,
f.module_source,
f.pos_source,
f.paye AS paye,
f.fk_statut,
f.close_code,
f.datec AS date_creation,
f.tms AS date_update,
f.date_closing AS date_closing,
f.retained_warranty,
f.retained_warranty_date_limit,
f.situation_final,
f.situation_cycle_ref,
f.situation_counter,
s.rowid AS socid,
s.nom AS name,
s.name_alias AS ALIAS,
s.email,
s.phone,
s.fax,
s.address,
s.town,
s.zip,
s.fk_pays,
s.client,
s.fournisseur,
s.code_client,
s.code_fournisseur,
s.code_compta AS code_compta_client,
s.code_compta_fournisseur,
typent.code AS typent_code,
state.code_departement AS state_code,
state.nom AS state_name,
country.code AS country_code,
p.rowid AS project_id,
p.ref AS project_ref,
p.title AS project_label,
u.login,
u.lastname,
u.firstname,
u.email AS user_email,
u.statut AS user_statut,
u.entity,
u.photo,
u.office_phone,
u.office_fax,
u.user_mobile,
u.job,
u.gender,
SUM(pf.amount) AS dynamount_payed,
SUM(pf.multicurrency_amount) AS multicurrency_dynamount_payed,
ccn.counter_number
FROM llx_societe AS s
LEFT JOIN llx_c_country AS country ON (country.rowid = s.fk_pays)
LEFT JOIN llx_c_typent AS typent ON (typent.id = s.fk_typent)
LEFT JOIN llx_c_departements AS state ON (state.rowid = s.fk_departement), llx_facture AS f
LEFT JOIN llx_paiement_facture AS pf ON pf.fk_facture = f.rowid
LEFT JOIN llx_projet AS p ON p.rowid = f.fk_projet
LEFT JOIN llx_user AS u ON f.fk_user_author = u.rowid
LEFT JOIN
(SELECT rowid AS fk_invoice,
CONCAT(SUBSTRING(REF, 1, 2), RIGHT(REF, 4)) AS counter_number
FROM llx_facture
WHERE REF NOT LIKE '(PROV%') AS ccn ON ccn.fk_invoice = f.rowid
WHERE f.fk_soc = s.rowid
AND f.entity IN (1)
GROUP BY f.rowid,
f.ref,
ref_client,
f.fk_soc,
f.type,
f.note_private,
f.note_public,
f.increment,
f.fk_mode_reglement,
f.fk_cond_reglement,
f.total_ht,
f.total_tva,
f.total_ttc,
f.localtax1,
f.localtax2,
f.datef,
f.date_valid,
f.date_lim_reglement,
f.module_source,
f.pos_source,
f.paye,
f.fk_statut,
f.close_code,
f.datec,
f.tms,
f.date_closing,
f.retained_warranty,
f.retained_warranty_date_limit,
f.situation_final,
f.situation_cycle_ref,
f.situation_counter,
f.fk_user_author,
f.fk_multicurrency,
f.multicurrency_code,
f.multicurrency_tx,
f.multicurrency_total_ht,
f.multicurrency_total_tva,
f.multicurrency_total_ttc,
s.rowid,
s.nom,
s.name_alias,
s.email,
s.phone,
s.fax,
s.address,
s.town,
s.zip,
s.fk_pays,
s.client,
s.fournisseur,
s.code_client,
s.code_fournisseur,
s.code_compta,
s.code_compta_fournisseur,
typent.code,
state.code_departement,
state.nom,
country.code,
p.rowid,
p.ref,
p.title,
u.login,
u.lastname,
u.firstname,
u.email,
u.statut,
u.entity,
u.photo,
u.office_phone,
u.office_fax,
u.user_mobile,
u.job,
u.gender
ORDER BY f.datef DESC,
f.rowid DESC

[lvessiller-opendsi]

SQL After

SELECT COUNT(f.rowid) AS nbtotalofrecords
FROM llx_facture
WHERE REF NOT LIKE '(PROV%') AS ccn ON ccn.fk_invoice = f.rowid WHERE f.fk_soc = s.rowid
AND f.entity IN (1)
GROUP BY f.rowid,
f.ref,
ref_client,
f.fk_soc,
f.type,
f.note_private,
f.note_public,
f.increment,
f.fk_mode_reglement,
f.fk_cond_reglement,
f.total_ht,
f.total_tva,
f.total_ttc,
f.localtax1,
f.localtax2,
f.datef,
f.date_valid,
f.date_lim_reglement,
f.module_source,
f.pos_source,
f.paye,
f.fk_statut,
f.close_code,
f.datec,
f.tms,
f.date_closing,
f.retained_warranty,
f.retained_warranty_date_limit,
f.situation_final,
f.situation_cycle_ref,
f.situation_counter,
f.fk_user_author,
f.fk_multicurrency,
f.multicurrency_code,
f.multicurrency_tx,
f.multicurrency_total_ht,
f.multicurrency_total_tva,
f.multicurrency_total_ttc,
s.rowid,
s.nom,
s.name_alias,
s.email,
s.phone,
s.fax,
s.address,
s.town,
s.zip,
s.fk_pays,
s.client,
s.fournisseur,
s.code_client,
s.code_fournisseur,
s.code_compta,
s.code_compta_fournisseur,
typent.code,
state.code_departement,
state.nom,
country.code,
p.rowid,
p.ref,
p.title,
u.login,
u.lastname,
u.firstname,
u.email,
u.statut,
u.entity,
u.photo,
u.office_phone,
u.office_fax,
u.user_mobile,
u.job,
u.gender
ORDER BY f.datef DESC,
f.rowid DESC

[lvessiller-opendsi]

Fixed with "^SELECT[a-zA-Z0-9._\s(),=<>:-']+\sFROM/Ui" (see product list for best practice : #22882)