weird bug with order by (2.4.1)

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

weird bug with order by (2.4.1)

logemann
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
Reply | Threaded
Open this post in threaded view
|

Re: weird bug with order by (2.4.1)

Mark Struberg-3
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 <[hidden email]> 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
>
>
>
Reply | Threaded
Open this post in threaded view
|

Re: weird bug with order by (2.4.1)

logemann
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 <[hidden email]>:

> 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 <
> [hidden email]> 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
> >
> >
> >
>
Reply | Threaded
Open this post in threaded view
|

Re: weird bug with order by (2.4.1)

Mark Struberg-3
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 <[hidden email]> 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 <[hidden email]>:
>
>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 <[hidden email]> 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
>>>
>>>
>>>
>>
>
>
>