Mailing-List: contact users-help@openjpa.apache.org; run by ezmlm
Precedence: bulk
Reply-To: users@openjpa.apache.org
Date: Sun, 11 Sep 2016 08:35:27 +0000 (UTC)
From: Mark Struberg <struberg@yahoo.de.INVALID>
Reply-To: Mark Struberg <struberg@yahoo.de>
To: Marc Logemann <marc.logemann@gmail.com>,
	"users@openjpa.apache.org" <users@openjpa.apache.org>
Message-ID: <615762972.2250207.1473582927891@mail.yahoo.com>
In-Reply-To: <CA+KtLoaVvQLKOs1tD9aZzk=HFDD1j3OtmWtMBFF67D-_mJg5Qg@mail.gmail.com>
References: <CA+KtLobU-Jfh3fyrbhMLn9F7TqUZWHyigviUJjqr3EfR-zDaxw@mail.gmail.com> <953055937.1928316.1473493682329@mail.yahoo.com> <CA+KtLoaVvQLKOs1tD9aZzk=HFDD1j3OtmWtMBFF67D-_mJg5Qg@mail.gmail.com>
Subject: Re: weird bug with order by (2.4.1)
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
archived-at: Sun, 11 Sep 2016 08:35:43 -0000

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.logemann@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 <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
>>>
>>>
>>>
>>
>
>
>