Hi Philippe, I really do not understand the fix. If think you are going to get random data. My understanding of this change is that you are going to get only 1 row that will be populated with the MAX of each column. What am I missing?
Le ven. 10 janv. 2020 à 22:30, Philippe Blouin <philippe.blo...@inlibro.com> a écrit : > > A month later... > > Maryse found a (recommanded) solution for the aqspent.pl crashes: > > - aqorders.biblionumber, aqorders.basketno, aqorders.ordernumber, > - quantity-quantityreceived AS tleft, > - budgetdate, entrydate, > - aqbasket.booksellerid, > + MAX(aqorders.biblionumber) AS biblionumber, MAX(aqorders.basketno) AS > basketno, aqorders.ordernumber, > + MAX(quantity)-MAX(quantityreceived) AS tleft, > + MAX(budgetdate) AS budgetdate, MAX(entrydate) AS entrydate, > + MAX(aqbasket.booksellerid) AS booksellerid, > GROUP_CONCAT(DISTINCT itype SEPARATOR '|') as itypes, > - title, > - aqorders.invoiceid, > - aqinvoices.invoicenumber, > - quantityreceived, > - unitprice_tax_included, > - datereceived, > - aqbooksellers.name as vendorname > + MAX(title) AS title, > + MAX(aqorders.invoiceid) AS invoiceid, > + MAX(aqinvoices.invoicenumber) AS invoicenumber, > + MAX(quantityreceived) AS quantityreceived, > + MAX(unitprice_tax_included) AS unitprice_tax_included, > + MAX(datereceived) AS datereceived, > + MAX(aqbooksellers.name) as vendorname > > We'll hack that in all our distributions. But I'd really like to have an > official distributed solution to the problem. To me, it's not that much > uglier than the fix adding all the column to avoid the problem with > ONLY_FULL_GROUP_BY. > > What's your take on it? > > Regards, > > Philippe Blouin, > Directeur de la technologie > > Tél. : (833) 465-4276, poste 230 > philippe.blo...@inlibro.com > > inLibro | pour esprit libre | www.inLibro.com > On 2019-12-13 11:25 a.m., Philippe Blouin wrote: > > minuscule: > > 2000 biblio, 12000 items > 8 aqbudgets, 200 invoices, 25 booksellers, 60 baskets, 240 orders. > > That makes little sense... > > Philippe Blouin, > Directeur de la technologie > > Tél. : (833) 465-4276, poste 230 > philippe.blo...@inlibro.com > > inLibro | pour esprit libre | www.inLibro.com > On 2019-12-13 9:49 a.m., Jonathan Druart wrote: > > How big is your tables? > As you can see SQL query is really ugly, and can be good to split it anyway... > > Le ven. 13 déc. 2019 à 15:26, Philippe Blouin > <philippe.blo...@inlibro.com> a écrit : > > Thanks, > > Ok, I've never seen the "$field is not in group by" error. Interesting. > > I've followed the bread crumbs: 21622 -> 20182 -> 21723 -> 22260... read it > all. It's "damned if you do, damned if you don't". > > ONLY_FULL_GROUP_BY mode is the SQL Standard, I don't think it would be wise > to stray from that. (I can do it locally, but I don't think Koha should, > philosophically) > > But my client can't click on spent.pl. And no memory setting seems to fix > it. So what's my solution? Could the query be reworked in two steps? This > is not a screen where 0.2s will make a difference in the user experience. > > Suggestions welcomed. > > Philippe Blouin, > Directeur de la technologie > > Tél. : (833) 465-4276, poste 230 > philippe.blo...@inlibro.com > > inLibro | pour esprit libre | www.inLibro.com > On 2019-12-13 4:33 a.m., Jonathan Druart wrote: > > Hi Philippe, > > See bug 21622. > It is necessary to not explode with "$field is not in group by" (when > sql_mode has ONLY_FULL_GROUP_BY) > However we may need to discuss the need of this flag if it brings us > performance issues. > > Cheers, > Jonathan > > Le jeu. 12 déc. 2019 à 23:23, Philippe Blouin > <philippe.blo...@inlibro.com> a écrit : > > Good morning Koha, > > Newbie question: why do we have 16 fields in GROUP BYs ? Are they really all > necessary to eliminate duplicate lines in budgets? Genuine question. > > When in acqui-home.pl, clicking certain entries linking to spent.pl just > crashes with the error listed in the mail's object: Out of memory. Although > I hate that solution, I went with it, increased sort_buffer_size 64x fold > (I'm a generous guy). Still not enough. > > Removing most lines in GROUP BY of spent.pl solves it all, of course. > > -- > Philippe Blouin, > Directeur de la technologie > > Tél. : (833) 465-4276, poste 230 > philippe.blo...@inlibro.com > > inLibro | pour esprit libre | www.inLibro.com > _______________________________________________ > Koha-devel mailing list > Koha-devel@lists.koha-community.org > https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel > website : http://www.koha-community.org/ > git : http://git.koha-community.org/ > bugs : http://bugs.koha-community.org/ > > > _______________________________________________ > Koha-devel mailing list > Koha-devel@lists.koha-community.org > https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel > website : http://www.koha-community.org/ > git : http://git.koha-community.org/ > bugs : http://bugs.koha-community.org/ _______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
