openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Marc Logemann <marc.logem...@gmail.com>
Subject Re: weird bug with order by (2.4.1)
Date Sun, 11 Sep 2016 01:37:07 GMT
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 <struberg@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.logemann@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
> >
> >
> >
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message