[Koha] report behaves differently if run from intranet or the outside [SOLVED]

[Alvaro Cornejo]

Hi

I found the solution:

Place a "LIMIT XXX" at the end of the report. It seems that there is a hard
limit for public reports that gets overridden if a LIMIT statement is
placed on the query.

Regards,

Alvaro


|----------------------------------------------------------------------------------------|
 Stay safe / Cuídate/  Reste sécurisé
*7* Switch off as you go / Apaga lo que no usas /  Débranchez au fur et à
mesure.
 *q *Recycle always / Recicla siempre / Recyclez toujours
 P Print only if absolutely necessary / Imprime solo si es necesario /
Imprimez seulement si nécessaire


Le sam. 11 juil. 2020 à 11:27, Alvaro Cornejo <cornejo.alvaro at gmail.com> a
écrit :

> Hi
>
> It seems to be a problem with the public setup of the report. Hard limit
> somewhere in the code?
>
> I changed almost all my reports to public to test and in all cases, I get
> no more than the first 10 records.
>
> I initially thought something crashed on the process but json files are
> completed, not corrupted or damaged.
>
> Regards
>
> Alvaro
>
>
>
> |----------------------------------------------------------------------------------------|
>  Stay safe / Cuídate/  Reste sécurisé
> *7* Switch off as you go / Apaga lo que no usas /  Débranchez au fur et à
> mesure.
>  *q *Recycle always / Recicla siempre / Recyclez toujours
>  P Print only if absolutely necessary / Imprime solo si es necesario /
> Imprimez seulement si nécessaire
>
>
> Le sam. 11 juil. 2020 à 00:41, Alvaro Cornejo <cornejo.alvaro at gmail.com>
> a écrit :
>
>> Hi
>>
>> I´ve been working with a report to get records stats and found a strange
>> behaviour.
>>
>> The exactly same report provides different data if run it from the report
>> interface in koha than calling it from the outside (public link) either
>> from a perl script or from a browser (tested on ie / chrome)
>>
>> The query is quite simple:
>> ------------------
>> SELECT
>> ExtractValue(metadata,'//datafield[@tag="942"]/subfield[@code="c"]') AS
>> Tipo,
>> FORMAT((COUNT(DISTINCT biblionumber)),0) AS Registros,
>> FORMAT((COUNT(itemnumber)),0) AS Volúmenes
>>
>> FROM biblio_metadata
>> LEFT JOIN items USING (biblionumber)
>>
>> GROUP BY Tipo
>> WITH ROLLUP
>> -------------------------
>>
>> if I run it from intranet I get (being set public):
>>
>> Tipo     Registros     Volúmenes
>> Analitico de Revista     7      7
>> ANLIB      6,939        10
>> ANREV     8,351       671
>> ART        96,109       95,054
>> ART-E      50        1
>> LIB        30,433        30,783
>> LIB-A      4,919       91
>> LIB-E      27       27
>> SEP      1,304      1,300
>> SER      165       485
>> SER-E     1        2
>> TESIS      9      9
>> VID       4       4
>>         148,318      128,444  (this is from ROLLUP
>>
>> If run it from the outside I get only the first 10 lines:
>> (tested with script and manually parse the JSON and calling the public
>> link with chrome and ie)
>>
>> Tipo     Registros     Volúmenes
>> Analitico de Revista     7      7
>> ANLIB      6,939        10
>> ANREV     8,351       671
>> ART        96,109       95,054
>> ART-E      50        1
>> LIB        30,433        30,783
>> LIB-A      4,919       91
>> LIB-E      27       27
>> SEP      1,304      1,300
>> SER      165       485
>>
>> I initially thought there were some bad records and fixed some that do
>> not have a 942c field defined.
>>
>> I tested it with a condition to get only new records in the last 30 days
>> and this behavior does not happen. Although the report have only 8 rows
>> including the rollup
>>
>> Updated query
>> ------------------
>> SELECT
>>
>> ExtractValue(metadata,'//datafield[@tag="942"]/subfield[@code="c"]') AS
>> Tipo,
>> FORMAT((COUNT(DISTINCT biblionumber)),0) AS Registros,
>> FORMAT((COUNT(itemnumber)),0) AS Volúmenes
>>
>> FROM biblio_metadata
>> LEFT JOIN items USING (biblionumber)
>>
>> WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= items.dateaccessioned
>>
>> GROUP BY Tipo
>> WITH ROLLUP
>> -------------------------
>>
>> Same result through intranet and public links with a script / browser
>> Tipo Registros Volúmenes
>> ANLIB    1    1
>> ART    895     895
>> LIB    60    62
>> LIB-E    22    22
>> SEP     21    21
>> SER    1   1
>> VID    4    4
>>        1,004    1,006
>>
>> I´ve increased the interval of days to this second report until I got
>> more than 10 lines and the behaviour repeats. From the public link I can
>> get no more than 10 lines but from within koha I get all the lines.
>>
>> I´m running koha 19.11.07.000 with perl  5.026001 in ubuntu 18.04
>>
>> Any ideas? I guess there is no record limit since I´ve coverflow running
>> and I get more than 10 records.
>>
>> Might be a timeout? It's a heavy query but runs for about 30 seconds on
>> intranet and from a browser.
>>
>> Regards
>>
>> Alvaro
>>
>>
>>
>>
>> |----------------------------------------------------------------------------------------|
>>  Stay safe / Cuídate/  Reste sécurisé
>> *7* Switch off as you go / Apaga lo que no usas /  Débranchez au fur et
>> à mesure.
>>  *q *Recycle always / Recicla siempre / Recyclez toujours
>>  P Print only if absolutely necessary / Imprime solo si es necesario /
>> Imprimez seulement si nécessaire
>>
>