Oki, thanks for the feedback! The reason I did ask was to know whether we introduced a regression. If you found that it worked in older versions then please let us know.
> ORDER BY t0.oid DESC, t24.distribution_oid ASC Looking at your query it seems to me that those 2 columns contain the same values? t0 is the table 'distribution' and t24.distribution_oid is most likely a FK pointing to t0. Is that correct? In that case t0.oid and t24.distribution_oid will always be the same values? or is there some left outer with other id's? Is there inheritance involved somewhere? LieGrue, strub On Sunday, 11 September 2016, 3:37, Marc Logemann <marc.logem...@gmail.com> wrote: > >I recently migrated to 2.4.1 from 2.4.0 because of this bug. So negative on >this one. > > >In the meantime i discovered why MariaDB has index problems. Its not because >of the second order parameter, but because of the second order parameter which >is the same field in a different table but with ASC order. The ASC is the >thing but of course the second parameter shouldnt be there at all. > > >I suspect its because of the "join fetch". Just for the record, of course i >dont use @OrderBy on the relation. If i do it on the 1:n relation in question, >i get this: ORDER BY t0.oid DESC, t24.distribution_oid ASC, >t24.distribution_oid ASC LIMIT ?, ? > > >Perhaps i try to downgrade a bit more because i am quite sure that this >problem is new somehow.... i did another upgrade from an older version to >2.4.0 a few weeks ago. > > >regards >marc > > > > >2016-09-10 9:48 GMT+02:00 Mark Struberg <strub...@yahoo.de.invalid>: > >Hi Marc! >> >>Can you please try with 2.4.0? >>Is the generated query the same or without the column? >> >>LieGrue, >>strub >> >> >> >> >> >>On Saturday, 10 September 2016, 2:14, Marc Logemann <marc.logem...@gmail.com> >>wrote: >> >> >>> >>> >>>Hi, >>> >>>i suspect i found a bug which has bad consequences on MariaDB not using an >>>index anymore. Lets take this JPAQL: >>> >>>select d from Distribution d join fetch d.distributionContainerList where >>>d.client = ?1 and d.deleted = ?2 order by d.oid desc >>> >>>My Distribution entity is quite big when it comes to 1:n relations and >>>stuff. So i wont get into the details here, but this JPAQL will result in >>>the following SQL (compressed because too big otherwise): >>> >>>SELECT t0.oid, t0.jpaversion, t0.created, t0.createdby, ... >>> FROM distribution t0 LEFT OUTER JOIN dist_altfrom t1 ON t0.altfrom_oid >>>= t1.oid >>> LEFT OUTER JOIN clients t3 ON t0.client_oid = t3.oid LEFT OUTER >>>JOIN >>> cmrcarrier t17 ON t0.cmrcarrier_oid = t17.oid LEFT OUTER JOIN >>>countries >>> t20 ON t0.empf_country = t20.isocode2 LEFT OUTER JOIN users t21 ON >>> t0.user_oid = t21.oid INNER JOIN dist_container t24 ON t0.oid = >>> t24.distribution_oid LEFT OUTER JOIN countries t2 ON t1.country = >>> t2.isocode2 LEFT OUTER JOIN address t4 ON t3.address_oid = t4.oid >>>LEFT >>> OUTER JOIN bankaccount t6 ON t3.bankaccount_oid = t6.oid LEFT OUTER >>> JOIN communication t7 ON t3.communication_oid = t7.oid LEFT OUTER >>>JOIN >>> persons t8 ON t3.cperson_oid = t8.oid LEFT OUTER JOIN bankaccount >>>t10 >>> ON t3.nnbankaccount_oid = t10.oid LEFT OUTER JOIN workplaces t11 ON >>> t3.workplaceoid = t11.oid LEFT OUTER JOIN address t18 ON >>> t17.address_oid = t18.oid LEFT OUTER JOIN communication t19 ON >>> t17.communication_oid = t19.oid LEFT OUTER JOIN persons t22 ON >>> t21.person_oid = t22.oid LEFT OUTER JOIN workplaces t23 ON >>> t21.workplaceoid = t23.oid LEFT OUTER JOIN distribution t25 ON >>> t24.old_distribution_oid = t25.oid LEFT OUTER JOIN countries t5 ON >>> t4.country_id = t5.isocode2 LEFT OUTER JOIN communication t9 ON >>> t8.communication_oid = t9.oid LEFT OUTER JOIN balance t12 ON >>> t11.balance = t12.oid LEFT OUTER JOIN balance t13 ON t11.balance2 = >>> t13.oid LEFT OUTER JOIN clients t14 ON t11.client_oid = t14.oid >>>LEFT >>> OUTER JOIN printer t15 ON t11.labelprinter = t15.oid LEFT OUTER >>>JOIN >>> printer t16 ON t11.laserprinter = t16.oid >>> WHERE (t0.client_oid = ? AND t0.deleted = ?) >>> ORDER BY t0.oid DESC, t24.distribution_oid ASC LIMIT ?, ? >>> >>> >>>Just look at the order by clause in the SQL. It correctly used t0.oid >>>because JPAQL said so. But why on earth is there another order clause with >>>the field "t24.distribution_oid" ?? This is a back reference for a 1:n >>>relation from Table "dist_container" back to "distribution". >>> >>>The real problem is: as soon as there is another sorting parameter from a >>>joined table, MariaDB doesnt use my ForeignKey Index anymore and does a >>>FULL-Scan on the t24 table, which is pretty heavy on a multi million >>>records table. When i leave out that ugly t24.distribution_oid ordering >>>field, everything is fast and ok. >>> >>>Can anyone explain to me how this ordering field gets into the picture? >>> >>>thanks >>>marc >>> >>> >>> >> > > >
