openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Marc Logemann <marc.logem...@gmail.com>
Subject weird bug with order by (2.4.1)
Date Sat, 10 Sep 2016 00:14:17 GMT
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